diff options
author | Kelly Rauchenberger <fefferburbia@gmail.com> | 2017-01-16 18:02:50 -0500 |
---|---|---|
committer | Kelly Rauchenberger <fefferburbia@gmail.com> | 2017-01-16 18:02:50 -0500 |
commit | 6746da6edd7d9d50efe374eabbb79a3cac882d81 (patch) | |
tree | ff20917e08b08d36b9541c1371106596e7bec442 /generator/schema.sql | |
parent | 4af7e55733098ca42f75a4ffaca1b0f6bab4dd36 (diff) | |
download | verbly-6746da6edd7d9d50efe374eabbb79a3cac882d81.tar.gz verbly-6746da6edd7d9d50efe374eabbb79a3cac882d81.tar.bz2 verbly-6746da6edd7d9d50efe374eabbb79a3cac882d81.zip |
Started structural rewrite
The new object structure was designed to build on the existing WordNet structure, while also adding in all of the data that we get from other sources. More information about this can be found on the project wiki. The generator has already been completely rewritten to generate a datafile that uses the new structure. In addition, a number of indexes are created, which does double the size of the datafile, but also allows for much faster lookups. Finally, the new generator is written modularly and is a lot more readable than the old one. The verbly interface to the new object structure has mostly been completed, but has not been tested fully. There is a completely new search API which utilizes a lot of operator overloading; documentation on how to use it should go up at some point. Token processing and verb frames are currently unimplemented. Source for these have been left in the repository for now.
Diffstat (limited to 'generator/schema.sql')
-rw-r--r-- | generator/schema.sql | 352 |
1 files changed, 135 insertions, 217 deletions
diff --git a/generator/schema.sql b/generator/schema.sql index 410b536..c3e54d8 100644 --- a/generator/schema.sql +++ b/generator/schema.sql | |||
@@ -1,286 +1,204 @@ | |||
1 | DROP TABLE IF EXISTS `verbs`; | 1 | CREATE TABLE `notions` ( |
2 | CREATE TABLE `verbs` ( | 2 | `notion_id` INTEGER PRIMARY KEY, |
3 | `verb_id` INTEGER PRIMARY KEY, | 3 | `part_of_speech` SMALLINT NOT NULL, |
4 | `infinitive` VARCHAR(32) NOT NULL, | 4 | `wnid` INTEGER, |
5 | `past_tense` VARCHAR(32) NOT NULL, | 5 | `images` INTEGER |
6 | `past_participle` VARCHAR(32) NOT NULL, | ||
7 | `ing_form` VARCHAR(32) NOT NULL, | ||
8 | `s_form` VARCHAR(32) NOT NULL | ||
9 | ); | 6 | ); |
10 | 7 | ||
11 | DROP TABLE IF EXISTS `groups`; | 8 | CREATE UNIQUE INDEX `notion_by_wnid` ON `notions`(`wnid`); |
12 | CREATE TABLE `groups` ( | ||
13 | `group_id` INTEGER PRIMARY KEY, | ||
14 | `data` BLOB NOT NULL | ||
15 | ); | ||
16 | |||
17 | DROP TABLE IF EXISTS `frames`; | ||
18 | CREATE TABLE `frames` ( | ||
19 | `frame_id` INTEGER PRIMARY KEY, | ||
20 | `group_id` INTEGER NOT NULL, | ||
21 | `data` BLOB NOT NULL, | ||
22 | FOREIGN KEY (`group_id`) REFERENCES `groups`(`group_id`) | ||
23 | ); | ||
24 | 9 | ||
25 | DROP TABLE IF EXISTS `verb_groups`; | ||
26 | CREATE TABLE `verb_groups` ( | ||
27 | `verb_id` INTEGER NOT NULL, | ||
28 | `group_id` INTEGER NOT NULL, | ||
29 | FOREIGN KEY (`verb_id`) REFERENCES `verbs`(`verb_id`), | ||
30 | FOREIGN KEY (`group_id`) REFERENCES `groups`(`group_id`) | ||
31 | ); | ||
32 | |||
33 | DROP TABLE IF EXISTS `adjectives`; | ||
34 | CREATE TABLE `adjectives` ( | ||
35 | `adjective_id` INTEGER PRIMARY KEY, | ||
36 | `base_form` VARCHAR(32) NOT NULL, | ||
37 | `comparative` VARCHAR(32), | ||
38 | `superlative` VARCHAR(32), | ||
39 | `position` CHAR(1), | ||
40 | `complexity` INTEGER NOT NULL | ||
41 | ); | ||
42 | |||
43 | DROP TABLE IF EXISTS `adverbs`; | ||
44 | CREATE TABLE `adverbs` ( | ||
45 | `adverb_id` INTEGER PRIMARY KEY, | ||
46 | `base_form` VARCHAR(32) NOT NULL, | ||
47 | `comparative` VARCHAR(32), | ||
48 | `superlative` VARCHAR(32), | ||
49 | `complexity` INTEGER NOT NULL | ||
50 | ); | ||
51 | |||
52 | DROP TABLE IF EXISTS `nouns`; | ||
53 | CREATE TABLE `nouns` ( | ||
54 | `noun_id` INTEGER PRIMARY KEY, | ||
55 | `singular` VARCHAR(32) NOT NULL, | ||
56 | `plural` VARCHAR(32), | ||
57 | `proper` INTEGER(1) NOT NULL, | ||
58 | `complexity` INTEGER NOT NULL, | ||
59 | `images` INTEGER NOT NULL, | ||
60 | `wnid` INTEGER NOT NULL | ||
61 | ); | ||
62 | |||
63 | DROP TABLE IF EXISTS `hypernymy`; | ||
64 | CREATE TABLE `hypernymy` ( | 10 | CREATE TABLE `hypernymy` ( |
65 | `hypernym_id` INTEGER NOT NULL, | 11 | `hypernym_id` INTEGER NOT NULL, |
66 | `hyponym_id` INTEGER NOT NULL, | 12 | `hyponym_id` INTEGER NOT NULL |
67 | FOREIGN KEY (`hypernym_id`) REFERENCES `nouns`(`noun_id`), | ||
68 | FOREIGN KEY (`hyponym_id`) REFERENCES `nouns`(`noun_id`) | ||
69 | ); | 13 | ); |
70 | 14 | ||
71 | DROP TABLE IF EXISTS `instantiation`; | 15 | CREATE INDEX `hyponym_of` ON `hypernymy`(`hypernym_id`); |
16 | CREATE INDEX `hypernym_of` ON `hypernymy`(`hyponym_id`); | ||
17 | |||
72 | CREATE TABLE `instantiation` ( | 18 | CREATE TABLE `instantiation` ( |
73 | `class_id` INTEGER NOT NULL, | 19 | `class_id` INTEGER NOT NULL, |
74 | `instance_id` INTEGER NOT NULL, | 20 | `instance_id` INTEGER NOT NULL |
75 | FOREIGN KEY (`class_id`) REFERENCES `nouns`(`noun_id`), | ||
76 | FOREIGN KEY (`instance_id`) REFERENCES `nouns`(`noun_id`) | ||
77 | ); | 21 | ); |
78 | 22 | ||
79 | DROP TABLE IF EXISTS `member_meronymy`; | 23 | CREATE INDEX `instance_of` ON `instantiation`(`class_id`); |
24 | CREATE INDEX `class_of` ON `instantiation`(`instance_id`); | ||
25 | |||
80 | CREATE TABLE `member_meronymy` ( | 26 | CREATE TABLE `member_meronymy` ( |
81 | `meronym_id` INTEGER NOT NULL, | 27 | `meronym_id` INTEGER NOT NULL, |
82 | `holonym_id` INTEGER NOT NULL, | 28 | `holonym_id` INTEGER NOT NULL |
83 | FOREIGN KEY (`meronym_id`) REFERENCES `nouns`(`noun_id`), | ||
84 | FOREIGN KEY (`holonym_id`) REFERENCES `nouns`(`noun_id`) | ||
85 | ); | 29 | ); |
86 | 30 | ||
87 | DROP TABLE IF EXISTS `part_meronymy`; | 31 | CREATE INDEX `member_holonym_of` ON `member_meronymy`(`meronym_id`); |
32 | CREATE INDEX `member_meronym_of` ON `member_meronymy`(`holonym_id`); | ||
33 | |||
88 | CREATE TABLE `part_meronymy` ( | 34 | CREATE TABLE `part_meronymy` ( |
89 | `meronym_id` INTEGER NOT NULL, | 35 | `meronym_id` INTEGER NOT NULL, |
90 | `holonym_id` INTEGER NOT NULL, | 36 | `holonym_id` INTEGER NOT NULL |
91 | FOREIGN KEY (`meronym_id`) REFERENCES `nouns`(`noun_id`), | ||
92 | FOREIGN KEY (`holonym_id`) REFERENCES `nouns`(`noun_id`) | ||
93 | ); | 37 | ); |
94 | 38 | ||
95 | DROP TABLE IF EXISTS `substance_meronymy`; | 39 | CREATE INDEX `part_holonym_of` ON `part_meronymy`(`meronym_id`); |
40 | CREATE INDEX `part_meronym_of` ON `part_meronymy`(`holonym_id`); | ||
41 | |||
96 | CREATE TABLE `substance_meronymy` ( | 42 | CREATE TABLE `substance_meronymy` ( |
97 | `meronym_id` INTEGER NOT NULL, | 43 | `meronym_id` INTEGER NOT NULL, |
98 | `holonym_id` INTEGER NOT NULL, | 44 | `holonym_id` INTEGER NOT NULL |
99 | FOREIGN KEY (`meronym_id`) REFERENCES `nouns`(`noun_id`), | ||
100 | FOREIGN KEY (`holonym_id`) REFERENCES `nouns`(`noun_id`) | ||
101 | ); | 45 | ); |
102 | 46 | ||
103 | DROP TABLE IF EXISTS `variation`; | 47 | CREATE INDEX `substance_holonym_of` ON `substance_meronymy`(`meronym_id`); |
48 | CREATE INDEX `substance_meronym_of` ON `substance_meronymy`(`holonym_id`); | ||
49 | |||
104 | CREATE TABLE `variation` ( | 50 | CREATE TABLE `variation` ( |
105 | `noun_id` INTEGER NOT NULL, | 51 | `noun_id` INTEGER NOT NULL, |
106 | `adjective_id` INTEGER NOT NULL, | 52 | `adjective_id` INTEGER NOT NULL |
107 | FOREIGN KEY (`noun_id`) REFERENCES `nouns`(`noun_id`), | ||
108 | FOREIGN KEY (`adjective_id`) REFERENCES `adjectives`(`adjective_id`) | ||
109 | ); | 53 | ); |
110 | 54 | ||
111 | DROP TABLE IF EXISTS `noun_antonymy`; | 55 | CREATE INDEX `variant_of` ON `variation`(`noun_id`); |
112 | CREATE TABLE `noun_antonymy` ( | 56 | CREATE INDEX `attribute_of` ON `variation`(`adjective_id`); |
113 | `noun_1_id` INTEGER NOT NULL, | ||
114 | `noun_2_id` INTEGER NOT NULL, | ||
115 | FOREIGN KEY (`noun_1_id`) REFERENCES `nouns`(`noun_id`), | ||
116 | FOREIGN KEY (`noun_2_id`) REFERENCES `nouns`(`noun_id`) | ||
117 | ); | ||
118 | 57 | ||
119 | DROP TABLE IF EXISTS `adjective_antonymy`; | 58 | CREATE TABLE `similarity` ( |
120 | CREATE TABLE `adjective_antonymy` ( | ||
121 | `adjective_1_id` INTEGER NOT NULL, | 59 | `adjective_1_id` INTEGER NOT NULL, |
122 | `adjective_2_id` INTEGER NOT NULL, | 60 | `adjective_2_id` INTEGER NOT NULL |
123 | FOREIGN KEY (`adjective_1_id`) REFERENCES `adjectives`(`adjective_id`), | 61 | ); |
124 | FOREIGN KEY (`adjective_2_id`) REFERENCES `adjectives`(`adjective_id`) | 62 | |
63 | CREATE INDEX `similar_to` ON `similarity`(`adjective_1_id`); | ||
64 | |||
65 | CREATE TABLE `is_a` ( | ||
66 | `notion_id` INTEGER NOT NULL, | ||
67 | `groupname` VARCHAR(32) NOT NULL | ||
125 | ); | 68 | ); |
126 | 69 | ||
127 | DROP TABLE IF EXISTS `adverb_antonymy`; | 70 | CREATE TABLE `entailment` ( |
128 | CREATE TABLE `adverb_antonymy` ( | 71 | `given_id` INTEGER NOT NULL, |
129 | `adverb_1_id` INTEGER NOT NULL, | 72 | `entailment_id` INTEGER NOT NULL |
130 | `adverb_2_id` INTEGER NOT NULL, | 73 | ); |
131 | FOREIGN KEY (`adverb_1_id`) REFERENCES `adverbs`(`adverb_id`), | 74 | |
132 | FOREIGN KEY (`adverb_2_id`) REFERENCES `adverbs`(`adverb_id`) | 75 | CREATE INDEX `entailment_of` ON `entailment`(`given_id`); |
76 | CREATE INDEX `entailed_by` ON `entailment`(`entailment_id`); | ||
77 | |||
78 | CREATE TABLE `causality` ( | ||
79 | `cause_id` INTEGER NOT NULL, | ||
80 | `effect_id` INTEGER NOT NULL | ||
81 | ); | ||
82 | |||
83 | CREATE INDEX `effect_of` ON `causality`(`cause_id`); | ||
84 | CREATE INDEX `cause_of` ON `causality`(`effect_id`); | ||
85 | |||
86 | CREATE TABLE `words` ( | ||
87 | `word_id` INTEGER PRIMARY KEY, | ||
88 | `notion_id` INTEGER NOT NULL, | ||
89 | `lemma_id` INTEGER NOT NULL, | ||
90 | `tag_count` INTEGER, | ||
91 | `position` SMALLINT, | ||
92 | `group_id` INTEGER | ||
93 | ); | ||
94 | |||
95 | CREATE INDEX `notion_words` ON `words`(`notion_id`); | ||
96 | CREATE INDEX `lemma_words` ON `words`(`lemma_id`); | ||
97 | CREATE INDEX `group_words` ON `words`(`group_id`); | ||
98 | |||
99 | CREATE TABLE `antonymy` ( | ||
100 | `antonym_1_id` INTEGER NOT NULL, | ||
101 | `antonym_2_id` INTEGER NOT NULL | ||
133 | ); | 102 | ); |
134 | 103 | ||
135 | DROP TABLE IF EXISTS `specification`; | 104 | CREATE INDEX `antonym_of` ON `antonymy`(`antonym_1_id`); |
105 | |||
136 | CREATE TABLE `specification` ( | 106 | CREATE TABLE `specification` ( |
137 | `general_id` INTEGER NOT NULL, | 107 | `general_id` INTEGER NOT NULL, |
138 | `specific_id` INTEGER NOT NULL, | 108 | `specific_id` INTEGER NOT NULL |
139 | FOREIGN KEY (`general_id`) REFERENCES `adjectives`(`adjective_id`), | ||
140 | FOREIGN KEY (`specific_id`) REFERENCES `adjectives`(`adjective_id`) | ||
141 | ); | 109 | ); |
142 | 110 | ||
143 | DROP TABLE IF EXISTS `pertainymy`; | 111 | CREATE INDEX `specification_of` ON `specification`(`general_id`); |
112 | CREATE INDEX `generalization_of` ON `specification`(`specific_id`); | ||
113 | |||
144 | CREATE TABLE `pertainymy` ( | 114 | CREATE TABLE `pertainymy` ( |
145 | `noun_id` INTEGER NOT NULL, | 115 | `noun_id` INTEGER NOT NULL, |
146 | `pertainym_id` INTEGER NOT NULL, | 116 | `pertainym_id` INTEGER NOT NULL |
147 | FOREIGN KEY (`noun_id`) REFERENCES `nouns`(`noun_id`), | ||
148 | FOREIGN KEY (`pertainym_id`) REFERENCES `adjectives`(`adjective_id`) | ||
149 | ); | 117 | ); |
150 | 118 | ||
151 | DROP TABLE IF EXISTS `mannernymy`; | 119 | CREATE INDEX `pertainym_of` ON `pertainymy`(`noun_id`); |
120 | CREATE INDEX `anti_pertainym_of` ON `pertainymy`(`pertainym_id`); | ||
121 | |||
152 | CREATE TABLE `mannernymy` ( | 122 | CREATE TABLE `mannernymy` ( |
153 | `adjective_id` INTEGER NOT NULL, | 123 | `adjective_id` INTEGER NOT NULL, |
154 | `mannernym_id` INTEGER NOT NULL, | 124 | `mannernym_id` INTEGER NOT NULL |
155 | FOREIGN KEY (`adjective_id`) REFERENCES `adjectives`(`adjective_id`), | ||
156 | FOREIGN KEY (`mannernym_id`) REFERENCES `adverbs`(`adverb_id`) | ||
157 | ); | 125 | ); |
158 | 126 | ||
159 | DROP TABLE IF EXISTS `noun_synonymy`; | 127 | CREATE INDEX `mannernym_of` ON `mannernymy`(`adjective_id`); |
160 | CREATE TABLE `noun_synonymy` ( | 128 | CREATE INDEX `anti_mannernym_of` ON `mannernymy`(`mannernym_id`); |
161 | `noun_1_id` INTEGER NOT NULL, | ||
162 | `noun_2_id` INTEGER NOT NULL, | ||
163 | FOREIGN KEY (`noun_1_id`) REFERENCES `nouns`(`nouns_id`), | ||
164 | FOREIGN KEY (`noun_2_id`) REFERENCES `nouns`(`nouns_id`) | ||
165 | ); | ||
166 | 129 | ||
167 | DROP TABLE IF EXISTS `adjective_synonymy`; | 130 | CREATE TABLE `usage` ( |
168 | CREATE TABLE `adjective_synonymy` ( | 131 | `domain_id` INTEGER NOT NULL, |
169 | `adjective_1_id` INTEGER NOT NULL, | 132 | `term_id` INTEGER NOT NULL |
170 | `adjective_2_id` INTEGER NOT NULL, | ||
171 | FOREIGN KEY (`adjective_1_id`) REFERENCES `adjectives`(`adjective_id`), | ||
172 | FOREIGN KEY (`adjective_2_id`) REFERENCES `adjectives`(`adjective_id`) | ||
173 | ); | 133 | ); |
174 | 134 | ||
175 | DROP TABLE IF EXISTS `adverb_synonymy`; | 135 | CREATE INDEX `usage_term_of` ON `usage`(`domain_id`); |
176 | CREATE TABLE `adverb_synonymy` ( | 136 | CREATE INDEX `usage_domain_of` ON `usage`(`term_id`); |
177 | `adverb_1_id` INTEGER NOT NULL, | ||
178 | `adverb_2_id` INTEGER NOT NULL, | ||
179 | FOREIGN KEY (`adverb_1_id`) REFERENCES `adverbs`(`adverb_id`), | ||
180 | FOREIGN KEY (`adverb_2_id`) REFERENCES `adverbs`(`adverb_id`) | ||
181 | ); | ||
182 | 137 | ||
183 | DROP TABLE IF EXISTS `noun_pronunciations`; | 138 | CREATE TABLE `topicality` ( |
184 | CREATE TABLE `noun_pronunciations` ( | 139 | `domain_id` INTEGER NOT NULL, |
185 | `noun_id` INTEGER NOT NULL, | 140 | `term_id` INTEGER NOT NULL |
186 | `pronunciation` VARCHAR(64) NOT NULL, | ||
187 | `prerhyme` VARCHAR(8), | ||
188 | `rhyme` VARCHAR(64), | ||
189 | `syllables` INT NOT NULL, | ||
190 | `stress` VARCHAR(64) NOT NULL, | ||
191 | FOREIGN KEY (`noun_id`) REFERENCES `nouns`(`noun_id`) | ||
192 | ); | 141 | ); |
193 | 142 | ||
194 | DROP TABLE IF EXISTS `verb_pronunciations`; | 143 | CREATE INDEX `topical_term_of` ON `topicality`(`domain_id`); |
195 | CREATE TABLE `verb_pronunciations` ( | 144 | CREATE INDEX `topical_domain_of` ON `topicality`(`term_id`); |
196 | `verb_id` INTEGER NOT NULL, | ||
197 | `pronunciation` VARCHAR(64) NOT NULL, | ||
198 | `prerhyme` VARCHAR(8), | ||
199 | `rhyme` VARCHAR(64), | ||
200 | `syllables` INT NOT NULL, | ||
201 | `stress` VARCHAR(64) NOT NULL, | ||
202 | FOREIGN KEY (`verb_id`) REFERENCES `verbs`(`verb_id`) | ||
203 | ); | ||
204 | 145 | ||
205 | DROP TABLE IF EXISTS `adjective_pronunciations`; | 146 | CREATE TABLE `regionality` ( |
206 | CREATE TABLE `adjective_pronunciations` ( | 147 | `domain_id` INTEGER NOT NULL, |
207 | `adjective_id` INTEGER NOT NULL, | 148 | `term_id` INTEGER NOT NULL |
208 | `pronunciation` VARCHAR(64) NOT NULL, | ||
209 | `prerhyme` VARCHAR(8), | ||
210 | `rhyme` VARCHAR(64), | ||
211 | `syllables` INT NOT NULL, | ||
212 | `stress` VARCHAR(64) NOT NULL, | ||
213 | FOREIGN KEY (`adjective_id`) REFERENCES `adjectives`(`adjective_id`) | ||
214 | ); | 149 | ); |
215 | 150 | ||
216 | DROP TABLE IF EXISTS `adverb_pronunciations`; | 151 | CREATE INDEX `regional_term_of` ON `regionality`(`domain_id`); |
217 | CREATE TABLE `adverb_pronunciations` ( | 152 | CREATE INDEX `regional_domain_of` ON `regionality`(`term_id`); |
218 | `adverb_id` INTEGER NOT NULL, | ||
219 | `pronunciation` VARCHAR(64) NOT NULL, | ||
220 | `prerhyme` VARCHAR(8), | ||
221 | `rhyme` VARCHAR(64), | ||
222 | `syllables` INT NOT NULL, | ||
223 | `stress` VARCHAR(64) NOT NULL, | ||
224 | FOREIGN KEY (`adverb_id`) REFERENCES `adverbs`(`adverb_id`) | ||
225 | ); | ||
226 | 153 | ||
227 | DROP TABLE IF EXISTS `noun_noun_derivation`; | 154 | CREATE TABLE `forms` ( |
228 | CREATE TABLE `noun_noun_derivation` ( | 155 | `form_id` INTEGER PRIMARY KEY, |
229 | `noun_1_id` INTEGER NOT NULL, | 156 | `form` VARCHAR(32) NOT NULL, |
230 | `noun_2_id` INTEGER NOT NULL, | 157 | `complexity` SMALLINT NOT NULL, |
231 | FOREIGN KEY (`noun_1_id`) REFERENCES `nouns`(`noun_id`), | 158 | `proper` SMALLINT NOT NULL |
232 | FOREIGN KEY (`noun_2_id`) REFERENCES `nouns`(`noun_id`) | ||
233 | ); | 159 | ); |
234 | 160 | ||
235 | DROP TABLE IF EXISTS `noun_adjective_derivation`; | 161 | CREATE UNIQUE INDEX `form_by_string` ON `forms`(`form`); |
236 | CREATE TABLE `noun_adjective_derivation` ( | ||
237 | `noun_id` INTEGER NOT NULL, | ||
238 | `adjective_id` INTEGER NOT NULL, | ||
239 | FOREIGN KEY (`noun_id`) REFERENCES `nouns`(`noun_id`), | ||
240 | FOREIGN KEY (`adjective_id`) REFERENCES `adjectives`(`adjective_id`) | ||
241 | ); | ||
242 | 162 | ||
243 | DROP TABLE IF EXISTS `noun_adverb_derivation`; | 163 | CREATE TABLE `lemmas_forms` ( |
244 | CREATE TABLE `noun_adverb_derivation` ( | 164 | `lemma_id` INTEGER NOT NULL, |
245 | `noun_id` INTEGER NOT NULL, | 165 | `form_id` INTEGER NOT NULL, |
246 | `adverb_id` INTEGER NOT NULL, | 166 | `category` SMALLINT NOT NULL |
247 | FOREIGN KEY (`noun_id`) REFERENCES `nouns`(`noun_id`), | ||
248 | FOREIGN KEY (`adverb_id`) REFERENCES `adverbs`(`adverb_id`) | ||
249 | ); | 167 | ); |
250 | 168 | ||
251 | DROP TABLE IF EXISTS `adjective_adjective_derivation`; | 169 | CREATE INDEX `form_of` ON `lemmas_forms`(`lemma_id`); |
252 | CREATE TABLE `adjective_adjective_derivation` ( | 170 | CREATE INDEX `lemma_of` ON `lemmas_forms`(`form_id`); |
253 | `adjective_1_id` INTEGER NOT NULL, | 171 | |
254 | `adjective_2_id` INTEGER NOT NULL, | 172 | CREATE TABLE `pronunciations` ( |
255 | FOREIGN KEY (`adjective_1_id`) REFERENCES `adjectives`(`adjective_id`), | 173 | `pronunciation_id` INTEGER PRIMARY KEY, |
256 | FOREIGN KEY (`adjective_2_id`) REFERENCES `adjectives`(`adjective_id`) | 174 | `phonemes` VARCHAR(64) NOT NULL, |
175 | `prerhyme` VARCHAR(8), | ||
176 | `rhyme` VARCHAR(64), | ||
177 | `syllables` INTEGER NOT NULL, | ||
178 | `stress` VARCHAR(64) NOT NULL | ||
257 | ); | 179 | ); |
258 | 180 | ||
259 | DROP TABLE IF EXISTS `adjective_adverb_derivation`; | 181 | CREATE TABLE `forms_pronunciations` ( |
260 | CREATE TABLE `adjective_adverb_derivation` ( | 182 | `form_id` INTEGER NOT NULL, |
261 | `adjective_id` INTEGER NOT NULL, | 183 | `pronunciation_id` INTEGER NOT NULL |
262 | `adverb_id` INTEGER NOT NULL, | ||
263 | FOREIGN KEY (`adjective_id`) REFERENCES `adjectives`(`adjective_id`), | ||
264 | FOREIGN KEY (`adverb_id`) REFERENCES `adverbs`(`adjective_id`) | ||
265 | ); | 184 | ); |
266 | 185 | ||
267 | DROP TABLE IF EXISTS `adverb_adverb_derivation`; | 186 | CREATE INDEX `pronunciation_of` ON `forms_pronunciations`(`form_id`); |
268 | CREATE TABLE `adverb_adverb_derivation` ( | 187 | CREATE INDEX `spelling_of` ON `forms_pronunciations`(`pronunciation_id`); |
269 | `adverb_1_id` INTEGER NOT NULL, | 188 | |
270 | `adverb_2_id` INTEGER NOT NULL, | 189 | CREATE TABLE `groups` ( |
271 | FOREIGN KEY (`adverb_1_id`) REFERENCES `adverbs`(`adverb_id`), | 190 | `group_id` INTEGER PRIMARY KEY, |
272 | FOREIGN KEY (`adverb_2_id`) REFERENCES `adverbs`(`adverb_id`) | 191 | `data` BLOB NOT NULL |
273 | ); | 192 | ); |
274 | 193 | ||
275 | DROP TABLE IF EXISTS `prepositions`; | 194 | CREATE TABLE `frames` ( |
276 | CREATE TABLE `prepositions` ( | 195 | `frame_id` INTEGER PRIMARY KEY, |
277 | `preposition_id` INTEGER PRIMARY KEY, | 196 | `data` BLOB NOT NULL |
278 | `form` VARCHAR(32) NOT NULL | ||
279 | ); | 197 | ); |
280 | 198 | ||
281 | DROP TABLE IF EXISTS `preposition_groups`; | 199 | CREATE TABLE `groups_frames` ( |
282 | CREATE TABLE `preposition_groups` ( | 200 | `group_id` INTEGER NOT NULL, |
283 | `preposition_id` INTEGER NOT NULL, | 201 | `frame_id` INTEGER NOT NULL |
284 | `groupname` VARCHAR(32) NOT NULL, | ||
285 | FOREIGN KEY (`preposition_id`) REFERENCES `prepositions`(`preposition_id`) | ||
286 | ); | 202 | ); |
203 | |||
204 | CREATE INDEX `frames_in` ON `groups_frames`(`group_id`); | ||