Esempio n. 1
0
/**
 * 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;
}
Esempio n. 2
0
/**
 * 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;
}