>I have a (small) directed graph which I would be able to fins all >ancestors or descendents of a certain vertex (transitive closure?). >So, using this graph: > >CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER, >UNIQUE(parent_id, child_id)); >INSERT INTO "levels_levels" VALUES(6,7); >INSERT INTO "levels_levels" VALUES(6,8); >INSERT INTO "levels_levels" VALUES(8,9); >INSERT INTO "levels_levels" VALUES(7,10); >INSERT INTO "levels_levels" VALUES(9,10); > >which would look like this: > > 6 >7 8 >| 9 >10 > >I would like to make it possible to find 10 as a descendent of 8 and 6 >as a parent of 9 (for instance). > >I have found a couple of procedural solutions using procedural calls >in sql server or postgresql, but is there a solution that I could get >into sqlite? > >The graph will never be very big, updating efficiency is not an >important factor. It will be queried a lot though, so search >efficiency is important.
Re-implement your levels_levels table as a couple of twin trees (father, mother) using integer intervals (lookup nested tree). All common queries into that can then be made very efficiently using a single SQL statement. Inserts (and updates but are there many in genealogy?) will definitely take longer, but your tree probably doesn't see too many new leaves each second! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
