jeudi 17 septembre 2015

Cannot detect cyclic data in an SQLite database

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