From 8da775f61daee071411758e6e86cb3e1e7cc5689 Mon Sep 17 00:00:00 2001 From: Přemysl Eric Janouch Date: Mon, 18 Dec 2023 21:11:06 +0100 Subject: Merge the hierarchy into a single table --- initialize.sql | 54 ++++++++++++++++++++++-------------------------------- 1 file changed, 22 insertions(+), 32 deletions(-) (limited to 'initialize.sql') diff --git a/initialize.sql b/initialize.sql index f42e55b..9698c69 100644 --- a/initialize.sql +++ b/initialize.sql @@ -12,46 +12,36 @@ CREATE INDEX IF NOT EXISTS image__dhash ON image(dhash); -- -CREATE TABLE IF NOT EXISTS directory( - id INTEGER NOT NULL, -- unique ID - name TEXT NOT NULL, -- basename - parent INTEGER REFERENCES directory(id), -- root if NULL +CREATE TABLE IF NOT EXISTS node( + id INTEGER NOT NULL, -- unique ID + parent INTEGER REFERENCES node(id), -- root if NULL + name TEXT NOT NULL, -- path component + mtime INTEGER, -- files: Unix time in seconds + sha1 TEXT REFERENCES image(sha1), -- files: content hash PRIMARY KEY (id) ) STRICT; -CREATE UNIQUE INDEX IF NOT EXISTS directory__parent_name -ON directory(IFNULL(parent, 0), name); +CREATE INDEX IF NOT EXISTS node__sha1 ON node(sha1); +CREATE UNIQUE INDEX IF NOT EXISTS node__parent_name +ON node(IFNULL(parent, 0), name); --- FIXME: I want a nullable parent, but that can't be a primary key. --- - Perhaps have an INTEGER for the PK of entry, and use a UNIQUE INDEX. --- - Alternatively, create a directory record for the root. -CREATE TABLE IF NOT EXISTS entry( - parent INTEGER NOT NULL REFERENCES directory(id), - name TEXT NOT NULL, -- last FS path component - mtime INTEGER NOT NULL, -- Unix time of last modification in seconds - sha1 TEXT NOT NULL REFERENCES image(sha1), - PRIMARY KEY (parent, name) -) STRICT; - -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 +CREATE TRIGGER IF NOT EXISTS node__sha1__check +BEFORE UPDATE OF sha1 ON node +WHEN OLD.sha1 IS NULL AND NEW.sha1 IS NOT NULL +AND EXISTS(SELECT id FROM node WHERE parent = OLD.id) 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); + SELECT RAISE(ABORT, 'trying to turn a non-empty directory into a file'); END; -CREATE TRIGGER IF NOT EXISTS directory__parent__gc -AFTER DELETE ON directory FOR EACH ROW +/* +Automatic garbage collection, not sure if it actually makes any sense. +It's also not recursive, but it should be: + +CREATE TRIGGER IF NOT EXISTS node__parent__gc +AFTER DELETE ON node 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); + DELETE FROM node WHERE id = OLD.parent + AND id NOT IN (SELECT DISTINCT parent FROM node); END; */ -- cgit v1.2.3-70-g09d2