all me here should merge

1 files changed, 84 insertions, 69 deletions

diff --git a/src/lib/db/prosodydb.ts b/src/lib/db/prosodydb.ts
index 52312bd..ec95359 100644
--- a/src/lib/db/prosodydb.ts
+++ b/src/lib/db/prosodydb.ts
@@ -1,11 +1,12 @@
 import Database from "bun:sqlite";
+import { Phoneme, Tone } from "../types/phonetics";
 type Str = string | null;
 type ItemType = "word" | "syllable" | "idiom";
 
 class DatabaseHandler {
   db: Database;
   constructor() {
-    const dbPath = "/home/y/code/bun/ssr/waku/bulkdata/prosodynew.db";
+    const dbPath = "/home/y/code/bun/ssr/waku/bulkdata/phon.db";
     const db = new Database(dbPath, { create: true });
     db.exec("PRAGMA journal_mode = WAL"); // Enable Write-Ahead Logging for better performance
     db.exec("PRAGMA foreign_keys = ON");
@@ -31,48 +32,39 @@ class DatabaseHandler {
       .run(code, name);
   }
   addPronunciation(
-    type: ItemType,
-    parentId: number | bigint,
+    wordId: number | bigint,
     ipa: string,
     syllables: number,
     tags: Str,
     notes: Str,
   ) {
-    try {
-      const query = this.db
-        .query(
-          `INSERT INTO pronunciation(type, parent_id,ipa, syllables, tag, notes) VALUES(?, ?, ?, ?, ?, ?)`,
-        )
-        .run(type, parentId, ipa, syllables, tags, notes);
-    } catch (e) {
-      // console.error(e);
-    }
+    const query = this.db
+      .query(
+        `INSERT OR IGNORE INTO word_phonetics(word_id,ipa, syllables, tag, notes) VALUES(?, ?, ?, ?, ?)`,
+      )
+      .run(wordId, ipa, syllables, tags, notes);
   }
   addWordRhyme(wordId: number | bigint, ipa: string, lang: string, notes: Str) {
-    try {
-      const query = this.db
-        .query(
-          `INSERT INTO word_rhymes(text, lang, notes) VALUES(?, ?, ?)
+    const query = this.db
+      .query(
+        `INSERT INTO word_rhymes(text, lang, notes) VALUES(?, ?, ?)
                  ON CONFLICT(text,lang) DO UPDATE SET
                  text = excluded.text
                  RETURNING rowid
           `,
-        )
-        .get(ipa, lang, notes) as { id: number };
-      const query2 = this.db
-        .query(
-          `
-            INSERT INTO words_idioms(word_id, idiom_id) VALUES(?, ?)
+      )
+      .get(ipa, lang, notes) as { id: number };
+    const query2 = this.db
+      .query(
+        `
+            INSERT INTO words_wrhymes(word_id, wrhyme_id) VALUES(?, ?)
           `,
-        )
-        .run(wordId, query.id);
-    } catch (e) {
-      // console.error(e);
-    }
+      )
+      .run(wordId, query.id);
   }
   addIdiom(spelling: string, lang: string) {
     const query = this.db.query(
-      `INSERT INTO idioms(spelling, lang) VALUES(?, ?)`,
+      `INSERT OR IGNORE INTO idioms(spelling, lang) VALUES(?, ?)`,
     );
     const res = query.run(spelling, lang);
     return res;
@@ -100,49 +92,72 @@ class DatabaseHandler {
       this.findIdiomWords(row.spelling, row.id);
     }
   }
-  addWord(spelling: string, lang: string) {
+  addWord(
+    spelling: string,
+    lang: string,
+    frequency: number | null,
+    notes: Str,
+  ) {
     const query = this.db.query(
-      // `INSERT OR IGNORE INTO words(spelling, lang) VALUES(?, ?)`,
-      `INSERT INTO words(spelling, lang) VALUES(?, ?)`,
+      `INSERT OR IGNORE INTO words(spelling, lang, frequency, notes) VALUES(?, ?, ?, ?)`,
+      // `INSERT INTO words(spelling, lang) VALUES(?, ?)`,
     );
-    const res = query.run(spelling, lang);
+    const res = query.run(spelling, lang, frequency, notes);
     const wordId = res.lastInsertRowid;
     return wordId;
   }
   addSyllable(
     wordId: number | bigint,
-    text: string,
+    sylIdx: number,
     lang: string,
+    ipa: string,
     long: boolean,
-    onset: Str,
-    medial: Str,
-    nucleus: string,
-    coda: Str,
-    rhyme: string,
-    tone: Str,
+    text: string,
+    onset: Phoneme,
+    medial: Phoneme,
+    nucleus: Phoneme,
+    coda: Phoneme,
+    rhyme: Phoneme,
+    tone: Tone | null,
     notes: Str,
   ) {
     const tx = this.db.transaction(() => {
       const query = this.db.query(
-        `INSERT INTO syllables(text, lang, long, onset, medial, nucleus, coda, rhyme, tone, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
+        `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(
-        text,
         lang,
+        ipa,
         long,
-        onset,
-        medial,
-        nucleus,
-        coda,
-        rhyme,
-        tone,
+        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) VALUES(?, ?)`)
-        .run(sylId, wordId);
+        .query(
+          `INSERT INTO syllables_words(syl_id, word_id, idx) VALUES(?, ?, ?)`,
+        )
+        .run(sylId, wordId, sylIdx);
       //
       return sylId;
     });
@@ -151,13 +166,13 @@ class DatabaseHandler {
     if (onset) {
       res1 = this.db
         .query(
-          `INSERT INTO onsets(text, lang) VALUES(?, ?)
-           ON CONFLICT(text, lang) DO UPDATE SET
+          `INSERT INTO onsets(ipa, lang, text) VALUES(?, ?, ?)
+           ON CONFLICT(ipa, lang, text) DO UPDATE SET
            text = excluded.text
            RETURNING rowid
           `,
         )
-        .get(onset, lang);
+        .get(onset.ipa, lang, onset.spelling);
       this.db
         .query(`INSERT INTO onsets_syllables(syl_id, onset_id) VALUES(?, ?)`)
         .run(sylId, res1.id);
@@ -165,65 +180,65 @@ class DatabaseHandler {
     if (medial) {
       res1 = this.db
         .query(
-          `INSERT INTO medials(text, lang) VALUES(?, ?)
-           ON CONFLICT(text, lang) DO UPDATE SET
+          `INSERT INTO medials(ipa, lang, text) VALUES(?, ?, ?)
+           ON CONFLICT(ipa, lang, text) DO UPDATE SET
            text = excluded.text
            RETURNING rowid
           `,
         )
-        .get(medial, lang);
+        .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(text, lang) VALUES(?, ?)
-         ON CONFLICT(text, lang) DO UPDATE SET
+        `INSERT INTO nucleus(ipa, lang, text) VALUES(?, ?, ?)
+         ON CONFLICT(ipa, lang, text) DO UPDATE SET
          text = excluded.text
          RETURNING rowid
         `,
       )
-      .get(nucleus, lang);
+      .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
         .query(
-          `INSERT INTO codas(text, lang) VALUES(?, ?)
-           ON CONFLICT(text, lang) DO UPDATE SET
+          `INSERT INTO codas(ipa, lang, text) VALUES(?, ?, ?)
+           ON CONFLICT(ipa, lang, text) DO UPDATE SET
            text = excluded.text
            RETURNING rowid
           `,
         )
-        .get(coda, lang);
+        .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(text, lang) VALUES(?, ?)
-         ON CONFLICT(text, lang) DO UPDATE SET
+        `INSERT INTO rhymes(ipa, lang, text) VALUES(?, ?, ?)
+         ON CONFLICT(ipa, lang, text) DO UPDATE SET
          text = excluded.text
          RETURNING rowid
         `,
       )
-      .get(rhyme, lang);
+      .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
         .query(
-          `INSERT INTO tones(text, lang) VALUES(?, ?)
-           ON CONFLICT(text, lang) DO UPDATE SET
-           text = excluded.text
+          `INSERT INTO tones(ipa, lang, name, nums) VALUES(?, ?, ?, ?)
+           ON CONFLICT(ipa, lang) DO UPDATE SET
+           ipa = excluded.ipa
            RETURNING rowid
           `,
         )
-        .get(tone, lang);
+        .get(tone.letters, lang, tone.name, tone.numbers);
       this.db
         .query(`INSERT INTO tones_syllables(syl_id, tone_id) VALUES(?, ?)`)
         .run(sylId, res1.id);