From a3f24ea79b14394b24c4b60a010651eb29eeb872 Mon Sep 17 00:00:00 2001 From: polwex Date: Thu, 29 May 2025 12:10:22 +0700 Subject: glorious new db --- src/lib/db/prosodyschema.sql | 178 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 178 insertions(+) create mode 100644 src/lib/db/prosodyschema.sql (limited to 'src/lib/db/prosodyschema.sql') diff --git a/src/lib/db/prosodyschema.sql b/src/lib/db/prosodyschema.sql new file mode 100644 index 0000000..e70b005 --- /dev/null +++ b/src/lib/db/prosodyschema.sql @@ -0,0 +1,178 @@ +-- Enable foreign key support +PRAGMA foreign_keys = ON; +PRAGMA journal_mode = WAL; +PRAGMA cache_size = -2000; +PRAGMA mmap_size = 30000000000; + + +-- proper prosody now +-- +-- +-- +CREATE TABLE IF NOT EXISTS languages( + iso6392 TEXT PRIMARY KEY, + -- bcp47 TEXT PRIMARY KEY, + -- iso6393 TEXT NOT NULL, + english TEXT NOT NULL + -- native TEXT, + -- iso6391 TEXT, + -- iso6395 TEXT, + -- glottolog TEXT +); + +CREATE TABLE IF NOT EXISTS idioms( + id INTEGER PRIMARY KEY AUTOINCREMENT, + spelling TEXT NOT NULL, + lang TEXT NOT NULL, + frequency INTEGER, + FOREIGN KEY (lang) REFERENCES languages(iso6392), + CONSTRAINT spell_unique UNIQUE (spelling, lang) +); + +CREATE INDEX IF NOT EXISTS idx_idioms_spelling ON idioms(spelling); +CREATE TABLE IF NOT EXISTS words( + id INTEGER PRIMARY KEY AUTOINCREMENT, + spelling TEXT NOT NULL, + lang TEXT NOT NULL, + frequency INTEGER, + FOREIGN KEY (lang) REFERENCES languages(iso6392), + CONSTRAINT spell_unique UNIQUE (spelling, lang) +); + +CREATE INDEX IF NOT EXISTS idx_words_spelling ON words(spelling); + +CREATE TABLE IF NOT EXISTS word_rhymes( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + notes TEXT, + CONSTRAINT wrhyme_unique UNIQUE (text, lang) +); +CREATE TABLE IF NOT EXISTS words_rhymes( + word_id INTEGER NOT NULL, + wrhyme_id INTEGER NOT NULL, + FOREIGN KEY (word_id) REFERENCES words(id), + FOREIGN KEY (wrhyme_id) REFERENCES word_rhymes(id) +); + +-- break up syllables +CREATE TABLE IF NOT EXISTS syllables( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + long INTEGER NOT NULL, + tone TEXT, + onset TEXT, + medial TEXT, + nucleus TEXT, + coda TEXT, + rhyme TEXT, + notes TEXT, + FOREIGN KEY (lang) REFERENCES languages(iso6392), + CONSTRAINT spell_unique UNIQUE (text, lang) +); + +CREATE TABLE IF NOT EXISTS tones( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + name TEXT, + num INTEGER, + CONSTRAINT tone_unique UNIQUE (text, lang) +); +CREATE TABLE IF NOT EXISTS onsets( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + CONSTRAINT onsets_unique UNIQUE (text, lang) +); +CREATE TABLE IF NOT EXISTS medials( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + CONSTRAINT medials_unique UNIQUE (text, lang) +); +CREATE TABLE IF NOT EXISTS nucleus( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + CONSTRAINT nucleus_unique UNIQUE (text, lang) +); +CREATE TABLE IF NOT EXISTS codas( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + CONSTRAINT coda_unique UNIQUE (text, lang) +); +CREATE TABLE IF NOT EXISTS rhymes( + id INTEGER PRIMARY KEY AUTOINCREMENT, + text TEXT NOT NULL, + lang TEXT NOT NULL, + CONSTRAINT rhyme_unique UNIQUE (text, lang) +); + +-- join tables + +CREATE TABLE IF NOT EXISTS tones_syllables( + syl_id INTEGER NOT NULL, + tone_id INTEGER NOT NULL, + FOREIGN KEY (syl_id) REFERENCES syllables(id), + FOREIGN KEY (tone_id) REFERENCES tones(id) +); +CREATE TABLE IF NOT EXISTS onsets_syllables( + syl_id INTEGER NOT NULL, + onset_id INTEGER NOT NULL, + FOREIGN KEY (syl_id) REFERENCES syllables(id), + FOREIGN KEY (onset_id) REFERENCES onsets(id) +); +CREATE TABLE IF NOT EXISTS medials_syllables( + syl_id INTEGER NOT NULL, + medial_id INTEGER NOT NULL, + FOREIGN KEY (syl_id) REFERENCES syllables(id), + FOREIGN KEY (medial_id) REFERENCES medials(id) +); +CREATE TABLE IF NOT EXISTS nucleus_syllables( + syl_id INTEGER NOT NULL, + nucleus_id INTEGER NOT NULL, + FOREIGN KEY (syl_id) REFERENCES syllables(id), + FOREIGN KEY (nucleus_id) REFERENCES nucleus(id) +); +CREATE TABLE IF NOT EXISTS codas_syllables( + syl_id INTEGER NOT NULL, + coda_id INTEGER NOT NULL, + FOREIGN KEY (syl_id) REFERENCES syllables(id), + FOREIGN KEY (coda_id) REFERENCES codas(id) +); +CREATE TABLE IF NOT EXISTS rhymes_syllables( + syl_id INTEGER NOT NULL, + rhyme_id INTEGER NOT NULL, + FOREIGN KEY (syl_id) REFERENCES syllables(id), + FOREIGN KEY (rhyme_id) REFERENCES rhymes(id) +); + +CREATE TABLE IF NOT EXISTS syllables_words( + syl_id INTEGER NOT NULL, + word_id INTEGER NOT NULL, + FOREIGN KEY (syl_id) REFERENCES syllables(id), + FOREIGN KEY (word_id) REFERENCES words(id) +); +CREATE TABLE IF NOT EXISTS words_idioms( + word_id INTEGER NOT NULL, + idiom_id INTEGER NOT NULL, + FOREIGN KEY (idiom_id) REFERENCES idioms(id), + FOREIGN KEY (word_id) REFERENCES words(id) +); + + +-- +CREATE TABLE IF NOT EXISTS pronunciation( + id INTEGER PRIMARY KEY AUTOINCREMENT, + type TEXT CHECK(type IN ('word', 'syllable', 'idiom')) NOT NULL, + parent_id INTEGER NOT NULL, + ipa TEXT NOT NULL, + syllables INTEGER NOT NULL, + tag TEXT, + notes TEXT, + CONSTRAINT ipa_unique UNIQUE (ipa, parent_id) +); +CREATE INDEX IF NOT EXISTS idx_words_ipa ON pronunciation(ipa, parent_id); -- cgit v1.2.3