diff options
| -rw-r--r-- | initialize.sql | 2 | ||||
| -rw-r--r-- | main.go | 36 |
2 files changed, 28 insertions, 10 deletions
diff --git a/initialize.sql b/initialize.sql index 60b50f0..f439953 100644 --- a/initialize.sql +++ b/initialize.sql @@ -49,7 +49,7 @@ END; CREATE TABLE IF NOT EXISTS orphan( sha1 TEXT NOT NULL REFERENCES image(sha1) - path TEXT NOT NULL, + path TEXT NOT NULL, -- last occurence within the database hierarchy PRIMARY KEY (sha1) ) STRICT; @@ -975,15 +975,33 @@ func syncDequeue(c *syncContext) error { // // Orphans keep their thumbnail files, as evidence. func syncDispose(c *syncContext, nodeID int64, keepNode bool) error { - // TODO: Implement. - // - When collecting node subtrees, we need to delete bottom-up - // because of foreign key constraints, - // so maybe in reverse order of recursive CTE results. - // - Sadly, this can't be done with a DB trigger. (What and why?) - // - One of the inputs needs to be the FS path, for the orphan table. - // - I may not have the FS path (symlink). - // - I can just recursively select for the path based on nodeID. - return nil + rows, err := c.tx.Query(`WITH RECURSIVE + root(id, parent, path) AS ( + SELECT id, parent, name FROM node WHERE id = ? + UNION ALL + SELECT r.id, node.parent, node.name || '/' || r.path + FROM node JOIN root AS r ON node.id = r.parent + ), + children(id, path, level) AS ( + SELECT id, path, 1 FROM root WHERE parent IS NULL + UNION ALL + SELECT node.id, c.path || '/' || node.name, c.level + 1 + FROM node JOIN children AS c ON node.parent = c.id + ) + SELECT id, path FROM children ORDER BY level DESC`, nodeID) + if err != nil { + return err + } + defer rows.Close() + + // TODO: Process. + // - Actually, I need to do two things here, in sequence: + // - Reinsert sha1, path into orphan. + // - Delete all children.id, with the exception of nodeID if keepNode. + // - I would like to avoid doing this in Go, if at all possible. + for rows.Next() { + } + return rows.Err() } func syncImage(c *syncContext, info syncFileInfo) error { |