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  accounts          AS a
   5 INNER JOIN  account_profiles  AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms))
   6 INNER JOIN  profile_name_enum AS le ON (le.type = 'lastname')
   7 INNER JOIN  profile_name_enum AS ce ON (ce.type = 'lastname_ordinary')
   8 INNER JOIN  profile_name      AS l  ON (ap.pid = l.pid AND le.id = l.typeid)
   9  LEFT JOIN  profile_name      AS c  ON (ap.pid = c.pid AND ce.id = c.typeid)
  10        SET  a.lastname = IF(c.pid IS NULL, IF(l.particle != '', l.name, CONCAT(l.particle, ' ', l.name)),
  11                                            IF(c.particle != '', c.name, CONCAT(c.particle, ' ', c.name)))
  12      WHERE  a.type IN ('x', 'master', 'phd');
  13
  14     UPDATE  accounts          AS a
  15 INNER JOIN  account_profiles  AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms))
  16 INNER JOIN  profile_name_enum AS fe ON (fe.type = 'firstname')
  17 INNER JOIN  profile_name_enum AS ce ON (ce.type = 'firstname_ordinary')
  18 INNER JOIN  profile_name      AS f  ON (ap.pid = f.pid AND fe.id = f.typeid)
  19  LEFT JOIN  profile_name      AS c  ON (ap.pid = c.pid AND ce.id = c.typeid)
  20        SET  a.firstname = IF(c.pid IS NULL, f.name, c.name)
  21      WHERE  a.type IN ('x', 'master', 'phd');
  22
  23 -- vim:set syntax=mysql: