From cd62d6a86e29be1d0643ba0efd70f23ba693344b Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Sat, 9 Dec 2023 04:29:12 +0100
Subject: Fix database design

---
 main.go | 19 +++++++++++--------
 1 file changed, 11 insertions(+), 8 deletions(-)

(limited to 'main.go')

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