On Tue, Jan 12, 2016 at 3:43 PM, Keith Medcalf <kmedcalf at dessus.com> wrote:
> On Tuesday, 12 January, 2016 13:51, James K. Lowden < > jklowden at schemamania.org> said: > > On Fri, 8 Jan 2016 08:28:29 +0100 > > Dominique Devienne <ddevienne at gmail.com> wrote: > > > > One way to do that would be to honor a special user-created table, > > > > say "PRAGMAS", with name-value pairs that are automatically applied > > > > when the database is opened. > > > Interesting idea. A "special"-to-SQLite sqlite_pragmas table, similar > > > to sqlite_master. > > Similar in name, yes. sqlite_master is special in that it's built into > > the database file structure, though. You couldn't add another such > > table without changing the file structure, something OFL understandably > > doesn't want to do. > > > I'm suggesting that the presence of a user-created > > "configuration table" would govern the behavior of the database, > > analogous to a configuration file. > > I would suppose that one could implement this using an SQLITE_EXTRA_INIT > linked function (a symbol attached to SQLITE_EXTRA_INIT is called at the > end of the sqlite3_initialize routine to perform extra initialization). > Pointing this to a function which calls a bunch of sqlite3_auto_extension > to add internally compiled extension init routines to the initialization of > each connection thus making such extensions available to all connections by > auto-magic. > > I suppose one could write some code which looks for the desired table and, > if found, reads and executes the statements contained therein. > > The entry point of this routine can be added into the auto_extension list > (even though it is not an extension) through the EXTRA_INIT hook. > > From my reading of the code, a pointer in the auto_extension list does not > *have* to actually be an extension -- it is just code that is called when a > connection object is created that receives the connection object as an > argument. If it *is* an extension init routine, it just happens to make > some calls to add functions/aggregates/modules/etc., but I see nothing to > indicate that this is actually required. > > The only thing you will NOT be able to do this way is anything that needs > to be set *before* initialization. Running a set of pragma's on each > connection should work just peachy though, I would expect. If you are writing a client that uses SQLite databases, then you probably have a wrapper for sqlite3_open*(), and you could just put the code to query and run the pragmas in there. If I am writing a client that can read SQLite databases, then I probably don't want your database to be injecting a bunch of arbitrary PRAGMA calls into my client. To be honest, I'm kind of pressed to imagine the set of pragmas which make sense for the database to select, rather than the client, since most of them affect client operation. I'd be very annoyed if my client has strong memory constraints and the database forced some how cache_size setting. Or if my client was written assuming temp_store = FILE and the database forced temp_store = MEMORY. Having the database run synchronous = OFF or journal_mode = OFF would be pretty fun! The only ones which seem more like they could be part of the schema itself are foreign_keys and secure_delete, so maybe it would make more sense to handle them differently in the first place, more like page_size or encoding where once set they're sticky. -scott
