diff options
| author | Jochen Topf <jochen@topf.org> | 2013-01-24 15:33:11 +0100 |
|---|---|---|
| committer | Jochen Topf <jochen@topf.org> | 2013-01-24 15:33:11 +0100 |
| commit | 55550a88ff517d48ca6e56f720c4080fdb569ac9 (patch) | |
| tree | 321e30c5f49533849cd3802641a7e86d23f79a5e /sources | |
| parent | 62e3e5509f05385eb1414172c53f5be9255bc520 (diff) | |
| download | taginfo-55550a88ff517d48ca6e56f720c4080fdb569ac9.tar taginfo-55550a88ff517d48ca6e56f720c4080fdb569ac9.tar.gz | |
Add in_wiki and in_wiki_en columns to tags and selected_tags table
Diffstat (limited to 'sources')
| -rw-r--r-- | sources/db/pre.sql | 4 | ||||
| -rw-r--r-- | sources/master/master.sql | 8 | ||||
| -rw-r--r-- | sources/wiki/post.sql | 2 |
3 files changed, 12 insertions, 2 deletions
diff --git a/sources/db/pre.sql b/sources/db/pre.sql index 8353b2f..5654074 100644 --- a/sources/db/pre.sql +++ b/sources/db/pre.sql @@ -133,6 +133,8 @@ CREATE TABLE selected_tags ( count_all INTEGER DEFAULT 0, count_nodes INTEGER DEFAULT 0, count_ways INTEGER DEFAULT 0, - count_relations INTEGER DEFAULT 0 + count_relations INTEGER DEFAULT 0, + in_wiki INTEGER, + in_wiki_en INTEGER ); diff --git a/sources/master/master.sql b/sources/master/master.sql index c55185a..1167d3a 100644 --- a/sources/master/master.sql +++ b/sources/master/master.sql @@ -63,9 +63,17 @@ UPDATE db.keys SET in_josm=1 WHERE key IN (SELECT distinct k FROM josm.josm -- potlatch XXX UPDATE db.keys SET in_merkaartor=1 WHERE key IN (SELECT key FROM merkaartor.keys); +-- ============================================================================ + -- too slow, so we drop it for now -- INSERT INTO db.tags (key, value) SELECT DISTINCT key, value FROM wiki.wikipages WHERE key || '=XX=' || value NOT IN (SELECT key || '=XX=' || value FROM db.tags); +UPDATE db.tags SET in_wiki=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*') AND key || '=' || value IN (SELECT distinct tag FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*'); +UPDATE db.tags SET in_wiki_en=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*' AND lang='en') AND key || '=' || value IN (SELECT distinct tag FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*' AND lang='en'); + +UPDATE db.selected_tags SET in_wiki=1 WHERE skey || '=' || svalue IN (SELECT distinct tag FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*'); +UPDATE db.selected_tags SET in_wiki_en=1 WHERE skey || '=' || svalue IN (SELECT distinct tag FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*' AND lang='en'); + -- ============================================================================ DROP TABLE IF EXISTS popular_keys; diff --git a/sources/wiki/post.sql b/sources/wiki/post.sql index 16d02ce..8d1fea3 100644 --- a/sources/wiki/post.sql +++ b/sources/wiki/post.sql @@ -23,7 +23,7 @@ CREATE INDEX relation_pages_rtype_idx ON relation_pages(rtype); CREATE INDEX wiki_images_image_idx ON wiki_images(image); INSERT INTO wikipages_keys (key, langs, lang_count) SELECT key, group_concat(lang || ' ' || status), count(*) FROM wikipages WHERE value IS NULL GROUP BY key; -INSERT INTO wikipages_tags (key, value, langs, lang_count) SELECT key, value, group_concat(lang || ' ' || status), count(*) FROM wikipages WHERE value IS NOT NULL GROUP BY key, value; +INSERT INTO wikipages_tags (key, value, langs, lang_count) SELECT key, value, group_concat(lang || ' ' || status), count(*) FROM wikipages WHERE value IS NOT NULL AND value != '*' GROUP BY key, value; INSERT INTO wiki_languages (language, count_pages) SELECT lang, count(*) FROM wikipages GROUP BY lang; |