glorious new db

1 files changed, 238 insertions, 0 deletions

diff --git a/src/lib/db/prosodydb.ts b/src/lib/db/prosodydb.ts
new file mode 100644
index 0000000..b3b973b
--- /dev/null
+++ b/src/lib/db/prosodydb.ts
@@ -0,0 +1,238 @@
+import Database from "bun:sqlite";
+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 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");
+    this.db = db;
+  }
+  async init() {
+    const file = Bun.file("./prosodyschema.sql");
+    const sql = await file.text();
+    this.db.exec(sql);
+  }
+  // selects
+  fetchWords(words: string[]) {
+    const query = this.db.query(
+      `SELECT id FROM words where spelling IN (${words.map((w) => `'${w}'`).join(", ")})`,
+    );
+    return query.all() as Array<{ id: number }>;
+  }
+  // inserts
+
+  addLanguage(code: string, name: string) {
+    const query = this.db
+      .query(`INSERT OR IGNORE INTO languages(iso6392, english) VALUES(?, ?)`)
+      .run(code, name);
+  }
+  addPronunciation(
+    type: ItemType,
+    parentId: 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);
+    }
+  }
+  addWordRhyme(wordId: number | bigint, ipa: string, lang: string, notes: Str) {
+    console.log("wordrhyme", notes);
+    try {
+      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(?, ?)
+          `,
+        )
+        .run(wordId, query.id);
+    } catch (e) {
+      // console.error(e);
+    }
+  }
+  addIdiom(spelling: string, lang: string) {
+    const query = this.db.query(
+      `INSERT INTO idioms(spelling, lang) VALUES(?, ?)`,
+    );
+    const res = query.run(spelling, lang);
+    return res;
+  }
+  findIdiomWords(spelling: string, idId: number | bigint) {
+    const split = spelling.split(" ");
+    const words = this.fetchWords(split);
+    console.log({ words });
+    const tx = this.db.transaction(() => {
+      for (const w of words) {
+        this.db
+          .query(
+            `
+            INSERT INTO words_idioms(word_id, idiom_id) VALUES(?, ?)
+          `,
+          )
+          .run(w.id, idId);
+      }
+    });
+    tx();
+  }
+  findIdiomsWords() {
+    const rows: any = this.db.query(`SELECT id, spelling FROM idioms`);
+    for (const row of rows) {
+      this.findIdiomWords(row.spelling, row.id);
+    }
+  }
+  addWord(spelling: string, lang: string) {
+    const query = this.db.query(
+      // `INSERT OR IGNORE INTO words(spelling, lang) VALUES(?, ?)`,
+      `INSERT INTO words(spelling, lang) VALUES(?, ?)`,
+    );
+    const res = query.run(spelling, lang);
+    const wordId = res.lastInsertRowid;
+    return wordId;
+  }
+  addSyllable(
+    wordId: number | bigint,
+    text: string,
+    lang: string,
+    long: boolean,
+    onset: Str,
+    medial: Str,
+    nucleus: string,
+    coda: Str,
+    rhyme: string,
+    tone: Str,
+    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(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
+      );
+      const res = query.run(
+        text,
+        lang,
+        long,
+        onset,
+        medial,
+        nucleus,
+        coda,
+        rhyme,
+        tone,
+        notes,
+      );
+      const sylId = res.lastInsertRowid;
+
+      const res1 = this.db
+        .query(`INSERT INTO syllables_words(syl_id, word_id) VALUES(?, ?)`)
+        .run(sylId, wordId);
+      //
+      return sylId;
+    });
+    const sylId = tx();
+    let res1: any;
+    if (onset) {
+      res1 = this.db
+        .query(
+          `INSERT INTO onsets(text, lang) VALUES(?, ?)
+           ON CONFLICT(text, lang) DO UPDATE SET
+           text = excluded.text
+           RETURNING rowid
+          `,
+        )
+        .get(onset, lang);
+      this.db
+        .query(`INSERT INTO onsets_syllables(syl_id, onset_id) VALUES(?, ?)`)
+        .run(sylId, res1.id);
+    }
+    if (medial) {
+      res1 = this.db
+        .query(
+          `INSERT INTO medials(text, lang) VALUES(?, ?)
+           ON CONFLICT(text, lang) DO UPDATE SET
+           text = excluded.text
+           RETURNING rowid
+          `,
+        )
+        .get(medial, lang);
+      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
+         text = excluded.text
+         RETURNING rowid
+        `,
+      )
+      .get(nucleus, lang);
+    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
+           text = excluded.text
+           RETURNING rowid
+          `,
+        )
+        .get(coda, lang);
+      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
+         text = excluded.text
+         RETURNING rowid
+        `,
+      )
+      .get(rhyme, lang);
+    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
+           RETURNING rowid
+          `,
+        )
+        .get(tone, lang);
+      this.db
+        .query(`INSERT INTO tones_syllables(syl_id, tone_id) VALUES(?, ?)`)
+        .run(sylId, res1.id);
+    }
+  }
+
+  // reads
+}
+const db = new DatabaseHandler();
+
+export default db;