=== Curios2 upgrade ===
Beschrijving van de upgrade-procedures. Bevat alle stappen/door te voeren wijzigingen op de bestaande productie code naar de overgang van de nieuwe release.


==== Kopie productie omgeving naar dev. omgeving ====

 * Dump databases:
   * curios
   * curios_tracking
 * Code (inclusief items & sections):
   * curios
   * curios2

==== Kopie & aanpassingen dokeos module op productie omgeving ====
 * Dokeos module kopieren:
{{{
$ cd /home/clarodev/minerva/main
$ cp -pr curios curios2
}}}

 * Config aanpassen:
{{{
$curios_server="curios"; // name of the curios production server
$curios_server_hostname="curios2.ugent.be";
$curios_db_server_hostname = "curios2.ugent.be";
$curios_dbLogin="GEBRUIKER";
$curios_dbPass="WACHTWOORD";
$curios_key = md5(KEY);
$curios_link= "http://$curios_server_hostname/curios2/code/main/remote_login.php";
}}}

 * $curios_link curios.inc.php aanpassen, fix #227:
{{{
$curios_link= "http://$curios_server_hostname/curios2/code/main/remote_login.php";
}}}
> Note: curios_key werd niet aangepast omdat deze in de code naar de dev. omgeving ook niet werd aangepast.


==== Aanpassingen op dev. omgeving ====

 * curios/index.php: LDAP checks in comment
{{{
                //$ldap_id=find_user(array("login" => "$_POST[login]",
"authSource" => "ldap", "origin" => "curios"));

                /*if(!$ldap_id && !$curios_id)
                {
                        $ok=login_with_ldap($login, $password);
                        //echo "ok for login_with_ldap: $ok<br>";
                }

                if($ldap_id && !$ok)
                {
                        $ok=login_with_ldap($login, $password, $ldap_id);
                        //echo "ok for login_with_ldap with ldap_id:
$ok<br>";
                }*/
}}}

 * curios/testtool.api.php: Hard coded user om in te loggen zonder Minerva/Dokeos
{{{
function curios_login($login, $password)
{
        global $sql,$curiosdb,$secret_key;

        if($login=="GEBRUIKER")
                return set_logged_in_user(GEBRUIKER_ID);
}}}

 * curios2/code/inc/config.inc.php: Database host/user/pass aanpassen
{{{
        default:
$host="157.193.39.217";$db="curios";$sql_user="root";$pass="";break;
}}}

 * curios2/code/inc/global.inc.php: Database host/user/pass aanpassen
{{{
                case "157.193.39.217":
$host='157.193.39.217';$db="curios";$login="root";$pass="";  break;
}}}

 * Permissies www-data op:
   * curios2/items
   * curios2/sections
   * curios2/temp

 * 7z path aanpassen in database:
{{{
UPDATE `curios`.`system_settings` SET `zip_executable` = '/var/www/curios2/3rd_party/p7zip_4.42/bin/7z' WHERE `system_settings`.`id` =1
}}}

 * Path naar Curios1 aanpassen in database:
{{{
UPDATE `curios`.`system_paths` SET `path` = 'http://curios2.ugent.be/curios/' WHERE CONVERT( `system_paths`.`name` USING utf8 ) = 'curios1'
}}}

----

=== Database upgrade ===
Onderstaand de verschillen op de database structure productie versus development:

==== Wijzigingen op bestaande tabellen ====
 * examinee_record:
{{{
ALTER TABLE `examinee_record` CHANGE COLUMN `score` `score` float default NULL; # was varchar(10) default NULL
ALTER TABLE `examinee_record` DROP COLUMN `response_id`; # was bigint(20) NOT NULL auto_increment
ALTER TABLE `examinee_record` ADD COLUMN `max_score` float default NULL;
ALTER TABLE `examinee_record` DROP INDEX `task_log_id`; # was INDEX (`task_log_id`) 
ALTER TABLE `examinee_record` DROP PRIMARY KEY; # was (`response_id`) 
ALTER TABLE `examinee_record` ADD PRIMARY KEY (`task_log_id`,`item_id`,`varname`); 
}}}

{{{
ALTER IGNORE TABLE `examinee_record` ADD UNIQUE INDEX(`task_log_id`,`item_id`,`varname`);
ALTER TABLE `examinee_record` DROP INDEX `task_log_id`; # was UNIQUE (`task_log_id`,`item_id`,`varname`) 
}}}

 * examinee_total_score:
{{{
ALTER TABLE `examinee_total_score` CHANGE COLUMN `max_score` `max_score` varchar(10) NOT NULL default ''; # was varchar(10) NOT NULL
}}}

 * item_editable:
{{{
ALTER TABLE `item_editable` CHANGE COLUMN `title` `title` varchar(200) NOT NULL; # was varchar(50) NOT NULL default ''
ALTER TABLE `item_editable` ADD COLUMN `secret_key` varchar(32) NOT NULL default '';
}}} 

 * item_section:
{{{
ALTER TABLE `item_section` CHANGE COLUMN `linkrefid` `linkrefid` bigint(20) NOT NULL; # was varchar(50) NOT NULL default '' 
ALTER TABLE `item_section` CHANGE COLUMN `section_id` `section_id` bigint(20) NOT NULL; # was varchar(50) NOT NULL default ''  
}}}

 * response:
{{{
ALTER TABLE `response` CHANGE COLUMN `status` `status` set('correct','wrong','no_answer','empty','old','active') NOT NULL default 'active'; # was set('correct','wrong','no_answer','empty') NOT NULL default 'empty'
}}}

 * section:
{{{
ALTER TABLE `section` CHANGE COLUMN `id` `id` bigint(20) unsigned NOT NULL auto_increment; # was bigint(20) NOT NULL auto_increment
ALTER TABLE `section` ADD COLUMN `metadata` text NOT NULL;                                                                
ALTER TABLE `section` ADD COLUMN `modified_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
}}}

 * section_user:
{{{
ALTER TABLE `section_user` DROP PRIMARY KEY; # was (`section_id`,`user_id`) 
ALTER TABLE `section_user` ADD PRIMARY KEY (`user_id`,`section_id`);
}}}

 * system_settings:
{{{
ALTER TABLE `system_settings` ADD COLUMN `encrypt_items` set('false','true') NOT NULL default 'true';
}}}

 * task:
{{{
ALTER TABLE `task` CHANGE COLUMN `go_back` `go_back` set('false','true') NOT NULL default 'true'; # was varchar(10) NOT NULL default 'true'
ALTER TABLE `task` DROP COLUMN `password`; # was varchar(255) default NULL
ALTER TABLE `task` ADD COLUMN `language` char(2) NOT NULL default 'nl';
ALTER TABLE `task` ADD COLUMN `branching_enabled` set('false','true') NOT NULL default 'false';
ALTER TABLE `task` ADD COLUMN `css` varchar(50) NOT NULL default 'show.css';
}}}

 * task_log:
{{{
ALTER TABLE `task_log` DROP COLUMN `id`; # was varchar(20) NOT NULL default ''
ALTER TABLE `task_log` ADD COLUMN `reps` int(11) NOT NULL default '-1';
ALTER TABLE `task_log` DROP INDEX `user_id`; # was INDEX (`user_id`)
ALTER TABLE `task_log` ADD INDEX `user_id` (`user_id`,`task_id`);
}}}

 * task_log_ops:
{{{
ALTER TABLE `task_log_ops` DROP INDEX `user_id`; # was INDEX (`user_id`)
ALTER TABLE `task_log_ops` ADD INDEX `user_id` (`user_id`,`task_id`);
}}}

 * user:
{{{
ALTER TABLE `user` DROP INDEX `login`; # was INDEX (`username`)
ALTER TABLE `user` ADD INDEX `login_2` (`username`);
ALTER TABLE `user` ADD INDEX `idx_origin_id` (`origin_id`);
ALTER TABLE `user` CHANGE COLUMN `language` `language` char(2) NOT NULL default 'en'; # was char(2) default 'nl'
}}}

{{{
#!html
<h2 style="color: red">Problemen:</h2>
}}}

Fout bij het aanmaken van de nieuwe primary key: dubbele entries aanwezig op nieuwe key

Eerste duplicate:
{{{
mysql> ALTER TABLE `examinee_record` ADD PRIMARY KEY (`task_log_id`,`item_id`,`varname`);
ERROR 1062 (23000): Duplicate entry '10603368-29862-SCORE' for key 1
mysql>
}}}

View:
{{{
mysql> select * from examinee_record where task_log_id = 10603368 and item_id = 29862 and varname="SCORE";
+-------------+---------+------------+---------+---------+-------+--------+---------+--------+---------------------+
| task_log_id | item_id | section_id | user_id | varname | score | manual | comment | status | edit_date           |
+-------------+---------+------------+---------+---------+-------+--------+---------+--------+---------------------+
|    10603368 |   29862 |          0 |       0 | SCORE   |    10 | false  |         |        | 2007-06-25 13:50:43 |
|    10603368 |   29862 |          0 |       0 | SCORE   |    10 | false  |         |        | 2007-06-25 13:50:43 |
+-------------+---------+------------+---------+---------+-------+--------+---------+--------+---------------------+
2 rows in set (1 min 14.48 sec)
mysql>
}}}

Delete:
{{{
mysql> delete from examinee_record where task_log_id = 10603368 and item_id = 29862 and varname="SCORE" limit 1;
Query OK, 1 row affected (1 min 14.21 sec)
}}}

2de duplicate:
{{{
mysql> ALTER TABLE `examinee_record` ADD PRIMARY KEY (`task_log_id`,`item_id`,`varname`);
ERROR 1062 (23000): Duplicate entry '10603369-29841-SCORE' for key 1
mysql>
}}}

View:
{{{
mysql> select * from examinee_record where task_log_id = 10603369 and item_id = 29841 and varname="SCORE";
+-------------+---------+------------+---------+---------+-------+--------+---------+--------+---------------------+
| task_log_id | item_id | section_id | user_id | varname | score | manual | comment | status | edit_date           |
+-------------+---------+------------+---------+---------+-------+--------+---------+--------+---------------------+
|    10603369 |   29841 |          0 |       0 | SCORE   |    10 | false  |         |        | 2007-06-25 13:50:45 |
|    10603369 |   29841 |          0 |       0 | SCORE   |    10 | false  |         |        | 2007-06-25 13:50:45 |
+-------------+---------+------------+---------+---------+-------+--------+---------+--------+---------------------+
2 rows in set (1 min 12.33 sec)

mysql>
}}}

Delete:
{{{
mysql> delete from examinee_record where task_log_id = 10603369 and item_id = 29841 and varname="SCORE" limit 1;
Query OK, 1 row affected (0.78 sec)
}}}

Enzovoort...

Om alle duplicate entries te controleren:
{{{
mysql> SELECT COUNT(*),`task_log_id`,`item_id`,`varname` FROM `examinee_record` GROUP BY `task_log_id`,`item_id`,`varname` HAVING COUNT(*)>1;
1345 rows in set (10 min 55.77 sec)
mysql>
}}}

Om alle duplicates te verwijderen:
{{{
mysql> SELECT COUNT(*) FROM `examinee_record`; ALTER IGNORE TABLE `examinee_record` ADD UNIQUE INDEX(`task_log_id`,`item_id`,`varname`); SELECT COUNT(*) FROM `examinee_record`;
+----------+
| count(*) |
+----------+
|  6138593 |
+----------+
1 row in set (0.00 sec)

Query OK, 6138593 rows affected (8 min 14.64 sec)
Records: 6138593  Duplicates: 1362  Warnings: 0

+----------+
| count(*) |
+----------+
|  6137231 |
+----------+
1 row in set (0.03 sec)
mysql>
}}}

==== Tabellen enkel op productie ====
 * inschrijving
 * item_templates_v1
 * item_templates_v2
 * langbackup_client_servers
 * langbackup_code
 * langbackup_location
 * langbackup_scripts
 * langbackup_translation
 * langbackup_var
 * mce_permutations
 * mce_permutations_3101
 * mce_permutations_bac
 * mce_permutations_bac1
 * mce_permutations_bac2
 * mce_permutations_bac3
 * mce_permutations_bac4
 * mce_permutations_bu
 * mce_permutations_bu2
 * mce_permutations_bu3
 * migration_log
 * permutations_bac

==== Nieuwe tabellen ====

 * categories:
{{{
CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL default '0',
  `position` int(10) unsigned NOT NULL default '0',
  `collapsed` tinyint(11) NOT NULL default '1' COMMENT '-1 = collapsed, 1 = not collapsed',
  PRIMARY KEY  (`id`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
}}}

 * section_category:
{{{
CREATE TABLE `section_category` (
  `section_id` int(10) unsigned NOT NULL default '0',
  `category_id` int(10) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `position` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`section_id`,`category_id`,`uid`),
  KEY `idx_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
}}}

 * user_category:
{{{
CREATE TABLE `user_category` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `collapsed` tinyint(1) NOT NULL default '0',
  `position` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
}}}

De volledige structuren van de tabellen zoals op development:
 * [attachment:curios3-db-structure.txt]
 * [attachment:curios3-tracking-structure.txt]

Merk op, dit zijn de structuren van de development omgeving zonder de autoincrement values. 

==== Update tabellen ====
Leegmaken van de taaltabellen, system tables en item_templates: 
{{{
mysql> truncate lang_translation;
mysql> truncate lang_var;
mysql> truncate lang_scripts;
mysql> truncate lang_location;
mysql> truncate lang_code;
mysql> truncate system_tables;
mysql> truncate item_templates;
}}}

----
=== Upgrade Dokeos module ===

 * Exporteer de code van svn:
{{{
$ svn export svn+ssh://curios3.ugent.be/svn/dokeos-module/trunk/1.6.5
}}}

 * Pas de config file aan (config.inc.php):
{{{
$curios_server="curios"; // name of the curios production server
$curios_server_hostname="curios2.ugent.be";
$curios_db_server_hostname = "curios2.ugent.be";
$curios_dbLogin="GEBRUIKER";
$curios_dbPass="WACHTWOORD";
$curios_key = md5(KEY);
$curios_link= "http://$curios_server_hostname/curios/remote_login.php";

$main_server="minerva"; // name of the main dokeos production server
$main_server_ip[]="157.193.40.86"; // ip of main server
$main_server_ip[]="157.193.40.87"; // ip of main server //optional
$main_server_ip[]="157.193.40.88"; // ip of main server //optional
$main_server_ip[]="157.193.43.9"; // loadbalancer //optional
}}}

 * $curios_link curios.inc.php aanpassen, fix #227:
{{{
$curios_link= "http://$curios_server_hostname/curios2/code/main/remote_login.php";
}}}

 * Kopieer de MCE folder naar de export

 * Kopieer het taalbestand lang/dutch/curios.inc.php naar dokeos/main/lang/dutch/curios2.inc.php en pas volgende files aan:
{{{
// setting the language file
        $langFile="curios2";
}}}
   * curios.php
   * curiosnewtask.php
   * curiostask.php
   * curiostaskdetail.php
   * curiostaskeditall.php

 * Pas de volgende files aan om #222 te fixen:
{{{
curios.php:     include('../inc/global.inc.php');
curiosnewtask.php:      include('../inc/global.inc.php');
curiostask.php: include('../inc/global.inc.php');
curiostaskdetail.php:   include('../inc/global.inc.php');
curiostaskeditall.php:  include('../inc/global.inc.php');
}}}

----
=== Apache ===

Plaats .htaccess in de webroot:
{{{
RewriteEngine On
Options +FollowSymlinks
RewriteBase /
RewriteRule curiosdl/(.*)$ /curios2/code/main/download.php?url_path=/$1 [QSA,L]
}}}

Mod_rewrite enablen op webserver (alsook de mogelijkheid om 'Options' param toe te laten):
{{{
# cd /etc/apache2/mods-enabled
# ln -s ../mods-available/rewrite.load .
}}}

{{{
# nano /etc/apache2/sites-enabled/curios

AllowOverride AuthConfig FileInfo Options
}}}

Apache herstarten:
{{{
# /etc/init.d/apache2 restart
}}}