Maintain directories in a hierarchy

2 files changed, 73 insertions, 14 deletions

diff --git a/initialize.sql b/initialize.sql
index cb4e3a7..0f43af5 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -1,23 +1,35 @@
 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
+	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 directory(
+	id       INTEGER NOT NULL,                  -- unique ID
+	name     TEXT NOT NULL,                     -- basename
+	parent   INTEGER REFERENCES directory(id),  -- root if NULL
+	PRIMARY KEY (id)
+) STRICT;
+
+CREATE UNIQUE INDEX IF NOT EXISTS directory_parent ON directory(parent, name);
+
 CREATE TABLE IF NOT EXISTS entry(
-	path     TEXT NOT NULL,     -- full FS directory path
-	basename TEXT NOT NULL,     -- last FS path component
+	parent   INTEGER REFERENCES directory(id),
+	name     TEXT NOT NULL,     -- last FS path component
 	mtime    INTEGER NOT NULL,  -- Unix time of last modification in seconds
 	sha1     TEXT NOT NULL REFERENCES image(sha1),
-	PRIMARY KEY (path, basename)
+	PRIMARY KEY (parent, name)
 ) STRICT;
 
-CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, path, basename);
+CREATE INDEX IF NOT EXISTS entry_sha1 ON entry(sha1, parent, name);
+
+--
 
 CREATE TABLE IF NOT EXISTS image_tag(
 	sha1     TEXT NOT NULL REFERENCES image(sha1),
diff --git a/main.go b/main.go
index 3324ae5..b6ef3e0 100644
--- a/main.go
+++ b/main.go
@@ -17,6 +17,7 @@ import (
 	"os/exec"
 	"path/filepath"
 	"regexp"
+	"strings"
 	"time"
 
 	_ "github.com/mattn/go-sqlite3"
@@ -43,7 +44,7 @@ func thumbPath(sha1 string) string {
 	return filepath.Join(gd, "thumbs", sha1[:2], sha1+".webp")
 }
 
-func dbCollect(query string) ([]string, error) {
+func dbCollectStrings(query string) ([]string, error) {
 	rows, err := db.Query(query)
 	if err != nil {
 		return nil, err
@@ -64,6 +65,44 @@ func dbCollect(query string) ([]string, error) {
 	return result, nil
 }
 
+type directoryManager struct {
+	cache map[string]int64 // Unix-style paths to directory.id
+}
+
+func (dm *directoryManager) IDForDirectoryPath(path string) (int64, error) {
+	// Relative paths could be handled differently,
+	// but right now, they're assumed to start at the root.
+	list := strings.Split(filepath.ToSlash(filepath.Clean(path)), "/")
+	if len(list) > 1 && list[0] == "" {
+		list = list[1:]
+	}
+	if len(list) == 0 {
+		return 0, nil
+	}
+
+	var parent sql.NullInt64
+	for _, name := range list {
+		if err := db.QueryRow(
+			`SELECT id FROM directory WHERE name = ? AND parent IS ?`,
+			name, parent).Scan(&parent); err == nil {
+			continue
+		} else if !errors.Is(err, sql.ErrNoRows) {
+			return 0, err
+		}
+
+		if result, err := db.Exec(
+			`INSERT INTO directory(name, parent) VALUES (?, ?)`,
+			name, parent); err != nil {
+			return 0, err
+		} else if id, err := result.LastInsertId(); err != nil {
+			return 0, err
+		} else {
+			parent = sql.NullInt64{Int64: id, Valid: true}
+		}
+	}
+	return parent.Int64, nil
+}
+
 // cmdInit initializes a "gallery directory" that contains gallery.sqlite,
 // images, thumbs.
 func cmdInit(args []string) error {
@@ -244,10 +283,17 @@ func importFunc(path string, d fs.DirEntry, err error) error {
 		return err
 	}
 
+	// TODO: Maintain the cache across calls.
+	dm := directoryManager{}
 	dbDirname, dbBasename := filepath.Split(path)
+	dbParent, err := dm.IDForDirectoryPath(dbDirname)
+	if err != nil {
+		return err
+	}
+
 	_, err = db.Exec(`INSERT INTO entry(
-		path, basename, mtime, sha1
-	) VALUES (?, ?, ?, ?)`, dbDirname, dbBasename, s.ModTime().Unix(), hexSHA1)
+		parent, name, mtime, sha1
+	) VALUES (?, ?, ?, ?)`, dbParent, dbBasename, s.ModTime().Unix(), hexSHA1)
 	return err
 }
 
@@ -350,7 +396,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 sha1 FROM image
+		hexSHA1, err = dbCollectStrings(`SELECT sha1 FROM image
 			WHERE thumbw IS NULL OR thumbh IS NULL`)
 		if err != nil {
 			return err
@@ -411,7 +457,8 @@ func cmdDhash(args []string) error {
 	hasher, hexSHA1 := args[1], args[2:]
 	if len(hexSHA1) == 0 {
 		var err error
-		hexSHA1, err = dbCollect(`SELECT sha1 FROM image WHERE dhash IS NULL`)
+		hexSHA1, err = dbCollectStrings(`SELECT sha1 FROM image
+			WHERE dhash IS NULL`)
 		if err != nil {
 			return err
 		}