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_db_upgrade_10(&$fsdb, $db_version) { $version_table = fs_version_table(); // a nice little convert loop. $useragents = fs_useragents_table(); $hits = fs_hits_table(); // upgrade to version 2 if ($db_version < 2) { if (!fs_create_options_table($fsdb)) { return false; } if (!fs_update_db_version($fsdb, 2)) { return false; } } // convert charsets, this is instead of collate which does not work on mysql 4.0 if ($db_version < 3) { if (ver_comp("4.1.0", fs_mysql_version()) < 0) { $sqls = array("ALTER TABLE `{$useragents}` DROP INDEX `unique`", "ALTER TABLE `{$useragents}` ADD `md5` CHAR( 32 ) NOT NULL AFTER `useragent`", "UPDATE `{$useragents}` SET `md5` = MD5( `useragent` )", "ALTER TABLE `{$useragents}` ADD UNIQUE (`md5`)", "ALTER TABLE `{$hits}` CHANGE `timestamp` `timestamp` DATETIME NOT NULL"); foreach ($sqls as $sql) { if ($fsdb->query($sql) === false) { $fsdb->debug(); return false; } } // deprecated table, function no longer exists. $referers = fs_table_prefix() . 'firestats_referers'; // convert tables charset to utf-8 $tables = array(fs_excluded_ips_table(), fs_hits_table(), fs_bots_table(), fs_options_table(), $referers, fs_urls_table(), fs_version_table(), fs_useragents_table()); foreach ($tables as $table) { $sql = "ALTER TABLE `{$table}` CONVERT TO CHARSET utf8"; if ($fsdb->query($sql) === false) { $fsdb->debug(); return false; } } } if (!fs_update_db_version($fsdb, 3)) { return false; } } if ($db_version < 4) { /*no longer recalculates bots count*/ if (!fs_update_db_version($fsdb, 4)) { return false; } } if ($db_version < 5) { if ($fsdb->query("ALTER TABLE `{$hits}` ADD `country_code` BLOB NULL DEFAULT NULL AFTER `user_id`") === false) { $fsdb->debug(); return false; } if (!fs_update_db_version($fsdb, 5)) { return false; } } if ($db_version < 6) { require_once FS_ABS_PATH . '/php/rebuild-db.php'; require_once dirname(__FILE__) . '/db-sql.php'; $res = fs_botlist_import(dirname(__FILE__) . '/botlist.txt', true); if ($res != '') { echo $res; return; } // bots are now matched using regular expressions. need to recalculate. fs_recalculate_match_bots(); if (!fs_update_db_version($fsdb, 6)) { return false; } } if ($db_version < 7) { if (fs_column_not_exists($fsdb, $hits, 'site_id')) { if ($fsdb->query("ALTER TABLE `{$hits}` ADD `site_id` INT NOT NULL DEFAULT 1 AFTER `id`") === false) { $fsdb->debug(); return false; } } if (fs_index_not_exists($fsdb, $hits, 'site_id')) { if ($fsdb->query("ALTER TABLE `{$hits}` ADD INDEX (`site_id`)") === false) { $fsdb->debug(); return false; } } if (!fs_update_db_version($fsdb, 7)) { return false; } } if ($db_version < 8) { if (!fs_create_sites_table($fsdb)) { return false; } if (!fs_update_db_version($fsdb, 8)) { return false; } } if ($db_version < 9) { if (!fs_create_archive_tables($fsdb)) { return false; } $urls = fs_urls_table(); $refs = fs_table_prefix() . 'firestats_referers'; // deprecated table, function no longer exists. $sqls = array(fs_index_exists($fsdb, $urls, 'url'), "ALTER TABLE `{$urls}` DROP INDEX `url`", fs_column_type_is_not($fsdb, $urls, 'url', 'Text'), "ALTER TABLE `{$urls}` CHANGE `url` `url` TEXT NULL DEFAULT NULL", fs_column_not_exists($fsdb, $urls, 'md5'), "ALTER TABLE `{$urls}` ADD `md5` CHAR( 32 ) NOT NULL AFTER `url`", true, "UPDATE `{$urls}` SET `md5` = MD5( `url` )", fs_index_not_exists($fsdb, $urls, 'md5'), "ALTER TABLE `{$urls}` ADD UNIQUE (`md5`)", fs_index_exists($fsdb, $refs, 'referer'), "ALTER TABLE `{$refs}` DROP INDEX `referer`", fs_column_type_is_not($fsdb, $refs, 'referer', 'Text'), "ALTER TABLE `{$refs}` CHANGE `referer` `referer` TEXT NULL DEFAULT NULL", fs_column_not_exists($fsdb, $refs, 'md5'), "ALTER TABLE `{$refs}` ADD `md5` CHAR( 32 ) NOT NULL AFTER `referer`", true, "UPDATE `{$refs}` SET `md5` = MD5( `referer`)", fs_index_not_exists($fsdb, $refs, 'md5'), "ALTER TABLE `{$refs}` ADD UNIQUE (`md5`)", fs_column_type_is_not($fsdb, $refs, 'search_engine_id', 'SMALLINT(6)'), "ALTER TABLE `{$refs}` ADD `search_engine_id` SMALLINT(6) NULL DEFAULT NULL " . fs_comment('Search engine ID'), fs_column_type_is_not($fsdb, $refs, 'search_terms', 'VARCHAR(255)'), "ALTER TABLE `{$refs}` ADD `search_terms` VARCHAR(255) NULL DEFAULT NULL " . fs_comment('Search terms'), fs_index_not_exists($fsdb, $refs, 'search_engine_id'), "ALTER TABLE `{$refs}` ADD INDEX ( `search_engine_id` )", fs_column_type_is_not($fsdb, $refs, 'host', 'VARCHAR(40)'), "ALTER TABLE `{$refs}` ADD `host` VARCHAR(40) NULL DEFAULT NULL AFTER `md5`", fs_index_not_exists($fsdb, $refs, 'host'), "ALTER TABLE `{$refs}` ADD INDEX (`host`)", true, "UPDATE `{$refs}` SET `host`=substring_index(substring_index(`referer`,'/',3),'/',-1) WHERE `referer` REGEXP 'http://.*'", fs_column_exists($fsdb, $useragents, 'count'), "ALTER TABLE `{$useragents}` DROP `count`"); if (!fs_apply_db_upgrade($fsdb, $sqls)) { return false; } if (!fs_update_db_version($fsdb, 9)) { return false; } } if ($db_version < 10) { // This is a special case. // Version 9 was a short lived version that already includes this change. // I moved it to version 10 to eliminate the problem of users not completing the upgrade and // getting stuck with version 9.5 (This operation is the longest in 8->9 upgrade and is the most likely cause for things like that). //Converts country code from blob to int. $sqls = array(fs_column_type_is_not($fsdb, $hits, 'country_code', 'INT(4)'), "ALTER TABLE `{$hits}` CHANGE `country_code` `country_code` INT(4) NULL DEFAULT NULL"); if (!fs_apply_db_upgrade($fsdb, $sqls)) { return false; } if (!fs_update_db_version($fsdb, 10)) { return false; } } return true; }
function fs_url_metadata_table() { return fs_table_prefix() . 'firestats_url_metadata'; }