Re: [sqlite] auto-incrementing integer in composite primary key (SQLite3 Solution)

[Kyle McKay]

On April 16, 2012 09:27:06 PDT, "Mr. Puneet Kishor" 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?


Here is an example of how to get the functionality you're looking for  
using SQLite3:


$ sqlite3 autoincrement-compound-key.sq3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> .schema
CREATE TABLE example (
  id INTEGER UNIQUE NOT NULL,
  name TEXT,
  PRIMARY KEY (id, name)
);
CREATE TRIGGER example_autoincrement BEFORE INSERT ON example
  FOR EACH ROW WHEN NEW.id IS NULL BEGIN
INSERT INTO example VALUES (
  COALESCE((SELECT MAX(id) FROM example),0) + 1,
  NEW.name
);
SELECT RAISE(IGNORE);
  END;
sqlite> DELETE FROM example;
sqlite> INSERT INTO example (name) VALUES ('first'),('second'), 
('third');

sqlite> INSERT INTO example VALUES (10, 'ten');
sqlite> INSERT INTO example (name) VALUES ('eleven');
sqlite> SELECT * FROM example ORDER BY id;
1|first
2|second
3|third
10|ten
11|eleven
sqlite> .quit
$

The idea is to have the BEFORE INSERT trigger do the actual  
computation of the next auto increment value to use (but only when one  
was not already specified) AND the actual insertion of the NEW row and  
then use the special SQLite3 RAISE(IGNORE) functionality to skip the  
insertion of the NEW row that would normally have occurred immediately  
following the BEFORE INSERT trigger.


In this example, the id column is constrained to be UNIQUE which  
causes an index to be created that the SELECT MAX(id) expression in  
the trigger then uses for efficiency.  If you do not require the id  
column to be UNIQUE, and instead only require that if id is not set,  
the max(id) + 1 value is used, you can remove the UNIQUE constraint  
and then add a separate CREATE INDEX for the id column so that the  
SELECT MAX(id) expression remains efficient in the context of a large  
number of rows.


--Kyle
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Kees Nuyt]

On Mon, 16 Apr 2012 11:27:06 -0500, "Mr. Puneet Kishor"
 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
, timeFrREAL
, timeToREAL 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Nico Williams]

On Mon, Apr 16, 2012 at 8:00 PM, Simon Slavin  wrote:
> On 17 Apr 2012, at 12:33am, Petite Abeille  wrote:
>> On Apr 17, 2012, at 12:51 AM, Simon Slavin wrote:
>>> Now, consider what it takes if you're logging value changes instead of 
>>> commands issued.  Your first problem is figuring out which rows exist.
>>
>> Why? Each row is time boxed. There is no ambiguities about what exists when.
>
> You're starting from the point where you know which row you're looking for.  
> I'm not sure how you knew the 'where   foo.foo_key = 1' part of your SELECT.  
> So in your reconstruction scenario, which question are you answering ?  Are 
> you
>
> 1) trying to reconstruct the entire database
> 2) trying to reconstruct all the data about a particular entity: find the row 
> for a customer named "ACME INC."
> 3) trying to find a number of rows: find all customers who a particular 
> salesman was managing

When all historical data is mixed with current (and future) data you
have to be careful to filter your queries for one point in time, else
the results may not be self-consistent (e.g., keys that are supposed
to be unique at a point in time may not be).

You also can't really rely on UNIQUE constraints/indexes.  Instead you
have to have application code (not just triggers!) to check -at
transaction commit time- that what would have been unique constraints
are not violated at *any* point in time.  This requires determining
all event times implied in a transaction (e.g., creating a row with a
not_after value less than infinity creates a future event).  You can
use temp tables and views to do most of these checks in SQL, but it
still requires application code.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Simon Slavin]

On 17 Apr 2012, at 12:33am, Petite Abeille  wrote:

> On Apr 17, 2012, at 12:51 AM, Simon Slavin wrote:
> 
>> Now, consider what it takes if you're logging value changes instead of 
>> commands issued.  Your first problem is figuring out which rows exist.
> 
> Why? Each row is time boxed. There is no ambiguities about what exists when.

You're starting from the point where you know which row you're looking for.  
I'm not sure how you knew the 'where   foo.foo_key = 1' part of your SELECT.  
So in your reconstruction scenario, which question are you answering ?  Are you

1) trying to reconstruct the entire database
2) trying to reconstruct all the data about a particular entity: find the row 
for a customer named "ACME INC."
3) trying to find a number of rows: find all customers who a particular 
salesman was managing

Your procedure is the right solution for

4) I know the rowid of the record I'm interested in

But I don't know if that's the scenario Puneet was interested in, or if that's 
something which would happen in real life: needing to reconstruct that row, and 
not caring about any of the other data in the database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 17, 2012, at 12:51 AM, Simon Slavin wrote:

> Now, consider what it takes if you're logging value changes instead of 
> commands issued.  Your first problem is figuring out which rows exist.

Why? Each row is time boxed. There is no ambiguities about what exists when.

>  Are you storing triplets, or complete rows ?

Let's assume complete rows for simplicity's sake.

>  What SELECTs are you going to do ?

As mentioned:

select  *
fromfoo

joinbar
on  bar.bar_key = foo.bar_key

where   foo.foo_key = 1
and julianday( ... ) between foo.valid_from and foo.valid_to
and julianday( ... ) between bar.valid_from and bar.valid_to

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 5:51 PM, Simon Slavin wrote:

