Hierarchical Deletion via a Trigger?
|
View:
New views
17 Messages
—
Rating Filter:
Alert me
|
 |
Hierarchical Deletion via a Trigger?
by John Elrick-2
::
Rate this Message:
I'm a bit stumped and was curious if anyone had an elegant solution for
this problem. Assuming the following simplified example, my goal is to cascade the deletes until all parent/child relations have been deleted. The trigger removes the first level, but stops there (I believe this behavior is documented). I can think of a delete query which would also remove the first level, but am having a brain lock on any single query which would walk a chain of arbitrary length. Am I missing something obvious? If not, does anyone have any brilliant ideas? John Elrick Fenestra Technologies Example...it can be assumed that the actual hierarchy will run from 0..n children for any given node. CREATE TABLE FOO ( PARENT_ID INTEGER, CHILD_ID INTEGER ); CREATE TRIGGER FOO_AD1 AFTER DELETE ON FOO BEGIN DELETE FROM FOO WHERE PARENT_ID = old.CHILD_ID; END; INSERT INTO FOO VALUES (1,2); INSERT INTO FOO VALUES (2,3); INSERT INTO FOO VALUES (2,4); INSERT INTO FOO VALUES (3,5); COMMIT; SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"], ["1", "2"], ["2", "3"], ["2", "4"], ["3", "5"]] DELETE FROM FOO WHERE PARENT_ID = 1 The result SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"], ["3", "5"]] The desired result SELECT * FROM FOO => [["PARENT_ID", "CHILD_ID"]] _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Igor Tandetnik
::
Rate this Message:
John Elrick <john.elrick@...> wrote:
> I'm a bit stumped and was curious if anyone had an elegant solution > for this problem. Assuming the following simplified example, my goal > is to cascade the deletes until all parent/child relations have been > deleted. The trigger removes the first level, but stops there (I > believe this behavior is documented). I can think of a delete query > which would also remove the first level, but am having a brain lock > on any single query which would walk a chain of arbitrary length. It's impossible in pure SQL, unless the DBMS supports special syntax for recursive queries, and/or recursive triggers. SQLite supports neither. You would have to code the recursion in your host application. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by John Elrick-2
::
Rate this Message:
Igor Tandetnik wrote:
> John Elrick <john.elrick@...> wrote: > >> I'm a bit stumped and was curious if anyone had an elegant solution >> for this problem. Assuming the following simplified example, my goal >> is to cascade the deletes until all parent/child relations have been >> deleted. The trigger removes the first level, but stops there (I >> believe this behavior is documented). I can think of a delete query >> which would also remove the first level, but am having a brain lock >> on any single query which would walk a chain of arbitrary length. >> > > It's impossible in pure SQL, unless the DBMS supports special syntax for > recursive queries, and/or recursive triggers. SQLite supports neither. > You would have to code the recursion in your host application. > I was afraid that was the case. Thank you Igor. John _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Nicolas Williams
::
Rate this Message:
On Fri, Feb 22, 2008 at 10:46:00AM -0500, Igor Tandetnik wrote:
> John Elrick <john.elrick@...> wrote: > > I'm a bit stumped and was curious if anyone had an elegant solution > > for this problem. Assuming the following simplified example, my goal > > is to cascade the deletes until all parent/child relations have been > > deleted. The trigger removes the first level, but stops there (I > > believe this behavior is documented). I can think of a delete query > > which would also remove the first level, but am having a brain lock > > on any single query which would walk a chain of arbitrary length. > > It's impossible in pure SQL, unless the DBMS supports special syntax for > recursive queries, and/or recursive triggers. SQLite supports neither. > You would have to code the recursion in your host application. IIUC ANSI SQL has a WITH keyword and support for recursive queries. SQLite doesn't support this, of course. But it could (whether it will is another story). Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by John Elrick-2
::
Rate this Message:
Nicolas Williams wrote:
> On Fri, Feb 22, 2008 at 10:46:00AM -0500, Igor Tandetnik wrote: > >> John Elrick <john.elrick@...> wrote: >> >>> I'm a bit stumped and was curious if anyone had an elegant solution >>> for this problem. Assuming the following simplified example, my goal >>> is to cascade the deletes until all parent/child relations have been >>> deleted. The trigger removes the first level, but stops there (I >>> believe this behavior is documented). I can think of a delete query >>> which would also remove the first level, but am having a brain lock >>> on any single query which would walk a chain of arbitrary length. >>> >> It's impossible in pure SQL, unless the DBMS supports special syntax for >> recursive queries, and/or recursive triggers. SQLite supports neither. >> You would have to code the recursion in your host application. >> > > IIUC ANSI SQL has a WITH keyword and support for recursive queries. > > SQLite doesn't support this, of course. But it could (whether it will > is another story). > A recursive trigger would handle this issue nicely...a way of SELECTing a hierarchy would also be nice, but I believe there is nothing standardized for that particular operation. John _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Dennis Cote
::
Rate this Message:
John Elrick wrote:
> I'm a bit stumped and was curious if anyone had an elegant solution for > this problem. Assuming the following simplified example, my goal is to > cascade the deletes until all parent/child relations have been deleted. > The trigger removes the first level, but stops there (I believe this > behavior is documented). I can think of a delete query which would also > remove the first level, but am having a brain lock on any single query > which would walk a chain of arbitrary length. > > Am I missing something obvious? No. > If not, does anyone have any brilliant ideas? > See http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=managing+trees+database for a method I use to manage trees in SQLite that works very well. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Nicolas Williams
::
Rate this Message:
On Fri, Feb 22, 2008 at 11:14:41AM -0500, John Elrick wrote:
> > IIUC ANSI SQL has a WITH keyword and support for recursive queries. > > > > SQLite doesn't support this, of course. But it could (whether it will > > is another story). > > A recursive trigger would handle this issue nicely...a way of SELECTing > a hierarchy would also be nice, but I believe there is nothing > standardized for that particular operation. I'm not familiar with the specs, so I can't tell you for sure, but the results for my searches seem to indicate that WITH recursive queries are part of ANSI/ISO SQL. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Dennis Cote
::
Rate this Message:
John Elrick wrote:
> > A recursive trigger would handle this issue nicely...a way of SELECTing > a hierarchy would also be nice, but I believe there is nothing > standardized for that particular operation. > As Nicolas said, SQL:1999 defines a standard method of doing this. It uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been widely implemented, but I believe that IBM's DB2 supports this mechanism. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Nicolas Williams
::
Rate this Message:
On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote:
> As Nicolas said, SQL:1999 defines a standard method of doing this. It > uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been > widely implemented, but I believe that IBM's DB2 supports this mechanism. Ah, good to know. Would there be interest in supporting that in SQLite? I think it'd be very useful (particularly given the populatiry of XML). _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by John Elrick-2
::
Rate this Message:
Nicolas Williams wrote:
> On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote: > >> As Nicolas said, SQL:1999 defines a standard method of doing this. It >> uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been >> widely implemented, but I believe that IBM's DB2 supports this mechanism. >> > > Ah, good to know. Would there be interest in supporting that in SQLite? > I think it'd be very useful (particularly given the populatiry of XML). I could be mistaken, however, I believe that recursive triggers or recursive selects would be highly useful as a substitute for enforcing referential integrity, especially cascading deletes. John _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Samuel Neff
::
Rate this Message:
I don't agree with the XML anaology. As I understand it, recursion in SQL
is referring to self-referencing tables that create a parent/child relationship. This generally does not apply to XML since XML is hierarchical but usually not recursive (i.e., the children are not the same elements as the parents). Certainly it's possible and does happen, but for the most part the relationship between XML elements can be easily mapped to relational tables and does not require recursive SQL to process. Oracle has had recursive SQL support for a while and it's very helpful when appropriately used. MSSQL adds hierarchical data types in 2008 to support similar functionality. HTH, Sam On Fri, Feb 22, 2008 at 11:32 AM, Nicolas Williams <Nicolas.Williams@...> wrote: > On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote: > > As Nicolas said, SQL:1999 defines a standard method of doing this. It > > uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been > > widely implemented, but I believe that IBM's DB2 supports this > mechanism. > > Ah, good to know. Would there be interest in supporting that in SQLite? > I think it'd be very useful (particularly given the populatiry of XML). > _______________________________________________ > sqlite-users mailing list > sqlite-users@... > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Nicolas Williams
::
Rate this Message:
On Fri, Feb 22, 2008 at 11:51:08AM -0500, Samuel Neff wrote:
> I don't agree with the XML anaology. As I understand it, recursion in SQL > is referring to self-referencing tables that create a parent/child > relationship. This generally does not apply to XML since XML is > hierarchical but usually not recursive (i.e., the children are not the same > elements as the parents). Certainly it's possible and does happen, but for > the most part the relationship between XML elements can be easily mapped to > relational tables and does not require recursive SQL to process. You've clearly not used xml2rfc. The schema for writing Internet-Drafts and RFCs in XML certainly does have recursive elements (e.g., the <t> and <list> elements). > Oracle has had recursive SQL support for a while and it's very helpful when > appropriately used. MSSQL adds hierarchical data types in 2008 to support > similar functionality. Yes. _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Dennis Cote
::
Rate this Message:
Samuel Neff wrote:
> As I understand it, recursion in SQL > is referring to self-referencing tables that create a parent/child > relationship. Actually the WITH RECURSIVE clause in SQL:1999 handles multiple mutually recursive tables as well as self referential tables. Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Dennis Cote
::
Rate this Message:
Nicolas Williams wrote:
> > Ah, good to know. Would there be interest in supporting that in SQLite? > I think it'd be very useful (particularly given the populatiry of XML). I don't know how Richard feels about it, but it is very useful for the common case of hierarchical bill of materials processing. I'm not sure how useful it would be for XML unless you were trying to store a parse tree of an XML document in your database. Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Thufir
::
Rate this Message:
On Fri, 22 Feb 2008 10:46:00 -0500, Igor Tandetnik wrote:
> It's impossible in pure SQL, unless the DBMS supports special syntax for > recursive queries, and/or recursive triggers. But isn't recursion, for better or worse, part of the SQL:2003 standard? -Thufir _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Dennis Cote
::
Rate this Message:
Thufir wrote:
> > But isn't recursion, for better or worse, part of the SQL:2003 standard? > It's an optional part of the SQL:1999 standard that is not widely implemented. Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
|
|
Re: Hierarchical Deletion via a Trigger?
by Klemens Friedl
::
Rate this Message:
2008/2/22, Dennis Cote <dennis.cote@...>:
http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=managing+trees+database > for a method I use to manage trees in SQLite that works very well. another method is to implement it like this (or similar): http://www.codeproject.com/KB/database/Trees_in_SQL_databases.aspx it should be faster for huge database tables in comparision to the slower LIKE comparision. Klemens Friedl _______________________________________________ sqlite-users mailing list sqlite-users@... http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users |
| Free embeddable forum powered by Nabble | Forum Help |
