Merge remote branch 'origin/platal-1.0.0'

[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..8ce64b6 100644 (file)
--- a/upgrade/newdirectory-0.0.1/15_addresses.sql
+++ b/upgrade/newdirectory-0.0.1/15_addresses.sql
@@ -1,4 +1,6 @@
-CREATE TABLE IF NOT EXISTS profile_addresses (
+DROP TABLE IF EXISTS profile_addresses;
+
+CREATE TABLE profile_addresses (

   pid INT(11) DEFAULT NULL,
   jobid INT(6) UNSIGNED DEFAULT NULL,
   type ENUM('home','job','hq') NOT NULL DEFAULT 'home',
   pid INT(11) DEFAULT NULL,
   jobid INT(6) UNSIGNED DEFAULT NULL,
   type ENUM('home','job','hq') NOT NULL DEFAULT 'home',


@@ -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,
   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),
   pub ENUM('public','ax','private') NOT NULL DEFAULT 'private',
   comment VARCHAR(255) DEFAULT NULL,
   PRIMARY KEY(pid, jobid, type, id),


@@ -30,9 +32,27 @@ CREATE TABLE IF NOT EXISTS profile_addresses (
   INDEX administrativeAreaId (administrativeAreaId),
   INDEX subAdministrativeAreaId (subAdministrativeAreaId),
   INDEX countryId (countryId)
   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, NULL, glat, glng, IF(country = '' OR country = '00', NULL, 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;
+
+INSERT INTO  profile_addresses (pid, jobid, id, postalCode, pub, comment,
+                                latitude, longitude, countryId, type)
+     SELECT  e.uid, j.id, e.entrid, e.postcode, e.adr_pub, NULL, e.glat, e.glng,
+             IF(e.country = '' OR e.country = '00', NULL, e.country), 'job'
+       FROM  #x4dat#.entreprises AS e
+ INNER JOIN  profile_job_enum    AS j ON (e.entreprise = j.name);

 
 

-CREATE TABLE IF NOT EXISTS geoloc_countries (
+DROP TABLE IF EXISTS geoloc_countries;
+CREATE TABLE geoloc_countries (

   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,
   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,


@@ -51,23 +71,235 @@ CREATE TABLE IF NOT EXISTS geoloc_countries (
   UNIQUE KEY(iso_3166_1_num),
   INDEX(iso_3166_1_a2),
   INDEX(phonePrefix)
   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";
+CREATE TEMPORARY TABLE tmp_update_geoloc_pays (
+  a2 CHAR(2) DEFAULT NULL,
+  code CHAR(4) DEFAULT NULL,
+  PRIMARY KEY(a2),
+  UNIQUE KEY(code)
+) ENGINE=InnoDB, CHARSET=utf8;

 
 

-UPDATE  geoloc_pays
-   SET  n3 = 807
- WHERE  a2 = "MK";
+INSERT IGNORE INTO  tmp_update_geoloc_pays (a2, code)
+            VALUES  ('AF', 'AFG'),
+                    ('ZA', 'ZA'),
+                    ('AL', 'AL'),
+                    ('DZ', 'DZ'),
+                    ('DE', 'D'),
+                    ('AD', 'AND'),
+                    ('AO', 'ANG'),
+                    ('AG', 'AG'),
+                    ('AN', 'NA'),
+                    ('SA', 'KSA'),
+                    ('AR', 'RA'),
+                    ('AM', 'ARM'),
+                    ('AU', 'AUS'),
+                    ('AT', 'A'),
+                    ('AZ', 'AZ'),
+                    ('BS', 'BS'),
+                    ('BH', 'BRN'),
+                    ('BD', 'BD'),
+                    ('BB', 'BDS'),
+                    ('BY', 'BY'),
+                    ('BE', 'B'),
+                    ('BZ', 'BZ'),
+                    ('BJ', 'DY'),
+                    ('BM', ''),
+                    ('BT', 'BHT'),
+                    ('BO', 'BOL'),
+                    ('BA', 'BIH'),
+                    ('BW', 'RB'),
+                    ('BR', 'BR'),
+                    ('BN', 'BRU'),
+                    ('BG', 'BG'),
+                    ('BF', 'BF'),
+                    ('BI', 'BU'),
+                    ('KH', 'K'),
+                    ('CM', 'CAM'),
+                    ('CA', 'CDN'),
+                    ('CV', 'CV'),
+                    ('CF', 'RCA'),
+                    ('CG', 'CGO'),
+                    ('CL', 'RCH'),
+                    ('CN', 'CN'),
+                    ('CY', 'CY'),
+                    ('CO', 'CO'),
+                    ('KM', 'COM'),
+                    ('CG', 'RCB'),
+                    ('CD', 'CD'),
+                    ('KR', 'ROK'),
+                    ('KP', 'DVRK'),
+                    ('CR', 'CR'),
+                    ('CI', 'CI'),
+                    ('HR', 'HR'),
+                    ('CU', 'C'),
+                    ('DK', 'DK'),
+                    ('DJ', 'DJI'),
+                    ('DO', 'DOM'),
+                    ('DM', 'WD'),
+                    ('EG', 'ET'),
+                    ('SV', 'ES'),
+                    ('AE', 'UAE'),
+                    ('EC', 'EC'),
+                    ('ER', 'ER'),
+                    ('ES', 'E'),
+                    ('EE', 'EST'),
+                    ('US', 'USA'),
+                    ('ET', 'ETH'),
+                    ('FO', 'FR'),
+                    ('FJ', 'FJI'),
+                    ('FI', 'FIN'),
+                    ('FR', 'F'),
+                    ('GA', 'G'),
+                    ('GM', 'WAG'),
+                    ('GE', 'GE'),
+                    ('GH', 'GH'),
+                    ('GI', 'GBZ'),
+                    ('GR', 'GR'),
+                    ('GD', 'WG'),
+                    ('GL', 'KN'),
+                    ('GT', 'GCA'),
+                    ('GN', 'RG'),
+                    ('GQ', 'GQ'),
+                    ('GY', 'GUY'),
+                    ('HT', 'RH'),
+                    ('HN', 'HN'),
+                    ('HK', 'HK'),
+                    ('HU', 'H'),
+                    ('VG', 'BVI'),
+                    ('IN', 'IND'),
+                    ('ID', 'RI'),
+                    ('IR', 'IR'),
+                    ('IQ', 'IRQ'),
+                    ('IE', 'IRL'),
+                    ('IS', 'IS'),
+                    ('IL', 'IL'),
+                    ('IT', 'I'),
+                    ('JM', 'JA'),
+                    ('JP', 'J'),
+                    ('JO', 'JOR'),
+                    ('KZ', 'KZ'),
+                    ('KE', 'EAK'),
+                    ('KG', 'KS'),
+                    ('KI', 'KIR'),
+                    ('KW', 'KWT'),
+                    ('LA', 'LAO'),
+                    ('LS', 'LS'),
+                    ('LV', 'LV'),
+                    ('LB', 'RL'),
+                    ('LR', 'LB'),
+                    ('LY', 'LAR'),
+                    ('LI', 'FL'),
+                    ('LT', 'LT'),
+                    ('LU', 'L'),
+                    ('MK', 'MK'),
+                    ('MG', 'RM'),
+                    ('MY', 'MAL'),
+                    ('MW', 'MW'),
+                    ('MV', 'MV'),
+                    ('ML', 'RMM'),
+                    ('MT', 'M'),
+                    ('MA', 'MA'),
+                    ('MH', 'MH'),
+                    ('MU', 'MS'),
+                    ('MR', 'RIM'),
+                    ('MX', 'MEX'),
+                    ('FM', 'FSM'),
+                    ('MD', 'MD'),
+                    ('MC', 'MC'),
+                    ('MN', 'MGL'),
+                    ('MZ', 'MOC'),
+                    ('MM', 'MYA'),
+                    ('NA', 'NAM'),
+                    ('NR', 'NAU'),
+                    ('NP', 'NEP'),
+                    ('NI', 'NIC'),
+                    ('NE', 'RN'),
+                    ('NG', 'WAN'),
+                    ('NO', 'N'),
+                    ('NZ', 'NZ'),
+                    ('OM', 'OM'),
+                    ('UG', 'EAU'),
+                    ('UZ', 'UZ'),
+                    ('PK', 'PK'),
+                    ('PW', 'PAL'),
+                    ('PS', 'PS'),
+                    ('PA', 'PA'),
+                    ('PG', 'PNG'),
+                    ('PY', 'PY'),
+                    ('NL', 'NL'),
+                    ('PE', 'PE'),
+                    ('PH', 'RP'),
+                    ('PL', 'PL'),
+                    ('PT', 'P'),
+                    ('QA', 'Q'),
+                    ('RE', 'RE'),
+                    ('RO', 'RO'),
+                    ('GB', 'GB'),
+                    ('RU', 'RUS'),
+                    ('RW', 'RWA'),
+                    ('LC', 'WL'),
+                    ('KN', 'SCN'),
+                    ('SM', 'RSM'),
+                    ('VA', 'V'),
+                    ('VC', 'WV'),
+                    ('SB', 'SOL'),
+                    ('WS', 'WS'),
+                    ('ST', 'STP'),
+                    ('SN', 'SN'),
+                    ('CS', 'SCG'),
+                    ('SC', 'SY'),
+                    ('SL', 'WAL'),
+                    ('SG', 'SGP'),
+                    ('SK', 'SK'),
+                    ('SI', 'SLO'),
+                    ('SO', 'SP'),
+                    ('SD', 'SUD'),
+                    ('LK', 'CL'),
+                    ('SE', 'S'),
+                    ('CH', 'CH'),
+                    ('SR', 'SME'),
+                    ('SZ', 'SD'),
+                    ('SY', 'SYR'),
+                    ('TJ', 'TJ'),
+                    ('TW', 'RC'),
+                    ('TZ', 'EAT'),
+                    ('TD', 'TCH'),
+                    ('CZ', 'CZ'),
+                    ('TH', 'THA'),
+                    ('TL', 'TL'),
+                    ('TG', 'RT'),
+                    ('TO', 'TO'),
+                    ('TT', 'TT'),
+                    ('TN', 'TN'),
+                    ('TM', 'TM'),
+                    ('TR', 'TR'),
+                    ('TV', 'TUV'),
+                    ('UA', 'UA'),
+                    ('UY', 'ROU'),
+                    ('VU', 'VU'),
+                    ('VE', 'YV'),
+                    ('VN', 'VN'),
+                    ('YE', 'YAR'),
+                    ('YU', 'YU'),
+                    ('ZM', 'Z'),
+                    ('ZW', 'ZW');

 
 INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion,
                                countryFR, country, capital, nationalityFR,
                                phonePrefix, phoneFormat, licensePlate)
 
 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;
+     SELECT  g.a2, g.a3,
+             CASE g.a2 WHEN "MG" THEN 450
+                     WHEN "MK" THEN 807
+                     WHEN "ER" THEN 232
+                     ELSE n3 END,
+             worldrgn, pays, country, capital, nat,
+             phoneprf, '', t.code
+       FROM  #x4dat#.geoloc_pays AS g
+ INNER JOIN  tmp_update_geoloc_pays AS t ON (t.a2 = g.a2);

 
 

-CREATE TABLE IF NOT EXISTS geoloc_administrativeareas (
+DROP TABLE IF EXISTS geoloc_administrativeareas;
+CREATE TABLE geoloc_administrativeareas (

   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   country CHAR(2) NOT NULL,
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   country CHAR(2) NOT NULL,


@@ -76,8 +308,9 @@ CREATE TABLE IF NOT EXISTS geoloc_administrativeareas (
   INDEX(id),
   INDEX(name),
   INDEX(country)
   INDEX(id),
   INDEX(name),
   INDEX(country)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 

+DROP TABLE IF EXISTS geoloc_subadministrativeareas;

 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,


@@ -87,8 +320,9 @@ CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
   INDEX(id),
   INDEX(name),
   INDEX(country)
   INDEX(id),
   INDEX(name),
   INDEX(country)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 

+DROP TABLE IF EXISTS geoloc_localities;

 CREATE TABLE IF NOT EXISTS geoloc_localities (
   id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
 CREATE TABLE IF NOT EXISTS geoloc_localities (
   id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,


@@ -98,6 +332,6 @@ CREATE TABLE IF NOT EXISTS geoloc_localities (
   INDEX(id),
   INDEX(name),
   INDEX(country)
   INDEX(id),
   INDEX(name),
   INDEX(country)

-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;

 
 -- vim:set syntax=mysql:
 
 -- vim:set syntax=mysql: