Fix database design

2 files changed, 22 insertions, 21 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,
diff --git a/main.go b/main.go
index b57042e..2a46b21 100644
--- a/main.go
+++ b/main.go
@@ -193,12 +193,16 @@ func importFunc(path string, d fs.DirEntry, err error) error {
 		return err
 	}
 
-	// TODO: This should run in a transaction.
+	// TODO: This should all run in a transaction.
+	if _, err = db.Exec(`INSERT INTO image(sha1) VALUES (?)
+		ON CONFLICT(sha1) DO NOTHING`, hexSHA1); err != nil {
+		return err
+	}
+
 	dbDirname, dbBasename := filepath.Split(path)
 	_, err = db.Exec(`INSERT INTO entry(
 		path, basename, mtime, sha1
 	) VALUES (?, ?, ?, ?)`, dbDirname, dbBasename, s.ModTime().Unix(), hexSHA1)
-	// TODO: Also ensure that a row exists in "image".
 	return err
 }
 
@@ -291,8 +295,7 @@ func cmdThumbnail(args []string) error {
 	if len(hexSHA1) == 0 {
 		// Get all unique images in the database with no thumbnail.
 		var err error
-		hexSHA1, err = dbCollect(`SELECT DISTINCT entry.sha1 FROM entry
-			LEFT OUTER JOIN image ON entry.sha1 = image.sha1
+		hexSHA1, err = dbCollect(`SELECT sha1 FROM image
 			WHERE thumbw IS NULL OR thumbh IS NULL`)
 		if err != nil {
 			return err
@@ -309,9 +312,8 @@ func cmdThumbnail(args []string) error {
 			return err
 		}
 
-		_, err = db.Exec(`INSERT INTO image(
-			sha1, thumbw, thumbh, dhash
-		) VALUES (?, ?, ?, NULL)`, sha1, w, h)
+		_, err = db.Exec(`UPDATE image SET thumbw = ?, thumbh = ?
+			WHERE sha1 = ?`, w, h, sha1)
 		if err != nil {
 			return err
 		}
@@ -337,7 +339,7 @@ func makeDhash(hasher, pathThumb string) (uint64, error) {
 	}
 
 	var hash uint64
-	_, err = fmt.Fscanf(bytes.NewReader(out), "%d", &hash)
+	_, err = fmt.Fscanf(bytes.NewReader(out), "%x", &hash)
 	return hash, err
 }
 
@@ -360,6 +362,7 @@ func cmdDhash(args []string) error {
 	}
 
 	// TODO: Try to run the hasher in parallel, somehow.
+	// TODO: Show progress in some manner. Perhaps port my propeller code.
 	for _, sha1 := range hexSHA1 {
 		pathThumb := thumbPath(sha1)
 		hash, err := makeDhash(hasher, pathThumb)