Re: [sqlite] integrity constraint that is equivalent to the following trigger:
Am 10.06.12 14:59, schrieb Petite Abeille: > > The short of it is that you can't. SQLite doesn't support such constraints. > Only the basics are supported: primary, unique, referential, not null, check: > Thank you for that information. It saves me a lot of time searching for a solution that does not exist. I think i can work arount this in sqlalchemy. Regards Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integrity constraint that is equivalent to the following trigger:
Am 10.06.12 14:49, schrieb Guenther Boelter: > On 06/10/2012 08:35 PM, Wolfgang Meiners wrote: >> So my question is: (how) can i write an integrity constraint in sqlite3 >> (version 3.6.12) which is equivalent to the part >> SELECT CASE >> WHEN EXISTS (SELECT * FROM ausleihen >> WHERE (beid = NEW.beid) AND (rueckgabe is\ >> NULL)) >> THEN RAISE(ROLLBACK, 'Buch ist ausgeliehen') >> END; >> of the trigger? >> >> > Moin Wolfgang, > > it's possible that I don't understand your problem, but why are doing a > rollback if you don't have changed anything in your database? > > Regards > > Guenther > Hi Guenther, you are right, thank you for this hint. I started with an AFTER INSERT ON ausleihen Trigger. When i changed this to BEFORE INSERT ON ausleihen, i forgot to change ROLLBACK to ABORT (would FAIL be better?). I just changed this but the errormessage from sqlalchemy remains. Regards Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] integrity constraint that is equivalent to the following trigger:
Hello, i have written a sqlite3-db for a small books library. Within this db there is a table: sqlite> .schema ausleihen CREATE TABLE ausleihen ( aid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, pid INTEGER NOT NULL, beid INTEGER NOT NULL, ausleihdatum DATE NOT NULL, rueckgabe DATE, FOREIGN KEY(pid) REFERENCES personen (pid) ON DELETE CASCADE, FOREIGN KEY(beid) REFERENCES buchexemplare (beid) ON DELETE CASCADE ); CREATE INDEX ix_ausleihen_ausleihdatum ON ausleihen (ausleihdatum); CREATE TRIGGER insertausleihe BEFORE INSERT ON ausleihen FOR EACH ROW BEGIN SELECT CASE WHEN EXISTS (SELECT * FROM ausleihen WHERE (beid = NEW.beid) AND (rueckgabe is\ NULL)) THEN RAISE(ROLLBACK, 'Buch ist ausgeliehen') END; SELECT CASE WHEN NOT EXISTS (SELECT * FROM personen WHERE pid = NEW.pid) THEN RAISE(ROLLBACK, 'Person existiert nicht') END; SELECT CASE WHEN NOT EXISTS (SELECT * FROM buchexemplare WHERE beid = NEW.beid) THEN RAISE(ROLLBACK, 'Buchexemplar existiert nicht') END; END; sqlite> In this table, every row belongs to a person (pid) which has an exemplar of a certain book on loan (beid). If the bookexemplar is on loan, the value rueckgabe is equal to NULL. The first SELECT statement in the trigger raises a rollback, if the book is already on loan. This works, but there is an issue with the orm-part of sqlalchemy: Since sqlalchemy.orm does not 'see' this trigger, the following python function returns true, even if the trigger is violated: def buchausleihen(session, pid, beid, ausleihdatum=None): Ttry: session.add(Ausleihe(pid=pid, beid=beid, ausleihdatum=ausleihdatum)) return True except: return False This means: (python code:) print(buchausleihen(session,pid=1,beid=1)) # True print(buchausleihen(session,pid=2,beid=1)) # True session.commit() # raises an error This is not what i expected. So i think, if i could write an integrity constraint - maybe an CHECK-clause, this would be seen by sqlalchemy. So my question is: (how) can i write an integrity constraint in sqlite3 (version 3.6.12) which is equivalent to the part SELECT CASE WHEN EXISTS (SELECT * FROM ausleihen WHERE (beid = NEW.beid) AND (rueckgabe is\ NULL)) THEN RAISE(ROLLBACK, 'Buch ist ausgeliehen') END; of the trigger? Thank you for any hints Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger, syntax question
Am 04.06.12 19:36, schrieb Igor Tandetnik: > > http://sqlite.org/lang.html > http://sqlite.org/syntaxdiagrams.html > I just found the case expression. I did not know it before. Thank you for pointing me to that direction! Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger, syntax question
Am 04.06.12 19:31, schrieb Michael Schlenker: > Am 04.06.2012 19:25, schrieb Wolfgang Meiners: >> Am 04.06.12 18:59, schrieb Igor Tandetnik: >>> On 6/4/2012 12:33 PM, Wolfgang Meiners wrote: >>>> this trigger does work but i think it is not in accordance with the >>>> syntax diagram of TRIGGER on >>>> http://sqlite.com/lang_createtrigger.html >>> >>> Which part do you feel is in violation of the diagram? >> >> I cant find anything about >> SELECT CASE >>... >> END; >> >> in the diagram and i dont understand, how it works. > Have a look at: > http://sqlite.org/lang_expr.html > > Michael > OK. I found it. I thougt, CASE ... END belonged to TRIGGER, but it belongs to SQL. I just found http://www.sqlite.org/lang_expr.html#case Thank you for pointing me in that direction. Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger, syntax question
Am 04.06.12 18:59, schrieb Igor Tandetnik: > On 6/4/2012 12:33 PM, Wolfgang Meiners wrote: >> this trigger does work but i think it is not in accordance with the >> syntax diagram of TRIGGER on >> http://sqlite.com/lang_createtrigger.html > > Which part do you feel is in violation of the diagram? I cant find anything about SELECT CASE ... END; in the diagram and i dont understand, how it works. Can there be just one WHEN ... THEN ... part or more then one? Is it possible to have something like IF ... THEN ... too? This seems to be an programming language, but i have not found the rules for this language. Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger, syntax question
Hi, I have written the following trigger with ideas from stackoverflow: (OSX 10.6.8, SQLite 3.6.12) This is for a book library. If i delete a bookexemplar from the library, this should not be possible if this bookexemplar is on loan, which means ausleihen.rueckgabe IS NULL. Otherwise every information on loans should be deleted, too. CREATE TRIGGER buchexemplare_delete AFTER DELETE ON buchexemplare FOR EACH ROW BEGIN SELECT CASE WHEN EXISTS (SELECT beid FROM ausleihen WHERE (ausleihen.beid = OLD.beid) AND\ (ausleihen.rueckgabe IS NULL)) THEN RAISE(ROLLBACK, 'Es sind noch Buchexemplare\ ausgeliehen') END; DELETE FROM ausleihen WHERE ausleihen.beid = OLD.beid; END; this trigger does work but i think it is not in accordance with the syntax diagram of TRIGGER on http://sqlite.com/lang_createtrigger.html So where can i find the exact actual syntax for triggers and maybe some examples? Thank you for any help Wolfgang ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
