Commit | Line | Data |
---|---|---|
5fecdf6d SJ |
1 | DROP TABLE IF EXISTS profile_mentor; |
2 | DROP TABLE IF EXISTS profile_mentor_country; | |
3 | DROP TABLE IF EXISTS profile_mentor_sector; | |
4 | ||
d0293d9b | 5 | CREATE TABLE profile_mentor ( |
ce0b2c6f | 6 | pid INT(11) NOT NULL DEFAULT 0, |
5fecdf6d | 7 | expertise TEXT NOT NULL, |
ce0b2c6f | 8 | PRIMARY KEY (pid) |
950bf4f6 | 9 | ) ENGINE=InnoDB, CHARSET=utf8; |
5fecdf6d | 10 | |
ce0b2c6f | 11 | INSERT INTO profile_mentor (pid, expertise) |
5fecdf6d | 12 | SELECT uid, expertise |
100e66fc | 13 | FROM #x4dat#.mentor; |
5fecdf6d | 14 | |
d0293d9b | 15 | CREATE TABLE profile_mentor_country ( |
ce0b2c6f | 16 | pid INT(11) NOT NULL DEFAULT 0, |
5fecdf6d | 17 | country CHAR(2) NOT NULL DEFAULT "FR", |
ce0b2c6f FB |
18 | PRIMARY KEY (pid, country), |
19 | INDEX pid (pid) | |
950bf4f6 | 20 | ) ENGINE=InnoDB, CHARSET=utf8; |
5fecdf6d | 21 | |
ce0b2c6f | 22 | INSERT INTO profile_mentor_country (pid, country) |
5fecdf6d | 23 | SELECT uid, pid |
100e66fc | 24 | FROM #x4dat#.mentor_pays; |
5fecdf6d | 25 | |
d0293d9b | 26 | CREATE TABLE profile_mentor_sector ( |
ce0b2c6f | 27 | pid INT(11) NOT NULL DEFAULT 0, |
5fecdf6d | 28 | sectorid TINYINT(2) UNSIGNED NOT NULL, |
97c79f14 | 29 | subsectorid SMALLINT(3) UNSIGNED NOT NULL, |
ce0b2c6f FB |
30 | PRIMARY KEY (pid, sectorid, subsectorid), |
31 | INDEX pid (pid) | |
950bf4f6 | 32 | ) ENGINE=InnoDB, CHARSET=utf8; |
5fecdf6d | 33 | |
97c79f14 FB |
34 | INSERT IGNORE INTO profile_mentor_sector (pid, sectorid, subsectorid) |
35 | SELECT ms4.uid, s.id, ss.id | |
36 | FROM #x4dat#.mentor_secteurs AS ms4 | |
37 | INNER JOIN #x4dat#.emploi_secteur AS s4 ON (s4.id = ms4.secteur) | |
38 | INNER JOIN profile_job_sector_enum AS s ON (s.name = s4.label) | |
39 | LEFT JOIN #x4dat#.emploi_ss_secteur AS ss4 ON (ss4.id = ms4.ss_secteur) | |
40 | LEFT JOIN profile_job_subsector_enum AS ss ON (ss.name = ss4.label); | |
41 | ||
5fecdf6d | 42 | -- vim:set syntax=mysql: |