On Wed, Jan 13, 2016 at 12:42 AM, Jean-Christophe Deschamps < jcd at antichoc.net> wrote:
> At 08:28 13/01/2016, you wrote: > >> On Wed, Jan 13, 2016 at 2:39 AM, Simon Slavin <slavins at bigfraud.org> >> wrote: >> > On 12 Jan 2016, at 11:56pm, Scott Hess <shess at google.com> wrote: >> > > 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. >> > >> > It is, after all, the equivalent of an autoexecute macro. And we all >> know >> > how good an idea that was. >> > >> > Okay, folks, I'm with Scott. If you want SQLite to execute a bunch of >> > commands when the database is opened write your own replacement for >> _open_ >> > which opens the file then reads commands from a table and executes them. >> > >> >> You have in my opinion taken this out of context, and are assuming the >> important part is the application, and not the data (i.e. database file). >> >> In original idea was simply to associate with the database some essential >> settings, which should have been such in the first place and aren't only >> because of legacy reasons, and not arbitrary macros. Keith just got >> carried >> away :) --DD >> > > Exactly! I see a clear difference between settings which are *-required-* > by the schema and those which are only a convenience or comfort for the > application(s). > > The former type includes what should actually be part of the schema in an > ideal world: > foreign keys, recursive triggers, loading of [auto]extensions set > ON/OFF, ...; > function extensions and collations, ICU, ... > > If some index uses a custom collation which isn't loaded, then queries > will give erroneous results and not perform DB changes as intended. > Many of these items are things which require that your version of SQLite be compiled with the right settings and/or additional code, so they definitely _are_ the purview of the client, not the data. The data may be corrupted by trying to use it in the wrong context, sure, but the client may be rendered insecure or buggy by loading inappropriate binary code. I think best case is for the database to have a catalog of what it needs, and the client to call an API function to verify that it is constructed in a way to meet the catalog's needs. Perhaps the intersection could have allowances for missing requirements, for instance read-only access can work without foreign-key support. If a schema change requires for instance recursive triggers ON, then maybe > 5, 10 or more applications will need maintainance and redistribution. > Forgetting update of a single app may silently spoil DB integrity. > This issue would seem to apply if you make a change requiring a new SQLite version, or different SQLite compile-time settings. Or if you find a bug with your queries or schema. These are really distinct areas of responsibility, so rather than partial piecemeal solutions at the SQLite level, you'd probably be better off finding a distribution strategy which makes such updates reasonable, or an architecture which isolates the database access into a single component. -scott
