Philipp Knüsel wrote:
SanjayK schrieb:
Since SQLite is perfect for use in single-user desktop utility
applications
and since such applications typically store hierarchial data (tree) in a
single table, it would be nice to have support for special features like
connect by of oracle.
See: http://www.adp-gmbh.ch/ora/sql/connect_by.html
--
View this message in context:
http://www.nabble.com/Managing-trees-in-the-database-t1135555.html#a2974277
Sent from the SQLite forum at Nabble.com.
Depending on your goals, this concept might give you another solution:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
As the code examples are for mysql, you need to adapt it a bit.
HTH
Philipp
The following is an implementation of the materialized path method of
storing trees in an SQL database. I have used this method in the past
with fairly large trees (about 30K nodes and around 10 levels). I find
it works quite well and it's a lot simpler to understand than the nested
set approach. For the types of queries I'm doing it usually results in
simpler queries as well.
It adds one variable length text field to each record to store the path.
It uses triggers to automatically maintain this path and implement
cascaded deletes.
It might be suitable for your needs as well.
-- Sample materialized path implementation of a tree in SQL
--
-- The materialized path is a string containing the path to the node
-- represented by the node ids along the path separated by / characters.
-- This string is easy to search using the LIKE comparison function
-- to check for path characteristics. All the methods that apply to the
-- adjacency list representation can also be used with this format.
-- The path string is maintained automatically by triggers. Any node
-- inserted with a NULL parent_id is the root of a new tree.
create table tree (
id integer primary key,
parent_id integer references tree,
data text,
path text -- materialized path
);
-- set path to node when it is inserted
create trigger tree_in after insert on tree
begin
update tree set path =
case when parent_id isnull then '/'
else (select path from tree where id = new.parent_id) ||
parent_id || '/'
end
where id = new.id;
end;
-- delete subtree below node when a node is deleted
create trigger tree_del before delete on tree
begin
delete from tree where id in
(select id from tree
where path like old.path || old.id || '/%');
end;
-- example
insert into tree (id, parent_id, data) values (1, NULL, 'parent');
insert into tree (id, parent_id, data) values (NULL, 1, 'son');
insert into tree (id, parent_id, data) values (NULL, 1, 'daughter');
insert into tree (id, parent_id, data) values (NULL, 2, 'grandchild');
select * from tree;
-- find all root nodes
select * from tree where parent_id isnull;
-- find all leaf nodes
select * from tree where id not in (select parent_id from tree);
-- find all nodes in the sub tree below node
select * from tree
where path like (select path || id || '/%' from tree where id =
:node_id);
-- find all nodes along the path to node
select * from tree
where (select path || id || '/' from tree where id = :node_id)
like path || id || '/%'
order by path;
-- find all nodes on level 3 or 4
select * from tree
where path like '/%/%/' or path like '/%/%/%/';
HTH
Dennis Cote
