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] Why are two select statements 2000 times faster than one?

[Steinar Midtskogen]

To answer my own question, whether there is an efficient way to find
max() of an increasingly sorted column in a virtual array: What is
needed is to make sure that xBestIndex sets orderByConsumed, and that
the module takes care of all sorting.

-- 
Steinar
___
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

[sqlite] auto-incrementing integer in composite primary key

[Mr. Puneet Kishor]

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] Curious query plan selection

[Richard Hipp]

On Mon, Apr 16, 2012 at 10:31 AM, George  wrote:

> On 4/16/12, Richard Hipp  wrote:
> > On Mon, Apr 16, 2012 at 9:43 AM, George  wrote:
> >
> >>
> >> create table if not exists SnapshotsMarketsRunners(
> >>ID integer primary key,
> >>SnapshotsID integer not null references Snapshots(ID),
> >>MarketsRunnersID not null references MarketsRunners(ID),
> >>
> >
> > Specify type "integer" on the MarketRunnersID column
> >
>
> Thanks, it works now. I had omitted the type name by mistake. I assume
> that the absence of data type specification means that the column can
> accept any data, is this correct?
>

Correct.  That means that you could have inserted a string '123' instead of
a number 123, and the string form would have been retained.  And because of
that, the index could not bee used to do the lookup.


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



-- 
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] Curious query plan selection

[George]

On 4/16/12, Richard Hipp  wrote:
> On Mon, Apr 16, 2012 at 9:43 AM, George  wrote:
>
>>
>> create table if not exists SnapshotsMarketsRunners(
>>ID integer primary key,
>>SnapshotsID integer not null references Snapshots(ID),
>>MarketsRunnersID not null references MarketsRunners(ID),
>>
>
> Specify type "integer" on the MarketRunnersID column
>

Thanks, it works now. I had omitted the type name by mistake. I assume
that the absence of data type specification means that the column can
accept any data, is this correct?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious query plan selection

[Richard Hipp]

On Mon, Apr 16, 2012 at 9:43 AM, George  wrote:

>
> create table if not exists SnapshotsMarketsRunners(
>ID integer primary key,
>SnapshotsID integer not null references Snapshots(ID),
>MarketsRunnersID not null references MarketsRunners(ID),
>

Specify type "integer" on the MarketRunnersID column



>LastPriceMatched real
> );
>

-- 
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] Curious query plan selection

[George]

On 4/16/12, Richard Hipp  wrote:

> Hard to say why, without knowing your schema.

Here is the schema:

create table if not exists Errors(
ID integer primary key,
Timestamp text not null,
ErrorCode text,
Name text,
RequestName text,
Response text,
ErrorText text
);

create table if not exists Markets(
ID integer primary key,
StartTimestamp text not null,
Name text not null,
RecordingPID text unique,
RecorderInterval real check (RecorderInterval >= 0.05),
IsDone integer not null check(IsDone in (0,1))
);

create table if not exists Runners(
ID integer primary key
);

create table if not exists MarketsRunners(
ID integer primary key,
RunnersID integer not null references Runners(ID),
MarketsID integer not null references Markets(ID)
);

create table if not exists Snapshots(
ID integer primary key,
Timestamp text not null
);

create table if not exists SnapshotsMarketsRunners(
ID integer primary key,
SnapshotsID integer not null references Snapshots(ID),
MarketsRunnersID not null references MarketsRunners(ID),
LastPriceMatched real
);

create table if not exists AvailablePrices(
ID integer primary key,
Price real not null check (Price > 1.),
VolumeAvailable real not null check (VolumeAvailable > 0),
BackOrLay text check (BackOrLay in ('B', 'L')),
SnapshotsMarketsRunnersID integer not null references
SnapshotsMarketsRunners(ID)
);

create unique index if not exists MarketsRunnersRunnersIDMarketsID on
MarketsRunners(RunnersID, MarketsID);

create index if not exists MarketsRunnersMarketsID on MarketsRunners(MarketsID);

create index if not exists AvailablePricesSnapshotsMarketsRunnersID on
AvailablePrices(SnapshotsMarketsRunnersID);

create index if not exists SnapshotsTimestamp on Snapshots(Timestamp);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice1 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay, Price);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice2 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay desc, Price
desc);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice3 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay, Price desc);

create index if not exists
AvailablePricesSnapshotsMarketsRunnersIDBackOrLayPrice4 on
AvailablePrices(SnapshotsMarketsRunnersID, BackOrLay desc, Price);

create index if not exists SnapshotsIDTimestamp on Snapshots(ID, Timestamp);

create unique index if not exists
SnapshotsMarketsRunnersSnapshotsIDMarketsRunnersID on
SnapshotsMarketsRunners(SnapshotsID, MarketsRunnersID);

create unique index if not exists
SnapshotsMarketsRunnersMarketsRunnersIDSnapshotsID on
SnapshotsMarketsRunners(MarketsRunnersID, SnapshotsID);

create unique index if not exists
AvailablePricesUniquePriceSnapshotsMarketsRunnersID on
AvailablePrices(Price, SnapshotsMarketsRunnersID);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Curious query plan selection

[Richard Hipp]

On Sun, Apr 15, 2012 at 2:31 PM, George  wrote:

> Compare the following two queries and their query plans:
>
> 1) explain query plan select * from snapshotsmarketsrunners where marketsru
> nnersid in (1);
>
> 0|0|0|SEARCH TABLE snapshotsmarketsrunners USING INDEX
> SnapshotsMarketsRunnersMa
> rketsRunnersIDSnapshotsID (MarketsRunnersID=?) (~10 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
> 2) explain query plan select * from snapshotsmarketsrunners where marketsru
> nnersid in (select id from marketsrunners where marketsid = 105195390);
>
> 0|0|0|SCAN TABLE snapshotsmarketsrunners (~223868 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 1|0|0|SEARCH TABLE marketsrunners USING COVERING INDEX
> MarketsRunnersMarketsID (
> MarketsID=?) (~10 rows)
>
> Why is the index used in the first query but not in the second?


Hard to say why, without knowing your schema.



> The
> same thing happens if I rewrite query (2) using JOIN syntax:
>
> explain query plan select * from snapshotsmarketsrunners join marketsrun
> ners on marketsrunnersid = marketsrunners.id where marketsid = 105195390;
>
> 0|0|0|SCAN TABLE snapshotsmarketsrunners (~2238685 rows)
> 0|1|1|SEARCH TABLE marketsrunners USING INTEGER PRIMARY KEY (rowid=?) (~1
> rows)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] SELECT statement seems to return incorrect results

[Igor Tandetnik]

Ludovic VP  wrote:
> REF: 
> http://stackoverflow.com/questions/10171403/why-does-select-results-differ-between-mysql-and-sqlite
> Consider these statements:
> create table foo (id INT, score INT);
> insert into foo values (106, 4);insert into foo values (107, 3);insert into 
> foo values (106, 5);insert into foo values (107, 5);
> select T1.id, avg(T1.score) avg1from foo T1group by T1.idhaving not exists ( 
> select T2.id, avg(T2.score) avg2 from foo T2 group
> by T2.id having avg2> avg1); 
> 
> The select statement returns:id avg1 -- --106 4.5 107 4.0
> Shouldn't it return:id avg1 -- --106 4.5
> instead?

Looks like a bug to me, for what it's worth.
-- 
Igor Tandetnik

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

Re: [sqlite] Why are two select statements 2000 times faster thanone?

[ProgenyUSA]

Testing for my e mail address 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Steinar Midtskogen
Sent: 15 April 2012 18:38
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Why are two select statements 2000 times faster
thanone?

[Kit]

> 2012/4/15 Steinar Midtskogen :
>> So, is there really a way to create an index in a virtual table, or a 
>> way to emulate this?
>
> Why? You don't need this. Use index on base tables.

My base tables are indexed.  Let's say I want to make a very simple virtual
table this way:

 create virtual table vtab using copy(indexed_table);

which simply maps any query for vtab to indexed_table and returns that.
So let's say that indexed_table have an integer column "key" which also a
primary key.  So "select max(key) from indexed_table" will be fast no matter
how big it is and the module can find this value in a blink.  What I would
like to is to have "select max(key) from vtab"
run fast as well, without having to run through the billion rows in index.

So what happens when I run "select max(key) from vtab"?  Well, all xFilter
will know is that it needs to produce the "key" column, and there should be
a "order by key" clause as well, but even if we can assume that what we're
dealing with is a sorted column, and xFilter could look up the max in no
time, xFilter doesn't know that the query is for the max value.  Can my
module do anything better than to produce all the rows for sqlite to feed
into the max aggregate function?

>> My xRowid function simply returns the value of the "unix_time" 
>> column, but even "select max(rowid)" is equally slow.
>> Steinar
>
> Why you need "select max(rowid)"? Something is wrong in your data 
> design. Use autoincrement.

I don't need it, but a virtual table must provide one.  I'm not sure why.

--
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1901 / Virus Database: 2411/4938 - Release Date: 04/15/12

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

[sqlite] SELECT statement seems to return incorrect results

[Ludovic VP]

Hi,
REF: 
http://stackoverflow.com/questions/10171403/why-does-select-results-differ-between-mysql-and-sqlite
Consider these statements:
create table foo (id INT, score INT);
insert into foo values (106, 4);insert into foo values (107, 3);insert into foo 
values (106, 5);insert into foo values (107, 5);
select T1.id, avg(T1.score) avg1from foo T1group by T1.idhaving not exists (    
select T2.id, avg(T2.score) avg2    from foo T2    group by T2.id    having 
avg2> avg1);

The select statement returns:id          avg1      --  --106    
     4.5       107         4.0     
Shouldn't it return:id          avg1      --  --106         4.5 
      
instead?
I'm using 3.7.7.1
Kindly,
Ludovic Vaugeois-Pepin
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite TEA version downgrades when loaded.

[Richard Hipp]

On Mon, Apr 16, 2012 at 1:28 AM, Madhur Kashyap wrote:

> Hello,
>
> I had compiled TEA (Tcl Extension) on a standard RHEL5 image with Tcl 8.4
> present in it as default. If I load the compiled .so file on a regular
> tclsh the version shown is
>
> % dbcmd version
> 3.7.10
>
> But if I do the same on a commercial EDA tool which uses Tcl has the user
> interface, it shows
>
> encounter 8> dbcmd version
> 3.5.9
>
> Furthermore, when I use "restore" command for a database created on tclsh
> (presumably 3.7.10) on regular tclsh, it works well. But when I use the
> same database on the commercial EDA tool shell, it causes the a core dump.
> So, I have two queries
>
> (1) What is the reason for this downgrading of version when loaded within
> EDA tool shell ? And any hints on fixing this behavior ?
>

Maybe the EDA tool shell has SQLite built in and hence is refusing to load
your shared library?  Or, maybe you are not giving the "load" command a
full pathname and the EDA tool is finding a much older SQLite library than
the one you intend?


> (2) Is the core dump issue related to above difference in versions ?


Probably.


> Reason
> I ask it is because, I tried a different approach to copy all tables
> instead and it seems to work well. Though I did not try saving and
> restoring from within EDA tool shell yet.
>
> Thanks for your help !
>
> Regards,
> Madhur
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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