On Tue, Aug 18, 2009 at 09:42:53AM -0500, Beau Wilkinson scratched on the wall: > >On Tue, Aug 18, 2009 at 08:28:13AM -0500, Beau Wilkinson scratched on the > >wall:
> > It is also worth noting that every non-SQL Relational language (e.g. > > Tutorial-D) I've looked at supports some form of multiple inserts, usually > > into multiple tables, with one command. If you're a stickler for > > constraints, and believe enforcement shouldn't be delayed (as most > > theory-heavy folks do), you have to have something like this. > Doesn't what you said about constraints imply that the individual > sub-INSERTs must be treated as if they all arrive at the database > at once, e.g. for purposes of constraint checking? This will be a > detail to consider if this gets implemented... The most common area where INSERTs start to trip over constraints is when you have a heavily entwined nest of foreign key constraints. SQLite doesn't support key constraints nativity, and the trigger-based workaround can't deal with constraint chains. So the problem is actually greatly reduced in SQLite, but only because the constraint checking isn't there at all. Normally constraints are only enforced when a transaction is committed. This is how one typically gets around any constraint problems. Since "pure" SQL doesn't allow multiple INSERTs in a single statement, if you have a constraint situation that requires several INSERTs to add a valid set of records, you would wrap all the INSERT statements into a single transaction. Since, in theory, the transaction isn't visible to other database users, everything is good-- assuming you can keep track of your own changes. The theory people are uncomfortable with this, however. You really have to grok constraints to their fullest to appreciate the depth of this, but the basic idea is that if any constraint is EVER violated, all bets are off about the consistency and "correctness" of the whole database. That sounds a bit excessive, but one must remember that the Relational Model is a formal mathematical system, so allowing temporary violation of constraints is similar to temporarily allowing zero plus one to equal something other than one while you're in the middle of a complex calculation. That's a different discussion for a different day, however. The SQL model allows violations within an open constraint, under the assumption that the data isn't "really" part of the database until it is committed, at which point the data is checked to be sure it is valid. As long as you understand the ramifications of that, it seems like a workable model to me. What all that means for this conversation is that a multi-valued INSERT issued outside outside of a transaction (i.e. in auto-commit mode) would play out the exact same as opening a transaction, applying several discrete INSERT statements, and closing the transaction. So the code modifications should be minimal, since every command is issuing an implied BEGIN/END anyways. Similarly, if we're already inside a transaction, the INSERT should just play out as if it was several discrete INSERT statements. In both cases all of the sub-INSERTs "arrive at the database" at the same time, since the SQL definition of "arrive at the database" is when the transaction is committed. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
