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'
  

Problemen:

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:

• curios3-db-structure.txt
• 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