1 files changed, 178 insertions, 0 deletions
diff --git a/src/lib/db/prosodyschema.sql b/src/lib/db/prosodyschema.sql
new file mode 100644
index 0000000..e70b005
--- /dev/null
+++ b/src/lib/db/prosodyschema.sql
@@ -0,0 +1,178 @@
+-- Enable foreign key support
+PRAGMA foreign_keys = ON;
+PRAGMA journal_mode = WAL;
+PRAGMA cache_size = -2000;
+PRAGMA mmap_size = 30000000000;
+
+
+-- proper prosody now
+--
+-- 
+--
+CREATE TABLE IF NOT EXISTS languages(
+    iso6392 TEXT PRIMARY KEY,
+    -- bcp47 TEXT PRIMARY KEY,
+    -- iso6393 TEXT NOT NULL,
+    english TEXT NOT NULL
+    -- native TEXT,
+    -- iso6391 TEXT,
+    -- iso6395 TEXT,
+    -- glottolog TEXT
+);
+
+CREATE TABLE IF NOT EXISTS idioms(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    spelling TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    frequency INTEGER,
+    FOREIGN KEY (lang) REFERENCES languages(iso6392),
+    CONSTRAINT spell_unique UNIQUE (spelling, lang)
+);
+
+CREATE INDEX IF NOT EXISTS idx_idioms_spelling ON idioms(spelling);
+CREATE TABLE IF NOT EXISTS words(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    spelling TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    frequency INTEGER,
+    FOREIGN KEY (lang) REFERENCES languages(iso6392),
+    CONSTRAINT spell_unique UNIQUE (spelling, lang)
+);
+
+CREATE INDEX IF NOT EXISTS idx_words_spelling ON words(spelling);
+
+CREATE TABLE IF NOT EXISTS word_rhymes(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    notes TEXT,
+    CONSTRAINT wrhyme_unique UNIQUE (text, lang)
+);
+CREATE TABLE IF NOT EXISTS words_rhymes(
+    word_id INTEGER NOT NULL,
+    wrhyme_id INTEGER NOT NULL,
+    FOREIGN KEY (word_id) REFERENCES words(id),
+    FOREIGN KEY (wrhyme_id) REFERENCES word_rhymes(id)
+);
+
+-- break up syllables
+CREATE TABLE IF NOT EXISTS syllables(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    long INTEGER NOT NULL,
+    tone TEXT,
+    onset TEXT,
+    medial TEXT,
+    nucleus TEXT,
+    coda TEXT,
+    rhyme TEXT,
+    notes TEXT,
+    FOREIGN KEY (lang) REFERENCES languages(iso6392),
+    CONSTRAINT spell_unique UNIQUE (text, lang)
+);
+
+CREATE TABLE IF NOT EXISTS tones(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    name TEXT,
+    num INTEGER,
+    CONSTRAINT tone_unique UNIQUE (text, lang)
+);
+CREATE TABLE IF NOT EXISTS onsets(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT onsets_unique UNIQUE (text, lang)
+);
+CREATE TABLE IF NOT EXISTS medials(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT medials_unique UNIQUE (text, lang)
+);
+CREATE TABLE IF NOT EXISTS nucleus(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT nucleus_unique UNIQUE (text, lang)
+);
+CREATE TABLE IF NOT EXISTS codas(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT coda_unique UNIQUE (text, lang)
+);
+CREATE TABLE IF NOT EXISTS rhymes(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    text TEXT NOT NULL,
+    lang TEXT NOT NULL,
+    CONSTRAINT rhyme_unique UNIQUE (text, lang)
+);
+
+-- join tables
+
+CREATE TABLE IF NOT EXISTS tones_syllables(
+    syl_id INTEGER NOT NULL,
+    tone_id INTEGER NOT NULL,
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (tone_id) REFERENCES tones(id)
+);
+CREATE TABLE IF NOT EXISTS onsets_syllables(
+    syl_id INTEGER NOT NULL,
+    onset_id INTEGER NOT NULL,
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (onset_id) REFERENCES onsets(id)
+);
+CREATE TABLE IF NOT EXISTS medials_syllables(
+    syl_id INTEGER NOT NULL,
+    medial_id INTEGER NOT NULL,
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (medial_id) REFERENCES medials(id)
+);
+CREATE TABLE IF NOT EXISTS nucleus_syllables(
+    syl_id INTEGER NOT NULL,
+    nucleus_id INTEGER NOT NULL,
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (nucleus_id) REFERENCES nucleus(id)
+);
+CREATE TABLE IF NOT EXISTS codas_syllables(
+    syl_id INTEGER NOT NULL,
+    coda_id INTEGER NOT NULL,
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (coda_id) REFERENCES codas(id)
+);
+CREATE TABLE IF NOT EXISTS rhymes_syllables(
+    syl_id INTEGER NOT NULL,
+    rhyme_id INTEGER NOT NULL,
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (rhyme_id) REFERENCES rhymes(id)
+);
+
+CREATE TABLE IF NOT EXISTS syllables_words(
+    syl_id INTEGER NOT NULL,
+    word_id INTEGER NOT NULL,
+    FOREIGN KEY (syl_id) REFERENCES syllables(id),
+    FOREIGN KEY (word_id) REFERENCES words(id)
+);
+CREATE TABLE IF NOT EXISTS words_idioms(
+    word_id INTEGER NOT NULL,
+    idiom_id INTEGER NOT NULL,
+    FOREIGN KEY (idiom_id) REFERENCES idioms(id),
+    FOREIGN KEY (word_id) REFERENCES words(id)
+);
+
+
+-- 
+CREATE TABLE IF NOT EXISTS pronunciation(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    type TEXT CHECK(type IN ('word', 'syllable', 'idiom')) NOT NULL,
+    parent_id INTEGER NOT NULL,
+    ipa TEXT NOT NULL,
+    syllables INTEGER NOT NULL,
+    tag TEXT,
+    notes TEXT,
+    CONSTRAINT ipa_unique UNIQUE (ipa, parent_id)
+);
+CREATE INDEX IF NOT EXISTS idx_words_ipa ON pronunciation(ipa, parent_id);