<?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);
}
?>