upgrade/1.1.1/06_accounts.sql

   1 ALTER TABLE accounts ADD COLUMN firstname VARCHAR(255) DEFAULT NULL AFTER email;
   2 ALTER TABLE accounts ADD COLUMN lastname VARCHAR(255) DEFAULT NULL AFTER firstname;
   3
   4 UPDATE TABLE  accounts          AS a
   5   INNER JOIN  profile_name_enum AS le ON (le.type = 'lastname')
   6   INNER JOIN  profile_name_enum AS ce ON (ce.type = 'lastname_ordinary')
   7   INNER JOIN  profile_name      AS l  ON (a.uid = l.uid AND le.id = l.typeid)
   8    LEFT JOIN  profile_name      AS c  ON (a.uid = c.uid AND ce.id = c.typeid)
   9          SET  a.lastname = IF(c.uid IS NULL, IF(l.particle != '', l.name, CONCAT(l.particle, ' ', l.name))
  10                                              IF(c.particle != '', c.name, CONCAT(c.particle, ' ', c.name)))
  11        WHERE  a.type IN ('x', 'master', 'phd');
  12
  13 UPDATE TABLE  accounts          AS a
  14   INNER JOIN  profile_name_enum AS fe ON (fe.type = 'firstname')
  15   INNER JOIN  profile_name_enum AS ce ON (ce.type = 'firstname_ordinary')
  16   INNER JOIN  profile_name      AS f  ON (a.uid = f.uid AND fe.id = f.typeid)
  17    LEFT JOIN  profile_name      AS c  ON (a.uid = c.uid AND ce.id = c.typeid)
  18          SET  a.firstname = IF(c.uid IS NULL, f.name, c.name)
  19        WHERE  a.type IN ('x', 'master', 'phd');
  20
  21 -- vim:set syntax=mysql: