| 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), |
| 10 | KEY (types, long_name), |
| 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; |
| 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; |
| 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: |