From 80c5f1cd32c06977da69c8a815ffa35ce9a909f1 Mon Sep 17 00:00:00 2001 From: Přemysl Eric Janouch 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