Fix database design

1 files changed, 11 insertions, 13 deletions

diff --git a/initialize.sql b/initialize.sql
index 6d5a9ad..cb4e3a7 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -1,26 +1,24 @@
+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
+	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 entry(
 	path     TEXT NOT NULL,     -- full FS directory path
 	basename TEXT NOT NULL,     -- last FS path component
 	mtime    INTEGER NOT NULL,  -- Unix time of last modification in seconds
-	sha1     TEXT NOT NULL,     -- SHA-1 hash of file in lowercase hexadecimal
+	sha1     TEXT NOT NULL REFERENCES image(sha1),
 	PRIMARY KEY (path, basename)
 ) STRICT;
 
 CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename);
 
--- XXX: Shouldn't perhaps "entry.sha1" reference "image.sha1"?
--- FIXME
-CREATE TABLE IF NOT EXISTS image(
-	sha1     TEXT NOT NULL REFERENCES entry(sha1, path, basename),
-	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);
-
 CREATE TABLE IF NOT EXISTS image_tag(
 	sha1     TEXT NOT NULL REFERENCES image(sha1),
 	tag      TEXT NOT NULL,