2 username text NOT NULL UNIQUE,
7 id integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
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 integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
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 integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
55 COMMENT ON COLUMN kind.truth IS 'link validity ranging from 0 (never) to 100 (always)';
57 CREATE OR REPLACE VIEW _word_ref AS
59 r.form, r.alt, r.lang,
60 coalesce(r.cat, w.cat ) cat, --TODO translate w?
61 coalesce(r.ref, r.id ) "ref",
62 coalesce(r.prio, w.prio ) prio,
63 coalesce(r.grade, w.grade ) grade,
64 coalesce(r.cover, w.cover ) cover,
65 coalesce(r.image, w.image ) image,
66 coalesce(r.wptitle, w.wptitle) wptitle,
67 coalesce(r.story, w.story ) story,
68 r.creator, r.created, r.updated,
69 CASE WHEN nullif(r.image, '{}') IS NOT NULL THEN r.id
70 WHEN nullif(w.image, '{}') IS NOT NULL THEN w.id END id -- image id
72 LEFT JOIN word w ON w.id = r.ref;
74 CREATE OR REPLACE VIEW _word_tree AS
76 SELECT w.ref, w.lang, w.cat, w.grade, w.form, NULL::int trans
79 SELECT r.ref, r.lang, w.cat,
80 CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN r.grade ELSE w.grade END,
81 CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN r.form ELSE w.form END,
82 CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN w.id END
83 FROM r JOIN word w ON w.id = r.cat
84 LEFT JOIN word t ON w.id = t.ref AND t.lang = r.lang
87 SELECT ref, lang, trans cat, grade, form FROM r WHERE trans IS NOT NULL
88 ORDER BY cat, grade, form, ref;
90 CREATE OR REPLACE VIEW _word AS
92 (SELECT array_agg(coalesce(ref, id)) FROM _word_tree
93 WHERE cat = w.ref AND lang = w.lang) sub,