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/index/schema.sql | 70 ----------------------------------------------------
 1 file changed, 70 deletions(-)
 delete mode 100644 pkg/index/schema.sql

(limited to 'pkg/index')

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);
-- 
cgit v1.2.3