1 DROP TABLE IF EXISTS profile_job_sector_enum
;
3 CREATE TABLE profile_job_sector_enum (
4 id TINYINT(2) UNSIGNED
NOT NULL AUTO_INCREMENT
,
5 name VARCHAR(256) NOT NULL DEFAULT '',
7 UNIQUE INDEX(name(128))
8 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
11 DROP TABLE IF EXISTS profile_job_subsector_enum
;
13 CREATE TABLE profile_job_subsector_enum (
14 id SMALLINT(4) UNSIGNED
NOT NULL AUTO_INCREMENT
,
15 sectorid
TINYINT(2) UNSIGNED
NOT NULL,
16 flags
SET('optgroup') DEFAULT '' NOT NULL,
17 name VARCHAR(256) NOT NULL DEFAULT '',
19 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
22 DROP TABLE IF EXISTS profile_job_subsubsector_enum
;
24 CREATE TABLE profile_job_subsubsector_enum (
25 id SMALLINT(3) UNSIGNED
NOT NULL AUTO_INCREMENT
,
26 sectorid
TINYINT(2) UNSIGNED
NOT NULL,
27 subsectorid
TINYINT(3) UNSIGNED
NOT NULL,
28 name VARCHAR(256) NOT NULL DEFAULT '',
29 flags
SET('display') NOT NULL DEFAULT 'display',
31 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
34 DROP TABLE IF EXISTS profile_job_alternates
;
36 CREATE TABLE profile_job_alternates (
37 id TINYINT(2) UNSIGNED
NOT NULL DEFAULT 0,
38 subsubsectorid
SMALLINT(3) UNSIGNED
NOT NULL,
39 name VARCHAR(256) NOT NULL DEFAULT '',
40 PRIMARY KEY(id, subsubsectorid
),
41 UNIQUE INDEX(name(128))
42 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
45 ALTER TABLE profile_phones CHANGE
COLUMN link_type link_type
ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user';
47 DROP TABLE IF EXISTS profile_job_enum
;
49 CREATE TABLE profile_job_enum (
50 id INT(6) UNSIGNED
NOT NULL AUTO_INCREMENT
,
51 name VARCHAR(255) NOT NULL DEFAULT '',
52 acronym
VARCHAR(255) NOT NULL DEFAULT '',
53 url
VARCHAR(255) NOT NULL DEFAULT '',
54 email
VARCHAR(255) NOT NULL DEFAULT '',
55 holdingid
INT(6) UNSIGNED
DEFAULT NULL,
56 NAF_code
CHAR(5) NOT NULL DEFAULT '',
57 AX_code
BIGINT(10) UNSIGNED
NOT NULL,
60 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
62 DROP TABLE IF EXISTS profile_job
;
64 CREATE TABLE profile_job (
65 id TINYINT(1) UNSIGNED
NOT NULL,
66 pid
INT(11) NOT NULL DEFAULT 0,
67 jobid
INT(6) UNSIGNED
NOT NULL DEFAULT 0,
68 sectorid
TINYINT(2) UNSIGNED
NOT NULL DEFAULT 0,
69 subsectorid
SMALLINT(3) UNSIGNED
NOT NULL DEFAULT 0,
70 subsubsectorid
SMALLINT(3) UNSIGNED
NOT NULL DEFAULT 0,
71 description
VARCHAR(255) NOT NULL DEFAULT '',
72 url
VARCHAR(255) NOT NULL DEFAULT '',
73 email
VARCHAR(255) NOT NULL DEFAULT '',
74 pub
ENUM('private', 'ax', 'public') DEFAULT 'private',
75 email_pub
ENUM('private', 'ax', 'public') DEFAULT 'private',
76 PRIMARY KEY (pid
, id),
78 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
80 # Fill sector
table with old sectors.
81 INSERT INTO profile_job_sector_enum (name)
83 FROM #x4dat#.emploi_secteur
;
85 INSERT INTO profile_job_subsector_enum (sectorid
, name)
87 FROM #x4dat#.emploi_ss_secteur
AS ss
88 INNER JOIN #x4dat#.emploi_secteur
AS s4
ON (ss.secteur
= s4.
id)
89 INNER JOIN profile_job_sector_enum
AS s
ON (s.
name = s4.label
);
91 # Since subsubsectors
are not supported
by the
old sectors
set, define
92 # a fake
value per subsector.
93 INSERT INTO profile_job_subsubsector_enum (id, sectorid
, subsectorid
, name)
94 SELECT ss.
id, ss.sectorid
, ss.
id, CONCAT(s.
name, " (", ss.
name, ")")
95 FROM profile_job_subsector_enum
AS ss
96 INNER JOIN profile_job_sector_enum
AS s
ON (s.
id = ss.sectorid
)
99 INSERT IGNORE INTO profile_job_enum (name, url
)
100 SELECT entreprise
, web
101 FROM #x4dat#.entreprises
102 WHERE entreprise
!= '';
104 INSERT INTO profile_job (id, pid
, jobid
, email
, pub
, email_pub
, sectorid
, subsectorid
, subsubsectorid
, description
)
105 SELECT e.entrid
, e.uid
, j.
id, e.email
,
106 IF(e.pub
= '', 'private', e.pub
), IF(e.email_pub
= '', 'private', e.email_pub
),
107 IF(s.
id IS NOT NULL, s.
id, 0), IF(ss.
id IS NOT NULL, ss.
id, 0),
108 IF(ss.
id IS NOT NULL, ss.
id, 0), e.poste
109 FROM #x4dat#.entreprises
AS e
110 INNER JOIN profile_job_enum
AS j
ON (e.entreprise
= j.
name)
111 LEFT JOIN #x4dat#.fonctions_def
AS f
ON (f.
id = e.fonction
)
112 LEFT JOIN #x4dat#.emploi_ss_secteur
AS ss4
ON (ss4.
id = e.ss_secteur
)
113 LEFT JOIN #x4dat#.emploi_secteur
AS s4
ON (s4.
id = e.secteur
)
114 LEFT JOIN profile_job_sector_enum
AS s
ON (s.
name = s4.label
)
115 LEFT JOIN profile_job_subsector_enum
AS ss
ON (ss.
name = ss4.label
AND ss.sectorid
= s.
id)
116 WHERE e.entreprise
!= '';
118 -- vim:set syntax=mysql: