1
3rd Party Plugins - (fb2k) / Re: [fb2k v2] SQLite Utilities (foo_sqlite)
Last post by Sue Dunham -All of my queries are separate .sql files stored in a subfolder of the profile directory. Some of those used a nested call of the eval and fileio_read functions to execute other such scripts. I do this to cut down on repetition, especially for long sequences of statements that several queries have in common.
What this used to look like was something like this:
Code: [Select]
[setup stuff]
SELECT eval(fileio_read(fb2k_profile_path()
|| '\themename\sql\scriptfile.sql'));
[SELECT query to display results in SQLite Console
| UPDATE query to write data to music file tags]
I wanted to get away from hard-coding the themename directory in each script, since that changes with different themes. So this value became a field in a table, constants, and a view created the full paths with that and other values stored in that table. So the replacement became this:
Code: [Select]
SELECT eval(fileio_read((SELECT sql FROM view_paths)
|| 'scriptfile.sql'));
Inserting that query in lieu of the string value gave rise to errors from the database being locked. I don't know anything about this issue.
The fix was to take the beginning of the scriptfile.sql out of it and paste it into each calling script right before the eval(fileio_read()) part. What was done for brevity may have been bad for other reasons. These first statements would DROP and CREATE the table that was subsequently used for the [SELECT | UPDATE] that occurred afterward in the main query. It seems that that separation was causing the lock issue, though it was fine before with the string values. The meat of scriptfile.sql is a series of statements manipulating that table, but maybe there's some parent-child threading circumstance I don't grok, or something.
Does that make any sense? How can I understand what happened? My use case is such a mass of bespoke tables and custom metadata tags that I'm hoping this description will suffice.