Re: [sqlite] Version 3.0.7
At 6:02 PM -0400 9/18/04, D. Richard Hipp wrote: SQLite version 3.0.7 is now available on the website. With this release, SQLite version 3.0 leaves beta and becomes "stable". And I thank-a-you :) -- Darren Duncan
[sqlite] Version 3.0.7
SQLite version 3.0.7 is now available on the website. With this release, SQLite version 3.0 leaves beta and becomes "stable". -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Triggers Not Implemented With INTEGER PRIMARY KEY. But Assured -1?
On Sat, Sep 18, 2004 at 04:58:53PM -0400, D. Richard Hipp wrote:
> [EMAIL PROTECTED] wrote:
> >I am working with triggers on a field defined in a table
> >as INTEGER PRIMARY KEY. Agreed, triggers are not fully
> >implemented on int primary key; but, I need the autoincrement
> >feature.
> >
> >I always get -1. Can I depend on that -1 until this feature
> >is implemented?
> >
>
> If you specify the value of the INTEGER PRIMARY KEY, you
> will always see that value in all triggers. If you put a
> NULL into an INTEGER PRIMARY KEY, you'll always see a
> -1 on BEFORE triggers but the true value on AFTER triggers.
> The -1 appears on BEFORE triggers because at the time the
> trigger fires, the actual rowid has not yet been computed.
>
> >--
> >-- Also create an insert log
> >CREATE TRIGGER insert_log INSERT ON mesg
>^--- insert AFTER here
> >BEGIN
> >INSERT INTO log (mkey,mesgNEW,sqlType,mesgtimeEnter,timeEnter)
> > values (new.mkey
> > ,new.mesg,'INSERT',new.timeEnter,DATETIME('NOW') );
> >END;
> >
>
> If you do not explicitly say "AFTER", it assumes a "BEFORE".
>
>
> --
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Wonderful! Thank you.
Mike Chirico
Re: [sqlite] Triggers Not Implemented With INTEGER PRIMARY KEY. But Assured -1?
[EMAIL PROTECTED] wrote:
I am working with triggers on a field defined in a table
as INTEGER PRIMARY KEY. Agreed, triggers are not fully
implemented on int primary key; but, I need the autoincrement
feature.
I always get -1. Can I depend on that -1 until this feature
is implemented?
If you specify the value of the INTEGER PRIMARY KEY, you
will always see that value in all triggers. If you put a
NULL into an INTEGER PRIMARY KEY, you'll always see a
-1 on BEFORE triggers but the true value on AFTER triggers.
The -1 appears on BEFORE triggers because at the time the
trigger fires, the actual rowid has not yet been computed.
--
-- Also create an insert log
CREATE TRIGGER insert_log INSERT ON mesg
^--- insert AFTER here
BEGIN
INSERT INTO log (mkey,mesgNEW,sqlType,mesgtimeEnter,timeEnter)
values (new.mkey ,new.mesg,'INSERT',new.timeEnter,DATETIME('NOW') );
END;
If you do not explicitly say "AFTER", it assumes a "BEFORE".
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Can I use both libraries?
At 3:41 PM -0400 9/18/04, D. Richard Hipp wrote: Check the website ;-) Well now! Aren't *we* happy as a clam! A new chapter in SQLite history begins today ... -- Darren Duncan
[sqlite] Triggers Not Implemented With INTEGER PRIMARY KEY. But Assured -1?
I am working with triggers on a field defined in a table
as INTEGER PRIMARY KEY. Agreed, triggers are not fully
implemented on int primary key; but, I need the autoincrement
feature.
I always get -1. Can I depend on that -1 until this feature
is implemented?
Here is an example. There are two tables, mesg with
the following triggers:
CREATE TABLE mesg (mkey INTEGER PRIMARY KEY,
mesg TEXT,
timeEnter DATE);
CREATE TRIGGER insert_mesg_timeEnter AFTER INSERT ON mesg
BEGIN
UPDATE mesg SET timeEnter = DATETIME('NOW')
WHERE rowid = new.rowid;
END;
And log with the following definition and triggers:
CREATE TABLE log (lkey INTEGER PRIMARY KEY,
mkey INTEGER,
mesgOLD TEXT,
mesgNEW TEXT,
sqlType VARCHAR(15),
mesgtimeEnterDATE,
mesgtimeUpdate DATE,
timeEnterDATE);
CREATE TRIGGER update_log UPDATE OF mesg ON mesg
BEGIN
INSERT INTO log (mkey,mesgOLD,mesgNEW,sqlType,mesgtimeEnter,mesgtimeUpdate,timeEnter)
values (old.mkey,old.mesg, new.mesg,
'UPDATE',old.timeEnter,DATETIME('NOW'),DATETIME('NOW') );
END;
--
-- Also create an insert log
CREATE TRIGGER insert_log INSERT ON mesg
BEGIN
INSERT INTO log (mkey,mesgNEW,sqlType,mesgtimeEnter,timeEnter)
values (new.mkey ,new.mesg,'INSERT',new.timeEnter,DATETIME('NOW') );
END;
-- Also create a DELETE entry in log
CREATE TRIGGER delete_log DELETE ON mesg
BEGIN
INSERT INTO log (mkey,mesgOLD,sqlType,timeEnter)
values (old.mkey,old.mesg,'DELETE',DATETIME('NOW') );
END;
Now, if I issue the following commands:
$ sqlite3 msgdatabase < mesgScript
$ sqlite3 msgdatabase "insert into mesg (mesg) values ('My first message to table
mesg')"
$ sqlite3 msgdatabase "select * from mesg"
1|My first message to table mesg|2004-09-18 20:09:46
$ sqlite3 msgdatabase < logScript
$ sqlite3 msgdatabase "insert into mesg (mesg) values ('Test log table message')"
$ sqlite3 msgdatabase "update mesg set mesg='NEW VALUE from update' where mesg like
'Test log%'"
$ sqlite3 msgdatabase "insert into mesg (mesg) values ('will soon delete this')"
$ sqlite3 msgdatabase "delete from mesg where mesg like 'will soon del%'"
The I will get the following output.
1|-1||Test log table message|INSERT|||2004-09-18 20:10:39
2|2|Test log table message|NEW VALUE from update|UPDATE|2004-09-18
20:10:39|2004-09-18 20:10:47|2004-09-18 20:10:47
3|-1||will soon delete this|INSERT|||2004-09-18 20:10:58
4|3|will soon delete this||DELETE|||2004-09-18 20:11:08
My question: What is meant by strange output in the documentation?
Will I always get -1, which will be the extent of the strange
behavior, or can I expect anything, any value?
Note the trigger issue is not limited to mkey in mesg. It
is anything that changes automatically, like timeEnter as
well. For instance, timeEnter changes in mesg cannot be
picked up on log, just as mkey cannot.
Regards,
Mike Chirico
Re: [sqlite] Can I use both libraries?
Darren Duncan wrote: At 10:04 AM -0400 9/18/04, D. Richard Hipp wrote: That problem was fixed on Sep 6 by check-in [1941]. See http://www.sqlite.org/cvstrac/chngview?cn=1941 While there are issues going on that prevent a move to 'production status' before the end of the month, would it be too much trouble to release a 3.0.7 today as another beta? That would make it easier for people to test the current code prior to the production release. -- Darren Duncan Check the website ;-) -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Can I use both libraries?
At 10:04 AM -0400 9/18/04, D. Richard Hipp wrote: That problem was fixed on Sep 6 by check-in [1941]. See http://www.sqlite.org/cvstrac/chngview?cn=1941 While there are issues going on that prevent a move to 'production status' before the end of the month, would it be too much trouble to release a 3.0.7 today as another beta? That would make it easier for people to test the current code prior to the production release. -- Darren Duncan
[sqlite] is it o.k. to close database handle in both child and parent
say you have the following logic: begin transaction in parent if pid = fork commit transaction close database else close database ... end should this be o.k.? i notice the unlink of the db-journal will happen twice - which should be o.k. but will flush/sync operations happen twice? my code that does this seems to work fine - but i'm wondering what the 'official' answer would be. kind regards. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] Can I use both libraries?
Marco Bambini wrote: both sqlite.h and sqlite3.h begins with: #ifndef _SQLITE_H_ #define _SQLITE_H_ ... #endif Any solution? That problem was fixed on Sep 6 by check-in [1941]. See http://www.sqlite.org/cvstrac/chngview?cn=1941 -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Can I use both libraries?
Hi,
I have to develop an application that can use both sqlite2 dbs and
sqlite3 dbs.
I have compiled fine both the libraries and my trial code looks like:
#include
#include "sqlite.h"
#include "sqlite3.h"
int main(int argc, char *argv[])
{
int err;
sqlite *ver2;
sqlite3 *ver3;
charpath2[]="trial2.db";
charpath3[]="trial3.db";
ver2 = sqlite_open(path2, 0, NULL);
if (ver2!=NULL)
{
// Ok for version 2
sqlite_close(ver2);
return 2;
}
err = sqlite3_open(path3, );
if (err==SQLITE_OK)
{
// OK for version 3
sqlite3_close(ver3);
return 3;
}
return 0;
}
Now the troubles
both sqlite.h and sqlite3.h begins with:
#ifndef _SQLITE_H_
#define _SQLITE_H_
...
#endif
so only one header is included and the other (the latest) is ignored.
A possible solution should be to change sqlite3.h with:
#ifndef _SQLITE3_H_
#define _SQLITE3_H_
...
#endif
but now, when I am trying to compile my application I have a lot of
'macro/identifiers redeclared' errors...
For example:
macro 'SQLITE_VERSION' redefined
or
identifier 'sqlite_callback' redeclared
or
macro 'SQLITE_TEXT' redefined
and so on...
Any solution?
I really need to use both libraries in my project...
Thanks a lot.
Marco Bambini
