diff options
| -rw-r--r-- | initialize.sql | 24 | ||||
| -rw-r--r-- | main.go | 19 |
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, @@ -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) |