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 tracking tool to 1.8
*/
function tracking_upgrade_to_18($course_code)
{
$versionRequiredToProceed = '/^1.7/';
$tool = 'CLSTATS';
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:
$sql = "UPDATE `" . $currentCourseDbNameGlu . "track_e_access`\n SET access_tlabel = TRIM(TRAILING '_' FROM access_tlabel)";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, 2, $course_code);
} else {
return $step;
}
case 2:
$sql = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices`\n CHANGE `exe_exo_id` `exe_exo_id` int(11)";
if (upgrade_sql_query($sql)) {
$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;
}
function forum_upgrade_to_19($course_code)
{
$versionRequiredToProcedd = '/^1.8/';
$tool = 'CLFRM';
global $currentCourseVersion;
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProcedd, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
$sql = "CREATE TABLE IF NOT EXISTS `" . $currentCourseDbNameGlu . "bb_rel_forum_userstonotify` (\n `notify_id` int(10) NOT NULL auto_increment,\n `user_id` int(10) NOT NULL default '0',\n `forum_id` int(10) NOT NULL default '0',\n PRIMARY KEY (`notify_id`),\n KEY `SECONDARY` (`user_id`,`forum_id`)\n ) ENGINE=MyISAM;";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, $step + 1, $course_code);
} else {
return $step;
}
default:
$step = set_upgrade_status($tool, 0, $course_code);
return $step;
}
}
return false;
}
