upgrade/newdirectory-0.0.1/13_mentoring.sql

   1 DROP TABLE IF EXISTS profile_mentor;
   2 DROP TABLE IF EXISTS profile_mentor_country;
   3 DROP TABLE IF EXISTS profile_mentor_sector;
   4
   5 CREATE TABLE profile_mentor (
   6   pid INT(11) NOT NULL DEFAULT 0,
   7   expertise TEXT NOT NULL,
   8   PRIMARY KEY (pid)
   9 ) ENGINE=InnoDB, CHARSET=utf8;
  10
  11 INSERT INTO  profile_mentor (pid, expertise)
  12      SELECT  uid, expertise
  13        FROM  #x4dat#.mentor;
  14
  15 CREATE TABLE profile_mentor_country (
  16   pid INT(11) NOT NULL DEFAULT 0,
  17   country CHAR(2) NOT NULL DEFAULT "FR",
  18   PRIMARY KEY (pid, country),
  19   INDEX pid (pid)
  20 ) ENGINE=InnoDB, CHARSET=utf8;
  21
  22 INSERT INTO  profile_mentor_country (pid, country)
  23      SELECT  uid, pid
  24        FROM  #x4dat#.mentor_pays;
  25
  26 CREATE TABLE profile_mentor_sector (
  27   pid INT(11) NOT NULL DEFAULT 0,
  28   sectorid TINYINT(2) UNSIGNED NOT NULL,
  29   subsectorid SMALLINT(3) UNSIGNED NOT NULL,
  30   PRIMARY KEY (pid, sectorid, subsectorid),
  31   INDEX pid (pid)
  32 ) ENGINE=InnoDB, CHARSET=utf8;
  33
  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
  42 -- vim:set syntax=mysql: