upgrade/0.9.2/nationalites.sql

   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;