+CREATE TABLE login (
+ username text NOT NULL UNIQUE,
+ pass text,
+ email text,
+ fullname text,
+ editlang text[],
+ id serial NOT NULL PRIMARY KEY
+);
+
CREATE TABLE word (
form text NOT NULL,
alt text[],
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,
thumb text[],
wptitle text,
created timestamptz DEFAULT now(),
+ creator integer REFERENCES login (id),
+ 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.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;