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; }