From 6382e7aa94c08df95b1429be3aae0393af642253 Mon Sep 17 00:00:00 2001 From: Jochen Topf Date: Tue, 9 Nov 2010 15:54:06 +0100 Subject: Numerous report improvments --- sources/wiki/post.sql | 22 +++++++++++++++++----- sources/wiki/pre.sql | 12 +++++++----- 2 files changed, 24 insertions(+), 10 deletions(-) (limited to 'sources/wiki') diff --git a/sources/wiki/post.sql b/sources/wiki/post.sql index 28e5673..eaf9ec0 100644 --- a/sources/wiki/post.sql +++ b/sources/wiki/post.sql @@ -13,14 +13,26 @@ UPDATE wikipages SET status='e' WHERE type='page' AND has_templ='true' AND parse CREATE INDEX wikipages_key_value_idx ON wikipages(key, value); -INSERT INTO wikipages_keys (key, langs) SELECT key, group_concat(lang || ' ' || status) FROM wikipages WHERE value IS NULL GROUP BY key; -INSERT INTO wikipages_tags (key, value, langs) SELECT key, value, group_concat(lang || ' ' || status) FROM wikipages WHERE value IS NOT NULL GROUP BY key, value; +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 wiki_languages (language, count_pages) SELECT lang, count(*) FROM wikipages GROUP BY lang; -INSERT INTO stats (key, value) SELECT 'wikipages_keys', count(*) FROM wikipages_keys; -INSERT INTO stats (key, value) SELECT 'wikipages_tags', count(*) FROM wikipages_tags; -INSERT INTO stats (key, value) SELECT 'wikipages_languages', count(*) FROM wiki_languages; +INSERT INTO stats (key, value) SELECT 'wiki_keys_described', count(*) FROM wikipages_keys; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys', count(*) FROM wikipages WHERE value IS NULL; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_redirect', count(*) FROM wikipages WHERE value IS NULL AND status='r'; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_without_template', count(*) FROM wikipages WHERE value IS NULL AND status='p'; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_with_template', count(*) FROM wikipages WHERE value IS NULL AND status='t'; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_keys_with_error', count(*) FROM wikipages WHERE value IS NULL AND status='e'; + +INSERT INTO stats (key, value) SELECT 'wiki_tags_described', count(*) FROM wikipages_tags; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags', count(*) FROM wikipages WHERE value IS NOT NULL; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_redirect', count(*) FROM wikipages WHERE value IS NOT NULL AND status='r'; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_without_template', count(*) FROM wikipages WHERE value IS NOT NULL AND status='p'; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_with_template', count(*) FROM wikipages WHERE value IS NOT NULL AND status='t'; +INSERT INTO stats (key, value) SELECT 'wiki_pages_for_tags_with_error', count(*) FROM wikipages WHERE value IS NOT NULL AND status='e'; + +INSERT INTO stats (key, value) SELECT 'wiki_languages', count(*) FROM wiki_languages; ANALYZE; diff --git a/sources/wiki/pre.sql b/sources/wiki/pre.sql index 04071eb..1f51754 100644 --- a/sources/wiki/pre.sql +++ b/sources/wiki/pre.sql @@ -53,16 +53,18 @@ CREATE TABLE wikipages ( DROP TABLE IF EXISTS wikipages_keys; CREATE TABLE wikipages_keys ( - key TEXT, - langs TEXT + key TEXT, + langs TEXT, + lang_count INTEGER ); DROP TABLE IF EXISTS wikipages_tags; CREATE TABLE wikipages_tags ( - key TEXT, - value TEXT, - langs TEXT + key TEXT, + value TEXT, + langs TEXT, + lang_count INTEGER ); DROP TABLE IF EXISTS wiki_languages; -- cgit v1.2.3