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_db_install(&$fsdb)
{
$fsdb->hide_errors();
$version_table = fs_version_table();
$hits_table = fs_hits_table();
$useragents_table = fs_useragents_table();
$sql = "\n\t\tCREATE TABLE IF NOT EXISTS {$hits_table}\n\t\t(\n\t\t\t`id` INTEGER PRIMARY KEY AUTO_INCREMENT " . fs_comment('Primary key') . ",\n\t\t\t`site_id` INTEGER default 1 " . fs_comment('Source site ID, defaults to site 0') . ",\n\t\t\t`ip` VARCHAR(40) NOT NULL DEFAULT 'unknown' " . fs_comment('IP Address of hit source') . ",\n\t\t\t`timestamp` DATETIME NOT NULL " . fs_comment('Hit timestamp') . ",\n\t\t\t`url_id` INTEGER " . fs_comment('Hit URL ID') . ",\n\t\t\t`referer_id` INTEGER " . fs_comment('Referer URL id') . ",\n\t\t\t`useragent_id` INTEGER " . fs_comment('UserAgent ID') . ",\n\t\t\t`session_id` VARCHAR(30) " . fs_comment('Client session ID') . ",\n\t\t\t`user_id` INTEGER default NULL " . fs_comment('User ID in the enclosing system, NULL for unknown user') . ",\n\t\t\t`country_code` INT(4) default NULL " . fs_comment('Country code of IP address or NULL if unknown') . ",\n\t\t\t`excluded_by_user` TINYINT(1) DEFAULT 0 " . fs_comment('1 if user explicitly excluded record, 0 otherwise') . ",\n\t\t\t`excluded_ip` TINYINT(1) DEFAULT 0 " . fs_comment('1 if the ip is in the excluded ips table, 0 otherwise') . ",\n\t\t\t`excluded_user` TINYINT(1) DEFAULT 0 " . fs_comment('1 if the user id is in the excluded users table, 0 otherwise') . ",\n\t\t\tINDEX (`site_id`)\n\t\t)\n\t\t" . fs_comment('Hits table') . fs_engine("InnoDB");
if ($fsdb->query($sql) === FALSE) {
$fsdb->debug();
return false;
}
$r = $fsdb->query("CREATE TABLE IF NOT EXISTS `{$useragents_table}`\n\t\t(\n\t\t\tid INTEGER PRIMARY KEY AUTO_INCREMENT " . fs_comment('Primary key') . ", \n\t\t\tuseragent TEXT " . fs_comment('Useragent string') . ",\n\t\t\tmd5 CHAR(32) NOT NULL,\n\t\t\tmatch_bots INTEGER DEFAULT 0 " . fs_comment('Number of matching bots (useragent wildcards), if 0 the useragent is not exluded') . ",\n\t\t\tUNIQUE(`md5`)\n\t\t) " . fs_comment('User-Agents table') . fs_engine("InnoDB"));
if ($r === FALSE) {
$fsdb->debug();
return false;
}
$urls_table = fs_urls_table();
$r = $fsdb->query("CREATE TABLE IF NOT EXISTS {$urls_table}\n\t\t(\n\t\t\t`id` INTEGER PRIMARY KEY AUTO_INCREMENT " . fs_comment('Primary key') . ",\n\t\t\t`url` TEXT,\n\t\t\t`site_id` INT NULL,\n\t\t\t`md5` CHAR(32) NOT NULL,\n\t\t\t`host` VARCHAR( 40 ) NULL DEFAULT NULL, \n\t\t\t`search_engine_id` SMALLINT NULL DEFAULT NULL " . fs_comment('Search engine ID') . ",\n\t\t\t`search_terms` VARCHAR( 255 ) NULL DEFAULT NULL " . fs_comment('Search terms') . ",\n\t\t\t`title` VARCHAR( 255 ) NULL DEFAULT NULL " . fs_comment('Optional title, or NULL for unkown') . ",\n\t\t\t`type` INT NULL DEFAULT NULL " . fs_comment('Optional type, or NULL for unkown') . ",\n\t\t\t`add_time` DATETIME NOT NULL " . fs_comment('Time this url was added') . ",\n\t\t\tUNIQUE(`md5`),\n\t\t\tINDEX (`search_engine_id`),\n\t\t\tINDEX (`host`),\n\t\t\tINDEX (`type`)\n\t\t) " . fs_comment('Referers table') . fs_engine("InnoDB"));
if ($r === FALSE) {
$fsdb->debug();
return false;
}
$excluded_ip_table = fs_excluded_ips_table();
$r = $fsdb->query("CREATE TABLE IF NOT EXISTS {$excluded_ip_table}\n\t\t(\n\t\t\tid INTEGER PRIMARY KEY AUTO_INCREMENT " . fs_comment('Primary key') . ",\n\t\t\tip VARCHAR(16) NOT NULL\n\t\t) " . fs_comment('List of excluded ips') . fs_engine("InnoDB"));
if ($r === FALSE) {
$fsdb->debug();
return false;
}
$bots_table = fs_bots_table();
$r = $fsdb->query("CREATE TABLE IF NOT EXISTS {$bots_table}\n\t\t(\n\t\t\tid INTEGER PRIMARY KEY AUTO_INCREMENT " . fs_comment('Primary key') . ",\n\t\t\twildcard VARCHAR(100) NOT NULL " . fs_comment('Bots wildcard') . "\n\t\t) " . fs_comment('Bots table') . fs_engine("InnoDB"));
if ($r === FALSE) {
$fsdb->debug();
return false;
}
if (!fs_create_options_table($fsdb)) {
return false;
}
if (!fs_create_sites_table($fsdb)) {
return false;
}
if (!fs_create_archive_tables($fsdb)) {
return false;
}
if (!fs_create_users_table($fsdb)) {
return false;
}
if (!fs_create_pending_data_table($fsdb)) {
return false;
}
if (!fs_create_url_metadata($fsdb)) {
return false;
}
$r = $fsdb->query("CREATE TABLE IF NOT EXISTS `{$version_table}`\n\t(\n\t\t\tversion INTEGER NOT NULL PRIMARY KEY\n\t)" . fs_comment('FireStats datbase schema version') . fs_engine("InnoDB"));
if ($r === FALSE) {
$fsdb->debug();
return false;
}
$r = $fsdb->query("INSERT INTO `{$version_table}` (`version`) VALUES('" . FS_REQUIRED_DB_VERSION . "')");
if ($r === FALSE) {
$fsdb->debug();
return false;
}
return true;
}
