8f753cb8f4634ef7a63d4d8c21b68e78e1ac3745
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',
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),
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
;
51 -- vim:set syntax=mysql: