upgrade/newdirectory-0.0.1/00_names.sql

   1 CREATE TABLE IF NOT EXISTS `profile_names_display` (
   2   `user_id` int(11) NOT NULL COMMENT 'id of alumni',
   3   `display` varchar(100) NOT NULL COMMENT 'name to display',
   4   `sort` varchar(100) NOT NULL COMMENT 'name used for sorting',
   5   `tooltip` varchar(150) NOT NULL COMMENT 'text to explain display name if needed',
   6   `firstname` varchar(50) NOT NULL COMMENT 'first name',
   7   `lastname` varchar(50) NOT NULL COMMENT 'last name',
   8   `yourself` varchar(100) NOT NULL COMMENT 'name we used to speak to him/her',
   9   PRIMARY KEY  (`user_id`),
  10   KEY `sort` (`sort`)
  11 ) CHARSET=utf8 COMMENT='Alumnis'' names to display';
  12
  13 INSERT INTO `profile_names_display` (
  14     SELECT
  15         `user_id`,
  16         CONCAT(`prenom`, ' ',IF(`nom_usage` != '',CONCAT(`nom_usage`,' (',`nom`,')'),`nom`)),
  17         CONCAT(IF(`nom_usage` != '',`nom_usage`,`nom`),', ',`prenom`),
  18         '',
  19         `prenom`,
  20         IF(`nom_usage` != '',`nom_usage`,`nom`),
  21         `prenom`
  22         FROM `auth_user_md5`);
  23
  24 UPDATE `profile_names_display` AS n INNER JOIN `auth_user_md5` AS u ON n.lastname = u.prenom
  25 SET n.tooltip = CONCAT('Prénom : ', n.firstname,' - Nom : ', n.lastname);
  26
  27 CREATE TABLE IF NOT EXISTS `profile_names_search` (
  28   `user_id` int(11) NOT NULL COMMENT 'id of alumni',
  29   `sn_id` smallint(6) NOT NULL COMMENT 'id of this search name in all alumni''s search names',
  30   `search_name` varchar(50) NOT NULL COMMENT 'name to search for',
  31   `name_type` enum('firstname','lastname','surname') NOT NULL default 'lastname' COMMENT 'type of name',
  32   `search_score` smallint(6) NOT NULL COMMENT 'used to sort search results',
  33   `pub` enum('always public','public','private') NOT NULL default 'private' COMMENT 'searchable on public site or only on private',
  34   PRIMARY KEY  (`name_type`,`search_name`,`user_id`),
  35   KEY `user_id` (`user_id`)
  36 ) CHARSET=utf8 COMMENT='Names of alumni (search table)';
  37
  38 INSERT INTO `profile_names_search` ( SELECT `user_id`, 0, `nom`, 'lastname', 10, 'always public' FROM `auth_user_md5` WHERE `nom` != '');
  39 INSERT INTO `profile_names_search` ( SELECT `user_id`, 1, `nom_usage`, 'lastname', 10, 'public' FROM `auth_user_md5` WHERE `nom` != `nom_usage` AND `nom_usage` != '');
  40 INSERT INTO `profile_names_search` ( SELECT `user_id`, 2, `nom_ini`, 'lastname', 7, 'private' FROM `auth_user_md5` WHERE `nom` != `nom_ini` AND `nom_ini` != '' AND `nom_ini` != `nom_usage`);
  41 INSERT INTO `profile_names_search` ( SELECT `user_id`, 3, `prenom`, 'firstname', 8, 'always public' FROM `auth_user_md5` WHERE `prenom` != '');
  42 INSERT INTO `profile_names_search` ( SELECT `user_id`, 4, `prenom_ini`, 'firstname', 5, 'private' FROM `auth_user_md5` WHERE `prenom_ini` != `prenom` AND `prenom_ini` != '');
  43 INSERT INTO `profile_names_search` ( SELECT `user_id`, 5, `profile_nick`, 'surname', 7, 'private' FROM `auth_user_quick` WHERE `profile_nick` != '');
  44
  45 -- vim:set syntax=mysql: