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: 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 7aacfa88d5cfb8f76fba04699e9ea87df8adc692..ec552c968b4a0add4f328d6e676deb434eccad60 100644
(file)
--- a/
tools/word.pg.sql
+++ b/
tools/word.pg.sql
@@
-20,8
+20,9
@@
CREATE TABLE word (
source text CHECK (source ~ '^https?://'),
thumb text[],
wptitle text,
source text CHECK (source ~ '^https?://'),
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
);
@@
-37,6
+38,7
@@
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';
COMMENT ON COLUMN word.updated IS 'last significant change';
COMMENT ON COLUMN word.creator IS 'user responsible for initial submit';
@@
-54,14
+56,14
@@
CREATE OR REPLACE VIEW _word_ref AS
coalesce(r.cat, w.cat ) cat, --TODO translate w?
coalesce(r.ref, r.id ) "ref",
coalesce(r.prio, w.prio ) prio,
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.source, w.source ) source,
coalesce(r.thumb, w.thumb ) thumb,
coalesce(r.wptitle, w.wptitle) wptitle,
- r.created, r.updated,
- CASE WHEN r.source IS NULL THEN w.id ELSE r.id END id, -- image id
- coalesce(r.cover, w.cover ) cover,
- coalesce(r.grade, w.grade ) grade,
- r.creator
+ 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;
FROM word r
LEFT JOIN word w ON w.id = r.ref;