diff options
| -rw-r--r-- | sources/db/pre.sql | 2 | ||||
| -rw-r--r-- | sources/projects/post.sql | 9 |
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'); |