| 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: |