diff options
author | Přemysl Eric Janouch <p@janouch.name> | 2023-12-21 23:58:32 +0100 |
---|---|---|
committer | Přemysl Eric Janouch <p@janouch.name> | 2023-12-21 23:58:32 +0100 |
commit | a3129ce9179ea8816c6442b73e61b7ba7bd2e49a (patch) | |
tree | 2a299f736ba4eaafdfb2ea62ac43c6d47bd726eb | |
parent | 2b5e3bd4eade8010c036633e5c69a2ce79a5ba15 (diff) | |
download | gallery-a3129ce9179ea8816c6442b73e61b7ba7bd2e49a.tar.gz gallery-a3129ce9179ea8816c6442b73e61b7ba7bd2e49a.tar.xz gallery-a3129ce9179ea8816c6442b73e61b7ba7bd2e49a.zip |
WIP: FS to DB sync
-rw-r--r-- | initialize.sql | 2 | ||||
-rw-r--r-- | main.go | 71 |
2 files changed, 57 insertions, 16 deletions
diff --git a/initialize.sql b/initialize.sql index f439953..fc7d468 100644 --- a/initialize.sql +++ b/initialize.sql @@ -48,7 +48,7 @@ END; -- CREATE TABLE IF NOT EXISTS orphan( - sha1 TEXT NOT NULL REFERENCES image(sha1) + sha1 TEXT NOT NULL REFERENCES image(sha1), path TEXT NOT NULL, -- last occurence within the database hierarchy PRIMARY KEY (sha1) ) STRICT; @@ -975,33 +975,74 @@ func syncDequeue(c *syncContext) error { // // Orphans keep their thumbnail files, as evidence. func syncDispose(c *syncContext, nodeID int64, keepNode bool) error { + const cte = `WITH RECURSIVE + root(id, sha1, parent, path) AS ( + SELECT id, sha1, parent, name FROM node WHERE id = ? + UNION ALL + SELECT r.id, r.sha1, n.parent, n.name || '/' || r.path + FROM node AS n JOIN root AS r ON n.id = r.parent + ), + children(id, sha1, path, level) AS ( + SELECT id, sha1, path, 1 FROM root WHERE parent IS NULL + UNION ALL + SELECT n.id, n.sha1, c.path || '/' || n.name, c.level + 1 + FROM node AS n JOIN children AS c ON n.parent = c.id + ), + removed(sha1, count, path) AS ( + SELECT sha1, COUNT(*) AS count, MIN(path) AS path + FROM children + GROUP BY sha1 + ), + orphaned(sha1, path, count, total) AS ( + SELECT r.sha1, r.path, r.count, COUNT(*) AS total + FROM removed AS r + JOIN node ON node.sha1 = r.sha1 + GROUP BY node.sha1 + HAVING count = total + )` + + // TODO: Prepare the statements. + var err error + if _, err = c.tx.Exec(cte+` + INSERT OR IGNORE INTO orphan(sha1, path) + SELECT sha1, path FROM orphaned`, nodeID); err != nil { + return err + } + if keepNode { + _, err = c.tx.Exec(cte+` + DELETE FROM node + WHERE id IN (SELECT DISTINCT id FROM children WHERE level <> 1)`, + nodeID) + } else { + _, err = c.tx.Exec(cte+` + DELETE FROM node + WHERE id IN (SELECT DISTINCT id FROM children)`, + nodeID) + } + if err != nil { + return err + } + rows, err := c.tx.Query(`WITH RECURSIVE - root(id, parent, path) AS ( - SELECT id, parent, name FROM node WHERE id = ? + root(id, sha1, parent, path) AS ( + SELECT id, sha1, parent, name FROM node WHERE id = ? UNION ALL - SELECT r.id, node.parent, node.name || '/' || r.path + SELECT r.id, r.sha1, 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 + children(id, sha1, path, level) AS ( + SELECT id, sha1, path, 1 FROM root WHERE parent IS NULL UNION ALL - SELECT node.id, c.path || '/' || node.name, c.level + 1 + SELECT node.id, node.sha1, 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) + SELECT id, sha1, 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() + return nil } func syncImage(c *syncContext, info syncFileInfo) error { |