Commit | Line | Data |
---|---|---|
c7139c07 SJ |
1 | DROP TABLE IF EXISTS tmp_sectors; |
2 | ||
3 | CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sectors ( | |
4 | id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, | |
5 | sect CHAR(1) NOT NULL, | |
6 | subsector TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, | |
7 | subsubsector TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, | |
8 | name VARCHAR(256) NOT NULL DEFAULT '', | |
9 | PRIMARY KEY(id) | |
10 | ) CHARSET=utf8; | |
11 | ||
12 | LOAD DATA LOCAL INFILE 'rome-v3.csv' INTO TABLE tmp_sectors FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' | |
13 | (sect, subsector, subsubsector, name); | |
14 | ||
15 | ALTER TABLE tmp_sectors ADD INDEX (sect); | |
16 | ALTER TABLE tmp_sectors ADD INDEX (subsector); | |
17 | ALTER TABLE tmp_sectors ADD INDEX (subsubsector); | |
18 | ALTER TABLE tmp_sectors ADD INDEX (name); | |
19 | ||
20 | DROP TABLE IF EXISTS profile_job_sector_enum; | |
21 | ||
22 | CREATE TABLE IF NOT EXISTS profile_job_sector_enum ( | |
23 | id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT, | |
24 | name VARCHAR(256) NOT NULL DEFAULT '', | |
25 | sect CHAR(1) NOT NULL, | |
26 | PRIMARY KEY(id), | |
27 | UNIQUE KEY(name) | |
28 | ) CHARSET=utf8; | |
29 | ||
30 | INSERT INTO profile_job_sector_enum (name, sect) | |
31 | SELECT name, sect | |
32 | FROM tmp_sectors | |
33 | WHERE subsector = 0 AND subsubsector = 0 | |
34 | ORDER BY id; | |
35 | ||
36 | DROP TABLE IF EXISTS profile_job_subsector_enum; | |
37 | ||
38 | CREATE TABLE IF NOT EXISTS profile_job_subsector_enum ( | |
39 | id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, | |
40 | sectorid TINYINT(2) UNSIGNED NOT NULL, | |
41 | flags SET('optgroup') DEFAULT '' NOT NULL, | |
42 | name VARCHAR(256) NOT NULL DEFAULT '', | |
43 | sect CHAR(1) NOT NULL, | |
44 | subsector TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, | |
45 | PRIMARY KEY(id) | |
46 | ) CHARSET=utf8; | |
47 | ||
48 | INSERT INTO profile_job_subsector_enum (sectorid, name, sect, subsector) | |
49 | SELECT s.id, t.name, t.sect, t.subsector | |
50 | FROM tmp_sectors AS t | |
51 | INNER JOIN profile_job_sector_enum AS s ON (t.sect = s.sect) | |
52 | WHERE t.subsector != 0 AND t.subsubsector = 0 | |
53 | ORDER BY t.id; | |
54 | ||
55 | UPDATE profile_job_subsector_enum AS su | |
56 | INNER JOIN profile_job_sector_enum AS se | |
57 | SET flags = 'optgroup' | |
58 | WHERE (se.name = "Industrie" AND (su.name = "Logistique" | |
59 | OR su.name = "Transport aérien et activités aéroportuaires" | |
60 | OR su.name = "Transport maritime et fluvial et activités portuaires" | |
61 | OR su.name = "Transport terrestre")) | |
62 | OR (se.name = "Transport et logistique" AND (su.name = "Études et supports techniques à l'industrie" | |
63 | OR su.name = "Production industrielle")); | |
64 | ||
65 | DROP TABLE IF EXISTS profile_job_subsubsector_enum; | |
66 | ||
67 | CREATE TABLE IF NOT EXISTS profile_job_subsubsector_enum ( | |
68 | id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, | |
69 | sectorid TINYINT(2) UNSIGNED NOT NULL, | |
70 | subsectorid TINYINT(3) UNSIGNED NOT NULL, | |
71 | name VARCHAR(256) NOT NULL DEFAULT '', | |
72 | PRIMARY KEY(id), | |
73 | UNIQUE KEY(name) | |
74 | ) CHARSET=utf8; | |
75 | ||
76 | INSERT INTO profile_job_subsubsector_enum (sectorid, subsectorid, name) | |
77 | SELECT s.sectorid, s.id, t.name | |
78 | FROM tmp_sectors AS t | |
79 | INNER JOIN profile_job_subsector_enum AS s ON (t.sect = s.sect AND t.subsector = s.subsector) | |
80 | WHERE t.subsector != 0 AND t.subsubsector != 0 | |
81 | ORDER BY t.id; | |
82 | ||
83 | ALTER TABLE profile_job_sector_enum DROP COLUMN sect; | |
84 | ALTER TABLE profile_job_subsector_enum DROP COLUMN sect; | |
85 | ALTER TABLE profile_job_subsector_enum DROP COLUMN subsector; | |
86 | ||
b814a8b8 SJ |
87 | |
88 | ALTER TABLE profile_phones CHANGE COLUMN link_type link_type ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user'; | |
89 | ||
c7139c07 | 90 | -- vim:set syntax=mysql: |