pass text,
email text,
fullname text,
+ editlang text[],
id serial NOT NULL PRIMARY KEY
);
lang text NOT NULL DEFAULT 'en',
cat integer REFERENCES word (id),
ref integer REFERENCES word (id),
- prio smallint NOT NULL DEFAULT '1',
+ prio smallint DEFAULT '1'
+ CHECK (prio >= 0 OR ref IS NOT NULL),
grade integer,
cover boolean NOT NULL DEFAULT FALSE,
- source text,
+ source text CHECK (source ~ '^https?://'),
thumb text[],
wptitle text,
+ story text,
+ creator integer REFERENCES login (id),
created timestamptz DEFAULT now(),
+ updated timestamptz,
id serial NOT NULL PRIMARY KEY
);
COMMENT ON COLUMN word.source IS 'URI of downloaded image';
COMMENT ON COLUMN word.thumb IS 'ImageMagick convert options to create thumbnail from source image';
COMMENT ON COLUMN word.wptitle IS 'reference Wikipedia article';
+COMMENT ON COLUMN word.story IS 'paragraph defining or describing the entity, wikipedia intro';
+COMMENT ON COLUMN word.updated IS 'last significant change';
+COMMENT ON COLUMN word.creator IS 'user responsible for initial submit';
CREATE OR REPLACE FUNCTION exportform(word) RETURNS text AS $$
SELECT concat(
- $1.prio || CASE WHEN $1.cover THEN 'c' ELSE '' END || ':',
+ $1.id, ':',
+ $1.prio, CASE WHEN $1.cover THEN 'c' ELSE '' END, ':',
array_to_string($1.form || $1.alt, '/')
);
$$ LANGUAGE SQL IMMUTABLE;
+CREATE OR REPLACE VIEW _word_ref AS
+ SELECT
+ r.form, r.alt, r.lang,
+ coalesce(r.cat, w.cat ) cat, --TODO translate w?
+ coalesce(r.ref, r.id ) "ref",
+ coalesce(r.prio, w.prio ) prio,
+ coalesce(r.grade, w.grade ) grade,
+ coalesce(r.cover, w.cover ) cover,
+ coalesce(r.source, w.source ) source,
+ coalesce(r.thumb, w.thumb ) thumb,
+ coalesce(r.wptitle, w.wptitle) wptitle,
+ coalesce(r.story, w.story ) story,
+ r.creator, r.created, r.updated,
+ CASE WHEN r.source IS NULL THEN w.id ELSE r.id END id -- image id
+ FROM word r
+ LEFT JOIN word w ON w.id = r.ref;
+
CREATE OR REPLACE VIEW _cat_words AS
SELECT exportform(word.*) form, sub.*, word.lang, word.ref
FROM word RIGHT JOIN (