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