aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--initialize.sql22
-rw-r--r--main.go3
2 files changed, 22 insertions, 3 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),
diff --git a/main.go b/main.go
index ca54ac0..71c3759 100644
--- a/main.go
+++ b/main.go
@@ -583,7 +583,8 @@ func cmdTag(args []string) error {
// or even just clear the tag space completely:
//
// DELETE FROM tag_assignment
- // WHERE tag IN (SELECT id FROM tag WHERE space = ?)
+ // WHERE tag IN (SELECT id FROM tag WHERE space = ?);
+ // DELETE FROM tag WHERE space = ?;
stmt, err := tx.Prepare(`INSERT INTO tag_assignment(sha1, tag, weight)
VALUES (?, (SELECT id FROM tag WHERE space = ? AND name = ?), ?)
ON CONFLICT DO UPDATE SET weight = ?`)