diff options
Diffstat (limited to 'initialize.sql')
| -rw-r--r-- | initialize.sql | 22 |
1 files changed, 20 insertions, 2 deletions
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), |