function fs_create_archive_tables(&$fsdb)
{
$ranges = fs_archive_ranges();
$sql = "CREATE TABLE IF NOT EXISTS `{$ranges}` (\n\t\t`range_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY " . fs_comment('Range ID') . ",\n\t\t`range_start` DATETIME NOT NULL " . fs_comment('Range start time') . ",\n\t\t`range_end` DATETIME NOT NULL " . fs_comment('Range end time') . ",\n\t\tUNIQUE `ranges index` (`range_start`,`range_end`)\n\t\t)" . fs_comment('Archive ranges table') . fs_engine("InnoDB");
$r = $fsdb->query($sql);
if ($r === false) {
$fsdb->debug();
return false;
}
// create baseline range
$r = $fsdb->query("INSERT IGNORE INTO `{$ranges}` (`range_id`,`range_start`,`range_end`) VALUES ('1' , '1000-01-01 00:00:00', '1000-01-01 00:00:00')");
if ($r === false) {
$fsdb->debug();
return false;
}
$r = $fsdb->query(fs_get_create_site_archive());
if ($r === false) {
$fsdb->debug();
return false;
}
$r = $fsdb->query(fs_get_create_archive_with_id(fs_archive_pages(), 'url_id', 'Archive for pages'));
if ($r === false) {
$fsdb->debug();
return false;
}
$r = $fsdb->query(fs_get_create_archive_with_id(fs_archive_referrers(), 'url_id', 'Archive for referrers'));
if ($r === false) {
$fsdb->debug();
return false;
}
$r = $fsdb->query(fs_get_create_archive_with_id(fs_archive_useragents(), 'useragent_id', 'Archive for useragents'));
if ($r === false) {
$fsdb->debug();
return false;
}
$countries_archive = fs_archive_countries();
$sql = "CREATE TABLE IF NOT EXISTS `{$countries_archive}` (\n\t\t`range_id` INT NOT NULL " . fs_comment('Range ID') . ",\n\t\t`site_id` INTEGER NOT NULL " . fs_comment('Site ID of this data') . ",\n\t\t`country_code` INTEGER NOT NULL " . fs_comment('Country code for this data') . ",\n\t\t`views` INTEGER NOT NULL " . fs_comment('Number of views from country in time range') . ",\n\t\t`visits` INTEGER NOT NULL " . fs_comment('Number of visits from country in time range') . ",\n\t\tUNIQUE `index` (`range_id`,`site_id`,`country_code`)\n\t\t) " . fs_comment("Countries archive table") . fs_engine("InnoDB");
$r = $fsdb->query($sql);
if ($r === false) {
$fsdb->debug();
return false;
}
return true;
}
function fs_get_popular_pages_range($num_limit, $is_unix_time, $start_time, $end_time, $site_id, $type = null)
{
$fsdb =& fs_get_db_conn();
$num_limit = $fsdb->escape($num_limit);
$hits = fs_hits_table();
$urls = fs_urls_table();
$url_metadata = fs_url_metadata_table();
$ua = fs_useragents_table();
$ranges = fs_archive_ranges();
$archive_pages = fs_archive_pages();
$not_excluded = not_excluded($site_id, 'h');
$limit = $num_limit ? " LIMIT {$num_limit}" : "";
if (fs_mysql_newer_than("4.1.14")) {
$select1 = "SELECT `site_id` , `url_id` as uid, count( `url_id` ) `cc` \n\t \t\t\tFROM `{$hits}` h, `{$ua}` ua \n\t \t\t\tWHERE h.useragent_id = ua.id AND {$not_excluded} AND " . fs_timestamp_between($is_unix_time, $start_time, $end_time) . "\n\t \t\t\tGROUP BY `site_id` , `url_id`";
$select2 = "SELECT `site_id` , `url_id` as uid, `views` AS cc FROM `{$archive_pages}` d, `{$ranges}` r \n\t\t\t\t\tWHERE d.range_id = r.range_id AND " . fs_get_site_id_query($site_id) . " \n\t\t\t\t\tAND " . fs_time_range_between($is_unix_time, $start_time, $end_time);
$sql = "SELECT uid as url_id, u.site_id, url, sum( cc ) c, title \n\t\t\t\tFROM ({$select1} UNION ALL {$select2}) `u2` , `{$urls}` `u` \n\t\t\t\tWHERE u.id = uid " . ($type != null ? "AND type = '{$type}'" : "") . "\n\t\t\t\tGROUP BY `site_id`, `uid`\n\t\t\t\tORDER BY c DESC " . $limit;
} else {
$sql = "SELECT h.url_id,u.`site_id`,`url`, COUNT(h.url_id) `c`, title \n \t\t\tFROM `{$hits}` h, `{$ua}` ua , `{$urls}` u \n \t\t\tWHERE h.url_id = u.id \n\t\t\t\t" . ($type != null ? " AND type = '{$type}'" : "") . "\n\t\t\t\tAND h.useragent_id = ua.id \n\t\t\t\tAND {$not_excluded} \n\t\t\t\tAND " . fs_timestamp_between($is_unix_time, $start_time, $end_time) . "\n \t\t\tGROUP BY `site_id` , h.url_id ORDER BY c DESC " . $limit;
}
return $fsdb->get_results($sql);
}
function fs_db_upgrade_11(&$fsdb, $db_version)
{
$r = fs_create_users_table($fsdb);
if ($r === FALSE) {
echo fs_db_error();
return false;
}
$options = fs_options_table();
$referers = fs_table_prefix() . 'firestats_referers';
// deprecated table, function no longer exists.
$urls = fs_urls_table();
$hits = fs_hits_table();
$archive_pages = fs_archive_pages();
$archive_ranges = fs_archive_ranges();
$user_id_missing = fs_column_not_exists($fsdb, $options, 'user_id');
$sqls = array(fs_index_exists($fsdb, $options, 'option_key'), "ALTER TABLE `{$options}` DROP INDEX `option_key`", fs_column_exists($fsdb, $options, 'id'), "ALTER TABLE `{$options}` DROP `id`", $user_id_missing, "ALTER TABLE `{$options}` ADD `user_id` INT NOT NULL FIRST", fs_index_not_exists($fsdb, $options, 'user_id_option_key_unique'), "ALTER TABLE `{$options}` ADD UNIQUE `user_id_option_key_unique` ( `user_id`,`option_key`)", fs_column_exists($fsdb, $referers, 'referer'), "ALTER TABLE `{$referers}` CHANGE `referer` `url` TEXT NULL DEFAULT NULL", fs_column_not_exists($fsdb, $referers, 'site_id'), "ALTER TABLE `{$referers}` ADD `site_id` INT NULL AFTER `url`", fs_column_not_exists($fsdb, $urls, 'site_id'), "ALTER TABLE `{$urls}` ADD `site_id` INT NULL AFTER `url`", fs_column_not_exists($fsdb, $urls, 'new_url_id'), "ALTER TABLE `{$urls}` ADD `new_url_id` INT NULL", fs_column_not_exists($fsdb, $referers, 'title'), "ALTER TABLE `{$referers}` ADD `title` VARCHAR( 255 ) NULL DEFAULT NULL", fs_column_not_exists($fsdb, $referers, 'type'), "ALTER TABLE `{$referers}` ADD `type` INT NULL DEFAULT NULL", fs_index_not_exists($fsdb, $referers, 'type'), "ALTER TABLE `{$referers}` ADD INDEX `type` (`type`)", fs_column_not_exists($fsdb, $referers, 'add_time'), "ALTER TABLE `{$referers}` ADD `add_time` DATETIME NOT NULL");
if (!fs_apply_db_upgrade($fsdb, $sqls)) {
return false;
}
// if created user id, change the following options to system options.
if ($user_id_missing) {
$system_options = array('firestats_id', 'first_run_time', 'archive_method', 'botlist_last_version_check_time', 'botlist_last_version_info_on_server', 'botlist_version_check_enabled', 'botlist_version_hash', 'firestats_last_version_check_time', 'firestats_last_version_info_on_server', 'firestats_version_check_enabled', 'ip-to-country-db_last_version_check_time', 'ip-to-country-db_last_version_info_on_server', 'ip-to-country-db_version_check_enabled', 'archive_method', 'archive_older_than', 'auto_bots_list_update', 'last_sent_sysinfo', 'user_agreed_to_send_system_information', 'last_version_check_time');
foreach ($system_options as $opt) {
$sql = "UPDATE `{$options}` SET `user_id` = '-1' WHERE `option_key`= '{$opt}'";
if (false === $fsdb->query($sql)) {
echo fs_db_error();
return false;
}
}
}
if (!fs_create_pending_data_table($fsdb)) {
return false;
}
if (!fs_create_url_metadata($fsdb)) {
return false;
}
$fsdb->query("START TRANSACTION");
if (fs_mysql_newer_than("4.1.0")) {
// pupulate current urls table with site ids based on urls in the hits table.
$sql = "UPDATE `{$urls}` u,\n\t\t\t\t (SELECT DISTINCT(u.id) url_id,h.site_id FROM `{$urls}` u,`{$hits}` h WHERE u.id = h.url_id ORDER BY `timestamp` DESC) k \n\t\t\t\tSET u.site_id = k.site_id \n\t\t\t\tWHERE u.id = k.url_id";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
// pupulate current urls table with site ids based on urls in the archive pages table.
// this step is not needed for mysql < 4.1.0 because we only support archving for mysql > 4.1.14
$sql = "UPDATE `{$urls}` u,(SELECT site_id, url_id, max(range_start) from `{$archive_pages}` p, `{$archive_ranges}` r WHERE p.range_id = r.range_id GROUP BY url_id) k \n\t\t\t\tSET u.site_id = k.site_id WHERE u.id = k.url_id";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
} else {
// mysql 4.0 does not support nested update-selects. need to update them one by one.
// get site ids of urls
$sql = "SELECT DISTINCT(u.id) url_id,h.site_id FROM `{$urls}` u,`{$hits}` h WHERE u.id = h.url_id ORDER BY `timestamp` DESC";
$res = $fsdb->get_results($sql);
if ($res === FALSE) {
echo fs_db_error(true);
return false;
}
// pupulate current urls table with site ids.
foreach ($res as $u) {
$sql = "UPDATE `{$urls}` u SET u.site_id = '{$u->site_id}' WHERE u.id = {$u->url_id}";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
}
}
// insert all urls in urls table into referrers table, along with their corrosponding site_id
$sql = "INSERT IGNORE INTO `{$referers}` (`url`,`md5`) SELECT url,MD5(url) FROM `{$urls}`";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
// set the site id in the referrers table for urls that were in the urls table.
$sql = "UPDATE `{$referers}` r, `{$urls}` u SET r.site_id = u.site_id WHERE u.md5 = r.md5";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
// update host column of referrers table (lines inserted from urls table does not contain them).
$sql = "UPDATE `{$referers}` SET `host`=substring_index(substring_index(`url`,'/',3),'/',-1) WHERE `url` REGEXP 'http://.*'";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
// populate new_url_id row in urls table based on the url id in the referrers table
$sql = "UPDATE `{$urls}` u,`{$referers}` r SET `new_url_id`= r.id WHERE MD5(u.url) = r.md5";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
// update add_time for existing urls.
if (fs_mysql_newer_than("4.1.0")) {
// set add_time to urls in the urls table
$select = "SELECT id,MIN(`timestamp`) `timestamp` FROM (SELECT url_id AS id, MIN(`timestamp`) `timestamp` FROM `{$hits}` GROUP BY `url_id` UNION SELECT `referer_id` AS `id`, MIN(`timestamp`) `timestamp` FROM `{$hits}` GROUP BY `referer_id`) `u` GROUP BY id";
$sql = "UPDATE `{$referers}`,({$select}) k SET `add_time` = k.`timestamp` WHERE {$referers}.id = k.id";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
$select = "SELECT id,MIN(`timestamp`) `timestamp` FROM (SELECT url_id AS id, MIN(`timestamp`) `timestamp` FROM `{$hits}` GROUP BY `url_id` UNION SELECT `referer_id` AS `id`, MIN(`timestamp`) `timestamp` FROM `{$hits}` GROUP BY `referer_id`) `u` GROUP BY id";
$sql = "UPDATE `{$referers}`,({$select}) k SET `add_time` = k.`timestamp` WHERE {$referers}.id = k.id";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
} else {
$sql = "SELECT referer_id id, MIN(timestamp) `timestamp` FROM `{$hits}` GROUP BY referer_id LIMIT 10";
$res = $fsdb->get_results($sql);
if ($res === FALSE) {
echo fs_db_error(true);
return false;
}
foreach ($res as $ref) {
$r = $fsdb->query("UPDATE {$referers} r set r.add_time = '{$ref->timestamp}' WHERE r.id = '{$ref->id}'");
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
}
}
// if unique index 'ip' exists in hits table, drop it
if (fs_index_exists($fsdb, $hits, 'ip')) {
// drop unique index.
// in fact, some tests shows that we don't really need it from the performance pov, and I really don't understand why we need
// it from the uniqueness pov.
$r = $fsdb->query("ALTER TABLE `{$hits}` DROP INDEX `ip`");
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
}
// update hits table with new url ids.
$sql = "UPDATE `{$hits}` h,`{$urls}` u SET h.url_id = u.new_url_id WHERE h.url_id = u.id";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
// if unique index exists in archive pages table, drop it
if (fs_index_exists($fsdb, $archive_pages, 'index')) {
// drop unique index for the duration of the update.
$r = $fsdb->query("ALTER TABLE `{$archive_pages}` DROP INDEX `index`");
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
}
// update pages archive table table with new url ids.
$sql = "UPDATE `{$archive_pages}` h,`{$urls}` u SET h.url_id = u.new_url_id WHERE h.url_id = u.id";
$r = $fsdb->query($sql);
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
// re-establish unique index
$r = $fsdb->query("ALTER TABLE `{$archive_pages}` ADD UNIQUE `index` ( `range_id` , `site_id` , `url_id` )");
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
$fsdb->query("COMMIT");
if (fs_table_exists($fsdb, $referers)) {
$r = $fsdb->query("DROP TABLE `{$urls}");
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
$r = $fsdb->query("RENAME TABLE `{$referers}` TO `{$urls}`");
if ($r === FALSE) {
echo fs_db_error(true);
return false;
}
}
fs_add_pending_maintanence_job('recalculate_search_engine_terms');
if (!fs_update_db_version($fsdb, 11)) {
return false;
}
return true;
}
function fs_get_tables_list()
{
$a = array(fs_version_table(), fs_hits_table(), fs_useragents_table(), fs_urls_table(), fs_excluded_ips_table(), fs_bots_table(), fs_options_table(), fs_sites_table(), fs_archive_ranges(), fs_archive_sites(), fs_archive_pages(), fs_archive_referrers(), fs_archive_useragents(), fs_archive_countries(), fs_users_table(), fs_pending_date_table(), fs_url_metadata_table());
return $a;
}
