1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
|
-- 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,
notes TEXT,
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_wrhymes(
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,
lang TEXT NOT NULL,
ipa TEXT NOT NULL,
long INTEGER NOT NULL,
text TEXT NOT NULL,
onset TEXT NOT NULL,
medial TEXT NOT NULL,
nucleus TEXT NOT NULL,
coda TEXT NOT NULL,
rhyme TEXT NOT NULL,
notes TEXT,
FOREIGN KEY (lang) REFERENCES languages(iso6392),
CONSTRAINT syllable_unique UNIQUE (text, ipa, lang)
);
CREATE TABLE IF NOT EXISTS tones(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ipa TEXT NOT NULL,
lang TEXT NOT NULL,
name TEXT NOT NULL,
nums INTEGER NOT NULL,
CONSTRAINT tone_unique UNIQUE (ipa, lang)
);
CREATE TABLE IF NOT EXISTS onsets(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ipa TEXT NOT NULL,
text TEXT NOT NULL,
lang TEXT NOT NULL,
CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
);
CREATE TABLE IF NOT EXISTS medials(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ipa TEXT NOT NULL,
text TEXT NOT NULL,
lang TEXT NOT NULL,
CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
);
CREATE TABLE IF NOT EXISTS nucleus(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ipa TEXT NOT NULL,
text TEXT NOT NULL,
lang TEXT NOT NULL,
CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
);
CREATE TABLE IF NOT EXISTS codas(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ipa TEXT NOT NULL,
text TEXT NOT NULL,
lang TEXT NOT NULL,
CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
);
CREATE TABLE IF NOT EXISTS rhymes(
id INTEGER PRIMARY KEY AUTOINCREMENT,
ipa TEXT NOT NULL,
text TEXT NOT NULL,
lang TEXT NOT NULL,
CONSTRAINT onsets_unique UNIQUE (ipa, 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,
idx 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 syl_ipa(
id INTEGER PRIMARY KEY AUTOINCREMENT,
syl_id INTEGER NOT NULL,
ipa TEXT NOT NULL,
onset TEXT NOT NULL,
medial TEXT NOT NULL,
nucleus TEXT NOT NULL,
rhyme TEXT NOT NULL,
coda TEXT NOT NULL,
notes TEXT,
CONSTRAINT syl_ipa_unique UNIQUE (ipa, syl_id)
);
CREATE TABLE IF NOT EXISTS word_phonetics(
id INTEGER PRIMARY KEY AUTOINCREMENT,
word_id INTEGER NOT NULL,
ipa TEXT NOT NULL,
syllables INTEGER NOT NULL,
tag TEXT,
notes TEXT,
CONSTRAINT ipa_unique UNIQUE (ipa, word_id)
);
CREATE INDEX IF NOT EXISTS idx_words_ipa ON word_phonetics(ipa, word_id);
|
