Roadmap 6PP herstructurering

Op dit moment is het conversie-script voor de normalisatie van 6PP af.
Om deze te testen moet je eerst een download doen van de huidige database. Deze moet je hernoemen naar `6pponline`. Wanneer je vervolgens dit script uitvoert zal de database automatisch de database `new_6pp` aanmaken met de nieuwe (genormaliseerde) data er in.
De oude database is na afloop rijp voor de sloop, gezien er in het script een aantal aanpassingen worden gedaan aan de database.

Afhankelijk van de instellingen van de server, hardware, OS, e.d. duurt de conversie ca. 15 - 60 min.

Mochten er vragen, opmerkingen, e.d. zijn, post dat dan hieronder even ;)

Met vriendelijke groet,
Jan Willem

 
 
DROP DATABASE IF EXISTS new_6pp; 
CREATE DATABASE new_6pp /*!40100 DEFAULT CHARACTER SET utf8 */; 
USE new_6pp; 
 
SET FOREIGN_KEY_CHECKS = 0; 
SET UNIQUE_CHECKS = 0; 
 
-- phpMyAdmin SQL Dump 
-- version 2.11.8.1 
-- http://www.phpmyadmin.net 
-- 
-- Host: localhost 
-- Generation Time: Jul 17, 2009 at 02:48 PM 
-- Server version: 5.0.51 
-- PHP Version: 5.2.6 
 
SET SQL_MODE = 'TRADITIONAL,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,PIPES_AS_CONCAT'; 
 
ALTER TABLE 6pponline.country DROP INDEX name, DROP INDEX ix_country_updated, DROP INDEX ix_country_created, DROP INDEX ix_country_active; 
ALTER TABLE 6pponline.cityname DROP INDEX ix_cityname_official, DROP INDEX ix_cityname_updated, DROP INDEX ix_cityname_active, DROP INDEX ix_cityname_name, DROP INDEX ix_cityname_created; 
ALTER TABLE 6pponline.source DROP INDEX source, DROP INDEX ix_source_updated, DROP INDEX ix_source_created, DROP INDEX ix_source_active, DROP INDEX ix_source_wikiuser; 
ALTER TABLE 6pponline.province DROP INDEX name, DROP INDEX ix_province_updated, DROP INDEX ix_province_active, DROP INDEX ix_province_created; 
ALTER TABLE 6pponline.postcode DROP INDEX ix_postcode_updated, DROP INDEX ix_postcode_active, DROP INDEX ix_postcode_created; 
ALTER TABLE 6pponline.street DROP INDEX street, DROP INDEX ix_street_active, DROP INDEX ix_street_updated, DROP INDEX ix_street_street, DROP INDEX ix_street_created, DROP INDEX ix_street_pobox; 
ALTER TABLE 6pponline.city DROP INDEX ix_city_updated, DROP INDEX ix_city_created, DROP INDEX ix_city_active; 
 
 
OPTIMIZE TABLE 6pponline.city; 
OPTIMIZE TABLE 6pponline.cityname; 
OPTIMIZE TABLE 6pponline.country; 
OPTIMIZE TABLE 6pponline.postcode; 
OPTIMIZE TABLE 6pponline.province; 
OPTIMIZE TABLE 6pponline.source; 
OPTIMIZE TABLE 6pponline.street; 
 
-- 
-- Database: 'new_6pp' 
-- 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'city' 
-- 
 
DROP TABLE IF EXISTS city; 
CREATE TABLE city ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  municipality_id int(10) unsigned default NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  area_code mediumint(8) unsigned default NULL, 
  PRIMARY KEY  (id), 
  KEY fk_city_source (source_id), 
  KEY fk_city_municipality (municipality_id), 
  KEY index_modification_time (modification_time), 
  KEY index_area_code (area_code) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'cityname' 
-- 
 
DROP TABLE IF EXISTS cityname; 
CREATE TABLE cityname ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  city_id int(10) unsigned NOT NULL, 
  name varchar(50) NOT NULL, 
  is_official tinyint(3) unsigned NOT NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_city_id_name USING BTREE (city_id,name), 
  KEY fk_cityname_city (city_id), 
  KEY fk_cityname_source (source_id), 
  KEY index_name (name), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'country' 
-- 
 
DROP TABLE IF EXISTS country; 
CREATE TABLE country ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  country_code mediumint(8) unsigned default NULL, 
  iso_3166_code_2 char(2) default NULL, 
  iso_3166_code_3 char(3) default NULL, 
  iso_3166_id mediumint(8) unsigned default NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_iso_3166_code_2 USING BTREE (iso_3166_code_2), 
  UNIQUE KEY unique_iso_3166_code_3 USING BTREE (iso_3166_code_3), 
  UNIQUE KEY unique_iso_3166_id USING BTREE (iso_3166_id), 
  KEY fk_country_source (source_id), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'countryname' 
-- 
 
DROP TABLE IF EXISTS countryname; 
CREATE TABLE countryname ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  country_id int(10) unsigned NOT NULL, 
  name varchar(100) NOT NULL, 
  is_official tinyint(1) NOT NULL default '0', 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_country_id_name USING BTREE (country_id,name), 
  KEY fk_countryname_source (source_id), 
  KEY fk_countryname_country (country_id), 
  KEY index_name (name), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
 
-- 
-- Table structure for table 'municipality' 
-- 
 
