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):
T try:
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
