On 01/16/2014 10:46 PM, Mcdonald, Brett wrote:
Can an existing sqlite read-only connection be promoted to a read-write
connection, perhaps using sqlite3_file_control() and sqlite3_io_methods? Or do
previously executed 'read-only' sql statements make promotion less desirable
then simply closing and opening another connection as read-write? I've no
strong objection to the latter, it's just that enquiring minds want to know and
perhaps a bit of background might prove interesting.
It appears that when opening a db connection and executing an sql select
statement, the wal file size increases when using sqlite3_open() but does not
increase when using sqlite3_open_v2() with the SQLITE_OPEN_READONLY flag. I
hope this is expected. Now, our embedded product's requirements push the
limits of flash lifespan and checkpoints by far generate the majority of flash
unit erases; it's just the nature of our beast. So, when pragmas page_size and
wal_autocheckpoint control checkpoint frequency via wal file size, wal file
growth and checkpoints due to 'read-only purposes' is flash expensive.
I don't see why that would happen. A SELECT statement should
never cause the WAL file to grow.
It might create a *-shm file. If that is the problem, and
you only ever have a single connection to the database
open, you could try executing "PRAGMA locking_mode = exclusive"
immediately after opening the db file. This will cause SQLite
to use an in-memory wal-index instead of creating the *-shm file.
http://www.sqlite.org/draft/fileformat2.html#walindexformat
Dan.
For our product, it's ideal that by default all db connections are opened using
SQLITE_OPEN_READONLY, at runtime use sqlite3_stmt_readonly() and promote an
existing db connection to read-write accordingly. I realize db connection
caching is a concern, but in theory clients of ours (that read the db many
times per second) generate a single db connection and are none the wiser
regarding promotion and wal file growth.
Thanks in advance.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
