Re: [sqlite] Re: insert default values - supporting it?

[Mario Frasca]

just to show that it does not crash:

sqlite> create table test2 (k integer primary key autoincrement);
sqlite> insert into test2 default values;
sqlite> insert into test2 () values ();
sqlite> select * from test2;
1
2
sqlite>

Re: [sqlite] Re: insert default values - supporting it?

[Mario Frasca]

I refined the patch.  it constructs a list of values with one NULL, but 
I don't see how to construct an idList with just the primary key.


also added the grammar rule to recognize both:
insert into  default values;
insert into  () values ();

anybody completing/correcting the work? 


sqlite> insert into test () values ();
SQL error: table test has 3 columns but 1 values were supplied

MF.


cvs diff: Diffing src
Index: src/insert.c
===
RCS file: /sqlite/sqlite/src/insert.c,v
retrieving revision 1.170
diff -u -r1.170 insert.c
--- src/insert.c	19 Jun 2006 03:05:10 -	1.170
+++ src/insert.c	1 Aug 2006 08:26:28 -
@@ -123,6 +123,7 @@
 **
 **insert into TABLE (IDLIST) values(EXPRLIST)
 **insert into TABLE (IDLIST) select
+**insert into TABLE default values
 **
 ** The IDLIST following the table name is always optional.  If omitted,
 ** then a list of all columns for the table is substituted.  The IDLIST
@@ -380,7 +381,22 @@
 }else{
   sqlite3VdbeJumpHere(v, iInitCode);
 }
-  }else{
+  }else if (pList == 0){
+assert( pColumn == 0 );
+/* This is the case if no data has been supplied and DEFAULT VALUES are
+** to be inserted.  a minimal impact approach would be to create here a
+** temporary list of columns containing just the primary key and a
+** temporary list of values containing just a NULL.  the rest of the
+** function would remain untouched.
+*/
+Expr *A;
+Token the_null = { (u8*)"NULL", 0, 4 };
+
+A = sqlite3Expr(TK_NULL, 0, 0, _null);
+pList = sqlite3ExprListAppend(0,A,0);
+nColumn = 1;
+
+  } else{
 /* This is the case if the data for the INSERT is coming from a VALUES
 ** clause
 */
Index: src/parse.y
===
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.206
diff -u -r1.206 parse.y
--- src/parse.y	11 Jul 2006 10:42:36 -	1.206
+++ src/parse.y	1 Aug 2006 08:26:28 -
@@ -598,6 +598,10 @@
 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) 
 VALUES LP itemlist(Y) RP.
 {sqlite3Insert(pParse, X, Y, 0, F, R);}
+cmd ::= insert_cmd(R) INTO fullname(X) DEFAULT VALUES.
+{ sqlite3Insert(pParse, X, 0, 0, 0, R);}
+cmd ::= insert_cmd(R) INTO fullname(X) LP RP VALUES LP RP.
+{ sqlite3Insert(pParse, X, 0, 0, 0, R);}
 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
 {sqlite3Insert(pParse, X, 0, S, F, R);}
 
cvs diff: Diffing src/ex

[sqlite] Re: insert default values - supporting it?

[Mario Frasca]

I'm throwing this here, I assume that it would not be too much work to 
complete this patch. the aim is to support the sql92 syntax insert into 
 default values;


any comments? hints?

thanks in advance,
Mario Frasca.

cvs diff: Diffing src
Index: src/insert.c
===
RCS file: /sqlite/sqlite/src/insert.c,v
retrieving revision 1.170
diff -u -r1.170 insert.c
--- src/insert.c19 Jun 2006 03:05:10 -  1.170
+++ src/insert.c31 Jul 2006 13:04:19 -
@@ -123,6 +123,7 @@
**
**insert into TABLE (IDLIST) values(EXPRLIST)
**insert into TABLE (IDLIST) select
+**insert into TABLE default values
**
** The IDLIST following the table name is always optional.  If omitted,
** then a list of all columns for the table is substituted.  The IDLIST
@@ -380,7 +381,16 @@
}else{
  sqlite3VdbeJumpHere(v, iInitCode);
}
-  }else{
+  }else if (pList == 0){
+assert( pColumns == 0 );
+/* This is the case if no data has been supplied and DEFAULT VALUES are
+** to be inserted.  a minimal impact approach would be to create here a
+** temporary list of columns containing just the primary key and a
+** temporary list of values containing just a NULL.  the rest of the
+** function would remain untouched.
+*/
+nColumn = 0;
+  } else{
/* This is the case if the data for the INSERT is coming from a VALUES
** clause
*/
Index: src/parse.y
===
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.206
diff -u -r1.206 parse.y
--- src/parse.y 11 Jul 2006 10:42:36 -  1.206
+++ src/parse.y 31 Jul 2006 13:04:19 -
@@ -598,6 +598,8 @@
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) 
VALUES LP itemlist(Y) RP.

{sqlite3Insert(pParse, X, Y, 0, F, R);}
+cmd ::= insert_cmd(R) INTO fullname(X) DEFAULT VALUES.
+{sqlite3Insert(pParse, X, 0, 0, 0, R);}
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
{sqlite3Insert(pParse, X, 0, S, F, R);}