DROP TABLE IF EXISTS profile_addresses;
-CREATE TABLE IF NOT 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',
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,
+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 adresses;
+ 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 (
+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);
+
+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,
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)
- 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,
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,
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,
INDEX(id),
INDEX(name),
INDEX(country)
-) CHARSET=utf8;
+) ENGINE=InnoDB, CHARSET=utf8;
-- vim:set syntax=mysql:
projects / platal.git / blobdiff