0337d704 |
1 | |
2 | alter table `nationalites` add column iso3166 varchar(2) not null; |
3 | |
4 | replace INTO `nationalites` VALUES (1,'Albanais','AL'); |
5 | replace INTO `nationalites` VALUES (2,'Algérien','DZ'); |
6 | replace INTO `nationalites` VALUES (3,'Allemand','DE'); |
7 | replace INTO `nationalites` VALUES (4,'Américain','US'); |
8 | replace INTO `nationalites` VALUES (5,'Argentin','AR'); |
9 | replace INTO `nationalites` VALUES (6,'Australien','AU'); |
10 | replace INTO `nationalites` VALUES (7,'Autrichien','AT'); |
11 | replace INTO `nationalites` VALUES (8,'Belge','BE'); |
12 | replace INTO `nationalites` VALUES (9,'Benin','BJ'); |
13 | replace INTO `nationalites` VALUES (10,'Botswanais','BW'); |
14 | replace INTO `nationalites` VALUES (11,'Bouthan','BT'); |
15 | replace INTO `nationalites` VALUES (12,'Brésilien','BR'); |
16 | replace INTO `nationalites` VALUES (13,'Britannique','GB'); |
17 | replace INTO `nationalites` VALUES (14,'Bulgare','BG'); |
18 | replace INTO `nationalites` VALUES (15,'Burkina Faso','BF'); |
19 | replace INTO `nationalites` VALUES (16,'Cambodgien','KH'); |
20 | replace INTO `nationalites` VALUES (17,'Camerounais','CM'); |
21 | replace INTO `nationalites` VALUES (18,'Canadien','CA'); |
22 | replace INTO `nationalites` VALUES (19,'Chilien','CL'); |
23 | replace INTO `nationalites` VALUES (20,'Chinois','CN'); |
24 | replace INTO `nationalites` VALUES (21,'Colombien','CO'); |
25 | replace INTO `nationalites` VALUES (22,'Coréen','KR'); |
26 | replace INTO `nationalites` VALUES (86,'Costaricien','CR'); |
27 | replace INTO `nationalites` VALUES (80,'Croate','HR'); |
28 | replace INTO `nationalites` VALUES (23,'Cubain','CU'); |
29 | replace INTO `nationalites` VALUES (24,'Danois','DK'); |
30 | replace INTO `nationalites` VALUES (26,'Egyptien','EG'); |
31 | replace INTO `nationalites` VALUES (81,'Equatorien','EC'); |
32 | replace INTO `nationalites` VALUES (27,'Espagnol','ES'); |
33 | replace INTO `nationalites` VALUES (28,'Estonien','EE'); |
34 | replace INTO `nationalites` VALUES (29,'Finlandais','FI'); |
35 | replace INTO `nationalites` VALUES (30,'Français','FR'); |
36 | replace INTO `nationalites` VALUES (31,'Gabonais','GA'); |
37 | replace INTO `nationalites` VALUES (32,'Georgien','GE'); |
38 | replace INTO `nationalites` VALUES (33,'Grec','GR'); |
39 | replace INTO `nationalites` VALUES (34,'Haïtien','HT'); |
40 | replace INTO `nationalites` VALUES (36,'Hong-Kong','HK'); |
41 | replace INTO `nationalites` VALUES (37,'Hongrois','HU'); |
42 | replace INTO `nationalites` VALUES (38,'Indien','IN'); |
43 | replace INTO `nationalites` VALUES (39,'Iranien','IR'); |
44 | replace INTO `nationalites` VALUES (40,'Irlandais','IE'); |
45 | replace INTO `nationalites` VALUES (41,'Islandais','IS'); |
46 | replace INTO `nationalites` VALUES (42,'Israélien','IL'); |
47 | replace INTO `nationalites` VALUES (43,'Italien','IT'); |
48 | replace INTO `nationalites` VALUES (44,'Ivoirien','CI'); |
49 | replace INTO `nationalites` VALUES (45,'Jamaïcain','JM'); |
50 | replace INTO `nationalites` VALUES (46,'Japonais','JP'); |
51 | replace INTO `nationalites` VALUES (47,'Kazakh','KZ'); |
52 | replace INTO `nationalites` VALUES (49,'Lettonien','LV'); |
53 | replace INTO `nationalites` VALUES (50,'Libanais','LB'); |
54 | replace INTO `nationalites` VALUES (51,'Lituanien','LT'); |
55 | replace INTO `nationalites` VALUES (52,'Luxembourgeois','LU'); |
56 | replace INTO `nationalites` VALUES (53,'Macédonien','MK'); |
57 | replace INTO `nationalites` VALUES (82,'Malgache','MG'); |
58 | replace INTO `nationalites` VALUES (54,'Malien','ML'); |
59 | replace INTO `nationalites` VALUES (55,'Marocain','MA'); |
60 | replace INTO `nationalites` VALUES (56,'Mexicain','MX'); |
61 | replace INTO `nationalites` VALUES (35,'Néerlandais','NL'); |
62 | replace INTO `nationalites` VALUES (57,'Néo-Zélandais','NZ'); |
63 | replace INTO `nationalites` VALUES (58,'Norvégien','NO'); |
64 | replace INTO `nationalites` VALUES (59,'Palestinien','PS'); |
65 | replace INTO `nationalites` VALUES (60,'Péruvien','PE'); |
66 | replace INTO `nationalites` VALUES (61,'Polonais','PL'); |
67 | replace INTO `nationalites` VALUES (62,'Portugais','PT'); |
68 | replace INTO `nationalites` VALUES (63,'Roumain','RO'); |
69 | replace INTO `nationalites` VALUES (64,'Russe','RU'); |
70 | replace INTO `nationalites` VALUES (65,'Sénégalais','SN'); |
71 | replace INTO `nationalites` VALUES (66,'Serbe','CS'); |
72 | replace INTO `nationalites` VALUES (84,'Singapourien','SG'); |
73 | replace INTO `nationalites` VALUES (67,'Slovaque','SI'); |
74 | replace INTO `nationalites` VALUES (69,'Sud-Africain','ZA'); |
75 | replace INTO `nationalites` VALUES (70,'Suédois','SE'); |
76 | replace INTO `nationalites` VALUES (71,'Suisse','CH'); |
77 | replace INTO `nationalites` VALUES (72,'Tadjik','TJ'); |
78 | replace INTO `nationalites` VALUES (73,'Taïwanais','TW'); |
79 | replace INTO `nationalites` VALUES (74,'Tchèque','CZ'); |
80 | replace INTO `nationalites` VALUES (75,'Tunisien','TN'); |
81 | replace INTO `nationalites` VALUES (76,'Turc','TR'); |
82 | replace INTO `nationalites` VALUES (83,'Ukrainien','UA'); |
83 | replace INTO `nationalites` VALUES (85,'Uruguayen','UY'); |
84 | replace INTO `nationalites` VALUES (77,'Vénézuélien','VE'); |
85 | replace INTO `nationalites` VALUES (78,'Vietnamien','VN'); |
86 | replace INTO `nationalites` VALUES (79,'Yougoslave','CS'); |
87 | |
88 | alter table geoloc_pays add column nat varchar(100) not null; |
89 | update geoloc_pays inner join nationalites ON iso3166=a2 set nat=text; |
90 | update auth_user_md5 set nationalite='' where nationalite='0' or nationalite='36'; |
91 | alter table auth_user_md5 change column nationalite nationalite varchar(2) not null; |
92 | update auth_user_md5 AS u |
93 | inner join nationalites AS n ON u.nationalite=n.id |
94 | inner join geoloc_pays AS g ON iso3166=a2 |
95 | set u.nationalite = a2; |
96 | |
97 | drop table nationalites; |