simplified the damn schema

2 files changed, 84 insertions, 139 deletions

diff --git a/src/lib/db/prosodydb.ts b/src/lib/db/prosodydb.ts
index ec95359..1cfb8f0 100644
--- a/src/lib/db/prosodydb.ts
+++ b/src/lib/db/prosodydb.ts
@@ -118,53 +118,11 @@ class DatabaseHandler {
     nucleus: Phoneme,
     coda: Phoneme,
     rhyme: Phoneme,
-    tone: Tone | null,
+    tone: Tone,
     notes: Str,
   ) {
     const tx = this.db.transaction(() => {
-      const query = this.db.query(
-        `INSERT INTO syllables(lang, ipa, long, text, onset, medial, nucleus, coda, rhyme, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
-      );
-      // TODO need a dual structure here for IPA and orto
-      const res = query.run(
-        lang,
-        ipa,
-        long,
-        text,
-        onset.spelling,
-        medial.spelling,
-        nucleus.spelling,
-        coda.spelling,
-        rhyme.spelling,
-        notes,
-      );
-      const sylId = res.lastInsertRowid;
-      const ipaq = this.db.query(`
-        INSERT INTO syl_ipa(syl_id, ipa, onset, medial, nucleus, coda, rhyme, notes)
-        VALUES(?, ?, ?, ?, ?, ?, ?, ?)`);
-      ipaq.run(
-        sylId,
-        ipa,
-        onset.ipa,
-        medial.ipa,
-        nucleus.ipa,
-        coda.ipa,
-        rhyme.ipa,
-        null,
-      );
-      //
-      const res1 = this.db
-        .query(
-          `INSERT INTO syllables_words(syl_id, word_id, idx) VALUES(?, ?, ?)`,
-        )
-        .run(sylId, wordId, sylIdx);
-      //
-      return sylId;
-    });
-    const sylId = tx();
-    let res1: any;
-    if (onset) {
-      res1 = this.db
+      const onsetId = this.db
         .query(
           `INSERT INTO onsets(ipa, lang, text) VALUES(?, ?, ?)
            ON CONFLICT(ipa, lang, text) DO UPDATE SET
@@ -172,13 +130,8 @@ class DatabaseHandler {
            RETURNING rowid
           `,
         )
-        .get(onset.ipa, lang, onset.spelling);
-      this.db
-        .query(`INSERT INTO onsets_syllables(syl_id, onset_id) VALUES(?, ?)`)
-        .run(sylId, res1.id);
-    }
-    if (medial) {
-      res1 = this.db
+        .get(onset.ipa, lang, onset.spelling) as number;
+      const medialId = this.db
         .query(
           `INSERT INTO medials(ipa, lang, text) VALUES(?, ?, ?)
            ON CONFLICT(ipa, lang, text) DO UPDATE SET
@@ -186,25 +139,17 @@ class DatabaseHandler {
            RETURNING rowid
           `,
         )
-        .get(medial.ipa, lang, medial.spelling);
-      this.db
-        .query(`INSERT INTO medials_syllables(syl_id, medial_id) VALUES(?, ?)`)
-        .run(sylId, res1.id);
-    }
-    res1 = this.db
-      .query(
-        `INSERT INTO nucleus(ipa, lang, text) VALUES(?, ?, ?)
+        .get(medial.ipa, lang, medial.spelling) as number;
+      const nucleusId = this.db
+        .query(
+          `INSERT INTO nucleus(ipa, lang, text) VALUES(?, ?, ?)
          ON CONFLICT(ipa, lang, text) DO UPDATE SET
          text = excluded.text
          RETURNING rowid
         `,
-      )
-      .get(nucleus.ipa, lang, nucleus.spelling);
-    this.db
-      .query(`INSERT INTO nucleus_syllables(syl_id, nucleus_id) VALUES(?, ?)`)
-      .run(sylId, res1.id);
-    if (coda) {
-      res1 = this.db
+        )
+        .get(nucleus.ipa, lang, nucleus.spelling) as number;
+      const codaId = this.db
         .query(
           `INSERT INTO codas(ipa, lang, text) VALUES(?, ?, ?)
            ON CONFLICT(ipa, lang, text) DO UPDATE SET
@@ -212,25 +157,17 @@ class DatabaseHandler {
            RETURNING rowid
           `,
         )
-        .get(coda.ipa, lang, coda.spelling);
-      this.db
-        .query(`INSERT INTO codas_syllables(syl_id, coda_id) VALUES(?, ?)`)
-        .run(sylId, res1.id);
-    }
-    res1 = this.db
-      .query(
-        `INSERT INTO rhymes(ipa, lang, text) VALUES(?, ?, ?)
+        .get(coda.ipa, lang, coda.spelling) as number;
+      const rhymeId = this.db
+        .query(
+          `INSERT INTO rhymes(ipa, lang, text) VALUES(?, ?, ?)
          ON CONFLICT(ipa, lang, text) DO UPDATE SET
          text = excluded.text
          RETURNING rowid
         `,
-      )
-      .get(rhyme.ipa, lang, rhyme.spelling);
-    this.db
-      .query(`INSERT INTO rhymes_syllables(syl_id, rhyme_id) VALUES(?, ?)`)
-      .run(sylId, res1.id);
-    if (tone) {
-      res1 = this.db
+        )
+        .get(rhyme.ipa, lang, rhyme.spelling) as number;
+      const toneId = this.db
         .query(
           `INSERT INTO tones(ipa, lang, name, nums) VALUES(?, ?, ?, ?)
            ON CONFLICT(ipa, lang) DO UPDATE SET
@@ -238,11 +175,49 @@ class DatabaseHandler {
            RETURNING rowid
           `,
         )
-        .get(tone.letters, lang, tone.name, tone.numbers);
-      this.db
-        .query(`INSERT INTO tones_syllables(syl_id, tone_id) VALUES(?, ?)`)
-        .run(sylId, res1.id);
-    }
+        .get(tone.letters, lang, tone.name, tone.numbers) as number;
+
+      const query = this.db.query(
+        `INSERT INTO syllables(lang, ipa, long, text, onset, medial, nucleus, coda, rhyme, tone, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
+      );
+      // TODO need a dual structure here for IPA and orto
+      const res = query.run(
+        lang,
+        ipa,
+        long,
+        text,
+        onsetId,
+        medialId,
+        nucleusId,
+        codaId,
+        rhymeId,
+        toneId,
+        notes,
+      );
+      const sylId = res.lastInsertRowid;
+      const ipaq = this.db.query(`
+        INSERT INTO syl_ipa(syl_id, ipa, onset, medial, nucleus, coda, rhyme, notes)
+        VALUES(?, ?, ?, ?, ?, ?, ?, ?)`);
+      ipaq.run(
+        sylId,
+        ipa,
+        onset.ipa,
+        medial.ipa,
+        nucleus.ipa,
+        coda.ipa,
+        rhyme.ipa,
+        null,
+      );
+      //
+      const res1 = this.db
+        .query(
+          `INSERT INTO syllables_words(syl_id, word_id, idx) VALUES(?, ?, ?)`,
+        )
+        .run(sylId, wordId, sylIdx);
+      //
+      return sylId;
+    });
+    const sylId = tx();
   }
 
   // reads
diff --git a/src/lib/db/prosodyschema.sql b/src/lib/db/prosodyschema.sql
index 09dabc2..26818f3 100644
--- a/src/lib/db/prosodyschema.sql
+++ b/src/lib/db/prosodyschema.sql
@@ -57,22 +57,6 @@ CREATE TABLE IF NOT EXISTS words_wrhymes(
 );
 
 -- break up syllables
-CREATE TABLE IF NOT EXISTS syllables(
-    id INTEGER PRIMARY KEY AUTOINCREMENT,
-    lang TEXT NOT NULL,
-    ipa TEXT NOT NULL,
-    long INTEGER NOT NULL,
-    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 syllable_unique UNIQUE (text, ipa, lang)
-);
-
 CREATE TABLE IF NOT EXISTS tones(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     ipa TEXT NOT NULL,
@@ -116,45 +100,31 @@ CREATE TABLE IF NOT EXISTS rhymes(
     lang TEXT NOT NULL,
     CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
 );
+CREATE TABLE IF NOT EXISTS syllables(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    lang TEXT NOT NULL,
+    ipa TEXT NOT NULL,
+    long INTEGER NOT NULL,
+    text TEXT NOT NULL,
+    onset INTEGER  NOT NULL,
+    medial INTEGER  NOT NULL,
+    nucleus INTEGER  NOT NULL,
+    coda INTEGER  NOT NULL,
+    rhyme INTEGER  NOT NULL,
+    tone INTEGER NOT NULL,
+    notes TEXT,
+    FOREIGN KEY (lang) REFERENCES languages(iso6392),
+    FOREIGN KEY (onset) REFERENCES onsets(id),
+    FOREIGN KEY (medial) REFERENCES medials(id),
+    FOREIGN KEY (nucleus) REFERENCES nucleus(id),
+    FOREIGN KEY (coda) REFERENCES codas(id),
+    FOREIGN KEY (rhyme) REFERENCES rhymes(id),
+    FOREIGN KEY (tone) REFERENCES tones(id),
+    CONSTRAINT syllable_unique UNIQUE (text, ipa, 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)
-);
+-- join tables
 
 CREATE TABLE IF NOT EXISTS syllables_words(
     syl_id INTEGER NOT NULL,