upgrade/newdirectory-0.0.1/15_addresses.sql

   1 DROP TABLE IF EXISTS profile_addresses;
   2
   3 CREATE TABLE profile_addresses (
   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,
  23   updateTime DATETIME NOT NULL DEFAULT 0,
  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)
  35 ) ENGINE=InnoDB, CHARSET=utf8;
  36
  37 INSERT INTO  profile_addresses (pid, id, postalCode, updateTime, pub, comment, latitude, longitude,
  38                                 countryId, type, flags)
  39      SELECT  uid, adrid, postcode, datemaj, pub, NULL, glat, glng, IF(country = '' OR country = '00', NULL, country),
  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', ''))
  45        FROM  #x4dat#.adresses;
  46
  47 INSERT INTO  profile_addresses (pid, jobid, id, postalCode, pub, comment,
  48                                 latitude, longitude, countryId, type)
  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);
  53
  54 DROP TABLE IF EXISTS geoloc_countries;
  55 CREATE TABLE geoloc_countries (
  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,
  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,
  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),
  73   INDEX(phonePrefix)
  74 ) ENGINE=InnoDB, CHARSET=utf8;
  75
  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;
  82
  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');
 287
 288 INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion,
 289                                countryFR, country, capital, nationalityFR,
 290                                phonePrefix, phoneFormat, licensePlate)
 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,
 297              phoneprf, '', t.code
 298        FROM  #x4dat#.geoloc_pays AS g
 299  INNER JOIN  tmp_update_geoloc_pays AS t ON (t.a2 = g.a2);
 300
 301 DROP TABLE IF EXISTS geoloc_administrativeareas;
 302 CREATE TABLE geoloc_administrativeareas (
 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)
 311 ) ENGINE=InnoDB, CHARSET=utf8;
 312
 313 DROP TABLE IF EXISTS geoloc_subadministrativeareas;
 314 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
 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)
 323 ) ENGINE=InnoDB, CHARSET=utf8;
 324
 325 DROP TABLE IF EXISTS geoloc_localities;
 326 CREATE TABLE IF NOT EXISTS geoloc_localities (
 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)
 335 ) ENGINE=InnoDB, CHARSET=utf8;
 336
 337 -- vim:set syntax=mysql: