Re: [sqlite] Should .dump preserve foreign_keys pragma?

[Dominique Devienne]

On Fri, Jan 9, 2015 at 11:57 AM, Dominique Devienne 
wrote:

> On Fri, Jan 9, 2015 at 11:35 AM, Niall O'Reilly 
> wrote:
>
>> At Thu, 08 Jan 2015 15:55:00 -0700,
>> Keith Medcalf wrote:
>> >
>> > when you load a dump file you need to have that foreign
>> > key enforcement off in order to be able to load the database.  This
>> > is because the tables and data are dumped in random order, not in
>> > hierarchical order (parents of parents then their children then
>> > their children and so on and so forth) or mayhaps there are
>> > self-referential or referential loops which cannot be resolved
>> > without turning off foreign key enforcement while loading the
>> > database "in bulk" rather than by following the application business
>> > logic processing to only add records the would meet referential
>> > constraints.
>>
>>   Thanks for explaining. This makes sense.
>>
>
> Yet SQLite defers FK errors to commit time, so even loading in a random
> order should work provided the load is within a single transaction, which
> it most likely is.
>
> So SQLite could well output PRAGMA foreign_keys=ON and still be able to
> load the dump. --DD
>

Rah, scrap the above... Sorry. You're right of course. I confused the
temporary FK violations during statement processing, and Oracle's deferred
constraint processing at commit time. Sorry for the noise. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[Dominique Devienne]

On Fri, Jan 9, 2015 at 11:35 AM, Niall O'Reilly 
wrote:

> At Thu, 08 Jan 2015 15:55:00 -0700,
> Keith Medcalf wrote:
> >
> > when you load a dump file you need to have that foreign
> > key enforcement off in order to be able to load the database.  This
> > is because the tables and data are dumped in random order, not in
> > hierarchical order (parents of parents then their children then
> > their children and so on and so forth) or mayhaps there are
> > self-referential or referential loops which cannot be resolved
> > without turning off foreign key enforcement while loading the
> > database "in bulk" rather than by following the application business
> > logic processing to only add records the would meet referential
> > constraints.
>
>   Thanks for explaining. This makes sense.
>

Yet SQLite defers FK errors to commit time, so even loading in a random
order should work provided the load is within a single transaction, which
it most likely is.

So SQLite could well output PRAGMA foreign_keys=ON and still be able to
load the dump. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[Niall O'Reilly]

At Thu, 08 Jan 2015 15:55:00 -0700,
Keith Medcalf wrote:
> 
> when you load a dump file you need to have that foreign
> key enforcement off in order to be able to load the database.  This
> is because the tables and data are dumped in random order, not in
> hierarchical order (parents of parents then their children then
> their children and so on and so forth) or mayhaps there are
> self-referential or referential loops which cannot be resolved
> without turning off foreign key enforcement while loading the
> database "in bulk" rather than by following the application business
> logic processing to only add records the would meet referential
> constraints.

  Thanks for explaining. This makes sense.

> ---
> Theory is when you know everything but nothing works.  Practice is
> when everything works but no one knows why.  Sometimes theory and
> practice are combined: nothing works and no one knows why.

  I've been appreciating that sig for a while, and am glad to have a
  real message as an opportunity for saying so!

  Best regards,
  Niall O'Reilly
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[Keith Medcalf]

It is correct.  

On the chance that you happen to have compiled your version of SQLite with 
Foreign Key enforcement turned on by default instead of off; or, a later 
versions decides to change the default to on rather than off; when you load a 
dump file you need to have that foreign key enforcement off in order to be able 
to load the database.  This is because the tables and data are dumped in random 
order, not in hierarchical order (parents of parents then their children then 
their children and so on and so forth) or mayhaps there are self-referential or 
referential loops which cannot be resolved without turning off foreign key 
enforcement while loading the database "in bulk" rather than by following the 
application business logic processing to only add records the would meet 
referential constraints.

The batch process cannot turn it back on for you because it does not really 
know (keep track of) the state around the "load" operation.  In other words, if 
you need FK enforcement on, use the pragma to turn it on.  If you need it off, 
use the pragma to turn it off.  It is unwise to depend on the default always 
being the same for everyone everywhere for all time.

In the present circumstance the default is off.  This is because at one time 
there was no FK enforcement so no application turned FK enforcement off.  If 
you changed the default, then all those applications might stop working 
(because the DDL syntax was supported, but it was just ignored).

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Niall O'Reilly
>Sent: Thursday, 8 January, 2015 06:39
>To: General Discussion of SQLite Database
>Subject: [sqlite] Should .dump preserve foreign_keys pragma?
>
>
>  Hello.
>
>  What follows puzzles me.  Either there's something I don't
>  understand, or something is wrong.
>
>dhcp-179(niall)7: sqlite3
>SQLite version 3.8.5 2014-08-15 22:37:57
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> PRAGMA foreign_keys=on;
>sqlite> PRAGMA foreign_keys;
>1
>sqlite> .dump
>PRAGMA foreign_keys=OFF;
>BEGIN TRANSACTION;
>COMMIT;
>sqlite> ^D
>dhcp-179(niall)8:
>
>  I'ld have expected the foreign_keys pragma setting to have been
>  preserved.
>
>  The version shown is currently bundled with Apple's OSX Yosemite.
>  I've checked subsequent release history for changes and not found
>  any of relevance.
>
>  Thanks in anticipation for any enlightenment.
>
>  Best regards,
>  Niall O'Reilly
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[J Decker]

On Thu, Jan 8, 2015 at 9:05 AM, Niall O'Reilly  wrote:

> At Thu, 8 Jan 2015 13:46:37 +,
> Simon Slavin wrote:
> >
> >
> > On 8 Jan 2015, at 1:38pm, Niall O'Reilly  wrote:
> >
> > >  I'ld have expected the foreign_keys pragma setting to have been
> > >  preserved.
> >
> > That makes sense in terms of how a sensible user would expect SQLite
> > to behave.  But unfortunately it's not what SQLite does.  See
> > section 2 of
> >
> > 
> >
> > I think that the reason is that FOREIGN KEYs were developed a long
> > time after SQLite3.  A choice was made that they should default to
> > OFF to preserve backward compatibility.
>
>   I think that was the right choice for default behaviour.
>
>   What seems wrong to me is that the design doesn't provide for
>   persistence of an explicit change to the default mode, just as in
>   the case of the pragma which sets journal mode to WAL, or the other
>   one which sets page size.  I can't see why one would wish to have
>   foreign key support for some connections but not for others.
>
>   I expect that the work involved in having this pragma set persistent
>   state (whether in the database header or in a special internal
>   table) would require only modest effort and would be almost
>   perfectly safe.
>
>   I hope I may look forward to reading a reaction from the developers.
>
>
except appears pragma isn't persistent... (especially the key one)
and when restoring several systems disable foriegn keys during the restore
and then reapply it... so really the dump script having forieng-keys
disabled as the first thing is sensible... but then if the keys were
already on, would expect an on at the end...


>   Best regards,
>   Niall O'Reilly
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[Niall O'Reilly]

At Thu, 8 Jan 2015 13:46:37 +,
Simon Slavin wrote:
> 
> 
> On 8 Jan 2015, at 1:38pm, Niall O'Reilly  wrote:
> 
> >  I'ld have expected the foreign_keys pragma setting to have been
> >  preserved.
> 
> That makes sense in terms of how a sensible user would expect SQLite
> to behave.  But unfortunately it's not what SQLite does.  See
> section 2 of
> 
> 
> 
> I think that the reason is that FOREIGN KEYs were developed a long
> time after SQLite3.  A choice was made that they should default to
> OFF to preserve backward compatibility.

  I think that was the right choice for default behaviour.

  What seems wrong to me is that the design doesn't provide for
  persistence of an explicit change to the default mode, just as in
  the case of the pragma which sets journal mode to WAL, or the other
  one which sets page size.  I can't see why one would wish to have
  foreign key support for some connections but not for others.

  I expect that the work involved in having this pragma set persistent
  state (whether in the database header or in a special internal
  table) would require only modest effort and would be almost
  perfectly safe.

  I hope I may look forward to reading a reaction from the developers.

  Best regards,
  Niall O'Reilly
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should .dump preserve foreign_keys pragma?

[Simon Slavin]

On 8 Jan 2015, at 1:38pm, Niall O'Reilly  wrote:

>  I'ld have expected the foreign_keys pragma setting to have been
>  preserved.

That makes sense in terms of how a sensible user would expect SQLite to behave. 
 But unfortunately it's not what SQLite does.  See section 2 of



I think that the reason is that FOREIGN KEYs were developed a long time after 
SQLite3.  A choice was made that they should default to OFF to preserve 
backward compatibility.

So yes, as your experiment shows, you have to execute

PRAGMA foreign_keys = ON

each time you open a database connection if you want the foreign keys to do 
their stuff.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users