X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2Fnewdirectory-0.0.1%2F00_names.sql;h=6260e327a1a51f288f515990e96046b44b3fd4dc;hb=c12cc82e1d97b51e19b294942ea9450a34d929cd;hp=bd0d88800a227383493264bebead868d25300e7f;hpb=424fb311ae697673637c58687b1fba3bdec625b0;p=platal.git diff --git a/upgrade/newdirectory-0.0.1/00_names.sql b/upgrade/newdirectory-0.0.1/00_names.sql index bd0d888..6260e32 100644 --- a/upgrade/newdirectory-0.0.1/00_names.sql +++ b/upgrade/newdirectory-0.0.1/00_names.sql @@ -1,45 +1,114 @@ -CREATE TABLE IF NOT EXISTS `profile_names_display` ( - `user_id` int(11) NOT NULL COMMENT 'id of alumni', - `display` varchar(100) NOT NULL COMMENT 'name to display', - `sort` varchar(100) NOT NULL COMMENT 'name used for sorting', - `tooltip` varchar(150) NOT NULL COMMENT 'text to explain display name if needed', - `firstname` varchar(50) NOT NULL COMMENT 'first name', - `lastname` varchar(50) NOT NULL COMMENT 'last name', - `yourself` varchar(100) NOT NULL COMMENT 'name we used to speak to him/her', - PRIMARY KEY (`user_id`), - KEY `sort` (`sort`) -) CHARSET=utf8 COMMENT='Alumnis'' names to display'; - -INSERT INTO `profile_names_display` ( - SELECT - `user_id`, - CONCAT(`prenom`, ' ',IF(`nom_usage` != '',CONCAT(`nom_usage`,' (',`nom`,')'),`nom`)), - CONCAT(IF(`nom_usage` != '',`nom_usage`,`nom`),', ',`prenom`), - '', - `prenom`, - IF(`nom_usage` != '',`nom_usage`,`nom`), - `prenom` - FROM `auth_user_md5`); - -UPDATE `profile_names_display` AS n INNER JOIN `auth_user_md5` AS u ON n.lastname = u.prenom -SET n.tooltip = CONCAT('Prénom : ', n.firstname,' - Nom : ', n.lastname); - -CREATE TABLE IF NOT EXISTS `profile_names_search` ( - `user_id` int(11) NOT NULL COMMENT 'id of alumni', - `sn_id` smallint(6) NOT NULL COMMENT 'id of this search name in all alumni''s search names', - `search_name` varchar(50) NOT NULL COMMENT 'name to search for', - `name_type` enum('firstname','lastname','surname') NOT NULL default 'lastname' COMMENT 'type of name', - `search_score` smallint(6) NOT NULL COMMENT 'used to sort search results', - `pub` enum('always public','public','private') NOT NULL default 'private' COMMENT 'searchable on public site or only on private', - PRIMARY KEY (`name_type`,`search_name`,`user_id`), - KEY `user_id` (`user_id`) -) CHARSET=utf8 COMMENT='Names of alumni (search table)'; - -INSERT INTO `profile_names_search` ( SELECT `user_id`, 0, `nom`, 'lastname', 10, 'always public' FROM `auth_user_md5` WHERE `nom` != ''); -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` != ''); -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`); -INSERT INTO `profile_names_search` ( SELECT `user_id`, 3, `prenom`, 'firstname', 8, 'always public' FROM `auth_user_md5` WHERE `prenom` != ''); -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` != ''); -INSERT INTO `profile_names_search` ( SELECT `user_id`, 5, `profile_nick`, 'surname', 7, 'private' FROM `auth_user_quick` WHERE `profile_nick` != ''); +DROP TABLE IF EXISTS profile_display; +CREATE TABLE profile_display ( + pid INT(11) NOT NULL DEFAULT 0, + yourself VARCHAR(255) NOT NULL, + public_name VARCHAR(255) NOT NULL, + private_name VARCHAR(255) NOT NULL, + directory_name VARCHAR(255) NOT NULL, + short_name VARCHAR(255) NOT NULL, + sort_name VARCHAR(255) NOT NULL, + promo VARCHAR(255) DEFAULT '' NOT NULL, + PRIMARY KEY(pid) +) CHARSET=utf8; + +INSERT INTO profile_display (pid, yourself, public_name, private_name, directory_name, short_name, sort_name) + SELECT u.user_id, u.prenom, + CONCAT(u.prenom, ' ', IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom)), + CONCAT(u.prenom, ' ', IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom), + IF(q.profile_nick != '', CONCAT(' (alias ', q.profile_nick, ')'), '')), + CONCAT(IF(u.nom_usage != '', CONCAT(u.nom_usage, ' (', u.nom, ')') , u.nom), ' ', u.prenom), + CONCAT(u.prenom, ' ', IF(u.nom_usage != '', u.nom_usage, u.nom)), + CONCAT(IF(u.nom_usage != '', u.nom_usage, u.nom), ' ', u.prenom) + FROM auth_user_md5 AS u + LEFT JOIN auth_user_quick AS q ON (u.user_id = q.user_id); + + +DROP TABLE IF EXISTS profile_name_enum; + +CREATE TABLE IF NOT EXISTS profile_name_enum ( + id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT, + name VARCHAR(255) NOT NULL, + explanations VARCHAR(255) NOT NULL, + type VARCHAR(255) NOT NULL, + flags SET('has_particle', 'not_displayed', 'always_displayed', 'public') NOT NULL, + score TINYINT(2) UNSIGNED NOT NULL DEFAULT 10, + PRIMARY KEY (id), + UNIQUE (name) +) CHARSET=utf8; + +INSERT INTO profile_name_enum (name, flags, explanations, type, score) + VALUES ('Nom patronymique', 'has_particle,always_displayed,public', + 'Le nom de famille avec lequel tu es né', 'lastname', 10), + ('Nom marital', 'has_particle,always_displayed,public', + 'Ton nom d\'épouse ou d\'époux', 'lastname_marital', 10), + ('Nom usuel', 'has_particle,always_displayed,public', + 'Le nom de famille que tu utilises usuellement s\'il est différent du nom patronymique, ce peut-être une version racourcie de celui-ci, ton nom marital, une combinaison de ces deux noms...', + 'lastname_ordinary', 10), + ('Prénom', 'always_displayed,public', 'Ton prénom', 'firstname', 10), + ('Pseudonyme (nom de plume)', 'always_displayed,public', + 'Pseudonyme pour les artistes, gens de lettres', 'pseudonym', 10), + ('Surnom', '', 'Surnom à l\'École ou ailleurs', 'nickname', 2), + ('Prénom usuel', 'public', 'Si tu utilises une version raccourcie, francisée... de ton prénom', + 'firstname_ordinary', 10), + ('Autre prénom', '', 'Si tu as d\'autres prénoms et que tu souhaites les faire apparaître', + 'firstname_other', 1), + ('Autre nom', '', 'Si tu as d\'autres noms et que tu souhaites les faire apparaître', + 'name_other', 1), + ('Nom initial', 'has_particle,not_displayed,public', '', 'name_ini', 10), + ('Prénom initial', 'has_particle,not_displayed,public', '', 'firstname_ini', 10); + + +DROP TABLE IF EXISTS profile_name; + +CREATE TABLE IF NOT EXISTS profile_name ( + id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id of this name for this user', + pid INT(11) NOT NULL COMMENT 'id of alumni', + name VARCHAR(255) NOT NULL COMMENT 'name to search for', + particle VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'particle of the name to search for', + typeid TINYINT(2) UNSIGNED NOT NULL COMMENT 'type of name', + PRIMARY KEY (pid, id), + INDEX pid (pid) +) CHARSET=utf8 COMMENT = 'Names of alumni (search table)'; + +INSERT INTO profile_name (pid, name, typeid) + SELECT u.user_id, u.nom, e.id + FROM auth_user_md5 AS u + INNER JOIN profile_name_enum AS e ON (e.name = 'Nom patronymique') + WHERE nom != ''; + +INSERT INTO profile_name (pid, name, typeid) + SELECT u.user_id, u.nom_ini, e.id + FROM auth_user_md5 AS u + INNER JOIN profile_name_enum AS e ON (e.name = 'Nom initial') + WHERE nom_ini != ''; + +INSERT INTO profile_name (pid, name, typeid) + SELECT u.user_id, u.prenom, e.id + FROM auth_user_md5 AS u + INNER JOIN profile_name_enum AS e ON (e.name = 'Prénom') + WHERE prenom != ''; + +INSERT INTO profile_name (pid, name, typeid) + SELECT u.user_id, u.prenom_ini, e.id + FROM auth_user_md5 AS u + INNER JOIN profile_name_enum AS e ON (e.name = 'Prénom initial') + WHERE prenom_ini != ''; + +INSERT INTO profile_name (pid, name, typeid) + SELECT u.user_id, u.nom_usage, e.id + FROM auth_user_md5 AS u + INNER JOIN profile_name_enum AS e ON (e.name = 'Nom usuel') + WHERE nom_usage != ''; + +INSERT INTO profile_name (pid, name, typeid) + SELECT q.user_id, q.profile_nick, e.id + FROM auth_user_quick AS q + INNER JOIN profile_name_enum AS e ON (e.name = 'Surnom') + WHERE profile_nick != ''; + +DROP TABLE IF EXISTS recherche_soundex; + +DELETE FROM search_autocomplete + WHERE name = 'name' OR name = 'firstname' OR name = 'nickname'; -- vim:set syntax=mysql: