diff options
| -rw-r--r-- | initialize.sql | 22 | ||||
| -rw-r--r-- | main.go | 3 |
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), @@ -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 = ?`) |