Re: [sqlite] SQLite TEA version downgrades when loaded.
On Mon, Apr 16, 2012 at 1:28 AM, Madhur Kashyap physiologo...@gmail.comwrote: 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
[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 (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] 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 times faster thanone? [Kit] 2012/4/15 Steinar Midtskogen stei...@latinitas.org: 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
Re: [sqlite] SELECT statement seems to return incorrect results
Ludovic VP ludovi...@hotmail.com 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] Curious query plan selection
On Sun, Apr 15, 2012 at 2:31 PM, George pinkisntw...@gmail.com 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] Curious query plan selection
On 4/16/12, Richard Hipp d...@sqlite.org 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
On Mon, Apr 16, 2012 at 9:43 AM, George pinkisntw...@gmail.com 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
On 4/16/12, Richard Hipp d...@sqlite.org wrote: On Mon, Apr 16, 2012 at 9:43 AM, George pinkisntw...@gmail.com 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
On Mon, Apr 16, 2012 at 10:31 AM, George pinkisntw...@gmail.com wrote: On 4/16/12, Richard Hipp d...@sqlite.org wrote: On Mon, Apr 16, 2012 at 9:43 AM, George pinkisntw...@gmail.com 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
[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 ___ 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
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
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' 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
On 16 Apr 2012, at 5:27pm, Mr. Puneet Kishor punk.k...@gmail.com 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
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
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
On 16 Apr 2012, at 6:58pm, Puneet Kishor punk.k...@gmail.com 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
On Apr 16, 2012, at 1:08 PM, Simon Slavin wrote: On 16 Apr 2012, at 6:58pm, Puneet Kishor punk.k...@gmail.com 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] 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 ___ 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
2012/4/16 Puneet Kishor punk.k...@gmail.com: I am experimenting with a home-grown versioning system where every significant modification to row would be performed on a copy of the row, the original being preserved. Any other suggestions to achieve a similar functionality would be welcome. -- Puneet Kishor 1. Use Git or Mercurial 2. Try this: CREATE TABLE instance ( filename TEXT, version INT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, PRIMARY KEY(filename,version), FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] auto-incrementing integer in composite primary key
On 16 Apr 2012, at 7:11pm, Puneet Kishor punk.k...@gmail.com 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
On Mon, Apr 16, 2012 at 2:14 PM, Kit kit.sa...@gmail.com wrote: 2012/4/16 Puneet Kishor punk.k...@gmail.com: 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 http://www.fossil-scm.org/ -- 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
On Apr 16, 2012, at 1:14 PM, Kit wrote: 2012/4/16 Puneet Kishor punk.k...@gmail.com: 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
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
On 16 Apr 2012, at 7:29pm, Puneet Kishor punk.k...@gmail.com 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
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 punk.k...@gmail.com wrote: On Apr 16, 2012, at 1:14 PM, Kit wrote: 2012/4/16 Puneet Kishor punk.k...@gmail.com: 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
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
On Mon, Apr 16, 2012 at 12:58 PM, Puneet Kishor punk.k...@gmail.com 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
2012/4/16 Puneet Kishor punk.k...@gmail.com: 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
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
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
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
On Mon, Apr 16, 2012 at 3:30 PM, Petite Abeille petite.abei...@gmail.com 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
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 punk.k...@gmail.com 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
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 punk.k...@gmail.com 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
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
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
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
2012/4/16 Petite Abeille petite.abei...@gmail.com: 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_ontime_xx GROUP BY t.doc_id HAVING created_on=max(created_on); -- 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
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_ontime_xx GROUP 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
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
On 16 Apr 2012, at 10:31pm, Peter Aronson pbaron...@att.net 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: http://www.dcs.warwick.ac.uk/~hugh/TTM/TemporalData.Warwick.pdf 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
On Mon, Apr 16, 2012 at 5:04 PM, Simon Slavin slav...@bigfraud.org wrote: On 16 Apr 2012, at 10:31pm, Peter Aronson pbaron...@att.net 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: http://www.dcs.warwick.ac.uk/~hugh/TTM/TemporalData.Warwick.pdf 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
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
On Apr 16, 2012, at 5:04 PM, Simon Slavin wrote: On 16 Apr 2012, at 10:31pm, Peter Aronson pbaron...@att.net 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: http://www.dcs.warwick.ac.uk/~hugh/TTM/TemporalData.Warwick.pdf 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
On 16 Apr 2012, at 11:25pm, Puneet Kishor punk.k...@gmail.com 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 n...@cryptonector.com 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 petite.abei...@gmail.com 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
On Apr 16, 2012, at 5:51 PM, Simon Slavin wrote: On 16 Apr 2012, at 11:25pm, Puneet Kishor punk.k...@gmail.com 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
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
On 17 Apr 2012, at 12:33am, Petite Abeille petite.abei...@gmail.com 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
On Mon, Apr 16, 2012 at 8:00 PM, Simon Slavin slav...@bigfraud.org wrote: On 17 Apr 2012, at 12:33am, Petite Abeille petite.abei...@gmail.com 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