Re: [sqlite] integrity constraint that is equivalent to the following trigger:

2012-06-13 Thread Christian Smith 
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):
> 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?


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

Re: [sqlite] integrity constraint that is equivalent to the following trigger:

2012-06-11 Thread Wolfgang Meiners 
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:

2012-06-10 Thread Wolfgang Meiners 
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

Re: [sqlite] integrity constraint that is equivalent to the following trigger:

2012-06-10 Thread Petite Abeille 

On Jun 10, 2012, at 2:35 PM, Wolfgang Meiners wrote:

> So my question is: (how) can i write an integrity constraint in sqlite3

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:

http://www.sqlite.org/lang_createtable.html#constraints

The check constraint can only contain basic expressions. In other words, no 
select statements.

For anything more, hmmm, convoluted, you better stick to triggers or ETL.

___
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:

2012-06-10 Thread 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

--
DavaoSOFT, the home of ERPel
ERPel, das deutsche Warenwirtschaftssystem fuer LINUX
http://www.davaosoft.com

___
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:

2012-06-10 Thread Wolfgang Meiners 
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