[sqlite] Regarding root in transitive closure extension of SQLite

[Sairam Gaddam]

> Hello !
>
> Look this comment on ext/misc/closure.c:
>
> --
>
> ** When it is created, the new transitive_closure table may be supplied
> ** with default values for the name of a table T and columns T.X and T.P.
> ** *The T.X and T.P columns must contain integers*.  The ideal case is
> for
> ** T.X to be the INTEGER PRIMARY KEY.  The T.P column should reference
> ** the T.X column. The row referenced by T.P is the parent of the current
> row
>

Can anyone kindly tell how to extent the support to other data types like
Text?

[sqlite] Regarding root in transitive closure extension of SQLite

[Sairam Gaddam]

>
> Given that SQLite supports CTE's now, why use that extension?
>
> AFAIK, it was to add hierarchical query capability to SQLite, which CTE
> does builtin now. --DD


But will that solve my problem of having the data type of groupId other
than Integer primary key like Text???

[sqlite] Regarding root in transitive closure extension of SQLite

[Sairam Gaddam]

I have enabled the transitive closure extension and I followed the steps
regarding querying the virtual table mentioned in closure.c file as follows

CREATE VIRTUAL TABLE ct1 USING transitive_closure(
   tablename='group1',
   idcolumn='groupId',
   parentcolumn='parentId'
   );

CREATE TABLE group1(
 groupId INTEGER PRIMARY KEY,
 parentId INTEGER REFERENCES group1
  );

CREATE INDEX group_idx1 ON group(parentId);

SELECT group1.* FROM group1, ct1
WHERE element.groupid=ct1.id
 AND ct1.root=?1
 AND ct1.depth<=2;

In the above query, can the root be assigned a text data type?

I tried with INTEGER data type for both groupId and parentId, it worked
fine but when they are TEXT or INT data type and if I query like
ct1.root='SQLITE' there was no output.

Can anyone kindly tell help me regarding this ?

[sqlite] Regarding root in transitive closure extension of SQLite

[Dominique Devienne]

On Thu, Aug 6, 2015 at 8:55 AM, Sairam Gaddam 
wrote:

> I have enabled the transitive closure extension and I followed the steps
> regarding querying the virtual table mentioned in closure.c file as follows
>

Given that SQLite supports CTE's now, why use that extension?

AFAIK, it was to add hierarchical query capability to SQLite, which CTE
does builtin now. --DD