Location: PHPKode > projects > Obsessive Website Statistics > ows/include/dimensions.inc.php
<?php
/*
	$Id: dimensions.inc.php 102 2007-08-28 05:59:41Z 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/>.
	
	This is mostly installation/manipulation routines that have to do
	with dimensions.. its messyish
	
*/


/*
	This should query each installation/analysis plugin and determine the dimensions
	they support, and the attributes of each dimension, and return an array describing them.
	The results of this function are cached.
	
	Format of dimension map:
	
	array(
		'dimension1' => array(
			'pnode_is' => string or array of logfile fields the dimension directly implements
			'attribute1' => attribute_defn(...),
			'attribute2' => ... 
		),
		'dimension2' => array(
			... 
		)
	);
	
	Otherwise, it returns false.
*/
function compile_dimensions($cache = true){

	static $dimension_map = -1;
	
	if ($cache && is_array($dimension_map))
		return $dimension_map;
	
	// initialize it
	$dimension_map = array();
	
	// this allows us to build a complete model of how the dimensions should be
	$plugins = get_plugins('analysis');
	
	
	foreach ($plugins as $plugin){
		
		$dimensions = $plugin['plugin']->define_dimensions();
		
		if (!is_array($dimensions))
			return show_plugin_error($plugin,"Invalid return value for define_dimensions()",true);
		
		// add it to the map.. merge recursive didn't work at some point, i forgot why though. 
		// this does pretty much the same thing, cept it has error checking in it. :)
		// array_merge_recursive($dimension_map,$dimensions);
		foreach ($dimensions as $name => $attrs)
			if (!array_key_exists($name,$dimension_map))
				$dimension_map[$name] = $attrs;
			else
				foreach ($attrs as $attr => $x)
					if (!array_key_exists($attr,$dimension_map[$name]))
						$dimension_map[$name] = array_merge($dimension_map[$name], array($attr => $x));
					else
						return show_plugin_error($plugin,"Duplicate dimension key $attr defined!");
					
	}
	
	// validate that each one has a dimension key
	foreach ($dimension_map as $dimension_name => $dimension)
		if (!array_key_exists($dimension_name,$dimension))
			return show_error("Invalid definition for dimension '$dimension_name'!",true);
	

	// thats it.
	return $dimension_map;
}

/*
	dimension_exists
	
	An explicit way of determining whether a particular dimension exists or not.
	This function caches its results by default, set $nocache to true if you
	don't want it using the cache.
	
	$website 		can also be log_table.. 
	$dimension		Name of dimension
	$nocache		Set $nocache to true if you	don't want it using the cache.
	
*/
function dimension_exists($website, $dimension, $cache = true){

	// cache the values
	static $dimension_cache = -1;
	$table = str_replace('.','_',$website);
	
	// return cached value
	if ($cache && is_array($dimension_cache))
		return array_key_exists($table . '_' . $dimension, $dimension_cache);
	
	// create the cache
	$dimension_cache = array();
	
	// cache em all at once :)
	if (db_has_rows($result = db_query("SHOW TABLES LIKE '" . db_escape_string($table . '_') . "%'"))){
	
		while ($row = db_fetch_row($result))
			$dimension_cache[$row[0]] = true;
	}
	
	return array_key_exists($table . '_' . $dimension, $dimension_cache);
}

/*

	Called by the installer to create/modify the dimensions for a website

	This function adds/removes dimensions/atrributes to/from the table. Returns
	a compile_dimensions style array of elements that are new. Or false on error.
	
	If the name of the attribute stays the same, but the type changes, this function
	currently doesn't handle that. Don't do that.
	
*/
function sync_dimensions($website){

	global $cfg;
	$options = get_website_options($website);
	$prefix_d = str_replace('.','_',$website);

	// array to return
	$new_dimensions = array();
	
	echo "Creating dimensions for $website\n";
	
	if (($dimensions = compile_dimensions(false)) === false)
		return false;
	
	// nobody will need this really
	if ($options['lots_of_hits'])
		$pkey_type = "BIGINT UNSIGNED";
	else
		$pkey_type = "INT UNSIGNED";
		
	// select the SQL engine -- probably should give the user a choice
	$sql_engine = ($cfg['db_type'] == 'mysql' ? ' ENGINE=' . $cfg['db_table_type'] . ' DEFAULT CHARSET=latin1' : '');

	// force dimension cache to clear
	dimension_exists($website,'x',false);
		
	foreach ($dimensions as $dimension_name => $attrs){
		
		$table = db_escape_string($prefix_d . "_" . $dimension_name);
		$sql = array();
		
		// does the dimension exist?
		if (!dimension_exists($website, $dimension_name)){
			
			$s_dimension_name = db_escape_string($dimension_name);
			
			// create 'create table' statement
			$statement = "CREATE TABLE $table ( ${s_dimension_name}_id $pkey_type AUTO_INCREMENT PRIMARY KEY NOT NULL, ";
			
			// setup dimension key
			$statement .= "$s_dimension_name " . $attrs[$dimension_name]['type'] .  
				($attrs[$dimension_name]['size'] !== null ? "(" . $attrs[$dimension_name]['size'] . ") " : ' ') .
				($attrs[$dimension_name]['allow_null'] === false ? "NOT NULL " : '');
			
			// get the creation query
			list($sql,$new_attributes) = create_dimension_table($dimension_name,$attrs,$table,array(),$statement,false,false);
			
			$sql[0] .= ") $sql_engine";
			
			// TODO: This should be UNIQUE, but it cant be for certain columns since
			// mysql only checks the key length, and not the whole column for uniqueness
			if (!($cfg['db_type'] == 'mysql' && strtolower($attrs[$dimension_name]['type']) == 'text' && strtolower($cfg['db_table_type']) != 'myisam' && strtolower($cfg['db_table_type']) != 'innodb' && strtolower($cfg['db_table_type']) != 'bdb'))
				$sql[] = "CREATE INDEX i_$s_dimension_name ON $table ($s_dimension_name" . 
						(is_numeric($attrs[$dimension_name]['index']) ? "(" . $attrs[$dimension_name]['index'] . ")" : '') . ')';
			
			// add to list
			$new_dimensions[$dimension_name] = $attrs;
			
		}else{
			// dimension exists, add columns if they dont already exist
			// we assume the dimension key and id do exist	
			if (!db_has_rows($result = db_query("DESCRIBE $table")))
				return show_error("Could not retrieve sane information about preexisting dimension $dimension_name!");

			$existing = array();
			while ($row = db_fetch_row($result))
				$existing[] = $row[0];
				
			$statement = "ALTER TABLE $table";
			
			list($sql,$new_attributes) = create_dimension_table($dimension_name,$attrs,$table,$existing,$statement,true,true);
			
			if (count($new_attributes) > 0)
				$new_dimensions[$dimension_name] = $new_attributes;
			
		}
		
		// execute all the SQL statements now for this dimension
		if (count($sql) > 0){
			foreach ($sql as $s)
				if (!db_is_valid_result(db_query($s)))
					return show_error("Could not create dimension $dimension_name!");
		
			echo "   >>> Processed dimension '$dimension_name': " . count($sql) . " queries\n";
		}
	}

	$sql = array();
	$fact_table = db_escape_string($prefix_d);
	
	// then, create the fact table here
	// TODO: Determine the value of indexing here?
	
	if (!db_has_rows(db_query("SHOW TABLES LIKE '$fact_table'"))){
		
		$statement = "CREATE TABLE $fact_table ( id $pkey_type AUTO_INCREMENT PRIMARY KEY NOT NULL";
		foreach ($dimensions as $dimension_name => $attrs){
			
			$dn = db_escape_string($dimension_name);
		
			$statement .= ', ' . $dn . "_id $pkey_type NOT NULL";
			
			$sql[] = "CREATE INDEX i_" . $dn . "_id ON $fact_table (" . $dn . "_id)";
		}
			
		$statement .= ")" . $sql_engine;
		array_unshift($sql,$statement);
		
	}else{
		$statement = "ALTER TABLE $fact_table";
		$add_statement = false;
		
		if (!db_has_rows($result = db_query("DESCRIBE $fact_table")))
			return show_error("Could not retrieve sane information about fact table!");
				
		$pre_attr = array();
		while ($row = db_fetch_row($result))
			$pre_attr[] = $row[0];
		
		foreach ($dimensions as $name => $attrs){
			
			$dn = db_escape_string($name);
			
			if (!in_array($name . '_id',$pre_attr)){
				$statement .= ($add_statement ? ',' : '') . " ADD COLUMN " . $dn . "_id $pkey_type NOT NULL";
				$sql[] = "CREATE INDEX i_" . $dn . "_id ON $fact_table (" . $dn . "_id)";
				
				$add_statement = true;
			}
		}
		
		if ($add_statement)
			array_unshift($sql,$statement);
	}
	
	// execute all the SQL statements now for the fact table
	if (count($sql) > 0){
		foreach ($sql as $s)
			if (!db_is_valid_result(db_query($s)))
				return show_error("Could not process fact table!");
		
		echo "   >>> Processed fact table: " . count($sql) . " queries\n";
	}
	
	return $new_dimensions;
}

/*
	Private utility function!
	
	Pass this attributes and the beginning of a statement, and it returns an
	array of SQL that creates/alters the table.
	
	$dimension_name	Name of the current dimension
	$attrs			All possible attributes for this dimension
	$table			Table to create indexes on
	$existing		Array of attributes that already exist
	$statement		SQL statement to append to
	$add_columns	Set to true if adding columns, false otherwise
	$first			Set to true if there are no previous columns listed in the statement
	
	Returns list($sql,$new_attributes)
*/
function create_dimension_table($dimension_name, $attrs, $table, $existing, $statement, $add_columns, $first){

	global $cfg;

	$add = $add_columns ? " ADD COLUMN" : '';
	$add_comma = !$first;
	
	$sql = array();
	$new_attributes = array();
	
	// setup attributes
	foreach ($attrs as $attr_name => $attr){
		
		if ($attr_name != $dimension_name && !in_array($attr_name,$existing) && $attr_name != 'pnode_is'){
			
			// its new, record it
			$new_attributes[$attr_name] = $attr;
			
			$s_attr_name = db_escape_string($attr_name);
			
			// for certain table types, you cannot create indexes on text/blobs
			if ($attr['index'] !== false && 
				!($cfg['db_type'] == 'mysql' && strtolower($attr['type']) == 'text' && strtolower($cfg['db_table_type']) != 'myisam' && strtolower($cfg['db_table_type']) != 'innodb' && strtolower($cfg['db_table_type']) != 'bdb'))
				
				
				$sql[] = "CREATE INDEX i_$s_attr_name ON $table ($s_attr_name" . 
					(is_numeric($attr['index']) ? "($attr[index])" : '') . ')';
			
			$statement .= ($add_comma ? ',' : '') . "$add $s_attr_name $attr[type]" . 
				($attr['size'] !== null ? "($attr[size]) " : ' ') . ($attr['allow_null'] === false ? "NOT NULL " : '');
				
			$add_comma = true;
		}
	}
	
	// drop any columns that aren't in the dimension array but already exist
	foreach ($existing as $attr)
		if ($attr != $dimension_name . '_id' && !array_key_exists($attr, $attrs))
			$sql[] = "ALTER TABLE $table DROP COLUMN " . db_escape_string($attr);
	
	if ($add_comma)
		array_unshift($sql,$statement);
		
	return array($sql,$new_attributes);
}

/*
	Uses the pnode_is field to create an SQL query which should return approximately the same
	array as parsing a logfile is. 
*/
function reconstruct_logfile($website, $get_id = false){

	require_once('filter_utility.inc.php');
	
	$prefix_d = str_replace('.','_',$website);

	if (($dimensions = compile_dimensions()) === false)
		return false;
	
	$fields = array();
	
	// use this to generate the query, easier
	$query = new SQLSelect($website);
	
	if ($get_id)
		$query->SELECT($query->FACT_TABLE() . ".id AS 'id'");
	
	foreach ($dimensions as $dname => $attrs){
	
		if (array_key_exists('pnode_is',$attrs) && $attrs['pnode_is'] !== null && !in_array($attrs['pnode_is'],$fields)){
		
			$s_dname = db_escape_string($dname);
			$s_pnode = db_escape_string($attrs['pnode_is']);
			
			$d = $query->DIMENSION($dname);
			$query->SELECT("$d.$s_dname AS '$s_pnode'");	
		}
	}
	
	$query->ORDER_BY($query->FACT_TABLE() . '.id ASC');
	
	return $query->generateQuery(false,false);
}

// does what the name says
function delete_all_dimensions($website){

	echo "Deleting dimensions for $website\n";

	$prefix_d = str_replace('.','_',$website);

	if (($dimensions = compile_dimensions(false)) === false)
		return false;

	// force dimension cache to clear
	dimension_exists($website,'x',false);
		
	// drop em all
	foreach ($dimensions as $dimension_name => $attrs)
		if (dimension_exists($website, $dimension_name))
			if (!db_is_valid_result(db_query("DROP TABLE " . db_escape_string($prefix_d . "_" . $dimension_name))))
				return show_error("Could not delete dimension $dimension_name");
			else
				echo "   >>> Deleted dimension '$dimension_name'\n";
			
	$fact_table = db_escape_string($prefix_d);
	if (db_has_rows(db_query("SHOW TABLES LIKE '$fact_table'")))
		if (!db_is_valid_result(db_query("DROP TABLE $fact_table")))
			return show_error("Could not delete fact table!");
		else
			echo "   >>> Deleted fact table\n";
	
	
	return true;
}

/*
	This returns an attribute array to be used with analysis plugins. Makes it more
	readable, and saves you typing. All values are assumed to be properly escaped.
	
	$type 			SQL Data Type
	$size			SQL Data Size (if not applicable, set 
	$index			Whether this field should be indexed. If it is a number,
					then it specifies a key size to be used
	$allow_null		Sets field to NOT NULL if false
		
	// TODO: Somehow there needs to be a textual description of each field.. :-/
*/
function attribute_defn($type, $size = null, $index = false, $allow_null = false){

	return array('type' => $type,'size' => $size, 'index' => $index, 'allow_null' => $allow_null);
}


?>
Return current item: Obsessive Website Statistics