Re: [sqlite] Conditional insertion syntaxe in SQLite
Thanks for the suggestion of a temp table for storing intermediaire data, Igor. Now DELETE TRANSACTION works fine with only SQL code! Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, January 26, 2010 6:23 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Conditional insertion syntaxe in SQLite Tiberio, Sylvain wrote: > Is it possible to write something like that in SQL (for SQLite of > course!): > > If [toDeleteMin,toDeleteMax] is include in an existing range Store the > existing range value into min and max variables Delete [Min,Max] > Create [Min,toDeleteMin-1] Create [toDeleteMax+1,max] endif There are no variables, but you can use a temp table. Something like this: create temp table Vars(rangeid, existingMin, existingMax); insert into Vars select rowid, roomIdMin, roomIdMax from range where roomIdMin < toDeleteMin and roomIdMax > toDeleteMax; delete from range where rowid = (select rangeid from Vars); insert into range select 1, existingMin, toDeleteMin from Vars; insert into range select 1, toDeleteMax, existingMax from Vars; drop table Vars; (Edge conditions are left as an exercise for the reader, I'm pretty sure I got them wrong). -- Igor Tandetnik ___ 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] Conditional insertion syntaxe in SQLite
Tiberio, Sylvain wrote: > Is it possible to write something like that in SQL (for SQLite of > course!): > > If [toDeleteMin,toDeleteMax] is include in an existing range > Store the existing range value into min and max variables > Delete [Min,Max] > Create [Min,toDeleteMin-1] > Create [toDeleteMax+1,max] > endif There are no variables, but you can use a temp table. Something like this: create temp table Vars(rangeid, existingMin, existingMax); insert into Vars select rowid, roomIdMin, roomIdMax from range where roomIdMin < toDeleteMin and roomIdMax > toDeleteMax; delete from range where rowid = (select rangeid from Vars); insert into range select 1, existingMin, toDeleteMin from Vars; insert into range select 1, toDeleteMax, existingMax from Vars; drop table Vars; (Edge conditions are left as an exercise for the reader, I'm pretty sure I got them wrong). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional insertion syntaxe in SQLite
Thank you Igor for you quick answer! I would like to implement the range deletion algorithm in SQL to avoid to maintain several implementation (C++, PHP...). If there is no other solution I will do it in C++ and in PHP! Your SQL statements doesn't work in my case, because I have a trigger that ignore the insertion of existing range. For instance: INSERT INTO range VALUES(1,100,199) Do nothing if the entry (1,100,400) exist because [100,199] is already set to level=1 by [100,400]. So I must delete the range [100,400] before create [100,199] and [201,400]... Is it possible to write something like that in SQL (for SQLite of course!): If [toDeleteMin,toDeleteMax] is include in an existing range Store the existing range value into min and max variables Delete [Min,Max] Create [Min,toDeleteMin-1] Create [toDeleteMax+1,max] endif Regards, Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Monday, January 25, 2010 5:06 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Conditional insertion syntaxe in SQLite Tiberio, Sylvain wrote: > So I would like to write something like that: > CASE WHEN > EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range >WHERE 200 BETWEEN roomIdMin AND roomIdMax > AND 400 BETWEEN roomIdMin AND roomIdMax) THEN DELETE > FROM range WHERE roomidm...@min AND roomidm...@max; INSERT INTO range > VALUES (1, @min, 199 ); INSERT INTO range VALUES (1, 301 , @max ); > END Things like this are best done in your application code, rather than a trigger. SQLite in particular has a limited trigger language, nowhere near Turing-complete (the way Transact-SQL or PL/SQL are). However, if you absolutely insist, you can do something like this: insert into range select 1, roomIdMin, 199 from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax; insert into range select 1, 301, roomIdMax from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax; delete from range where rowid = (select rowid from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax); Note that if the condition doesn't hold (there is no row encompassing both 200 and 400), all three statements are simply no-ops. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional insertion syntaxe in SQLite
Tiberio, Sylvain wrote: > So I would like to write something like that: > CASE WHEN > EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range >WHERE 200 BETWEEN roomIdMin AND roomIdMax > AND 400 BETWEEN roomIdMin AND roomIdMax) > THEN > DELETE FROM range WHERE roomidm...@min AND roomidm...@max; > INSERT INTO range VALUES (1, @min, 199 ); > INSERT INTO range VALUES (1, 301 , @max ); > END Things like this are best done in your application code, rather than a trigger. SQLite in particular has a limited trigger language, nowhere near Turing-complete (the way Transact-SQL or PL/SQL are). However, if you absolutely insist, you can do something like this: insert into range select 1, roomIdMin, 199 from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax; insert into range select 1, 301, roomIdMax from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax; delete from range where rowid = (select rowid from range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax); Note that if the condition doesn't hold (there is no row encompassing both 200 and 400), all three statements are simply no-ops. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Conditional insertion syntaxe in SQLite
Hi, I manage range in a database: CREATE TABLE range(level INTEGER, roomIdMin INTEGER, roomIdMax INTEGER, CHECK (roomIdMin<=roomIdMax) ); INSERT INTO range VALUES (1,100,200); INSERT INTO range VALUES (2,300,400); | level | roomIdMin | roomIdMax | ++++ | 1 |100 |200 | | 2 |300 |400 | I manage range insertion through a Trigger to have automatique range merging (if level are =). For instance : INSERT INTO range VALUES (1,150,250); | level | roomIdMin | roomIdMax | ++++ | 1 |100 |250 | | 2 |300 |400 | Idem for all insertion case... For range deletion, I would like to do be able to delete for instance [150,200] and get: | level | roomIdMin | roomIdMax | ++++ | 1 |100 |149 | | 1 |201 |250 | | 2 |300 |400 | So I cannot use Trigger because range [150,200] is not an entry in range table. So I write a "generic" TRANSACTION where I process all deletation case but I have a SQL syntaxe problem with one case (the "split" case): If I have in my database: | level | roomIdMin | roomIdMax | ++++ | 1 |100 |400 | And I delete [200,300], I would like to have | level | roomIdMin | roomIdMax | ++++ | 1 |100 |199 | | 1 |301 |400 | SO I can: Case 1: - delete [100,400] - insert [100,199] - insert [301,400] Case 2: - Update [100,400] to [100,199] - insert [301,400] So I would like to write something like that: CASE WHEN EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax) THEN DELETE FROM range WHERE roomidm...@min AND roomidm...@max; INSERT INTO range VALUES (1, @min, 199 ); INSERT INTO range VALUES (1, 301 , @max ); END or CASE WHEN EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range WHERE 200 BETWEEN roomIdMin AND roomIdMax AND 400 BETWEEN roomIdMin AND roomIdMax) THEN UPDATE range SET roomIdMax=199 WHERE roomidm...@min AND roomidm...@max; INSERT INTO range VALUES (1, 301 , @max ); END But SQLite generate a syntaxe error... Because I think we cannot use CASE WHEN THEN END outside and instruction... I tried lots of syntax without success... How can I write that in SQL for SQLite? Regards, Sylvain ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
