DROP TABLE IF EXISTS profile_mentor_country;
DROP TABLE IF EXISTS profile_mentor_sector;
-CREATE TABLE IF NOT EXISTS profile_mentor (
- uid INT(11) NOT NULL DEFAULT 0,
+CREATE TABLE profile_mentor (
+ pid INT(11) NOT NULL DEFAULT 0,
expertise TEXT NOT NULL,
- PRIMARY KEY (uid),
- FULLTEXT INDEX (expertise)
-) CHARSET=utf8;
+ PRIMARY KEY (pid)
+) ENGINE=InnoDB, CHARSET=utf8;
-INSERT INTO profile_mentor (uid, expertise)
+INSERT INTO profile_mentor (pid, expertise)
SELECT uid, expertise
- FROM mentor;
+ FROM #x4dat#.mentor;
-CREATE TABLE IF NOT EXISTS profile_mentor_country (
- uid INT(11) NOT NULL DEFAULT 0,
+CREATE TABLE profile_mentor_country (
+ pid INT(11) NOT NULL DEFAULT 0,
country CHAR(2) NOT NULL DEFAULT "FR",
- PRIMARY KEY (uid, country)
-) CHARSET=utf8;
+ PRIMARY KEY (pid, country),
+ INDEX pid (pid)
+) ENGINE=InnoDB, CHARSET=utf8;
-INSERT INTO profile_mentor_country (uid, country)
+INSERT INTO profile_mentor_country (pid, country)
SELECT uid, pid
- FROM mentor_pays;
+ FROM #x4dat#.mentor_pays;
-CREATE TABLE IF NOT EXISTS profile_mentor_sector (
- uid INT(11) NOT NULL DEFAULT 0,
+CREATE TABLE profile_mentor_sector (
+ pid INT(11) NOT NULL DEFAULT 0,
sectorid TINYINT(2) UNSIGNED NOT NULL,
- subsectorid TINYINT(3) UNSIGNED NOT NULL,
- PRIMARY KEY (uid, sectorid, subsectorid)
-) CHARSET=utf8;
+ subsectorid SMALLINT(3) UNSIGNED NOT NULL,
+ PRIMARY KEY (pid, sectorid, subsectorid),
+ INDEX pid (pid)
+) ENGINE=InnoDB, CHARSET=utf8;
+
+INSERT IGNORE INTO profile_mentor_sector (pid, sectorid, subsectorid)
+ SELECT ms4.uid, s.id, ss.id
+ FROM #x4dat#.mentor_secteurs AS ms4
+ INNER JOIN #x4dat#.emploi_secteur AS s4 ON (s4.id = ms4.secteur)
+ INNER JOIN profile_job_sector_enum AS s ON (s.name = s4.label)
+ LEFT JOIN #x4dat#.emploi_ss_secteur AS ss4 ON (ss4.id = ms4.ss_secteur)
+ LEFT JOIN profile_job_subsector_enum AS ss ON (ss.name = ss4.label);
-- vim:set syntax=mysql: