From 076414f518a298e4f98bc1e69a9008b199e08884 Mon Sep 17 00:00:00 2001 From: Jochen Topf Date: Thu, 18 Sep 2014 12:31:17 +0200 Subject: Prepare for putting project stats in db.keys and db.values. --- sources/db/pre.sql | 2 ++ sources/projects/post.sql | 9 +++++++++ 2 files changed, 11 insertions(+) diff --git a/sources/db/pre.sql b/sources/db/pre.sql index bc51db0..9d349a3 100644 --- a/sources/db/pre.sql +++ b/sources/db/pre.sql @@ -28,6 +28,7 @@ CREATE TABLE keys ( cells_ways INTEGER DEFAULT 0, in_wiki INTEGER DEFAULT 0, in_wiki_en INTEGER DEFAULT 0, + in_projects INTEGER DEFAULT 0, in_josm INTEGER DEFAULT 0, in_potlatch INTEGER DEFAULT 0, characters VARCHAR @@ -73,6 +74,7 @@ CREATE TABLE tags ( object_id INTEGER, in_wiki INTEGER DEFAULT 0, in_wiki_en INTEGER DEFAULT 0, + in_projects INTEGER DEFAULT 0, in_josm INTEGER DEFAULT 0, in_potlatch INTEGER DEFAULT 0 ); diff --git a/sources/projects/post.sql b/sources/projects/post.sql index 5fa2b5a..5a6f73c 100644 --- a/sources/projects/post.sql +++ b/sources/projects/post.sql @@ -11,6 +11,15 @@ INSERT INTO stats (key, value) SELECT 'projects_ok', count(*) FROM projects WHER INSERT INTO stats (key, value) SELECT 'project_keys', count(*) FROM project_tags WHERE value IS NULL; INSERT INTO stats (key, value) SELECT 'project_tags', count(*) FROM project_tags WHERE value IS NOT NULL; +CREATE TABLE project_counts ( + key TEXT NOT NULL, + value TEXT, + num INTEGER +); + +INSERT INTO project_counts (key, value, num) SELECT key, NULL, count(*) FROM (SELECT DISTINCT project_id, key FROM project_tags) GROUP BY key; +INSERT INTO project_counts (key, value, num) SELECT key, value, count(*) FROM (SELECT DISTINCT project_id, key, value FROM project_tags WHERE value IS NOT NULL) GROUP BY key, value; + ANALYZE; UPDATE source SET update_end=datetime('now'); -- cgit v1.2.3