upgrade/newdirectory-0.0.1/15_addresses.sql

   1 CREATE TABLE IF NOT EXISTS profile_addresses (
   2   pid INT(11) DEFAULT NULL,
   3   jobid INT(6) UNSIGNED DEFAULT NULL,
   4   type ENUM('home','job','hq') NOT NULL DEFAULT 'home',
   5   id TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
   6   flags SET('current','temporary','secondary','mail','cedex') DEFAULT NULL,
   7   accuracy TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
   8   text TEXT NOT NULL,
   9   postalText TEXT NOT NULL,
  10   postalCode VARCHAR(255) DEFAULT NULL,
  11   localityId INT(11) DEFAULT NULL,
  12   subAdministrativeAreaId INT(11) DEFAULT NULL,
  13   administrativeAreaId INT(11) DEFAULT NULL,
  14   countryId CHAR(2) DEFAULT NULL,
  15   latitude FLOAT(10,7) DEFAULT NULL,
  16   longitude FLOAT(10,7) DEFAULT NULL,
  17   north FLOAT(10,7) DEFAULT NULL,
  18   south FLOAT(10,7) DEFAULT NULL,
  19   east FLOAT(10,7) DEFAULT NULL,
  20   west FLOAT(10,7) DEFAULT NULL,
  21   updateTime DATE NOT NULL DEFAULT 0,
  22   pub ENUM('public','ax','private') NOT NULL DEFAULT 'private',
  23   comment VARCHAR(255) DEFAULT NULL,
  24   PRIMARY KEY(pid, jobid, type, id),
  25   INDEX pid (pid),
  26   INDEX jobid (jobid),
  27   INDEX type (type),
  28   INDEX adrid (id),
  29   INDEX localityId (localityId),
  30   INDEX administrativeAreaId (administrativeAreaId),
  31   INDEX subAdministrativeAreaId (subAdministrativeAreaId),
  32   INDEX countryId (countryId)
  33 ) CHARSET=utf8;
  34
  35 CREATE TABLE IF NOT EXISTS geoloc_countries (
  36   iso_3166_1 CHAR(2) NOT NULL,
  37   iso_3166_2 CHAR(3) NOT NULL,
  38   iso_3166_3 SMALLINT(3) UNSIGNED NOT NULL,
  39   worldRegion CHAR(2) DEFAULT NULL,
  40   countryFR VARCHAR(255) NOT NULL,
  41   country VARCHAR(255) NOT NULL,
  42   capital VARCHAR(255) NOT NULL,
  43   nationalityFR VARCHAR(255) DEFAULT NULL,
  44   nationality VARCHAR(255) DEFAULT NULL,
  45   phonePrefix SMALLINT(5) UNSIGNED DEFAULT NULL,
  46   phoneFormat VARCHAR(255) NOT NULL,
  47   licensePlate CHAR(4) DEFAULT NULL,
  48   belongsTo CHAR(2) DEFAULT NULL,
  49   PRIMARY KEY(iso_3166_1),
  50   INDEX(iso_3166_1),
  51   INDEX(phonePrefix)
  52 ) CHARSET=utf8;
  53
  54 INSERT INTO  geoloc_countries (iso_3166_1, iso_3166_2, iso_3166_3, worldRegion,
  55                                countryFR, country, capital, nationalityFR,
  56                                phonePrefix, phoneFormat, licensePlate)
  57      SELECT  a2, a3, n3, worldrgn, pays, country, capital, nat, phoneprf, phoneformat, license_plate
  58        FROM  geoloc_pays;
  59
  60 CREATE TABLE IF NOT EXISTS geoloc_administrativeArea (
  61   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  62   name VARCHAR(255) NOT NULL,
  63   country CHAR(2) NOT NULL,
  64   PRIMARY KEY(id),
  65   UNIQUE KEY(id, name, country),
  66   INDEX(id),
  67   INDEX(name),
  68   INDEX(country)
  69 ) CHARSET=utf8;
  70
  71 CREATE TABLE IF NOT EXISTS geoloc_subAdministrativeArea (
  72   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  73   name VARCHAR(255) NOT NULL,
  74   country CHAR(2) NOT NULL,
  75   PRIMARY KEY(id),
  76   UNIQUE KEY(id, name, country),
  77   INDEX(id),
  78   INDEX(name),
  79   INDEX(country)
  80 ) CHARSET=utf8;
  81
  82 CREATE TABLE IF NOT EXISTS geoloc_locality (
  83   id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  84   name VARCHAR(255) NOT NULL,
  85   country CHAR(2) NOT NULL,
  86   PRIMARY KEY(id),
  87   UNIQUE KEY(id, name, country),
  88   INDEX(id),
  89   INDEX(name),
  90   INDEX(country)
  91 ) CHARSET=utf8;
  92
  93 -- vim:set syntax=mysql: