Commit | Line | Data |
---|---|---|
041a5cec SJ |
1 | CREATE TABLE IF NOT EXISTS profile_addresses ( |
2 | pid INT(11) DEFAULT NULL, | |
3 | jobid INT(6) UNSIGNED DEFAULT NULL, | |
4 | type ENUM('home','job','hq') NOT NULL DEFAULT 'home', | |
5 | id TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, | |
6 | flags SET('current','temporary','secondary','mail','cedex') DEFAULT NULL, | |
7 | accuracy TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, | |
8 | text TEXT NOT NULL, | |
9 | postalText TEXT NOT NULL, | |
10 | postalCode VARCHAR(255) DEFAULT NULL, | |
11 | localityId INT(11) DEFAULT NULL, | |
12 | subAdministrativeAreaId INT(11) DEFAULT NULL, | |
13 | administrativeAreaId INT(11) DEFAULT NULL, | |
14 | countryId CHAR(2) DEFAULT NULL, | |
15 | latitude FLOAT(10,7) DEFAULT NULL, | |
16 | longitude FLOAT(10,7) DEFAULT NULL, | |
17 | north FLOAT(10,7) DEFAULT NULL, | |
18 | south FLOAT(10,7) DEFAULT NULL, | |
19 | east FLOAT(10,7) DEFAULT NULL, | |
20 | west FLOAT(10,7) DEFAULT NULL, | |
21 | updateTime DATE NOT NULL DEFAULT 0, | |
22 | pub ENUM('public','ax','private') NOT NULL DEFAULT 'private', | |
23 | comment VARCHAR(255) DEFAULT NULL, | |
24 | PRIMARY KEY(pid, jobid, type, id), | |
25 | INDEX pid (pid), | |
26 | INDEX jobid (jobid), | |
27 | INDEX type (type), | |
28 | INDEX adrid (id), | |
29 | INDEX localityId (localityId), | |
30 | INDEX administrativeAreaId (administrativeAreaId), | |
31 | INDEX subAdministrativeAreaId (subAdministrativeAreaId), | |
32 | INDEX countryId (countryId) | |
33 | ) CHARSET=utf8; | |
34 | ||
35 | CREATE TABLE IF NOT EXISTS geoloc_countries ( | |
644f8ee5 SJ |
36 | iso_3166_1_a2 CHAR(2) NOT NULL, |
37 | iso_3166_1_a3 CHAR(3) NOT NULL, | |
38 | iso_3166_1_num SMALLINT(3) UNSIGNED NOT NULL, | |
041a5cec SJ |
39 | worldRegion CHAR(2) DEFAULT NULL, |
40 | countryFR VARCHAR(255) NOT NULL, | |
41 | country VARCHAR(255) NOT NULL, | |
42 | capital VARCHAR(255) NOT NULL, | |
43 | nationalityFR VARCHAR(255) DEFAULT NULL, | |
44 | nationality VARCHAR(255) DEFAULT NULL, | |
45 | phonePrefix SMALLINT(5) UNSIGNED DEFAULT NULL, | |
46 | phoneFormat VARCHAR(255) NOT NULL, | |
47 | licensePlate CHAR(4) DEFAULT NULL, | |
48 | belongsTo CHAR(2) DEFAULT NULL, | |
644f8ee5 SJ |
49 | PRIMARY KEY(iso_3166_1_a2), |
50 | UNIQUE KEY(iso_3166_1_a3), | |
51 | UNIQUE KEY(iso_3166_1_num), | |
52 | INDEX(iso_3166_1_a2), | |
041a5cec SJ |
53 | INDEX(phonePrefix) |
54 | ) CHARSET=utf8; | |
55 | ||
644f8ee5 SJ |
56 | UPDATE geoloc_pays |
57 | SET n3 = 450 | |
58 | WHERE a2 = "MG"; | |
59 | ||
60 | UPDATE geoloc_pays | |
61 | SET n3 = 807 | |
62 | WHERE a2 = "MK"; | |
63 | ||
64 | INSERT INTO geoloc_countries (iso_3166_1_a2, iso_3166_1_a3, iso_3166_1_num, worldRegion, | |
041a5cec SJ |
65 | countryFR, country, capital, nationalityFR, |
66 | phonePrefix, phoneFormat, licensePlate) | |
67 | SELECT a2, a3, n3, worldrgn, pays, country, capital, nat, phoneprf, phoneformat, license_plate | |
68 | FROM geoloc_pays; | |
69 | ||
4c906759 | 70 | CREATE TABLE IF NOT EXISTS geoloc_administrativeareas ( |
041a5cec SJ |
71 | id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, |
72 | name VARCHAR(255) NOT NULL, | |
73 | country CHAR(2) NOT NULL, | |
74 | PRIMARY KEY(id), | |
75 | UNIQUE KEY(id, name, country), | |
76 | INDEX(id), | |
77 | INDEX(name), | |
78 | INDEX(country) | |
79 | ) CHARSET=utf8; | |
80 | ||
4c906759 | 81 | CREATE TABLE IF NOT EXISTS geoloc_subadministrativeareas ( |
041a5cec SJ |
82 | id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, |
83 | name VARCHAR(255) NOT NULL, | |
84 | country CHAR(2) NOT NULL, | |
85 | PRIMARY KEY(id), | |
86 | UNIQUE KEY(id, name, country), | |
87 | INDEX(id), | |
88 | INDEX(name), | |
89 | INDEX(country) | |
90 | ) CHARSET=utf8; | |
91 | ||
4c906759 | 92 | CREATE TABLE IF NOT EXISTS geoloc_localities ( |
041a5cec SJ |
93 | id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
94 | name VARCHAR(255) NOT NULL, | |
95 | country CHAR(2) NOT NULL, | |
96 | PRIMARY KEY(id), | |
97 | UNIQUE KEY(id, name, country), | |
98 | INDEX(id), | |
99 | INDEX(name), | |
100 | INDEX(country) | |
101 | ) CHARSET=utf8; | |
102 | ||
103 | -- vim:set syntax=mysql: |