1 DROP TABLE IF EXISTS tmp_sectors
;
3 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sectors (
4 id SMALLINT UNSIGNED
NOT NULL AUTO_INCREMENT
,
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 '',
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);
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);
20 DROP TABLE IF EXISTS profile_job_sector_enum
;
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,
30 INSERT INTO profile_job_sector_enum (name, sect
)
33 WHERE subsector
= 0 AND subsubsector
= 0
36 DROP TABLE IF EXISTS profile_job_subsector_enum
;
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,
48 INSERT INTO profile_job_subsector_enum (sectorid
, name, sect
, subsector
)
49 SELECT s.
id, t.
name, t.sect
, t.subsector
51 INNER JOIN profile_job_sector_enum
AS s
ON (t.sect
= s.sect
)
52 WHERE t.subsector
!= 0 AND t.subsubsector
= 0
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"));
65 DROP TABLE IF EXISTS profile_job_subsubsector_enum
;
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 '',
76 INSERT INTO profile_job_subsubsector_enum (sectorid
, subsectorid
, name)
77 SELECT s.sectorid
, s.
id, t.
name
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
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
;
88 ALTER TABLE profile_phones CHANGE
COLUMN link_type link_type
ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user';
90 -- vim:set syntax=mysql: