[sqlite] Conditional insertion syntaxe in SQLite

[Tiberio, Sylvain]

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