Resolve some SQL issues

1 files changed, 30 insertions, 21 deletions

diff --git a/initialize.sql b/initialize.sql
index 63632c2..706580d 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -6,10 +6,11 @@ CREATE TABLE IF NOT EXISTS image(
 	PRIMARY KEY (sha1)
 ) STRICT;
 
-CREATE INDEX IF NOT EXISTS image_dhash_idx ON image(dhash, sha1);
+CREATE INDEX IF NOT EXISTS image__dhash ON image(dhash);
 
 --
 
+-- NOTE: This table requires garbage collection.
 CREATE TABLE IF NOT EXISTS directory(
 	id       INTEGER NOT NULL,                  -- unique ID
 	name     TEXT NOT NULL,                     -- basename
@@ -17,45 +18,53 @@ CREATE TABLE IF NOT EXISTS directory(
 	PRIMARY KEY (id)
 ) STRICT;
 
-CREATE UNIQUE INDEX IF NOT EXISTS directory_parent_idx
-ON directory(parent, name);
+CREATE UNIQUE INDEX IF NOT EXISTS directory__parent_name
+ON directory(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(
-	-- FIXME: I want a nullable parent, but that can't be a primary key.
-	--  - Perhaps have an INTEGER for the PK, and use a UNIQUE INDEX.
-	--  - Alternatively, create a directory record for the root.
-	parent   INTEGER REFERENCES directory(id),
+	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_idx ON entry(sha1, parent, name);
+CREATE INDEX IF NOT EXISTS entry__sha1 ON entry(sha1);
 
 --
 
--- These could also contain a description. In the future.
 CREATE TABLE IF NOT EXISTS tag_space(
+	id          INTEGER NOT NULL,
+	name        TEXT NOT NULL,
+	description TEXT,
+	PRIMARY KEY (id)
+) STRICT;
+
+CREATE UNIQUE INDEX IF NOT EXISTS tag_space__name ON tag_space(name);
+
+-- To avoid having to deal with NULLs, always create this special tag space.
+INSERT INTO tag_space(id, name, description)
+VALUES(0, '', 'User-defined tags')
+ON CONFLICT DO NOTHING;
+
+-- NOTE: This table requires garbage collection.
+CREATE TABLE IF NOT EXISTS tag(
 	id       INTEGER NOT NULL,
+	space    INTEGER NOT NULL REFERENCES tag_space(id),
 	name     TEXT NOT NULL,
 	PRIMARY KEY (id)
 ) STRICT;
 
-CREATE UNIQUE INDEX IF NOT EXISTS tag_space_name_idx ON tag_space(name);
+CREATE UNIQUE INDEX IF NOT EXISTS tag__space_name ON tag(space, name);
 
-CREATE TABLE IF NOT EXISTS tag(
+CREATE TABLE IF NOT EXISTS tag_assignment(
 	sha1     TEXT NOT NULL REFERENCES image(sha1),
-	-- FIXME: I want a nullable tag space, but that can't be a primary key.
-	--  - Perhaps have an INTEGER for the PK, and use a UNIQUE INDEX.
-	--  - (tag, space) pairs could generally use a separate table,
-	--    so that the TEXT column is deduplicated (or rather compressed).
-	--    That table just needs garbage collection.
-	space    INTEGER REFERENCES tag_space(id),
-	tag      TEXT NOT NULL,
+	tag      INTEGER NOT NULL REFERENCES tag(id),
 	weight   REAL NOT NULL,     -- 0..1 normalized weight assigned to tag
-	PRIMARY KEY (sha1, space, tag)
+	PRIMARY KEY (sha1, tag)
 ) STRICT;
 
-CREATE INDEX IF NOT EXISTS tag_space_tag_idx ON tag(space, tag);
-CREATE INDEX IF NOT EXISTS tag_tag_idx ON tag(tag);
+CREATE INDEX IF NOT EXISTS tag_assignment__tag ON tag_assignment(tag);