From ba2dbc660c229d3e86662d35513dfa7c904d9870 Mon Sep 17 00:00:00 2001
From: polwex <polwex@sortug.com>
Date: Sun, 23 Nov 2025 13:29:28 +0700
Subject: wew

---
 packages/db/src/schema.sql | 97 +++-------------------------------------------
 1 file changed, 5 insertions(+), 92 deletions(-)

(limited to 'packages/db/src/schema.sql')

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

