[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,
                                IF(countryId = '' OR countryId = '00', NULL, countryId), type, flags)
     SELECT  uid, adrid, postcode, datemaj, pub, NULL, 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;

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