Esempio n. 1
0
function fs_archive_old_data_impl($older_than, $max_days_to_archive)
{
    if (!isset($max_days_to_archive) || $max_days_to_archive <= 0) {
        return "Invalid max value : {$max_days_to_archive}";
    }
    $hits = fs_hits_table();
    $ranges = fs_archive_ranges();
    $archive_sites = fs_archive_sites();
    $archive_pages = fs_archive_pages();
    $archive_referrers = fs_archive_referrers();
    $archive_useragents = fs_archive_useragents();
    $archive_countries = fs_archive_countries();
    $supports_subquery = fs_mysql_newer_than("4.1.14");
    // mysql bug http://bugs.mysql.com/bug.php?id=13385
    if (!$supports_subquery) {
        return sprintf(fs_r("MySQL 4.1.14 or newer is required for data compacting support"));
    }
    $fsdb =& fs_get_db_conn();
    // no need to archive excluded entries
    // its faster to purge them now than to consider them when archiving.
    fs_purge_excluded_entries($older_than);
    $sql = "SELECT DISTINCT SUBSTRING(timestamp,1,10) start, DATE_ADD(SUBSTRING(timestamp,1,10), INTERVAL 1 DAY) end FROM `{$hits}` WHERE timestamp < FROM_UNIXTIME('{$older_than}') ORDER BY `timestamp`";
    $days = $fsdb->get_results($sql);
    if ($days === false) {
        return fs_db_error();
    }
    $num_processed = 0;
    if (count($days) > 0) {
        foreach ($days as $d) {
            if ($num_processed >= $max_days_to_archive) {
                break;
            }
            if ($fsdb->query("START TRANSACTION") === false) {
                return fs_db_error();
            }
            $start = $d->start . " 00:00:00";
            $end = $d->end . " 00:00:00";
            $sql = "INSERT IGNORE INTO `{$ranges}` ( `range_id` , `range_start` , `range_end` )\tVALUES (NULL , '{$start}','{$end}')";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            $r = $fsdb->get_var("SELECT LAST_INSERT_ID()");
            if ($r === false) {
                return fs_db_error(true);
            }
            $range_id = $r;
            // $range_id will be 0 if the range was already in the database.
            if ($range_id == "0") {
                $range_id = $fsdb->get_var("SELECT `range_id` FROM `{$ranges}` WHERE `range_start` = '{$start}' AND `range_end` = '{$end}'");
                if (!$range_id) {
                    return fs_db_error(true);
                }
            }
            // Sites archive - views
            $sql = "INSERT INTO `{$archive_sites}` (`range_id`,`site_id`,`views`)" . "SELECT '{$range_id}',`site_id`,COUNT(`ip`) " . "FROM `{$hits}`" . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id` " . "ON DUPLICATE KEY UPDATE `views`=`views`+VALUES(`views`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // Sites archive - visits
            $sql = "INSERT INTO `{$archive_sites}`(`range_id`,`site_id`,`visits`)" . "SELECT '{$range_id}',`site_id`,COUNT(DISTINCT(`ip`)) " . "FROM `{$hits}` " . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id` " . "ON DUPLICATE KEY UPDATE `visits`=`visits`+VALUES(`visits`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // pages archive - views
            $sql = "INSERT INTO `{$archive_pages}` (`range_id`,`site_id`,`url_id`,`views`)" . "SELECT '{$range_id}',`site_id`,`url_id`,COUNT(`ip`) " . "FROM `{$hits}` " . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`url_id` " . "ON DUPLICATE KEY UPDATE `views`=`views`+VALUES(`views`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // pages archive - visits
            $sql = "INSERT INTO `{$archive_pages}`(`range_id`,`site_id`,`url_id`,`visits`)" . "SELECT '{$range_id}',`site_id`,`url_id`, COUNT(DISTINCT(`ip`)) " . "FROM `{$hits}`" . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`url_id` " . "ON DUPLICATE KEY UPDATE `visits`=`visits`+VALUES(`visits`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // referrers archive - views
            $sql = "INSERT INTO `{$archive_referrers}` (`range_id`,`site_id`,`url_id`,`views`)" . "SELECT '{$range_id}',`site_id`,`referer_id`,COUNT(`ip`) " . "FROM `{$hits}` " . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`referer_id` " . "ON DUPLICATE KEY UPDATE `views`=`views`+VALUES(`views`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // referrers archive - visits
            $sql = "INSERT INTO `{$archive_referrers}`(`range_id`,`site_id`,`url_id`,`visits`)" . "SELECT '{$range_id}',`site_id`,`referer_id`, COUNT(DISTINCT(`ip`)) " . "FROM `{$hits}`" . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`referer_id` " . "ON DUPLICATE KEY UPDATE `visits`=`visits`+VALUES(`visits`)";
            // useragents archive - views
            $sql = "INSERT INTO `{$archive_useragents}` (`range_id`,`site_id`,`useragent_id`,`views`)" . "SELECT '{$range_id}',`site_id`,`useragent_id`,COUNT(`ip`) " . "FROM `{$hits}` " . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`useragent_id` " . "ON DUPLICATE KEY UPDATE `views`=`views`+VALUES(`views`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // useragents archive - visits
            $sql = "INSERT INTO `{$archive_useragents}`(`range_id`,`site_id`,`useragent_id`,`visits`)" . "SELECT '{$range_id}',`site_id`,`useragent_id`, COUNT(DISTINCT(`ip`)) " . "FROM `{$hits}`" . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`useragent_id` " . "ON DUPLICATE KEY UPDATE `visits`=`visits`+VALUES(`visits`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // countries archive - views
            $sql = "INSERT INTO `{$archive_countries}` (`range_id`,`site_id`,`country_code`,`views`)" . "SELECT '{$range_id}',`site_id`,`country_code`,COUNT(`country_code`) " . "FROM `{$hits}` " . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`country_code` " . "ON DUPLICATE KEY UPDATE `views`=`views`+VALUES(`views`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            // countries archive - visits
            $sql = "INSERT INTO `{$archive_countries}`(`range_id`,`site_id`,`country_code`,`visits`)" . "SELECT '{$range_id}',`site_id`,`country_code`, COUNT(DISTINCT(`ip`)) " . "FROM `{$hits}`" . "WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'" . "GROUP BY `site_id`,`country_code` " . "ON DUPLICATE KEY UPDATE `visits`=`visits`+VALUES(`visits`)";
            $r = $fsdb->query($sql);
            if ($r === false) {
                return fs_db_error(true);
            }
            if ($fsdb->query("DELETE FROM `{$hits}` WHERE `timestamp` >= '{$start}' AND `timestamp` < '{$end}'") === false) {
                return fs_db_error(true);
            }
            if ($fsdb->query("COMMIT") === false) {
                return fs_db_error(true);
            }
            $num_processed++;
        }
    }
    return $num_processed;
}
Esempio n. 2
0
function fs_ajax_purge_excluded_hits(&$response)
{
    if (!fs_ajax_assert_admin($response)) {
        return;
    }
    if (fs_check_is_demo($response)) {
        return;
    }
    $res = fs_purge_excluded_entries();
    if ($res === false) {
        ajax_error($response, sprintf(fs_r('Error purging excluded records: %s'), fs_db_error()));
    } else {
        $response['message'] = fs_r('Purged excluded records');
        $response['fields']['num_excluded'] = fs_get_num_excluded();
    }
}