/**
* 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 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;
}
/**
* Add a new tool in tool_list table of a course
*
* @param string claro_label
* @param string access level to tools if null get the default value from main table
* @param string course db name glued
*
* @return boolean
*/
function add_tool_in_course_tool_list($claro_label, $access = null, $courseDbNameGlu = null)
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_cdb_names = claro_sql_get_course_tbl($courseDbNameGlu);
$tbl_course_tool = $tbl_mdb_names['tool'];
$tbl_tool_list = $tbl_cdb_names['tool'];
// get rank of tool in course table
$sql = "SELECT MAX(`rank`) as `max_rank`\n FROM `" . $tbl_tool_list . "`";
$rank = claro_sql_query_get_single_value($sql);
$rank++;
// get id of tool on the platform and default access
$sql = "SELECT `id`, `def_access`\n FROM `" . $tbl_course_tool . "`\n WHERE `claro_label` = '" . claro_sql_escape($claro_label) . "'";
$result = upgrade_sql_query($sql);
if (mysql_num_rows($result)) {
$row = mysql_fetch_array($result);
// if $access emtpy get default access
if (empty($access)) {
$access = $row['access'];
}
// add tool in course_tool table
$sql = "INSERT INTO `" . $tbl_tool_list . "`\n (`tool_id`,`rank`,`access`)\n VALUES\n ('" . $row['id'] . "','" . $rank . "','" . $access . "')";
$result = upgrade_sql_query($sql);
return mysql_insert_id();
} else {
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;
}
/**
* Upgrade module (from main database) to 1.8
* @return step value, 0 if succeed
*/
function upgrade_main_database_module_to_18()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tool = 'MODULE_18';
switch ($step = get_upgrade_status($tool)) {
case 1:
// module
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . $tbl_mdb_names['module'] . "` (\n `id` smallint unsigned NOT NULL auto_increment,\n `label` char(8) NOT NULL default '',\n `name` char(100) NOT NULL default '',\n `activation` enum('activated','desactivated') NOT NULL default 'desactivated',\n `type` enum('tool','applet') NOT NULL default 'applet',\n `script_url` char(255) NOT NULL default 'entry.php',\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM";
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . $tbl_mdb_names['module_info'] . "` (\n id smallint NOT NULL auto_increment,\n module_id smallint NOT NULL default '0',\n version varchar(10) NOT NULL default '',\n author varchar(50) default NULL,\n author_email varchar(100) default NULL,\n author_website varchar(255) default NULL,\n description varchar(255) default NULL,\n website varchar(255) default NULL,\n license varchar(50) default NULL,\n PRIMARY KEY (id)\n ) ENGINE=MyISAM AUTO_INCREMENT=0";
$sqlForUpdate[] = "CREATE TABLE IF NOT EXISTS `" . $tbl_mdb_names['dock'] . "` (\n id smallint unsigned NOT NULL auto_increment,\n module_id smallint unsigned NOT NULL default '0',\n name varchar(50) NOT NULL default '',\n rank tinyint unsigned NOT NULL default '0',\n PRIMARY KEY (id)\n ) ENGINE=MyISAM AUTO_INCREMENT=0";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
case 3:
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "`\n SET claro_label = TRIM(TRAILING '_' FROM claro_label )";
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "`\n SET `script_url` = SUBSTRING_INDEX( `script_url` , '/', -1 ) ";
$sqlForUpdate[] = "UPDATE `" . $tbl_mdb_names['tool'] . "`\n SET `script_url` = 'exercise.php' WHERE `script_url` = 'exercice.php' ";
if (upgrade_apply_sql($sqlForUpdate)) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
unset($sqlForUpdate);
case 4:
// include libray to manage module
require_once $GLOBALS['includePath'] . '/lib/module/manage.lib.php';
$error = false;
$sql = " SELECT id, claro_label, script_url, icon, def_access, def_rank, add_in_course, access_manager\n FROM `" . $tbl_mdb_names['tool'] . "`";
$toolList = claro_sql_query_fetch_all($sql);
foreach ($toolList as $tool) {
$toolLabel = $tool['claro_label'];
// get module path, for read module manifest
$toolPath = get_module_path($toolLabel);
if (($toolInfo = readModuleManifest($toolPath)) !== false) {
// get script url
if (isset($toolInfo['ENTRY'])) {
$script_url = $toolInfo['ENTRY'];
} else {
$script_url = 'entry.php';
}
} else {
// init toolInfo
$toolInfo['LABEL'] = $tool['claro_label'];
$toolInfo['NAME'] = $tool['claro_label'];
$toolInfo['TYPE'] = 'tool';
$toolInfo['VERSION'] = '1.8';
$toolInfo['AUTHOR']['NAME'] = '';
$toolInfo['AUTHOR']['EMAIL'] = '';
$toolInfo['AUTHOR']['WEB'] = '';
$toolInfo['DESCRIPTION'] = '';
$toolInfo['LICENSE'] = 'unknown';
$script_url = $tool['script_url'];
}
// fill table module and module_info
// code from register_module_core from inc/lib/module.manage.lib.php
$sql = "INSERT INTO `" . $tbl_mdb_names['module'] . "`\n SET label = '" . claro_sql_escape($toolInfo['LABEL']) . "',\n name = '" . claro_sql_escape($toolInfo['NAME']) . "',\n type = '" . claro_sql_escape($toolInfo['TYPE']) . "',\n activation = 'activated' ,\n script_url = '" . claro_sql_escape($script_url) . "'";
$moduleId = claro_sql_query_insert_id($sql);
$sql = "INSERT INTO `" . $tbl_mdb_names['module_info'] . "`\n SET module_id = " . (int) $moduleId . ",\n version = '" . claro_sql_escape($toolInfo['VERSION']) . "',\n author = '" . claro_sql_escape($toolInfo['AUTHOR']['NAME']) . "',\n author_email = '" . claro_sql_escape($toolInfo['AUTHOR']['EMAIL']) . "',\n website = '" . claro_sql_escape($toolInfo['AUTHOR']['WEB']) . "',\n description = '" . claro_sql_escape($toolInfo['DESCRIPTION']) . "',\n license = '" . claro_sql_escape($toolInfo['LICENSE']) . "'";
if (upgrade_sql_query($sql) === false) {
$error = true;
break;
}
}
if (!$error) {
$step = set_upgrade_status($tool, $step + 1);
} else {
return $step;
}
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;
}