summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorPřemysl Eric Janouch <p@janouch.name>2023-12-21 23:58:32 +0100
committerPřemysl Eric Janouch <p@janouch.name>2023-12-21 23:58:32 +0100
commita3129ce9179ea8816c6442b73e61b7ba7bd2e49a (patch)
tree2a299f736ba4eaafdfb2ea62ac43c6d47bd726eb
parent2b5e3bd4eade8010c036633e5c69a2ce79a5ba15 (diff)
downloadgallery-a3129ce9179ea8816c6442b73e61b7ba7bd2e49a.tar.gz
gallery-a3129ce9179ea8816c6442b73e61b7ba7bd2e49a.tar.xz
gallery-a3129ce9179ea8816c6442b73e61b7ba7bd2e49a.zip
WIP: FS to DB sync
-rw-r--r--initialize.sql2
-rw-r--r--main.go71
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;
diff --git a/main.go b/main.go
index f63a42a..4f65e0b 100644
--- a/main.go
+++ b/main.go
@@ -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 {