Maintain directories in a hierarchy

1 files changed, 21 insertions, 9 deletions

diff --git a/initialize.sql b/initialize.sql
index cb4e3a7..0f43af5 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -1,23 +1,35 @@
 CREATE TABLE IF NOT EXISTS image(
-	sha1     TEXT NOT NULL,     -- SHA-1 hash of file in lowercase hexadecimal
-	thumbw   INTEGER,           -- cached thumbnail width, if known
-	thumbh   INTEGER,           -- cached thumbnail height, if known
-	dhash    INTEGER,           -- uint64 perceptual hash as a signed integer
+	sha1     TEXT NOT NULL,  -- SHA-1 hash of file in lowercase hexadecimal
+	thumbw   INTEGER,        -- cached thumbnail width, if known
+	thumbh   INTEGER,        -- cached thumbnail height, if known
+	dhash    INTEGER,        -- uint64 perceptual hash as a signed integer
 	PRIMARY KEY (sha1)
 ) STRICT;
 
 CREATE INDEX IF NOT EXISTS image_dhash ON image(dhash, sha1);
 
--- XXX: The directory hierarchy should be perhaps kept normalized.
+--
+
+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
+	PRIMARY KEY (id)
+) STRICT;
+
+CREATE UNIQUE INDEX IF NOT EXISTS directory_parent ON directory(parent, name);
+
 CREATE TABLE IF NOT EXISTS entry(
-	path     TEXT NOT NULL,     -- full FS directory path
-	basename TEXT NOT NULL,     -- last FS path component
+	parent   INTEGER 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 (path, basename)
+	PRIMARY KEY (parent, name)
 ) STRICT;
 
-CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename);
+CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, parent, name);
+
+--
 
 CREATE TABLE IF NOT EXISTS image_tag(
 	sha1     TEXT NOT NULL REFERENCES image(sha1),