[sqlite] ON INSERT trigger

[Alex]

Hi,

In my project I have two databases: primary which is an SQLite db, and
secondary which is based on a different engine. A table in the primary db
contains values which are keys for the secondary db. To maintain coherence
of the two databases I use SQLite triggers. The DELETE and UPDATE commands
are handled fine, but INSERT causes a problem.

The point is that if the OR REPLACE clause is specified, INSERT may
overwrite an existing row instead of inserting a new one. In this case the
appropriate record in the secondary db must be deleted. However, I cannot do
it because OLD is not available inside the INSERT trigger regardless of the
operation actually performed.

What is worse, the SQL part of the project is done by a quite different
person, so no restrictions on SQL syntax used may be imposed. The solution I
need must be universal.

So, before starting to invent a workaround I'd like to consult with you,
people. I hope, I'm not the first person who encountered this problem. Any
suggestions are highly appriciated.

Thanks,
Alex

[Christian Smith]

Post by Alex
Hi,
The point is that if the OR REPLACE clause is specified, INSERT may
overwrite an existing row instead of inserting a new one. In this case the
appropriate record in the secondary db must be deleted. However, I cannot do
it because OLD is not available inside the INSERT trigger regardless of the
operation actually performed.

If there is an "OR REPLACE" clause on your primary key, then you know
exactly which row to delete from the secondary db. It will be the one with
the same primary key as the new row. So, for each insert, just delete the
row from the secondary db with the same key. If it doesn't exist, you'll
have lost nothing (a bit of time, perhaps, as you'll have to do a negative
index scan.) Then just insert the data as you would normally do.

Post by Alex
Thanks,
Alex

Christian

[Alex]

Hi, Christian

Thanks for your response. The situation, however, is more complicated.
Please, consider an example in SQL:

CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)

INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')

INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')

In the given example the "UNIQUE" constraint will cause the row to be
overwritten, and the "A0B1C2D3" key will be lost. And it seems there is no
way to intercept this deletion. The ROWID will also be changed, so it cannot
be used for tracking purposes. If anyone knows a solution, please, advice.

Thanks,
Alex

Post by Christian Smith
If there is an "OR REPLACE" clause on your primary key, then you know
exactly which row to delete from the secondary db. It will be the one with
the same primary key as the new row. So, for each insert, just delete the
row from the secondary db with the same key. If it doesn't exist, you'll
have lost nothing (a bit of time, perhaps, as you'll have to do a negative
index scan.) Then just insert the data as you would normally do.
Christian

[D***@public.gmane.org]

Post by Alex
CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')
In the given example the "UNIQUE" constraint will cause the row to be
overwritten, and the "A0B1C2D3" key will be lost. And it seems there is no
way to intercept this deletion. The ROWID will also be changed, so it cannot
be used for tracking purposes. If anyone knows a solution, please, advice.

If I understand properly what you're trying to do, then you can probably add
an automatically-maintained map table, something like this:

CREATE TABLE table (id TEXT PRIMARY KEY,
name TEXT,
surname TEXT,
secDbKey TEXT);

CREATE TABLE map (id TEXT PRIMARY KEY,
secDbKey TEXT);

CREATE TABLE SecDb (secDbKey TEXT PRIMARY KEY);

CREATE TRIGGER table_insert_tr AFTER INSERT ON table
FOR EACH ROW
BEGIN
DELETE FROM SecDb WHERE secDbKey =
(SELECT secDbKey FROM map WHERE id = new.id);
INSERT OR REPLACE INTO map VALUES (new.id, new.secDbKey);
END

Note that the PRIMARY KEY definitions are necessary to ensure that the correct
rowid (which is also 'id') is overwritten on an INSERT OR REPLACE query.

Derrell

[Dennis Cote]

Post by Alex
Hi, Christian
Thanks for your response. The situation, however, is more complicated.
CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker',
'E4F51234')
In the given example the "UNIQUE" constraint will cause the row to be
overwritten, and the "A0B1C2D3" key will be lost. And it seems there
is no way to intercept this deletion. The ROWID will also be changed,
so it cannot be used for tracking purposes. If anyone knows a
solution, please, advice.
Thanks,
Alex

