Merge the hierarchy into a single table

1 files changed, 22 insertions, 32 deletions

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;
 */