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 d8f34ce..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,7 +32,17 @@ 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_a2 CHAR(2) NOT NULL,
@@ -51,7 +63,7 @@ CREATE TABLE IF NOT EXISTS geoloc_countries (
   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
@@ -66,6 +78,7 @@ INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, wor
                                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_administrativeareas (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
@@ -76,7 +89,7 @@ CREATE TABLE IF NOT EXISTS geoloc_administrativeareas (
   INDEX(id),
   INDEX(name),
   INDEX(country)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
 
 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
@@ -87,7 +100,7 @@ CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
   INDEX(id),
   INDEX(name),
   INDEX(country)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
 
 CREATE TABLE IF NOT EXISTS geoloc_localities (
   id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
@@ -98,6 +111,6 @@ CREATE TABLE IF NOT EXISTS geoloc_localities (
   INDEX(id),
   INDEX(name),
   INDEX(country)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
 
 -- vim:set syntax=mysql: