From 5d0b36cf87ee94c690d11c0beab48f4dadc6fc52 Mon Sep 17 00:00:00 2001
From: JP Appel <jeanpierre.appel01@gmail.com>
Date: Sun, 10 Aug 2025 02:30:00 -0400
Subject: Change db schema; remove aliases, add fts5 integration

Greatly simplify db schema by removing alias functionality.
Create fts5 tables for text search over paths, headings, titles, meta,
authors, and links.
---
 pkg/data/update.go | 108 ++++++++++++++++++++---------------------------------
 1 file changed, 40 insertions(+), 68 deletions(-)

(limited to 'pkg/data/update.go')

diff --git a/pkg/data/update.go b/pkg/data/update.go
index 6aecb41..52d9d53 100644
--- a/pkg/data/update.go
+++ b/pkg/data/update.go
@@ -390,46 +390,37 @@ func (u Update) authors() error {
 		return err
 	}
 
-	tempTable, args := BatchQuery(`
-		CREATE TEMPORARY TABLE new_names AS
-		SELECT column1 AS name
-		FROM ( VALUES `,
-		"", "(?)", ",", ")",
-		len(u.Doc.Authors), u.Doc.Authors,
-	)
-	_, err := u.tx.Exec(tempTable, args...)
+	authStmt, err := u.tx.Prepare("INSERT OR IGNORE INTO Authors(name) VALUES(?)")
 	if err != nil {
 		return err
 	}
-	defer u.tx.Exec("DROP TABLE temp.new_names")
+	defer authStmt.Close()
 
-	_, err = u.tx.Exec(`
-	INSERT OR IGNORE INTO Authors(name)
-	SELECT * FROM new_names
-	`)
+	idStmt, err := u.tx.Prepare("SELECT id FROM Authors WHERE name = ?")
 	if err != nil {
 		return err
 	}
-	_, err = u.tx.Exec(`
-	INSERT OR IGNORE INTO Aliases(alias)
-	SELECT * FROM new_names
-	`)
+	defer idStmt.Close()
+
+	docAuthStmt, err := u.tx.Prepare(
+		fmt.Sprintf("INSERT INTO DocumentAuthors(docId,authorId) VALUES (%d,?)", u.Id),
+	)
 	if err != nil {
 		return err
 	}
+	defer docAuthStmt.Close()
 
-	docAuthQuery := fmt.Sprintf(`
-	INSERT INTO DocumentAuthors
-	SELECT %d, existing.id
-	FROM new_names
-	LEFT JOIN (
-		SELECT * FROM Authors
-		UNION ALL
-		SELECT * FROM Aliases
-	) AS existing ON existing.name = new_names.name
-	`, u.Id)
-	if _, err := u.tx.Exec(docAuthQuery); err != nil {
-		return err
+	var authId int64
+	for _, author := range u.Doc.Authors {
+		if _, err := authStmt.Exec(author); err != nil {
+			return err
+		}
+		if err := idStmt.QueryRow(author).Scan(&authId); err != nil {
+			return err
+		}
+		if _, err := docAuthStmt.Exec(authId); err != nil {
+			return err
+		}
 	}
 
 	return nil
@@ -442,61 +433,42 @@ func (u UpdateMany) authors() error {
 	}
 	defer deleteStmt.Close()
 
-	_, err = u.tx.Exec(`
-	CREATE TEMPORARY TABLE new_names (
-		docId INTEGER NOT NULL,
-		name TEXT NOT NULL,
-		UNIQUE(docId, name)
-	)`)
+	authStmt, err := u.tx.Prepare("INSERT OR IGNORE INTO Authors(name) VALUES(?)")
 	if err != nil {
 		return err
 	}
-	defer u.tx.Exec("DROP TABLE temp.new_names")
+	defer authStmt.Close()
 
-	insertTempTable, err := u.tx.Prepare("INSERT INTO temp.new_names VALUES (?,?)")
+	idStmt, err := u.tx.Prepare("SELECT id FROM Authors WHERE name = ?")
 	if err != nil {
 		return err
 	}
-	defer insertTempTable.Close()
+	defer idStmt.Close()
 
-	for id, doc := range u.Docs {
-		if _, err := deleteStmt.Exec(id); err != nil {
+	docAuthStmt, err := u.tx.Prepare("INSERT INTO DocumentAuthors(docId,authorId) VALUES (?,?)")
+	if err != nil {
+		return err
+	}
+	defer docAuthStmt.Close()
+
+	var authId int64
+	for docId, doc := range u.Docs {
+		if _, err := deleteStmt.Exec(docId); err != nil {
 			return err
 		}
-
 		for _, author := range doc.Authors {
-			if _, err := insertTempTable.Exec(id, author); err != nil {
+			if _, err := authStmt.Exec(author); err != nil {
+				return err
+			}
+			if err := idStmt.QueryRow(author).Scan(&authId); err != nil {
+				return err
+			}
+			if _, err := docAuthStmt.Exec(docId, authId); err != nil {
 				return err
 			}
 		}
-	}
 
-	_, err = u.tx.Exec(`
-	INSERT OR IGNORE INTO Authors(name)
-	SELECT name FROM new_names
-	`)
-	if err != nil {
-		return err
-	}
-
-	_, err = u.tx.Exec(`
-	INSERT OR IGNORE INTO Aliases(alias)
-	SELECT name FROM new_names
-	`)
-	if err != nil {
-		return err
 	}
 
-	_, err = u.tx.Exec(`
-	INSERT INTO DocumentAuthors
-	SELECT docId, existing.id
-	FROM new_names
-	LEFT JOIN (
-	SELECT * FROM Authors
-	UNION ALL
-	SELECT * FROM Aliases
-	) AS existing ON existing.name = new_names.name
-	`)
-
 	return err
 }
-- 
cgit v1.2.3