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

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',
  id TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  flags SET('current','temporary','secondary','mail','cedex') DEFAULT NULL,
  accuracy TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  text TEXT NOT NULL,
  postalText TEXT NOT NULL,
  postalCode VARCHAR(255) DEFAULT NULL,
  localityId INT(11) DEFAULT NULL,
  subAdministrativeAreaId INT(11) DEFAULT NULL,
  administrativeAreaId INT(11) DEFAULT NULL,
  countryId CHAR(2) DEFAULT NULL,
  latitude FLOAT(10,7) DEFAULT NULL,
  longitude FLOAT(10,7) DEFAULT NULL,
  north FLOAT(10,7) DEFAULT NULL,
  south FLOAT(10,7) DEFAULT NULL,
  east FLOAT(10,7) DEFAULT NULL,
  west FLOAT(10,7) DEFAULT NULL,
  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),
  INDEX pid (pid),
  INDEX jobid (jobid),
  INDEX type (type),
  INDEX adrid (id),
  INDEX localityId (localityId),
  INDEX administrativeAreaId (administrativeAreaId),
  INDEX subAdministrativeAreaId (subAdministrativeAreaId),
  INDEX countryId (countryId)
) 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  uid, entrid, 0, postcode, adr_pub, NULL, glat, glng,
             IF(country = '' OR country = '00', NULL, country), 'job'
       FROM  #x4dat#.entreprises;

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,
  worldRegion CHAR(2) DEFAULT NULL,
  countryFR VARCHAR(255) NOT NULL,
  country VARCHAR(255) NOT NULL,
  capital VARCHAR(255) NOT NULL,
  nationalityFR VARCHAR(255) DEFAULT NULL,
  nationality VARCHAR(255) DEFAULT NULL,
  phonePrefix SMALLINT(5) UNSIGNED DEFAULT NULL,
  phoneFormat VARCHAR(255) NOT NULL,
  licensePlate CHAR(4) DEFAULT NULL,
  belongsTo CHAR(2) DEFAULT NULL,
  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)
) ENGINE=InnoDB, CHARSET=utf8;

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;

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  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);

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,
  PRIMARY KEY(id),
  UNIQUE KEY(id, name, country),
  INDEX(id),
  INDEX(name),
  INDEX(country)
) 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,
  country CHAR(2) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY(id, name, country),
  INDEX(id),
  INDEX(name),
  INDEX(country)
) 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,
  country CHAR(2) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY(id, name, country),
  INDEX(id),
  INDEX(name),
  INDEX(country)
) ENGINE=InnoDB, CHARSET=utf8;

-- vim:set syntax=mysql:
Commit	Line	Data
8f2f5083 SJ	1	DROP TABLE IF EXISTS profile_addresses;
8f2f5083 SJ	2
d0293d9b	3	CREATE TABLE profile_addresses (
041a5cec SJ	4	pid INT(11) DEFAULT NULL,
	5	jobid INT(6) UNSIGNED DEFAULT NULL,
	6	type ENUM('home','job','hq') NOT NULL DEFAULT 'home',
	7	id TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
	8	flags SET('current','temporary','secondary','mail','cedex') DEFAULT NULL,
	9	accuracy TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
	10	text TEXT NOT NULL,
	11	postalText TEXT NOT NULL,
	12	postalCode VARCHAR(255) DEFAULT NULL,
	13	localityId INT(11) DEFAULT NULL,
	14	subAdministrativeAreaId INT(11) DEFAULT NULL,
	15	administrativeAreaId INT(11) DEFAULT NULL,
	16	countryId CHAR(2) DEFAULT NULL,
	17	latitude FLOAT(10,7) DEFAULT NULL,
	18	longitude FLOAT(10,7) DEFAULT NULL,
	19	north FLOAT(10,7) DEFAULT NULL,
	20	south FLOAT(10,7) DEFAULT NULL,
	21	east FLOAT(10,7) DEFAULT NULL,
	22	west FLOAT(10,7) DEFAULT NULL,
eecbf7f5	23	updateTime DATETIME NOT NULL DEFAULT 0,
041a5cec SJ	24	pub ENUM('public','ax','private') NOT NULL DEFAULT 'private',
	25	comment VARCHAR(255) DEFAULT NULL,
	26	PRIMARY KEY(pid, jobid, type, id),
	27	INDEX pid (pid),
	28	INDEX jobid (jobid),
	29	INDEX type (type),
	30	INDEX adrid (id),
	31	INDEX localityId (localityId),
	32	INDEX administrativeAreaId (administrativeAreaId),
	33	INDEX subAdministrativeAreaId (subAdministrativeAreaId),
	34	INDEX countryId (countryId)
950bf4f6	35	) ENGINE=InnoDB, CHARSET=utf8;
041a5cec	36
30c41429	37	INSERT INTO profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude,
ff900fde SJ	38	countryId, type, flags)
ff900fde SJ	39	SELECT uid, adrid, postcode, datemaj, pub, NULL, glat, glng, IF(country = '' OR country = '00', NULL, country),
8f2f5083 SJ	40	IF(FIND_IN_SET('pro', 'statut'), 'job', 'home'),
	41	CONCAT(IF(FIND_IN_SET('res-secondaire', 'statut'), 'secondary,', ''),
	42	IF(FIND_IN_SET('courrier', 'statut'), 'mail,', ''),
	43	IF(FIND_IN_SET('active', 'statut'), 'current,', ''),
	44	IF(FIND_IN_SET('temporaire', 'statut'), 'temporary', ''))
