samedi 31 janvier 2015

SQLite 3 Tree List Without CTE


I adapted a SQL statement to work with SQLite 3. But unfortunately Common Table Expressions and WITH clause works only in SQLite 3.6 ahead, but the users of my Open Source application are using SQLite 3.2 and I cannot force them to update the whole Linux system to get the new packages. Is it possible to adapt the code to work without using a CTE and "With" Clause using only SQL Language?


Here's the code:



WITH
cte AS
(SELECT 0 AS level, collectionID, collectionName, parentCollectionID, CAST(collectionID AS VARCHAR(128)) AS Sort
FROM collections WHERE parentCollectionID IS NULL
UNION ALL
SELECT p.level + 1, c.collectionID, c.collectionName, c.parentCollectionID, CAST(p.Sort || '/' || CAST(c.collectionID AS VARCHAR) AS VARCHAR(128))
FROM collections c
INNER JOIN cte p ON p.collectionID = c.parentCollectionID)
SELECT
collectionID,
printf('%*s', level * 4, '') || collectionName AS collectionName,
Sort,
parentCollectionID
FROM cte
ORDER BY Sort;


Here's the result:



collectionID collectionName Sort parentCollectionID
1 Dissertação 1 0
10 Filosofia Reformacional 10 0
11 Dooyeweerd 11 0
14 ZotPad favorites 14 0
15 Diversos 15 0
2 Bíblia 2 0
3 Políticas Públicas 3 0
4 Zotero 4 0
5 Linux 5 0
6 Tese Doutorado 6 0
12 Pontal Do Paraná 6/12 6
7 Multimodal 6/7 6
13 Modalidades 6/7/13 7
8 Base Histórica 6/7/8 7
9 Artigo Weber 9 0


Thank you so much, Best regards, Christian





Aucun commentaire:

Enregistrer un commentaire