glorious new db

1 files changed, 54 insertions, 0 deletions

diff --git a/src/lib/db/senseschema.sql b/src/lib/db/senseschema.sql
new file mode 100644
index 0000000..f07a208
--- /dev/null
+++ b/src/lib/db/senseschema.sql
@@ -0,0 +1,54 @@
+-- Enable foreign key support
+PRAGMA foreign_keys = ON;
+PRAGMA journal_mode = WAL;
+PRAGMA cache_size = -2000;
+PRAGMA mmap_size = 30000000000;
+
+-- a semantic entity
+CREATE TABLE IF NOT EXISTS senses(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    parent_id INTEGER NOT NULL,
+    spelling TEXT NOT NULL,
+    pos TEXT,
+    etymology TEXT,
+    senses JSONB,
+    forms JSONB,
+    related JSONB,
+    confidence INTEGER NOT NULL DEFAULT 0,
+    FOREIGN KEY (parent_id) REFERENCES expressions(id)
+);
+CREATE INDEX IF NOT EXISTS idx_words_pos ON senses(pos);
+CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);
+
+CREATE TABLE IF NOT EXISTS subsenses(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    sid INTEGER NOT NULL
+    gloss TEXT NOT NULL,
+    examples JSONB,    
+    FOREIGN KEY (sid) REFERENCES senses(id)
+);
+
+CREATE TABLE IF NOT EXISTS derivation(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    sid INTEGER NOT NULL
+    type TEXT NOT NULL,
+    text TEXT NOT NULL,
+    tags JSONB,    
+    FOREIGN KEY (sid) REFERENCES senses(id)
+);
+
+-- Categories table (for noun and verb categories)
+CREATE TABLE IF NOT EXISTS categories (
+    name TEXT PRIMARY KEY
+);
+
+-- Word Categories junction table
+CREATE TABLE IF NOT EXISTS word_categories (
+    word_id INTEGER NOT NULL,
+    category INTEGER NOT NULL,
+    PRIMARY KEY (word_id, category),
+    FOREIGN KEY (word_id) REFERENCES expressions(id),
+    FOREIGN KEY (category) REFERENCES categories(name)
+);
+CREATE INDEX IF NOT EXISTS idx_word_categories_category_id ON word_categories(category);
+