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