keyboard/altgr/index: move ctrl rows to distinct inventory mode

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

diff --git a/tools/word.pg.sql b/tools/word.pg.sql
index a142ebc6cab2215e16012ebccb52f4cbf33cb8d1..9f972dc01041c2a867f9c5b3a6a47d9769edc237 100644 (file)
--- a/tools/word.pg.sql
+++ b/tools/word.pg.sql
@@ -4,13 +4,13 @@ CREATE TABLE login (
        email      text,
        fullname   text,
        editlang   text[],
        email      text,
        fullname   text,
        editlang   text[],

-       id         serial      NOT NULL PRIMARY KEY
+       id         integer     NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY

 );
 
 CREATE TABLE word (
 );
 
 CREATE TABLE word (

-       form       text        NOT NULL,
+       form       text,

        alt        text[],
        alt        text[],

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

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


@@ -24,7 +24,7 @@ CREATE TABLE word (
        creator    integer              REFERENCES login (id),
        created    timestamptz          DEFAULT now(),
        updated    timestamptz,
        creator    integer              REFERENCES login (id),
        created    timestamptz          DEFAULT now(),
        updated    timestamptz,

-       id         serial      NOT NULL PRIMARY KEY
+       id         integer     NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY

 );
 
 COMMENT ON COLUMN word.form       IS 'preferred textual representation';
 );
 
 COMMENT ON COLUMN word.form       IS 'preferred textual representation';


@@ -49,19 +49,11 @@ CREATE TABLE kind (
        creator    integer              REFERENCES login (id),
        created    timestamptz          DEFAULT now(),
        updated    timestamptz,
        creator    integer              REFERENCES login (id),
        created    timestamptz          DEFAULT now(),
        updated    timestamptz,

-       id         serial      NOT NULL PRIMARY KEY
+       id         integer     NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY

 );
 
 COMMENT ON COLUMN kind.truth      IS 'link validity ranging from 0 (never) to 100 (always)';
 
 );
 
 COMMENT ON COLUMN kind.truth      IS 'link validity ranging from 0 (never) to 100 (always)';
 

-CREATE OR REPLACE FUNCTION exportform(word) RETURNS text AS $$
-       SELECT concat(
-               coalesce($1.ref, $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,
 CREATE OR REPLACE VIEW _word_ref AS
        SELECT
                r.form, r.alt, r.lang,


@@ -74,13 +66,31 @@ CREATE OR REPLACE VIEW _word_ref AS
                coalesce(r.wptitle, w.wptitle) wptitle,
                coalesce(r.story,   w.story  ) story,
                r.creator, r.created, r.updated,
                coalesce(r.wptitle, w.wptitle) wptitle,
                coalesce(r.story,   w.story  ) story,
                r.creator, r.created, r.updated,

-               CASE WHEN r.image IS NULL THEN w.id ELSE r.id END id -- image id
+               CASE WHEN nullif(r.image, '{}') IS NOT NULL THEN r.id
+                    WHEN nullif(w.image, '{}') IS NOT NULL THEN w.id END id -- image id

        FROM word r
        LEFT JOIN word w ON w.id = r.ref;
 
        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 (
-               SELECT cat id, array_agg(exportform(word.*) ORDER BY grade, form) forms
-                       FROM word WHERE ref IS NULL GROUP BY cat
-       ) sub USING (id);
+CREATE OR REPLACE VIEW _word_tree AS
+       WITH RECURSIVE r AS (
+               SELECT w.ref, w.lang, w.cat, w.grade, w.form, NULL::int trans
+               FROM _word_ref w
+       UNION ALL
+               SELECT r.ref, r.lang, w.cat,
+                       CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN r.grade ELSE w.grade END,
+                       CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN r.form ELSE w.form END,
+                       CASE WHEN w.lang=r.lang OR t.lang=r.lang THEN w.id END
+               FROM r JOIN word w ON w.id = r.cat
+                 LEFT JOIN word t ON w.id = t.ref AND t.lang = r.lang
+               WHERE r.trans IS NULL
+       )
+       SELECT ref, lang, trans cat, grade, form FROM r WHERE trans IS NOT NULL
+               ORDER BY cat, grade, form, ref;
+
+CREATE OR REPLACE VIEW _word AS
+       SELECT
+               (SELECT array_agg(coalesce(ref, id)) FROM _word_tree
+                       WHERE cat = w.ref AND lang = w.lang) sub,
+               w.*
+       FROM _word_ref w
+       ;