Nested set INSERT operation for SQLite

Question

I am trying to implement the nested-set model for a sqlite database. So far I have implemented getting leaf nodes, finding subordinates etc. I am using this tutorial as a reference However, I am stuck at inserting a new node. Here's the code from the site

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

I know that there's no lock table in sqlite.. my problem here is that I need to pass the result of the first query to the next one. Here it's done by using a user-created variable by @. I couldn't figure out how to do it in SQLite.

Thanks in advance.

score 4 · Accepted Answer · answered Jan 01 '12 at 11:40

An ideea is to replace the variable myRight with the query that generated it. That is to move the code into pure SQL.

UPDATE nested_category SET rgt = rgt + 2 
WHERE rgt > (SELECT rgt 
             FROM nested_category
             WHERE name = 'TELEVISIONS');

UPDATE nested_category SET lft = lft + 2 
WHERE lft > (SELECT rgt 
             FROM nested_category
             WHERE name = 'TELEVISIONS');

INSERT INTO nested_category(name, lft, rgt)
SELECT 'GAME CONSOLES', rgt + 1, rgt + 2 
FROM nested_category
WHERE name = 'TELEVISIONS' ;

Nested set INSERT operation for SQLite

1 Answers1