100e66fc	45	FROM #x4dat#.adresses;
8f2f5083	46
d989355f SJ	47	INSERT INTO profile_addresses (pid, jobid, id, postalCode, pub, comment,
	48	latitude, longitude, countryId, type)
	49	SELECT uid, entrid, 0, postcode, adr_pub, NULL, glat, glng,
	50	IF(country = '' OR country = '00', NULL, country), 'job'
	51	FROM #x4dat#.entreprises;
	52
d0293d9b FB	53	DROP TABLE IF EXISTS geoloc_countries;
d0293d9b FB	54	CREATE TABLE geoloc_countries (
644f8ee5 SJ	55	iso_3166_1_a2 CHAR(2) NOT NULL,
	56	iso_3166_1_a3 CHAR(3) NOT NULL,
	57	iso_3166_1_num SMALLINT(3) UNSIGNED NOT NULL,
041a5cec SJ	58	worldRegion CHAR(2) DEFAULT NULL,
	59	countryFR VARCHAR(255) NOT NULL,
	60	country VARCHAR(255) NOT NULL,
	61	capital VARCHAR(255) NOT NULL,
	62	nationalityFR VARCHAR(255) DEFAULT NULL,
	63	nationality VARCHAR(255) DEFAULT NULL,
	64	phonePrefix SMALLINT(5) UNSIGNED DEFAULT NULL,
	65	phoneFormat VARCHAR(255) NOT NULL,
	66	licensePlate CHAR(4) DEFAULT NULL,
	67	belongsTo CHAR(2) DEFAULT NULL,
644f8ee5 SJ	68	PRIMARY KEY(iso_3166_1_a2),
	69	UNIQUE KEY(iso_3166_1_a3),
	70	UNIQUE KEY(iso_3166_1_num),
	71	INDEX(iso_3166_1_a2),
041a5cec	72	INDEX(phonePrefix)
950bf4f6	73	) ENGINE=InnoDB, CHARSET=utf8;
041a5cec	74
fcff94a5 FB	75	CREATE TEMPORARY TABLE tmp_update_geoloc_pays (
	76	a2 CHAR(2) DEFAULT NULL,
	77	code CHAR(4) DEFAULT NULL,
	78	PRIMARY KEY(a2),
	79	UNIQUE KEY(code)
	80	) ENGINE=InnoDB, CHARSET=utf8;