DROP TABLE IF EXISTS municipality; 
CREATE TABLE municipality ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  province_id int(10) unsigned NOT NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  PRIMARY KEY  (id), 
  KEY fk_municipality_province (province_id), 
  KEY index_modification_time (modification_time), 
  KEY fk_municipality_source (source_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'municipalityname' 
-- 
 
DROP TABLE IF EXISTS municipalityname; 
CREATE TABLE municipalityname ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  municipality_id int(10) unsigned NOT NULL, 
  name varchar(100) NOT NULL, 
  is_official tinyint(1) unsigned NOT NULL default '0', 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_municipality_id_name USING BTREE (municipality_id,name), 
  KEY fk_municipalityname_source (source_id), 
  KEY fk_municipalityname_municipality (municipality_id), 
  KEY index_name (name), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'nl_fourpp' 
-- 
 
DROP TABLE IF EXISTS nl_fourpp; 
CREATE TABLE nl_fourpp ( 
  id mediumint(8) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  city_id int(10) unsigned NOT NULL, 
  nl_fourpp mediumint (8) unsigned NOT NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  PRIMARY KEY  (id), 
  KEY index_modification_time (modification_time), 
  KEY fk_fourpps_source USING BTREE (source_id), 
  KEY fk_fourpps_city (city_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'nl_sixpp' 
-- 
 
DROP TABLE IF EXISTS nl_sixpp; 
CREATE TABLE nl_sixpp ( 
  id int(10) unsigned NOT NULL default '0', 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned default NULL, 
  nl_fourpp_id mediumint(8) unsigned NOT NULL, 
  sixppchars char(2) NOT NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  PRIMARY KEY  (id), 
  KEY fk_sixpp_source (source_id), 
  KEY fk_sixpp_fourpps (nl_fourpp_id), 
  KEY index_modification_time (modification_time), 
  KEY unique_nl_fourpp_id_sixppchars (nl_fourpp_id,sixppchars) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'nl_sixppstreet_number' 
-- 
 
DROP TABLE IF EXISTS nl_sixppstreet_number; 
CREATE TABLE nl_sixppstreet_number ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  nl_sixpp_id int(10) unsigned NOT NULL, 
  street_number_id int(10) unsigned NOT NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_sixpp_id_street_number_id (nl_sixpp_id,street_number_id), 
  KEY fk_sixpptreetnumbers_street_number (street_number_id), 
  KEY fk_sixpptreetnumbers_source (source_id), 
  KEY fk_sixpptreetnumbers_sixpp (nl_sixpp_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'provincename' 
-- 
 
DROP TABLE IF EXISTS provincename; 
CREATE TABLE provincename ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  province_id int(10) unsigned NOT NULL, 
  name varchar(100) NOT NULL, 
  is_official tinyint(1) NOT NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_province_id_name (province_id,name), 
  KEY fk_provincename_source (source_id), 
  KEY fk_provincename_province (province_id), 
  KEY index_name (name), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'province' 
-- 
 
DROP TABLE IF EXISTS province; 
CREATE TABLE province ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  country_id int(10) unsigned NOT NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  iso_3166_code varchar(3) default NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_iso_3166_code_country_id (country_id,iso_3166_code), 
  KEY fk_province_source (source_id), 
  KEY fk_province_country (country_id), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'sourcename' 
-- 
 
DROP TABLE IF EXISTS sourcename; 
CREATE TABLE sourcename ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  name varchar(100) NOT NULL, 
  ip_address varchar(50) default NULL, 
  is_wikiuser tinyint(3) unsigned NOT NULL default '0', 
  PRIMARY KEY  (id), 
  KEY unique_name_is_wikiuser USING BTREE (name,is_wikiuser), 
  KEY index_name (name), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'source' 
-- 
 
DROP TABLE IF EXISTS source; 
CREATE TABLE source ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  sourcename_id int(10) unsigned NOT NULL, 
  location varchar(255) default NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_location_sourcename_id (sourcename_id,location), 
  KEY fk_source_sourcename (sourcename_id), 
  KEY index_modification_time (modification_time), 
  KEY index_location (location) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'streetname' 
-- 
 
DROP TABLE IF EXISTS streetname; 
CREATE TABLE streetname ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  street_id int(10) unsigned NOT NULL, 
  name varchar(100) NOT NULL, 
  is_official tinyint(1) NOT NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_street_id_name (street_id,name), 
  KEY fk_streetname_source (source_id), 
  KEY fk_streetname_street (street_id), 
  KEY index_name (name), 
  KEY index_modification_time (modification_time) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'street_number' 
-- 
 
DROP TABLE IF EXISTS street_number; 
CREATE TABLE street_number ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  street_id int(10) unsigned NOT NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  street_number mediumint(8) unsigned default NULL, 
  PRIMARY KEY  (id), 
  UNIQUE KEY unique_street_id_street_number (street_number,street_id), 
  KEY fk_street_number_source (source_id), 
  KEY fk_street_number_street (street_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- -------------------------------------------------------- 
 
-- 
-- Table structure for table 'street' 
-- 
 
DROP TABLE IF EXISTS street; 
CREATE TABLE street ( 
  id int(10) unsigned NOT NULL auto_increment, 
  creation_time datetime default NULL, 
  modification_time datetime default NULL, 
  source_id int(10) unsigned NOT NULL, 
  city_id int(10) unsigned NOT NULL, 
  latitude decimal(8,5) default NULL, 
  longitude decimal(8,5) default NULL, 
  is_pobox tinyint(3) unsigned NOT NULL default '0', 
  subtitle mediumtext, 
  PRIMARY KEY  (id), 
  KEY fk_street_source (source_id), 
  KEY fk_street_city (city_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
DROP TRIGGER IF EXISTS new_6pp.nl_sixpp_binsert; 
DELIMITER // 
 
CREATE TRIGGER new_6pp.nl_sixpp_binsert BEFORE INSERT ON new_6pp.nl_sixpp 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
 
    SET NEW.sixppchars = TRIM(NEW.sixppchars); 
    IF (NOT NEW.sixppchars REGEXP '^[a-zA-Z]{2}$') THEN 
       SET NEW.sixppchars = NULL; 
    END IF; 
 
    SET NEW.id = (NEW.nl_fourpp_id * 676) + (((ASCII(SUBSTR(NEW.sixppchars, 1, 1)) & 0xDF) - 65) * 26) + ((ASCII(SUBSTR(NEW.sixppchars, 2, 1)) & 0xDF) - 65); 
  END 
// 
DELIMITER ; 
 
-- Speed up order 
ALTER TABLE cityname ORDER BY is_official DESC, name ASC; 
ALTER TABLE countryname ORDER BY is_official DESC, name ASC; 
ALTER TABLE municipalityname ORDER BY is_official DESC, name ASC; 
ALTER TABLE provincename ORDER BY is_official DESC, name ASC; 
ALTER TABLE streetname ORDER BY is_official DESC, name ASC; 
ALTER TABLE street_number ORDER BY street_number; 
ALTER TABLE nl_fourpp ORDER BY id; 
ALTER TABLE nl_sixpp ORDER BY id; 
 
-- Disable KEYS for INSERT-speed 
ALTER TABLE city DISABLE KEYS; 
ALTER TABLE cityname DISABLE KEYS; 
ALTER TABLE country DISABLE KEYS; 
ALTER TABLE countryname DISABLE KEYS; 
ALTER TABLE municipality DISABLE KEYS; 
ALTER TABLE municipalityname DISABLE KEYS; 
ALTER TABLE nl_fourpp DISABLE KEYS; 
ALTER TABLE nl_sixpp DISABLE KEYS; 
ALTER TABLE nl_sixppstreet_number DISABLE KEYS; 
ALTER TABLE provincename DISABLE KEYS; 
ALTER TABLE province DISABLE KEYS; 
ALTER TABLE sourcename DISABLE KEYS; 
ALTER TABLE source DISABLE KEYS; 
ALTER TABLE streetname DISABLE KEYS; 
ALTER TABLE street DISABLE KEYS; 
ALTER TABLE street_number DISABLE KEYS; 
 
-- Fix country data 
DELETE FROM 6pponline.country WHERE NOT active; 
 
-- FIX province data 
DELETE FROM 6pponline.province WHERE NOT active; 
 
-- Fix city data 
DROP TABLE IF EXISTS municipalitymapper; 
CREATE TEMPORARY TABLE municipalitymapper ( 
    id int(10) unsigned AUTO_INCREMENT, 
    old_id int(10) unsigned, 
    KEY index_old_id (old_id), 
    PRIMARY KEY (id) 
) ENGINE = InnoDB; 
 
 
ALTER TABLE municipalitymapper DISABLE KEYS; 
INSERT INTO municipalitymapper (old_id) SELECT DISTINCT c.municipality_id FROM 6pponline.city c WHERE (c.municipality_id IS NOT NULL) ORDER BY (SELECT MIN(name) FROM 6pponline.cityname WHERE (city_id=c.municipality_id)); 
ALTER TABLE municipalitymapper ENABLE KEYS; 
 
UPDATE 6pponline.city SET active=1 WHERE (id IN (SELECT old_id FROM municipalitymapper)); 
DELETE FROM 6pponline.city WHERE NOT active; 
 
-- FIX cityname data 
DELETE FROM 6pponline.cityname WHERE NOT active OR (TRIM(name) = ''); 
 
-- FIX street data 
DELETE FROM 6pponline.street WHERE NOT active OR (street=''); 
 
-- FIX postcode data 
DELETE FROM 6pponline.postcode WHERE NOT active; 
 
-- Fix source data 
CREATE TEMPORARY TABLE sourcehelper ( 
  id int(11) NOT NULL auto_increment, 
  created datetime NOT NULL, 
  updated datetime NOT NULL, 
  name varchar(255) default NULL, 
  source varchar(255) default NULL, 
  ip varchar(200), 
  wikiuser tinyint(1) default NULL, 
  PRIMARY KEY  (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
INSERT INTO sourcehelper (id, created, updated, name, source, ip, wikiuser) SELECT id, created, updated, TRIM(name), IF((source IS NULL) OR (source=name),'',TRIM(source)), ip, wikiuser FROM 6pponline.source WHERE active AND (id IN (SELECT DISTINCT source_id FROM 6pponline.postcode UNION SELECT DISTINCT source_id FROM 6pponline.country UNION SELECT DISTINCT source_id FROM 6pponline.city UNION SELECT DISTINCT source_id FROM 6pponline.street UNION SELECT DISTINCT source_id FROM 6pponline.cityname UNION SELECT DISTINCT source_id FROM 6pponline.province)); 
 
-- Migrate 
INSERT INTO sourcename (creation_time, modification_time, name, ip_address, is_wikiuser) SELECT MIN(created), MIN(created), name, MAX(ip), wikiuser FROM sourcehelper GROUP BY name, wikiuser; 
ALTER TABLE sourcename ENABLE KEYS; 
 
INSERT INTO source (creation_time, modification_time, sourcename_id, location) SELECT MIN(s.created) a, MIN(s.created) b, sn.id, s.source FROM sourcehelper s LEFT JOIN sourcename sn ON (sn.name=s.name) WHERE (sn.is_wikiuser=s.wikiuser) GROUP BY sn.id, s.source; 
ALTER TABLE source ENABLE KEYS; 
 
-- START source 
-- CREATE sourcemapper 
DROP TABLE IF EXISTS sourcemapper; 
CREATE TABLE sourcemapper ( 
    id int(10) unsigned, 
    new_id int(10) unsigned, 
    PRIMARY KEY (id) 
) ENGINE=InnoDB; 
 
INSERT INTO sourcemapper (id, new_id) SELECT s.id, so.id FROM sourcehelper s LEFT JOIN sourcename sn ON (sn.name=s.name) LEFT JOIN source so ON (so.sourcename_id=sn.id) WHERE (sn.is_wikiuser=s.wikiuser) AND (so.location=s.source); 
 
-- DROP sourcehelper 
DROP TABLE sourcehelper; 
 
-- Fix source 
UPDATE source SET location=NULL WHERE (TRIM(location)=''); 
-- END source 
 
-- START country 
INSERT INTO country (id, creation_time, modification_time, source_id, latitude, longitude) SELECT c.id, c.created, c.updated, s.new_id, c.lat, c.lng FROM 6pponline.country c LEFT JOIN sourcemapper s ON (s.id=c.source_id); 
-- END country 
 
-- START countryname 
INSERT INTO countryname (creation_time, modification_time, source_id, country_id, name, is_official) SELECT c.created, c.updated, s.new_id, c.id, c.name, 1 FROM 6pponline.country c LEFT JOIN sourcemapper s ON (s.id=c.source_id); 
-- END countryname 
 
-- START province 
INSERT INTO province (id, creation_time, modification_time, source_id, country_id, latitude, longitude) SELECT p.id, p.created, p.updated, s.new_id, p.country_id, p.lat, p.lng FROM 6pponline.province p LEFT JOIN sourcemapper s ON (s.id=p.source_id); 
-- END province 
 
-- START provincename 
INSERT INTO provincename (creation_time, modification_time, source_id, province_id, name, is_official) SELECT p.created, p.updated, s.new_id, p.id, p.name, 1 FROM 6pponline.province p LEFT JOIN sourcemapper s ON (s.id=p.source_id) ORDER BY name; 
-- END provincename 
 
-- START municipality 
INSERT INTO municipality (id, creation_time, modification_time, source_id, province_id) SELECT m.id, c.created, c.updated, s.new_id, c.province_id FROM municipalitymapper m LEFT JOIN 6pponline.city c ON (c.id=m.old_id) LEFT JOIN sourcemapper s ON (s.id=c.source_id); 
-- END municipality 
 
-- START municipalityname 
INSERT INTO municipalityname (creation_time, modification_time, source_id, municipality_id, name, is_official) SELECT MIN(c.created), MIN(c.updated), MIN(s.new_id), m.id, c.name, MAX(c.official) FROM municipalitymapper m LEFT JOIN 6pponline.cityname c ON (c.city_id=m.old_id) LEFT JOIN sourcemapper s ON (s.id=c.source_id) GROUP BY m.id, c.name; 
-- END municipalityname 
 
-- START city 
-- CREATE citymapper 
DROP TABLE IF EXISTS citymapper; 
 
CREATE TEMPORARY TABLE citymapper ( 
    id int(10) unsigned AUTO_INCREMENT, 
    old_id int(10) unsigned, 
    PRIMARY KEY (id), 
    KEY index_old_id (old_id) 
) ENGINE = InnoDB; 
 
ALTER TABLE citymapper DISABLE KEYS; 
INSERT INTO citymapper (old_id) SELECT id FROM 6pponline.city WHERE (id IN (SELECT city_id FROM 6pponline.postcode)); 
ALTER TABLE citymapper ENABLE KEYS; 
 
 
INSERT INTO city (id, creation_time, modification_time, source_id, municipality_id, latitude, longitude, area_code) SELECT cm.id, c.created, c.updated, s.new_id, m.id, c.lat, c.lng, IF((c.areacode = ''), NULL, CAST(c.areacode AS UNSIGNED)) FROM citymapper cm LEFT JOIN 6pponline.city c ON (c.id=cm.old_id) LEFT JOIN municipalitymapper m ON (m.old_id=c.municipality_id) LEFT JOIN sourcemapper s ON (s.id=c.source_id); 
-- END city 
 
-- DROP mapper 
DROP TABLE municipalitymapper; 
 
-- START cityname 
INSERT INTO cityname (creation_time, modification_time, source_id, city_id, name, is_official) SELECT MIN(cn.created), MIN(cn.updated), MIN(s.new_id), m.id, cn.name, MAX(cn.official) FROM citymapper m LEFT JOIN 6pponline.cityname cn ON (cn.city_id=m.old_id) LEFT JOIN sourcemapper s ON (s.id=cn.source_id) GROUP BY cn.name, m.id; 
-- END cityname 
 
-- START fourpps 
-- CREATE fourppmapper 
DROP TEMPORARY TABLE IF EXISTS fourppmapper; 
CREATE TEMPORARY TABLE fourppmapper ( 
    id int(10) unsigned, 
    new_id int(10) unsigned, 
    PRIMARY KEY (id) 
) ENGINE=InnoDB; 
 
INSERT INTO fourppmapper SELECT id, fourpp FROM 6pponline.postcode ORDER BY fourpp; 
 
INSERT INTO nl_fourpp (id, creation_time, modification_time, source_id, city_id, nl_fourpp, latitude, longitude) SELECT p.fourpp, p.created, p.updated, s.new_id, m.id, p.fourpp, p.lat, p.lng FROM 6pponline.postcode p LEFT JOIN citymapper m ON (m.old_id=p.city_id) LEFT JOIN sourcemapper s ON (s.id=p.source_id) ORDER BY 1; 
-- END fourpps 
 
-- DROP mapper 
DROP TABLE citymapper; 
 
-- START sixpp 
INSERT INTO nl_sixpp (creation_time, modification_time, source_id, nl_fourpp_id, sixppchars) SELECT MIN(s.created), MIN(s.updated), MIN(m.new_id), fm.new_id, s.chars FROM 6pponline.street s LEFT JOIN sourcemapper m ON (m.id=s.source_id) LEFT JOIN fourppmapper fm ON (fm.id=s.postcode_id) GROUP BY fm.new_id, s.chars; 
ALTER TABLE nl_sixpp ENABLE KEYS; 
-- END sixpp 
 
-- START street 
-- CREATE streetmapper/helper 
DROP TABLE IF EXISTS streetpremapper; 
CREATE TEMPORARY TABLE streetpremapper ( 
  id int(10) unsigned, 
  street VARCHAR(60), 
  city_id INT(10) unsigned, 
  PRIMARY KEY (id), 
  KEY index_city_id_street (city_id, street) 
) ENGINE = InnoDB; 
 
ALTER TABLE streetpremapper DISABLE KEYS; 
INSERT INTO streetpremapper (id, street, city_id) SELECT s.id, s.street, nf.city_id FROM 6pponline.street s LEFT JOIN fourppmapper fm ON (fm.id=s.postcode_id) LEFT JOIN nl_fourpp nf ON (nf.id=fm.new_id); 
ALTER TABLE streetpremapper ENABLE KEYS; 
 
DROP TABLE IF EXISTS streethelper; 
CREATE TEMPORARY TABLE streethelper ( 
  id int(10) unsigned AUTO_INCREMENT, 
  street VARCHAR(60), 
  city_id INT (10) unsigned, 
  PRIMARY KEY(id), 
  UNIQUE KEY unique_city_id_street (city_id, street) 
) ENGINE = InnoDB; 
 
ALTER TABLE streethelper DISABLE KEYS; 
 
INSERT INTO streethelper (street, city_id) SELECT DISTINCT street, city_id FROM streetpremapper; 
ALTER TABLE streethelper ENABLE KEYS; 
 
DROP TABLE IF EXISTS streetmapper; 
CREATE TABLE streetmapper ( 
  id int(10) unsigned, 
  city_id INT(10) unsigned, 
  new_id INT(10) unsigned, 
  PRIMARY KEY (id), 
  KEY index_new_id (new_id) 
) ENGINE = InnoDB; 
 
ALTER TABLE streetmapper DISABLE KEYS; 
 
INSERT INTO streetmapper (id, city_id, new_id) SELECT sp.id, sp.city_id, sh.id FROM streetpremapper sp LEFT JOIN streethelper sh ON (sh.city_id=sp.city_id) WHERE (sh.street=sp.street); 
ALTER TABLE streethelper ENABLE KEYS; 
 
-- DROP mappers 
DROP TABLE streetpremapper; 
DROP TABLE streethelper; 
 
INSERT INTO street (id, creation_time, modification_time, source_id, city_id) SELECT m.new_id, MIN(s.created), MIN(s.updated), MIN(h.new_id), m.city_id FROM streetmapper m LEFT JOIN 6pponline.street s ON (s.id=m.id) LEFT JOIN sourcemapper h ON (h.id=s.source_id) GROUP BY m.city_id, m.new_id; 
-- END street 
 
-- START streetname 
INSERT INTO streetname (creation_time, modification_time, source_id, street_id, name, is_official) SELECT MIN(s.created), MIN(s.updated), MIN(h.new_id), m.new_id, s.street, 1 FROM streetmapper m LEFT JOIN 6pponline.street s ON (s.id=m.id) LEFT JOIN sourcemapper h ON (h.id=s.source_id) GROUP BY m.new_id, s.street; 
ALTER TABLE streetname ENABLE KEYS; 
 
-- FIX streetdata 
UPDATE street s SET is_pobox=('Postbus' IN (SELECT name FROM streetname WHERE (street_id=s.id))); 
-- END streetname 
 
-- START street_number 
CREATE TEMPORARY TABLE street_numberpremapper ( 
  nr INT UNSIGNED, 
  oldstreet_id INT UNSIGNED, 
  street_id INT UNSIGNED, 
  KEY index_nr_street_id (street_id, nr) 
) ENGINE=InnoDB; 
 
ALTER TABLE street_numberpremapper DISABLE KEYS; 
INSERT INTO street_numberpremapper (nr, oldstreet_id, street_id) SELECT IFNULL(s.low,0), s.id, m.new_id FROM 6pponline.street s LEFT JOIN streetmapper m ON (m.id=s.id) UNION DISTINCT SELECT IFNULL(s.high,0), s.id, m.new_id FROM 6pponline.street s LEFT JOIN streetmapper m ON (m.id=s.id); 
ALTER TABLE street_numberpremapper ENABLE KEYS; 
 
CREATE TABLE street_numbermapper ( 
  id INT UNSIGNED AUTO_INCREMENT, 
  nr INT UNSIGNED, 
  street_id INT UNSIGNED, 
  oldstreet_id INT UNSIGNED, 
  PRIMARY KEY (id), 
  UNIQUE KEY unique_nr_street_id (nr, street_id), 
  KEY index_oldstreet_id (oldstreet_id) 
) ENGINE=InnoDB; 
 
ALTER TABLE street_numbermapper DISABLE KEYS; 
INSERT INTO street_numbermapper (nr, street_id, oldstreet_id) SELECT nr, street_id, MIN(oldstreet_id) FROM street_numberpremapper GROUP BY nr, street_id; 
ALTER TABLE street_numbermapper ENABLE KEYS; 
 
-- DROP premapper 
DROP TABLE street_numberpremapper; 
 
INSERT INTO street_number (id, creation_time, modification_time, source_id, street_id, street_number) SELECT m.id, s.created, s.updated, h.new_id, m.street_id, m.nr FROM street_numbermapper m LEFT JOIN 6pponline.street s ON (s.id=m.oldstreet_id) LEFT JOIN sourcemapper h ON (h.id=s.source_id) WHERE (IFNULL(s.low,0)=m.nr) UNION DISTINCT SELECT m.id, s.created, s.updated, h.new_id, m.street_id, m.nr FROM street_numbermapper m LEFT JOIN 6pponline.street s ON (s.id=m.oldstreet_id) LEFT JOIN sourcemapper h ON (h.id=s.source_id) WHERE (IFNULL(s.high,0)=m.nr); 
-- END street_number 
 
-- START nl_sixppstreet_number 
 
INSERT INTO nl_sixppstreet_number (creation_time, modification_time, source_id, nl_sixpp_id, street_number_id) SELECT s.created, s.updated, h.new_id, sp.id, m.id FROM street_numbermapper m LEFT JOIN 6pponline.street s ON (s.id=m.oldstreet_id) LEFT JOIN sourcemapper h ON (h.id=s.source_id) LEFT JOIN fourppmapper fm ON (fm.id=s.postcode_id) LEFT JOIN nl_sixpp sp ON ((sp.nl_fourpp_id=fm.new_id) AND (sp.sixppchars = s.chars)); 
-- END nl_sixpptreetnumber 
 
-- DROP mappers 
DROP TABLE sourcemapper; 
DROP TABLE streetmapper; 
DROP TABLE fourppmapper; 
DROP TABLE street_numbermapper; 
 
DROP DATABASE 6pponline; 
 
ALTER TABLE city ENABLE KEYS; 
ALTER TABLE cityname ENABLE KEYS; 
ALTER TABLE country ENABLE KEYS; 
ALTER TABLE countryname ENABLE KEYS; 
ALTER TABLE municipality ENABLE KEYS; 
ALTER TABLE municipalityname ENABLE KEYS; 
ALTER TABLE nl_fourpp ENABLE KEYS; 
ALTER TABLE nl_sixppstreet_number ENABLE KEYS; 
ALTER TABLE provincename ENABLE KEYS; 
ALTER TABLE province ENABLE KEYS; 
ALTER TABLE street ENABLE KEYS; 
ALTER TABLE street_number ENABLE KEYS; 
 
 
-- 
-- Constraints for table city 
-- 
ALTER TABLE city 
  ADD CONSTRAINT fk_city_municipality FOREIGN KEY (municipality_id) REFERENCES municipality (id), 
  ADD CONSTRAINT fk_city_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table cityname 
-- 
ALTER TABLE cityname 
  ADD CONSTRAINT fk_cityname_city FOREIGN KEY (city_id) REFERENCES city (id), 
  ADD CONSTRAINT fk_cityname_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table country 
-- 
ALTER TABLE country 
  ADD CONSTRAINT fk_country_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table countryname 
-- 
ALTER TABLE countryname 
  ADD CONSTRAINT fk_countryname_country FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_countryname_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table municipality 
-- 
ALTER TABLE municipality 
  ADD CONSTRAINT fk_municipality_province FOREIGN KEY (province_id) REFERENCES province (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_municipality_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table municipalityname 
-- 
ALTER TABLE municipalityname 
  ADD CONSTRAINT fk_municipalityname_municipality FOREIGN KEY (municipality_id) REFERENCES municipality (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_municipalityname_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table nl_fourpp 
-- 
ALTER TABLE nl_fourpp 
  ADD CONSTRAINT fk_fourpps_city FOREIGN KEY (city_id) REFERENCES city (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table nl_sixpp 
-- 
ALTER TABLE nl_sixpp 
  ADD CONSTRAINT fk_sixpp_fourpps FOREIGN KEY (nl_fourpp_id) REFERENCES fourpps (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_sixpp_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table nl_sixppstreet_number 
-- 
ALTER TABLE nl_sixppstreet_number 
  ADD CONSTRAINT fk_sixpptreetnumbers_sixpp FOREIGN KEY (nl_sixpp_id) REFERENCES nl_sixpp (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_sixpptreetnumbers_street_number FOREIGN KEY (street_number_id) REFERENCES street_number (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table provincename 
-- 
ALTER TABLE provincename 
  ADD CONSTRAINT fk_provincename_province FOREIGN KEY (province_id) REFERENCES province (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_provincename_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table province 
-- 
ALTER TABLE province 
  ADD CONSTRAINT fk_province_country FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_province_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table source 
-- 
ALTER TABLE source 
  ADD CONSTRAINT fk_source_sourcename FOREIGN KEY (sourcename_id) REFERENCES sourcename (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table streetname 
-- 
ALTER TABLE streetname 
  ADD CONSTRAINT fk_streetname_source FOREIGN KEY (source_id) REFERENCES street_number (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_streetname_street FOREIGN KEY (street_id) REFERENCES street (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table street_number 
-- 
ALTER TABLE street_number 
  ADD CONSTRAINT fk_street_number_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_street_number_street FOREIGN KEY (street_id) REFERENCES street (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
-- 
-- Constraints for table street 
-- 
ALTER TABLE street 
  ADD CONSTRAINT fk_street_city FOREIGN KEY (city_id) REFERENCES city (id) ON DELETE CASCADE ON UPDATE CASCADE, 
  ADD CONSTRAINT fk_street_source FOREIGN KEY (source_id) REFERENCES source (id) ON DELETE CASCADE ON UPDATE CASCADE; 
 
 
DROP TRIGGER IF EXISTS new_6pp.city_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.city_binsert BEFORE INSERT ON new_6pp.city 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.city_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.city_bupdate BEFORE UPDATE ON new_6pp.city 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
  END 
// 
DELIMITER ; 
 
-- 
-- Triggers 'cityname' 
-- 
DROP TRIGGER IF EXISTS new_6pp.cityname_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.cityname_binsert BEFORE INSERT ON new_6pp.cityname 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.cityname_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.cityname_bupdate BEFORE UPDATE ON new_6pp.cityname 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.country_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.country_binsert BEFORE INSERT ON new_6pp.country 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
 
    SET NEW.iso_3166_code_2 = TRIM(NEW.iso_3166_code_2); 
    SET NEW.iso_3166_code_3 = TRIM(NEW.iso_3166_code_3); 
 
    IF (NOT (NEW.iso_3166_code_2 REGEXP '^[a-zA-Z]{2}$')) THEN 
        SET NEW.iso_3166_code_2 = NULL; 
    END IF; 
    IF (NOT (NEW.iso_3166_code_2 REGEXP '^[a-zA-Z]{3}$')) THEN 
        SET NEW.iso_3166_code_3 = NULL; 
    END IF; 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.country_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.country_bupdate BEFORE UPDATE ON new_6pp.country 
 FOR EACH ROW BEGIN 
     SET NEW.modification_time = NOW(); 
 
     SET NEW.iso_3166_code_2 = TRIM(NEW.iso_3166_code_2); 
     SET NEW.iso_3166_code_3 = TRIM(NEW.iso_3166_code_3); 
 
     IF (NOT (NEW.iso_3166_code_2 REGEXP '^[a-zA-Z]{2}$')) THEN 
         SET NEW.iso_3166_code_2 = OLD.iso_3166_code_2; 
     END IF; 
     IF (NOT (NEW.iso_3166_code_2 REGEXP '^[a-zA-Z]{3}$')) THEN 
         SET NEW.iso_3166_code_3 = OLD.iso_3166_code_3; 
     END IF; 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.countryname_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.countryname_binsert BEFORE INSERT ON new_6pp.countryname 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.countryname_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.countryname_bupdate BEFORE UPDATE ON new_6pp.countryname 
 FOR EACH ROW BEGIN 
        SET NEW.modification_time = NOW(); 
        SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.municipality_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.municipality_binsert BEFORE INSERT ON new_6pp.municipality 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.municipality_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.municipality_bupdate BEFORE UPDATE ON new_6pp.municipality 
 FOR EACH ROW BEGIN 
        SET NEW.modification_time = NOW(); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.municipalityname_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.municipalityname_binsert BEFORE INSERT ON new_6pp.municipalityname 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.municipalityname_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.municipalityname_bupdate BEFORE UPDATE ON new_6pp.municipalityname 
 FOR EACH ROW BEGIN 
        SET NEW.modification_time = NOW(); 
        SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.nl_fourpp_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.nl_fourpp_binsert BEFORE INSERT ON new_6pp.nl_fourpp 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.nl_fourpp_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.nl_fourpp_bupdate BEFORE UPDATE ON new_6pp.nl_fourpp 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.nl_sixpp_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.nl_sixpp_bupdate BEFORE UPDATE ON new_6pp.nl_sixpp 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
    SET NEW.sixppchars = TRIM(NEW.sixppchars); 
 
    IF (NOT NEW.sixppchars REGEXP '^[a-zA-Z]{2}$') THEN 
       SET NEW.sixppchars = OLD.sixppchars; 
    END IF; 
    IF ((NEW.nl_fourpp_id <> OLD.nl_fourpp_id) OR (NEW.sixppchars <> OLD.sixppchars)) THEN 
      SET NEW.id = (NEW.nl_fourpp_id * 676) + (((ASCII(SUBSTR(NEW.sixppchars, 1, 1)) & 0xDF) - 65) * 26) + ((ASCII(SUBSTR(NEW.sixppchars, 2, 1)) & 0xDF) - 65); 
    END IF; 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.nl_sixppstreet_number_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.nl_sixppstreet_number_binsert BEFORE INSERT ON new_6pp.nl_sixppstreet_number 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.nl_sixppstreet_number_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.nl_sixppstreet_number_bupdate BEFORE UPDATE ON new_6pp.nl_sixppstreet_number 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.provincename_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.provincename_binsert BEFORE INSERT ON new_6pp.provincename 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.provincename_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.provincename_bupdate BEFORE UPDATE ON new_6pp.provincename 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.province_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.province_binsert BEFORE INSERT ON new_6pp.province 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.province_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.province_bupdate BEFORE UPDATE ON new_6pp.province 
 FOR EACH ROW BEGIN 
     SET NEW.modification_time = NOW(); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.sourcename_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.sourcename_binsert BEFORE INSERT ON new_6pp.sourcename 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.sourcename_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.sourcename_bupdate BEFORE UPDATE ON new_6pp.sourcename 
 FOR EACH ROW BEGIN 
     SET NEW.modification_time = NOW(); 
     SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.source_binsert; 
DELIMITER // 
 
CREATE TRIGGER new_6pp.source_binsert BEFORE INSERT ON new_6pp.source 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
    IF ((SELECT name FROM sourcename WHERE (id=NEW.sourcename_id)) = NEW.location) THEN 
        SET NEW.location = NULL; 
    END IF; 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.source_bupdate; 
DELIMITER // 
 
CREATE TRIGGER new_6pp.source_bupdate BEFORE UPDATE ON new_6pp.source 
 FOR EACH ROW BEGIN 
     SET NEW.modification_time = NOW(); 
     IF ((SELECT name FROM sourcename WHERE (id=NEW.sourcename_id)) = NEW.location) THEN 
         SET NEW.location = NULL; 
     END IF; 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.streetname_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.streetname_binsert BEFORE INSERT ON new_6pp.streetname 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
    SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.streetname_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.streetname_bupdate BEFORE UPDATE ON new_6pp.streetname 
 FOR EACH ROW BEGIN 
     SET NEW.modification_time = NOW(); 
     SET NEW.name = TRIM(NEW.name); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.street_number_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.street_number_binsert BEFORE INSERT ON new_6pp.street_number 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.street_number_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.street_number_bupdate BEFORE UPDATE ON new_6pp.street_number 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
  END 
// 
DELIMITER ; 
 
DROP TRIGGER IF EXISTS new_6pp.street_binsert; 
DELIMITER // 
CREATE TRIGGER new_6pp.street_binsert BEFORE INSERT ON new_6pp.street 
 FOR EACH ROW BEGIN 
    SET NEW.creation_time = IFNULL(NEW.creation_time, NOW()); 
    SET NEW.modification_time = IFNULL(NEW.modification_time, NOW()); 
  END 
// 
DELIMITER ; 
DROP TRIGGER IF EXISTS new_6pp.street_bupdate; 
DELIMITER // 
CREATE TRIGGER new_6pp.street_bupdate BEFORE UPDATE ON new_6pp.street 
 FOR EACH ROW BEGIN 
    SET NEW.modification_time = NOW(); 
  END 
// 
DELIMITER ; 
 
SET UNIQUE_CHECKS = 1; 
SET FOREIGN_KEY_CHECKS = 1;

[API-CALLS]
get_country (id: int[], name: string, countrycode: int, modified_since: int, page: int, per_page: int)

Get one or more countries.

@param id Use the country id to get one specific country.
@param name Gets countries with this exact name. Use % for matching patterns.
@param countrycode Gets countries with this country calling code.
@param modified_since Minimum update date. Countries that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per_page: int 
- total: int 
- countrylist 
	- id: int 
        - creationtime: int, 
        - modificationtime: int, 
	- source_id: int 
	- name: string 
	- alternative_names: string[] 
	- latitude: real 
	- longitude: real 
	- iso3166id: int 
	- iso3166code2: string 
	- iso3166code3: string 
	- countrycode: int

get_province (id: int[], name: string, country_id: int[], modified_since: int, page: int, per_page: int)

Get one or more provinces.

@param id Use the province-id to get one specific province.
@param name Gets province with this exact name. Use % for matching patterns.
@param country_id Get provinces with given country-id
@param modified_since Minimum update date. Provinces that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per_page: int 
- total: int 
- provincelist 
	- id: int 
        - creationtime: int, 
        - modificationtime: int, 
	- source_id: int 
	- name: string 
	- alternative_names: string[] 
	- latitude: real 
	- longitude: real

get_municipality (id: int[], name: string, province_id: int[], modified_since: int, page: int, per_page: int)

Get one or more municipalities.

@param id Use the municipality-id to get one specific municipality.
@param name Gets municipality with this exact name. Use % for matching patterns.
@param province_id Get municipalities with given province-id
@param modified_since Minimum update date. Municipalities that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per_page: int 
- total: int 
- municipalitylist 
	- id: int 
        - creationtime: int, 
        - modificationtime: int, 
	- source_id: int 
	- name: string 
	- alternative_names: string[] 
	- latitude: real 
	- longitude: real

get_city (id: int[], name: string, municipality_id: int[], areacode: int, modified_since: int, page: int, per_page: int)

Get one or more cities.

@param id Use the city-id to get one specific city.
@param name Gets city with this exact name. Use % for matching patterns.
@param municipality_id Get cities with given municipality-id
@param areacode Get cities with given areacode
@param modified_since Minimum update date. Cities that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per-page: int 
- total: int 
- citylist 
	- id: int 
        - creationtime: int 
        - modificationtime: int 
	- source_id: int 
	- name: string 
	- alternative_names: string[] 
	- latitude: real 
	- longitude: real 
	- areacode: int 
	- province_id: int

get_street (id: int[], modified_since: int, name: string, city_id: int[], page: int, per_page: int, ispobox: int)

Get one or more streets.

@param id Use the street-id to get one specific street.
@param name Gets street with this exact name. Use % for matching patterns.
@param city_id Get streets with given city-id
@param modified_since Minimum update date. Streets that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per-page: int 
- total: int 
- citylist 
	- id: int 
        - creationtime: int 
        - modificationtime: int 
	- source_id: int 
	- name: string 
	- alternative_names: string[] 
	- latitude: real 
	- longitude: real 
	- city_id: int 
	- subtitle: string 
	- ispobox: int

get_streetnumber (id: int[], street_id: int[], streetnumber: int[], modified_since: int, page: int, per_page: int)

Get one or more streetnumbers.

@param id Use the streetnumber-id to get one specific streetnumber.
@param name Gets streetnumber with this exact name. Use % for matching patterns.
@param street_id Get streetnumbers with given street-id
@param modified_since Minimum update date. Streetnumbers that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per-page: int 
- total: int 
- streetnumberlist: 
	- id: int 
        - creationtime: int 
        - modificationtime: int 
	- source_id: int 
	- name: string 
	- alternative_names: string[] 
	- latitude: real 
	- longitude: real 
	- street_id: int 
	- streetnumber: int

get_nl_fourpp (id: int[], city_id: int[], modified_since: int, page: int, per_page: int)

Get one or more nl_fourpps.

@param id Use the nl_fourpp-id to get one specific nl_fourpp.
@param city_id Get nl_fourpps with given city_id.
@param modified_since Minimum update date. Nl_fourpps that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per-page: int 
- total: int 
- streetnumberlist: 
	- id: int 
        - creationtime: int 
        - modificationtime: int 
	- source_id: int 
	- city_id: int 
	- latitude: real 
	- longitude: real

get_nl_sixpp (id: int[], nl_fourpp_id: int[], sixppchars: string, modified_since: int, page: int, per_page: int)

Get one or more nl_fourpps.

@param id Use the nl_sixpp-id to get one specific nl_sixpp.
@param nl_fourpp_id Get nl_sixpps with given nl_fourpp_id.
@param modified_since Minimum update date. Nl_fourpps that are created or updated on and after this date will be returned. The date should be in the form of a unix timestamp.
@param page Gets a page of results. By default page 1.
@param per_page Sets the maximum amount of results per page. By default 50, max 1000.

Returns:

 
- page: int 
- pages: int 
- per-page: int 
- total: int 
- streetnumberlist: 
	- id: int 
        - creationtime: int 
        - modificationtime: int 
	- source_id: int 
	- fourpp_id: int 
	- latitude: real 
	- longitude: real 
	- streetnumbers: int[]

get_source (id:int[], name: string, location: string, modified_since: int, page: int, per_page: int)

Returns:

 
- page: int 
- pages: int 
- per-page: int 
- total: int 
- streetnumberlist: 
	- id: int 
        - creationtime: int 
        - modificationtime: int 
	- name: string 
	- location: string 
	- iswikiuser: int 
	- ipaddress: string

Common documentation set-functions:
@param id: The ID of the record you want to edit. In case of function setnl_fourpp this parameter is also needed for adding a record because the ID is the same as the 4PP.
@param source_id: The reference to the source you are using for editing or adding the record.
@param *_id: Reference to any record of *, where * is for exampe city, country or nl_fourpp.
@param name: The official name of a city, street, municipality, ...
@param alternative_names: Non-official names of city, street, municipality, ...

set_country (id: int, source_id: int, name: string, alternative_names: string[], latitude: real, longitude: real, countrycode: int, iso_3166_code_2: string, iso_3166_code_3: string, iso_3166_id: int)

set_province (id: int, source_id: int, country_id: int, name: string, alternative_names: string[], latitude: real, longitude: real, iso_3166_code: string)

set_municipality (id: int, source_id: int, province_id: int, name: string, alternative_names: string[], latitude: real, longitude: real)

set_city (id: int, source_id: int, municipality_id: int, name: string, alternative_names: string[], latitude: real, longitude: real)

set_street (id: int, source_id: int, city_id: int, name: string, alternative_names: string[], latitude: real, longitude: real);

set_streetnumber (id: int, source_id: int, street_id: int, latitude: real, longitude: real, streetnumber: int)

set_nl_fourpp (id: int, source_id: int, city_id: int, latitude: real, longitude: real)

set_nl_sixpp (id: int, source_id: int, nl_fourpp_id: int, sixppchars: string, latitude: real, longitude: real)

Last but not least: delete-functions
delete_* (id:int[])

Delete * , where * is one of country, province, municipality, city, street, streetnumber, nl_fourpp, nl_sixpp.

Returns:

???

[PROPOSALS]
getphonenumberinfo (phonenumber: string)

Gets information about a given phonenumber

@param phonenumber Phonenumber used to find information about.

Returns:

 
- page: int 
- pages: int 
- per-page: int 
- total: int 
- phonenumberinfolist 
	- countryid: int 
	- countryname: string 
	- provinceid: int 
	- provincename: string 
	- municipalityid: int 
	- municipalityname: string 
	- cityid: int 
	- cityname: string

Something like:
SELECT
c.id, cn.name, p.id, pn.name, m.id, mn.name, ct.id, ctn.name
FROM
country c
LEFT JOIN
countryname cn ON (cn.country_id=c.id)
LEFT JOIN
province p ON (p.country_id=c.id)
LEFT JOIN
provincename pn ON (pn.province_id=p.id)
LEFT JOIN
municipality m ON (m.province_id=p.id)
LEFT JOIN
municipalityname mn ON (mn.municipality_id=m.id)
LEFT JOIN
city ct ON (c.municipality_id=m.id)
LEFT JOIN
cityname ctn ON (cn.city_id=c.id)
WHERE
ctn.isofficial
AND
mn.isofficial
AND
pn.isofficial
AND
cn.isofficial
AND
POS((CAST(c.countrycode TO STRING) || CAST(ct.areacode TO STRING)), {voorbewerkt_telefoonnummer}) = 1;

kvdb Admin	#4 \| Posted: 10 Aug 2009 12:45 Reply
Misschien handig om ook antwoordnummers te flaggen, zoals we nu ook het veld is_pobox in tabel Street hebben. Anders hebben we nooit postcode 2300 VB in onze database en halen we nooit de 100%. Voorbeeld: Antwoordnummer 10271 2300 VB Leiden

kvdb Admin	#7 \| Posted: 15 Sep 2009 10:47 Reply
Het zou kunnen zijn dat JWvdV zo'n script al heeft liggen, maar hij is vrij druk met studeren op het moment. Ik zal je script op 6PP graag willen loslaten. Groet, Kees

martijn189 Forums Member	#8 \| Posted: 15 Sep 2009 10:51 Reply
OK, ik zal vanmiddag de scripts maken en op een demo pagina zetten. Tevens zal ik de demo van auto aanvul via jquery erop zetten want die geeft inmiddels straatnamen horend bij een plaats.

JWvdVeer Guest	#9 \| Posted: 15 Sep 2009 12:45 Reply
>>>> Het zou kunnen zijn dat JWvdV zo'n script al heeft liggen, maar hij is vrij druk met studeren op het moment. <<<< Jan Willem heeft op het moment geen script klaar liggen. Maar Jan Willem wilde dat samen doen met het project samen met OSM. Zodat we gewoon de schrijfwijze van OSM overnemen. Hebben we tenminste toch nog één bron waaraan we het kunnen valideren.

martijn189 Forums Member	#10 \| Posted: 16 Sep 2009 12:00 \| Edited by: martijn189 Reply
Ik heb het script klaar. Het toont een lijst van duplicate plaatsen/straten die je vervolgens direct kunt verwijderen of hernoemen (kleine letters van maken). Download: http://home.casema.nl/mr4/6ppcleaner.zip Screenshot: http://home.casema.nl/mr4/6ppcleaner.jpg

kvdb Admin	#13 \| Posted: 17 Sep 2009 09:01 Reply
De dump die je hebt is nu 2 weken oud. De verschillen zullen wel meevallen.

martijn189 Forums Member	#14 \| Posted: 17 Sep 2009 10:39 Reply
eehm de dump die ik gebruik is een maand of 2 oud denk ik. Is de dump al genormaliseerd of is dat nog de oude

kvdb Admin	#15 \| Posted: 17 Sep 2009 11:56 Reply
De nieuwe dump: http://kvdb.net/upload/files/new_6pp.sql.gz