Re: [sqlite] sql optimization question

[Jay Sprenkle]

On Jan 20, 2008 9:16 PM, Ken <[EMAIL PROTECTED]> wrote:
> Jay
>
>   I've used a trigger to do this with good success.
>
>   You'll need one trigger per table to cause the delete to cascade through 
> the tree.
>
>   Or if you know that you will always delete from the top level tree"parent" 
> then just one trigger would probably suffice.

I wonder why yours works but Ken's doesn't. In mine one trigger should
do it, but it would have to be recursive.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sql optimization question

[Jay Sprenkle]

> In response to the deletion of (0,null), the trigger fired, deleting
> (1,0), and (2,0).  But the trigger didn't fire again in response to
> either of these subsequent deletions, so (3,1) was not automatically
> deleted.
>
> If anyone knows how to get around this problem, I would like to know.

I wrote mine as a loop that deletes anything that has no valid parent,
but that assumes you can program that into whatever you're using.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sql optimization question

[Andy Goth]

On Sun, 20 Jan 2008 19:16:03 -0800 (PST), Ken wrote
> Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>> I'm deleting a tree of data stored in sqlite and was looking for the
>> most efficient way to do it.
>
> You'll need one trigger per table to cause the delete to cascade
> through the tree.

Watch out.  SQLite doesn't support recursive triggers, so the following
won't work.  At least, it didn't work for me. :^)

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> create table x (id, parent);
sqlite> create trigger deltree after delete on x begin
   ...> delete from x where parent = old.id;
   ...> end;
sqlite> insert into x values(0, null);
sqlite> insert into x values(1, 0);
sqlite> insert into x values(2, 0);
sqlite> insert into x values(3, 1);
sqlite> delete from x where id = 0;
sqlite> select * from x;
3|1
sqlite> 

In response to the deletion of (0,null), the trigger fired, deleting
(1,0), and (2,0).  But the trigger didn't fire again in response to
either of these subsequent deletions, so (3,1) was not automatically
deleted.

If anyone knows how to get around this problem, I would like to know.

-- 
Andy Goth
<[EMAIL PROTECTED]>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sql optimization question

[Ken]

Jay 
   
  I've used a trigger to do this with good success.
   
  You'll need one trigger per table to cause the delete to cascade through the 
tree.
   
  Or if you know that you will always delete from the top level tree"parent" 
then just one trigger would probably suffice.
   
  Ken

Jay Sprenkle <[EMAIL PROTECTED]> wrote:
  I'm deleting a tree of data stored in sqlite and was looking for the
most efficient way to do it.
I thought the best solution was to delete the row and then delete all
the orphaned rows the referenced it in a loop

delete from category where id = 5;

To delete the orphaned rows I repeat one of the following until
nothing more is deleted.
Which of these two forms would run faster?

delete from category where ParentId not in ( select distinct id from category );

or

delete from category A
left join category B on b.id = a.parent.id
where b.id is null;

Thanks


-- 
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-