Re: [sqlite] Hierarchical Queries with Looping in SQLite DB

[Jean-Luc Hainaut]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 , coalesce(LINK.NAME
>  || '/', '') || FOLDER.NAME ,
> LEVEL1 + 1
> FROM LINK INNER JOIN FOLDER ON 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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 , coalesce(LINK.NAME
 || '/', '') || FOLDER.NAME ,
LEVEL1 + 1
FROM LINK INNER JOIN FOLDER ON 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users