> 
> On 16 Apr 2012, at 11:25pm, Puneet Kishor  wrote:
> 
>> I absolutely don't get any of the above. Why is "keeping the data" worse 
>> than keeping the commands? I am not even sure what is a command vs. what is 
>> a data. A SQL command has data embedded in it. I mean, if I have
>> 
>>  UPDATE t 
>>  SET name = 'foo', val = 3.1415
>>  WHERE id = 22;
>> 
>> which part was the command and which part was the data?
> 
> The command is the three rows above.  Imagine you'd passed that command to 
> _exec().  So as well as executing that command you write it to a file 
> somewhere:
> 
> logid timestamp   command
> 1637422347634.133 UPDATE t SET name = 'foo', val = 3.1415 WHERE 
> id = 22;
> 
> In terms of the data, I'm not sure whether you're keeping copies of the 
> entire row, or just triplets.  If you're keeping a copy of the row every time 
> the row changes then you would need to store
> 
> logid timestamp   tablename   id  nameval col3col4
> col5...
> 2138762347634.133 t   22  foo 3.1415  6   
> fredx   ...
> 
> if instead you are storing triplets then you would need to store two rows of 
> data
> 
> logid timestamp   tablename   id  column  value
> 8247242347634.133 t   22  namefoo
> 8247252347634.133 t   22  val 3.1415


all that is fine, but how does that solve my problem? So, I want to find out 
row 22 "AS OF", to use Oracle's Total Recall functionality (thanks PA), at a 
time before the update happened. How do I do that?

This just seems way too elaborate with no gains toward the capability I desire. 
Maybe I am missing something.

--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Simon Slavin]

On 16 Apr 2012, at 11:25pm, Puneet Kishor  wrote:

> I absolutely don't get any of the above. Why is "keeping the data" worse than 
> keeping the commands? I am not even sure what is a command vs. what is a 
> data. A SQL command has data embedded in it. I mean, if I have
> 
>   UPDATE t 
>   SET name = 'foo', val = 3.1415
>   WHERE id = 22;
> 
> which part was the command and which part was the data?

The command is the three rows above.  Imagine you'd passed that command to 
_exec().  So as well as executing that command you write it to a file somewhere:

logid   timestamp   command
163742  2347634.133 UPDATE t SET name = 'foo', val = 3.1415 WHERE id = 22;

In terms of the data, I'm not sure whether you're keeping copies of the entire 
row, or just triplets.  If you're keeping a copy of the row every time the row 
changes then you would need to store

logid   timestamp   tablename   id  nameval col3col4
col5...
213876  2347634.133 t   22  foo 3.1415  6   fred
x   ...

if instead you are storing triplets then you would need to store two rows of 
data

logid   timestamp   tablename   id  column  value
824724  2347634.133 t   22  namefoo
824725  2347634.133 t   22  val 3.1415

Obviously I just made up the column names off the top of my head, and I didn't 
invent a mechanism for noting DELETE.

On 16 Apr 2012, at 11:10pm, Nico Williams  wrote:

> The nice thing about having all historical and current and future data
> in one table is that you can:
> 
> a) trivially review the past,
> b) trivially create future changes that become effective as time passes.

I agree that this has its advantages.  I suppose it comes down to what kind of 
use you're going to make of the data.

>> Whether you are keeping copies of the rows in the table, or timestamping SQL 
>> commands, I suggest that for SQLite your timestamps should be unixepoch 
>> stored as a REAL rather than a text expression of seconds.
> 
> Why REAL instead of INTEGER?

Because with sufficient resolution they are unique, which gives you an 
understanding of which change was made before which other change.  If you use 
just integer seconds you can be left with two changes with the same timestamp.



On 16 Apr 2012, at 11:15pm, Petite Abeille  wrote:

> But, in practice, how would one use such DML logs? Say someone got a small 
> million rows, with over a period of time where subjected to 10x that many 
> DMLs (update, insert, delete). How would one now practically use these DML 
> logs to query data at two different point in times? Replay all the logs from 
> the beginning each and every time?

Good question.  You can take periodical snapshots of your entire database, and 
log those together with your log of changes.  So to restore your data as of 
time T, you would

1) restore the latest snapshot made before time T
2) then replay all commands issued after that snapshot but before time T.

Now, consider what it takes if you're logging value changes instead of commands 
issued.  Your first problem is figuring out which rows exist.  Are you storing 
triplets, or complete rows ?  What SELECTs are you going to do ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 5:04 PM, Simon Slavin wrote:

> 
> On 16 Apr 2012, at 10:31pm, Peter Aronson  wrote:
> 
>> You might want to look at the book Temporal Data and the Relational Model by 
>> Date, Darwin and Lorentzos, which goes into the subject in fairly great 
>> detail.  
>> There are subtleties.
> 
> Doctor Darwen teaches this as a course.  You might like to read the free 
> detailed course notes here:
> 
> 
> 
> Unfortunately he uses terms aimed at an academic mathematical understanding 
> of attributes (relvars), which are difficult to understand if you're just a 
> programmer at the rock face.  What it comes down to is that if the only data 
> you have is the result of SQL commands you don't have enough data to 
> understand the facts implicit in the contents of your tables.
> 
> I urge again the different approach I mentioned earlier.  Forget keeping the 
> data, and instead keep the commands used to change the data.  That way, 
> instead of keeping the /results/ of your SQL commands, you're keeping the 
> commands yourself, which is rawer (more raw ?) data, and therefore more 
> faithful to what you know, rather than what you're trying to deduce.

I absolutely don't get any of the above. Why is "keeping the data" worse than 
keeping the commands? I am not even sure what is a command vs. what is a data. 
A SQL command has data embedded in it. I mean, if I have

UPDATE t 
SET name = 'foo', val = 3.1415
WHERE id = 22;

which part was the command and which part was the data? 

