upgrade/1.1.0/09_addresses.sql

   1 DROP TABLE IF EXISTS tmp_profile_addresses;
   2 CREATE TEMPORARY TABLE tmp_profile_addresses LIKE profile_addresses;
   3 INSERT INTO tmp_profile_addresses SELECT * FROM profile_addresses;
   4 DROP TABLE profile_addresses;
   5 CREATE TABLE profile_addresses (
   6   pid int(11) unsigned NOT NULL DEFAULT '0',
   7   jobid int(6) unsigned NOT NULL DEFAULT '0',
   8   groupid SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
   9   type enum('home','job','hq','group') NOT NULL DEFAULT 'home',
  10   id tinyint(3) unsigned NOT NULL DEFAULT '0',
  11   flags set('current','temporary','secondary','mail','cedex','deliveryIssue') DEFAULT NULL,
  12   accuracy tinyint(1) unsigned NOT NULL DEFAULT '0',
  13   text text NOT NULL,
  14   postalText text NOT NULL,
  15   postalCode varchar(255) DEFAULT NULL,
  16   localityId bigint(20) unsigned DEFAULT NULL,
  17   subAdministrativeAreaId int(11) unsigned DEFAULT NULL,
  18   administrativeAreaId int(11) unsigned DEFAULT NULL,
  19   countryId char(2) DEFAULT NULL,
  20   latitude float(10,7) DEFAULT NULL,
  21   longitude float(10,7) DEFAULT NULL,
  22   north float(10,7) DEFAULT NULL,
  23   south float(10,7) DEFAULT NULL,
  24   east float(10,7) DEFAULT NULL,
  25   west float(10,7) DEFAULT NULL,
  26   pub enum('public','ax','private') NOT NULL DEFAULT 'private',
  27   comment varchar(255) DEFAULT NULL,
  28   PRIMARY KEY (pid,jobid,groupid,type,id),
  29   KEY pid (pid),
  30   KEY jobid (jobid),
  31   KEY type (type),
  32   KEY adrid (id),
  33   KEY localityId (localityId),
  34   KEY administrativeAreaId (administrativeAreaId),
  35   KEY subAdministrativeAreaId (subAdministrativeAreaId),
  36   KEY countryId (countryId),
  37   CONSTRAINT profile_addresses_ibfk_1 FOREIGN KEY (localityId) REFERENCES geoloc_localities (id) ON DELETE CASCADE ON UPDATE CASCADE,
  38   CONSTRAINT profile_addresses_ibfk_2 FOREIGN KEY (subAdministrativeAreaId) REFERENCES geoloc_subadministrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE,
  39   CONSTRAINT profile_addresses_ibfk_3 FOREIGN KEY (administrativeAreaId) REFERENCES geoloc_administrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE,
  40   CONSTRAINT profile_addresses_ibfk_4 FOREIGN KEY (countryId) REFERENCES geoloc_countries (iso_3166_1_a2) ON DELETE CASCADE ON UPDATE CASCADE
  41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  42 INSERT INTO  profile_addresses (pid, jobid, groupid, type, id, flags, accuracy, text, postalText,
  43              postalCode, localityId, subAdministrativeAreaId, administrativeAreaId, countryId,
  44              latitude, longitude, north, south, east, west, pub, comment)
  45      SELECT  pid, jobid, 0, type, id, flags, accuracy, text, postalText,
  46              postalCode, localityId, subAdministrativeAreaId, administrativeAreaId, countryId,
  47              latitude, longitude, north, south, east, west, pub, comment
  48        FROM  tmp_profile_addresses;
  49 DROP TABLE IF EXISTS tmp_profile_addresses;
  50
  51 -- vim:set syntax=mysql: