On Mon, 16 Apr 2012 11:27:06 -0500, "Mr. Puneet Kishor"
<[email protected]> wrote:
>Given
>
> CREATE TABLE t (
> id INTEGER NOT NULL,
> created_on DATETIME DEFAULT CURRENT_TIMESTAMP
> PRIMARY KEY (id, created_on)
> );
>
>how can I make just the 'id' column auto-increment?
I found this on the interwebs a long time ago, I didn't bookmark it and
forgot who the author was.
The original version was a bash script which used a separate notes table
and an audit table, I rewrote it to use a writable view (instead of
trigger) plus a demo case, leaving out interactive use.
The advantage of a separate notes table (and after insert/update/delete
triggers) is the autoincrement you are looking for, the disadvantage is
having two tables. Let me know if you want me to post that version as
well.
Anyway, I hope it inspires you, or any other reader.
The demo code runs slow, because it has to sleep a lot to demonstrate
evolving lifetime.
It's slightly tested, obviously the julianday('some date in the very
remote future') should be optimmized into proper real literals]
<===================================>
#!/usr/bin/sh
#
# takes notes, keep an audit trail
#
DB=/tmp/notes.sqlite
createdb () {
sqlite3 "${DB}" <<'EOSQL'
PRAGMA page_size=4096;
PRAGMA cache_size=100;
PRAGMA default_cache_size=100;
BEGIN TRANSACTION;
DROP TABLE IF EXISTS audit;
CREATE TABLE audit (
nkey INTEGER
, timeFr REAL
, timeTo REAL DEFAULT (julianday('2199-12-31 23:59:59'))
, category TEXT
, msg TEXT
, PRIMARY KEY (nkey,timeFr)
);
DROP VIEW IF EXISTS notes;
CREATE VIEW notes AS
SELECT
nkey, category, msg
FROM audit
WHERE timeTo > julianday('2199-12-31 00:00:00');
CREATE TRIGGER audit_insert_notes INSTEAD OF INSERT ON notes
FOR EACH ROW BEGIN
INSERT INTO audit (nkey, category, msg, timeFr)
VALUES (new.nkey, new.category, new.msg, julianday('now'));
END;
CREATE TRIGGER audit_update_notes INSTEAD OF UPDATE ON notes
FOR EACH ROW BEGIN
UPDATE audit SET timeTo = julianday('now')
WHERE nkey == new.nkey AND timeTo > julianday('2199-12-31 00:00:00');
INSERT INTO audit (nkey, category, msg, timeFr)
VALUES (new.nkey, new.category, new.msg, julianday('now'));
END;
CREATE TRIGGER audit_delete_notes INSTEAD OF DELETE ON notes
FOR EACH ROW BEGIN
UPDATE audit SET timeTo = julianday('now')
WHERE nkey == old.nkey AND timeTo > julianday('2199-12-31 00:00:00');
END;
DROP VIEW IF EXISTS sh_notes;
CREATE VIEW sh_notes AS
SELECT nkey
, datetime(timeFr)
, datetime(timeTo)
, category, msg
, CAST ((julianday('now') - timeFr) * 86400 AS INTEGER)
AS lifetimeseconds
FROM audit WHERE timeTo > julianday('2199-12-31 00:00:00')
ORDER BY nkey,timeFr;
DROP VIEW IF EXISTS sh_audit;
CREATE VIEW sh_audit AS
SELECT nkey
, datetime(timeFr)
, datetime(timeTo)
, category, msg
, CAST ((CASE
WHEN timeTo > julianday('2199-12-31 00:00:00') THEN julianday('now')
ELSE timeTo
END - timeFr) * 86400 AS INTEGER)
AS lifetimeseconds
FROM audit
ORDER BY nkey,timeFr;
DROP VIEW IF EXISTS sh_status;
CREATE VIEW sh_status AS
SELECT nkey
, COUNT( CASE
WHEN timeTo > julianday('2199-12-31 00:00:00') THEN 1
ELSE NULL END) AS NrActive
, COUNT( CASE
WHEN timeTo < julianday('now') THEN 1
ELSE NULL END) AS NrDeleted
FROM audit
GROUP BY nkey;
COMMIT;
EOSQL
}
insrow () { # nkey, cat#, version#
printf "INSERT INTO notes (nkey,category,msg) VALUES (%d,'cat%d','note
%d v%d');\n" $1 $2 $1 $3
sleep 3
}
updrow () { # nkey, cat#, version#
printf "UPDATE notes set category='cat%d',msg='note %d v%d' WHERE
nkey=%d;\n" $2 $1 $3 $1
sleep 2
}
delrow () { # nkey
printf "DELETE FROM notes WHERE nkey = %d;\n" $1
sleep 1
}
reportall () {
sqlite3 "${DB}" <<'EOSQL'
.header on
.mode column
.echo on
SELECT nkey,category,msg FROM notes;
SELECT * FROM sh_notes;
SELECT nkey,datetime(timeFr),datetime(timeTo),category,msg FROM audit;
SELECT * FROM sh_audit;
SELECT * FROM sh_status;
EOSQL
}
report () {
sqlite3 "${DB}" <<'EOSQL'
.header on
.mode column
.echo on
SELECT * FROM sh_notes;
SELECT * FROM sh_audit;
EOSQL
}
demo () {
{
printf ".echo on\n"
# params: nkey, cat#, version#
insrow 1 3 1
insrow 2 5 1
updrow 1 3 2
updrow 2 4 2
insrow 3 2 1
updrow 3 2 2
delrow 3
} | sqlite3 "${DB}"
}
### MAIN ###
test -f "${DB}" && rm "${DB}"
createdb
demo
reportall
sleep 5
report
<===================================>
--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
