From: Mischa POSLAWSKY Date: Wed, 25 Aug 2021 21:22:13 +0000 (+0200) Subject: word: kind table to link optional categories X-Git-Tag: v1.13~155 X-Git-Url: http://git.shiar.net/sheet.git/commitdiff_plain/8c855abc140a52d23fed749e936733d3d1eee4f8 word: kind table to link optional categories Allow for some kind of 20 Questions and other tagging. The hard part will be filling and utilising it properly. --- diff --git a/tools/word.pg.sql b/tools/word.pg.sql index ec552c9..eabf17d 100644 --- a/tools/word.pg.sql +++ b/tools/word.pg.sql @@ -30,7 +30,7 @@ CREATE TABLE word ( COMMENT ON COLUMN word.form IS 'preferred textual representation'; COMMENT ON COLUMN word.alt IS 'alternate forms with equivalent meaning'; COMMENT ON COLUMN word.lang IS 'ISO 639 language code matching wikipedia subdomain'; -COMMENT ON COLUMN word.cat IS 'hierarchical classification'; +COMMENT ON COLUMN word.cat IS 'primary hierarchical classification'; COMMENT ON COLUMN word.ref IS 'reference to equivalent en translation'; COMMENT ON COLUMN word.prio IS 'difficulty level or importance; lower values have precedence'; COMMENT ON COLUMN word.grade IS 'ascending hierarchical order, preceding default alphabetical'; @@ -42,6 +42,19 @@ COMMENT ON COLUMN word.story IS 'paragraph defining or describing the entit COMMENT ON COLUMN word.updated IS 'last significant change'; COMMENT ON COLUMN word.creator IS 'user responsible for initial submit'; +CREATE TABLE kind ( + word integer NOT NULL REFERENCES word (id), + cat integer NOT NULL REFERENCES word (id), + UNIQUE (word, cat), + truth smallint NOT NULL DEFAULT '50', + creator integer REFERENCES login (id), + created timestamptz DEFAULT now(), + updated timestamptz, + id serial NOT NULL PRIMARY KEY +); + +COMMENT ON COLUMN kind.truth IS 'link validity ranging from 0 (never) to 100 (always)'; + CREATE OR REPLACE FUNCTION exportform(word) RETURNS text AS $$ SELECT concat( $1.id, ':',