Post by Christian Smith
If there is an "OR REPLACE" clause on your primary key, then you know
exactly which row to delete from the secondary db. It will be the
one with the same primary key as the new row. So, for each insert,
just delete the row from the secondary db with the same key. If it
doesn't exist, you'll have lost nothing (a bit of time, perhaps, as
you'll have to do a negative index scan.) Then just insert the data
as you would normally do.
Christian

Alex,

Christian's proposal doesn't work for you because you are storing a
reference to the secondary DB in the primary DB. If you link your table the
other way, by storing the a reference to the primary DB in the corresponding
secondary DB record, it will work as he described.

You could also check if the insert is going to result in a replacement by
doing a select for the primary id before doing the insert. If it exists,
then delete the corresponding record from the secondary DB before executing
the insert (which you know will be replacing the existing record).

[Christian Smith]

Post by Alex
Hi, Christian
Thanks for your response. The situation, however, is more complicated.
CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')
In the given example the "UNIQUE" constraint will cause the row to be
overwritten, and the "A0B1C2D3" key will be lost. And it seems there is no
way to intercept this deletion. The ROWID will also be changed, so it cannot
be used for tracking purposes. If anyone knows a solution, please, advice.

What would you be planning to do with this "SecDbKey" column? There's
nothing stopping you retrieving the current value from table.

But I think the gist of you email is that you'd prefer the "INSERT OR
REPLACE" to be handled like an update. I can't see a reason why an OLD
record could be created for replace inserts. The problem then becomes how
do you know in the trigger whether OLD is valid? If the insert is a
straight insert, then OLD will not be valid, and you'll probably get an
exception trying to use it.

In which case it might be better to convert the replace insert into an
update, and use the update trigger which is guaranteed to have valid OLD
and NEW.

But that is not my decision to make. Create a new enhancement ticket in
CVSTrac.

Post by Alex
Thanks,
Alex

Post by Christian Smith
If there is an "OR REPLACE" clause on your primary key, then you know
exactly which row to delete from the secondary db. It will be the one with
the same primary key as the new row. So, for each insert, just delete the
row from the secondary db with the same key. If it doesn't exist, you'll
have lost nothing (a bit of time, perhaps, as you'll have to do a negative
index scan.) Then just insert the data as you would normally do.
Christian

[Ron Aaron]

Post by Christian Smith
In which case it might be better to convert the replace insert into an
update, and use the update trigger which is guaranteed to have valid OLD
and NEW.
But that is not my decision to make. Create a new enhancement ticket in
CVSTrac.

Also see ticket #368, which I reported on a similar issue.

[Alex]

Post by Christian Smith
But I think the gist of you email is that you'd prefer the "INSERT OR
REPLACE" to be handled like an update. I can't see a reason why an OLD
record could be created for replace inserts. The problem then becomes how
do you know in the trigger whether OLD is valid? If the insert is a
straight insert, then OLD will not be valid, and you'll probably get an
exception trying to use it.

Well, I'd prefer the 'ON DELETE' trigger to be called every time a deletion
occures regardless of whether it's caused by a "DELETE" or "REPLACE"
statement.

Post by Christian Smith
In which case it might be better to convert the replace insert into an
update, and use the update trigger which is guaranteed to have valid OLD
and NEW.

I'm afraid, it's impossible. The actual SQL code will be written by
different persons, and they must be able to use all the SQL features
supported in SQLite.

Probably, I should provide some more info on the project I'm working on.
There exists a legacy db which does not support SQL syntax at all. The goal
of the project is to allow SQL management for this db. SQLite was chosen as
the SQL interface provider, but the problem is that the old db contains
quite big blob objects (up to 25 MB and more) which cannot be simply copied
into a SQLite table. The obvious solution is to copy not the blob itself,
but only a reference to it (this is what the SecDbKey field actually is). In
this case any changes in an SQLite table must be properly reflected in the
old db. What is worse, the problem of maintaining the coherency is highly
desirable to be solved in the most common manner, since there is no way for
me to predict the structure of SQLite tables ever created or SQL queries
ever constructed. Triggers would be the right solution, but they don't seem
to be accurate enough (at least, the INSERT one). Still thinking what to
do...

Thank you guys for all of your suggestions. I'll see what I can do about it.

Regards,
Alex

[Christian Smith]

Post by Alex

Post by Christian Smith
But I think the gist of you email is that you'd prefer the "INSERT OR
REPLACE" to be handled like an update. I can't see a reason why an OLD
record could be created for replace inserts. The problem then becomes how
do you know in the trigger whether OLD is valid? If the insert is a
straight insert, then OLD will not be valid, and you'll probably get an
exception trying to use it.

Well, I'd prefer the 'ON DELETE' trigger to be called every time a deletion
occures regardless of whether it's caused by a "DELETE" or "REPLACE"
statement.

Existing ticket #368 may cover this behaviour. I guess Ron is the user who
added the latest remark:
http://www.sqlite.org/cvstrac/tktview?tn=368

If I have time at the weekend, I might have a bash at producing a patch
that will implement what is required. No promises, mind.

What would be preferred? Convert the trigger to an update trigger, or fire
a delete trigger followed by an insert trigger?

I'd prefer the former (update) as requested in the ticket. I think it may
be easier to code, as well, but not sure.

Post by Alex

Post by Christian Smith
In which case it might be better to convert the replace insert into an
update, and use the update trigger which is guaranteed to have valid OLD
and NEW.

I'm afraid, it's impossible. The actual SQL code will be written by
different persons, and they must be able to use all the SQL features
supported in SQLite.

I actually meant behind the scenes in SQLite.

Post by Alex
Probably, I should provide some more info on the project I'm working on.
There exists a legacy db which does not support SQL syntax at all. The goal
of the project is to allow SQL management for this db. SQLite was chosen as
the SQL interface provider, but the problem is that the old db contains
quite big blob objects (up to 25 MB and more) which cannot be simply copied
into a SQLite table. The obvious solution is to copy not the blob itself,
but only a reference to it (this is what the SecDbKey field actually is). In
this case any changes in an SQLite table must be properly reflected in the
old db. What is worse, the problem of maintaining the coherency is highly
desirable to be solved in the most common manner, since there is no way for
me to predict the structure of SQLite tables ever created or SQL queries
ever constructed. Triggers would be the right solution, but they don't seem
to be accurate enough (at least, the INSERT one). Still thinking what to
do...

What is stopping you checking for an existing row in the trigger body
yourself? Something like (based on your example):
create trigger table_replace_check
before insert on 'table'
for each row
begin
insert into table_log
select 'D', id, SecDbKey from 'table'
where id=NEW.id;
end;

Then you just keep a regular post insert trigger to put the new updated
values into the log.

Post by Alex
Thank you guys for all of your suggestions. I'll see what I can do about it.
Regards,
Alex

Christian

[Alex]

Post by Christian Smith
If I have time at the weekend, I might have a bash at producing a patch
that will implement what is required. No promises, mind.
What would be preferred? Convert the trigger to an update trigger, or fire
a delete trigger followed by an insert trigger?
I'd prefer the former (update) as requested in the ticket. I think it may
be easier to code, as well, but not sure.

Yes, I agree, firing the UPDATE trigger would be better. But I would also be
satisfied with an additional DELETE trigger call. BTW, if you release this
patch, which SQLite version will it be available for? Only 3.x or also 2.x?

Post by Christian Smith
What is stopping you checking for an existing row in the trigger body
create trigger table_replace_check
before insert on 'table'
for each row
begin
insert into table_log
select 'D', id, SecDbKey from 'table'
where id=NEW.id;
end;
Then you just keep a regular post insert trigger to put the new updated
values into the log.

The example I posted is a simple case I use for testing. Other people will
create different tables with differend fields, and I have no idea how these
tables will look like. So, how can I code a trigger for an arbitrary table
without doing havy parsing of the incoming "CREATE TABLE" query? No, firing
the UPDATE trigger is definitely a better solution :)

Regards,
Alex

[Christian Smith]

Post by Alex

Post by Christian Smith
If I have time at the weekend, I might have a bash at producing a patch
that will implement what is required. No promises, mind.
What would be preferred? Convert the trigger to an update trigger, or fire
a delete trigger followed by an insert trigger?
I'd prefer the former (update) as requested in the ticket. I think it may
be easier to code, as well, but not sure.

Yes, I agree, firing the UPDATE trigger would be better. But I would also be
satisfied with an additional DELETE trigger call. BTW, if you release this
patch, which SQLite version will it be available for? Only 3.x or also 2.x?

I'd probably initially do it for 3.x, as 2.x is basically in feature
freeze, I believe. Are you 2.x based?

BTW, anyone else have an opinion on whether it should be a update trigger
or delete + insert?

Post by Alex

Post by Christian Smith
What is stopping you checking for an existing row in the trigger body
create trigger table_replace_check
before insert on 'table'
for each row
begin
insert into table_log
select 'D', id, SecDbKey from 'table'
where id=NEW.id;
end;
Then you just keep a regular post insert trigger to put the new updated
values into the log.

The example I posted is a simple case I use for testing. Other people will
create different tables with differend fields, and I have no idea how these
tables will look like. So, how can I code a trigger for an arbitrary table
without doing havy parsing of the incoming "CREATE TABLE" query? No, firing
the UPDATE trigger is definitely a better solution :)

So, you're expected to provide a SQL front end to a legacy database,
without dumping the old database, and keep the two in sync, but allow
people to create arbitrary tables in the SQL frontend that will be synced
to the legacy backend?

Don't want much, do they:)

Post by Alex
Regards,
Alex

Christian

[Alex]

Post by Christian Smith
I'd probably initially do it for 3.x, as 2.x is basically in feature
freeze, I believe. Are you 2.x based?

Yes, I am, for it was the only stable version when the project was started.
And I'd prefer to stay on it since version 3 is too young, its docs are not
complete (AFAIK). So, if possible, I'd like version 2.x to be also modified.
After all, this improvement can be thought of as a kind of SQL bug fix.

Post by Christian Smith
So, you're expected to provide a SQL front end to a legacy database,
without dumping the old database, and keep the two in sync, but allow
people to create arbitrary tables in the SQL frontend that will be synced
to the legacy backend?

Exactly so.

Post by Christian Smith
Don't want much, do they:)

They have a lot of legacy client software which uses the legacy db API. It
was a primary requirement to provide coexistance for SQL and non-SQL
environments.

Regards,
Alex

[Christian Smith]

Post by Christian Smith
Existing ticket #368 may cover this behaviour. I guess Ron is the user who
http://www.sqlite.org/cvstrac/tktview?tn=368
If I have time at the weekend, I might have a bash at producing a patch
that will implement what is required. No promises, mind.

Nope, my email crapped out on Sunday, and I spent too much brain power
trying to recover the wretched stuff to do a patch. I'll see what I can do
this week.

Christian

[Alex]

Post by Christian Smith
Nope, my email crapped out on Sunday, and I spent too much brain power
trying to recover the wretched stuff to do a patch. I'll see what I can do
this week.

OK. Looking forward to hear form you.

Regards,
Alex

[Alex]

Hi

Post by Christian Smith
Existing ticket #368 may cover this behaviour. I guess Ron is the user who
http://www.sqlite.org/cvstrac/tktview?tn=368
If I have time at the weekend, I might have a bash at producing a patch
that will implement what is required. No promises, mind.
What would be preferred? Convert the trigger to an update trigger, or fire
a delete trigger followed by an insert trigger?

Christian, I'm about to release my project, so I'd like to know if there is
any progress on solving this problem. Version 3.0.8 doesn't seem to fix it.
Any ideas on when (if ever) this issue is fixed?

Thanks,
Alex

[Christian Smith]

Post by Alex
Hi

Post by Christian Smith
Existing ticket #368 may cover this behaviour. I guess Ron is the user who
http://www.sqlite.org/cvstrac/tktview?tn=368
If I have time at the weekend, I might have a bash at producing a patch
that will implement what is required. No promises, mind.
What would be preferred? Convert the trigger to an update trigger, or fire
a delete trigger followed by an insert trigger?

Christian, I'm about to release my project, so I'd like to know if there is
any progress on solving this problem. Version 3.0.8 doesn't seem to fix it.
Any ideas on when (if ever) this issue is fixed?

Sorry, I'm coming to end of cycle on my project as well, so it's a busy
time at work. Spare time is mostly taken up with getting fit and winding
down from work, and I've not had a quiet weekend for several weeks.

If this issue is critical to your project, you should perhaps contract
Richard to do the changes, as I can't give any guarantees on ever getting
round to implementing this. I'm still in the code familiarisation phase.

Post by Alex
Thanks,
Alex

Christian

[D. Richard Hipp]

Post by Christian Smith
If this issue is critical to your project, you should perhaps contract
Richard to do the changes, as I can't give any guarantees on ever getting
round to implementing this. I'm still in the code familiarisation phase.

I do not consider this a bug. INSERT OR REPLACE is not an UPDATE so
an UPDATE trigger should not fire on an INSERT OR REPLACE. So don't
be looking for me to change it anytime soon.

[Christian Smith]

Post by D. Richard Hipp

Post by Christian Smith
If this issue is critical to your project, you should perhaps contract
Richard to do the changes, as I can't give any guarantees on ever getting
round to implementing this. I'm still in the code familiarisation phase.

I do not consider this a bug. INSERT OR REPLACE is not an UPDATE so
an UPDATE trigger should not fire on an INSERT OR REPLACE. So don't
be looking for me to change it anytime soon.

I was thinking more along the lines of a DELETE. That would be an accurate
semantic, would it not?

Christian

[D. Richard Hipp]

Post by Christian Smith
I was thinking more along the lines of a DELETE. That would be an accurate
semantic, would it not?

Yes.

[Alex]

Post by Christian Smith

Post by D. Richard Hipp
I do not consider this a bug. INSERT OR REPLACE is not an UPDATE so
an UPDATE trigger should not fire on an INSERT OR REPLACE. So don't
be looking for me to change it anytime soon.

I was thinking more along the lines of a DELETE. That would be an accurate
semantic, would it not?

Yes, what about firing the DELETE trigger? During REPLACE a row is deleted,
isn't it?

Regards,
Alex

[D. Richard Hipp]

Post by Alex
Yes, what about firing the DELETE trigger? During REPLACE a row is deleted,
isn't it?

That's the way REPLACE works, yes. So one could reasonably argue for
a DELETE trigger to fire on a REPLACE.

[Kurt Welgehausen]

Post by Alex
CREATE TABLE table (ID text UNIQUE, Name text, Surname text, SecDbKey text)
INSERT INTO table VALUES ('001', 'John', 'Smith', 'A0B1C2D3')
INSERT OR REPLACE INTO table VALUES ('001', 'Jane', 'Parker', 'E4F51234')

I'm not sure exactly what relationship you're trying to maintain
between this table and the other one, but assuming that SecDbKey
is a foreign key that references table2 and that you have no
duplicate foreign keys (SecDbKey should probably be declared
unique if that's the case), then you can execute

delete from table2 where table2.DbKey not in
(select SecDbKey from table)

after you do your <insert or replace>, or after you do several
of them. You can put it in a trigger if you don't want to do
it explicitly.

BTW, you might want to review your db design. Usually you
delete foreign keys when the referenced primary key disappears;
you seem to be doing the opposite.

Regards