diff options
Diffstat (limited to 'packages/db/src/schema.sql')
| -rw-r--r-- | packages/db/src/schema.sql | 97 |
1 files changed, 5 insertions, 92 deletions
diff --git a/packages/db/src/schema.sql b/packages/db/src/schema.sql index c4a7c76..85d0d77 100644 --- a/packages/db/src/schema.sql +++ b/packages/db/src/schema.sql @@ -1,82 +1,11 @@ -/** - * UNIFIED DATABASE SCHEMA FOR THAI LANGUAGE LEARNING APPLICATION - * - * This file consolidates 3 redundant database schemas: - * - schema.sql (main schema with courses and SRS) - * - prosodyschema.sql (phonetic analysis with syllable breakdown) - * - senseschema.sql (semantic analysis with subsenses and derivations) - * - * REDUNDANCY ANALYSIS: - * ==================== - * - ** MAJOR CONFLICTS RESOLVED: - * 1. Languages table: - * - schema.sql: uses 'code' as PRIMARY KEY - * - prosodyschema.sql: uses 'iso6392' as PRIMARY KEY - * - RESOLVED: Unified with both code systems supported - * - * 2. Senses table: - * - schema.sql: includes ipa/prosody JSONB fields - * - senseschema.sql: missing phonetic fields - * - RESOLVED: Enhanced version with all fields - * - * 3. Word/Expression entities: - * - schema.sql: uses 'expressions' table - * - prosodyschema.sql: uses 'words' table - * - RESOLVED: Standardized on 'expressions' terminology - * - * 4. Categories tables: - * - EXACT DUPLICATES in schema.sql and senseschema.sql - * - RESOLVED: Keep one instance, remove duplicate - * - * UNIQUE FEATURES PRESERVED: - * ======================== - * - * FROM schema.sql (Main Course Learning): - * - User management: users, cookies - * - Course structure: lessons, cards, cards_lessons, cards_expressions - * - SRS tracking: user_progress, attempts - * - Bookmarks and user features - * - * FROM prosodyschema.sql (Phonetic Analysis): - * - Detailed syllable breakdown: tones, onsets, medials, nucleus, codas, rhymes - * - Phonetic patterns: word_phonetics, syllables_words - * - Rhyme analysis: word_rhymes, words_wrhymes - * - * FROM senseschema.sql (Semantic Analysis): - * - Detailed definitions: subsenses - * - Etymology tracking: derivation - * - * FOREIGN KEY RELATIONSHIPS: - * ========================= - * - Updated all references from 'words.id' to 'expressions.id' - * - Unified language references to use languages.code - * - Maintained proper cascade relationships - * - * BENEFITS OF UNIFICATION: - * ======================= - * 1. Single source of truth for database structure - * 2. Eliminated conflicting table definitions - * 3. Preserved all unique functionality - * 4. Improved foreign key consistency - * 5. Better support for comprehensive Thai language analysis - */ --- Enable foreign key support and performance optimizations PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA cache_size = -2000; PRAGMA mmap_size = 30000000000; +PRAGMA busy_timeout = 5000; + -/** - * UNIFIED LANGUAGES TABLE - * - * RESOLVES CONFLICT between: - * - schema.sql: code (PRIMARY KEY), name, native_name - * - prosodyschema.sql: iso6392 (PRIMARY KEY), english - * - * NOW SUPPORTS multiple language code systems for maximum compatibility - */ CREATE TABLE IF NOT EXISTS languages ( code TEXT PRIMARY KEY, -- Primary language code (ISO 639-1 preferred) name TEXT NOT NULL, -- English name @@ -85,14 +14,7 @@ CREATE TABLE IF NOT EXISTS languages ( CONSTRAINT name_unique UNIQUE (name) ); -/** - * CORE CONTENT TABLES - * Standardized on 'expressions' terminology from schema.sql - * Enhanced with fields from both schemas - */ --- Main expressions table (formerly 'words' in prosodyschema.sql) --- UNIFIED from schema.sql expressions and prosodyschema.sql words CREATE TABLE IF NOT EXISTS expressions ( id INTEGER PRIMARY KEY AUTOINCREMENT, spelling TEXT NOT NULL, @@ -104,8 +26,6 @@ CREATE TABLE IF NOT EXISTS expressions ( CONSTRAINT spell_unique UNIQUE (spelling, lang) ); --- Enhanced senses table with phonetic capabilities --- MERGED from schema.sql senses (with ipa/prosody) and senseschema.sql senses CREATE TABLE IF NOT EXISTS senses ( id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER NOT NULL, @@ -153,14 +73,6 @@ CREATE TABLE IF NOT EXISTS derivation ( FOREIGN KEY (sid) REFERENCES senses(id) ON DELETE CASCADE ); -/** - * PHONETIC ANALYSIS TABLES (FROM prosodyschema.sql) - * - * Comprehensive syllable breakdown system for Thai language analysis - * These tables provide detailed phonetic analysis beyond basic JSONB fields - */ - - -- Syllable component tables CREATE TABLE IF NOT EXISTS tones ( id INTEGER PRIMARY KEY AUTOINCREMENT, @@ -252,10 +164,11 @@ CREATE TABLE IF NOT EXISTS word_phonetics ( syllable_sequence TEXT NOT NULL, -- Comma-separated syllables stressed INTEGER, -- index of stressed syllable tone_sequence TEXT, -- Comma-separated tones - tag JSONB, -- Pattern/usage tag + tags TEXT, -- Usually dialect, array from wikisource, we join it with "/-/" notes TEXT, FOREIGN KEY (word_id) REFERENCES expressions(id), - FOREIGN KEY (sense_id) REFERENCES senses(id) + FOREIGN KEY (sense_id) REFERENCES senses(id), + CONSTRAINT phonetics_unique UNIQUE (ipa, tags) ); -- Rhyme analysis tables (UNIQUE to prosodyschema.sql) |