upgrade/newdirectory-0.0.1/00_names.sql

   1 ALTER TABLE profile_display ADD COLUMN yourself VARCHAR(255) NOT NULL,
   2                             ADD COLUMN public_name VARCHAR(255) NOT NULL,
   3                             ADD COLUMN private_name VARCHAR(255) NOT NULL,
   4                             ADD COLUMN directory_name VARCHAR(255) NOT NULL,
   5                             ADD COLUMN short_name VARCHAR(255) NOT NULL,
   6                             ADD COLUMN sort_name VARCHAR(255) NOT NULL,
   7                             CHANGE COLUMN uid pid INT(11),
   8                             CHANGE COLUMN promo_display promo VARCHAR(255);
   9
  10 REPLACE INTO  profile_display (pid, yourself, public_name, private_name, directory_name, short_name, sort_name)
  11       SELECT  u.user_id, u.prenom,
  12               CONCAT(u.prenom, ' ', IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom)),
  13               CONCAT(u.prenom, ' ', IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom),
  14                 IF(q.profile_nick != '', CONCAT(' (alias ', q.profile_nick, ')'), '')),
  15               CONCAT(IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom), ', ', u.prenom),
  16               CONCAT(u.prenom, ' ', IF(u.nom_usage != '', u.nom_usage, u.nom)),
  17               CONCAT(IF(u.nom_usage != '', u.nom_usage, u.nom), ' ', u.prenom)
  18         FROM  auth_user_md5   AS u
  19    LEFT JOIN  auth_user_quick AS q ON (u.user_id = q.user_id);
  20
  21
  22 DROP TABLE IF EXISTS profile_name_search_enum;
  23
  24 CREATE TABLE IF NOT EXISTS profile_name_search_enum (
  25   id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  26   name VARCHAR(255) NOT NULL,
  27   explanations VARCHAR(255) NOT NULL,
  28   flags SET('has_particle', 'not_displayed', 'always_displayed', 'public') NOT NULL,
  29   PRIMARY KEY (id),
  30   UNIQUE (name)
  31 ) CHARSET=utf8;
  32
  33 INSERT INTO  profile_name_search_enum (name, flags)
  34      VALUES  ('Nom patronymique', 'has_particle,always_displayed,public'),
  35              ('Nom marital', 'has_particle,always_displayed,public'),
  36              ('Nom usuel', 'has_particle,always_displayed,public'),
  37              ('Prénom', 'always_displayed,public'),
  38              ('Pseudonyme (nom de plume)', 'always_displayed,public'),
  39              ('Surnom', ''),
  40              ('Prénom usuel', 'public'),
  41              ('Autre prénom', ''),
  42              ('Autre nom', ''),
  43              ('Nom initial', 'has_particle,not_displayed,public'),
  44              ('Prénom initial', 'has_particle,not_displayed,public');
  45
  46
  47 DROP TABLE IF EXISTS profile_name_search;
  48
  49 CREATE TABLE IF NOT EXISTS profile_name_search (
  50   id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id of this name for this user',
  51   pid INT(11) NOT NULL COMMENT 'id of alumni',
  52   name VARCHAR(255) NOT NULL COMMENT 'name to search for',
  53   particle VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'particle of the name to search for',
  54   typeid TINYINT(2) UNSIGNED NOT NULL COMMENT 'type of name',
  55   PRIMARY KEY (pid, id)
  56 ) CHARSET=utf8 COMMENT = 'Names of alumni (search table)';
  57
  58 INSERT INTO  profile_name_search (pid, name, typeid)
  59      SELECT  u.user_id, u.nom, e.id
  60        FROM  auth_user_md5            AS u
  61  INNER JOIN  profile_name_search_enum AS e ON (e.name = 'Nom patronymique')
  62       WHERE  nom != '';
  63
  64 INSERT INTO  profile_name_search (pid, name, typeid)
  65      SELECT  u.user_id, u.nom_ini, e.id
  66        FROM  auth_user_md5            AS u
  67  INNER JOIN  profile_name_search_enum AS e ON (e.name = 'Nom initial')
  68       WHERE  nom_ini != '';
  69
  70 INSERT INTO  profile_name_search (pid, name, typeid)
  71      SELECT  u.user_id, u.prenom, e.id
  72        FROM  auth_user_md5            AS u
  73  INNER JOIN  profile_name_search_enum AS e ON (e.name = 'Prénom')
  74       WHERE  prenom != '';
  75
  76 INSERT INTO  profile_name_search (pid, name, typeid)
  77      SELECT  u.user_id, u.prenom_ini, e.id
  78        FROM  auth_user_md5            AS u
  79  INNER JOIN  profile_name_search_enum AS e ON (e.name = 'Prénom initial')
  80       WHERE  prenom_ini != '';
  81
  82 INSERT INTO  profile_name_search (pid, name, typeid)
  83      SELECT  u.user_id, u.nom_usage, e.id
  84        FROM  auth_user_md5            AS u
  85  INNER JOIN  profile_name_search_enum AS e ON (e.name = 'Nom usuel')
  86       WHERE  nom_usage != '';
  87
  88 INSERT INTO  profile_name_search (pid, name, typeid)
  89      SELECT  q.user_id, q.profile_nick, e.id
  90        FROM  auth_user_quick          AS q
  91  INNER JOIN  profile_name_search_enum AS e ON (e.name = 'Surnom')
  92       WHERE  profile_nick != '';
  93
  94 -- vim:set syntax=mysql: