From 34b8d8ff1f9d65c08a9156d72f08cf548183c6f4 Mon Sep 17 00:00:00 2001
From: JP Appel <jeanpierre.appel01@gmail.com>
Date: Sun, 27 Apr 2025 00:49:27 -0400
Subject: Large commit; many features

---
 pkg/index/schema.sql | 70 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 70 insertions(+)
 create mode 100644 pkg/index/schema.sql

(limited to 'pkg/index/schema.sql')

diff --git a/pkg/index/schema.sql b/pkg/index/schema.sql
new file mode 100644
index 0000000..fb06351
--- /dev/null
+++ b/pkg/index/schema.sql
@@ -0,0 +1,70 @@
+-- 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

