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

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

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

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

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

[sqlite] auto-incrementing integer in composite primary key

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

Re: [sqlite] Curious query plan selection

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

Re: [sqlite] Curious query plan selection

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), >>

Re: [sqlite] Curious query plan selection

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), >

Re: [sqlite] Curious query plan selection

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,

Re: [sqlite] Curious query plan selection

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 >

Re: [sqlite] SELECT statement seems to return incorrect results

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

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

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

[sqlite] SELECT statement seems to return incorrect results

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

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

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