function upgrade_main_database_to_17()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tool = 'MAINDB17';
switch ($step = get_upgrade_status($tool)) {
case 1:
// create notification table
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . $tbl_mdb_names['notify'] . "` (\n `id` int(11) NOT NULL auto_increment,\n `course_code` varchar(40) NOT NULL default '0',\n `tool_id` int(11) NOT NULL default '0',\n `ressource_id` varchar(255) NOT NULL default '0',\n `group_id` int(11) NOT NULL default '0',\n `user_id` int(11) NOT NULL default '0',\n `date` datetime default '0000-00-00 00:00:00',\n PRIMARY KEY (`id`),\n KEY `course_id` (`course_code`)\n ) ENGINE=MyISAM";
// add enrollment key
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['course'] . "` ADD `enrollment_key` varchar(255) default NULL";
// remove old columns : cahier_charges, scoreShow, description
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['course'] . "` DROP COLUMN `cahier_charges`";
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['course'] . "` DROP COLUMN `scoreShow`";
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['course'] . "` DROP COLUMN `description`";
// add index in rel_class_user table
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['rel_class_user'] . "` ADD INDEX ( `user_id` ) ";
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['rel_class_user'] . "` ADD INDEX ( `class_id` ) ";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, 2);
} else {
return $step;
}
case 2:
register_tool_in_main_database('CLWIKI__', 'wiki/wiki.php', 'wiki.gif');
$step = set_upgrade_status($tool, 0);
return $step;
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
return false;
}
function upgrade_main_database_to_16()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tool = 'MAINDB16';
switch ($step = get_upgrade_status($tool)) {
case 1:
$lenForDbNameOfACourse = 20 + 30;
// (max for prefix + max for code course);
// Update table admin
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['admin'] . "` CHANGE `idUser` `idUser` int(11) unsigned NOT NULL default '0'";
// Create new table class
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . $tbl_mdb_names['class'] . "` (\n `id` int(11) NOT NULL auto_increment,\n `name` varchar(100) NOT NULL default '',\n `class_parent_id` int(11) default NULL,\n `class_level` int(11) NOT NULL default '0',\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM";
// Create new table rel_class_user
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . $tbl_mdb_names['rel_class_user'] . "` (\n `id` int(11) NOT NULL auto_increment,\n `user_id` int(11) NOT NULL default '0',\n `class_id` int(11) NOT NULL default '0',\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM";
// Update table user_id
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['user'] . "` CHANGE `user_id` `user_id` int(11) unsigned NOT NULL auto_increment";
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['user'] . "` CHANGE `creatorId` `creatorId` int(11) unsigned default NULL";
// Update table cours
$sqlForUpdate[] = " ALTER IGNORE TABLE `" . $tbl_mdb_names['course'] . "` CHANGE `dbName` `dbName` varchar(" . $lenForDbNameOfACourse . ") default NULL";
// Create new table config_file
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . $tbl_mdb_names['config_file'] . "` (\n `config_code` varchar(30) NOT NULL default '',\n `config_hash` varchar(40) NOT NULL default '',\n PRIMARY KEY (`config_code` )\n ) ENGINE=MyISAM AVG_ROW_LENGTH=48";
// Create new table sso
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS`" . $tbl_mdb_names['sso'] . "` (\n `id` int(11) NOT NULL auto_increment,\n `cookie` varchar(255) NOT NULL default '',\n `rec_time` datetime NOT NULL default '0000-00-00 00:00:00',\n `user_id` int(11) NOT NULL default '0',\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM";
// Update course tool icon
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "` SET `icon` = 'announcement.gif' WHERE `claro_label` = 'CLANN___'";
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "` SET `icon` = 'assignment.gif' WHERE `claro_label` = 'CLWRK___'";
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "` SET `icon` = 'chat.gif' WHERE `claro_label` = 'CLCHT___'";
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "` SET `icon` = 'document.gif' WHERE `claro_label` = 'CLDOC___'";
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "` SET `icon` = 'learnpath.gif' WHERE `claro_label` = 'CLLNP___'";
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "` SET `icon` = 'user.gif' WHERE `claro_label` = 'CLUSR___'";
// Update table track_e_default
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['track_e_default'] . "` CHANGE `default_user_id` `default_user_id` int(11) NOT NULL default '0'";
// Update table login_user_id
$sqlForUpdate[] = "ALTER IGNORE TABLE `" . $tbl_mdb_names['track_e_login'] . "` CHANGE `login_user_id` `login_user_id` int(11) NOT NULL default '0'";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, 2);
} else {
return $step;
}
case 2:
if (fill_table_config_with_md5()) {
$step = set_upgrade_status($tool, 0);
} else {
return $step;
}
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
return false;
}
/**
* Move tracking data from old tables to new ones.
*
* @return upgrade status
*/
function upgrade_main_database_tracking_data_to_19()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tool = 'MAIN_TRACKING_DATA_19';
switch ($step = get_upgrade_status($tool)) {
case 1:
// drop id to be able to recreate it with correct autoincrement values at last step
$sql = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` DROP `id`";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sql);
case 2:
// get total number of rows in track_e_login
$sql = "SELECT COUNT(*)\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `login_id`, `login_user_id`, `login_date`, `login_ip`\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`\n ORDER BY `login_date`, `login_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "tracking_event`\n ( `user_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$sql .= "(" . (int) $event['login_user_id'] . ",'" . claro_sql_escape($event['login_date']) . "','user_login','" . claro_sql_escape(serialize(array('ip' => $event['login_ip']))) . "'),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 3:
// get total number of rows in track_e_login
$sql = "SELECT COUNT(*)\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `open_id`, `open_date`\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`\n ORDER BY `open_date`, `open_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "tracking_event`\n ( `user_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$sql .= "(NULL,'" . claro_sql_escape($event['open_date']) . "','platform_access',''),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 4:
// order table using dates then recreate primary key with correct autoincrement value
$sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` ORDER BY `date`";
$sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
case 5:
//drop deprecated tracking tables
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`";
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`";
// we should probably keep this table as it may be usefull for history purpose. By the way it is not used in
// any tracking interface.
//$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf( 'mainTblPrefix' ) . "track_e_default`";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
return false;
}
/**
* Upgrade tracking tool to 1.6
* STEP 1 BAcKUP OLD TABLE Before creat the new
* STEP 2 Create The new table
* STEP 3 Update tracking Table
*/
function tracking_upgrade_to_16($course_code)
{
global $currentCourseVersion;
$versionRequiredToProceed = '/^1.5/';
$tool = 'CLTRK';
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
// STEP 1 BAcKUP OLD TABLE Before creat the new
$sql_step1[] = "RENAME TABLE `" . $currentCourseDbNameGlu . "track_e_access` TO `" . $currentCourseDbNameGlu . "track_e_access_15`";
if (!upgrade_apply_sql($sql_step1)) {
return $step;
}
$step = set_upgrade_status($tool, 2, $course_code);
case 2:
// STEP 2 Create The new table
$sql_step2[] = "CREATE TABLE IF NOT EXISTS `" . $currentCourseDbNameGlu . "track_e_access` (\n `access_id` int(11) NOT NULL auto_increment,\n `access_user_id` int(11) default NULL ,\n `access_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `access_tid` int(10) default NULL,\n `access_tlabel` varchar(8) default NULL,\n PRIMARY KEY (`access_id`)\n ) ENGINE=MyISAM COMMENT='Record informations about access to course or tools'";
if (!upgrade_apply_sql($sql_step2)) {
return $step;
}
$step = set_upgrade_status($tool, 3, $course_code);
case 3:
// STEP 3 Update tracking Table
$sql_step3[] = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices` " . "ADD `exe_time` mediumint(8) NOT NULL default '0'";
$sql_step3[] = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices` " . "CHANGE `exe_result` `exe_result` float NOT NULL default '0'";
$sql_step3[] = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices` " . "CHANGE `exe_weighting` `exe_weighting` float NOT NULL default '0'";
if (!upgrade_apply_sql($sql_step3)) {
return $step;
}
$step = set_upgrade_status($tool, 0, $course_code);
default:
return $step;
}
}
return false;
}
/**
* Upgrade introduction text table to 1.7
* @param $course_code string
* @return boolean whether tru if succeed
*/
function introtext_upgrade_to_17($course_code)
{
global $currentCourseVersion;
$versionRequiredToProceed = '/^1.6/';
$tool = 'CLINTRO';
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
// IN 1.7 intro table has new column to prepare multi-intro orderable
// Job for this upgrade
// STEP 1 BAcKUP OLD TABLE Before creat the new
// STEP 2 Create The new table
// STEP 3 FILL The new table with value from the old
// STEP 4 Delete backuped table
////////////////////////////////////
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
// groups of forums
case 1:
// STEP 1 BAcKUP OLD TABLE Before creat the new
$sql_step1[] = "RENAME TABLE `" . $currentCourseDbNameGlu . "tool_intro` TO `" . $currentCourseDbNameGlu . "tool_intro_prev17`";
if (!upgrade_apply_sql($sql_step1)) {
return $step;
}
$step = set_upgrade_status($tool, 2, $course_code);
case 2:
// STEP 2 Create The new table
$sql_step2[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "tool_intro` (\n `id` int(11) NOT NULL auto_increment,\n `tool_id` int(11) NOT NULL default '0',\n `title` varchar(255) default NULL,\n `display_date` datetime default NULL,\n `content` text,\n `rank` int(11) default '1',\n `visibility` enum('SHOW','HIDE') NOT NULL default 'SHOW',\n PRIMARY KEY (`id`) ) ";
if (!upgrade_apply_sql($sql_step2)) {
return $step;
}
$step = set_upgrade_status($tool, 3, $course_code);
case 3:
// STEP 3 FILL The new table with value from the old
$sql = " SELECT `id`, `texte_intro`\n FROM `" . $currentCourseDbNameGlu . "tool_intro_prev17` ";
$result = upgrade_sql_query($sql);
if (!$result) {
return $step;
}
while ($row = mysql_fetch_array($result)) {
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "tool_intro`\n (`tool_id`,`content`)\n VALUES\n ('" . $row['id'] . "','" . claro_sql_escape($row['texte_intro']) . "')";
if (!upgrade_sql_query($sql)) {
return $step;
}
}
$step = set_upgrade_status($tool, 4, $course_code);
case 4:
// STEP 4 Delete OLD
$sql_step4[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "tool_intro_prev17`";
if (!upgrade_apply_sql($sql_step4)) {
return $step;
}
$step = set_upgrade_status($tool, 0, $course_code);
default:
return $step;
}
}
return false;
}
/**
* Upgrade foo tool to 1.8
*
* explanation of task
*
* @param $course_code string
* @return boolean whether true if succeed
*/
function quiz_upgrade_to_18($course_code)
{
global $currentCourseVersion, $currentcoursePathSys;
$versionRequiredToProceed = '/^1.7/';
$tool = 'CLQWZ';
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
// On init , $step = 1
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
$sql_step1[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "qwz_exercise` (\n `id` int(11) NOT NULL auto_increment,\n `title` varchar(255) NOT NULL,\n `description` text NOT NULL,\n `visibility` enum('VISIBLE','INVISIBLE') NOT NULL default 'INVISIBLE',\n `displayType` enum('SEQUENTIAL','ONEPAGE') NOT NULL default 'ONEPAGE',\n `shuffle` smallint(6) NOT NULL default '0',\n `showAnswers` enum('ALWAYS','NEVER','LASTTRY') NOT NULL default 'ALWAYS',\n `startDate` datetime NOT NULL,\n `endDate` datetime NOT NULL,\n `timeLimit` smallint(6) NOT NULL default '0',\n `attempts` tinyint(4) NOT NULL default '0',\n `anonymousAttempts` enum('ALLOWED','NOTALLOWED') NOT NULL default 'NOTALLOWED',\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM ";
$sql_step1[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "qwz_question` (\n `id` int(11) NOT NULL auto_increment,\n `title` varchar(255) NOT NULL default '',\n `description` text NOT NULL,\n `attachment` varchar(255) NOT NULL default '',\n `type` enum('MCUA','MCMA','TF','FIB','MATCHING') NOT NULL default 'MCUA',\n `grade` float NOT NULL default '0',\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM ";
$sql_step1[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "qwz_rel_exercise_question` (\n `exerciseId` int(11) NOT NULL,\n `questionId` int(11) NOT NULL,\n `rank` int(11) NOT NULL default '0'\n ) ENGINE=MyISAM ";
$sql_step1[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "qwz_answer_truefalse` (\n `id` int(11) NOT NULL auto_increment,\n `questionId` int(11) NOT NULL,\n `trueFeedback` text NOT NULL,\n `trueGrade` float NOT NULL,\n `falseFeedback` text NOT NULL,\n `falseGrade` float NOT NULL,\n `correctAnswer` enum('TRUE','FALSE') NOT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM ";
$sql_step1[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "qwz_answer_multiple_choice` (\n `id` int(11) NOT NULL auto_increment,\n `questionId` int(11) NOT NULL,\n `answer` text NOT NULL,\n `correct` tinyint(4) NOT NULL,\n `grade` float NOT NULL,\n `comment` text NOT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM ";
$sql_step1[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "qwz_answer_fib` (\n `id` int(11) NOT NULL auto_increment,\n `questionId` int(11) NOT NULL,\n `answer` text NOT NULL,\n `gradeList` text NOT NULL,\n `wrongAnswerList` text NOT NULL,\n `type` tinyint(4) NOT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM ";
$sql_step1[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "qwz_answer_matching` (\n `id` int(11) NOT NULL auto_increment,\n `questionId` int(11) NOT NULL,\n `answer` text NOT NULL,\n `match` varchar(32) default NULL,\n `grade` float NOT NULL default '0',\n `code` varchar(32) default NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM ";
if (upgrade_apply_sql($sql_step1)) {
$step = set_upgrade_status($tool, 2, $course_code);
} else {
return $step;
}
// handle exercises and questions
// handle exercises and questions
case 2:
// add old exercise data in new exercise table
$sql_step2[] = "INSERT IGNORE INTO `" . $currentCourseDbNameGlu . "qwz_exercise`\n (id,title,description,visibility,displayType,shuffle,showAnswers,startDate,endDate,timeLimit,attempts,anonymousAttempts)\n SELECT id, titre, description, IF(active,'VISIBLE','INVISIBLE'),IF(type = 1,'ONEPAGE','SEQUENTIAL'),random,show_answer,start_date,end_date,max_time,max_attempt,IF(anonymous_attempts = 'YES','ALLOWED','NOTALLOWED')\n FROM `" . $currentCourseDbNameGlu . "quiz_test`";
// add old question data in new question table
$sql_step2[] = "INSERT IGNORE INTO `" . $currentCourseDbNameGlu . "qwz_question`\n (id,title,description,attachment,type,grade)\n SELECT id,question,description,attached_file,\n CASE type WHEN 1 THEN 'MCUA' WHEN 2 THEN 'MCMA' WHEN 3 THEN 'FIB' WHEN 4 THEN 'MATCHING' WHEN 5 THEN 'TF' END,\n ponderation\n FROM `" . $currentCourseDbNameGlu . "quiz_question`";
// add relations between exercises and questions and recalculate rank
$sql = "SELECT exercice_id, question_id, q_position\n FROM `" . $currentCourseDbNameGlu . "quiz_rel_test_question` AS RTQ, `" . $currentCourseDbNameGlu . "quiz_question` AS Q\n WHERE RTQ.question_id = Q.id\n ORDER BY exercice_id ASC, q_position ASC";
$result = claro_sql_query($sql);
if (!$result) {
return $step;
}
$sql_upgrade_qwz_rel = "INSERT INTO `" . $currentCourseDbNameGlu . "qwz_rel_exercise_question`\n ( exerciseId, questionId, rank )\n VALUES\n ";
$rankList = array();
while ($row = mysql_fetch_array($result)) {
if (isset($rankList[$row['exercice_id']])) {
$rankList[$row['exercice_id']]++;
} else {
$rankList[$row['exercice_id']] = 1;
}
$sql_upgrade_qwz_rel_values[] = "(" . $row['exercice_id'] . "," . $row['question_id'] . "," . $rankList[$row['exercice_id']] . ")";
}
if (!empty($sql_upgrade_qwz_rel_values)) {
$sql_step2[] = $sql_upgrade_qwz_rel . implode(",", $sql_upgrade_qwz_rel_values);
}
if (upgrade_apply_sql($sql_step2)) {
$step = set_upgrade_status($tool, 3, $course_code);
} else {
return $step;
}
// handle answers
// handle answers
case 3:
// add MCMA AND MCUA answers (let id auto increment)
$sql_step3[] = "INSERT IGNORE INTO `" . $currentCourseDbNameGlu . "qwz_answer_multiple_choice`\n (questionId,answer,correct,grade,comment)\n SELECT A.question_id,A.reponse,A.correct,A.ponderation,A.comment\n FROM `" . $currentCourseDbNameGlu . "quiz_answer` AS A, `" . $currentCourseDbNameGlu . "quiz_question` AS Q\n WHERE A.question_id = Q.id\n AND ( Q.type = 1 OR Q.type = 2 )";
// Q.type = mcma or mcua
// add FIB answers
$sql = "SELECT Q.id, A.reponse\n FROM `" . $currentCourseDbNameGlu . "quiz_answer` AS A, `" . $currentCourseDbNameGlu . "quiz_question` AS Q\n WHERE A.question_id = Q.id\n AND Q.type = 3";
// Q.type = FIB
$result = claro_sql_query($sql);
if (!$result) {
return $step;
}
while ($row = mysql_fetch_array($result)) {
$reponse = explode('::', $row['reponse']);
$answer = isset($reponse[0]) ? $reponse[0] : '';
$gradeList = isset($reponse[1]) ? $reponse[1] : '';
$type = !empty($reponse[2]) ? $reponse[2] : 1;
$wrongAnswerList = isset($reponse[3]) ? $reponse[3] : '';
$wrongAnswerList = str_replace(',', ',', $wrongAnswerList);
$wrongAnswerList = str_replace('[', ',', $wrongAnswerList);
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "qwz_answer_fib`\n (`questionId`,`answer`, `gradeList`,`wrongAnswerList`,`type`)\n VALUES\n ('" . $row['id'] . "',\n '" . claro_sql_escape($answer) . "',\n '" . claro_sql_escape($gradeList) . "',\n '" . claro_sql_escape($wrongAnswerList) . "',\n '" . claro_sql_escape($type) . "'\n )";
if (!upgrade_sql_query($sql)) {
return $step;
}
}
// add MATCHING answers
$answerList = array();
$sql = "SELECT A.id, A.question_id, A.reponse, A.correct, A.ponderation\n FROM `" . $currentCourseDbNameGlu . "quiz_answer` AS A, `" . $currentCourseDbNameGlu . "quiz_question` AS Q\n WHERE A.question_id = Q.id\n AND Q.type = 4";
// Q.type = MATCHING
$result = claro_sql_query($sql);
if (!$result) {
return $step;
}
while ($row = mysql_fetch_array($result)) {
$answerId = $row['question_id'] . '-' . $row['id'];
$code = md5(uniqid(''));
$answerList[$answerId]['questionId'] = $row['question_id'];
$answerList[$answerId]['answer'] = $row['reponse'];
$answerList[$answerId]['code'] = $code;
// if answer is a rightProposal
if ($row['correct'] == 0) {
$answerList[$answerId]['match'] = 0;
$answerList[$answerId]['grade'] = 0;
} else {
$answerList[$answerId]['match'] = $row['correct'];
$answerList[$answerId]['grade'] = $row['ponderation'];
}
}
foreach ($answerList as $answerId => $answer) {
if ($answer['match'] != 0) {
// find the matching right proposal code for all left proposals
$matchingAnswerId = $answer['questionId'] . '-' . $answer['match'];
if (isset($answerList[$matchingAnswerId]['code'])) {
$answer['match'] = $answerList[$matchingAnswerId]['code'];
}
}
// else right proposal, leave match to 'NULL' value
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "qwz_answer_matching`\n (`questionId`,`answer`, `match`,`grade`,`code`)\n VALUES\n ('" . $answer['questionId'] . "',\n '" . claro_sql_escape($answer['answer']) . "',\n " . ($answer['match'] == 0 ? 'NULL' : "'" . $answer['match'] . "'") . ",\n '" . $answer['grade'] . "',\n '" . $answer['code'] . "'\n )";
if (!upgrade_sql_query($sql)) {
return $step;
}
}
// add TF answers
$answerList = array();
$sql = "SELECT A.id, A.question_id, A.reponse, A.correct, A.comment, A.ponderation\n FROM `" . $currentCourseDbNameGlu . "quiz_answer` AS A, `" . $currentCourseDbNameGlu . "quiz_question` AS Q\n WHERE A.question_id = Q.id\n AND Q.type = 5";
// Q.type = TF
$result = claro_sql_query($sql);
if (!$result) {
return $step;
}
// build an answer array that looks like the new db format
while ($row = mysql_fetch_array($result)) {
$answerId = $row['question_id'];
$answerList[$answerId]['questionId'] = $answerId;
if ($row['id'] == '1') {
// 'True'
$answerList[$answerId]['trueFeedback'] = $row['comment'];
$answerList[$answerId]['trueGrade'] = $row['ponderation'];
$answerList[$answerId]['correctAnswer'] = $row['correct'] == 1 ? 'TRUE' : 'FALSE';
} else {
// $row['id'] = 2 so 'False'
$answerList[$answerId]['falseFeedback'] = $row['comment'];
$answerList[$answerId]['falseGrade'] = $row['ponderation'];
$answerList[$answerId]['correctAnswer'] = $row['correct'] == 1 ? 'FALSE' : 'TRUE';
}
}
foreach ($answerList as $answerId => $answer) {
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "qwz_answer_truefalse`\n (`questionId`,`trueFeedback`, `trueGrade`,`falseFeedback`,`falseGrade`,`correctAnswer`)\n VALUES\n ('" . $answer['questionId'] . "',\n '" . claro_sql_escape($answer['trueFeedback']) . "',\n '" . $answer['trueGrade'] . "',\n '" . claro_sql_escape($answer['falseFeedback']) . "',\n '" . $answer['falseGrade'] . "',\n '" . $answer['correctAnswer'] . "'\n )";
if (!upgrade_sql_query($sql)) {
return $step;
}
}
if (upgrade_apply_sql($sql_step3)) {
$step = set_upgrade_status($tool, 4, $course_code);
} else {
return $step;
}
case 4:
// move attached files
$sql = "SELECT id, attached_file\n FROM `" . $currentCourseDbNameGlu . "quiz_question`";
$result = claro_sql_query($sql);
if (!$result) {
return $step;
}
while ($row = mysql_fetch_array($result)) {
// create new folder
$exe_dirname = $currentcoursePathSys . 'exercise';
// is also the dir where file where in previous versions
if (!is_dir($exe_dirname)) {
if (!@mkdir($exe_dirname, CLARO_FILE_PERMISSIONS)) {
log_message('Error: Cannot create ' . $exe_dirname);
return $step;
}
}
$question_dirname = $exe_dirname . '/question_' . $row['id'];
if (!is_dir($question_dirname)) {
if (!@mkdir($question_dirname, CLARO_FILE_PERMISSIONS)) {
log_message('Error: Cannot create ' . $question_dirname);
return $step;
}
}
// move file
$filename = $row['attached_file'];
if (!empty($filename) && file_exists($exe_dirname . '/' . $filename)) {
if (@rename($exe_dirname . '/' . $filename, $question_dirname . '/' . $filename) === FALSE) {
log_message('Error: Cannot rename ' . $exe_dirname . '/' . $filename . ' to ' . $question_dirname . '/' . $filename);
return $step;
}
}
}
$step = set_upgrade_status($tool, 5, $course_code);
case 5:
$sql_step5 = "DROP TABLE `" . $currentCourseDbNameGlu . "quiz_answer`,\n `" . $currentCourseDbNameGlu . "quiz_question`,\n `" . $currentCourseDbNameGlu . "quiz_rel_test_question`,\n `" . $currentCourseDbNameGlu . "quiz_test`";
if (upgrade_sql_query($sql_step5)) {
$step = set_upgrade_status($tool, 0, $course_code);
} else {
return $step;
}
default:
return $step;
}
}
return false;
}
function upgrade_event_resource_to_110()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tool = 'EVENT_RESOURCE_LINKER';
switch ($step = get_upgrade_status($tool)) {
case 1:
// Create table
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . get_conf('mainTblPrefix') . "event_resource` (\n `event_id` INTEGER NOT NULL,\n `resource_id` INTEGER NOT NULL,\n `tool_id` INTEGER NOT NULL,\n `course_code` VARCHAR(40) NOT NULL,\n PRIMARY KEY (`event_id`, `resource_id`, `tool_id`, `course_code`),\n UNIQUE KEY (`event_id`, `course_code`)\n ) ENGINE=MyISAM";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
return false;
}
function tool_intro_upgrade_to_110($course_code)
{
global $currentCourseVersion;
$versionRequiredToProceed = '/^1.9/';
$tool = 'CLTI';
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
// On init , $step = 1
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
// Are there any tool_intro to migrate ?
$req = "SELECT\n COUNT(id) AS nbToolIntro\n FROM `" . $currentCourseDbNameGlu . "tool_intro`\n WHERE `tool_id` <= 0";
$sql = mysql_query($req);
$sqlForUpdate = array();
if ($sql) {
$res = mysql_fetch_assoc($sql);
// If yes: create a portlet for this course in `rel_course_portlet`
if (isset($res['nbToolIntro']) && $res['nbToolIntro'] > 0) {
// Select the id of the course (int)
$req = "SELECT cours_id AS courseId\n FROM `" . get_conf('mainTblPrefix') . "cours`\n WHERE `code` = '" . $course_code . "'";
$sql = mysql_query($req);
$res = mysql_fetch_assoc($sql);
// Insert the portlet
$sqlForUpdate[] = "INSERT INTO `" . get_conf('mainTblPrefix') . "rel_course_portlet`\n (courseId, rank, label, visible)\n VALUES\n ('" . $res['courseId'] . "', 1, 'CLTI', 1)";
}
}
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1, $course_code);
} else {
return $step;
}
unset($sqlForUpdate);
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
}
return false;
}
/**
* Upgrade tracking to 1.8
* @return step value, 0 if succeed
*/
function upgrade_main_database_tracking_to_18()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tool = 'TRACKING_18';
switch ($step = get_upgrade_status($tool)) {
case 1:
// Add indexes
$sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['track_e_default'] . "` ADD INDEX `default_user_id` ( `default_user_id` )";
$sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['track_e_login'] . "` ADD INDEX `login_user_id` ( `login_user_id` )";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
return false;
}
/**
* Move tracking data from old tables to new ones.
* Note that exercise tracking update is made in quiz_upgrade_to_19 function
* and that tmp table is mostly created to insert in date order data from different old tables
* to the new one
*
* @param integer $course_code
* @return upgrade status
*/
function tracking_data_upgrade_to_19($course_code)
{
$versionRequiredToProceed = '/^1.8/';
$tool = 'CLSTATS_DATA';
global $currentCourseVersion;
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
// drop id to be able to recreate it with correct autoincrement values at last step
$sql = "ALTER TABLE `" . $currentCourseDbNameGlu . "tracking_event` DROP `id`";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sql);
case 2:
// get total number of rows in track_e_access
$sql = "SELECT COUNT(*)\n FROM `" . $currentCourseDbNameGlu . "track_e_access`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `access_id`, `access_user_id`, `access_date`, `access_tid`\n FROM `" . $currentCourseDbNameGlu . "track_e_access`\n ORDER BY `access_date`, `access_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "tracking_event`\n ( `user_id`, `tool_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$user_id = !is_null($event['access_user_id']) ? $event['access_user_id'] : "null";
$tool_id = !is_null($event['access_tid']) ? $event['access_tid'] : "null";
$date = $event['access_date'];
$type = !is_null($event['access_tid']) ? 'tool_access' : 'course_access';
$data = '';
$sql .= "(" . $user_id . "," . $tool_id . "," . "'" . claro_sql_escape($date) . "'," . "'" . claro_sql_escape($type) . "'," . "'" . claro_sql_escape($data) . "'),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 3:
// get total number of rows in track_e_downloads
$sql = "SELECT COUNT(*)\n FROM `" . $currentCourseDbNameGlu . "track_e_downloads`";
$tableRows = (int) claro_sql_query_fetch_single_value($sql);
$recoveredOffset = UpgradeTrackingOffset::retrieve();
// get a subgroup of 250 rows and insert group by group in tracking_event table
for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) {
// we have to store offset to start again from it if something failed
UpgradeTrackingOffset::store($offset);
$query = "SELECT `down_id`, `down_user_id`, `down_date`, `down_doc_path`\n FROM `" . $currentCourseDbNameGlu . "track_e_downloads`\n ORDER BY `down_date`, `down_id`\n LIMIT " . $offset . ", 250";
// then copy these 250 rows to tracking_event
$eventList = claro_sql_query_fetch_all_rows($query);
// build query to insert all 250 rows
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "tracking_event`\n ( `user_id`, `tool_id`, `date`, `type`, `data` )\n VALUES\n ";
//inject former data into new table structure
foreach ($eventList as $event) {
$user_id = !is_null($event['down_user_id']) ? $event['down_user_id'] : "null";
$tool_id = "null";
$date = $event['down_date'];
$type = 'download';
$data = serialize(array('url' => $event['down_doc_path']));
$sql .= "(" . $user_id . "," . $tool_id . "," . "'" . claro_sql_escape($date) . "'," . "'" . claro_sql_escape($type) . "'," . "'" . claro_sql_escape($data) . "'),\n";
}
unset($eventList);
if (upgrade_sql_query(rtrim($sql, ",\n"))) {
unset($sql);
//continue;
} else {
return $step;
}
}
UpgradeTrackingOffset::reset();
$step = set_upgrade_status($tool, $step + 1);
case 4:
// order table using dates then recreate primary key with correct autoincrement value
$sqlForUpdate[] = "ALTER TABLE `" . $currentCourseDbNameGlu . "tracking_event` ORDER BY `date`";
$sqlForUpdate[] = "ALTER TABLE `" . $currentCourseDbNameGlu . "tracking_event` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
case 5:
//drop deprecated tracking tables and temporary table
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "track_e_uploads`";
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "track_e_access`";
$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "track_e_downloads`";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
default:
$step = set_upgrade_status($tool, 0);
return $step;
}
}
return false;
}
