From abdb3a4322c3e89ba1052bcff6d0b0a532e7b986 Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Fri, 8 Dec 2023 10:16:13 +0100
Subject: Implement a few functions

---
 initialize.sql | 12 +++++++-----
 1 file changed, 7 insertions(+), 5 deletions(-)

(limited to 'initialize.sql')

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)
-- 
cgit v1.2.3-70-g09d2

