initial public commit

1 files changed, 104 insertions, 0 deletions

diff --git a/db/dict/template.sql.m4 b/db/dict/template.sql.m4
new file mode 100644
index 0000000..00de413
--- /dev/null
+++ b/db/dict/template.sql.m4
@@ -0,0 +1,104 @@
+-- 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. 読み込む)
+	reading text not null, -- reading of term (e.g. よみこむ)
+	term_tags text not null default '', -- space-separated *term* tags, merged if term already exists in DB
+
+	-- definition fields
+	glossary_sort int not null default 1, -- order of multiple meanings (glossaries)
+	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
+);
+
+include(`/dev/stdin')dnl --'
+-- the apostrophe is so my editor highlighting keeps working if I force the
+-- filetype to sql instead of m4
+
+-- create dict id
+insert into dict (tag, language) values ('dict:' || :dict, :lang);
+
+-- add terms
+insert into term (expression, reading)
+select expression, reading
+from ingest;
+
+-- add definitions
+insert into definition (term_id, sort, glossary, dict_id)
+select
+	term.id,
+	ingest.glossary_sort,
+	ingest.glossary,
+	(select id from dict where tag = 'dict:' || :dict)
+from ingest
+join term on term.expression = ingest.expression and term.reading = ingest.reading;
+
+-- create map of term_id and tag code
+drop table if exists term_tag_map;
+create temporary table term_tag_map (term_id, tag);
+insert into term_tag_map
+with tag_map(term_id, temp, tag) as (
+	select
+		(select id from term where expression is ingest.expression and reading is ingest.reading),
+		term_tags || ' ',
+		''
+	from ingest
+	union
+	select
+		term_id,
+		`substr'(temp, instr(temp, ' ') + 1),
+		`substr'(temp, 0, instr(temp, ' '))
+	from tag_map
+	where length(temp) > 1
+)
+select term_id, replace(tag, ' ', '')
+from tag_map
+where length(tag) > 0;
+
+-- create map of definition_id and tag code
+drop table if exists definition_tag_map;
+create temporary table definition_tag_map (definition_id, tag);
+insert into definition_tag_map
+with tag_map(definition_id, temp, tag) as (
+	select
+		(select id from definition where glossary is ingest.glossary),
+		glossary_tags || ' ',
+		''
+	from ingest
+	union
+	select
+		definition_id,
+		`substr'(temp, instr(temp, ' ') + 1),
+		`substr'(temp, 0, instr(temp, ' '))
+	from tag_map
+	where length(temp) > 1
+)
+select definition_id, replace(tag, ' ', '')
+from tag_map
+where length(tag) > 0;
+
+-- make sure tags exist
+insert into tag (code)
+select tag from term_tag_map
+union
+select tag from definition_tag_map;
+
+-- add tags to terms
+insert into term_tag (term_id, tag_id)
+select
+	term_id,
+	tag.id
+from term_tag_map
+join tag on tag.code = term_tag_map.tag;
+
+-- add tags to definitions
+insert into definition_tag (definition_id, tag_id)
+select
+	definition_id,
+	tag.id
+from definition_tag_map
+join tag on tag.code = definition_tag_map.tag;
+