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