Esempio n. 1
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;
}
Esempio n. 2
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);
}
Esempio n. 3
0
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;
}
Esempio n. 4
0
		</tr>
	<?php 
if (fs_is_admin() || fs_is_demo()) {
    ?>
	<tr>
		<td class="config_cell" colspan="2">
		
		<h3><?php 
    fs_e('Compact old data');
    ?>
			<?php 
    fs_create_wiki_help_link('ArchiveOldData');
    ?>
		</h3>
		<?php 
    if (fs_mysql_newer_than("4.1.14")) {
        ?>
		<?php 
        $method_dropbox = fs_get_archive_method_dropbox();
        $num_dropbox = fs_get_archive_dropbox();
        ?>
		<div style="padding-left:10px;padding-right:10px">
			<?php 
        echo sprintf(fs_r('%s compact data older than %s'), $method_dropbox, $num_dropbox);
        ?>
		&nbsp;&nbsp;&nbsp;
		<button class="button" id="fs_archive_button" onclick="toggleArchiveOldData()">
			<?php 
        fs_e('Compact now');
        ?>
</button>
Esempio n. 5
0
function fs_ajax_getNextUserMessage(&$response)
{
    require_once FS_ABS_PATH . '/php/html-utils.php';
    require_once FS_ABS_PATH . '/php/utils.php';
    // currently we don't show any user messages in demo mode.
    if (fs_is_demo()) {
        return;
    }
    if (fs_is_admin() && fs_get_system_option("user_agreed_to_send_system_information", '') == '') {
        fs_ajax_get_window($response, 'ask_to_send_sysinfo');
    } else {
        if (fs_time_to_nag()) {
            fs_ajax_get_window($response, 'ask_for_donation');
        } else {
            if (fs_is_admin() && fs_mysql_newer_than("4.1.14") && fs_get_system_option('archive_method') == null && fs_get_num_old_days() > 0) {
                // if the user never selected archive method
                fs_ajax_get_window($response, 'notify_about_archive');
            }
        }
    }
}