Prepare for putting project stats in db.keys and db.values.

2 files changed, 11 insertions, 0 deletions

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');