git.shiar.nl
/
sheet.git
/ blobdiff
commit
grep
author
committer
pickaxe
?
search:
re
summary
|
shortlog
|
log
|
commit
|
commitdiff
|
tree
raw
|
inline
| side by side
word: select mkwordlist forms from _cat_words view
[sheet.git]
/
tools
/
word.pg.sql
diff --git
a/tools/word.pg.sql
b/tools/word.pg.sql
index 3c4f264b48c27350bcca962a882843b6477a8240..da22b55311246348d8832e07542511b9aa3815d0 100644
(file)
--- a/
tools/word.pg.sql
+++ b/
tools/word.pg.sql
@@
-3,6
+3,7
@@
CREATE TABLE word (
lang text NOT NULL DEFAULT 'eng',
cat integer REFERENCES word (id),
ref integer REFERENCES word (id),
lang text NOT NULL DEFAULT 'eng',
cat integer REFERENCES word (id),
ref integer REFERENCES word (id),
+ prio smallint NOT NULL DEFAULT '1',
source text,
thumb text[],
wptitle text,
source text,
thumb text[],
wptitle text,
@@
-14,6
+15,13
@@
COMMENT ON COLUMN word.form IS 'preferred textual representation';
COMMENT ON COLUMN word.lang IS 'ISO 639-3 language code';
COMMENT ON COLUMN word.cat IS 'hierarchical classification';
COMMENT ON COLUMN word.ref IS 'reference to equivalent eng translation';
COMMENT ON COLUMN word.lang IS 'ISO 639-3 language code';
COMMENT ON COLUMN word.cat IS 'hierarchical classification';
COMMENT ON COLUMN word.ref IS 'reference to equivalent eng translation';
+COMMENT ON COLUMN word.prio IS 'difficulty level or importance; lower values have precedence';
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';
+
+CREATE OR REPLACE VIEW _cat_words AS
+ SELECT * FROM word RIGHT JOIN (
+ SELECT cat id, array_agg(form ORDER BY form) forms
+ FROM word WHERE ref IS NULL GROUP BY cat
+ ) sub USING (id);