Re: [sqlite] When to release version 3.3.10?
I've read this article more than once and I still find it inspiring... http://catb.org/~esr/writings/cathedral-bazaar/cathedral-bazaar/ar01s04.html I'd say, you have the fix, just release it, why not? if you're waiting for a next bug, you'll never have the guarantee that "tomorrow" no bug will be found which can be fixed within a limited amount of hours... [EMAIL PROTECTED] wrote: The question is: should I rush out 3.3.10 to cover this important bug fix, wait a week to see if any other bugs surface, or do the usual 1-2 month release schedule and let people effected by this bug apply the patch above. thanks for all your work! Mario. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
Christian Smith wrote: You can use the rowid to track the row version, and a unique constraint on your key to track conflicts. When updating a record, read the row, including the rowid. Update any columns. When writing the row out, delete the existing rowid, then insert the new updated row, all within a transaction. If noone has updated the row since you read it, the delete should delete one row and the insert should succeed. If someone else has updated the row using this protocol, the delete should delete no rows, and the insert should fail with a unique constraint violation on your key. maybe I'm missing something, but how does this behave if the row is updated a second time in the meanwhile (something like C acting as B between A-read and A-write)? I've the impression that the insert will not fail... just a doubt... regards, MF - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
hongdong wrote: I just have a base question: assume user A and user B now both connection to a same database and both of them want to update a same record,but only one is allowed in this condition: A begin to browse the data in a client application,and load rowid into GUI and keep it in memory. B update the record which A want to update A now used the id stored in memory to update the same record now it's wrong. anyone can give me advice how to avoid this and keep the operation is effient thanks! this sounds like shared and exclusive locks. A wants a shared lock and possibly upgrade it. B wants an exclusive lock. when A starts first: once A gets its shared lock, B cannot receive an exclusive lock and goes in standby. A upgrades its lock to an exclusive lock (there are reasons not to start asking for a shared lock if you know from the beginning that you're going to need an exclusive lock). A releases the lock. B finally gets in. notice that if both A and B start asking a shared lock, you'll get in trouble... someone must be allowed to break locks and oblige clients to rollback. can't point you to english literature, but I'm sure Wikipedia contains useful hints. ciao, MF - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query generation
Lloyd wrote:
select a from mytable where (b=0) and
and this is a syntax error.
you're not saying which language you're using.
in Python a common solution looks like this:
clauses = []
# add strings to the clauses list, like
clauses.append('(b=0)')
# join the parts using appropriate glue
where = ' AND '.join(clauses)
sometimes I do need something like what you call "an empty clause"...
some engines accept 'TRUE', others don't know that literal.
I find this quite convenient:
'0=0'
hth...
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] autoincrement and integer primary key
[EMAIL PROTECTED] wrote:
In the FAQ's on the web site it indicated that when the primary key is
autoincrement, the data type is a signed 64 bit number. Has this been your
experience?
never noticed... not in Python with the sqlite modules I have... and
no difference whether I use the 'autoincrement' keyword or not, as long
as we're talking about 'integer primary key' / oid...
Python 2.3.5 (#2, Oct 16 2006, 19:19:48)
{also tried with no difference:
Python 2.4.1 (#2, Oct 18 2006, 20:58:01)
}
[GCC 3.3.5 (Debian 1:3.3.5-13)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from pysqlite2 import dbapi2 as sqlite
>>> db = sqlite.connect(':memory:')
>>> cr = db.cursor()
>>> cr.execute('create table test(pk integer primary key autoincrement,
v string)')
>>> cr.execute("insert into test (v) values ('test')")
>>> cr.execute("select * from test")
>>> cr.fetchone()
(1, u'test')
>>> cr.execute("select * from test")
>>> [type(i) for i in cr.fetchone()]
[, ]
>>> type(1L)
>>>
Python 2.4.4 (#2, Oct 20 2006, 00:57:46)
[GCC 4.1.2 20061007 (prerelease) (Debian 4.1.1-16)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite
>>> db = sqlite.connect(':memory:')
>>> cr = db.cursor()
>>> cr.execute('create table test(pk integer primary key, v string)')
>>> cr.execute("insert into test (v) values ('test')")
>>> cr.execute("select * from test")
>>> cr.fetchone()
(1, 'test')
>>> cr.execute("select * from test")
>>> [type(i) for i in cr.fetchone()]
[, ]
>>>
in short: no, it's not my experience.
MF
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] index in CREATE TABLE
Cécilia Vigny wrote: With MySQL, it's possible to declare an index in CREATE TABLE, using KEY, like in this example : CREATE TABLE table_name ( id int(6) NOT NULL auto_increment, field_name date default NULL, PRIMARY KEY (id), KEY idx_field_name (fieldname), ) ; I want to know if there is an equivalence with SQLite ? maybe this is what you want: CREATE TABLE table_name ( id integer PRIMARY KEY NOT NULL, field_name date default NULL ) ; CREATE INDEX idx_field_name ON table_name(field_name); also: have a look at http://www.sqlite.org/lang.html (SQL As Understood By SQLite) (editing your code you forgot an underscore and left an extra comma) regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] autoincrement and integer primary key
just a few explainatory notes from me to myself, hoping that they might be useful for the community... again about integer primary key autoincrement, with some details about last_insert_rowid() and sqlite_sequence. --- each table has an integer primary key, that you explictly ask for it or not. this field is called "oid" and possibly also in other ways... if you explicitly declare a field "integer primary key" (literally!), the integer primary key (which would be created anyways) becomes also visible under the name you chose for the field. the function last_insert_rowid() returns the value of the integer primary key of the last inserted record. (the oid, which may coincide with your integer primary key). if you declare a field "integer primary key autoincrement" (literally, non case sensitive, in this order!), some sort of sequence is created to insure that no value for primary key is ever reused. real sequences do not exist in sqlite3, so the reserved table sqlite_sequence is used to associate a table name to the highest value already used. the record relative to the table is created when the first record is inserted into the table. when you insert a record into a table and leave the task of choosing the oid to sqlite, sqlite will look into the table to get the highest oid currently in use (or into the sqlite_sequence table if you used "autoincrement"), increment it by one and use this value (and possibly update the sqlite_sequence table). just to make a silly example: sqlite> CREATE TABLE test( ...> f INTEGER PRIMARY KEY AUTOINCREMENT, ...> v int default 0); sqlite> INSERT INTO "test" VALUES(2, 0); sqlite> INSERT INTO "test" VALUES(1, 1); sqlite> select last_insert_rowid(); 1 sqlite> SELECT seq FROM sqlite_sequence WHERE name='test'; 2 sqlite> --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] using auto increment in java
On 11/11/2006 10:16 PM, Will Leshner wrote: You should be able to get the last inserted rowid using the last_insert_rowid() function in a query: SELECT last_insert_rowid(); sorry for the slow reaction, but I had to check my own code first... On Fri, 28 Jul 2006 16:47:21 +0200, Nemanja Corlija <[EMAIL PROTECTED]> wrote: 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. based that, I included this in my library... elif engine == 'sqlite': query2 = "SELECT seq FROM sqlite_sequence WHERE name='%s'" % self._table any clarifying comments? thanks a lot, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] LEFT OUTER JOIN + INNER JOIN problem
Gábor Farkas wrote: i think it should only output the first row ( "5|admin|1|5|1|john" ) tried what you describe and get the answer you expect, not the one you got: sqlite> select * from role ...> LEFT OUTER JOIN person_role on role.id = person_role.role_id ...> INNER JOIN person on person_role.person_id = person.id ...> WHERE person.id=1; 5|admin|1|5|1|john (using ubuntu edgy, sqlite3 3.3.5) using debian 3.1, sqlite3 3.3.8, the same also on Mac OS X, sqlite3 3.3.7... ciao, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] strange behavior of "<" and ">" operator
Andrea Federico Grisotto wrote:
*fromId* and *toId* are VARCHAR, if I use *int* the behavior is correct,
excuse me again, but remain the difference behavior between sqlite
and Mysql.
just food for thought: when you're asking an ambiguous question (like in
the case of comparing apples with potatoes) the answer is a question of
interpretation.
since you have declared fromId and toId to be VARCHAR, it is correct to
compare them with other VARCHAR values, like here...
SELECT * from np WHERE ('42'>=fromId) AND ('42'<=toId);
in this case you are clearing stating what you want and you get the same
answer from both engines.
in the expression (42 >= fromId) mysql converts fromId to integer and
performs an integer comparison, while sqlite3 converts the 42 to string
and performs a string comparison. as said, a question of interpretation.
hth,
MF
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Database on usbstick
Sergio 'OKreZ' Agosti wrote: On 05/set/06, at 15:03, eWobbuh wrote: I still cant find it. Cant find anything about where to look for a database. [...] The name of the database passed to the open function actually is the path to the database file yes, where maybe we could add that sqlite has this big difference from (say) mysql, that is there is no server process running anywhere: the client attaches to a file containing the whole database. but why add it here if it is already written in the online documentation? just follow the link 'distinctive features'. did you try the suggestion in http://www.sqlite.org/quickstart.html: "Create A New Database"? as said: have fun, trying... Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database on usbstick
eWobbuh wrote: Havent try it yet, just wondering if its possible. Do you know how you tell sqlite where to find a database? havent worked before with it.. only with mysql http://www.sqlite.org contains a link to 'documentation'. the very impatient reader will follow 'sqlite in 5 minutes or less' there (s)he will find the answer to the very first questions. maybe (s)he wants to follow the link to 'additional documentation', something like 'life with sqlite from minute 6 to 20'... have fun, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database on usbstick
eWobbuh wrote: here a sqlite noob. Is it possible in linux to have a sqlite database on a usb stick which i can acces from an hard disk? On the hard disk runs linux with sqlite. have you already tried? I don't think there should be any problems, except possibly regarding the performance... when you have 'mounted' the usb stick, I don't think that sqlite3 can tell the difference between a file on the usb stick, one on your local hard disk, or wherever else... M - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] problem with auto increment of ROWID
Dixon Hutchinson wrote:
H:\b>sqlite3.exe t.dat
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> CREATE TABLE abc
...> (
...> c TEXT,
...> p INTEGER,
...> t TEXT,
...> masked INTEGER PRIMARY KEY,
...> UNIQUE(p,c)
...> );
[...]
Notice I still have elements 1,2 and 3 in the end where I want to have
elements 1, 2 and 4.
which is the reason why sqlite has autoincrement...
[EMAIL PROTECTED]:~$ /usr/bin/sqlite3
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> CREATE TABLE abc ( c TEXT,p INTEGER, t TEXT, masked INTEGER
PRIMARY KEY AUTOINCREMENT, UNIQUE(p,c));
sqlite> INSERT INTO abc(c,p,t) VALUES('t1', 24, 't2');
sqlite> INSERT INTO abc(c,p,t) VALUES('t3', 25, 't4');
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't6');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t6|3
sqlite> DELETE FROM abc WHERE ROWID='3';
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t8|4
sqlite>
works also if you write "rowid" instead of "masked"
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Performance Question
On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote: To my surprise (perhaps "horror") I find that SQLite has for a very long time allowed NULL values in PRIMARY KEY columns. [...] I understand your concern about legacy programs, but most of us expect PRIMARY KEY to imply NOT NULL... don't we? what about looking for alternative good solutions? we could put the correction code in the source, conditionally compiled (not the default) and with the next major release reverse the condition (new 'corrected' source becomes default and old 'legacy' behaviour still available if desired) ... ? maybe 'the best of both worlds', hope you agree. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Mario Frasca wrote: Kurt Welgehausen wrote: [...] should I write a bug ticket about a primary key accepting nulls? there is already a ticket for that: 518. I reopened it three days ago. I have right now attached a patch for it. it is quite small and I hope it fits in the current style. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Kurt Welgehausen wrote: [EMAIL PROTECTED] wrote: Saying NOT NULL on a PRIMARY KEY is redundant, by the way. [...] Am I missing something, or should I write a bug ticket about a primary key accepting nulls? there is already a ticket for that: 518. I reopened it three days ago. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] duplicate primary key
I'm recording this behaviour: sqlite> create table test(f integer primary key autoincrement, v integer default 0); sqlite> insert into test(f) values(NULL); sqlite> insert into test(f) values(NULL); sqlite> insert into test(f) values(NULL); sqlite> select * from test; 1|0 2|0 3|0 this is very nice... sqlite> create table test(f integer primary key, v integer default 0); sqlite> insert into test(f) values(NULL); sqlite> insert into test(f) values(NULL); sqlite> insert into test(f) values(NULL); sqlite> select * from test; 1|0 2|0 3|0 this is a bit surprising; where does the autoincrement-like behaviour come from? without an explicit autoincrement definition, I would expect something like 'ERROR: null value in column "f" violates not-null constraint' (for all inserts) or at least 'SQL error: PRIMARY KEY must be unique' (for all inserts after the first), depending on whether we want to allow NULL primary keys or not. sqlite> create table test(f char(24) primary key, v integer default 0); sqlite> insert into test(f) values(NULL); sqlite> insert into test(f) values(NULL); sqlite> insert into test(f) values(NULL); sqlite> select * from test; |0 |0 |0 what is going on here? I'm having three records with the same NULL primary key. regards, MF - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seems like a bug in the parser
Joe Wilson wrote: --- [EMAIL PROTECTED] wrote: SQLite accepts the above and does the right thing with it. It is the equivalent of saying: SELECT a FROM (SELECT a,b FROM qqq GROUP BY b); Not sure what you mean by the "right thing". It's not obvious why the rows returned by this GROUP BY are significant. sqlite> select a,b from qqq group by b; 2|9 3|10 -3|11 mysql> select a,b from qqq group by b; +--+--+ | a| b| +--+--+ | 4|9 | | 1| 10 | | 4| 11 | +--+--+ 3 rows in set (0.00 sec) postgresql says: ERROR: column "qqq.a" must appear in the GROUP BY clause or be used in an aggregate function oracle says: ORA-00979: non è un'espressione GROUP BY (in translation: this is not a GROUP BY expression) as you see, the two engines returning a result give different results on the same data inserted in the same order. as Joe, I also don't see which should be considered "the" right thing and would rather have an error message. what does SQL92 say? regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] primary key information
Nemanja Corlija wrote: I think in any other case unique index would be created as expected. sqlite> drop table test; sqlite> create table test (ni integer, pk_name varchar(32) primary key, info integer); sqlite> pragma index_list(test); 0|sqlite_autoindex_test_1|1 sqlite> pragma table_info(test); 0|ni|integer|0||0 1|pk_name|varchar(32)|0||1 2|info|integer|0||0 sqlite> that seems to be correct... interesting, thanks, Mario
Re: [sqlite] primary key information
Nemanja Corlija wrote:
If you turn the headers on (.header ON) you'll see that the last
column of table_info() output is "pk".
This column indicates weather or not the table column in question is
part of primary key.
Though that last column of table_info() pragma is missing from
documentation at
http://www.sqlite.org/pragma.html#schema
very nice, thanks:
elif engine == 'sqlite':
cr.execute("pragma table_info(%s)"
% tablename)
pkeys = [fielddef[1] for fielddef in cr.fetchall() if
fielddef[5] ==1]
pass
I was a bit wondering: does the declaration 'primary key' actually
produce an indexing? or does that happen only if I explicitly ask for a
(unique) index?
Mario
[sqlite] primary key information
hallo, list... how do I get primary key information about a table? [EMAIL PROTECTED]:~$ sqlite3 /data/mariof/test.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> drop table test; sqlite> create table test (pk integer primary key, name varchar(32), info integer); sqlite> create unique index itest on test (name); sqlite> pragma index_list(test); 0|itest|1 sqlite> pragma table_info(test); 0|pk|integer|0||1 1|name|varchar(32)|0||0 2|info|integer|0||0 sqlite> pragma foreign_key_list(test); sqlite> I would have expected to see something like this: sqlite> pragma index_list(test); 0|test_pkey|1 1|itest|1 that is, that primary keys be treated as unique indices with a standard naming pattern. what is the deal? thanks, Mario
Re: [sqlite] date data types
Kees Nuyt wrote:
Will
PRAGMA table_info(tablename);
do?
>>> from pysqlite2 import dbapi2
>>> db = dbapi2.connect('/data/mariof/test.db')
>>> cr = db.cursor()
>>> cr.execute('pragma table_info(test3)')
>>> cr.fetchall()
[(0, u'd', u'date', 0, None, 0),
(1, u'h', u'time', 0, None, 0),
(2, u't', u'timestamp', 0, None, 0),
(3, u'i', u'interval', 0, None, 0)]
so yes, it does.
many thanks.
but I still don't understand... casting a value to the type of the
declaration sounds to me quite in conflict with the dynamic typing of
sqlite. on the other hand I don't see in which way adding these three
types (time being assimilated in interval) would break the design. just
wondering.
Mario
Re: [sqlite] date data types
[EMAIL PROTECTED] wrote: Adding DATE and TIMEINTERVAL types to SQLite would require an incompatible file format change. well, yes, that was already clear. but: where is the type of the data being stored? aren't there a few spare bits to use for 'future additions', that is, new data types? sure, a file containing date data would not be understood by executables where this has not been defined, but maybe it is possible to do it so that they see a 'text'... or maybe not... And it would go against the basic philosophy of SQLite. in which way? but as far as I am concerned, I'll be happy if I can get the original type declaration for the column by querying the database. is there a way to do so? I've looked into the doc (for example, "information_schema") but I found nothing which was working for me... thanks and regards, MF
Re: [sqlite] date data types
Nuno Lucas wrote: You need to get the column declared type and convert it to the type you want based on that. mmm... so I would look at it in a statically typed way. it sounds reasonable and is surely acceptable for me. but: how do I get the declared type of the column? currently the python interface does not retrieve this information, probably because of the dynamic typing of the engine... You can always add a ticket for it, as a new feature, and see how it goes ;-) you mean: reporting a bug? I don't manage to access the page from here, but I see that from home it does work. will definitely do so. it could be an optional feature, to be enabled at compile time... regards, MarioF
Re: [sqlite] date data types
Dennis Cote wrote:
Mario Frasca wrote:
where I would expect:
sqlite> select datetime('now');
2006-08-03 11:36:32.211032
sqlite> select typeof(datetime('now'));
datetime
sqlite> select datetime('now') - date('2006-01-01');
214 11:36:51.291331
sqlite> select typeof(datetime('now') - date('2006-01-01'));
timedelta
Mario,
The DATE and DATETIME types you seem to be expecting are handled
in SQLite as Julian dates. These are floating point numbers that
[...]
The TIMEDELTA type is also represented as a floating point number
[...]
Try these queries instead: [...]
nice. if I was just working inside of SQL and was only interested in printing
the values, it would probably be fine. my problem is that I'm co-author of a
python library (http://ibo.sourceforge.net) on top of various db-api2 interface
libraries to three or four db engines (four, if I can include sqlite). one of
the problems consists in writing and retrieving datetime data to the database.
the dynamic typing of sqlite is not a problem, actually it fits quite good with
the strong dynamic typing system of Python... but then, when I write to just
any field a value which is a date, I would like to get back a value which is a
date, not a string or a floating point number. the same goes for a datetime
and a timedelta, which are each a separate type in python...
would it be difficult, or simply impossible, to implement these types in sqlite?
anyway thanks a lot for your answer and queries!
MF
[sqlite] date data types
are there any plans to support more data types than NULL, INTEGER, REAL,
TEXT, BLOB? in particular I am interested in support of: DATE,
DATETIME, TIMEDELTA.
I tried the datetime functions, but:
sqlite> select datetime('now');
2006-08-03 11:36:32
sqlite> select typeof(datetime('now'));
text
sqlite> select datetime('now') - date('2006-01-01');
0
sqlite> select typeof(datetime('now') - date('2006-01-01'));
integer
where I would expect:
sqlite> select datetime('now');
2006-08-03 11:36:32.211032
sqlite> select typeof(datetime('now'));
datetime
sqlite> select datetime('now') - date('2006-01-01');
214 11:36:51.291331
sqlite> select typeof(datetime('now') - date('2006-01-01'));
timedelta
as far as I can judge, it does fit in the dynamic typing of sqlite...
would it be too difficult to implement cleanly and efficiently?
thanks,
MF
[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] Re: insert default values - supporting it?
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?
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?
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);}
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
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.
[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
