diff options
Diffstat (limited to 'postprocess.sql')
| -rw-r--r-- | postprocess.sql | 78 |
1 files changed, 78 insertions, 0 deletions
diff --git a/postprocess.sql b/postprocess.sql new file mode 100644 index 0000000..5976cb1 --- /dev/null +++ b/postprocess.sql @@ -0,0 +1,78 @@ +-- Drop unused tables, hopefully saving a few megabytes. +-- `expressions_words` is for excercises??? +DROP TABLE expressions_words; +-- `categories_words2` references `categories` but is not in the database!? +DROP TABLE categories_words2; + +-- This table is especially added to aid lookups and autocompletions on all +-- possible inflections. +-- This is significantly faster than using the existing tables. +-- +-- FIXME: Some of the columns contain multiple comma-separated values +-- (sometimes in braces), so we probably need a Lua script for initializion. +-- TODO: Translations should probably be in this table, or a separate one as well. +-- But see above. +CREATE TABLE bare_inflections (`word_id` INTEGER NOT NULL, `bare` VARCHAR(100) NOT NULL COLLATE BINARY); +-- Indexing the `bare` column allows ultra-fast lookups even via GLOB +-- in most common cases. For this optimization to work, the column must +-- also be BINARY collated. See also: +-- https://www.sqlite.org/optoverview.html#the_like_optimization +CREATE INDEX idx_bare_inflections_bare ON bare_inflections (`bare`); +INSERT INTO bare_inflections SELECT word_id, REPLACE(temp, "'", "") AS bare +FROM ( + -- Search word might be a noun or adjective declension + SELECT word_id, nom AS temp FROM declensions + UNION ALL + SELECT word_id, gen AS temp FROM declensions + UNION ALL + SELECT word_id, dat AS temp FROM declensions + UNION ALL + SELECT word_id, acc AS temp FROM declensions + UNION ALL + SELECT word_id, inst AS temp FROM declensions + UNION ALL + SELECT word_id, prep AS temp FROM declensions + UNION ALL + -- Search word might be a special adjective inflection + SELECT word_id, comparative AS temp FROM adjectives + UNION ALL + SELECT word_id, superlative AS temp FROM adjectives + UNION ALL + SELECT word_id, short_m AS temp FROM adjectives + UNION ALL + SELECT word_id, short_f AS temp FROM adjectives + UNION ALL + SELECT word_id, short_n AS temp FROM adjectives + UNION ALL + SELECT word_id, short_pl AS temp FROM adjectives + UNION ALL + -- Search word might be a verb imperative or past form + SELECT word_id, imperative_sg AS temp FROM verbs + UNION ALL + SELECT word_id, imperative_pl AS temp FROM verbs + UNION ALL + SELECT word_id, past_m AS temp FROM verbs + UNION ALL + SELECT word_id, past_f AS temp FROM verbs + UNION ALL + SELECT word_id, past_n AS temp FROM verbs + UNION ALL + SELECT word_id, past_pl AS temp FROM verbs + UNION ALL + -- Search word might be a verb conjugation + SELECT word_id, sg1 AS temp FROM conjugations + UNION ALL + SELECT word_id, sg2 AS temp FROM conjugations + UNION ALL + SELECT word_id, sg3 AS temp FROM conjugations + UNION ALL + SELECT word_id, pl1 AS temp FROM conjugations + UNION ALL + SELECT word_id, pl2 AS temp FROM conjugations + UNION ALL + SELECT word_id, pl3 AS temp FROM conjugations +) +WHERE bare <> ""; + +-- Saves a few megabytes +VACUUM; |