From 0fb9c4f71392db849acd51667426775ec3eb843c Mon Sep 17 00:00:00 2001 From: Pascal Corpet Date: Thu, 3 Apr 2008 22:48:39 +0200 Subject: [PATCH] create special upgrade directories for fusionax upgrades --- .../00_names.sql} | 23 ++++++++++------ upgrade/fusionax-0.0.1/update.sh | 32 ++++++++++++++++++++++ 2 files changed, 47 insertions(+), 8 deletions(-) rename upgrade/{0.9.16/01_fusionax.sql => fusionax-0.0.1/00_names.sql} (51%) create mode 100755 upgrade/fusionax-0.0.1/update.sh diff --git a/upgrade/0.9.16/01_fusionax.sql b/upgrade/fusionax-0.0.1/00_names.sql similarity index 51% rename from upgrade/0.9.16/01_fusionax.sql rename to upgrade/fusionax-0.0.1/00_names.sql index 13a0414..e615bfd 100644 --- a/upgrade/0.9.16/01_fusionax.sql +++ b/upgrade/fusionax-0.0.1/00_names.sql @@ -2,6 +2,7 @@ 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', @@ -13,21 +14,34 @@ 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`), + 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` != ''); + CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS SELECT u.user_id, u.matricule_ax, u.promo, @@ -36,11 +50,4 @@ CREATE OR REPLACE ALGORITHM=MERGE VIEW fusionax_xorg_anciens AS FROM `auth_user_md5` AS u INNER JOIN `profile_names_display` AS n ON ( n.user_id = u.user_id ); -INSERT INTO `profile_names_search` ( SELECT `user_id`, `nom`, 'lastname', 10 FROM `auth_user_md5` WHERE `nom` != ''); -INSERT INTO `profile_names_search` ( SELECT `user_id`, `nom_usage`, 'lastname', 10 FROM `auth_user_md5` WHERE `nom` != `nom_usage` AND `nom_usage` != ''); -INSERT INTO `profile_names_search` ( SELECT `user_id`, `nom_ini`, 'lastname', 7 FROM `auth_user_md5` WHERE `nom` != `nom_ini` AND `nom_ini` != '' AND `nom_ini` != `nom_usage`); -INSERT INTO `profile_names_search` ( SELECT `user_id`, `prenom`, 'firstname', 8 FROM `auth_user_md5` WHERE `prenom` != ''); -INSERT INTO `profile_names_search` ( SELECT `user_id`, `prenom_ini`, 'firstname', 5 FROM `auth_user_md5` WHERE `prenom_ini` != `prenom` AND `prenom_ini` != ''); -INSERT INTO `profile_names_search` ( SELECT `user_id`, `profile_nick`, 'surname', 7 FROM `auth_user_quick` WHERE `profile_nick` != ''); - -- vim:set syntax=mysql: diff --git a/upgrade/fusionax-0.0.1/update.sh b/upgrade/fusionax-0.0.1/update.sh new file mode 100755 index 0000000..de82fc0 --- /dev/null +++ b/upgrade/fusionax-0.0.1/update.sh @@ -0,0 +1,32 @@ +#!/bin/bash + +. ../inc/pervasive.sh + +mailman_stop +mailman_templates +mailman_start + + +########################################################### +for sql in *.sql +do + echo -n $sql + $MYSQL x4dat < $sql &>/dev/null || echo -n " ERROR" + echo . +done + +########################################################### + +echo "we will now upgrade the search table (this may be a long operation) + +please hit ^D to continue +" + +cat + +pushd ../../bin +./search.rebuild_db.php +popd + +########################################################### + -- 2.1.4