From a3129ce9179ea8816c6442b73e61b7ba7bd2e49a Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Thu, 21 Dec 2023 23:58:32 +0100
Subject: WIP: FS to DB sync

---
 initialize.sql |  2 +-
 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;
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 {
-- 
cgit v1.2.3-70-g09d2