Re: [sqlite] INSERT with multiple VALUES clause?

[Kit]

2009/8/18 Terrence Brannon :
> Hello, when converting a .sql file containing MySQL INSERT clauses,
> one often runs into the problem that the MySQL INSERT can accept
> multiple VALUES arguments:
>
>INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15
> 04:34:33'),(2,'NICK','WAHLBERG','2006-02-15
> 04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33')
>
>  whereas the SQLite INSERT can only take one...
>
> Is there any possibility of extending the syntax of the SQLite insert
> to accept multiple VALUES arguments?

INSERT INTO actor
SELECT 1,'PENELOPE','GUINESS','2006-02-15 04:34:33'
UNION ALL
SELECT 2,'NICK','WAHLBERG','2006-02-15 04:34:33'
UNION ALL
SELECT 3,'ED','CHASE','2006-02-15 04:34:33';
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT with multiple VALUES clause?

[Jay A. Kreibich]

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

Re: [sqlite] INSERT with multiple VALUES clause?

[John Machin]

On 18/08/2009 11:28 PM, Beau Wilkinson wrote:
> That said, if you're in posession of the source code,
 > you can certainly hack something up to support that.
 > A better option might be to pre-process the MySQL file
 > using C, Perl, XSLT (just kidding - don't use XSLT)
 > or whatever else you prefer for this kind of rote file manipulation

"rote" is relative; it's certainly just a flick of the wrist if you're 
willing to bet on there being no ')' characters in the text literals, 
otherwise it gets a bit hairy...

Here's an attempt at something fairly general using Python regular 
expressions; just point this at the remainder of the statement after the 
  VALUES keyword:

import re
value_literal = r"""
 (?:
 ' (?: [^'] | '' ) * ' # text literal
 |
 [^,)\s] + # any other literal
 )
 """
value_list_re = r"\(\s*LIT\s*(?:,\s*LIT\s*)*\)".replace("LIT", 
value_literal)
data = """
 (1,'PENELOPE','GUINESS','2006-02-15 04:34:33'), (2,'NICK','WAHLBERG',
 '2006-02-15 04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33'),
 (4,'Seamus', 'O''Reilly'),(4.1,x'f00baa'),(5678),
 (6, 'William','Pitt (the Elder)'),(6.1, 'Willie', 'Pitt (the 
Younger)'),
 (  7  , 'spaced'  ,  'out'  )
 """
rx = re.compile(value_list_re, re.VERBOSE)
for vlist in rx.findall(data):
 print vlist

and here's the output:
(1,'PENELOPE','GUINESS','2006-02-15 04:34:33')
(2,'NICK','WAHLBERG',
 '2006-02-15 04:34:33')
(3,'ED','CHASE','2006-02-15 04:34:33')
(4,'Seamus', 'O''Reilly')
(4.1,x'f00baa')
(5678)
(6, 'William','Pitt (the Elder)')
(6.1, 'Willie', 'Pitt (the Younger)')
(  7  , 'spaced'  ,  'out'  )

Cheers,
John


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

Re: [sqlite] INSERT with multiple VALUES clause?

[Beau Wilkinson]

>On Tue, Aug 18, 2009 at 08:28:13AM -0500, Beau Wilkinson scratched on the wall:
>> To me that seems like an annoying deviation from standard practice.
>> Do other databases support such an INSERT?
>
>Yes.  MySQL, PostgreSQL, and SQLServer all support this syntax.
>
>Oracle supports a slightly modified syntax that allows multple rows
>to be inserted into multiple tables with one INSERT ALL.  The syntax
>for that is basically INSERT ALL INTO... INTO... INTO...
>
> DB2 and HSQL (and SQLite) do not support this.
>
> Personally, I think the first syntax would be a nice enhancement.
>
> 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.

I definitely see what you mean about constraints; I guess when I was doing 
heavy (Oracle) database programming I must have been sidestepping the issue by 
turning things constraints off selectively, or maybe just inserting things in a 
certain order.

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 information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT with multiple VALUES clause?

[Jay A. Kreibich]

On Tue, Aug 18, 2009 at 08:28:13AM -0500, Beau Wilkinson scratched on the wall:
> To me that seems like an annoying deviation from standard practice.
> Do other databases support such an INSERT? 

  Yes.  MySQL, PostgreSQL, and SQLServer all support this syntax.

  Oracle supports a slightly modified syntax that allows multple rows
  to be inserted into multiple tables with one INSERT ALL.  The syntax
  for that is basically INSERT ALL INTO... INTO... INTO...

  DB2 and HSQL (and SQLite) do not support this.

  Personally, I think the first syntax would be a nice enhancement.



  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.

   -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

Re: [sqlite] INSERT with multiple VALUES clause?

[Beau Wilkinson]

To me that seems like an annoying deviation from standard practice. Do other 
databases support such an INSERT? Is it envisioned by standards? I suspect the 
answer is "no" in both cases, and this is a classic example of how "less" 
functionality is actually "more" useful.

That said, if you're in posession of the source code, you can certainly hack 
something up to support that. A better option might be to pre-process the MySQL 
file using C, Perl, XSLT (just kidding - don't use XSLT) or whatever else you 
prefer for this kind of rote file manipulation


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Terrence Brannon [scheme...@gmail.com]
Sent: Tuesday, August 18, 2009 7:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] INSERT with multiple VALUES clause?

Hello, when converting a .sql file containing MySQL INSERT clauses,
one often runs into the problem that the MySQL INSERT can accept
multiple VALUES arguments:

INSERT INTO actor VALUES (1,'PENELOPE','GUINESS','2006-02-15
04:34:33'),(2,'NICK','WAHLBERG','2006-02-15
04:34:33'),(3,'ED','CHASE','2006-02-15 04:34:33')


 whereas the SQLite INSERT can only take one...

Is there any possibility of extending the syntax of the SQLite insert
to accept multiple VALUES arguments?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users