diff options
Diffstat (limited to 'src/lib/db/prosodydb.ts')
| -rw-r--r-- | src/lib/db/prosodydb.ts | 215 |
1 files changed, 201 insertions, 14 deletions
diff --git a/src/lib/db/prosodydb.ts b/src/lib/db/prosodydb.ts index d6da389..7c067d2 100644 --- a/src/lib/db/prosodydb.ts +++ b/src/lib/db/prosodydb.ts @@ -1,5 +1,5 @@ import Database from "bun:sqlite"; -import { Phoneme, Tone } from "../types/phonetics"; +import { MutationOrder, Phoneme, Tone } from "../types/phonetics"; import { ProsodyWord, ProsodyWordDB } from "../types/cards"; type Str = string | null; type ItemType = "word" | "syllable" | "idiom"; @@ -113,6 +113,7 @@ class DatabaseHandler { w.spelling, wp.ipa, w.frequency, + GROUP_CONCAT(s.text ORDER BY sw.idx) as syl_seq, GROUP_CONCAT(t.name ORDER BY sw.idx) as tone_sequence, COUNT(sw.syl_id) as syllable_count FROM words w @@ -127,17 +128,166 @@ class DatabaseHandler { spelling, ipa, frequency, + syl_seq, tone_sequence, syllable_count FROM word_tone_sequences WHERE tone_sequence LIKE ? AND syllable_count = ? - ORDER BY frequency DESC NULLS LAST; + ORDER BY frequency ASC NULLS LAST; `, ); return query.all(toneString.slice(1), tones.length) as any[]; } + // fetchWordsByToneAndSyls(tones: Array<string | null>) { + // const toneString = tones.reduce((acc: string, item) => { + // if (!item) return `${acc},%`; + // else return `${acc},${item}`; + // }, ""); + // console.log({ toneString }); + // const query = this.db.query( + // ` + // WITH word_tone_sequences AS ( + // SELECT + // w.id as word_id, + // w.spelling, + // wp.ipa, + // w.frequency, + // GROUP_CONCAT(s.text ORDER BY sw.idx) as syl_seq, + // GROUP_CONCAT(t.name ORDER BY sw.idx) as tone_sequence, + // COUNT(sw.syl_id) as syllable_count + // FROM words w + // JOIN word_phonetics wp ON w.id = wp.word_id + // JOIN syllables_words sw ON w.id = sw.word_id + // JOIN syllables s ON sw.syl_id = s.id + // JOIN tones t ON s.tone = t.id + // GROUP BY w.id, w.spelling, w.lang, w.frequency + // ) + // SELECT + // word_id, + // spelling, + // ipa, + // frequency, + // syl_seq, + // tone_sequence, + // syllable_count + // FROM word_tone_sequences + // WHERE tone_sequence LIKE ? + // AND syllable_count = ? + // ORDER BY frequency DESC NULLS LAST; + // `, + // ); + // return query.all(toneString.slice(1), tones.length) as any[]; + // } + fetchWordsByToneSylsWords(order: MutationOrder) { + console.log({ order }); + type Acc = { tones: string; syls: string }; + const strings = order.reduce( + (acc: Acc, item, idx) => { + const startString = idx === 0 ? "" : ","; + if ("change" in item) + return { + tones: `${acc.tones}${startString}${item.change}`, + syls: `${acc.syls}${startString}%`, + }; + else + return { + tones: `${acc.tones}${startString}%`, + syls: `${acc.syls}${startString}${item.keep}`, + }; + }, + { tones: "", syls: "" }, + ); + const query = this.db.query(` + SELECT + w.id as word_id, + w.spelling, + w.lang, + w.frequency, + wp.ipa, + wp.syllable_sequence, + wp.tone_sequence, + wp.ipa_sequence, + GROUP_CONCAT(s.text ORDER BY sw.idx) as syllable_pattern, + GROUP_CONCAT(t.name ORDER BY sw.idx) as tone_pattern + FROM words w + JOIN syllables_words sw ON w.id = sw.word_id + JOIN syllables s ON sw.syl_id = s.id + JOIN tones t ON s.tone = t.id + JOIN word_phonetics wp ON wp.word_id= w.id + WHERE wp.syllable_sequence LIKE ?1 + AND tone_sequence LIKE ?2 + AND syllable_count = ?3 + GROUP BY w.id, w.spelling, w.lang, w.frequency + ORDER BY w.frequency ASC NULLS LAST; `); + return query.all(strings.syls, strings.tones, order.length) as any[]; + } // inserts + superAdd(p: { + word: string; + lang: string; + frequency: number | null; + wordNotes: Str; + phonetics: Array<{ + ipa: string; + syllable_count: number; + syllable_sequence: string; + tone_sequence: string; + ipa_sequence: string; + tags: Str; + notes: Str; + wordRhyme: Str; + syllables: Array<{ + idx: number; + stressed: boolean | null; + spelling: string; + ipa: string; + long: boolean; + onset: Phoneme; + medial: Phoneme; + nucleus: Phoneme; + coda: Phoneme; + rhyme: Phoneme; + tone: Tone; + notes: Str; + }>; + }>; + }) { + const tx = this.db.transaction(() => { + const wordId = this.addWord(p.word, p.lang, p.frequency, p.wordNotes); + for (const ph of p.phonetics) { + this.addPronunciation( + wordId, + ph.ipa, + ph.syllable_count, + ph.syllable_sequence, + ph.tone_sequence, + ph.ipa_sequence, + ph.tags, + ph.notes, + ); + for (const syl of ph.syllables) { + this.addSyllable( + wordId, + syl.idx, + syl.stressed, + p.lang, + syl.ipa, + syl.long, + syl.spelling, + syl.onset, + syl.medial, + syl.nucleus, + syl.coda, + syl.rhyme, + syl.tone, + syl.notes, + ); + } + } + }); + tx(); + } addLanguage(code: string, name: string) { const query = this.db @@ -147,15 +297,44 @@ class DatabaseHandler { addPronunciation( wordId: number | bigint, ipa: string, - syllables: number, + syllable_count: number, + syllable_sequence: string, + tone_sequence: string, + ipa_sequence: string, tags: Str, notes: Str, ) { + console.log({ + wordId, + ipa, + syllable_count, + syllable_sequence, + tone_sequence, + ipa_sequence, + }); const query = this.db .query( - `INSERT OR IGNORE INTO word_phonetics(word_id,ipa, syllables, tag, notes) VALUES(?, ?, ?, ?, ?)`, + `INSERT OR IGNORE INTO word_phonetics( + word_id, + ipa, + syllable_count, + syllable_sequence, + tone_sequence, + ipa_sequence, + tag, + notes) + VALUES(?, ?, ?, ?, ?, ?, ?, ?)`, ) - .run(wordId, ipa, syllables, tags, notes); + .run( + wordId, + ipa, + syllable_count, + syllable_sequence, + tone_sequence, + ipa_sequence, + tags, + notes, + ); } addWordRhyme(wordId: number | bigint, ipa: string, lang: string, notes: Str) { const query = this.db @@ -212,12 +391,14 @@ class DatabaseHandler { notes: Str, ) { const query = this.db.query( - `INSERT OR IGNORE INTO words(spelling, lang, frequency, notes) VALUES(?, ?, ?, ?)`, - // `INSERT INTO words(spelling, lang) VALUES(?, ?)`, + `INSERT INTO words(spelling, lang, frequency, notes) VALUES(?, ?, ?, ?) + ON CONFLICT(spelling, lang) DO UPDATE SET + lang = excluded.lang + RETURNING rowid + `, ); - const res = query.run(spelling, lang, frequency, notes); - const wordId = res.lastInsertRowid; - return wordId; + const res = query.get(spelling, lang, frequency, notes) as { id: number }; + return res.id; } addSyllable( wordId: number | bigint, @@ -292,9 +473,15 @@ class DatabaseHandler { .get(tone.letters, lang, tone.name, tone.numbers) as { id: number }; const query = this.db.query( - `INSERT INTO syllables(lang, ipa, long, text, onset, medial, nucleus, coda, rhyme, tone, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, + `INSERT INTO syllables( + lang, ipa, long, text, onset, medial, nucleus, coda, rhyme, tone, notes) + VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + ON CONFLICT(text, ipa, lang) DO UPDATE SET + lang = excluded.lang + RETURNING rowid + `, ); - const res = query.run( + const res = query.get( lang, ipa, long, @@ -306,8 +493,8 @@ class DatabaseHandler { rhymeId.id, toneId.id, notes, - ); - const sylId = res.lastInsertRowid; + ) as { id: number }; + const sylId = res.id; // const res1 = this.db .query( |