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.

1 files changed, 154 insertions, 33 deletions

diff --git a/pkg/data/db.go b/pkg/data/db.go
index bf707b4..58244ac 100644
--- a/pkg/data/db.go
+++ b/pkg/data/db.go
@@ -126,17 +126,6 @@ func createSchema(db *sql.DB, version string) error {
 	}
 
 	_, err = tx.Exec(`
-	CREATE TABLE IF NOT EXISTS Aliases(
-		authorId INT NOT NULL,
-		alias TEXT UNIQUE NOT NULL,
-		FOREIGN KEY (authorId) REFERENCES Authors(id) ON DELETE CASCADE
-	)`)
-	if err != nil {
-		tx.Rollback()
-		return err
-	}
-
-	_, err = tx.Exec(`
 	CREATE TABLE IF NOT EXISTS Tags(
 		id INTEGER PRIMARY KEY,
 		name TEXT UNIQUE NOT NULL
@@ -201,37 +190,87 @@ func createSchema(db *sql.DB, version string) error {
 		return err
 	}
 
-	_, err = tx.Exec("CREATE INDEX IF NOT EXISTS idx_aliases_alias ON Aliases(alias)")
+	_, err = tx.Exec("CREATE INDEX IF NOT EXISTS idx_links_link ON Links(link)")
 	if err != nil {
 		tx.Rollback()
 		return err
 	}
 
-	_, err = tx.Exec("CREATE INDEX IF NOT EXISTS idx_aliases_authorId ON Aliases(authorId)")
+	_, err = tx.Exec("CREATE INDEX IF NOT EXISTS idx_doctags_tagid ON DocumentTags (tagId)")
 	if err != nil {
 		tx.Rollback()
 		return err
 	}
 
-	_, err = tx.Exec("CREATE INDEX IF NOT EXISTS idx_links_link ON Links(link)")
+	_, err = tx.Exec(`
+	CREATE VIRTUAL TABLE IF NOT EXISTS Documents_fts
+	USING fts5 (
+		path, headings, title, meta, content=Documents, content_rowid=id
+	)
+	`)
 	if err != nil {
 		tx.Rollback()
 		return err
 	}
 
-	_, err = tx.Exec("CREATE INDEX IF NOT EXISTS idx_doctags_tagid ON DocumentTags (tagId)")
+	_, err = tx.Exec(`
+	CREATE VIRTUAL TABLE IF NOT EXISTS Authors_fts
+	USING fts5 (
+		name, content=Authors, content_rowid=id
+	)
+	`)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	_, err = tx.Exec(`
+	CREATE VIRTUAL TABLE IF NOT EXISTS Tags_fts
+	USING fts5 (
+		name, content=Tags, content_rowid=id
+	)
+	`)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	// FIXME: doesn't set new.id
+	_, err = tx.Exec(`
+	CREATE TRIGGER IF NOT EXISTS trig_ai_authors
+	AFTER INSERT ON Authors
+	BEGIN
+		INSERT INTO Authors_fts(rowid, name)
+		VALUES (new.id, new.name);
+	END
+	`)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	_, err = tx.Exec(`
+	CREATE TRIGGER IF NOT EXISTS trig_ad_authors
+	AFTER DELETE ON Authors
+	BEGIN
+		INSERT INTO Authors_fts(Authors_fts, rowid, name)
+		VALUES ('delete', old.id, old.name);
+	END
+	`)
 	if err != nil {
 		tx.Rollback()
 		return err
 	}
 
+	// FIXME: doesn't set new.id
 	_, err = tx.Exec(`
-	CREATE TRIGGER IF NOT EXISTS trig_new_author
-	BEFORE INSERT ON Authors
+	CREATE TRIGGER IF NOT EXISTS trig_au_authors
+	AFTER UPDATE ON Authors
 	BEGIN
-		SELECT CASE WHEN NEW.name IN (SELECT alias FROM Aliases) THEN
-			RAISE(IGNORE)
-		END;
+		INSERT INTO Authors_fts(Authors_fts, rowid, name)
+		VALUES ('delete', old.id, old.name);
+		INSERT INTO Authors_fts(rowid, name)
+		VALUES (new.id, new.name);
 	END
 	`)
 	if err != nil {
@@ -239,13 +278,13 @@ func createSchema(db *sql.DB, version string) error {
 		return err
 	}
 
+	// FIXME: doesn't set new.id
 	_, err = tx.Exec(`
-	CREATE TRIGGER IF NOT EXISTS trig_new_alias
-	BEFORE INSERT ON Aliases
+	CREATE TRIGGER IF NOT EXISTS trig_ai_tags
+	AFTER INSERT ON Tags
 	BEGIN
-		SELECT CASE WHEN NEW.alias IN (SELECT name FROM Authors) THEN
-			RAISE(IGNORE)
-		END;
+		INSERT INTO Tags_fts(rowid, name)
+		VALUES (new.id, new.name);
 	END
 	`)
 	if err != nil {
@@ -254,22 +293,94 @@ func createSchema(db *sql.DB, version string) error {
 	}
 
 	_, err = tx.Exec(`
+	CREATE TRIGGER IF NOT EXISTS trig_ad_tags
+	AFTER DELETE ON Tags
+	BEGIN
+		INSERT INTO Tags_fts(Tags_fts, rowid, name)
+		VALUES ('delete', old.id, old.name);
+	END
+	`)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	// FIXME: doesn't set new.id
+	_, err = tx.Exec(`
+	CREATE TRIGGER IF NOT EXISTS trig_au_tags
+	AFTER UPDATE ON Tags
+	BEGIN
+		INSERT INTO Tags_fts(Tags_fts, rowid, name)
+		VALUES ('delete', old.id, old.name);
+		INSERT INTO Tags_fts(rowid, name)
+		VALUES (new.id, new.name);
+	END
+	`)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	// FIXME: doesn't set new.id
+	_, err = tx.Exec(`
+	CREATE TRIGGER IF NOT EXISTS trig_ai_doc
+	AFTER INSERT ON Documents
+	BEGIN
+		INSERT INTO Documents_fts(rowid, path, headings, title, meta)
+		VALUES (new.id, new.path, new.headings, new.title, new.meta);
+	END
+	`)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	_, err = tx.Exec(`
+	CREATE TRIGGER IF NOT EXISTS trig_ad_doc
+	AFTER DELETE ON Documents
+	BEGIN
+		INSERT INTO Documents_fts(Documents_fts, rowid, path, headings, title, meta)
+		VALUES ('delete', old.id, old.path, old.headings, old.title, old.meta);
+	END
+	`)
+	if err != nil {
+		tx.Rollback()
+		return err
+	}
+
+	// FIXME: doesn't set new.id
+	_, err = tx.Exec(`
+	CREATE TRIGGER IF NOT EXISTS trig_au_doc
+	AFTER UPDATE ON Documents
+	BEGIN
+		INSERT INTO Documents_fts(Documents_fts, rowid, path, headings, title, meta)
+		VALUES ('delete', old.id, old.path, old.headings, old.title, old.meta);
+		INSERT INTO Documents_fts(rowid, path, headings, title, meta)
+		VALUES (new.id, new.path, new.headings, new.title, new.meta);
+	END
+	`)
+	if err != nil {
+		return err
+	}
+
+	// TODO: update to use fts tables
+	_, err = tx.Exec(`
 	CREATE VIEW IF NOT EXISTS Search AS
 	SELECT
 		d.id AS docId,
-		d.path,
-		d.title,
+		d_fts.path,
+		d_fts.title,
 		d.date,
 		d.fileTime,
-		d.headings,
-		d.meta,
-		COALESCE(a.name, al.alias) AS author,
+		d_fts.headings,
+		d_fts.meta,
+		a_fts.name AS author,
 		t.name AS tag,
 		l.link
 	FROM Documents d
+	JOIN Documents_fts as d_fts ON d.id = d_fts.rowid
 	LEFT JOIN DocumentAuthors da ON d.id = da.docId
-	LEFT JOIN Authors a ON da.authorId = a.id
-	LEFT JOIN Aliases al ON a.id = al.authorId
+	LEFT JOIN Authors_fts a_fts ON da.authorId = a_fts.rowid
 	LEFT JOIN DocumentTags dt ON d.id = dt.docId
 	LEFT JOIN Tags t ON dt.tagId = t.id
 	LEFT JOIN Links l ON d.id = l.docId
@@ -339,7 +450,7 @@ func (q Query) GetDocument(ctx context.Context, path string) (*index.Document, e
 	return f.Get(ctx)
 }
 
-// Shrink database by removing unused authors, aliases, tags and VACUUM-ing
+// Shrink database by removing unused authors and tags and VACUUM-ing
 func (q Query) Tidy() error {
 	if _, err := q.db.Exec(`
 	DELETE FROM Authors
@@ -362,6 +473,16 @@ func (q Query) Tidy() error {
 		return err
 	}
 
+	if _, err := q.db.Exec("INSERT INTO Documents_fts(Documents_fts) VALUES('optimize')"); err != nil {
+		return err
+	}
+	if _, err := q.db.Exec("INSERT INTO Authors_fts(Authors_fts) VALUES('optimize')"); err != nil {
+		return err
+	}
+	if _, err := q.db.Exec("INSERT INTO Tags_fts(Tags_fts) VALUES('optimize')"); err != nil {
+		return err
+	}
+
 	return nil
 }