diff options
| -rw-r--r-- | sources/db/pre.sql | 5 | ||||
| -rw-r--r-- | sources/master/master.sql | 12 |
2 files changed, 4 insertions, 13 deletions
diff --git a/sources/db/pre.sql b/sources/db/pre.sql index 31c6f8b..91bd7e7 100644 --- a/sources/db/pre.sql +++ b/sources/db/pre.sql @@ -27,7 +27,6 @@ CREATE TABLE keys ( cells_nodes INTEGER DEFAULT 0, cells_ways INTEGER DEFAULT 0, in_wiki INTEGER DEFAULT 0, - in_wiki_en INTEGER DEFAULT 0, in_projects INTEGER DEFAULT 0, characters VARCHAR ); @@ -71,7 +70,6 @@ CREATE TABLE tags ( object_type VARCHAR(1), -- (n)ode, (w)ay, (r)elation object_id INTEGER, in_wiki INTEGER DEFAULT 0, - in_wiki_en INTEGER DEFAULT 0, in_projects INTEGER DEFAULT 0 ); @@ -139,7 +137,6 @@ CREATE TABLE selected_tags ( count_nodes INTEGER DEFAULT 0, count_ways INTEGER DEFAULT 0, count_relations INTEGER DEFAULT 0, - in_wiki INTEGER, - in_wiki_en INTEGER + in_wiki INTEGER ); diff --git a/sources/master/master.sql b/sources/master/master.sql index 5a4e207..82bf034 100644 --- a/sources/master/master.sql +++ b/sources/master/master.sql @@ -51,16 +51,14 @@ INSERT INTO master_stats SELECT * FROM db.stats INSERT INTO db.keys (key) SELECT DISTINCT key FROM wiki.wikipages WHERE key NOT IN (SELECT key FROM db.keys); -UPDATE db.keys SET in_wiki=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages WHERE value IS NULL); -UPDATE db.keys SET in_wiki_en=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages WHERE value IS NULL AND lang='en'); +UPDATE db.keys SET in_wiki=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages WHERE value IS NULL); -- ============================================================================ -- 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.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 != '*'); -- ============================================================================ @@ -73,7 +71,6 @@ CREATE TABLE top_tags ( count_ways INTEGER DEFAULT 0, count_relations INTEGER DEFAULT 0, in_wiki INTEGER DEFAULT 0, - in_wiki_en INTEGER DEFAULT 0, in_projects INTEGER DEFAULT 0 ); @@ -88,8 +85,7 @@ UPDATE top_tags SET count_ways = (SELECT t.count_ways FROM db.tags t WHERE t.key=skey AND t.value=svalue), count_relations = (SELECT t.count_relations FROM db.tags t WHERE t.key=skey AND t.value=svalue); -UPDATE top_tags SET in_wiki=1 WHERE skey || '=' || svalue IN (SELECT distinct tag FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*'); -UPDATE top_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'); +UPDATE top_tags SET in_wiki=1 WHERE skey || '=' || svalue IN (SELECT distinct tag FROM wiki.wikipages WHERE value IS NOT NULL AND value != '*'); UPDATE top_tags SET in_projects=(SELECT num FROM projects.project_counts p WHERE p.key=skey AND p.value=svalue); @@ -104,7 +100,6 @@ CREATE TABLE popular_keys ( users INTEGER, wikipages INTEGER DEFAULT 0, in_wiki INTEGER DEFAULT 0, - in_wiki_en INTEGER DEFAULT 0, scale_count REAL, scale_users REAL, scale_wiki REAL, @@ -120,7 +115,6 @@ INSERT INTO popular_keys (key, count, users) UPDATE popular_keys SET wikipages = (SELECT count(*) FROM wiki.wikipages w WHERE w.key=popular_keys.key); UPDATE popular_keys SET in_wiki=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages); -UPDATE popular_keys SET in_wiki_en=1 WHERE key IN (SELECT distinct key FROM wiki.wikipages WHERE lang='en'); -- ============================================================================ |