tools/word.pg.sql

   1 CREATE TABLE login (
   2         username   text        NOT NULL UNIQUE,
   3         pass       text,
   4         email      text,
   5         fullname   text,
   6         editlang   text[],
   7         id         integer     NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
   8 );
   9
  10 CREATE TABLE word (
  11         form       text,
  12         alt        text[],
  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),
  18         grade      integer,
  19         cover      boolean     NOT NULL DEFAULT FALSE,
  20         image      jsonb                CHECK (image->>'source' ~ '^https?://'
  21                                            AND jsonb_typeof(image->'convert') = 'array'),
  22         wptitle    text,
  23         story      text,
  24         creator    integer              REFERENCES login (id),
  25         created    timestamptz          DEFAULT now(),
  26         updated    timestamptz,
  27         id         integer     NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
  28 );
  29
  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';
  43
  44 CREATE TABLE kind (
  45         word       integer     NOT NULL REFERENCES word (id),
  46         cat        integer     NOT NULL REFERENCES word (id),
  47                                         UNIQUE (word, cat),
  48         truth      smallint    NOT NULL DEFAULT '50',
  49         creator    integer              REFERENCES login (id),
  50         created    timestamptz          DEFAULT now(),
  51         updated    timestamptz,
  52         id         integer     NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
  53 );
  54
  55 COMMENT ON COLUMN kind.truth      IS 'link validity ranging from 0 (never) to 100 (always)';
  56
  57 CREATE OR REPLACE FUNCTION exportform(word) RETURNS text AS $$
  58         SELECT concat(
  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, '/')
  62         );
  63 $$ LANGUAGE SQL IMMUTABLE;
  64
  65 CREATE OR REPLACE VIEW _word_ref AS
  66         SELECT
  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 nullif(r.image, '{}') IS NOT NULL THEN r.id
  78                      WHEN nullif(w.image, '{}') IS NOT NULL THEN w.id END id -- image id
  79         FROM word r
  80         LEFT JOIN word w ON w.id = r.ref;
  81
  82 CREATE OR REPLACE VIEW _word_tree AS
  83         WITH RECURSIVE r AS (
  84                 SELECT w.ref, w.lang, w.cat, w.grade, w.form, NULL::int trans
  85                 FROM _word_ref w
  86         UNION ALL
  87                 SELECT r.ref, r.lang, w.cat,
  88                         CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN r.grade ELSE w.grade END,
  89                         CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN r.form ELSE w.form END,
  90                         CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN w.id END
  91                 FROM r JOIN word w ON w.id = r.cat
  92                   LEFT JOIN word t ON w.id = t.ref AND t.lang = r.lang
  93                 WHERE r.trans IS NULL
  94         )
  95         SELECT ref, lang, trans cat, grade, form FROM r WHERE trans IS NOT NULL
  96                 ORDER BY cat, grade, form, ref;
  97
  98 CREATE OR REPLACE VIEW _word AS
  99         SELECT
 100                 (SELECT array_agg(coalesce(ref, id)) FROM _word_tree
 101                         WHERE cat = w.ref AND lang = w.lang) sub,
 102                 w.*
 103         FROM _word_ref w
 104         ;
 105
 106 CREATE OR REPLACE VIEW _cat_words AS
 107         SELECT exportform(word.*) form, sub.*, word.lang, word.ref
 108         FROM word RIGHT JOIN (
 109                 SELECT cat id, array_agg(exportform(word.*) ORDER BY grade, form) forms
 110                         FROM word WHERE ref IS NULL GROUP BY cat
 111         ) sub USING (id);