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