upgrade/newdirectory-0.0.1/12_secteurs.sql

   1 DROP TABLE IF EXISTS tmp_sectors;
   2
   3 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sectors (
   4   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   5   sect CHAR(1) NOT NULL,
   6   subsector TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
   7   subsubsector TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
   8   name VARCHAR(256) NOT NULL DEFAULT '',
   9   PRIMARY KEY(id)
  10 ) CHARSET=utf8;
  11
  12 LOAD DATA LOCAL INFILE 'rome-v3.csv' INTO TABLE  tmp_sectors FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
  13 (sect, subsector, subsubsector, name);
  14
  15 ALTER TABLE tmp_sectors ADD INDEX (sect);
  16 ALTER TABLE tmp_sectors ADD INDEX (subsector);
  17 ALTER TABLE tmp_sectors ADD INDEX (subsubsector);
  18 ALTER TABLE tmp_sectors ADD INDEX (name);
  19
  20 DROP TABLE IF EXISTS profile_job_sector_enum;
  21
  22 CREATE TABLE IF NOT EXISTS profile_job_sector_enum (
  23   id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  24   name VARCHAR(256) NOT NULL DEFAULT '',
  25   sect CHAR(1) NOT NULL,
  26   PRIMARY KEY(id),
  27   UNIQUE KEY(name)
  28 ) CHARSET=utf8;
  29
  30 INSERT INTO  profile_job_sector_enum (name, sect)
  31      SELECT  name, sect
  32        FROM  tmp_sectors
  33       WHERE  subsector = 0 AND subsubsector = 0
  34    ORDER BY  id;
  35
  36 DROP TABLE IF EXISTS profile_job_subsector_enum;
  37
  38 CREATE TABLE IF NOT EXISTS profile_job_subsector_enum (
  39   id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  40   sectorid TINYINT(2) UNSIGNED NOT NULL,
  41   flags SET('optgroup') DEFAULT '' NOT NULL,
  42   name VARCHAR(256) NOT NULL DEFAULT '',
  43   sect CHAR(1) NOT NULL,
  44   subsector TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
  45   PRIMARY KEY(id)
  46 ) CHARSET=utf8;
  47
  48 INSERT INTO  profile_job_subsector_enum (sectorid, name, sect, subsector)
  49      SELECT  s.id, t.name, t.sect, t.subsector
  50        FROM  tmp_sectors             AS t
  51  INNER JOIN  profile_job_sector_enum AS s ON (t.sect = s.sect)
  52       WHERE  t.subsector != 0 AND t.subsubsector = 0
  53    ORDER BY  t.id;
  54
  55     UPDATE  profile_job_subsector_enum AS su
  56 INNER JOIN  profile_job_sector_enum    AS se
  57        SET  flags = 'optgroup'
  58      WHERE  (se.name = "Industrie" AND (su.name = "Logistique"
  59                                         OR su.name = "Transport aérien et activités aéroportuaires"
  60                                         OR su.name = "Transport maritime et fluvial et activités portuaires"
  61                                         OR su.name = "Transport terrestre"))
  62             OR (se.name = "Transport et logistique" AND (su.name = "Études et supports techniques à l'industrie"
  63                                                          OR su.name = "Production industrielle"));
  64
  65 DROP TABLE IF EXISTS profile_job_subsubsector_enum;
  66
  67 CREATE TABLE IF NOT EXISTS profile_job_subsubsector_enum (
  68   id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  69   sectorid TINYINT(2) UNSIGNED NOT NULL,
  70   subsectorid TINYINT(3) UNSIGNED NOT NULL,
  71   name VARCHAR(256) NOT NULL DEFAULT '',
  72   PRIMARY KEY(id),
  73   UNIQUE KEY(name)
  74 ) CHARSET=utf8;
  75
  76 INSERT INTO  profile_job_subsubsector_enum (sectorid, subsectorid, name)
  77      SELECT  s.sectorid, s.id, t.name
  78        FROM  tmp_sectors                AS t
  79  INNER JOIN  profile_job_subsector_enum AS s ON (t.sect = s.sect AND t.subsector = s.subsector)
  80       WHERE  t.subsector != 0 AND t.subsubsector != 0
  81    ORDER BY  t.id;
  82
  83 ALTER TABLE profile_job_sector_enum DROP COLUMN sect;
  84 ALTER TABLE profile_job_subsector_enum DROP COLUMN sect;
  85 ALTER TABLE profile_job_subsector_enum DROP COLUMN subsector;
  86
  87
  88 ALTER TABLE profile_phones CHANGE COLUMN link_type link_type ENUM('address','pro','user', 'hq') NOT NULL DEFAULT 'user';
  89
  90 -- vim:set syntax=mysql: