upgrade/newdirectory-0.0.1/00_names.sql

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