Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Simon Slavin]

On 10 Mar 2011, at 7:57am, Mohd Radzi Ibrahim wrote:

> So, that's explain why my column ID INTEGER PRIMARY KEY, changes when I use 
> REPLACE. I could not rely on the ID with this command. 

Yes.   You explicitly said 'REPLACE' so that's what it's doing.  If you want to 
modify an existing record use the UPDATE command.

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Mohd Radzi Ibrahim]

On 10-Mar-2011, at 6:52 AM, Jay A. Kreibich wrote:

> On Wed, Mar 09, 2011 at 11:37:46PM +0100, Armin Kunaschik scratched on the 
> wall:
>> On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik  wrote:
> 
>>> INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.
> 
>> Is it really DELETE and INSERT internally?
> 
>  Yes and no.  "REPLACE" alters the conflict resolution on UNIQUE
>  constraints.  In all cases, the new row is INSERTed.  In the case of
>  a REPLACE, if the INSERT would cause a UNIQUE constraint violation
>  than any and all rows that are part of the conflict are first
>  deleted before the new row is INSERTed.  In some situations a single
>  INSERT OR REPLACE can actually cause multiple existing rows to be
>  deleted before the new row is inserted.
> 
>  So it is always an INSERT, but sometimes the INSERT triggers one or
>  more internal DELETEs first.


So, that's explain why my column ID INTEGER PRIMARY KEY, changes when I use 
REPLACE. I could not rely on the ID with this command. 

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Jay A. Kreibich]

On Wed, Mar 09, 2011 at 11:37:46PM +0100, Armin Kunaschik scratched on the wall:
> On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik  wrote:

> > INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.

> Is it really DELETE and INSERT internally?

  Yes and no.  "REPLACE" alters the conflict resolution on UNIQUE
  constraints.  In all cases, the new row is INSERTed.  In the case of
  a REPLACE, if the INSERT would cause a UNIQUE constraint violation
  than any and all rows that are part of the conflict are first
  deleted before the new row is INSERTed.  In some situations a single
  INSERT OR REPLACE can actually cause multiple existing rows to be
  deleted before the new row is inserted.

  So it is always an INSERT, but sometimes the INSERT triggers one or
  more internal DELETEs first.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

On Wed, Mar 9, 2011 at 11:44 PM, Simon Slavin  wrote:
>
> On 9 Mar 2011, at 10:37pm, Armin Kunaschik wrote:
>
>> Is it really DELETE and INSERT internally?
>
> As far as you can tell when how SQLite behaves, yes it is.
>
>> This would explain, why ctime is "invisible" inside the trigger...
>> it's simply not there
>> when replace is used.
>>
>> Maybe I should think about using a temporary table for that purpose...
>> any ideas?
>
> Why use a temporary table for it.  You appear to have two sets of data which 
> should not be in the same table.  One associates your primary key (the date) 
> with ctime, and another associates your primary key with text and mtime.  So 
> make two tables.  In one you use INSERT OR FAIL, in the other you use INSERT 
> OR REPLACE.

Great idea! In conjunction with the DEFAULT this would make things far easier.
And having the date in 2 tables instead of one is no disadvantage here.
I'll give it a try...

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Simon Slavin]

On 9 Mar 2011, at 10:37pm, Armin Kunaschik wrote:

> Is it really DELETE and INSERT internally?

As far as you can tell when how SQLite behaves, yes it is.

> This would explain, why ctime is "invisible" inside the trigger...
> it's simply not there
> when replace is used.
> 
> Maybe I should think about using a temporary table for that purpose...
> any ideas?

Why use a temporary table for it.  You appear to have two sets of data which 
should not be in the same table.  One associates your primary key (the date) 
with ctime, and another associates your primary key with text and mtime.  So 
make two tables.  In one you use INSERT OR FAIL, in the other you use INSERT OR 
REPLACE.

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Jay A. Kreibich]

On Wed, Mar 09, 2011 at 11:19:51PM +0100, Armin Kunaschik scratched on the wall:

> Important: I also want to be able to "insert or replace" rows and keep ctime.

> The update trigger works fine, but the insert trigger ALWAYS updates ctime.

  As Igor pointed out, "INSERT OR REPLACE" is called "INSERT OR **REPLACE**"
  not "INSERT OR UPDATE."  The old row is completely DELETEed before the
  new row is INSERTed.  There is no UPDATE in a INSERT OR REPLACE.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik  wrote:
> On 3/9/2011 5:19 PM, Armin Kunaschik wrote:
>> I'm trying this for quite some time... and I'm totally stuck.
>>
>> I have the following table:
>>
>> CREATE TABLE example(
>>   date integer primary key not null,
>>   text text,
>>   ctime TIMESTAMP,
>>   mtime TIMESTAMP);
>>
>> ctime=creation time (should be set only once)
>> mtime=modification time (should be set every time the row is updated)
>>
>> Important: I also want to be able to "insert or replace" rows and keep ctime.
>
> INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.
> I don't see how you can preserve ctime through that.
Is it really DELETE and INSERT internally?
This would explain, why ctime is "invisible" inside the trigger...
it's simply not there
when replace is used.

Maybe I should think about using a temporary table for that purpose...
any ideas?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Igor Tandetnik]

On 3/9/2011 5:19 PM, Armin Kunaschik wrote:
> I'm trying this for quite some time... and I'm totally stuck.
>
> I have the following table:
>
> CREATE TABLE example(
>   date integer primary key not null,
>   text text,
>   ctime TIMESTAMP,
>   mtime TIMESTAMP);
>
> ctime=creation time (should be set only once)
> mtime=modification time (should be set every time the row is updated)
>
> Important: I also want to be able to "insert or replace" rows and keep ctime.

INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT. 
I don't see how you can preserve ctime through that.
-- 
Igor Tandetnik

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

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

On Wed, Mar 9, 2011 at 11:21 PM, Mr. Puneet Kishor  wrote:
>
> On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote:
>
>> Hi there,
>>
>> I'm trying this for quite some time... and I'm totally stuck.
>>
>> I have the following table:
>>
>> CREATE TABLE example(
>>  date integer primary key not null,
>>  text text,
>>  ctime TIMESTAMP,
>>  mtime TIMESTAMP);
>>
>> ctime=creation time (should be set only once)
>> mtime=modification time (should be set every time the row is updated)
>>
>
> change ctime definition to
>
> ctime DEFAULT CURRENT_TIMESTAMP
>
> and use trigger for only the mtime
>
Unfortunately this will overwrite ctime with the new current time :-(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

[Mr. Puneet Kishor]

On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote:

> Hi there,
> 
> I'm trying this for quite some time... and I'm totally stuck.
> 
> I have the following table:
> 
> CREATE TABLE example(
>  date integer primary key not null,
>  text text,
>  ctime TIMESTAMP,
>  mtime TIMESTAMP);
> 
> ctime=creation time (should be set only once)
> mtime=modification time (should be set every time the row is updated)
> 

change ctime definition to

ctime DEFAULT CURRENT_TIMESTAMP

and use trigger for only the mtime


> Important: I also want to be able to "insert or replace" rows and keep ctime.
> 
> Currently I'm trying with 2 triggers:
> 
> CREATE TRIGGER insert_example_timestamp AFTER INSERT ON example
> BEGIN
>  UPDATE example SET ctime = DATETIME('now','localtime')
>  WHERE ( rowid = new.rowid AND ctime IS NULL);
> END;
> 
> CREATE TRIGGER update_example_timestamp AFTER UPDATE ON example
> BEGIN
>  UPDATE example SET mtime=DATETIME('now','localtime') WHERE rowid = new.rowid;
> END;
> 
> The update trigger works fine, but the insert trigger ALWAYS updates ctime.
> I tried various combinations to query a unset/empty ctime and only
> update if ctime is NULL or ''.
> It doesn't work inside the trigger.
> Either it updates always or never.
> Where is my mistake?
> 
> Is there another way to achieve this? Maybe without triggers?
> 
> Many thanks!
> ___
> 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] Creation and modification timestamps with "insert or replace" possible?

[Armin Kunaschik]

Hi there,

I'm trying this for quite some time... and I'm totally stuck.

I have the following table:

CREATE TABLE example(
 date integer primary key not null,
 text text,
 ctime TIMESTAMP,
 mtime TIMESTAMP);

ctime=creation time (should be set only once)
mtime=modification time (should be set every time the row is updated)

Important: I also want to be able to "insert or replace" rows and keep ctime.

Currently I'm trying with 2 triggers:

CREATE TRIGGER insert_example_timestamp AFTER INSERT ON example
BEGIN
 UPDATE example SET ctime = DATETIME('now','localtime')
 WHERE ( rowid = new.rowid AND ctime IS NULL);
END;

CREATE TRIGGER update_example_timestamp AFTER UPDATE ON example
BEGIN
 UPDATE example SET mtime=DATETIME('now','localtime') WHERE rowid = new.rowid;
END;

The update trigger works fine, but the insert trigger ALWAYS updates ctime.
I tried various combinations to query a unset/empty ctime and only
update if ctime is NULL or ''.
It doesn't work inside the trigger.
Either it updates always or never.
Where is my mistake?

Is there another way to achieve this? Maybe without triggers?

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