upgrade/1.1.2/03_names.sql

   1 DROP TABLE IF EXISTS profile_public_names;
   2 CREATE TABLE IF NOT EXISTS profile_public_names (
   3   pid INT(11) UNSIGNED NOT NULL DEFAULT 0,
   4   particles SET('initial', 'main', 'marital', 'ordinary') NOT NULL DEFAULT '',
   5   lastname_initial VARCHAR(255) NOT NULL DEFAULT '',
   6   lastname_main VARCHAR(255) NOT NULL DEFAULT '',
   7   lastname_marital VARCHAR(255) NOT NULL DEFAULT '',
   8   lastname_ordinary VARCHAR(255) NOT NULL DEFAULT '',
   9   firstname_initial VARCHAR(255) NOT NULL DEFAULT '',
  10   firstname_main VARCHAR(255) NOT NULL DEFAULT '',
  11   firstname_ordinary VARCHAR(255) NOT NULL DEFAULT '',
  12   pseudonym VARCHAR(255) NOT NULL DEFAULT '',
  13   PRIMARY KEY (pid),
  14   FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE
  15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  16
  17 DROP TABLE IF EXISTS profile_private_names;
  18 CREATE TABLE IF NOT EXISTS profile_private_names (
  19   pid INT(11) UNSIGNED NOT NULL DEFAULT 0,
  20   type ENUM('lastname', 'firstname', 'nickname') NOT NULL DEFAULT 'nickname',
  21   id TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'id of the name among those sharing the same pid / general_type',
  22   name VARCHAR(255) NOT NULL DEFAULT '',
  23   PRIMARY KEY (pid, type, id),
  24   FOREIGN KEY (pid) REFERENCES profiles (pid) ON DELETE CASCADE ON UPDATE CASCADE
  25 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  26
  27 -- Initiates profile_public_names.
  28 INSERT INTO  profile_public_names (pid)
  29      SELECT  pid
  30        FROM  profiles;
  31
  32 -- Insert lastnames.
  33     UPDATE  profile_public_names AS ppn
  34 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  35 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  36        SET  ppn.lastname_initial = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)),
  37             ppn.particles = IF(pn.particle = '', '', 'initial')
  38      WHERE  pne.type = 'name_ini';
  39     UPDATE  profile_public_names AS ppn
  40 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  41 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  42        SET  ppn.lastname_main = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)),
  43             ppn.particles = IF(pn.particle = '', ppn.particles, CONCAT_WS(',', ppn.particles, 'main'))
  44      WHERE  pne.type = 'lastname';
  45 UPDATE  profile_public_names
  46    SET  particles = TRIM(BOTH ',' FROM particles);
  47     UPDATE  profile_public_names AS ppn
  48 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  49 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  50        SET  ppn.lastname_marital = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)),
  51             ppn.particles = IF(pn.particle = '', ppn.particles, CONCAT_WS(',', ppn.particles, 'marital'))
  52      WHERE  pne.type = 'lastname_marital';
  53 UPDATE  profile_public_names
  54    SET  particles = TRIM(BOTH ',' FROM particles);
  55     UPDATE  profile_public_names AS ppn
  56 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  57 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  58        SET  ppn.lastname_ordinary = IF(pn.particle = '', pn.name, CONCAT(pn.particle, ' ', pn.name)),
  59             ppn.particles = IF(pn.particle = '', ppn.particles, CONCAT_WS(',', ppn.particles, 'ordinary'))
  60      WHERE  pne.type = 'lastname_ordinary';
  61 UPDATE  profile_public_names
  62    SET  particles = TRIM(BOTH ',' FROM particles);
  63
  64 -- Insert other names.
  65     UPDATE  profile_public_names AS ppn
  66 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  67 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  68        SET  ppn.firstname_initial = pn.name
  69      WHERE  pne.type = 'firstname_ini';
  70     UPDATE  profile_public_names AS ppn
  71 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  72 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  73        SET  ppn.firstname_main = pn.name
  74      WHERE  pne.type = 'firstname';
  75     UPDATE  profile_public_names AS ppn
  76 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  77 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  78        SET  ppn.firstname_ordinary = pn.name
  79      WHERE  pne.type = 'firstname_other';
  80     UPDATE  profile_public_names AS ppn
  81 INNER JOIN  profile_name         AS pn  ON (pn.pid = ppn.pid)
  82 INNER JOIN  profile_name_enum    AS pne ON (pn.typeid = pne.id)
  83        SET  ppn.pseudonym = pn.name
  84      WHERE  pne.type = 'pseudonym';
  85
  86 -- Insert privates names.
  87 INSERT INTO  profile_private_names (pid, type, id, name)
  88      SELECT  pn.pid, 'nickname', 0, pn.name
  89        FROM  profile_name      AS pn
  90  INNER JOIN  profile_name_enum AS pne ON (pn.typeid = pne.id)
  91       WHERE  pne.type = 'nickname';
  92 INSERT INTO  profile_private_names (pid, type, id, name)
  93      SELECT  pn.pid, 'lastname', 0, pn.name
  94        FROM  profile_name      AS pn
  95  INNER JOIN  profile_name_enum AS pne ON (pn.typeid = pne.id)
  96       WHERE  pne.type = 'name_other';
  97 INSERT INTO  profile_private_names (pid, type, id, name)
  98      SELECT  pn.pid, 'firstname', 0, pn.name
  99        FROM  profile_name      AS pn
 100  INNER JOIN  profile_name_enum AS pne ON (pn.typeid = pne.id)
 101       WHERE  pne.type = 'firstname_other';
 102
 103 -- DROP TABLE IF EXISTS profile_name;
 104 -- DROP TABLE IF EXISTS profile_name_enum;
 105
 106 -- vim:set syntax=mysql: