Update import scripts: create table using InnoDB engine.

[platal.git] / upgrade / newdirectory-0.0.1 / 15_addresses.sql

diff --git a/upgrade/newdirectory-0.0.1/15_addresses.sql b/upgrade/newdirectory-0.0.1/15_addresses.sql
index e437e22..8a95d0a 100644 (file)
--- a/upgrade/newdirectory-0.0.1/15_addresses.sql
+++ b/upgrade/newdirectory-0.0.1/15_addresses.sql
@@ -1,3 +1,5 @@
+DROP TABLE IF EXISTS profile_addresses;
+
 CREATE TABLE IF NOT EXISTS profile_addresses (
   pid INT(11) DEFAULT NULL,
   jobid INT(6) UNSIGNED DEFAULT NULL,
@@ -18,7 +20,7 @@ CREATE TABLE IF NOT EXISTS profile_addresses (
   south FLOAT(10,7) DEFAULT NULL,
   east FLOAT(10,7) DEFAULT NULL,
   west FLOAT(10,7) DEFAULT NULL,
-  updateTime DATE NOT NULL DEFAULT 0,
+  updateTime DATETIME NOT NULL DEFAULT 0,
   pub ENUM('public','ax','private') NOT NULL DEFAULT 'private',
   comment VARCHAR(255) DEFAULT NULL,
   PRIMARY KEY(pid, jobid, type, id),
@@ -30,12 +32,22 @@ CREATE TABLE IF NOT EXISTS profile_addresses (
   INDEX administrativeAreaId (administrativeAreaId),
   INDEX subAdministrativeAreaId (subAdministrativeAreaId),
   INDEX countryId (countryId)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
+
+INSERT INTO  profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude, countryId,
+                                type, flags)
+     SELECT  uid, adrid, postcode, datemaj, pub, comment, glat, glng, country,
+             IF(FIND_IN_SET('pro', 'statut'), 'job', 'home'),
+             CONCAT(IF(FIND_IN_SET('res-secondaire', 'statut'), 'secondary,', ''),
+                    IF(FIND_IN_SET('courrier', 'statut'), 'mail,', ''),
+                    IF(FIND_IN_SET('active', 'statut'), 'current,', ''),
+                    IF(FIND_IN_SET('temporaire', 'statut'), 'temporary', ''))
+       FROM  #x4dat#.adresses;
 
 CREATE TABLE IF NOT EXISTS geoloc_countries (
-  iso_3166_1 CHAR(2) NOT NULL,
-  iso_3166_2 CHAR(3) NOT NULL,
-  iso_3166_3 SMALLINT(3) UNSIGNED NOT NULL,
+  iso_3166_1_a2 CHAR(2) NOT NULL,
+  iso_3166_1_a3 CHAR(3) NOT NULL,
+  iso_3166_1_num SMALLINT(3) UNSIGNED NOT NULL,
   worldRegion CHAR(2) DEFAULT NULL,
   countryFR VARCHAR(255) NOT NULL,
   country VARCHAR(255) NOT NULL,
@@ -46,18 +58,29 @@ CREATE TABLE IF NOT EXISTS geoloc_countries (
   phoneFormat VARCHAR(255) NOT NULL,
   licensePlate CHAR(4) DEFAULT NULL,
   belongsTo CHAR(2) DEFAULT NULL,
-  PRIMARY KEY(iso_3166_1),
-  INDEX(iso_3166_1),
+  PRIMARY KEY(iso_3166_1_a2),
+  UNIQUE KEY(iso_3166_1_a3),
+  UNIQUE KEY(iso_3166_1_num),
+  INDEX(iso_3166_1_a2),
   INDEX(phonePrefix)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
+
+UPDATE  geoloc_pays
+   SET  n3 = 450
+ WHERE  a2 = "MG";
+
+UPDATE  geoloc_pays
+   SET  n3 = 807
+ WHERE  a2 = "MK";
 
-INSERT INTO  geoloc_countries (iso_3166_1, iso_3166_2, iso_3166_3, worldRegion,
+INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion,
                                countryFR, country, capital, nationalityFR,
                                phonePrefix, phoneFormat, licensePlate)
      SELECT  a2, a3, n3, worldrgn, pays, country, capital, nat, phoneprf, phoneformat, license_plate
        FROM  geoloc_pays;
+DROP TABLE geoloc_pays;
 
-CREATE TABLE IF NOT EXISTS geoloc_administrativeArea (
+CREATE TABLE IF NOT EXISTS geoloc_administrativeareas (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   country CHAR(2) NOT NULL,
@@ -66,9 +89,9 @@ CREATE TABLE IF NOT EXISTS geoloc_administrativeArea (
   INDEX(id),
   INDEX(name),
   INDEX(country)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
 
-CREATE TABLE IF NOT EXISTS geoloc_subAdministrativeArea (
+CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   country CHAR(2) NOT NULL,
@@ -77,9 +100,9 @@ CREATE TABLE IF NOT EXISTS geoloc_subAdministrativeArea (
   INDEX(id),
   INDEX(name),
   INDEX(country)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
 
-CREATE TABLE IF NOT EXISTS geoloc_locality (
+CREATE TABLE IF NOT EXISTS geoloc_localities (
   id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   country CHAR(2) NOT NULL,
@@ -88,6 +111,6 @@ CREATE TABLE IF NOT EXISTS geoloc_locality (
   INDEX(id),
   INDEX(name),
   INDEX(country)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
 
 -- vim:set syntax=mysql: