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

---
 initialize.sql |  2 +-
 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;
 
diff --git a/main.go b/main.go
index 54cea82..f63a42a 100644
--- a/main.go
+++ b/main.go
@@ -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 {
-- 
cgit v1.2.3-70-g09d2