all me here should merge

1 files changed, 44 insertions, 23 deletions

diff --git a/src/lib/db/prosodyschema.sql b/src/lib/db/prosodyschema.sql
index e70b005..09dabc2 100644
--- a/src/lib/db/prosodyschema.sql
+++ b/src/lib/db/prosodyschema.sql
@@ -35,6 +35,7 @@ CREATE TABLE IF NOT EXISTS words(
     spelling TEXT NOT NULL,
     lang TEXT NOT NULL,
     frequency INTEGER,
+    notes TEXT,
     FOREIGN KEY (lang) REFERENCES languages(iso6392),
     CONSTRAINT spell_unique UNIQUE (spelling, lang)
 );
@@ -48,7 +49,7 @@ CREATE TABLE IF NOT EXISTS word_rhymes(
     notes TEXT,
     CONSTRAINT wrhyme_unique UNIQUE (text, lang)
 );
-CREATE TABLE IF NOT EXISTS words_rhymes(
+CREATE TABLE IF NOT EXISTS words_wrhymes(
     word_id INTEGER NOT NULL,
     wrhyme_id INTEGER NOT NULL,
     FOREIGN KEY (word_id) REFERENCES words(id),
@@ -58,57 +59,62 @@ CREATE TABLE IF NOT EXISTS words_rhymes(
 -- break up syllables
 CREATE TABLE IF NOT EXISTS syllables(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
-    text TEXT NOT NULL,
     lang TEXT NOT NULL,
+    ipa TEXT NOT NULL,
     long INTEGER NOT NULL,
-    tone TEXT,
-    onset TEXT,
-    medial TEXT,
-    nucleus TEXT,
-    coda TEXT,
-    rhyme TEXT,
+    text TEXT NOT NULL,
+    onset TEXT NOT NULL,
+    medial TEXT NOT NULL,
+    nucleus TEXT NOT NULL,
+    coda TEXT NOT NULL,
+    rhyme TEXT NOT NULL,
     notes TEXT,
     FOREIGN KEY (lang) REFERENCES languages(iso6392),
-    CONSTRAINT spell_unique UNIQUE (text, lang)
+    CONSTRAINT syllable_unique UNIQUE (text, ipa, lang)
 );
 
 CREATE TABLE IF NOT EXISTS tones(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
-    text TEXT NOT NULL,
+    ipa TEXT NOT NULL,
     lang TEXT NOT NULL,
-    name TEXT,
-    num INTEGER,
-    CONSTRAINT tone_unique UNIQUE (text, lang)
+    name TEXT NOT NULL,
+    nums INTEGER NOT NULL,
+    CONSTRAINT tone_unique UNIQUE (ipa, lang)
 );
 CREATE TABLE IF NOT EXISTS onsets(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa  TEXT NOT NULL,
     text TEXT NOT NULL,
     lang TEXT NOT NULL,
-    CONSTRAINT onsets_unique UNIQUE (text, lang)
+    CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
 );
 CREATE TABLE IF NOT EXISTS medials(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa  TEXT NOT NULL,
     text TEXT NOT NULL,
     lang TEXT NOT NULL,
-    CONSTRAINT medials_unique UNIQUE (text, lang)
+    CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
 );
 CREATE TABLE IF NOT EXISTS nucleus(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa  TEXT NOT NULL,
     text TEXT NOT NULL,
     lang TEXT NOT NULL,
-    CONSTRAINT nucleus_unique UNIQUE (text, lang)
+    CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
 );
 CREATE TABLE IF NOT EXISTS codas(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa  TEXT NOT NULL,
     text TEXT NOT NULL,
     lang TEXT NOT NULL,
-    CONSTRAINT coda_unique UNIQUE (text, lang)
+    CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
 );
 CREATE TABLE IF NOT EXISTS rhymes(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
+    ipa  TEXT NOT NULL,
     text TEXT NOT NULL,
     lang TEXT NOT NULL,
-    CONSTRAINT rhyme_unique UNIQUE (text, lang)
+    CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
 );
 
 -- join tables
@@ -153,9 +159,12 @@ CREATE TABLE IF NOT EXISTS rhymes_syllables(
 CREATE TABLE IF NOT EXISTS syllables_words(
     syl_id INTEGER NOT NULL,
     word_id INTEGER NOT NULL,
+    idx 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,
@@ -165,14 +174,26 @@ CREATE TABLE IF NOT EXISTS words_idioms(
 
 
 -- 
-CREATE TABLE IF NOT EXISTS pronunciation(
+CREATE TABLE IF NOT EXISTS syl_ipa(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
-    type TEXT CHECK(type IN ('word', 'syllable', 'idiom')) NOT NULL,
-    parent_id INTEGER NOT NULL,
+    syl_id INTEGER NOT NULL,
+    ipa TEXT NOT NULL,
+    onset TEXT NOT NULL,
+    medial TEXT NOT NULL,
+    nucleus TEXT NOT NULL,
+    rhyme TEXT NOT NULL,
+    coda TEXT NOT NULL,
+    notes TEXT,
+    CONSTRAINT syl_ipa_unique UNIQUE (ipa, syl_id)
+);
+
+CREATE TABLE IF NOT EXISTS word_phonetics(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    word_id INTEGER NOT NULL,
     ipa TEXT NOT NULL,
     syllables INTEGER NOT NULL,
     tag TEXT,
     notes TEXT,
-    CONSTRAINT ipa_unique UNIQUE (ipa, parent_id)
+    CONSTRAINT ipa_unique UNIQUE (ipa, word_id)
 );
-CREATE INDEX IF NOT EXISTS idx_words_ipa ON pronunciation(ipa, parent_id);
+CREATE INDEX IF NOT EXISTS idx_words_ipa ON word_phonetics(ipa, word_id);