Why is just making a copy of the existing row with id = 22 and then modifying 
the copy not good enough? I don't have to deduce anything. All I have to do is 
make a copy of any row that is "more than a trivial update". Theoretically I 
could do that with every single table, and if the id didn't change (assuming I 
had a PK that was different from the id, say, a composite PK), then I could 
reconstruct exact queries easily.

> 
> Whether you are keeping copies of the rows in the table, or timestamping SQL 
> commands, I suggest that for SQLite your timestamps should be unixepoch 
> stored as a REAL rather than a text expression of seconds.
> 

Yes, that is a useful advice.

Thanks,

--
Puneet Kishor

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 17, 2012, at 12:04 AM, Simon Slavin wrote:

> I urge again the different approach I mentioned earlier.  Forget keeping the 
> data, and instead keep the commands used to change the data.  That way, 
> instead of keeping the /results/ of your SQL commands, you're keeping the 
> commands yourself, which is rawer (more raw ?) data, and therefore more 
> faithful to what you know, rather than what you're trying to deduce.

Perhaps. 

But, in practice, how would one use such DML logs? Say someone got a small 
million rows, with over a period of time where subjected to 10x that many DMLs 
(update, insert, delete). How would one now practically use these DML logs to 
query data at two different point in times? Replay all the logs from the 
beginning each and every time?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Nico Williams]

On Mon, Apr 16, 2012 at 5:04 PM, Simon Slavin  wrote:
> On 16 Apr 2012, at 10:31pm, Peter Aronson  wrote:
>> You might want to look at the book Temporal Data and the Relational Model by
>> Date, Darwin and Lorentzos, which goes into the subject in fairly great 
>> detail.
>> There are subtleties.
>
> Doctor Darwen teaches this as a course.  You might like to read the free 
> detailed course notes here:
>
> 

Thanks for the reference.

> I urge again the different approach I mentioned earlier.  Forget keeping the 
> data, and instead keep the commands used to change the data.  That way, 
> instead of keeping the /results/ of your SQL commands, you're keeping the 
> commands yourself, which is rawer (more raw ?) data, and therefore more 
> faithful to what you know, rather than what you're trying to deduce.

The nice thing about having all historical and current and future data
in one table is that you can:

a) trivially review the past,
b) trivially create future changes that become effective as time passes.

These are non-trivial benefits.  The main problem is that the
complications added by this model effectively require one to build a
SQL-on-SQL system.  For some applications this additional complication
is probably justifiable by the value of its benefits.

> Whether you are keeping copies of the rows in the table, or timestamping SQL 
> commands, I suggest that for SQLite your timestamps should be unixepoch 
> stored as a REAL rather than a text expression of seconds.

Why REAL instead of INTEGER?

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Simon Slavin]

On 16 Apr 2012, at 10:31pm, Peter Aronson  wrote:

> You might want to look at the book Temporal Data and the Relational Model by 
> Date, Darwin and Lorentzos, which goes into the subject in fairly great 
> detail.  
> There are subtleties.

Doctor Darwen teaches this as a course.  You might like to read the free 
detailed course notes here:



Unfortunately he uses terms aimed at an academic mathematical understanding of 
attributes (relvars), which are difficult to understand if you're just a 
programmer at the rock face.  What it comes down to is that if the only data 
you have is the result of SQL commands you don't have enough data to understand 
the facts implicit in the contents of your tables.

I urge again the different approach I mentioned earlier.  Forget keeping the 
data, and instead keep the commands used to change the data.  That way, instead 
of keeping the /results/ of your SQL commands, you're keeping the commands 
yourself, which is rawer (more raw ?) data, and therefore more faithful to what 
you know, rather than what you're trying to deduce.

Whether you are keeping copies of the rows in the table, or timestamping SQL 
commands, I suggest that for SQLite your timestamps should be unixepoch stored 
as a REAL rather than a text expression of seconds.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Peter Aronson]

You might want to look at the book Temporal Data and the Relational Model by 
Date, Darwin and Lorentzos, which goes into the subject in fairly great 
detail.  
There are subtleties.
 
Best regards,
 
Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 16, 2012, at 11:01 PM, Kit wrote:

>> - how do you represent deleted rows?
> 
> I will create a new record with attribute "deleted" and new timestamp.

So there is now a new attribute that indicates deletion? Which needs to be 
included in all queries? In addition to the time aspect? Why a different way to 
indicate deletion from time boxing? 

> 
>> - how do you avoid version ambiguities (e.g. two rows created with the same 
>> timestamp)?
> 
> UNIQUE index on (t.doc_id,t.created_on)

So one cannot make more than one change per second? What happen if multiple 
changes occur at the same time granularity?

> 
> A modified select:
> SELECT doc.record, t.rec, t.created_on FROM doc LEFT JOIN t ON doc.id=t.doc_id
>WHERE doc.id=id_xx AND created_onGROUP BY t.doc_id
>HAVING created_on=max(created_on);

The above will return deleted rows, no? Shouldn't it include that new "deleted" 
attribute?

Also... why an outer join? Why two tables? Is it mostly for auditing? Not 
versioning? 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Kit]

2012/4/16 Petite Abeille :
> On Apr 16, 2012, at 9:09 PM, Kit wrote:
>> SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id
>>      WHERE doc.id=id_xx AND created_on<=time_xx
>>      ORDER BY created_on DESC LIMIT 1;

> - how do you represent deleted rows?

I will create a new record with attribute "deleted" and new timestamp.

> - how do you avoid version ambiguities (e.g. two rows created with the same 
> timestamp)?

UNIQUE index on (t.doc_id,t.created_on)

A modified select:
SELECT doc.record, t.rec, t.created_on FROM doc LEFT JOIN t ON doc.id=t.doc_id
WHERE doc.id=id_xx AND created_onhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 16, 2012, at 10:51 PM, Puneet Kishor wrote:

> how do I get the effect of `id INTEGER AUTOINCREMENT`. Guess I can't, not in 
> sqlite3, because AUTOINCREMENT only works with the PK invocation. So, I have 
> to use some other manual mechanism. 

Right, no cigar in SQLite. You will need to roll your own mechanism to get the 
equivalent of a sequence. Which could be as simple as "select coalesce( max( id 
), 0 ) + 1 from foo".

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 3:47 PM, Petite Abeille wrote:

> 
> On Apr 16, 2012, at 10:37 PM, Puneet Kishor wrote:
> 
>> Thanks for your wise words. I am not at all under any illusion that this is 
>> going to be easy, but it is worthy of an honest try. Two reactions --
>> 
>> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
>> `created_on <= :provided_date`? The latter requires storing only a single 
>> date value for every row. Although, after some studying of the Pg timetravel 
>> docs, seems like they too use a start and stop date.
> 
> Although the end date is not strictly speaking necessary, and can be derived 
> from a previous start date, it make the query more natural: "date between 
> start and end", as opposed to some other peculiar oddities…
> 
> It also allows to express deletion in one fell swoop: delete a record by 
> closing its  end date.
> 
>> 2. Yes, most constraint mechanisms might be useless or difficult to 
>> implement, but I do need a PK.
> 
> Well, I suspect you need the equivalent of, say, a "business key". Something 
> that uniquely identify a record *outside* of its versioning. But such an 
> identifier is most likely not going to be a primary key, in the traditional 
> relational constraint sense of it.
> 


and hence, my original question: given

id INTEGER,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_on)

how do I get the effect of `id INTEGER AUTOINCREMENT`. Guess I can't, not in 
sqlite3, because AUTOINCREMENT only works with the PK invocation. So, I have to 
use some other manual mechanism. Fwiw, in Pg I can do

id SERIAL,
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_on)

where `SERIAL` does the right thing by way of setting up the sequences, etc.


--
Puneet Kishor

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 16, 2012, at 10:37 PM, Puneet Kishor wrote:

> Thanks for your wise words. I am not at all under any illusion that this is 
> going to be easy, but it is worthy of an honest try. Two reactions --
> 
> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
> `created_on <= :provided_date`? The latter requires storing only a single 
> date value for every row. Although, after some studying of the Pg timetravel 
> docs, seems like they too use a start and stop date.

Although the end date is not strictly speaking necessary, and can be derived 
from a previous start date, it make the query more natural: "date between start 
and end", as opposed to some other peculiar oddities…

It also allows to express deletion in one fell swoop: delete a record by 
closing its  end date.

> 2. Yes, most constraint mechanisms might be useless or difficult to 
> implement, but I do need a PK.

Well, I suspect you need the equivalent of, say, a "business key". Something 
that uniquely identify a record *outside* of its versioning. But such an 
identifier is most likely not going to be a primary key, in the traditional 
relational constraint sense of it.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Rob Richardson]

If you deleted  record on New Year's Day, you want a query for data on New 
Year's Eve to find the record but you don't want a query for data on January 
2nd to find it.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Monday, April 16, 2012 4:43 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] auto-incrementing integer in composite primary key

> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
> `created_on <= :provided_date`?

What if there are several versions created before your provided_date?
Not all queries will allow to add `order by created_on desc limit 1`.


Pavel


On Mon, Apr 16, 2012 at 4:37 PM, Puneet Kishor  wrote:
>
> On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote:
>
>>
>> On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote:
>>
>>> I am trying to create a data versioning system so that a query done at a 
>>> particular time can be reproduced identically as to the original query even 
>>> if the data have been modified in the interim time.
>>
>> My 2¢ worth.
>>
>> (1) Proper historization/versioning is not a piece of cake
>> (2) Most constraint mechanisms do not help with it
>>
>> Regarding (1), I would suggest a relatively straightforward setup where all 
>> you versioned tables include a date range specifying the point in time a 
>> record is valid. This is more conveniently expressed as two fields, along 
>> the lines of valid_from and valid_to, so you can then query it with a 
>> between clause.
>>
>> Each DML operations need to maintain that date range so it stays logically 
>> consistent (e.g. no overlaps, not gaps, no delete, etc).
>>
>> At the end of the day, you should be able to query your data for any point 
>> in time consistently:
>>
>> select  *
>> from    foo
>>
>> join    bar
>> on      bar.bar_key = foo.bar_key
>>
>> where   foo.foo_key = 1
>> and     julianday( ... ) between foo.valid_from and foo.valid_to and     
>> julianday( ... ) between bar.valid_from and bar.valid_to
>>
>>
>> Regarding (2), I would suggest to forgo traditional integrity constraint 
>> mechanisms (primary, unique, referential, etc) as they simply don't play 
>> well with (1). For example, one cannot express a meaningful, and useful, 
>> primary, nor unique key on versioned data. Ditto for referential 
>> constraints. Which also means you have to re-implement  all of the above by 
>> yourself. Which is a pain and rather error prone.
>>
>
>
> Thanks for your wise words. I am not at all under any illusion that 
> this is going to be easy, but it is worthy of an honest try. Two 
> reactions --
>
> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
> `created_on <= :provided_date`? The latter requires storing only a single 
> date value for every row. Although, after some studying of the Pg timetravel 
> docs, seems like they too use a start and stop date.
>
> 2. Yes, most constraint mechanisms might be useless or difficult to 
> implement, but I do need a PK.
>
>
> --
> Puneet Kishor
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Pavel Ivanov]

> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
> `created_on <= :provided_date`?

What if there are several versions created before your provided_date?
Not all queries will allow to add `order by created_on desc limit 1`.


Pavel


On Mon, Apr 16, 2012 at 4:37 PM, Puneet Kishor  wrote:
>
> On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote:
>
>>
>> On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote:
>>
>>> I am trying to create a data versioning system so that a query done at a 
>>> particular time can be reproduced identically as to the original query even 
>>> if the data have been modified in the interim time.
>>
>> My 2¢ worth…
>>
>> (1) Proper historization/versioning is not a piece of cake
>> (2) Most constraint mechanisms do not help with it
>>
>> Regarding (1), I would suggest a relatively straightforward setup where all 
>> you versioned tables include a date range specifying the point in time a 
>> record is valid. This is more conveniently expressed as two fields, along 
>> the lines of valid_from and valid_to, so you can then query it with a 
>> between clause.
>>
>> Each DML operations need to maintain that date range so it stays logically 
>> consistent (e.g. no overlaps, not gaps, no delete, etc).
>>
>> At the end of the day, you should be able to query your data for any point 
>> in time consistently:
>>
>> select  *
>> from    foo
>>
>> join    bar
>> on      bar.bar_key = foo.bar_key
>>
>> where   foo.foo_key = 1
>> and     julianday( ... ) between foo.valid_from and foo.valid_to
>> and     julianday( ... ) between bar.valid_from and bar.valid_to
>>
>>
>> Regarding (2), I would suggest to forgo traditional integrity constraint 
>> mechanisms (primary, unique, referential, etc) as they simply don't play 
>> well with (1). For example, one cannot express a meaningful, and useful, 
>> primary, nor unique key on versioned data. Ditto for referential 
>> constraints. Which also means you have to re-implement  all of the above by 
>> yourself. Which is a pain and rather error prone.
>>
>
>
> Thanks for your wise words. I am not at all under any illusion that this is 
> going to be easy, but it is worthy of an honest try. Two reactions --
>
> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
> `created_on <= :provided_date`? The latter requires storing only a single 
> date value for every row. Although, after some studying of the Pg timetravel 
> docs, seems like they too use a start and stop date.
>
> 2. Yes, most constraint mechanisms might be useless or difficult to 
> implement, but I do need a PK.
>
>
> --
> Puneet Kishor
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Nico Williams]

On Mon, Apr 16, 2012 at 3:30 PM, Petite Abeille
 wrote:
>
> On Apr 16, 2012, at 9:09 PM, Kit wrote:
>
>> SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id
>>      WHERE doc.id=id_xx AND created_on<=time_xx
>>      ORDER BY created_on DESC LIMIT 1;
>
> - how do you represent deleted rows?
> - how do you avoid version ambiguities (e.g. two rows created with the same 
> timestamp)?

The latter should be handled by having the create/modify time be part
of the primary key or otherwise part of a unique index.

The former could be done with a "not_after" column, say.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote:

> 
> On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote:
> 
>> I am trying to create a data versioning system so that a query done at a 
>> particular time can be reproduced identically as to the original query even 
>> if the data have been modified in the interim time.
> 
> My 2¢ worth…
> 
> (1) Proper historization/versioning is not a piece of cake
> (2) Most constraint mechanisms do not help with it
> 
> Regarding (1), I would suggest a relatively straightforward setup where all 
> you versioned tables include a date range specifying the point in time a 
> record is valid. This is more conveniently expressed as two fields, along the 
> lines of valid_from and valid_to, so you can then query it with a between 
> clause.
> 
> Each DML operations need to maintain that date range so it stays logically 
> consistent (e.g. no overlaps, not gaps, no delete, etc).
> 
> At the end of the day, you should be able to query your data for any point in 
> time consistently:
> 
> select  *
> fromfoo
> 
> joinbar
> on  bar.bar_key = foo.bar_key
> 
> where   foo.foo_key = 1
> and julianday( ... ) between foo.valid_from and foo.valid_to
> and julianday( ... ) between bar.valid_from and bar.valid_to
> 
> 
> Regarding (2), I would suggest to forgo traditional integrity constraint 
> mechanisms (primary, unique, referential, etc) as they simply don't play well 
> with (1). For example, one cannot express a meaningful, and useful, primary, 
> nor unique key on versioned data. Ditto for referential constraints. Which 
> also means you have to re-implement  all of the above by yourself. Which is a 
> pain and rather error prone.
> 


Thanks for your wise words. I am not at all under any illusion that this is 
going to be easy, but it is worthy of an honest try. Two reactions --

1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
`created_on <= :provided_date`? The latter requires storing only a single date 
value for every row. Although, after some studying of the Pg timetravel docs, 
seems like they too use a start and stop date.

2. Yes, most constraint mechanisms might be useless or difficult to implement, 
but I do need a PK.


--
Puneet Kishor

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 16, 2012, at 9:09 PM, Kit wrote:

> SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id
>  WHERE doc.id=id_xx AND created_on<=time_xx
>  ORDER BY created_on DESC LIMIT 1;

- how do you represent deleted rows?
- how do you avoid version ambiguities (e.g. two rows created with the same 
timestamp)?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote:

> I am trying to create a data versioning system so that a query done at a 
> particular time can be reproduced identically as to the original query even 
> if the data have been modified in the interim time.

My 2¢ worth…

(1) Proper historization/versioning is not a piece of cake
(2) Most constraint mechanisms do not help with it

