On Mon, Apr 16, 2012 at 8:00 PM, Simon Slavin wrote:
> On 17 Apr 2012, at 12:33am, Petite Abeille wrote:
>> On Apr 17, 2012, at 12:51 AM, Simon Slavin wrote:
>>> Now, consider what it takes if you're logging value changes instead of
>>> commands issued. Your first problem is figuring out which
On 17 Apr 2012, at 12:33am, Petite Abeille wrote:
> On Apr 17, 2012, at 12:51 AM, Simon Slavin wrote:
>
>> Now, consider what it takes if you're logging value changes instead of
>> commands issued. Your first problem is figuring out which rows exist.
>
> Why? Each row is time boxed. There is
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 tr
On Apr 16, 2012, at 5:51 PM, Simon Slavin wrote:
>
> On 16 Apr 2012, at 11:25pm, Puneet Kishor wrote:
>
>> I absolutely don't get any of the above. Why is "keeping the data" worse
>> than keeping the commands? I am not even sure what is a command vs. what is
>> a data. A SQL command has data
On 16 Apr 2012, at 11:25pm, Puneet Kishor wrote:
> I absolutely don't get any of the above. Why is "keeping the data" worse than
> keeping the commands? I am not even sure what is a command vs. what is a
> data. A SQL command has data embedded in it. I mean, if I have
>
> UPDATE t
>
On Apr 16, 2012, at 5:04 PM, Simon Slavin wrote:
>
> On 16 Apr 2012, at 10:31pm, Peter Aronson wrote:
>
>> You might want to look at the book Temporal Data and the Relational Model by
>> Date, Darwin and Lorentzos, which goes into the subject in fairly great
>> detail.
>> There are subtlet
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 your
On Mon, Apr 16, 2012 at 5:04 PM, Simon Slavin wrote:
> On 16 Apr 2012, at 10:31pm, Peter Aronson wrote:
>> You might want to look at the book Temporal Data and the Relational Model by
>> Date, Darwin and Lorentzos, which goes into the subject in fairly great
>> detail.
>> There are subtleties.
>
On 16 Apr 2012, at 10:31pm, Peter Aronson wrote:
> You might want to look at the book Temporal Data and the Relational Model by
> Date, Darwin and Lorentzos, which goes into the subject in fairly great
> detail.
> There are subtleties.
Doctor Darwen teaches this as a course. You might like
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@sqlit
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
2012/4/16 Petite Abeille :
> On Apr 16, 2012, at 9:09 PM, Kit wrote:
>> SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id
>> WHERE doc.id=id_xx AND created_on<=time_xx
>> ORDER BY created_on DESC LIMIT 1;
> - how do you represent deleted rows?
I will create a new record w
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 ro
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 B
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 <= :
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 Iv
> 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
On Mon, Apr 16, 2012 at 3:30 PM, Petite Abeille
wrote:
>
> On Apr 16, 2012, at 9:09 PM, Kit wrote:
>
>> SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id
>> WHERE doc.id=id_xx AND created_on<=time_xx
>> ORDER BY created_on DESC LIMIT 1;
>
> - how do you represent deleted r
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 modifi
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
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 historizatio
2012/4/16 Puneet Kishor :
> I am trying to create a data versioning system so that a query done at a
> particular time can be reproduced identically as to the original query even
> if the data have been modified in the interim time.
CREATE TABLE doc (
id INTEGER PRIMARY KEY autoincrement,
On Mon, Apr 16, 2012 at 12:58 PM, Puneet Kishor wrote:
> I am experimenting with a home-grown versioning system where every
> "significant" modification to row would be performed on a copy of the row,
> the original being preserved. So, if I have
There are several ways to handle this.
You coul
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
> 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
On 16 Apr 2012, at 7:29pm, Puneet Kishor wrote:
> So, if a query returns one or more rows today, the same query (that is, the
> same query params with an additional time stamp param) should return exactly
> the same result 3 years from now even if the rows themselves may have been
> modified.
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.orac
On Apr 16, 2012, at 1:14 PM, Kit wrote:
> 2012/4/16 Puneet Kishor :
>> I am experimenting with a home-grown versioning system where every
>> "significant" modification to row would be performed on a copy of the row,
>> the original being preserved.
>> Any other suggestions to achieve a similar
On Mon, Apr 16, 2012 at 2:14 PM, Kit wrote:
> 2012/4/16 Puneet Kishor :
> > I am experimenting with a home-grown versioning system where every
> "significant" modification to row would be performed on a copy of the row,
> the original being preserved.
> > Any other suggestions to achieve a simila
On 16 Apr 2012, at 7:11pm, Puneet Kishor wrote:
> Thanks. That is one approach I have considered. I will try it out, but I am
> less enthusiastic about it as it would involve creating a shadow table for
> every table in the db.
If you can summarise, instead of copying the columns individually
2012/4/16 Puneet Kishor :
> I am experimenting with a home-grown versioning system where every
> "significant" modification to row would be performed on a copy of the row,
> the original being preserved.
> Any other suggestions to achieve a similar functionality would be welcome.
> --
> Puneet Ki
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
__
On Apr 16, 2012, at 1:08 PM, Simon Slavin wrote:
>
> On 16 Apr 2012, at 6:58pm, Puneet Kishor wrote:
>
>> I am experimenting with a home-grown versioning system where every
>> "significant" modification to row would be performed on a copy of the row,
>> the original being preserved. So, if I
On 16 Apr 2012, at 6:58pm, Puneet Kishor wrote:
> I am experimenting with a home-grown versioning system where every
> "significant" modification to row would be performed on a copy of the row,
> the original being preserved. So, if I have
>
> CREATE TABLE t (
> id INTEGE
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 )
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
On 16 Apr 2012, at 5:27pm, "Mr. Puneet Kishor" wrote:
> Given
>
> CREATE TABLE t (
> id INTEGER NOT NULL,
> created_on DATETIME DEFAULT CURRENT_TIMESTAMP
> PRIMARY KEY (id, created_on)
> );
>
> how can I make just the 'id' column auto-incre
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' colum
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
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
On Mon, Apr 16, 2012 at 10:31 AM, George wrote:
> On 4/16/12, Richard Hipp wrote:
> > On Mon, Apr 16, 2012 at 9:43 AM, George wrote:
> >
> >>
> >> create table if not exists SnapshotsMarketsRunners(
> >>ID integer primary key,
> >>SnapshotsID integer not null references Snapshot
On 4/16/12, Richard Hipp wrote:
> On Mon, Apr 16, 2012 at 9:43 AM, George wrote:
>
>>
>> create table if not exists SnapshotsMarketsRunners(
>>ID integer primary key,
>>SnapshotsID integer not null references Snapshots(ID),
>>MarketsRunnersID not null references MarketsRun
On Mon, Apr 16, 2012 at 9:43 AM, George wrote:
>
> create table if not exists SnapshotsMarketsRunners(
>ID integer primary key,
>SnapshotsID integer not null references Snapshots(ID),
>MarketsRunnersID not null references MarketsRunners(ID),
>
Specify type "integer" on th
On 4/16/12, Richard Hipp wrote:
> Hard to say why, without knowing your schema.
Here is the schema:
create table if not exists Errors(
ID integer primary key,
Timestamp text not null,
ErrorCode text,
Name text,
RequestName text,
Response text,
On Sun, Apr 15, 2012 at 2:31 PM, George wrote:
> Compare the following two queries and their query plans:
>
> 1) explain query plan select * from snapshotsmarketsrunners where marketsru
> nnersid in (1);
>
> 0|0|0|SEARCH TABLE snapshotsmarketsrunners USING INDEX
> SnapshotsMarketsRunnersMa
>
Ludovic VP wrote:
> REF:
> http://stackoverflow.com/questions/10171403/why-does-select-results-differ-between-mysql-and-sqlite
> Consider these statements:
> create table foo (id INT, score INT);
> insert into foo values (106, 4);insert into foo values (107, 3);insert into
> foo values (106, 5);
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 time
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 (10
On Mon, Apr 16, 2012 at 1:28 AM, Madhur Kashyap wrote:
> Hello,
>
> I had compiled TEA (Tcl Extension) on a standard RHEL5 image with Tcl 8.4
> present in it as default. If I load the compiled .so file on a regular
> tclsh the version shown is
>
> % dbcmd version
> 3.7.10
>
> But if I do the same
49 matches
Mail list logo