2 username text NOT NULL UNIQUE,
7 id serial NOT NULL PRIMARY KEY
13 lang text DEFAULT 'en',
14 cat integer REFERENCES word (id),
15 ref integer REFERENCES word (id),
16 prio smallint DEFAULT '1'
17 CHECK (prio >= 0 OR ref IS NOT NULL),
19 cover boolean NOT NULL DEFAULT FALSE,
20 image jsonb CHECK (image->>'source' ~ '^https?://'
21 AND jsonb_typeof(image->'convert') = 'array'),
24 creator integer REFERENCES login (id),
25 created timestamptz DEFAULT now(),
27 id serial NOT NULL PRIMARY KEY
30 COMMENT ON COLUMN word.form IS 'preferred textual representation';
31 COMMENT ON COLUMN word.alt IS 'alternate forms with equivalent meaning';
32 COMMENT ON COLUMN word.lang IS 'ISO 639 language code matching wikipedia subdomain';
33 COMMENT ON COLUMN word.cat IS 'primary hierarchical classification';
34 COMMENT ON COLUMN word.ref IS 'reference to equivalent en translation';
35 COMMENT ON COLUMN word.prio IS 'difficulty level or importance; lower values have precedence';
36 COMMENT ON COLUMN word.grade IS 'ascending hierarchical order, preceding default alphabetical';
37 COMMENT ON COLUMN word.cover IS 'highlight if selected';
38 COMMENT ON COLUMN word.image IS 'metadata of illustrations, including downloaded URI and ImageMagick convert options';
39 COMMENT ON COLUMN word.wptitle IS 'reference Wikipedia article';
40 COMMENT ON COLUMN word.story IS 'paragraph defining or describing the entity, wikipedia intro';
41 COMMENT ON COLUMN word.updated IS 'last significant change';
42 COMMENT ON COLUMN word.creator IS 'user responsible for initial submit';
45 word integer NOT NULL REFERENCES word (id),
46 cat integer NOT NULL REFERENCES word (id),
48 truth smallint NOT NULL DEFAULT '50',
49 creator integer REFERENCES login (id),
50 created timestamptz DEFAULT now(),
52 id serial NOT NULL PRIMARY KEY
55 COMMENT ON COLUMN kind.truth IS 'link validity ranging from 0 (never) to 100 (always)';
57 CREATE OR REPLACE FUNCTION exportform(word) RETURNS text AS $$
59 coalesce($1.ref, $1.id), ':',
60 $1.prio, CASE WHEN $1.cover THEN 'c' ELSE '' END, ':',
61 array_to_string($1.form || $1.alt, '/')
63 $$ LANGUAGE SQL IMMUTABLE;
65 CREATE OR REPLACE VIEW _word_ref AS
67 r.form, r.alt, r.lang,
68 coalesce(r.cat, w.cat ) cat, --TODO translate w?
69 coalesce(r.ref, r.id ) "ref",
70 coalesce(r.prio, w.prio ) prio,
71 coalesce(r.grade, w.grade ) grade,
72 coalesce(r.cover, w.cover ) cover,
73 coalesce(r.image, w.image ) image,
74 coalesce(r.wptitle, w.wptitle) wptitle,
75 coalesce(r.story, w.story ) story,
76 r.creator, r.created, r.updated,
77 CASE WHEN r.image IS NULL THEN w.id ELSE r.id END id -- image id
79 LEFT JOIN word w ON w.id = r.ref;
81 CREATE OR REPLACE VIEW _cat_words AS
82 SELECT exportform(word.*) form, sub.*, word.lang, word.ref
83 FROM word RIGHT JOIN (
84 SELECT cat id, array_agg(exportform(word.*) ORDER BY grade, form) forms
85 FROM word WHERE ref IS NULL GROUP BY cat