transitive-closure-table example

Question

I know that transitive-closure concept is used for storing tree structures data. This concept also used to retrieve hierarchical data in very efficient and quickly with minimum complex query.

In SQLite Query browser, I have tried these queries:

CREATE TABLE category (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(255),
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES category (id)
); 

CREATE VIRTUAL TABLE category_closure USING transitive_closure (
    tablename="category",
    idcolumn="id",
    parentcolumn="parent_id"
);

I am able to create table using query no. 1 but query no. 2 is not working.

Can someone provide specific example in Android using SQLite?

@CL. in SQLite Query browser, I have fired below queries. 1. CREATE TABLE category ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(255), parent_id INTEGER, FOREIGN KEY (parent_id) REFERENCES category (id)); 2. CREATE VIRTUAL TABLE category_closure USING transitive_closure ( tablename="category", idcolumn="id", parentcolumn="parent_id"); I can able to create table using query no. 1 but query no. 2 is not working. can you please look into it? — Durgesh Patel, Jul 20 '15 at 06:09
Where did you learn about the `transitive_closure` module? It is neither standard SQL, nor part of the Android API. You have to do all the queries manually. — CL., Jul 20 '15 at 06:46
I have found while reading this blog http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ also on sqlite official site http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012 Please have a look and let me know your feedback for the same. — Durgesh Patel, Jul 22 '15 at 05:13

score 1 · Answer 1 · answered Jul 22 '15 at 07:47

As shown in the blog post Querying Tree Structures in SQLite using Python and the Transitive Closure Extension, the transitive closure extension must be compiled manually. This is not possible when using the default Android database API.

You could use triggers to maintain an explicit closure table.

transitive-closure-table example

1 Answers1