word: allow browser access to json data

[sheet.git] / tools / word.pg.sql

diff --git a/tools/word.pg.sql b/tools/word.pg.sql
index 101af7a19491142d9d91920240d22c04017547ec..ec552c968b4a0add4f328d6e676deb434eccad60 100644 (file)
--- a/tools/word.pg.sql
+++ b/tools/word.pg.sql
@@ -3,6 +3,7 @@ CREATE TABLE login (
        pass       text,
        email      text,
        fullname   text,
        pass       text,
        email      text,
        fullname   text,

+       editlang   text[],

        id         serial      NOT NULL PRIMARY KEY
 );
 
        id         serial      NOT NULL PRIMARY KEY
 );
 


@@ -12,14 +13,16 @@ CREATE TABLE word (
        lang       text        NOT NULL DEFAULT 'en',
        cat        integer              REFERENCES word (id),
        ref        integer              REFERENCES word (id),
        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,
        grade      integer,
        cover      boolean     NOT NULL DEFAULT FALSE,

-       source     text,
+       source     text                 CHECK (source ~ '^https?://'),

        thumb      text[],
        wptitle    text,
        thumb      text[],
        wptitle    text,

-       created    timestamptz          DEFAULT now(),
+       story      text,

        creator    integer              REFERENCES login (id),
        creator    integer              REFERENCES login (id),

+       created    timestamptz          DEFAULT now(),

        updated    timestamptz,
        id         serial      NOT NULL PRIMARY KEY
 );
        updated    timestamptz,
        id         serial      NOT NULL PRIMARY KEY
 );


@@ -35,16 +38,35 @@ COMMENT ON COLUMN word.cover      IS 'highlight if selected';
 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.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(
 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;
 
                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 (
 CREATE OR REPLACE VIEW _cat_words AS
        SELECT exportform(word.*) form, sub.*, word.lang, word.ref
        FROM word RIGHT JOIN (