Using SQLite, I have a suspected case of recursive data that has cycle - in other words, a child node is also its own grandparent. The symptom, of course, is an infinite loop
I know how Oracle and Postgresql handle cyclic data; but have not found any method to do so with SQLite. Below is an example of data with a cycle. If you remove the last "row" in the dataset table, it will work. As written, it goes into an infinite loop.
with DataSet as
(
select 'A' as node, null as parent union all
select 'B' as node, 'A' as parent union all
select 'C' as node, 'B' as parent union all
select 'D' as node, 'C' as parent union all
select 'A' as node, 'D' as parent
),
Hierarchy( node, parent, level, path )
as
(
select DataSet.node,
DataSet.parent,
1 as level,
'/' || DataSet.node as path
from DataSet
where DataSet.parent is null
union all
select DataSet.node,
DataSet.parent,
Hierarchy.level + 1 as level,
Hierarchy.path || '/' || DataSet.node as path
from Hierarchy
join DataSet
on DataSet.parent = Hierarchy.node
)
select *
from Hierarchy
order by path
;
Aucun commentaire:
Enregistrer un commentaire