SQLCommands

DROP VIEW IF EXISTS lgd_tags;
CREATE VIEW lgd_tags AS SELECT t.osm_entity_type, t.osm_entity_id, t.k, t.v FROM ((SELECT 'node' AS osm_entity_type, node_id AS osm_entity_id, k, v FROM node_tags) UNION ALL (SELECT 'way' AS osm_entity_type, way_id AS osm_entity_id, k, v FROM way_tags) UNION ALL (SELECT 'relation' AS osm_entity_type, relation_id AS osm_entity_id, k, v FROM relation_tags)) AS t;
SELECT k, COUNT(k) AS usage_count, COUNT(DISTINCT v) AS distinct_value_count INTO lgd_stat_tags_k FROM lgd_tags GROUP BY k;
CREATE INDEX idx_lgd_stat_tags_k_k ON lgd_stat_tags_k(k);
SELECT k, v, COUNT(*) AS usage_count INTO lgd_stat_tags_kv FROM lgd_tags GROUP BY k, v;
CREATE INDEX idx_lgd_stats_kv_k_v ON lgd_stat_tags_kv(k, v);




CREATE TABLE lgd_user(username text PRIMARY KEY, email text, password text, admin boolean not null, view TEXT);
INSERT INTO lgd_user VALUES ('main', '', '', FALSE, 'lgd_user_main');




CREATE TABLE lgd_map_resource_k_history(id SERIAL PRIMARY KEY, k TEXT NOT NULL, property TEXT NOT NULL,  object TEXT NOT NULL, user_id TEXT REFERENCES lgd_user(username) ON UPDATE CASCADE, comment TEXT, timestamp TEXT NOT NULL, action TEXT);
ALTER TABLE lgd_map_resource_k ADD COLUMN user_id TEXT;
ALTER TABLE lgd_map_resource_k ADD FOREIGN KEY(user_id) REFERENCES lgd_user(username) ON UPDATE CASCADE;
UPDATE lgd_map_resource_k SET user_id ='main';
ALTER TABLE lgd_map_resource_k ADD COLUMN last_history_id INTEGER;
ALTER TABLE lgd_map_resource_k ADD FOREIGN KEY(last_history_id) REFERENCES lgd_map_resource_k_history(id);
ALTER TABLE lgd_map_resource_k_history ADD COLUMN userspace TEXT;
ALTER TABLE lgd_map_resource_k_history ADD FOREIGN KEY(userspace) REFERENCES lgd_user(username) ON UPDATE CASCADE;
ALTER TABLE lgd_map_resource_k_history ADD COLUMN history_id INTEGER;
ALTER TABLE lgd_map_resource_k_history ADD FOREIGN KEY(history_id) REFERENCES lgd_map_resource_k_history(id);




CREATE TABLE lgd_map_resource_kv_history(id SERIAL PRIMARY KEY, k TEXT NOT NULL, v TEXT NOT NULL, property TEXT NOT NULL, object TEXT NOT NULL, user_id TEXT REFERENCES lgd_user(username) ON UPDATE CASCADE, comment TEXT, timestamp TEXT NOT NULL, action TEXT);
ALTER TABLE lgd_map_resource_kv ADD COLUMN user_id TEXT;
ALTER TABLE lgd_map_resource_kv ADD FOREIGN KEY(user_id) REFERENCES lgd_user(username) ON UPDATE CASCADE;
UPDATE lgd_map_resource_kv SET user_id ='main';
ALTER TABLE lgd_map_resource_kv ADD COLUMN last_history_id INTEGER;
ALTER TABLE lgd_map_resource_kv ADD FOREIGN KEY(last_history_id) REFERENCES lgd_map_resource_kv_history(id)  ON UPDATE CASCADE;
ALTER TABLE lgd_map_resource_kv_history ADD COLUMN userspace TEXT;
ALTER TABLE lgd_map_resource_kv_history ADD FOREIGN KEY(userspace) REFERENCES lgd_user(username) ON UPDATE CASCADE;
ALTER TABLE lgd_map_resource_kv_history ADD COLUMN history_id INTEGER;
ALTER TABLE lgd_map_resource_kv_history ADD FOREIGN KEY(history_id) REFERENCES lgd_map_resource_kv_history(id);




ALTER TYPE lgd_datatype ADD VALUE 'deleted';
ALTER TABLE lgd_map_datatype DROP CONSTRAINT lgd_map_datatype_pkey;
CREATE TABLE lgd_map_datatype_history(id SERIAL PRIMARY KEY, k TEXT NOT NULL, datatype lgd_datatype NOT NULL, user_id TEXT REFERENCES lgd_user(username) ON UPDATE CASCADE, comment TEXT, timestamp TEXT NOT NULL, action TEXT);
ALTER TABLE lgd_map_datatype ADD COLUMN user_id TEXT;
ALTER TABLE lgd_map_datatype ADD FOREIGN KEY(user_id) REFERENCES lgd_user(username) ON UPDATE CASCADE;
UPDATE lgd_map_datatype SET user_id ='main';
ALTER TABLE lgd_map_datatype ADD COLUMN last_history_id INTEGER;
ALTER TABLE lgd_map_datatype ADD FOREIGN KEY(last_history_id) REFERENCES lgd_map_datatype_history(id);
ALTER TABLE lgd_map_datatype_history ADD COLUMN userspace TEXT;
ALTER TABLE lgd_map_datatype_history ADD FOREIGN KEY(userspace) REFERENCES lgd_user(username) ON UPDATE CASCADE;
ALTER TABLE lgd_map_datatype_history ADD COLUMN history_id INTEGER;
ALTER TABLE lgd_map_datatype_history ADD FOREIGN KEY(history_id) REFERENCES lgd_map_datatype_history(id);




ALTER TABLE lgd_map_literal DROP CONSTRAINT lgd_map_literal_k_key;
CREATE TABLE lgd_map_literal_history(id SERIAL PRIMARY KEY, k TEXT NOT NULL, property TEXT NOT NULL, language TEXT NOT NULL, user_id TEXT REFERENCES lgd_user(username) ON UPDATE CASCADE, comment TEXT, timestamp TEXT NOT NULL, action TEXT);
ALTER TABLE lgd_map_literal ADD COLUMN user_id TEXT;
ALTER TABLE lgd_map_literal ADD FOREIGN KEY(user_id) REFERENCES lgd_user(username) ON UPDATE CASCADE;
UPDATE lgd_map_literal SET user_id ='main';
ALTER TABLE lgd_map_literal ADD COLUMN last_history_id INTEGER;
ALTER TABLE lgd_map_literal ADD FOREIGN KEY(last_history_id) REFERENCES lgd_map_literal_history(id);
ALTER TABLE lgd_map_literal_history ADD COLUMN userspace TEXT;
ALTER TABLE lgd_map_literal_history ADD FOREIGN KEY(userspace) REFERENCES lgd_user(username) ON UPDATE CASCADE;
ALTER TABLE lgd_map_literal_history ADD COLUMN history_id INTEGER;
ALTER TABLE lgd_map_literal_history ADD FOREIGN KEY(history_id) REFERENCES lgd_map_literal_history(id);