From 80c5f1cd32c06977da69c8a815ffa35ce9a909f1 Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Tue, 12 Dec 2023 06:37:20 +0100
Subject: Think about garbage collection

---
 initialize.sql | 22 ++++++++++++++++++++--
 1 file changed, 20 insertions(+), 2 deletions(-)

(limited to 'initialize.sql')

diff --git a/initialize.sql b/initialize.sql
index 36af2aa..4e2c56e 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -10,7 +10,6 @@ CREATE INDEX IF NOT EXISTS image__dhash ON image(dhash);
 
 --
 
--- NOTE: This table requires garbage collection. Perhaps as a trigger.
 CREATE TABLE IF NOT EXISTS directory(
 	id       INTEGER NOT NULL,                  -- unique ID
 	name     TEXT NOT NULL,                     -- basename
@@ -34,6 +33,26 @@ CREATE TABLE IF NOT EXISTS entry(
 
 CREATE INDEX IF NOT EXISTS entry__sha1 ON entry(sha1);
 
+/*
+Automatic garbage collection, not sure if it actually makes any sense:
+
+CREATE TRIGGER IF NOT EXISTS entry__parent__gc
+AFTER DELETE ON entry FOR EACH ROW
+BEGIN
+	DELETE FROM directory WHERE id = OLD.parent
+	AND id NOT IN (SELECT DISTINCT parent FROM entry)
+	AND id NOT IN (SELECT DISTINCT parent FROM directory);
+END;
+
+CREATE TRIGGER IF NOT EXISTS directory__parent__gc
+AFTER DELETE ON directory FOR EACH ROW
+BEGIN
+	DELETE FROM directory WHERE id = OLD.parent
+	AND id NOT IN (SELECT DISTINCT parent FROM entry)
+	AND id NOT IN (SELECT DISTINCT parent FROM directory);
+END;
+*/
+
 --
 
 CREATE TABLE IF NOT EXISTS tag_space(
@@ -50,7 +69,6 @@ INSERT INTO tag_space(id, name, description)
 VALUES(0, '', 'User-defined tags')
 ON CONFLICT DO NOTHING;
 
--- NOTE: This table requires garbage collection. Perhaps as a trigger.
 CREATE TABLE IF NOT EXISTS tag(
 	id       INTEGER NOT NULL,
 	space    INTEGER NOT NULL REFERENCES tag_space(id),
-- 
cgit v1.2.3-70-g09d2

