InterDash Blogs

Sharing experience

SQLite and PCM-MVVMCross

SQLite and Window Phone (or Window Store) Applications

Working with SQLite and MVVMCross plugins I’ve established a ‘sweet’ environment to work in. However, SQLite for MVVMCross and the Portable Class Library doesn’t seem ‘tree ready’.

I’d love to hear from anyone on how to use WITH RECURSIVE (if it is part of this version) but since I couldn’t find any references other than ‘roll your own’ solutions anyway it seemed I was on my own.

To start, I couldn’t find any looping mechanism with SQLite. There is no creating of Stored Procedures. There is no easy update of a table using another table (at least that I could find).

The good news is SQLite does allow you to create TRIGGERs and VIEWs, and the use of sub-queries to customize behavior in a programmatic way.

A View may even have a trigger that updates the underlying database making the View updatable. In our case we are adding triggers to a Table but the concept is the same.

A Trigger can deal with managing referential updates. It appears that AFTER is the most stable path where the data appears in the database. In reading up on use of SQLite I’ve seen a recommendation to pull information in one large query instead of multiple small queries. I like to use Views that diminish the need for additional calls for data.

The result is a mixing Views and Triggers.

Path Management

A description of the Adjacency Model in SQL is at http://www.sqlteam.com/article/more-trees-hierarchies-in-sql and a functional example with explain at http://dje.me/2011/03/26/sqlite-data-trees.html for reference.

A quick tutorial on SQLite I found valuable is at http://linuxgazette.net/109/chirico1.html and another at

http://www.tutorialspoint.com/sqlite/sqlite_triggers.htm helped understand the documentation at http://www.sqlite.org/lang_select.html

Items with Path

The following diagram demonstrates our implementation

CREATE TABLE ‘Item'(

‘ItemId’ integer primary key autoincrement not null ,

‘Name’ varchar(140) ,

‘Description’ varchar(140) ,

‘ParentId’ integer );

CREATE TABLE ItemPath

(“ItemId” integer,

“ParentId” integer,

“Path” varchar(255) NULL,

“Depth” integer NULL

);

 

— Level -1 (Seed ItemPath with a ‘virtual root’)

insert into ItemPath

SELECT 0,-1,”,-1;


 

Item

TRIGGERs on Item manage ItemPath

CREATE TRIGGER delete_path AFTER DELETE ON Item FOR EACH ROW

BEGIN

DELETE FROM ItemPath

WHERE ItemId = old.ItemId;

END;

CREATE TRIGGER update_parentId AFTER UPDATE OF ParentId ON Item

BEGIN

UPDATE ItemPath SET Path = (SELECT p.Path || ‘/’ || new.Name FROM ItemPath p WHERE p.ItemId = new.ParentId),

            Depth = (SELECT d.Depth + 1 FROM ItemPath d WHERE d.ItemId = new.ParentId),

            ParentId = new.ParentId

WHERE ItemId = new.ItemId;

END;

CREATE TRIGGER insert_path AFTER INSERT ON Item FOR EACH ROW

BEGIN

    INSERT INTO ItemPath

     SELECT new.ItemId, new.ParentId, p.Path || ‘/’ || new.Name, p.Depth + 1

FROM ItemPath p WHERE p.ItemId = new.ParentId;

END;

VIEWS join Item with ItemPath and provide a way to ‘populate’ data recursively

CREATE VIEW ItemWithPath AS

SELECT t.*, p.Path, p.Depth

FROM Item t

LEFT JOIN ItemPath p ON t.ItemId = p.ItemId;

CREATE VIEW MissingPaths AS

SELECT t.ItemId, t.ParentId, p.Path || ‘/’ || t.Name, p.Depth + 1 AS ‘Depth’

FROM ItemWithPath t

JOIN ItemWithPath p ON t.ParentId = p.ItemId

WHERE P.Path IS NOT NULL AND T.Path IS NULL;

Rebuild ItemPath

Importing data requires building out ItemPath. It is also reasonable to assume that since the data is not a ‘true view’ there is a chance of corruption in the event a trigger fails.

 

The initial ItemPath value is a seed to establish a lookup for ItemId zero, which is the root. When a record is inserted into Item it must be within an established lineage or in root (ParentId=0).

 

After importing a tree the ItemPath values need to be created. Simple delete everything and start from scratch by DELETE FROM ItemPath.

 

Seed ItemPath with the ItemId=0 reference.

 

Seed ItemPath with root level (ParentId = 0) paths to establish Level 0.

 

If you select from MissingPaths you will now see the list of ‘Level 1’ rows. Selecting them into ItemPath loads the values for the next level.

 

If you select from MissingPaths now you will see a list of ‘Level 2’ rows. Selecting them into ItemPath loads the values for the next level.

 

When ‘MissingPaths’ returns zero rows all paths exist and the TRIGGERS on Item will take care of managing the paths.

–///////////////////////////////////////////////

DELETE FROM ItemPath;

 

— Level -1

insert into ItemPath

SELECT 0,-1,”,-1

 

— Level 0

INSERT INTO ItemPath

SELECT ItemId, ParentId, ‘/’ || Name AS ‘Path’, 0 AS ‘Depth’

FROM Item

WHERE ParentId = 0;

 

— Level 1

INSERT INTO ItemPath

SELECT * FROM MissingPaths;

 

— Level 2

INSERT INTO ItemPath

SELECT * FROM MissingPaths;

 

— Level n (Continue until MissingPaths is empty)

INSERT INTO ItemPath

SELECT * FROM MissingPaths;

 

–///////////////////////////////////////////

 

Summary

Using SQLite it is possible to implement a Tree structure though Triggers and programmatic process maintenance. I long for a looping mechanism or better understanding of the SQLite implementation to move this away from Triggers but after a day of working this out and looking for examples for using ‘WITH RECURIVE’ or a looping mechanism I could use in a trigger until MissingPaths is empty to assure ItemPath is always fully populated.

I hope this helps someone else out there trying to work with Tree data in SQLite.

 

 

Updated: March 2, 2014 — 12:07 am

Leave a Reply

Your email address will not be published. Required fields are marked *

InterDash Blogs © 2014 Frontier Theme