From 249230c8e0e1bdb8ae4f433262997b84ee274904 Mon Sep 17 00:00:00 2001
From: polwex <polwex@sortug.com>
Date: Mon, 2 Jun 2025 23:14:05 +0700
Subject: simplified the damn schema

---
 src/lib/db/prosodydb.ts | 147 ++++++++++++++++++++----------------------------
 1 file changed, 61 insertions(+), 86 deletions(-)

(limited to 'src/lib/db/prosodydb.ts')

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