· 4 years ago · May 13, 2021, 12:20 AM
1DROP TABLE IF EXISTS grammatical_category;
2DROP TABLE IF EXISTS grammeme;
3DROP TABLE IF EXISTS language;
4DROP TABLE IF EXISTS morpheme;
5DROP TABLE IF EXISTS map_morpheme_language;
6DROP TABLE IF EXISTS accent;
7DROP TABLE IF EXISTS word;
8DROP TABLE IF EXISTS map_word_language;
9DROP TABLE IF EXISTS definition;
10DROP TABLE IF EXISTS map_morpheme_word;
11DROP TABLE IF EXISTS map_gloss_word;
12DROP TABLE IF EXISTS pronunciation;
13DROP TABLE IF EXISTS script;
14DROP TABLE IF EXISTS grapheme;
15
16CREATE TABLE grammatical_category (
17 category_id INTEGER PRIMARY KEY AUTOINCREMENT,
18 category_val TEXT UNIQUE NOT NULL
19);
20
21CREATE TABLE grammeme (
22 grammeme_id INTEGER PRIMARY KEY AUTOINCREMENT,
23 grammeme_val TEXT NOT NULL,
24 category_id INTEGER,
25 FOREIGN KEY (category_id) REFERENCES grammatical_category (category_id)
26);
27
28CREATE TABLE language (
29 language_id INTEGER PRIMARY KEY AUTOINCREMENT,
30 language_val TEXT NOT NULL UNIQUE,
31 eng_name TEXT,
32 ancestor_id INTEGER,
33 iso_639_1 TEXT,
34 iso_639_2 TEXT,
35 iso_639_3 TEXT,
36 FOREIGN KEY (ancestor_id) REFERENCES language (language_id)
37);
38
39CREATE TABLE morpheme (
40 morpheme_id INTEGER PRIMARY KEY AUTOINCREMENT,
41 morpheme_val TEXT NOT NULL,
42 grammeme_id INTEGER,
43 FOREIGN KEY (grammeme_id) REFERENCES grammeme (grammeme_id)
44);
45
46CREATE TABLE map_morpheme_language (
47 map_id INTEGER PRIMARY KEY AUTOINCREMENT,
48 morpheme_id INTEGER NOT NULL,
49 language_id INTEGER NOT NULL,
50 is_bound INTEGER NOT NULL DEFAULT 0,
51 FOREIGN KEY (morpheme_id) REFERENCES morpheme (morpheme_id),
52 FOREIGN KEY (language_id) REFERENCES language (language_id)
53);
54
55CREATE TABLE accent (
56 accent_id INTEGER PRIMARY KEY AUTOINCREMENT,
57 accent_val TEXT NOT NULL,
58 language_id INTEGER NOT NULL,
59 abbreviation TEXT,
60 IETF TEXT UNIQUE,
61 FOREIGN KEY (language_id) REFERENCES language (language_id)
62);
63
64-- CREATE TABLE word (
65-- word_id INTEGER PRIMARY KEY AUTOINCREMENT,
66-- word_val TEXT NOT NULL,
67-- language_id INTEGER NOT NULL,
68-- is_lemma INTEGER NOT NULL,
69-- lemma_id INTEGER NOT NULL,
70-- ancestor_id INTEGER,
71-- FOREIGN KEY (lemma_id) REFERENCES word (word_id),
72-- FOREIGN KEY (language_id) REFERENCES language (language_id),
73-- FOREIGN KEY (ancestor_id) REFERENCES word (word_id)
74-- );
75
76CREATE TABLE word (
77 word_id INTEGER PRIMARY KEY AUTOINCREMENT,
78 word_val TEXT NOT NULL
79);
80
81CREATE TABLE map_word_language (
82 map_id INTEGER PRIMARY KEY AUTOINCREMENT,
83 word_id INTEGER NOT NULL,
84 language_id INTEGER NOT NULL,
85 is_lemma INTEGER NOT NULL,
86 lemma_id INTEGER NOT NULL,
87 ancestor_id INTEGER,
88 FOREIGN KEY (word_id) REFERENCES word (word_id),
89 FOREIGN KEY (language_id) REFERENCES language (language_id),
90 FOREIGN KEY (lemma_id) REFERENCES word (word_id),
91 FOREIGN KEY (ancestor_id) REFERENCES word (word_id)
92);
93
94CREATE TABLE definition (
95 definition_id INTEGER PRIMARY KEY AUTOINCREMENT,
96 definition_val TEXT NOT NULL,
97 word_id INTEGER NOT NULL,
98 FOREIGN KEY (word_id) REFERENCES map_word_language (word_id)
99);
100
101CREATE TABLE map_morpheme_word (
102 map_id INTEGER PRIMARY KEY AUTOINCREMENT,
103 morpheme_id INTEGER NOT NULL,
104 word_id INTEGER NOT NULL,
105 FOREIGN KEY (morpheme_id) REFERENCES morpheme (morpheme_id),
106 FOREIGN KEY (word_id) REFERENCES map_word_language (map_id)
107);
108
109CREATE TABLE map_gloss_word (
110 map_id INTEGER PRIMARY KEY AUTOINCREMENT,
111 gloss_id INTEGER NOT NULL,
112 word_id INTEGER NOT NULL,
113 FOREIGN KEY (gloss_id) REFERENCES word (word_id)
114 FOREIGN KEY (word_id) REFERENCES map_word_language (map_id),
115);
116
117CREATE TABLE pronunciation (
118 pronunciation_id INTEGER PRIMARY KEY AUTOINCREMENT,
119 pronunciation_val TEXT NOT NULL,
120 word_id INTEGER NOT NULL,
121 accent_id INTEGER NOT NULL,
122 FOREIGN KEY (word_id) REFERENCES map_word_language (map_id),
123 FOREIGN KEY (accent_id) REFERENCES accent (accent_id)
124);
125
126-- CREATE TABLE script (
127-- script_id INTEGER PRIMARY KEY AUTOINCREMENT,
128-- script_val TEXT NOT NULL
129-- );
130
131-- CREATE TABLE grapheme (
132-- grapheme_id INTEGER PRIMARY KEY AUTOINCREMENT,
133-- grapheme_val TEXT NOT NULL,
134-- script_id INTEGER NOT NULL,
135-- FOREIGN KEY (script_id) REFERENCES script (script_id)
136-- );
137
138INSERT INTO grammatical_category (category_val)
139 VALUES
140 ("number")
141;
142
143INSERT INTO grammeme (grammeme_val, category_id)
144 VALUES
145 ("singular", 1),
146 ("plural", 1)
147;
148
149INSERT INTO language (language_val, eng_name)
150 VALUES
151 ("English", "English"),
152 ("Middle English", "Middle English")
153;
154
155INSERT INTO morpheme (morpheme_val, grammeme_id)
156 VALUES
157 ("bread", NULL),
158 ("-s", 2)
159;
160
161INSERT INTO map_morpheme_language (morpheme_id, language_id, is_bound)
162 VALUES
163 (1, 1, 0),
164 (2, 1, 1)
165;
166
167INSERT INTO accent (accent_val, language_id, abbreviation, IETF)
168 VALUES
169 ("Unspecified", 1, NULL, NULL),
170 ("American English", 1, "AmE", "en-US"),
171 ("General Australian", 1, "AuE", "en-AU"),
172 ("British English", 1, "BrE", "en-GB")
173;
174
175INSERT INTO word (word_val)
176 VALUES
177 ("bread"),
178 ("bred"),
179 ("breads")
180;
181
182INSERT INTO map_word_language (word_id, language_id, is_lemma, lemma_id, ancestor_id)
183 VALUES
184 (1, 1, 1, 1, 2),
185 (2, 2, 1, 2, NULL),
186 (3, 1, 0, 1, 2)
187;
188
189INSERT INTO map_gloss_word (word_id, gloss_id)
190 VALUES
191 (1, 1),
192 (2, 1)
193;
194
195INSERT INTO map_morpheme_word (morpheme_id, word_id)
196 VALUES
197 (1, 1),
198 (1, 3),
199 (2, 3)
200;
201
202INSERT INTO pronunciation (pronunciation_val, word_id, accent_id)
203 VALUES
204 ("bɹɛd", 1, 2),
205 ("bɹeːd", 1, 3)
206;
207
208SELECT
209 -- *
210 gloss_val.word_val AS gloss, word.word_val AS word, GROUP_CONCAT("(" || accent.abbreviation || ")" || ": " || pronunciation.pronunciation_val) AS pronunciation, language.language_val AS language, IIF(map.is_lemma, "True", "False") AS is_lemma, UPPER(lemma.word_val) AS lemma, ancestor.word_val AS ancestor
211 FROM map_word_language AS map
212 LEFT JOIN word ON word.word_id = map.word_id
213 LEFT JOIN language ON language.language_id = map.language_id
214 LEFT JOIN word AS lemma ON lemma.word_id = map.lemma_id
215 LEFT JOIN word AS ancestor ON ancestor.word_id = map.ancestor_id
216 LEFT JOIN map_gloss_word AS gloss ON gloss.word_id = map.map_id
217 LEFT JOIN word AS gloss_val ON gloss_val.word_id = gloss.gloss_id
218 LEFT JOIN pronunciation ON pronunciation.word_id = map.map_id
219 LEFT JOIN accent ON accent.accent_id = pronunciation.accent_id
220 GROUP BY map.map_id
221;
222
223SELECT
224 --*
225 UPPER(lemma.word_val) AS lemma, GROUP_CONCAT(word.word_val)
226 FROM map_word_language AS map
227 LEFT JOIN word ON word.word_id = map.word_id
228 LEFT JOIN language ON language.language_id = map.language_id
229 LEFT JOIN
230 (SELECT * FROM word
231 LEFT JOIN map_word_language AS map ON map.word_id = word.word_id
232 WHERE map.is_lemma = 1
233 ) AS lemma ON lemma.word_id = map.lemma_id
234 LEFT JOIN word AS ancestor ON ancestor.word_id = map.ancestor_id
235 GROUP BY map.language_id, map.lemma_id
236;
237
238SELECT
239 -- *
240 word.word_val AS word, GROUP_CONCAT(morpheme.morpheme_val || IFNULL(" '" || grammeme.grammeme_val || "'", ""), ', ') AS morphemes
241 FROM map_morpheme_word AS um
242 LEFT JOIN morpheme on morpheme.morpheme_id = um.morpheme_id
243 LEFT JOIN grammeme ON grammeme.grammeme_id = morpheme.grammeme_id
244 LEFT JOIN map_word_language AS word_lang ON word_lang.map_id = um.word_id
245 LEFT JOIN word ON word.word_id = word_lang.word_id
246 GROUP BY word_lang.map_id
247;