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