Re: [sqlite] Trouble with constraints and triggers

2010-09-23 Thread Josh Gibbs 

On 23/09/2010 11:52 p.m., Richard Hipp wrote:
>> Josh Gibbs  wrote:
>>> CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT,
>>> Subject TEXT);
>>> CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES
>>> Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES
>>> Recipient(recipient_id));
>>> CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT,
>>> name);
>>>
>>> I've tried creating a trigger after delete on MessageRecipient to remove
>> the
>>> referenced Recipient, and this works if it's the only related item,
>>> however any
>>> other MessageRecipient relationship causes the delete to fail.  As there
>>> is no
>>> 'or ignore' for the delete statement, I can't get this to keep my data
>>> clean.
>
> DELETE FROM recipient
>   WHERE recipient_id = old.recipient_id
> AND NOT EXISTS(SELECT 1 FROM message_recipient
> WHERE recipient.recipient_id=
>   message_recipient.recipient_id);
>

This works perfectly, thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with constraints and triggers

2010-09-23 Thread Igor Tandetnik 
Josh Gibbs  wrote:
>  On 23/09/2010 3:15 p.m., Igor Tandetnik wrote:
>> You could do something like
>> 
>> delete from Recipient where recipient_ID = old.recipient_ID and
>>recipient_ID not in (select recipient_ID from MessageRecipient);
>> 
> 
> That was the last idea we had as well.  Trouble is MessageRecipient
> contains hundreds
> of thousands of records.  Would that cipple the speed of the delete, or
> would the query
> apply the 'not in' to the sub-select on its index?

If there is an index on MessageRecipient(recipient_ID), the query should use it.

Alternatively, you could maintain a reference count in Recipient (with still 
more triggers), and delete the record once the count reaches zero.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with constraints and triggers

2010-09-23 Thread Richard Hipp 
On Wed, Sep 22, 2010 at 11:15 PM, Igor Tandetnik wrote:

> Josh Gibbs  wrote:
> > CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT,
> > Subject TEXT);
> > CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES
> > Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES
> > Recipient(recipient_id));
> > CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT,
> > name);
> >
> > I've tried creating a trigger after delete on MessageRecipient to remove
> the
> > referenced Recipient, and this works if it's the only related item,
> > however any
> > other MessageRecipient relationship causes the delete to fail.  As there
> > is no
> > 'or ignore' for the delete statement, I can't get this to keep my data
> > clean.
>
> You could do something like
>
> delete from Recipient where recipient_ID = old.recipient_ID and
>  recipient_ID not in (select recipient_ID from MessageRecipient);
>


DELETE FROM recipient
 WHERE recipient_id = old.recipient_id
   AND NOT EXISTS(SELECT 1 FROM message_recipient
   WHERE recipient.recipient_id=
 message_recipient.recipient_id);


>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with constraints and triggers

2010-09-22 Thread Josh Gibbs 
  On 23/09/2010 3:15 p.m., Igor Tandetnik wrote:
> Josh Gibbs  wrote:
>> CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT,
>> Subject TEXT);
>> CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES
>> Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES
>> Recipient(recipient_id));
>> CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT,
>> name);
>>
>> I've tried creating a trigger after delete on MessageRecipient to remove the
>> referenced Recipient, and this works if it's the only related item,
>> however any
>> other MessageRecipient relationship causes the delete to fail.  As there
>> is no
>> 'or ignore' for the delete statement, I can't get this to keep my data
>> clean.
> You could do something like
>
> delete from Recipient where recipient_ID = old.recipient_ID and
>recipient_ID not in (select recipient_ID from MessageRecipient);
>

That was the last idea we had as well.  Trouble is MessageRecipient 
contains hundreds
of thousands of records.  Would that cipple the speed of the delete, or 
would the query
apply the 'not in' to the sub-select on its index?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble with constraints and triggers

2010-09-22 Thread Igor Tandetnik 
Josh Gibbs  wrote:
> CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT,
> Subject TEXT);
> CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES
> Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES
> Recipient(recipient_id));
> CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT,
> name);
> 
> I've tried creating a trigger after delete on MessageRecipient to remove the
> referenced Recipient, and this works if it's the only related item,
> however any
> other MessageRecipient relationship causes the delete to fail.  As there
> is no
> 'or ignore' for the delete statement, I can't get this to keep my data
> clean.

You could do something like

delete from Recipient where recipient_ID = old.recipient_ID and
  recipient_ID not in (select recipient_ID from MessageRecipient);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trouble with constraints and triggers

2010-09-22 Thread Josh Gibbs 
  Hi all, I'm hoping someone can assist me with a problem I'm having
creating a cascading delete operation as well as a constraint.

This table is an example of the layout of my data:

CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, 
Subject TEXT);
CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES 
Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES 
Recipient(recipient_id));
CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT, 
name);

My goal is that when I remove an item from the Message table, all of the 
related
items in the MessageRecipient table are removed, and all items from the 
Recipient
table that no longer have references to the MessageRecipient are removed.

The tables as listed above fulfill the need for a Recipient to exist, 
prevent a
Recipient from being removed if a MessageRecipient still points to them, and
removes the MessageRecipient when the Message is deleted.

It does not fulfill tidying up of the Recipient table, and items are 
left orphaned
when all Messages are removed that refer to that recipient.

I've tried creating a trigger after delete on MessageRecipient to remove the
referenced Recipient, and this works if it's the only related item, 
however any
other MessageRecipient relationship causes the delete to fail.  As there 
is no
'or ignore' for the delete statement, I can't get this to keep my data 
clean.

Any solutions to this conundrum would be greatly appreciated.

Thanks, Josh

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users