644f8ee5	81
fcff94a5 FB	82	INSERT IGNORE INTO tmp_update_geoloc_pays (a2, code)
	83	VALUES ('AF', 'AFG'),
	84	('ZA', 'ZA'),
	85	('AL', 'AL'),
	86	('DZ', 'DZ'),
	87	('DE', 'D'),
	88	('AD', 'AND'),
	89	('AO', 'ANG'),
	90	('AG', 'AG'),
	91	('AN', 'NA'),
	92	('SA', 'KSA'),
	93	('AR', 'RA'),
	94	('AM', 'ARM'),
	95	('AU', 'AUS'),
	96	('AT', 'A'),
	97	('AZ', 'AZ'),
	98	('BS', 'BS'),
	99	('BH', 'BRN'),
	100	('BD', 'BD'),
	101	('BB', 'BDS'),
	102	('BY', 'BY'),
	103	('BE', 'B'),
	104	('BZ', 'BZ'),
	105	('BJ', 'DY'),
	106	('BM', ''),
	107	('BT', 'BHT'),
	108	('BO', 'BOL'),
	109	('BA', 'BIH'),
	110	('BW', 'RB'),
	111	('BR', 'BR'),
	112	('BN', 'BRU'),
	113	('BG', 'BG'),
	114	('BF', 'BF'),
	115	('BI', 'BU'),
	116	('KH', 'K'),
	117	('CM', 'CAM'),
	118	('CA', 'CDN'),
	119	('CV', 'CV'),
	120	('CF', 'RCA'),
	121	('CG', 'CGO'),
	122	('CL', 'RCH'),
	123	('CN', 'CN'),
	124	('CY', 'CY'),
	125	('CO', 'CO'),
	126	('KM', 'COM'),
	127	('CG', 'RCB'),
	128	('CD', 'CD'),
	129	('KR', 'ROK'),
	130	('KP', 'DVRK'),
	131	('CR', 'CR'),
	132	('CI', 'CI'),
	133	('HR', 'HR'),
	134	('CU', 'C'),
	135	('DK', 'DK'),
	136	('DJ', 'DJI'),
	137	('DO', 'DOM'),
	138	('DM', 'WD'),
	139	('EG', 'ET'),
	140	('SV', 'ES'),
	141	('AE', 'UAE'),
	142	('EC', 'EC'),
	143	('ER', 'ER'),
	144	('ES', 'E'),
	145	('EE', 'EST'),
146	('US', 'USA'),
147	('ET', 'ETH'),
148	('FO', 'FR'),
149	('FJ', 'FJI'),
150	('FI', 'FIN'),
151	('FR', 'F'),
152	('GA', 'G'),
153	('GM', 'WAG'),
154	('GE', 'GE'),
155	('GH', 'GH'),
156	('GI', 'GBZ'),
157	('GR', 'GR'),
158	('GD', 'WG'),
159	('GL', 'KN'),
160	('GT', 'GCA'),
161	('GN', 'RG'),
162	('GQ', 'GQ'),
163	('GY', 'GUY'),
164	('HT', 'RH'),
165	('HN', 'HN'),
166	('HK', 'HK'),
167	('HU', 'H'),
168	('VG', 'BVI'),
169	('IN', 'IND'),
170	('ID', 'RI'),
171	('IR', 'IR'),
172	('IQ', 'IRQ'),
173	('IE', 'IRL'),
174	('IS', 'IS'),
175	('IL', 'IL'),
176	('IT', 'I'),
177	('JM', 'JA'),
178	('JP', 'J'),
179	('JO', 'JOR'),
180	('KZ', 'KZ'),
181	('KE', 'EAK'),
182	('KG', 'KS'),
183	('KI', 'KIR'),
184	('KW', 'KWT'),
185	('LA', 'LAO'),
186	('LS', 'LS'),
187	('LV', 'LV'),
188	('LB', 'RL'),
189	('LR', 'LB'),
190	('LY', 'LAR'),
191	('LI', 'FL'),
192	('LT', 'LT'),
193	('LU', 'L'),
194	('MK', 'MK'),
195	('MG', 'RM'),
196	('MY', 'MAL'),
197	('MW', 'MW'),
198	('MV', 'MV'),
199	('ML', 'RMM'),
200	('MT', 'M'),
201	('MA', 'MA'),
202	('MH', 'MH'),
203	('MU', 'MS'),
204	('MR', 'RIM'),
205	('MX', 'MEX'),
206	('FM', 'FSM'),
207	('MD', 'MD'),
208	('MC', 'MC'),
209	('MN', 'MGL'),
210	('MZ', 'MOC'),
211	('MM', 'MYA'),
212	('NA', 'NAM'),
213	('NR', 'NAU'),
214	('NP', 'NEP'),
215	('NI', 'NIC'),
216	('NE', 'RN'),
217	('NG', 'WAN'),
218	('NO', 'N'),
219	('NZ', 'NZ'),
220	('OM', 'OM'),
221	('UG', 'EAU'),
222	('UZ', 'UZ'),
223	('PK', 'PK'),
224	('PW', 'PAL'),
225	('PS', 'PS'),
226	('PA', 'PA'),
227	('PG', 'PNG'),
228	('PY', 'PY'),
229	('NL', 'NL'),
230	('PE', 'PE'),
231	('PH', 'RP'),
232	('PL', 'PL'),
233	('PT', 'P'),
234	('QA', 'Q'),
235	('RE', 'RE'),
236	('RO', 'RO'),
237	('GB', 'GB'),
238	('RU', 'RUS'),
239	('RW', 'RWA'),
240	('LC', 'WL'),
241	('KN', 'SCN'),
242	('SM', 'RSM'),
243	('VA', 'V'),
244	('VC', 'WV'),
245	('SB', 'SOL'),
246	('WS', 'WS'),
247	('ST', 'STP'),
248	('SN', 'SN'),
249	('CS', 'SCG'),
250	('SC', 'SY'),
251	('SL', 'WAL'),
252	('SG', 'SGP'),
253	('SK', 'SK'),
254	('SI', 'SLO'),
255	('SO', 'SP'),
256	('SD', 'SUD'),
257	('LK', 'CL'),
258	('SE', 'S'),
259	('CH', 'CH'),
260	('SR', 'SME'),
261	('SZ', 'SD'),
262	('SY', 'SYR'),
263	('TJ', 'TJ'),
264	('TW', 'RC'),
265	('TZ', 'EAT'),
266	('TD', 'TCH'),
267	('CZ', 'CZ'),
268	('TH', 'THA'),
269	('TL', 'TL'),
270	('TG', 'RT'),
271	('TO', 'TO'),
272	('TT', 'TT'),
273	('TN', 'TN'),
274	('TM', 'TM'),
275	('TR', 'TR'),
276	('TV', 'TUV'),
277	('UA', 'UA'),
278	('UY', 'ROU'),
279	('VU', 'VU'),
280	('VE', 'YV'),
281	('VN', 'VN'),
282	('YE', 'YAR'),
283	('YU', 'YU'),
284	('ZM', 'Z'),
285	('ZW', 'ZW');
d0293d9b	286
644f8ee5	287	INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion,
041a5cec SJ	288	countryFR, country, capital, nationalityFR,
041a5cec SJ	289	phonePrefix, phoneFormat, licensePlate)
fcff94a5 FB	290	SELECT g.a2, g.a3,
	291	CASE g.a2 WHEN "MG" THEN 450
	292	WHEN "MK" THEN 807
	293	WHEN "ER" THEN 232
	294	ELSE n3 END,
	295	worldrgn, pays, country, capital, nat,
