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

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

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

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

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

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

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

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

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

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

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

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.

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

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 >

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 <=

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

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 ar

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

> 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

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

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

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

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

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

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

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

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. >

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

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

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

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

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

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

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

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 ( >

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

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

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

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

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

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

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

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'

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

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