On 12/29/2017 01:28 AM, Gwendal Roué wrote:
Hello,
Season's greetings to all SQLite fellows!
I'm developping a library that would like to keep a "cache" of some information
about the database schema. Such information are the columns of a table, its primary key,
or its indexes. The purpose of this cache is not really speed, even if it may help, but
mainly to avoid cluttering the sqlite3_trace/sqlite3_trace_v2 hooks with noisy pragmas
whenever the library needs to infer some implicit information from the actual database
schema.
This cache has to be invalidated whenever the schema changes. The Compile-Time
Authorization Callback [1] is the perfect tool for the job, since it allows to
identify statements that create, drop, alter tables and indexes.
Everything is fine and easy when a single connection is used in a
single-threaded way: statements are executed one after the other, and the
management of the schema cache is trivial.
It's much less trivial with the WAL mode. I focus on a setup which uses a
unique writer connection, and several reader connections. All connections are
used sequentially in their own thread, but readers and writer can run
concurrently in order to take advantage from the WAL mode.
When a read-only connection uses a deferred transaction to enter snapshot
isolation, it doesn't see the changes performed by other transactions. For
example, if a reader acquires snapshot isolation before a table is altered by
the writer, it won't see the alteration until it commits its deferred
transaction. I wish my schema cache would behave the same.
To be precise, I only have two important needs:
1. A connection's schema cache is correct, which means that it never contains
information that does not match SQLite's genuine view of the database schema.
Being invalidated/empty is correct, if not efficient (the missing information
is then loaded from SQLite).
2. Synchronization points between readers and writers are avoided (non-blocking
access is the whole point of WAL, and I want to avoid locks as much as possible)
I was hoping that a connection would have a "schema version": an automatically
incremented value that SQLite bumps whenever the schema is changed. That would have been
enough for my use case. Unfortunately, PRAGMA schema_version reads the database header,
and I thus guess that it does not play well with WAL (I'm not sure).
"PRAGMA schema_version" should work the same way in WAL mode. The pragma
will read the "database header" from the newest version of page 1 in the
wal file if required.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
