Commit | Line | Data |
---|---|---|
baab50c7 SJ |
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: |