<?php
/*
$Id: filter_utility.inc.php 81 2007-08-05 16:08:31Z randomperson83 $
Obsessive Web Statistics
Copyright (C) 2007 Dustin Spicuzza <hide@address.com>
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
/*
Human readable description of each field, along with a convienent default column
description. The items with an _s at the end are the versions intended
to be used with COUNT, SUM, etc
You should avoid directly accessing this array.
Hopefully, in the future this can be easily localized
TODO: This needs to be readdressed to be more compatible with the star schema.
*/
global $db_key_map;
$site_options = get_current_website_options();
$preferred_name = $site_options['preferred_name'];
$db_key_map = array(
'id' => array('ID'),
'id_s' => array('IDs'),
'host' => array('Host Address'),
'host_s' => array('# of hosts'),
'user' => array('User'),
'user_s' => array('Users'),
'date' => array('Date'),
'date_s' => array('Dates'),
'time' => array('Time'),
'time_s' => array('Time'),
'method' => array('Method'),
'method_s' => array('Method'),
'request' => array('Requested URL',array('ww' => 64, 'href' => 'http://' . $preferred_name )),
'request_s' => array('# of Requested URLs'),
'filename' => array('Filename',array('ww' => 64, 'href' => 'http://' . $preferred_name )),
'filename_s' => array('Files'),
'protocol' => array('Protocol'),
'protocol_s' => array('Protocol'),
'status' => array('Status'),
'status_s' => array('Status'),
'bytes' => array('Bytes',array('filesize' => true)),
'bytes_s' => array('Total Bytes',array('filesize' => true)),
'referrer' => array('Referrer URL',array('ww' => 64, 'href' => '')),
'referrer_s' => array('Referrers'),
'agent' => array('User-Agent',array('ww' => 128)),
'agent_s' => array('User-Agents')
);
/*
get_col_name
This returns the descriptive name for that particular column. This
will help for localization help.
For a COUNT/SUM version, add _s to the column
*/
function col_name($column){
global $db_key_map;
if (array_key_exists($column,$db_key_map))
return $db_key_map[$column][0];
return '';
}
/*
* col_description
*
* You pass 'column' as the description, and it returns a description array for use
* with add_col_key
*
* ww -> length to wordwrap at, false means dont wrap
* href -> this is prefixed to the contents of the column,
* and then it is turned into a link
* bold -> if true column is made bold
* filesize -> if true, this interprets the column as a filesize
*/
function col_description($column, $ww = false, $href = false, $bold = false, $filesize = false){
$process = array();
if ($ww !== false && is_numeric($ww))
$process['ww'] = $ww;
if ($href !== false)
$process['href'] = $href;
if ($bold !== false)
$process['bold'] = true;
if ($filesize !== false)
$process['filesize'] = true;
return array($column, $process);
}
/*
add_col_key
$header lets you set $column to a particular row header array to be
used with the show_result_table option. See the description of $header and $process
in filter_output.inc.php at show_result_table.
If header is an array, then it is assumed that it is a column description array, and
it will be used as such. If it is a string, then it will map the current column to
an existing default column description. If it ends with _s, then it is assumed to
be the version to be used with COUNT,SUM,etc.
*/
function add_col_key($column,$header){
global $db_key_map;
// do the description mapping
if ($header != null)
if (is_array($header))
$db_key_map[$column] = $header;
else if (isset($db_key_map[$header]))
$db_key_map[$column] = $db_key_map[$header];
}
/*
The point of this is to be able to create SQL queries and being able to modify them
easily using a standard set of filter functions without having to parse the contents
of said SQL queries. Instead, we just have to add things to this object, then call generate()
and it will return the SQL string.
Of course, you don't really have to use this. Its just so we can have standard things like
bot filtering, filtering by date, etc for every plugin without having to repeat code for it, including
the SQL interpretation thereof.
The other useful purpose of this class with the current star schema is to make it so that you
dont have to manually specify all the needed joins, it can specify them for you. :)
In the future, we can probably implement an optimizer of sorts.
*/
class SQLSelect {
var $dimensions = array();
var $sql_calc_found_rows = false;
var $select_columns = array();
var $from_tables = array();
var $join = array();
var $where = array();
var $group_by = array();
var $having = array();
var $order_by = array();
var $limit = -1;
var $with_rollup = false; // if this is true, we must disregard limit
var $website = "";
var $table = "";
var $curalias = 'a';
function __construct ($website){
$this->website = $website;
$this->table = str_replace('.','_',$website);
}
/*
getUniqueAlias
This returns a unique alias that you can use for a column instead of using the
table name. Starts with a and goes up. Only works 26 times..
TODO: Is this useful?
function getUniqueAlias(){
$alias = $this->curalias;
$this->curalias = chr(ord($this->curalias)+1);
return $alias;
}
/*
This function is used to specify a dimension that is being used. The object
will automatically JOIN the dimension with the fact table.
The function will return the escaped alias or table name for the dimension, so
writing queries like this possible:
$x = $query->DIMENSION('time');
$query->SELECT("$x.hour");
TODO: Specify ON conditions?
*/
function DIMENSION($dimension){
// add to dimensions list
if (!in_array($dimension,$this->dimensions))
$this->dimensions[] = $dimension;
// return identifier (currently long, may introduce aliases in future?)
return db_escape_string($this->table . '_' . $dimension);
}
/*
Gets the name of the current fact table.
*/
function FACT_TABLE(){
return db_escape_string($this->table);
}
/*
Add columns to query.
You should specify the table that is being selected in each query. For selecting
dimensions, it is best to use the DIMENSION function to retrieve the dimension
table identifier (see DIMENSION for more info).
$header is optional, and let you set $column to a particular row header array to be
used with the show_result_table option. See the description of $header and $process
in filter_output.inc.php at show_result_table.
If header is an array, then it is assumed that it is a column description array, and
it will be used as such. If it is a string, then it will map the current column to
an existing default column description. If it ends with _s, then it is assumed to
be the version to be used with COUNT,SUM,etc.
TODO: Evaluate $header for usefulness/flexibility
*/
function SELECT($column, $header = null){
if (!in_array($column,$this->select_columns)){
$this->select_columns[] = $column;
if ($header != null)
add_col_key($column,$header);
}
}
/*
This is no longer needed, I think..
// add tables to query, specified as "FROM $table, $table".. which is really
// a join, come to think of it
function FROM($table){
if (!in_array($table,$this->from_tables))
$this->from_tables[] = $table;
}
*/
/*
Joins... I don't 100% understand their complexities, so this may not
work as expected at first.
If ON is null, then the on phrase is omitted
TODO: Evaluate how these fit in with dimensions
function LEFT_JOIN($table, $on = null, $using = null){
$item = array('LEFT JOIN',$table,$on,$using);
if (!in_array($item,$this->join))
$this->join[] = $item;
}
function LEFT_OUTER_JOIN($table, $on = null, $using = null){
$item = array('LEFT OUTER JOIN',$table,$on,$using);
if (!in_array($item,$this->join))
$this->join[] = $item;
}
*/
/*
This specifies a where clause to use. You MUST specify the tablename for
every column, for example:
$query->WHERE("some_table.id < 5 AND some_table.id > 0");
This where clause is and'ed with every other where clause, so it will put
parentheses around your expression
*/
function WHERE($expr){
if (!in_array($expr,$this->where))
$this->where[] = "($expr)";
}
// add something to do GROUP BY. Be sure to specify a table, of course.
function GROUP_BY($column){
if (!in_array($column,$this->group_by))
$this->group_by[] = $column;
}
// this adds summary fields at the end of groups created with group_by
// This disables any ordering set by ORDER_BY.. however, you can still use
// ordering with GROUP_BY
function WITH_ROLLUP(){
$this->with_rollup = true;
}
// add a having clause.. will cause a failure if there is no group by
// be sure to specify the table name
function HAVING($expr){
if (!in_array($expr,$this->having))
$this->having[] = $expr;
}
// order by something.
function ORDER_BY($column){
if (!in_array($column,$this->order_by))
$this->order_by[] = $column;
}
// limits amount of rows. We assume the first value received is the correct one
function LIMIT($limit){
if ($this->limit == -1)
$this->limit = $limit;
}
/*
generates the query using the variables previously set, and returns a string with the
query, or false on failure.
*/
function generateQuery($load_plugins = true, $show_explain = true, $show_error = true){
$s_fact_table = db_escape_string($this->table);
// load plugins
if ($load_plugins)
if ($this->addCommonLimits() === false)
return false;
// be sure to do basic checks to make sure we generate a valid query..
if (count($this->select_columns) < 1)
return ($show_error ? show_error("generateQuery: Invalid query, no columns added") : false);
$sql = 'SELECT ' . ($this->sql_calc_found_rows ? 'SQL_CALC_FOUND_ROWS ' : '' ) . implode(',',$this->select_columns);
/*
if (count($this->from_tables) < 1)
return ($show_error ? show_error("generateQuery: Invalid query, no tables added") : false);
$sql .= ' FROM ' . implode(',',$this->from_tables);
*/
/*// add join stuff in here
foreach ($this->join as $join)
$sql .= ' ' . $join[0] . ' ' . $join[1] .
($join[2] != null ? " ON $join[2]" : '') .
($join[3] != null ? " USING ($join[3])" : '');
*/
if (count($this->dimensions) < 1)
return ($show_error ? show_error("generateQuery: Invalid query, no dimensions specified") : false);
$sql .= " FROM $s_fact_table";
// dimensions
foreach ($this->dimensions as $dimension){
$dim = db_escape_string($this->table . '_' . $dimension);
$dimen = db_escape_string($dimension . '_id');
$sql .= " INNER JOIN $dim ON $dim.$dimen = $s_fact_table.$dimen";
}
// where is optional
if (count($this->where) > 0)
$sql .= ' WHERE ' . implode(' AND ',$this->where);
if (count($this->group_by) > 0){
$sql .= ' GROUP BY ' . implode(',',$this->group_by);
if ($this->with_rollup){
$this->order_by = array();
$sql .= ' WITH ROLLUP';
}
if (count($this->having) > 0)
$sql .= " HAVING " . implode(',',$this->having);
}else if (count($this->having) > 0)
return ($show_error ? show_error("generateQuery: Invalid query, HAVING clause without GROUP BY") : false);
if (count($this->order_by) > 0)
$sql .= ' ORDER BY ' . implode(',',$this->order_by);
if ($this->limit != -1)
$sql .= " LIMIT $this->limit";
if ($show_explain)
$this->explainSQL($sql);
// useful for troubleshooting really ridiculously long queries
if (get_post_var('only_explain') == 'yes')
return false;
// looks like its done
return $sql;
}
// this calls all plugins and adds their SQL statements to the query
function addCommonLimits(){
if (!load_plugins(''))
return false;
$plugins = get_plugins('limit');
foreach ($plugins as $plugin)
if ($plugin['plugin']->limitResults($this->website,$this) === false)
return false;
return true;
}
// this shows a standard box that runs an EXPLAIN on the SQL statement passed
function explainSQL($sql){
global $cfg;
if (!$cfg['explain_sql'])
return true; // pretend the user saw it
if (!db_is_valid_result($result = db_query("EXPLAIN extended " . $sql)))
return false;
// don't bother the user with the information unless they want it. :)
echo '<div class="sql_explanation"><span class="addsub" onclick="if ($(this).next().css(\'display\') == \'none\'){$(this).text(\'- Hide SQL EXPLAIN Information\').next().fadeIn();} else {$(this).text(\'+ Show SQL EXPLAIN Information\').next().fadeOut();};">+ Show SQL EXPLAIN Information</span><div class="hide">';
if ($cfg['debug'])
echo "<pre>" . htmlentities($sql) . "</pre>";
show_result_table(false, $result);
echo "<pre>SHOW WARNINGS</pre>";
show_result_table(false, db_query("SHOW WARNINGS"));
echo "</div></div>";
return true;
}
}
// use this in conjunction with SQL Select
function show_common_limits(){
echo '<input type="hidden" class="show_common_limits" />';
}
?>