Regarding (1), I would suggest a relatively straightforward setup where all you 
versioned tables include a date range specifying the point in time a record is 
valid. This is more conveniently expressed as two fields, along the lines of 
valid_from and valid_to, so you can then query it with a between clause.

Each DML operations need to maintain that date range so it stays logically 
consistent (e.g. no overlaps, not gaps, no delete, etc).

At the end of the day, you should be able to query your data for any point in 
time consistently:

select  *
fromfoo

joinbar
on  bar.bar_key = foo.bar_key

where   foo.foo_key = 1
and julianday( ... ) between foo.valid_from and foo.valid_to
and julianday( ... ) between bar.valid_from and bar.valid_to


Regarding (2), I would suggest to forgo traditional integrity constraint 
mechanisms (primary, unique, referential, etc) as they simply don't play well 
with (1). For example, one cannot express a meaningful, and useful, primary, 
nor unique key on versioned data. Ditto for referential constraints. Which also 
means you have to re-implement  all of the above by yourself. Which is a pain 
and rather error prone.

Got luck either ways. :)



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Kit]

2012/4/16 Puneet Kishor :
> I am trying to create a data versioning system so that a query done at a 
> particular time can be reproduced identically as to the original query even 
> if the data have been modified in the interim time.

CREATE TABLE doc (
  id INTEGER PRIMARY KEY autoincrement,
  record TEXT
);

CREATE TABLE t (
  id INTEGER PRIMARY KEY autoincrement,
  doc_id INTEGER,
  rec TEXT,
  created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(doc_id) REFERENCES doc(id)
);

SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id
  WHERE doc.id=id_xx AND created_on<=time_xx
  ORDER BY created_on DESC LIMIT 1;

`id_xx` and `time_xx` are keys for search. You may use some additional indexes.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Nico Williams]

On Mon, Apr 16, 2012 at 12:58 PM, Puneet Kishor  wrote:
> I am experimenting with a home-grown versioning system where every 
> "significant" modification to row would be performed on a copy of the row, 
> the original being preserved. So, if I have

There are several ways to handle this.

You could denormalize the ID into a separate table that holds... just
the ID, so that way you get your autoincrement.  Or you could use a
trigger to set the ID column (which means you must allow it to be
NULL) to the max() + 1 of the IDs. and you'll need the ID to be first
in some index (or the composite key) so that you can make that max()
run efficiently.  The denormalization-of-the-ID approach also lets you
create other sorts of stable identifiers besides integers, such as
UUIDs, say.

You'll need VIEWs to filter out all but current data.

Simon suggests moving the historical data into separate tables, which
is a good idea, except that if you want to have future changes
pre-created and take effect as time passes then the separate tables
schema doesn't work very well.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 16, 2012, at 8:38 PM, Simon Slavin wrote:

> However, one way to achieve your requirements efficiently is extremely 
> simple: just log all UPDATE and INSERT commands.  Save them, plus a 
> timestamp, in a file, either a text file or a SQLite database.  When you need 
> to reconstruct your database at any date/time, simply replay your transcript 
> up to that data/time.

"In theory, there is no difference between theory and practice. But, in 
practice, there is." :P



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Pavel Ivanov]

> So, if a query returns one or more rows today, the same query (that is, the 
> same query params with an additional time stamp param) should return exactly 
> the same result 3 years from now even if the rows themselves may have been 
> modified.

I just want to note that to support this function you probably want to
add 2 dates to each row - one when this version's life
started and another one - when it's ended. Otherwise your queries to
the past will be very complicated (but it seems to me queries about
present are pretty complicated too).

For auto-incrementing maybe you want to implement your own auxiliary
table a-la sqlite_sequence: when you need to insert new row you select
current value from this table, update it and insert row with selected
value.


Pavel


On Mon, Apr 16, 2012 at 2:29 PM, Puneet Kishor  wrote:
>
> On Apr 16, 2012, at 1:14 PM, Kit wrote:
>
>> 2012/4/16 Puneet Kishor :
>>> I am experimenting with a home-grown versioning system where every 
>>> "significant" modification to row would be performed on a copy of the row, 
>>> the original being preserved.
>>> Any other suggestions to achieve a similar functionality would be welcome.
>>> --
>>> Puneet Kishor
>>
>> 1. Use Git or Mercurial
>
>
> My statement might have been misunderstood. I am not trying to create a 
> versioning system a la Git, Mercurial or Fossil. I am trying to create a data 
> versioning system so that a query done at a particular time can be reproduced 
> identically as to the original query even if the data have been modified in 
> the interim time.
>
> So, if a query returns one or more rows today, the same query (that is, the 
> same query params with an additional time stamp param) should return exactly 
> the same result 3 years from now even if the rows themselves may have been 
> modified.
>
> In Postgres world they call it timetravel. See "F.39.2. timetravel — 
> Functions for Implementing Time Travel" at 
> http://www.postgresql.org/docs/9.1/static/contrib-spi.html for reference.
>
>
>
>
>> 2. Try this:
>>
>> CREATE TABLE instance  (
>>         filename TEXT,
>>         version INT,
>>         size INT,
>>         md5sum TEXT,
>>         creation_date TEXT,
>>         last_write_time TEXT,
>>         PRIMARY KEY(filename,version),
>>         FOREIGN KEY (md5sum) REFERENCES resource (md5sum)
>>         );
>>
>> CREATE TABLE resource (
>>         md5sum TEXT,
>>         data BLOB,
>>         primary key(md5sum)
>>       );
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Simon Slavin]

On 16 Apr 2012, at 7:29pm, Puneet Kishor  wrote:

> So, if a query returns one or more rows today, the same query (that is, the 
> same query params with an additional time stamp param) should return exactly 
> the same result 3 years from now even if the rows themselves may have been 
> modified.

If your system can accept UPDATE commands which multiple rows, then the only 
way to do it correctly is to use a TRIGGER.  SQLite triggers automatically 
execute once per row changed.  All alternatives involve writing your own parser 
for SQL commands.

However, one way to achieve your requirements efficiently is extremely simple: 
just log all UPDATE and INSERT commands.  Save them, plus a timestamp, in a 
file, either a text file or a SQLite database.  When you need to reconstruct 
your database at any date/time, simply replay your transcript up to that 
data/time.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Petite Abeille]

On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote:

> In Postgres world they call it timetravel. 

Time travel? Meh...

Oracle features Total Recall!!! 

http://www.orafaq.com/wiki/Oracle_Total_Recall

In a nutshell:

select *
fromfoo
*as of* a point in time

Oracle Total Recall
http://www.oracle.com/technetwork/database/focus-areas/storage/total-recall-whitepaper-171749.pdf

Got to use that just for the name! :D
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 1:14 PM, Kit wrote:

> 2012/4/16 Puneet Kishor :
>> I am experimenting with a home-grown versioning system where every 
>> "significant" modification to row would be performed on a copy of the row, 
>> the original being preserved.
>> Any other suggestions to achieve a similar functionality would be welcome.
>> --
>> Puneet Kishor
> 
> 1. Use Git or Mercurial


My statement might have been misunderstood. I am not trying to create a 
versioning system a la Git, Mercurial or Fossil. I am trying to create a data 
versioning system so that a query done at a particular time can be reproduced 
identically as to the original query even if the data have been modified in the 
interim time.

So, if a query returns one or more rows today, the same query (that is, the 
same query params with an additional time stamp param) should return exactly 
the same result 3 years from now even if the rows themselves may have been 
modified.

In Postgres world they call it timetravel. See "F.39.2. timetravel — Functions 
for Implementing Time Travel" at 
http://www.postgresql.org/docs/9.1/static/contrib-spi.html for reference.




> 2. Try this:
> 
> CREATE TABLE instance  (
> filename TEXT,
> version INT,
> size INT,
> md5sum TEXT,
> creation_date TEXT,
> last_write_time TEXT,
> PRIMARY KEY(filename,version),
> FOREIGN KEY (md5sum) REFERENCES resource (md5sum)
> );
> 
> CREATE TABLE resource (
> md5sum TEXT,
> data BLOB,
> primary key(md5sum)
>   );




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Richard Hipp]

On Mon, Apr 16, 2012 at 2:14 PM, Kit  wrote:

> 2012/4/16 Puneet Kishor :
> > I am experimenting with a home-grown versioning system where every
> "significant" modification to row would be performed on a copy of the row,
> the original being preserved.
> > Any other suggestions to achieve a similar functionality would be
> welcome.
> > --
> > Puneet Kishor
>
> 1. Use Git or Mercurial
>

SQLite uses Fossil 



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Simon Slavin]

On 16 Apr 2012, at 7:11pm, Puneet Kishor  wrote:

> Thanks. That is one approach I have considered. I will try it out, but I am 
> less enthusiastic about it as it would involve creating a shadow table for 
> every table in the db.

If you can summarise, instead of copying the columns individually, then you 
need only have one shadow table.  Just make the table name a column in the 
shadow table.

> I am planning to try both approaches, evaluate, and choose among them after 
> real experimentation.

Another possibility would be to return to your own approach and simply have 
your software supply the values for new entries instead of making SQLite do it 
with AUTOINCREMENT.  Before each INSERT just do

BEGIN
SELECT max(id)+1 FROM theTable
INSERT ...
END

and supply the value returned from the SELECT in the INSERT command.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Kit]

2012/4/16 Puneet Kishor :
> I am experimenting with a home-grown versioning system where every 
> "significant" modification to row would be performed on a copy of the row, 
> the original being preserved.
> Any other suggestions to achieve a similar functionality would be welcome.
> --
> Puneet Kishor

1. Use Git or Mercurial
2. Try this:

CREATE TABLE instance  (
 filename TEXT,
 version INT,
 size INT,
 md5sum TEXT,
 creation_date TEXT,
 last_write_time TEXT,
 PRIMARY KEY(filename,version),
 FOREIGN KEY (md5sum) REFERENCES resource (md5sum)
 );

CREATE TABLE resource (
 md5sum TEXT,
 data BLOB,
 primary key(md5sum)
   );
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 1:08 PM, Simon Slavin wrote:

> 
> On 16 Apr 2012, at 6:58pm, Puneet Kishor  wrote:
> 
>> I am experimenting with a home-grown versioning system where every 
>> "significant" modification to row would be performed on a copy of the row, 
>> the original being preserved. So, if I have 
>> 
>>  CREATE TABLE t (
>>  id INTEGER,
>>  created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
>>  name TEXT,
>>  is_trivial_update BOOLEAN DEFAULT 0,
>>  PRIMARY KEY (id, created_on)
>>  );
>> 
>> today I can have
>> 
>>  1, 2012-04-16 12:51:00, John, 0
>> 
>> and in the coming days I can make it
>> 
>>  1, 2012-04-16 12:51:00, John, 0
>>  1, 2012-04-17 10:00:00, Johnny, 0
>>  1, 2012-04-17 10:00:00, Johnnie, 1
>>  1, 2012-04-17 22:12:00, John Walker, 0
> 
> Have one table which holds just the current data.  Use the standard primary 
> key mechanism with that table, allowing it to supply an autoincrementing 
> integer primary key for that table.
> 
> Have another table which lists all the changes for the first table.  The 
> primary key for the second table can also be an autoincrementing integer 
> primary key, but that has nothing to do with one with all the current values 
> in it.  The 'id' column of the first table should be a different column of 
> the second table.  Use a TRIGGER mechanism so that every INSERT and UPDATE 
> for the first table makes an entry in the second table.
> 

