From 39e7cce65fdb49f624bc3dccd4ef279db7f7b389 Mon Sep 17 00:00:00 2001
From: Přemysl Eric Janouch <p@janouch.name>
Date: Tue, 12 Dec 2023 05:57:04 +0100
Subject: Add a tag import command
---
Makefile | 2 +-
initialize.sql | 4 +--
main.go | 84 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3 files changed, 85 insertions(+), 5 deletions(-)
diff --git a/Makefile b/Makefile
index 97cc5ef..77d7875 100644
--- a/Makefile
+++ b/Makefile
@@ -5,7 +5,7 @@ outputs = gallery initialize.go public/mithril.js
all: $(outputs)
gallery: main.go initialize.go
- go build -gcflags="all=-N -l" -o $@
+ go build -tags "fts5" -gcflags="all=-N -l" -o $@
initialize.go: initialize.sql gen-initialize.sh
./gen-initialize.sh initialize.sql > $@
public/mithril.js:
diff --git a/initialize.sql b/initialize.sql
index 706580d..36af2aa 100644
--- a/initialize.sql
+++ b/initialize.sql
@@ -10,7 +10,7 @@ CREATE INDEX IF NOT EXISTS image__dhash ON image(dhash);
--
--- NOTE: This table requires garbage collection.
+-- NOTE: This table requires garbage collection. Perhaps as a trigger.
CREATE TABLE IF NOT EXISTS directory(
id INTEGER NOT NULL, -- unique ID
name TEXT NOT NULL, -- basename
@@ -50,7 +50,7 @@ INSERT INTO tag_space(id, name, description)
VALUES(0, '', 'User-defined tags')
ON CONFLICT DO NOTHING;
--- NOTE: This table requires garbage collection.
+-- NOTE: This table requires garbage collection. Perhaps as a trigger.
CREATE TABLE IF NOT EXISTS tag(
id INTEGER NOT NULL,
space INTEGER NOT NULL REFERENCES tag_space(id),
diff --git a/main.go b/main.go
index 598c5c5..61f089a 100644
--- a/main.go
+++ b/main.go
@@ -1,6 +1,7 @@
package main
import (
+ "bufio"
"bytes"
"context"
"crypto/sha1"
@@ -38,8 +39,8 @@ var (
func openDB(directory string) error {
var err error
- db, err = sql.Open("sqlite3",
- "file:"+filepath.Join(directory, "gallery.db?_foreign_keys=1"))
+ db, err = sql.Open("sqlite3", "file:"+filepath.Join(directory,
+ "gallery.db?_foreign_keys=1&_busy_timeout=1000"))
galleryDirectory = directory
return err
}
@@ -279,6 +280,7 @@ func cmdRun(args []string) error {
// but having an elementary level of security doesn't hurt either.
staticHandler = http.FileServer(http.Dir("public"))
+ // TODO: Make sure the database handle isn't used concurrently.
http.HandleFunc("/", handleRequest)
http.HandleFunc("/image/", handleImages)
http.HandleFunc("/thumb/", handleThumbs)
@@ -534,6 +536,83 @@ func cmdSync(args []string) error {
return nil
}
+// --- Tagging -----------------------------------------------------------------
+
+// cmdTag mass imports tags from data passed on stdin as a TSV
+// of SHA1 TAG WEIGHT entries.
+func cmdTag(args []string) error {
+ if len(args) < 2 || len(args) > 3 {
+ return errors.New("usage: GD SPACE [DESCRIPTION]")
+ }
+ if err := openDB(args[0]); err != nil {
+ return err
+ }
+
+ space := args[1]
+
+ var description sql.NullString
+ if len(args) >= 3 {
+ description = sql.NullString{String: args[2], Valid: true}
+ }
+
+ // Note that starting as a write transaction prevents deadlocks.
+ // Imports are rare, and just bulk load data, so this scope is fine.
+ tx, err := db.Begin()
+ if err != nil {
+ return err
+ }
+ defer tx.Rollback()
+
+ if _, err := tx.Exec(`INSERT OR IGNORE INTO tag_space(name, description)
+ VALUES (?, ?)`, space, description); err != nil {
+ return err
+ }
+
+ var spaceID int64
+ if err := tx.QueryRow(`SELECT id FROM tag_space WHERE name = ?`,
+ space).Scan(&spaceID); err != nil {
+ return err
+ }
+
+ // TODO: Prepare statements for tag/assignment updates.
+
+ scanner := bufio.NewScanner(os.Stdin)
+ for scanner.Scan() {
+ fields := strings.Split(scanner.Text(), "\t")
+ if len(fields) != 3 {
+ return errors.New("invalid input format")
+ }
+
+ sha1, tag := fields[0], fields[1]
+ weight, err := strconv.ParseFloat(fields[2], 64)
+ if err != nil {
+ return err
+ }
+
+ if _, err := tx.Exec(`INSERT OR IGNORE INTO tag(space, name)
+ VALUES (?, ?);`, spaceID, tag); err != nil {
+ return nil
+ }
+
+ var tagID int64
+ if err := tx.QueryRow(`SELECT id FROM tag WHERE space = ? AND name = ?`,
+ spaceID, tag).Scan(&tagID); err != nil {
+ return err
+ }
+
+ if _, err := tx.Exec(`INSERT INTO tag_assignment(sha1, tag, weight)
+ VALUES (?, ?, ?) ON CONFLICT DO UPDATE SET weight = ?`,
+ sha1, tagID, weight, weight); err != nil {
+ return err
+ }
+ }
+ if err := scanner.Err(); err != nil {
+ return err
+ }
+
+ return tx.Commit()
+}
+
// --- Check -------------------------------------------------------------------
// cmdCheck checks if all files tracked in the DB are accessible.
@@ -735,6 +814,7 @@ var commands = map[string]struct {
"init": {cmdInit},
"run": {cmdRun},
"import": {cmdImport},
+ "tag": {cmdTag},
"sync": {cmdSync},
"check": {cmdCheck},
"thumbnail": {cmdThumbnail},
--
cgit v1.2.3-70-g09d2