Ejemplo n.º 1
0
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;
}
Ejemplo n.º 2
0
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;
}
Ejemplo n.º 3
0
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;
}
Ejemplo n.º 4
0
/**
 * Returns a table mapping country codes to page views from the country in each row, for the specified time range.
 * range is half inclusive : [). 
 *
 * $site_id site id to work on, or false for all sites, or true for current site in options table. (see not_excluded() doc).
 * $is_unix_time true if the start and end times are unix time, false for mysql datetime
 * $start_time timestamp of start time
 * $end_time timestamp of end time.
 */
function fs_get_views_per_country_range($site_id, $is_unix_time, $start_time, $end_time)
{
    $fsdb =& fs_get_db_conn();
    $hits = fs_hits_table();
    $ua = fs_useragents_table();
    $archive_countries = fs_archive_countries();
    $ranges = fs_archive_ranges();
    $not_excluded = not_excluded();
    $valid_country_code = "`country_code` IS NOT NULL AND `country_code` != '0'";
    if (fs_mysql_newer_than("4.1.14")) {
        $from_site = fs_get_site_id_query($site_id);
        $timestamp_between = fs_timestamp_between($is_unix_time, $start_time, $end_time);
        $select1 = "SELECT `site_id`,`country_code`, count(`country_code`) c\n\t    \t\t\tFROM `{$hits}` h, `{$ua}` ua \n\t    \t\t\tWHERE h.useragent_id = ua.id AND {$not_excluded} AND {$timestamp_between} AND {$valid_country_code}\n\t    \t\t\tGROUP BY `site_id` , `country_code`";
        $timerange_between = fs_time_range_between($is_unix_time, $start_time, $end_time);
        $select2 = "SELECT `site_id`,`country_code` ,`views` AS c FROM `{$archive_countries}` d, `{$ranges}` r " . "WHERE d.range_id = r.range_id AND {$from_site} AND {$timerange_between}  AND {$valid_country_code}";
        $sql = "SELECT `country_code`, sum( u.c ) c " . "FROM ({$select1} UNION ALL {$select2})\n\t\t\t\t`u` GROUP BY `site_id` , `country_code`\n\t\t\t\tORDER BY c DESC";
    } else {
        $sql = "SELECT `country_code`, count(`country_code`) c\n\t\t\t\t\t\tFROM `{$hits}` h,`{$ua}` ua\n\t\t\t\t\t\tWHERE ua.id = h.useragent_id AND \n\t\t\t\t\t\t{$not_excluded} AND {$valid_country_code}";
        $sql .= "AND " . fs_timestamp_between($is_unix_time, $start_time, $end_time);
        $sql .= " GROUP BY `country_code` ORDER BY c DESC";
    }
    return $fsdb->get_results($sql);
}
Ejemplo n.º 5
0
function fs_rebuild_countries_calc_max()
{
    $fsdb =& fs_get_db_conn();
    $hits = fs_hits_table();
    $count = $fsdb->get_var("SELECT COUNT(DISTINCT(IP)) c FROM `{$hits}`");
    if ($count === null) {
        return fs_db_error();
    } else {
        return $count;
    }
}
Ejemplo n.º 6
0
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;
}
Ejemplo n.º 7
0
function fs_add_hit_immediate__($user_id, $site_id, $time = null)
{
    if (!fs_db_valid()) {
        return fs_get_database_status_message();
    }
    $fsdb =& fs_get_db_conn();
    $d = fs_get_hit_data($fsdb, $user_id, $site_id);
    $user_id = $d->user_id;
    $site_id = $d->site_id;
    $remoteaddr = $d->remoteaddr;
    $useragent = $d->useragent;
    $url = $d->url;
    $referer = $d->referer;
    if ($time === null) {
        $time = "NOW()";
    } else {
        $time = $fsdb->escape($time);
    }
    $useragents = fs_useragents_table();
    $urls = fs_urls_table();
    $excluded_ips = fs_excluded_ips_table();
    if ($fsdb->query("START TRANSACTION") === false) {
        return fs_debug_rollback();
    }
    // insert to user agent table (no duplicates)
    $ret = $fsdb->query("INSERT IGNORE INTO `{$useragents}` (`useragent`,`md5`) VALUES ({$useragent} ,MD5(`useragent`))");
    if ($ret === false) {
        return fs_debug_rollback();
    }
    // if we actually inserted a new useragent, we need to match it against existing filters.
    if ($ret > 0) {
        $bots = fs_bots_table();
        $ret = $fsdb->get_row("SELECT ua.id id,count(wildcard) c\n\t\t\t\tFROM {$bots} RIGHT JOIN {$useragents} ua \n\t\t\t\tON useragent REGEXP wildcard \n\t\t\t\tWHERE useragent = {$useragent}\n\t\t\t\tGROUP BY useragent");
        if ($ret === false) {
            return fs_debug_rollback();
        }
        $ret = $fsdb->query("UPDATE {$useragents} SET match_bots='{$ret->c}' WHERE id='{$ret->id}'");
        if ($ret === false) {
            return fs_debug_rollback();
        }
    }
    $save_excluded = fs_get_save_excluded_records() === 'true';
    $c = $fsdb->get_var("SELECT COUNT(ip) FROM `{$excluded_ips}` WHERE `ip` = " . $remoteaddr);
    if ($c === false) {
        return fs_debug_rollback();
    }
    $c = (int) $c;
    $excluded_ip = $c > 0 ? 1 : 0;
    $excluded_users = fs_get_local_option('firestats_excluded_users');
    if ($excluded_users === false) {
        return fs_debug_rollback();
    }
    $excluded_user = $user_id && $excluded_users && in_array($user_id, explode(",", $excluded_users)) ? 1 : 0;
    // get index of useragent in table, can't use LAST_INSERT_ID() here because of the no-dups policy
    $ua_info = $fsdb->get_row("SELECT id,match_bots from `{$useragents}` WHERE `useragent` = {$useragent}");
    $excluded_useragent = $ua_info->match_bots > 0;
    // check if we want to save this
    if (!$save_excluded && ($excluded_useragent || $excluded_user || $excluded_ip)) {
        return true;
    }
    $useragent_id = $ua_info->id;
    if ($useragent_id === false) {
        return fs_debug_rollback();
    }
    // insert to urls table (no duplicates)
    $url = $url ? "{$url}" : "''";
    if ($fsdb->query("INSERT IGNORE INTO `{$urls}` (`url`,`md5`,`add_time`,`host`) \n\t\t\t\t\t VALUES ({$url},MD5(url),{$time},substring_index(substring_index(`url`,'/',3),'/',-1))") === false) {
        return fs_debug_rollback();
    }
    // get index of url in table, can't use LAST_INSERT_ID() here because of the no-dups policy
    $url_id = $fsdb->get_var("SELECT id from " . fs_urls_table() . " WHERE `url` = {$url}");
    if ($url_id === false) {
        return fs_debug_rollback();
    }
    if ($url_id == null) {
        return fs_debug_rollback();
    }
    // update site id of url to current site id.
    // this is only done for the url and not for the referrer:
    // we don't know the site id of the referrer. if it will appear as a url it will be assigned the site_id.
    if (false === $fsdb->get_var("UPDATE `{$urls}` SET `site_id` = {$site_id} WHERE `id` = {$url_id}")) {
        return fs_debug_rollback();
    }
    // insert referers into urls table (no duplicates)
    $referer = $referer ? "{$referer}" : "''";
    require_once FS_ABS_PATH . '/php/searchengines.php';
    $search_engine_id = "NULL";
    $search_terms = "NULL";
    $referrer_breakdown = null;
    if (isset($_SERVER['HTTP_REFERER']) && !empty($_SERVER['HTTP_REFERER'])) {
        $search_params = fs_get_search_terms_and_engine($_SERVER['HTTP_REFERER'], $referrer_breakdown);
        if ($search_params) {
            $id = $search_params->engine_id;
            $terms = $search_params->search_terms;
            if (!empty($id)) {
                $search_engine_id = $fsdb->escape($id);
            }
            if (!empty($terms)) {
                $search_terms = $fsdb->escape($terms);
            }
        }
    }
    $has_scheme = isset($referrer_breakdown['scheme']);
    $optional_host = $has_scheme ? ",`host`" : "";
    $optional_host_query = $has_scheme ? ",substring_index(substring_index(`url`,'/',3),'/',-1)" : "";
    if ($fsdb->query("INSERT IGNORE INTO `{$urls}`(`url`,`md5`,`add_time`,`search_engine_id`,`search_terms` {$optional_host}) VALUES ({$referer},MD5(url),{$time},{$search_engine_id} ,{$search_terms} {$optional_host_query})") === false) {
        return fs_debug_rollback();
    }
    // get index of url in table, can't use LAST_INSERT_ID() here because of the no-dups policy
    $referer_id = $fsdb->get_var("SELECT id from {$urls} WHERE `url` = {$referer}");
    if ($referer_id === false) {
        return fs_debug_rollback();
    }
    if ($referer_id == null) {
        echo "FireStats : Error getting referrer id  ";
        return fs_debug_rollback();
    }
    require_once dirname(__FILE__) . '/ip2country.php';
    $ip2c_res = fs_ip2c($d->ip_address, true);
    $ccode = $ip2c_res ? $fsdb->escape($ip2c_res) : "NULL";
    // insert to database.
    $sql = "INSERT IGNORE INTO " . fs_hits_table() . "\n\t\t\t(site_id,ip,timestamp,url_id,referer_id,useragent_id,session_id,excluded_ip,excluded_user,user_id,country_code) \n\t\t\t\t\tVALUES ({$site_id},\n\t\t\t\t\t\t\t{$remoteaddr},\n\t\t\t\t\t\t\t{$time},\n\t\t\t\t\t\t\t{$url_id},\n\t\t\t\t\t\t\t{$referer_id},\n\t\t\t\t\t\t\t{$useragent_id},\n\t\t\t\t\t\t\t" . (isset($session_id) ? "{$session_id}" : "NULL") . ",\n\t\t\t\t\t\t\t{$excluded_ip},\n\t\t\t\t\t\t\t{$excluded_user},\n\t\t\t\t\t\t\t" . ($user_id ? "{$user_id}" : "NULL") . ",\n\t\t\t\t\t\t\t{$ccode}\n\t\t\t\t\t\t\t)";
    if ($fsdb->query($sql) === false) {
        return fs_debug_rollback();
    }
    if ($fsdb->query("COMMIT") === false) {
        return fs_debug_rollback();
    }
    return true;
}