word: sitewide styling of images gallery

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

diff --git a/tools/word.pg.sql b/tools/word.pg.sql
index 1dc4f5bc68c8b3ed107967e6c69b9eaaa8a9d613..d18a99cb4b3172f464f5302832957e910ba9a678 100644 (file)
--- a/tools/word.pg.sql
+++ b/tools/word.pg.sql
@@ -1,28 +1,43 @@
+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[],
 CREATE TABLE word (
        form       text        NOT NULL,
        alt        text[],

-       lang       text        NOT NULL DEFAULT 'eng',
+       lang       text        NOT NULL DEFAULT 'en',

        cat        integer              REFERENCES word (id),
        ref        integer              REFERENCES word (id),
        prio       smallint    NOT NULL DEFAULT '1',
        cat        integer              REFERENCES word (id),
        ref        integer              REFERENCES word (id),
        prio       smallint    NOT NULL DEFAULT '1',

+       grade      integer,

        cover      boolean     NOT NULL DEFAULT FALSE,
        source     text,
        thumb      text[],
        wptitle    text,
        created    timestamptz          DEFAULT now(),
        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.form       IS 'preferred textual representation';
 COMMENT ON COLUMN word.alt        IS 'alternate forms with equivalent meaning';
        id         serial      NOT NULL PRIMARY KEY
 );
 
 COMMENT ON COLUMN word.form       IS 'preferred textual representation';
 COMMENT ON COLUMN word.alt        IS 'alternate forms with equivalent meaning';

-COMMENT ON COLUMN word.lang       IS 'ISO 639-3 language code';
+COMMENT ON COLUMN word.lang       IS 'ISO 639 language code matching wikipedia subdomain';

 COMMENT ON COLUMN word.cat        IS 'hierarchical classification';
 COMMENT ON COLUMN word.cat        IS 'hierarchical classification';

-COMMENT ON COLUMN word.ref        IS 'reference to equivalent eng translation';
+COMMENT ON COLUMN word.ref        IS 'reference to equivalent en translation';

 COMMENT ON COLUMN word.prio       IS 'difficulty level or importance; lower values have precedence';
 COMMENT ON COLUMN word.prio       IS 'difficulty level or importance; lower values have precedence';

+COMMENT ON COLUMN word.grade      IS 'ascending hierarchical order, preceding default alphabetical';

 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.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.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(
 
 CREATE OR REPLACE FUNCTION exportform(word) RETURNS text AS $$
        SELECT concat(


@@ -34,6 +49,6 @@ $$ LANGUAGE SQL IMMUTABLE;
 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 (

-               SELECT cat id, array_agg(exportform(word.*) ORDER BY form) forms
+               SELECT cat id, array_agg(exportform(word.*) ORDER BY grade, form) forms

                        FROM word WHERE ref IS NULL GROUP BY cat
        ) sub USING (id);
                        FROM word WHERE ref IS NULL GROUP BY cat
        ) sub USING (id);