C3Cz8nGf

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