The attached patch implements the compound (multi-row) INSERT statement
syntax against the SQLite 3.5.1 source tree. Both named and unnamed
INSERT column syntax with DEFAULT column values are supported.
The patch transforms multi-row INSERT statements into compound SELECT
statements separated by UNION ALLs. It should be fairly efficient,
as all rows within the compound INSERT are inserted within a single
implicit transaction.
SQLITE_MAX_COMPOUND_SELECT in sqliteLimit.h also serves as the maximum
number of rows in a multi-row INSERT.
No regressions in "make test". Post any problems to the mailing list,
and I'll try to keep this patch up to date.
This patch is hereby placed in the public domain.
Examples:
SQLite version 3.5.1
Enter ".help" for instructions
sqlite> CREATE TABLE foo(a DEFAULT 123, b DEFAULT 'Bob');
sqlite> insert into foo values (1,'Ace'), (2,'Two'), (3,'Three');
sqlite> select * from foo;
1|Ace
2|Two
3|Three
sqlite> delete from foo;
sqlite> insert into foo(b,a) values (1,'Ace'), (2,'Two'), (3,'Three');
sqlite> select * from foo;
Ace|1
Two|2
Three|3
sqlite> delete from foo;
sqlite> insert into foo(a) values (10), (20), (30);
sqlite> select * from foo;
10|Bob
20|Bob
30|Bob
____________________________________________________________________________________
Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for
today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow Index: src/parse.y
===================================================================
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.234
diff -u -3 -p -r1.234 parse.y
--- src/parse.y 21 Aug 2007 10:44:16 -0000 1.234
+++ src/parse.y 14 Oct 2007 07:29:32 -0000
@@ -624,6 +624,42 @@ inscollist(A) ::= inscollist(X) COMMA nm
inscollist(A) ::= nm(Y).
{A = sqlite3IdListAppend(pParse->db,0,&Y);}
+%ifndef SQLITE_OMIT_COMPOUND_INSERT
+%ifndef SQLITE_OMIT_COMPOUND_SELECT
+
+%type inslistItem {Select*}
+%destructor inslistItem {sqlite3SelectDelete($$);}
+
+inslistItem(A) ::= LP itemlist(W) RP. {
+ SrcList *pSrc = sqlite3DbMallocZero(pParse->db, sizeof(SrcList));
+ A = sqlite3SelectNew(pParse,W,pSrc,0,0,0,0,0,0,0);
+}
+
+%type inslist {Select*}
+%destructor inslist {sqlite3SelectDelete($$);}
+
+%type comma_opt {int}
+comma_opt(X) ::= . {X = 0;}
+comma_opt(X) ::= COMMA. {X = 1;}
+
+inslist(A) ::= inslistItem(Z) COMMA. {A = Z;}
+inslist(A) ::= inslist(X) inslistItem(Z) comma_opt(C). {
+ C = C;
+ if( Z ){
+ Z->op = TK_ALL;
+ Z->pPrior = X;
+ }else{
+ sqlite3SelectDelete(X);
+ }
+ A = Z;
+}
+
+cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) VALUES inslist(S).
+ {sqlite3Insert(pParse, X, 0, S, F, R);}
+
+%endif SQLITE_OMIT_COMPOUND_SELECT
+%endif SQLITE_OMIT_COMPOUND_INSERT
+
/////////////////////////// Expression Processing /////////////////////////////
//
Index: src/sqliteLimit.h
===================================================================
RCS file: /sqlite/sqlite/src/sqliteLimit.h,v
retrieving revision 1.2
diff -u -3 -p -r1.2 sqliteLimit.h
--- src/sqliteLimit.h 24 Aug 2007 11:52:29 -0000 1.2
+++ src/sqliteLimit.h 14 Oct 2007 07:29:33 -0000
@@ -75,7 +75,7 @@
** any limit on the number of terms in a compount SELECT.
*/
#ifndef SQLITE_MAX_COMPOUND_SELECT
-# define SQLITE_MAX_COMPOUND_SELECT 500
+# define SQLITE_MAX_COMPOUND_SELECT 5000000
#endif
/*
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
