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, countryId,
  38                                 type, flags)
  39      SELECT  uid, adrid, postcode, datemaj, pub, NULL, glat, glng, 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 DROP TABLE IF EXISTS geoloc_countries;
  48 CREATE TABLE geoloc_countries (
  49   iso_3166_1_a2 CHAR(2) NOT NULL,
  50   iso_3166_1_a3 CHAR(3) NOT NULL,
  51   iso_3166_1_num SMALLINT(3) UNSIGNED NOT NULL,
  52   worldRegion CHAR(2) DEFAULT NULL,
  53   countryFR VARCHAR(255) NOT NULL,
  54   country VARCHAR(255) NOT NULL,
  55   capital VARCHAR(255) NOT NULL,
  56   nationalityFR VARCHAR(255) DEFAULT NULL,
  57   nationality VARCHAR(255) DEFAULT NULL,
  58   phonePrefix SMALLINT(5) UNSIGNED DEFAULT NULL,
  59   phoneFormat VARCHAR(255) NOT NULL,
  60   licensePlate CHAR(4) DEFAULT NULL,
  61   belongsTo CHAR(2) DEFAULT NULL,
  62   PRIMARY KEY(iso_3166_1_a2),
  63   UNIQUE KEY(iso_3166_1_a3),
  64   UNIQUE KEY(iso_3166_1_num),
  65   INDEX(iso_3166_1_a2),
  66   INDEX(phonePrefix)
  67 ) ENGINE=InnoDB, CHARSET=utf8;
  68
  69 UPDATE  geoloc_pays
  70    SET  n3 = 450
  71  WHERE  a2 = "MG";
  72
  73 UPDATE  geoloc_pays
  74    SET  n3 = 807
  75  WHERE  a2 = "MK";
  76
  77 UPDATE  geoloc_pays
  78   SET   n3 = 232
  79  WHERE  a2 = "ER";
  80
  81 INSERT INTO  geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion,
  82                                countryFR, country, capital, nationalityFR,
  83                                phonePrefix, phoneFormat, licensePlate)
  84      SELECT  a2, a3, n3, worldrgn, pays, country, capital, nat, phoneprf, phoneformat, license_plate
  85        FROM  geoloc_pays;
  86
  87 DROP TABLE IF EXISTS geoloc_administrativeareas;
  88 CREATE TABLE geoloc_administrativeareas (
  89   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  90   name VARCHAR(255) NOT NULL,
  91   country CHAR(2) NOT NULL,
  92   PRIMARY KEY(id),
  93   UNIQUE KEY(id, name, country),
  94   INDEX(id),
  95   INDEX(name),
  96   INDEX(country)
  97 ) ENGINE=InnoDB, CHARSET=utf8;
  98
  99 DROP TABLE IF EXISTS geoloc_subadministrativeareas;
 100 CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas (
 101   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 102   name VARCHAR(255) NOT NULL,
 103   country CHAR(2) NOT NULL,
 104   PRIMARY KEY(id),
 105   UNIQUE KEY(id, name, country),
 106   INDEX(id),
 107   INDEX(name),
 108   INDEX(country)
 109 ) ENGINE=InnoDB, CHARSET=utf8;
 110
 111 DROP TABLE IF EXISTS geoloc_localities;
 112 CREATE TABLE IF NOT EXISTS geoloc_localities (
 113   id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 114   name VARCHAR(255) NOT NULL,
 115   country CHAR(2) NOT NULL,
 116   PRIMARY KEY(id),
 117   UNIQUE KEY(id, name, country),
 118   INDEX(id),
 119   INDEX(name),
 120   INDEX(country)
 121 ) ENGINE=InnoDB, CHARSET=utf8;
 122
 123 DROP TABLE geoloc_pays;
 124 -- vim:set syntax=mysql: