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, 0 insertions, 70 deletions

diff --git a/pkg/index/schema.sql b/pkg/index/schema.sql
deleted file mode 100644
index fb06351..0000000
--- a/pkg/index/schema.sql
+++ /dev/null
@@ -1,70 +0,0 @@
--- TABLE of config values
-CREATE TABLE Indexes(
-    root TEXT NOT NULL,
-    followSym DATE
-);
-
--- Schema
-CREATE TABLE Documents(
-    id INTEGER PRIMARY KEY,
-    path TEXT UNIQUE NOT NULL,
-    title TEXT,
-    date INT,
-    fileTime INT,
-    meta BLOB
-);
-
-CREATE TABLE Authors(
-    id INTEGER PRIMARY KEY,
-    name TEXT UNIQUE NOT NULL
-);
-
-CREATE TABLE Aliases(
-    authorId INT NOT NULL,
-    alias TEXT UNIQUE NOT NULL,
-    FOREIGN KEY (authorId) REFERENCES Authors(id)
-);
-
-CREATE TABLE Tags(
-    id INTEGER PRIMARY KEY,
-    name TEXT UNIQUE NOT NULL,
-);
-
-CREATE TABLE Links(
-    referencedId INT,
-    refererId INT,
-    FOREIGN KEY (referencedId) REFERENCES Documents(id),
-    FOREIGN KEY (refererId) REFERENCES Documents(id)
-);
-
-CREATE TABLE DocumentAuthors(
-    docId INT NOT NULL,
-    authorId INT NOT NULL,
-    FOREIGN KEY (docId) REFERENCES Documents(id),
-    FOREIGN KEY (authorId) REFERENCES Authors(id)
-);
-
-CREATE TABLE DocumentTags(
-    docId INT NOT NULL,
-    tagId INT NOT NULL,
-    FOREIGN KEY (docId) REFERENCES Documents(id),
-    FOREIGN KEY (tagId) REFERENCES Tags(id),
-    UNIQUE(docId, tagId)
-);
-
--- Indexes
-CREATE INDEX idx_doc_dates
-ON Documents (date);
-CREATE INDEX idx_doc_titles
-ON Documents (title);
-
-CREATE INDEX idx_author_name
-ON Authors(name);
-
-CREATE INDEX idx_aliases_alias
-ON Aliases(alias);
-CREATE INDEX idx_aliases_authorId
-ON Aliases(authorId);
-
-CREATE INDEX idx_doctags_tagid
-ON DocumentTags (tagId);