ff900fde	296	phoneprf, '', t.code
fcff94a5 FB	297	FROM #x4dat#.geoloc_pays AS g
fcff94a5 FB	298	INNER JOIN tmp_update_geoloc_pays AS t ON (t.a2 = g.a2);
041a5cec	299
d0293d9b FB	300	DROP TABLE IF EXISTS geoloc_administrativeareas;
d0293d9b FB	301	CREATE TABLE geoloc_administrativeareas (
041a5cec SJ	302	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	303	name VARCHAR(255) NOT NULL,
	304	country CHAR(2) NOT NULL,
	305	PRIMARY KEY(id),
	306	UNIQUE KEY(id, name, country),
	307	INDEX(id),
	308	INDEX(name),
	309	INDEX(country)
950bf4f6	310	) ENGINE=InnoDB, CHARSET=utf8;
041a5cec	311
d0293d9b	312	DROP TABLE IF EXISTS geoloc_subadministrativeareas;
4c906759	313	CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
041a5cec SJ	314	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	315	name VARCHAR(255) NOT NULL,
	316	country CHAR(2) NOT NULL,
	317	PRIMARY KEY(id),
	318	UNIQUE KEY(id, name, country),
	319	INDEX(id),
	320	INDEX(name),
	321	INDEX(country)
950bf4f6	322	) ENGINE=InnoDB, CHARSET=utf8;
041a5cec	323
d0293d9b	324	DROP TABLE IF EXISTS geoloc_localities;
4c906759	325	CREATE TABLE IF NOT EXISTS geoloc_localities (
041a5cec SJ	326	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	327	name VARCHAR(255) NOT NULL,
	328	country CHAR(2) NOT NULL,
	329	PRIMARY KEY(id),
	330	UNIQUE KEY(id, name, country),
	331	INDEX(id),
	332	INDEX(name),
	333	INDEX(country)
950bf4f6	334	) ENGINE=InnoDB, CHARSET=utf8;
041a5cec SJ	335
041a5cec SJ	336	-- vim:set syntax=mysql: