upgrade/newdirectory-0.0.1/07_corps.sql

   1 DROP TABLE IF EXISTS profile_corps;
   2 DROP TABLE IF EXISTS profile_corps_enum;
   3 DROP TABLE IF EXISTS profile_corps_rank_enum;
   4
   5 CREATE TABLE profile_corps (
   6   pid INT(11) NOT NULL,
   7   original_corpsid INT(4) UNSIGNED NOT NULL DEFAULT 0,
   8   current_corpsid INT(4) UNSIGNED NOT NULL DEFAULT 0,
   9   rankid INT(4) UNSIGNED NOT NULL DEFAULT 0,
  10   corps_pub ENUM('private', 'ax', 'public') NOT NULL DEFAULT 'private',
  11   PRIMARY KEY(pid)
  12 ) ENGINE=InnoDB, CHARSET=utf8;
  13
  14
  15 CREATE TABLE profile_corps_enum (
  16   id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  17   name VARCHAR(255) NOT NULL DEFAULT '',
  18   abbreviation CHAR(5) NOT NULL DEFAULT '',
  19   still_exists TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
  20   PRIMARY KEY(id),
  21   UNIQUE KEY(name),
  22   UNIQUE KEY(abbreviation)
  23 ) ENGINE=InnoDB, CHARSET=utf8;
  24
  25
  26 CREATE TABLE profile_corps_rank_enum (
  27   id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  28   name VARCHAR(255) NOT NULL DEFAULT '',
  29   abbreviation CHAR(5) NOT NULL DEFAULT '',
  30   PRIMARY KEY(id),
  31   UNIQUE KEY(name),
  32   UNIQUE KEY(abbreviation)
  33 ) ENGINE=InnoDB, CHARSET=utf8;
  34
  35     UPDATE  profile_corps      AS c
  36 INNER JOIN  profile_corps_enum AS e ON (c.original_corpsid = e.id)
  37  LEFT JOIN  profile_corps_enum AS a ON (a.name = "Aucun (anc. démissionnaire)")
  38        SET  c.original_corpsid = a.id
  39      WHERE  e.name = "Ancien élève étranger";
  40
  41 UPDATE  profile_corps_enum
  42    SET  name = "Aucun"
  43  WHERE  name = "Aucun (anc. démissionnaire)";
  44
  45 DELETE FROM  profile_corps_enum
  46       WHERE  name = "Ancien élève étranger";
  47
  48
  49 ALTER TABLE watch_profile MODIFY field enum('nom', 'freetext', 'mobile', 'nationalite', 'nationalite2',
  50                                             'nationalite3', 'nick', 'web', 'networking', 'edus', 'addresses',
  51                                             'section', 'binets', 'medals', 'cv', 'jobs', 'photo', 'corps');
  52
  53 --  vim:set syntax=mysql: