1 DROP TABLE IF EXISTS profile_addresses_components
;
2 DROP TABLE IF EXISTS profile_addresses_components_enum
;
4 CREATE TABLE profile_addresses_components_enum (
5 id BIGINT(10) UNSIGNED
NOT NULL AUTO_INCREMENT
,
6 short_name
VARCHAR(255) NOT NULL DEFAULT '',
7 long_name
VARCHAR(255) NOT NULL DEFAULT '',
8 types
SET('street_address', 'route', 'intersection', 'political', 'country', 'administrative_area_level_1', 'administrative_area_level_2', 'administrative_area_level_3', 'colloquial_area', 'locality', 'sublocality', 'neighborhood', 'premise', 'subpremise', 'postal_code', 'natural_feature', 'airport', 'park', 'point_of_interest', 'post_box', 'street_number', 'floor', 'room') NOT NULL DEFAULT '',
10 KEY (types
, long_name
),
11 KEY (types
, short_name
)
12 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
14 CREATE TABLE profile_addresses_components (
15 pid
INT(11) UNSIGNED
NOT NULL DEFAULT 0,
16 jobid
INT(6) UNSIGNED
NOT NULL DEFAULT 0,
17 groupid
SMALLINT(5) UNSIGNED
NOT NULL DEFAULT 0,
18 type ENUM('home', 'job', 'hq', 'group') NOT NULL DEFAULT 'home',
19 id TINYINT(3) UNSIGNED
NOT NULL DEFAULT 0,
20 component_id
BIGINT(10) UNSIGNED
NOT NULL DEFAULT 0,
21 PRIMARY KEY (pid
, jobid
, groupid
, type, id, component_id
),
23 FOREIGN KEY (pid
, jobid
, groupid
, type, id) REFERENCES profile_addresses (pid
, jobid
, groupid
, type, id) ON UPDATE CASCADE ON DELETE CASCADE,
24 FOREIGN KEY (component_id
) REFERENCES profile_addresses_components_enum (id) ON UPDATE CASCADE ON DELETE CASCADE
25 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
27 ALTER TABLE profile_addresses
DROP FOREIGN KEY profile_addresses_ibfk_1
;
28 ALTER TABLE profile_addresses
DROP FOREIGN KEY profile_addresses_ibfk_2
;
29 ALTER TABLE profile_addresses
DROP FOREIGN KEY profile_addresses_ibfk_3
;
30 ALTER TABLE profile_addresses
DROP FOREIGN KEY profile_addresses_ibfk_4
;
32 ALTER TABLE profile_addresses
DROP COLUMN accuracy
;
33 ALTER TABLE profile_addresses
DROP COLUMN postalCode
;
34 ALTER TABLE profile_addresses
DROP COLUMN localityId
;
35 ALTER TABLE profile_addresses
DROP COLUMN subAdministrativeAreaId
;
36 ALTER TABLE profile_addresses
DROP COLUMN administrativeAreaId
;
37 ALTER TABLE profile_addresses
DROP COLUMN countryId
;
38 ALTER TABLE profile_addresses
DROP COLUMN north
;
39 ALTER TABLE profile_addresses
DROP COLUMN south
;
40 ALTER TABLE profile_addresses
DROP COLUMN east
;
41 ALTER TABLE profile_addresses
DROP COLUMN west
;
43 ALTER TABLE profile_addresses
ADD COLUMN formatted_address
TEXT NOT NULL DEFAULT '' AFTER postalText
;
44 ALTER TABLE profile_addresses
ADD COLUMN types
SET('street_address', 'route', 'intersection', 'political', 'country', 'administrative_area_level_1', 'administrative_area_level_2', 'administrative_area_level_3', 'colloquial_area', 'locality', 'sublocality', 'neighborhood', 'premise', 'subpremise', 'postal_code', 'natural_feature', 'airport', 'park', 'point_of_interest', 'post_box', 'street_number', 'floor', 'room') NOT NULL DEFAULT '' AFTER formatted_address
;
45 ALTER TABLE profile_addresses
ADD COLUMN southwest_latitude
FLOAT(10, 7) DEFAULT NULL AFTER longitude
;
46 ALTER TABLE profile_addresses
ADD COLUMN southwest_longitude
FLOAT(10, 7) DEFAULT NULL AFTER southwest_latitude
;
47 ALTER TABLE profile_addresses
ADD COLUMN northeast_latitude
FLOAT(10, 7) DEFAULT NULL AFTER southwest_longitude
;
48 ALTER TABLE profile_addresses
ADD COLUMN northeast_longitude
FLOAT(10, 7) DEFAULT NULL AFTER northeast_latitude
;
49 ALTER TABLE profile_addresses
ADD COLUMN location_type
ENUM('ROOFTOP', 'RANGE_INTERPOLATED', 'GEOMETRIC_CENTER', 'APPROXIMATE') DEFAULT NULL AFTER northeast_longitude
;
50 ALTER TABLE profile_addresses
ADD COLUMN partial_match
BOOLEAN NOT NULL DEFAULT false AFTER location_type
;
51 ALTER TABLE profile_addresses
ADD COLUMN geocoding_date
DATE DEFAULT NULL;
52 ALTER TABLE profile_addresses
ADD COLUMN geocoding_calls TINYINT
NOT NULL DEFAULT 0;
54 UPDATE profile_addresses
55 SET latitude
= NULL, longitude
= NULL;
57 DROP TABLE IF EXISTS geoloc_administrativeareas
;
58 DROP TABLE IF EXISTS geoloc_localities
;
59 DROP TABLE IF EXISTS geoloc_subadministrativeareas
;
61 -- vim:set syntax=mysql: