Location: PHPKode > projects > Obsessive Website Statistics > ows/include/filter_utility.inc.php
<?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" />';
}

?>
Return current item: Obsessive Website Statistics