diff options
Diffstat (limited to 'db/dict/template.sql')
| -rw-r--r-- | db/dict/template.sql | 26 |
1 files changed, 24 insertions, 2 deletions
diff --git a/db/dict/template.sql b/db/dict/template.sql index 1a07252..6b17a0c 100644 --- a/db/dict/template.sql +++ b/db/dict/template.sql @@ -1,7 +1,5 @@ -- create temporary ingest table drop table if exists ingest; --- TODO: ingest pitch-accent dictionaries --- TODO: ingest alternate writings (space-separated) create temporary table ingest( -- term fields expression text not null, -- kanji of term (e.g. 読み込む) @@ -13,6 +11,16 @@ create temporary table ingest( glossary text null default null, -- glossary content (support for basic HTML markup/styling) glossary_tags text null default null -- add tags to single glossary entry ); +-- TODO: ingest pitch-accent dictionaries + +-- create temporary alternate readings table +drop table if exists alts; +create temporary table alts( + expression text not null, -- kanji of alternate version + reading text not null, -- reading of alternate version + normal_expression text not null, -- kanji of parent (original) + normal_reading text not null -- reading of parent (original) +); -- #DICTIONARY_CONTENT_BEGIN -- this template is 'rendered' by pasting a .dict.sql file in between these @@ -35,6 +43,11 @@ insert into term (expression, reading) select expression, reading from ingest; +-- add alternates +insert into term (expression, reading, alt) +select expression, reading, (select id from term where expression = normal_expression and reading = normal_reading) +from alts; + -- add definitions insert into definition (term_id, sort, glossary, dict_id) select @@ -103,6 +116,15 @@ select from term_tag_map join tag on tag.code = term_tag_map.tag; +-- add tags to alternates +insert into term_tag (term_id, tag_id) +select term_alt.id, term_tag.tag_id +from alts +inner join term as term_normal on term_normal.expression = alts.normal_expression and term_normal.reading = alts.normal_reading +inner join term as term_alt on term_alt.expression = alts.expression and term_alt.reading = alts.reading +left join term_tag on term_tag.term_id = term_normal.id +where term_tag.tag_id is not null; + -- add tags to definitions insert into definition_tag (definition_id, tag_id) select |