On Sun, Jun 10, 2012 at 02:35:20PM +0200, Wolfgang Meiners wrote:
> 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?
Why not add a unique index on ausleihen(beid,rueckgabe). That way, the book
identified by beid cannot be inserted twice into the ausleihen table if the
current book is on loan. However, you'd need a non-NULL sentinal value for
rueckgabe for this to work, perhaps some epoch that is before any valid return
time, such that a book is considered on loan if rueckgabe = sentinal value.
Your foreign key constraints take care of ensuring the book and person exist.
The unique index deals with the book only being loaned once at a time, so you
can discard the trigger completely.
So you'd have:
CREATE TABLE ausleihen (
aid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
pid INTEGER NOT NULL,
beid INTEGER NOT NULL,
ausleihdatum DATE NOT NULL,
rueckgabe DATE NOT NULL DEFAULT 0, -- 0 is sentinal to indicate not
returned instead of NULL
CONSTRAINT Leihgabe_Scheck UNIQUE (beid,rueckgabe),
FOREIGN KEY(pid) REFERENCES personen (pid) ON DELETE CASCADE,
FOREIGN KEY(beid) REFERENCES buchexemplare (beid) ON DELETE CASCADE
);
Hope that helps,
Christian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
