Implement a few functions

1 files changed, 7 insertions, 5 deletions

diff --git a/initialize.sql b/initialize.sql
index 07d2969..746b0e2 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -1,3 +1,4 @@
+-- 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
@@ -8,24 +9,25 @@ CREATE TABLE IF NOT EXISTS entry(
 
 CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename);
 
+-- XXX: Shouldn't perhaps "entry.sha1" reference "image.sha1"?
 CREATE TABLE IF NOT EXISTS image(
 	sha1     TEXT NOT NULL REFERENCES entry(sha1),
-	thumbw   INTEGER,
-	thumbh   INTEGER,
-	dhash    INTEGER,           -- uint64 as a signed integer
+	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)
 );
 
 CREATE INDEX IF NOT EXISTS image_dhash ON image(dhash, sha1);
 
 CREATE TABLE IF NOT EXISTS image_tag(
-	sha1     TEXT NOT NULL REFERENCES entry(sha1),
+	sha1     TEXT NOT NULL REFERENCES image(sha1),
 	tag      TEXT NOT NULL,
 	PRIMARY KEY (sha1)
 );
 
 CREATE TABLE IF NOT EXISTS image_autotag(
-	sha1     TEXT NOT NULL REFERENCES entry(sha1),
+	sha1     TEXT NOT NULL REFERENCES image(sha1),
 	tag      TEXT NOT NULL,
 	weight   REAL NOT NULL,     -- 0..1 normalized weight assigned to tag
 	PRIMARY KEY (sha1, tag)