function fs_engine($engine) { $fs_mysql_version = fs_mysql_version(); if (ver_comp("4.0.18", $fs_mysql_version) > 0 || ver_comp("4.1.2", $fs_mysql_version) > 0) { return " TYPE={$engine}"; } else { return " ENGINE={$engine}"; } }
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_getentries() { $amount = fs_get_num_hits_in_table(); $timezone = fs_get_option('firestats_user_timezone', 'system'); $db_support_tz = ver_comp("4.1.3", fs_mysql_version()) <= 0; $ts = $db_support_tz && $timezone != 'system' ? "CONVERT_TZ(`timestamp`,'system','{$timezone}')" : "timestamp"; if ($amount === false) { return false; } $hits = fs_hits_table(); $ua = fs_useragents_table(); $urls = fs_urls_table(); $not_excluded = not_excluded(true, 'hits'); $not_filtered = fs_not_filtered(); $sql = "SELECT hits.id,ip,useragent,referers.url as referer,referers.search_terms,urls.url as url,{$ts} as timestamp,country_code,urls.title as url_title, referers.title as referrer_title\n\t\t\t\t\tFROM `{$hits}` AS hits,`{$ua}` AS agents,`{$urls}` AS urls,`{$urls}` AS referers\n\t\t\t\t\tWHERE \n\t\t\t\t\t\thits.useragent_id = agents.id AND \n\t\t\t\t\t\thits.url_id = urls.id AND \n\t\t\t\t\t\thits.referer_id = referers.id \n\t\t\t\t\t\tAND {$not_excluded} \n\t\t\t\t\t\tAND {$not_filtered}\n\t\t\t\t\tORDER BY timestamp DESC"; $sql .= " LIMIT {$amount}"; $fsdb =& fs_get_db_conn(); return $fsdb->get_results($sql); }
/** * System information includes: * Unique firestats id * FireStats version * Installation time * PHP version * MySQL version * Server software (apache? IIS? which version?) * Memory limit * Number of sites monitored * Number of sites monitored from each type (how many wordpress blogs, how many drupals etc). */ function fs_get_sysinfo() { require_once dirname(__FILE__) . '/db-common.php'; $s = array(); $s["FIRESTATS_ID"] = fs_get_system_option('firestats_id'); $s["FIRESTATS_VERSION"] = FS_VERSION; $s["INSTALLATION_TIME"] = fs_get_system_option('first_run_time'); $s["PHP_VERSION"] = phpversion(); $s["MYSQL_VERSION"] = fs_mysql_version(); $s["SERVER_SOFTWARE"] = $_SERVER["SERVER_SOFTWARE"]; $s["MEMOEY_LIMIT"] = ini_get('memory_limit'); $sites_table = fs_sites_table(); $sql = "SELECT type,COUNT(type) c from {$sites_table} GROUP BY type"; $fsdb =& fs_get_db_conn(); $res = $fsdb->get_results($sql); if ($res === false) { return $s; } $total = 0; if (count($res) > 0) { foreach ($res as $r) { $s["NUM_SITES_{$r->type}"] = $r->c; $total += $r->c; } } $s["NUM_SITES"] = $total; return $s; }
function fs_systest_database_test() { require_once FS_ABS_PATH . '/php/db-common.php'; $db = fs_get_db_status(); $errors = array(); if ($db['status'] == FS_DB_NOT_CONFIGURED) { $errors[] = fs_systest_error("fatal", "Database is not configured"); } else { // database is configured, we can do some serious tests. $mysql_version = fs_mysql_version(); if (!fs_mysql_newer_than("4.0.17")) { $errors[] = fs_systest_error("fatal", "Your MySQL database version is <b>{$mysql_version}</b>, FireStats requires <b>4.0.17</b> or newer"); } else { if (!fs_mysql_newer_than("4.1.14")) { $errors[] = fs_systest_error("warning", "Your MySQL database version is <b>{$mysql_version}</b>, Some features of FireStats reqruires <b>4.1.14</b> or newer"); } } if ($db['status'] != FS_DB_VALID && $db['status'] != FS_DB_NOT_CONFIGURED) { $errors[] = fs_systest_error("fatal", fs_get_database_status_message($db)); } else { $fsdb =& fs_get_db_conn(); $tables = fs_get_tables_list(); $except = array(fs_pending_date_table()); // don't check this one for InnoDB. $res = $fsdb->get_results("SHOW TABLE STATUS"); if ($res === false) { $errors[] = fs_systest_error("fatal", "Error querying database"); } else { $bad_tables = ""; $found = array(); foreach ($res as $t) { if (in_array($t->Name, $tables) === true) { $found[$t->Name] = true; if (in_array($t->Name, $except) === false) { if (isset($t->Engine) && $t->Engine != "InnoDB" || isset($t->Type) && $t->Type != "InnoDB") { if ($bad_tables == "") { $bad_tables .= $t->Name; } else { $bad_tables .= ", " . $t->Name; } } } } } foreach ($tables as $t) { if (!(isset($found[$t]) && $found[$t])) { $errors[] = fs_systest_error("fatal", "missing table <b>{$t}</b>"); } } if ($bad_tables != "") { $errors[] = fs_systest_error("fatal", "Some of your tables are not using the InnoDB engine, which is required by FireStats. wierd things may happen <b>({$bad_tables})</b>"); } } } } return $errors; }
function fs_mysql_newer_than($version) { return ver_comp($version, fs_mysql_version(), true) <= 0; }
<table border="1"> <tr> <td><?php fs_e('PHP Version'); ?> </td><td><?php echo phpversion(); ?> </td> </tr> <tr> <td><?php fs_e('MySQL Version'); ?> </td><td><?php echo fs_mysql_version(); ?> </td> </tr> <tr> <td><?php fs_e('Memory limit'); ?> </td><td><?php echo ini_get('memory_limit'); ?> </td> </tr> </table>