From 0fdfaca9dfad26abe8f6e25ef12ca1824d2e0c10 Mon Sep 17 00:00:00 2001 From: Jochen Topf Date: Mon, 29 Sep 2014 15:44:45 +0200 Subject: Use new top_tags table instead of old selected_tags table. --- sources/db/post.sql | 5 +++++ sources/db/pre.sql | 2 +- sources/master/master.sql | 5 ----- web/lib/api/v4/tag.rb | 10 +++++----- web/lib/api/v4/tags.rb | 6 +++--- web/viewsjs/index.js.erb | 2 +- 6 files changed, 15 insertions(+), 15 deletions(-) diff --git a/sources/db/post.sql b/sources/db/post.sql index 35c0a43..08f33b0 100644 --- a/sources/db/post.sql +++ b/sources/db/post.sql @@ -27,6 +27,9 @@ CREATE INDEX tag_combinations_key2_value2_idx ON tag_combinations (key2, CREATE UNIQUE INDEX relation_types_rtype_idx ON relation_types (rtype); CREATE INDEX relation_roles_rtype_idx ON relation_roles (rtype); +-- ============================================================================ +-- deprecated: can be removed soon + INSERT INTO selected_tags (skey, svalue) SELECT key1, value1 FROM tag_combinations WHERE value1 != '' UNION @@ -42,6 +45,8 @@ ANALYZE selected_tags; CREATE UNIQUE INDEX selected_tags_key_value_idx ON selected_tags (skey, svalue); +-- ============================================================================ + INSERT INTO stats (key, value) SELECT 'num_keys', count(*) FROM keys; INSERT INTO stats (key, value) SELECT 'num_keys_on_nodes', count(*) FROM keys WHERE count_nodes > 0; INSERT INTO stats (key, value) SELECT 'num_keys_on_ways', count(*) FROM keys WHERE count_ways > 0; diff --git a/sources/db/pre.sql b/sources/db/pre.sql index 9d349a3..e0320ea 100644 --- a/sources/db/pre.sql +++ b/sources/db/pre.sql @@ -134,8 +134,8 @@ CREATE TABLE prevalent_roles ( fraction REAL ); +-- deprecated: can be removed soon DROP TABLE IF EXISTS selected_tags; - CREATE TABLE selected_tags ( skey VARCHAR, svalue VARCHAR, diff --git a/sources/master/master.sql b/sources/master/master.sql index 5397a4c..8433ec5 100644 --- a/sources/master/master.sql +++ b/sources/master/master.sql @@ -62,13 +62,8 @@ UPDATE db.keys SET in_wiki_en=1 WHERE key IN (SELECT distinct key FROM wiki.wiki 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'); - -- ============================================================================ --- currently unused future replacement for db.selected_tags - DROP TABLE IF EXISTS top_tags; CREATE TABLE top_tags ( skey VARCHAR, diff --git a/web/lib/api/v4/tag.rb b/web/lib/api/v4/tag.rb index 80bdc61..8e3a98b 100644 --- a/web/lib/api/v4/tag.rb +++ b/web/lib/api/v4/tag.rb @@ -53,13 +53,13 @@ class Taginfo < Sinatra::Base get_first_value() res = (params[:query].to_s != '' ? - @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE ? ESCAPE '@') OR (p.value1 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0 + @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, top_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE ? ESCAPE '@') OR (p.value1 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0 UNION SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key1=k.key AND p.value1='' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE ? ESCAPE '@') OR (p.value1 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0 - UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE ? ESCAPE '@') OR (p.value2 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0 + UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, top_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE ? ESCAPE '@') OR (p.value2 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0 UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key2=k.key AND p.value2='' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE ? ESCAPE '@') OR (p.value2 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0", key, value, query_substr, query_substr, key, value, query_substr, query_substr, key, value, query_substr, query_substr, key, value, query_substr, query_substr) : - @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 - UNION SELECT p.key1 AS other_key, '' AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key1=k.key AND p.value1 = '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 - UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND p.count_#{filter_type} > 0 + @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, top_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 + UNION SELECT p.key1 AS other_key, '' AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key1=k.key AND p.value1 = '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 + UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, top_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND p.count_#{filter_type} > 0 UNION SELECT p.key2 AS other_key, '' AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key2=k.key AND p.value2 = '' AND p.key1=? AND p.value1=? AND p.count_#{filter_type} > 0", key, value, key, value, key, value, key, value)). order_by(@ap.sortname, @ap.sortorder) { |o| o.together_count diff --git a/web/lib/api/v4/tags.rb b/web/lib/api/v4/tags.rb index 7fe5561..842673a 100644 --- a/web/lib/api/v4/tags.rb +++ b/web/lib/api/v4/tags.rb @@ -128,11 +128,11 @@ class Taginfo < Sinatra::Base :ui => '/tags' }) do - total = @db.count('db.selected_tags'). + total = @db.count('top_tags'). condition_if("(skey LIKE ? ESCAPE '@') OR (svalue LIKE ? ESCAPE '@')", like_contains(params[:query]), like_contains(params[:query])). get_first_value().to_i - - res = @db.select('SELECT * FROM db.selected_tags'). + + res = @db.select('SELECT * FROM top_tags'). condition_if("(skey LIKE ? ESCAPE '@') OR (svalue LIKE ? ESCAPE '@')", like_contains(params[:query]), like_contains(params[:query])). order_by(@ap.sortname, @ap.sortorder) { |o| o.tag :skey diff --git a/web/viewsjs/index.js.erb b/web/viewsjs/index.js.erb index 28ab272..215c9f5 100644 --- a/web/viewsjs/index.js.erb +++ b/web/viewsjs/index.js.erb @@ -7,7 +7,7 @@ tagcloud_number_of_relations = 30 keys = @db.select("SELECT key, scale1 FROM popular_keys ORDER BY scale1 DESC LIMIT #{ tagcloud_number_of_keys }"). execute(). each_with_index{ |tag, idx| tag['pos'] = (tagcloud_number_of_keys - idx) / tagcloud_number_of_keys.to_f } -tags = @db.select("SELECT skey, svalue FROM db.selected_tags WHERE skey NOT IN ('source', 'source_ref', 'attribution') ORDER BY count_all DESC LIMIT #{ tagcloud_number_of_tags }").execute() +tags = @db.select("SELECT skey, svalue FROM top_tags WHERE skey NOT IN ('source', 'source_ref', 'attribution') ORDER BY count_all DESC LIMIT #{ tagcloud_number_of_tags }").execute() relations = @db.select("SELECT rtype FROM db.relation_types ORDER BY count DESC LIMIT #{ tagcloud_number_of_relations }").execute() %> -- cgit v1.2.3