| 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 | CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS |
| 46 | SELECT |
| 47 | u.user_id, u.matricule_ax, u.promo, |
| 48 | n.display AS display_name, n.sort AS sort_name, |
| 49 | u.nom, u.prenom |
| 50 | FROM `auth_user_md5` AS u |
| 51 | INNER JOIN `profile_names_display` AS n ON ( n.user_id = u.user_id ); |
| 52 | |
| 53 | -- vim:set syntax=mysql: |