> On Mar 18, 2019, at 5:21 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> UPDATE tree
> SET position = (SELECT position FROM _children WHERE id = tree.id) --
> Multiply by x to number by x
> WHERE id IN (SELECT id FROM _children);
> DELETE FROM _children;
> END;
I don’t see the window function causing a significant performance loss, but
your UPDATE statement is much better. You could also get rid of the gentleman’s
agreement by temporarily setting both parent and position to NULL.
CREATE TEMP VIEW normalize_tree(parent) AS SELECT NULL;
CREATE TEMP TABLE _children(id INTEGER PRIMARY KEY, position REAL);
CREATE TEMP TRIGGER normalize_tree_impl INSTEAD OF UPDATE ON normalize_tree
BEGIN
INSERT INTO _children
SELECT id, row_number() OVER (ORDER BY position)
FROM tree
WHERE parent = new.parent
ORDER BY position;
UPDATE tree
SET (parent, position) = (NULL, NULL)
WHERE id IN (SELECT id FROM _children);
UPDATE tree
SET (parent, position) = (new.parent, (SELECT position FROM
_children WHERE id = tree.id <http://tree.id/>))
WHERE id IN (SELECT id FROM _children);
DELETE FROM _children;
END;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
