Commit | Line | Data |
---|---|---|
0f5f1b70 SJ |
1 | DROP TABLE IF EXISTS profile_addresses_components; |
2 | DROP TABLE IF EXISTS profile_addresses_components_enum; | |
3 | ||
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 '', | |
9 | PRIMARY KEY (id), | |
710a2ffc | 10 | KEY (types, long_name), |
0f5f1b70 SJ |
11 | KEY (types, short_name) |
12 | ) ENGINE=InnoDB, CHARSET=utf8; | |
13 | ||
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), | |
22 | KEY(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; | |
26 | ||
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; | |
31 | ||
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; | |
42 | ||
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; | |
72a4c6a8 SJ |
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; | |
0f5f1b70 SJ |
53 | |
54 | UPDATE profile_addresses | |
55 | SET latitude = NULL, longitude = NULL; | |
56 | ||
57 | DROP TABLE IF EXISTS geoloc_administrativeareas; | |
58 | DROP TABLE IF EXISTS geoloc_localities; | |
59 | DROP TABLE IF EXISTS geoloc_subadministrativeareas; | |
60 | ||
61 | -- vim:set syntax=mysql: |