From 068f6f82cfee723e6c6213ea58d05c9e9708e131 Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Thu, 21 Dec 2023 06:19:03 +0100
Subject: WIP: FS to DB sync

---
 initialize.sql | 17 ++++++++++++++++-
 1 file changed, 16 insertions(+), 1 deletion(-)

(limited to 'initialize.sql')

diff --git a/initialize.sql b/initialize.sql
index c230f18..60b50f0 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -47,13 +47,28 @@ END;
 
 --
 
--- TODO: Add a trigger to delete from here when a node for the sha1 is added.
 CREATE TABLE IF NOT EXISTS orphan(
 	sha1 TEXT NOT NULL REFERENCES image(sha1)
 	path TEXT NOT NULL,
 	PRIMARY KEY (sha1)
 ) STRICT;
 
+-- Renaming/moving a file can result either in a (ref, unref) or a (unref, ref)
+-- sequence during sync, and I want to get at the same result.
+CREATE TRIGGER IF NOT EXISTS node__sha1__deorphan_insert
+AFTER INSERT ON node
+WHEN NEW.sha1 IS NOT NULL
+BEGIN
+	DELETE FROM orphan WHERE sha1 = NEW.sha1;
+END;
+
+CREATE TRIGGER IF NOT EXISTS node__sha1__deorphan_update
+AFTER UPDATE OF sha1 ON node
+WHEN NEW.sha1 IS NOT NULL
+BEGIN
+	DELETE FROM orphan WHERE sha1 = NEW.sha1;
+END;
+
 --
 
 CREATE TABLE IF NOT EXISTS tag_space(
-- 
cgit v1.2.3-70-g09d2