Thanks. That is one approach I have considered. I will try it out, but I am 
less enthusiastic about it as it would involve creating a shadow table for 
every table in the db. I am planning to try both approaches, evaluate, and 
choose among them after real experimentation.


--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Simon Slavin]

On 16 Apr 2012, at 6:58pm, Puneet Kishor  wrote:

> I am experimenting with a home-grown versioning system where every 
> "significant" modification to row would be performed on a copy of the row, 
> the original being preserved. So, if I have 
> 
>   CREATE TABLE t (
>   id INTEGER,
>   created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
>   name TEXT,
>   is_trivial_update BOOLEAN DEFAULT 0,
>   PRIMARY KEY (id, created_on)
>   );
> 
> today I can have
> 
>   1, 2012-04-16 12:51:00, John, 0
> 
> and in the coming days I can make it
> 
>   1, 2012-04-16 12:51:00, John, 0
>   1, 2012-04-17 10:00:00, Johnny, 0
>   1, 2012-04-17 10:00:00, Johnnie, 1
>   1, 2012-04-17 22:12:00, John Walker, 0

Have one table which holds just the current data.  Use the standard primary key 
mechanism with that table, allowing it to supply an autoincrementing integer 
primary key for that table.

Have another table which lists all the changes for the first table.  The 
primary key for the second table can also be an autoincrementing integer 
primary key, but that has nothing to do with one with all the current values in 
it.  The 'id' column of the first table should be a different column of the 
second table.  Use a TRIGGER mechanism so that every INSERT and UPDATE for the 
first table makes an entry in the second table.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 12:32 PM, Igor Tandetnik wrote:

> On 4/16/2012 12:51 PM, Mr. Puneet Kishor wrote:
>> 
>> On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote:
>> 
>>> You can use:
>>> 
>>> create table t ( id integer primary key autoincrement, created_on
>>> DATETIME DEFAULT CURRENT_TIMESTAMP )
>>> 
>> 
>> No, the above will create a PK on only the 'id' column. I want a composite 
>> PK with 'id' and 'created_on' columns
> 
> Why?  What purpose do you believe a composite key would serve, that would not 
> be served equally well with a primary key on id column alone?
> 


I am experimenting with a home-grown versioning system where every 
"significant" modification to row would be performed on a copy of the row, the 
original being preserved. So, if I have 

CREATE TABLE t (
id INTEGER,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
name TEXT,
is_trivial_update BOOLEAN DEFAULT 0,
PRIMARY KEY (id, created_on)
);

today I can have

1, 2012-04-16 12:51:00, John, 0

and in the coming days I can make it

1, 2012-04-16 12:51:00, John, 0
1, 2012-04-17 10:00:00, Johnny, 0
1, 2012-04-17 10:00:00, Johnnie, 1
1, 2012-04-17 22:12:00, John Walker, 0

Then, I can get the value of id 1 on any given datetime with something like

SELECT name, created_on 
FROM t 
WHERE 
id = 1 AND 
is_trivial_update = 0 AND 
created_on <= '2012-04-17 09:00:00' 
ORDER DESC 
LIMIT 1;

which would yield 

John, 2012-04-16 12:51:00

Any other suggestions to achieve a similar functionality would be welcome.


--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Igor Tandetnik]

On 4/16/2012 12:51 PM, Mr. Puneet Kishor wrote:


On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote:


You can use:

create table t ( id integer primary key autoincrement, created_on
DATETIME DEFAULT CURRENT_TIMESTAMP )



No, the above will create a PK on only the 'id' column. I want a composite PK 
with 'id' and 'created_on' columns


Why?  What purpose do you believe a composite key would serve, that 
would not be served equally well with a primary key on id column alone?


In any case, SQLite only supports AUTOINCREMENT on a column declared 
INTEGER PRIMARY KEY.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Simon Slavin]

On 16 Apr 2012, at 5:27pm, "Mr. Puneet Kishor"  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?

If there was a syntax it would be

CREATE TABLE t (
id INTEGER AUTOINCREMENT,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (id, created_on)
);

so try that.  But the diagram on

http://www.sqlite.org/lang_createtable.html

suggests that AUTOINCREMENT can be used only as part of the PRIMARY KEY 
definition.

Another way to do it might be to use a TRIGGER to look up the current MAX() 
value of the column and add 1 to it.  (I believe you can't do this as a 
DEFAULT.)

So you'd define a TRIGGER on INSERT which looked to see if new.id is NULL and 
if it is, sets new.id to max(id)+1 .  I have no idea whether this would 
actually work.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Mr. Puneet Kishor]

On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote:

> You can use:
> 
> create table t ( id integer primary key autoincrement, created_on
> DATETIME DEFAULT CURRENT_TIMESTAMP )
> 
> 



No, the above will create a PK on only the 'id' column. I want a composite PK 
with 'id' and 'created_on' columns, but 'autoincrement' keyword seems to work 
only with 'primary key' invocation.



> 
> On 04/16/2012 06:27 PM, Mr. Puneet Kishor 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?
>> 
>> 
>> --
>> Puneet Kishor

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Patrik Nilsson]

You can use:

create table t ( id integer primary key autoincrement, created_on
DATETIME DEFAULT CURRENT_TIMESTAMP )

Patrik

On 04/16/2012 06:27 PM, Mr. Puneet Kishor 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?
> 
> 
> --
> Puneet Kishor
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users