Hello all, I have two slightly related questions regarding sqlite database handles.
First, I'm developing a system that opens thousands of different sqlite databases at a variety of times, some database handles more often than others, but never the same one multiple times concurrently, though sometimes the same database handle will get opened and closed multiple times in a row in quick succession. My understanding is that this isn't as performant as it could be, since it's a shame I'm not just reusing sqlite pages in memory instead of swapping to and from disk. My question regards the shared cache feature (http://www.sqlite.org/c3ref/enable_shared_cache.html). Is this an optimization for the same sqlite db handle being opened concurrently, or does this actually assist with keeping sqlite pages in memory so they don't have to be read from disk again the second time the same database handle is opened after the first one has been closed? If so, how do I configure the amount of memory sqlite uses for caching said pages, and if not, is there anything better I can do besides keeping an LRU cache of DB handles open? Second, in the same system, on these db handles, I am considering allowing users of my system to provide Lua-scripts with built in SQL queries and run them, but I don't want them to be able to modify the database. Of course, I can run the Lua script with appropriate bindings to my existing db handles inside a transaction, but I'd rather have SQL queries that may potentially write to disk fail completely, instead of wasting resources just to get rolled back. Is there a way to temporarily mark a read/write sqlite disk handle read-only, or perhaps is there a way to check a prepared statement before allowing its use as to if it will attempt to write to disk? Digging through the code it looks like I could potentially set readOnly flags in various structs (pager, vdbe, etc), but there doesn't seem to be a library interface to do so. I suppose if the shared cache works as I would like, there is little performance harm in closing the database and then reopening in read-only mode. Thanks in advance, -JT _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
