upgrade/fusionax-0.0.1/06_education.sql

   1 CREATE TABLE IF NOT EXISTS profile_education_field_enum (
   2   id INT(2) NOT NULL AUTO_INCREMENT,
   3   field VARCHAR(255) DEFAULT NULL,
   4   PRIMARY KEY(id),
   5   UNIQUE KEY(field)
   6 ) CHARSET=utf8;
   7
   8 CREATE TABLE IF NOT EXISTS profile_education_degree_enum (
   9   id INT(2) NOT NULL AUTO_INCREMENT,
  10   degree VARCHAR(255) DEFAULT NULL,
  11   PRIMARY KEY(id),
  12   UNIQUE KEY(degree)
  13 ) CHARSET=utf8;
  14
  15 CREATE TABLE IF NOT EXISTS profile_education_degree (
  16   eduid INT(4) NOT NULL DEFAULT 0,
  17   degreeid INT(2) NOT NULL DEFAULT 0,
  18   PRIMARY KEY(eduid, degreeid)
  19 ) CHARSET=utf8;
  20
  21 CREATE TABLE IF NOT EXISTS profile_education_enum (
  22   id INT(4) NOT NULL AUTO_INCREMENT,
  23   name VARCHAR(255) DEFAULT NULL,
  24   url VARCHAR(255) DEFAULT NULL,
  25   country CHAR(2) NOT NULL DEFAULT 'FR',
  26   PRIMARY KEY(id),
  27   UNIQUE KEY(name)
  28 ) CHARSET=utf8;
  29
  30 CREATE TABLE IF NOT EXISTS profile_education (
  31   id INT(2) NOT NULL DEFAULT 0,
  32   uid INT(11) NOT NULL DEFAULT 0,
  33   eduid INT(4) NOT NULL DEFAULT 0,
  34   degreeid INT(4) NOT NULL DEFAULT 0,
  35   fieldid INT(2) NOT NULL DEFAULT 0,
  36   grad_year INT(4) NOT NULL DEFAULT 0,
  37   PRIMARY KEY(id, uid)
  38 ) CHARSET=utf8;
  39
  40 INSERT INTO  profile_education_field_enum (field)
  41      VALUES  ('Aéronautique'), ('Agronomie'), ('Assurance'), ('Biologie'),
  42              ('Chimie'), ('Droit'), ('Économie'), ('Électronique/électricité'),
  43              ('Environnement/développement durable'), ('Finance'), ('Géographie'),
  44              ('Histoire'), ('Informatique'), ('Langues'), ('Mathématiques'),
  45              ('Mathématiques appliquées'), ('Mécanique'), ('Médecine'),
  46              ('Philosophie'), ('Physique'), ('Sciences politiques');
  47
  48 INSERT INTO  profile_education_degree_enum (degree)
  49      VALUES  ('Diplôme'), ('Ingénieur'), ('Corps'), ('MS'), ('PhD'),
  50              ('DEA'), ('ME'), ('MBA'), ('MiF'), ('MPA'), ('Licence');
  51
  52 INSERT INTO  profile_education_degree (eduid, degreeid)
  53      SELECT  a.id, d.id
  54        FROM  applis_def AS a
  55  INNER JOIN  profile_education_degree_enum AS d ON (FIND_IN_SET(d.degree, a.type));
  56
  57 INSERT INTO  profile_education_enum (id, name, url)
  58      SELECT  id, text, url
  59        FROM  applis_def;
  60
  61 INSERT INTO  profile_education (id, uid, eduid, degreeid)
  62      SELECT  a.ordre, a.uid, a.aid, d.id
  63        FROM  applis_ins AS a
  64  INNER JOIN  profile_education_degree_enum AS d ON (a.type = d.degree);
  65
  66
  67 # vim:set syntax=mysql:
  68