Re: [sqlite] ON INSERT trigger
Alex wrote: 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. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] ON INSERT trigger
> >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
Re: [sqlite] ON INSERT trigger
Christian Smith wrote: I was thinking more along the lines of a DELETE. That would be an accurate semantic, would it not? Yes. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] ON INSERT trigger
On Tue, 12 Oct 2004, D. Richard Hipp wrote: >Christian Smith wrote: >> >> 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 -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] ON INSERT trigger
Christian Smith wrote: 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. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] ON INSERT trigger
On Tue, 12 Oct 2004, Alex wrote: >Hi > >> 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? > >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. > >Thanks, >Alex > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] ON INSERT trigger
Hi > 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? 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
Re: [sqlite] ON INSERT trigger
> 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. > 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. > 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
Re: [sqlite] ON INSERT trigger
On Thu, 23 Sep 2004, Alex wrote: >> 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? > >> 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. > >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:) > >Regards, >Alex > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] ON INSERT trigger
> 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? > 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. 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
Re: [sqlite] ON INSERT trigger
On Tue, 21 Sep 2004, Alex wrote: >> 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. > >> 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. > >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. > >Thank you guys for all of your suggestions. I'll see what I can do about it. > >Regards, >Alex > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] ON INSERT trigger
> 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. > 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
Re: [sqlite] ON INSERT trigger
> 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 , 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
Re: [sqlite] ON INSERT trigger
On Tue, September 21, 2004 11:26, Christian Smith said: > On Tue, 21 Sep 2004, Alex wrote: > > 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. -- My GPG public key is at http://ronware.org/ fingerprint: 8130 734C 69A3 6542 0853 CB42 3ECF 9259 AD29 415D
Re: [sqlite] ON INSERT trigger
On Tue, 21 Sep 2004, Alex wrote:
>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.
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.
>
>Thanks,
>Alex
>
>
>> 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
>
>
>
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] ON INSERT trigger
Alex wrote:
> 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
>
>
>> 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).
Re: [sqlite] ON INSERT trigger
"Alex" <[EMAIL PROTECTED]> writes:
> 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
Re: [sqlite] ON INSERT trigger
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
> 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
Re: [sqlite] ON INSERT trigger
On Sun, 19 Sep 2004, Alex wrote: >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. > >Thanks, >Alex > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] ON INSERT trigger
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
