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