Hierarchical Queries with Looping in SQLite DB

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Hierarchical Queries with Looping in SQLite DB

Ashif Ahamed
I 'm trying to achieve , oracle's hierarchical CONNECT BY NOCYCLE PRIOR in
SQLite database hierarchical queries i.e. with clause queries(common table
expression)

When there is a loop in the data ,with clause queries in SQLite database is
running for long with infinite loops

Consider the below sample data:   (With Out Loop)

CREATE TABLE FOLDER(ID INT , NAME VARCHAR(255), PARENT INT);

INSERT INTO FOLDER VALUES(1, null, null);
INSERT INTO FOLDER VALUES(2, 'src', 1);
INSERT INTO FOLDER VALUES(3, 'main', 2);
INSERT INTO FOLDER VALUES(4, 'org', 3);
INSERT INTO FOLDER VALUES(5, 'test', 2);


WITH LINK(ID, NAME, LEVEL1) AS (
    SELECT ID, NAME, 0  LEVEL1 FROM FOLDER WHERE PARENT IS NULL
    UNION ALL
    SELECT FOLDER.ID <http://folder.id/>, coalesce(LINK.NAME
<http://link.name/> || '/', '') || FOLDER.NAME <http://folder.name/>,
LEVEL1 + 1
    FROM LINK INNER JOIN FOLDER ON LINK.ID <http://link.id/> = FOLDER.PARENT
)
SELECT * FROM LINK WHERE NAME IS NOT NULL ORDER BY ID

Above query works fine because there is no loop in data.


When there is some data bringing looping scenario  :

*INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);*


After inserting this loop data , when i trigger the above recursive query
in  SQLite it keeps on running without bringing any results.


Note: In oracle database , this kind of scenario is handled by connect by
nocycle prior or cycle column set is_cycle to '1' default '0'


How to handle this scenario in SQLite?

Can anyone look on into this..

--
 Ashif Ahamed . A
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Hierarchical Queries with Looping in SQLite DB

Clemens Ladisch
Ashif Ahamed wrote:
> I 'm trying to achieve , oracle's hierarchical CONNECT BY NOCYCLE PRIOR in
> SQLite database hierarchical queries i.e. with clause queries(common table
> expression)
>
> When there is a loop in the data ,with clause queries in SQLite database is
> running for long with infinite loops

When you use UNION instead of UNION ALL, duplicate rows are ignored.

> WITH LINK(ID, NAME, LEVEL1) AS (
>     SELECT ID, NAME, 0  LEVEL1 FROM FOLDER WHERE PARENT IS NULL
>     UNION ALL
>     SELECT FOLDER.ID <http://folder.id/>, coalesce(LINK.NAME
> <http://link.name/> || '/', '') || FOLDER.NAME <http://folder.name/>,
> LEVEL1 + 1
>     FROM LINK INNER JOIN FOLDER ON LINK.ID <http://link.id/> = FOLDER.PARENT
> )
> SELECT * FROM LINK WHERE NAME IS NOT NULL ORDER BY ID

The level value would prevent the duplicate detection.
Do you really need it?

You could add a LIMIT clause, which at least prevents the infinite loop.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Hierarchical Queries with Looping in SQLite DB

Jean-Luc Hainaut
In reply to this post by Ashif Ahamed
On 06/07/2017 08:08, Ashif Ahamed wrote:

>
> When there is some data bringing looping scenario  :
>
> *INSERT INTO FOLDER VALUES(2, 'Loop Data', 5);*
>
> After inserting this loop data , when i trigger the above recursive query
> in  SQLite it keeps on running without bringing any results.
>
> Note: In oracle database , this kind of scenario is handled by connect by
> nocycle prior or cycle column set is_cycle to '1' default '0'
>
> How to handle this scenario in SQLite?
>
> Can anyone look on into this..

You could use one of the three techniques suggested in this document.

https://www.dropbox.com/s/5tsh7lod3oqv3h5/Tuto19-Recursive-programming.pdf?dl=0

J-L Hainaut

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...