[sqlite] insert default values - implemented
Mario Frasca wrote:
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;
all right, a possible complete patch, maybe could be added to the
rest... don't tell me that it looks ugly, I totally agree, but it
behaves as described... :)
regards,
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 11:56:15 -
@@ -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,14 +381,36 @@
}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. this is a minimalistic impact approach... a fake
+** list of columns containing just the primary key and a fake list of
+** values containing just a NULL are created, as if the user had issued
+** the SQL command: "insert into () values (NULL)". the
+** rest of the function remains untouched. error messages are issued if
+** the equivalent command causes them.
+*/
+Expr *A;
+Token the_null = { (u8*)"NULL", 0, 4 };
+
+A = sqlite3Expr(TK_NULL, 0, 0, _null);
+pList = sqlite3ExprListAppend(0,A,0);
+nColumn = 1; /* same as pList->nExpr */
+
+Token the_pkey = { 0, 0, 0 };
+the_pkey.z = pTab->aCol[pTab->iPKey].zName;
+the_pkey.n = strlen(pTab->aCol[pTab->iPKey].zName);
+
+pColumn = sqlite3IdListAppend(0, _pkey);
+
+ } else{
/* This is the case if the data for the INSERT is coming from a VALUES
** clause
*/
NameContext sNC;
memset(, 0, sizeof(sNC));
sNC.pParse = pParse;
-assert( pList!=0 );
srcTab = -1;
useTempTable = 0;
assert( pList );
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 11:56:16 -
@@ -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
Re: [sqlite] insert default values
On 2006-0728 16:47:21, Nemanja Corlija wrote: > You can get that with this query: > select seq from sqlite_sequence where name='test' [...] > > There is also a last_insert_rowid() [...] > sqlite_sequence is really the way to go. very useful comments from everybody, thanks! Mario -- Power corrupts. Absolute power is kind of neat.
Re: [sqlite] insert default values
On 7/28/06, Mario Frasca <[EMAIL PROTECTED]> wrote: On 2006-0728 16:07:47, Nemanja Corlija wrote: > You can insert default value like this: > insert into test (f) values (NULL); > > Inserting NULL into autoincrement field just increments it. While > omitting value for any other field uses default for that field, if one > is defined. next question: is there a way to ask which was the last (autoincremented) value inserted in the table? or is there a guarantee that this works, as it seems... select max(f) from test; You can get that with this query: select seq from sqlite_sequence where name='test' 'test' is the name of your table and "seq" field keeps the last/highest value that was inserted in AUTOINCREMENT field. This will only work if you define your table to use true AUTOINCREMENT field, like we did in this example. If you use just INTEGER PRIMARY KEY, without AUTOINCREMENT, that table doesn't have a record in sqlite_sequence table. For the later case "select max(f) from test;" would work. Though that's not what we have here. There is also a last_insert_rowid() function that is like an alias for sqlite_last_insert_rowid() API function and it works per db connection. I don't think this this is very useful with true AUTOINCREMENT fields, so sqlite_sequence is really the way to go. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] insert default values
Hi Gerry, yes, your help was quite useful... now we have two problems here, I would say: the first one is that, of all the things you have tried, only one is correct but two more are accepted without causing an error. On 2006-0728 06:55:22, Gerry Snyder wrote: > sqlite> create table test(f int auto_increment primary key, v int > default 0); -- no error, not working (I would expect either a syntax error or a 'auto_increment only on integer') > -- > sqlite> create table test(f integer auto_increment primary key, v int > default 0); -- no error, not working (I would expect either a syntax error or complete equivalence with the working version) > -- > sqlite> create table test(f integer primary key auto_increment, v int > default 0); > SQL error: near "auto_increment": syntax error > > sqlite> create table test(f int autoincrement primary key, v int default 0); > SQL error: near "autoincrement": syntax error > - > sqlite> create table test(f int primary key autoincrement, v int default 0); > SQL error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY > -- > sqlite> create table test(f integer primary key autoincrement, v int > default 0); -- all right, this is the only working one, thanks a lot! the next problem is that I still would like to insert into test () values (); or maybe insert into test default values; here sqlite does respond clearly: sqlite> insert into test () values (); SQL error: near ")": syntax error sqlite> insert into test default values; SQL error: near "default": syntax error is it possible to do this in sqlite? Mario -- Gotta run, my government's collapsing.
Re: [sqlite] insert default values
On 7/28/06, Gerry Snyder <[EMAIL PROTECTED]> wrote: Finally, get it right: sqlite> create table test(f integer primary key autoincrement, v int default 0); sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (2); sqlite> insert into test (v) values (NULL); sqlite> select * from test; 1|1 2|2 3| Not sure why the last row is not 3|0. Because NULL is allowed for field in question. You can insert default value like this: insert into test (f) values (NULL); Inserting NULL into autoincrement field just increments it. While omitting value for any other field uses default for that field, if one is defined. -- Nemanja Corlija <[EMAIL PROTECTED]>
Re: [sqlite] insert default values
Mario Frasca wrote: and how do I insert a 'all-default' record? After getting everything else right (see my previous post): sqlite> insert into test (f) values (NULL); sqlite> select * from test; 1|1 2|2 3| 4|0 And this answers my previous comment, too. Inserting a NULL into v overrides the default. Gerry
Re: [sqlite] insert default values
Gerry Snyder uttered: Finally, get it right: sqlite> create table test(f integer primary key autoincrement, v int default 0); sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (2); sqlite> insert into test (v) values (NULL); sqlite> select * from test; 1|1 2|2 3| Not sure why the last row is not 3|0. Because you've explicitly inserted a NULL. The default value is only used if the column is not explicitly specified in the insert. HTH, Gerry -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] insert default values
Mario Frasca wrote: I'm trying to use default values and autoincrementing primary keys. [EMAIL PROTECTED]:~$ sqlite3 /data/mariof/test.db_scia.db SQLite version 3.3.4 Enter ".help" for instructions sqlite> create table test(f int auto_increment primary key, v int default 0); The above is not correct syntax for what you want. sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (2); sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (NULL); sqlite> select * from test; |1 |2 |1 | First, recreating your example: $ sqlite3 SQLite version 3.3.6 Enter ".help" for instructions sqlite> create table test(f int auto_increment primary key, v int default 0); sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (2); sqlite> insert into test (v) values (NULL); sqlite> select * from test; |1 |2 | -- Next, showing that f is really not what you want: sqlite> select oid,f,v from test; 1||1 2||2 3|| sqlite> drop table test; -- Next, correcting int to integer (no change.. yet) sqlite> create table test(f integer auto_increment primary key, v int default 0) ; sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (2); sqlite> insert into test (v) values (NULL); sqlite> select * from test; |1 |2 | sqlite> drop table test; -- Now, correcting the order: sqlite> create table test(f integer primary key auto_increment, v int default 0); SQL error: near "auto_increment": syntax error Now correct the spelling of autoincrement: sqlite> create table test(f int autoincrement primary key, v int default 0); SQL error: near "autoincrement": syntax error - Now correct the order again: sqlite> create table test(f int primary key autoincrement, v int default 0); SQL error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY -- Finally, get it right: sqlite> create table test(f integer primary key autoincrement, v int default 0); sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (2); sqlite> insert into test (v) values (NULL); sqlite> select * from test; 1|1 2|2 3| Not sure why the last row is not 3|0. HTH, Gerry
[sqlite] insert default values
I'm trying to use default values and autoincrementing primary keys. [EMAIL PROTECTED]:~$ sqlite3 /data/mariof/test.db_scia.db SQLite version 3.3.4 Enter ".help" for instructions sqlite> create table test(f int auto_increment primary key, v int default 0); sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (2); sqlite> insert into test (v) values (1); sqlite> insert into test (v) values (NULL); sqlite> select * from test; |1 |2 |1 | I'm not a great fan of the auto_increment feature, I like sequences a lot better, but as sqlite recognizes it, does it also implement it? the documentation states "the requested feature was added in 3.1", I'm testing with 3.3.4... and how do I insert a 'all-default' record? sqlite> insert into test default values; SQL error: near "default": syntax error sqlite> insert into test () values (); SQL error: near ")": syntax error actually, missing auto_increment and sequences, inserting an 'all default' record is just a style exercise, not really particularly useful... thanks, Mario Frasca -- Die Welt wird nicht bedroht von den Menschen, die böse sind, sondern von denen, die das Böse zulassen -- Albert Einstein
