| 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 | |
| 87 | |
| 88 | ALTER TABLE profile_phones CHANGE COLUMN link_type link_type ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user'; |
| 89 | |
| 90 | -- vim:set syntax=mysql: |