diff options
Diffstat (limited to 'src/lib/db/prosodydb.ts')
| -rw-r--r-- | src/lib/db/prosodydb.ts | 238 |
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; |