Large commit; many features

1 files changed, 70 insertions, 0 deletions

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);