<?php

//
// Open Web Analytics - An Open Source Web Analytics Framework
//
// Copyright 2006 Peter Adams. All rights reserved.
//
// Licensed under GPL v2.0 http://www.gnu.org/copyleft/gpl.html
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
// $Id$
//

require_once(OWA_BASE_DIR.DIRECTORY_SEPARATOR.'owa_base.php');

/**
 * Database Connection Class
 * 
 * @author      Peter Adams <peter@openwebanalytics.com>
 * @copyright   Copyright &copy; 2006 Peter Adams <peter@openwebanalytics.com>
 * @license     http://www.gnu.org/copyleft/gpl.html GPL v2.0
 * @category    owa
 * @package     owa
 * @version		$Revision$	      
 * @since		owa 1.0.0
 */
class owa_db extends owa_base {
	
	/**
	 * Database Connection
	 *
	 * @var object
	 */
	var $connection;
	
	var $connectionParams;
	
	/**
	 * Number of queries
	 *
	 * @var integer
	 */
	var $num_queries;
	
	/**
	 * Raw result object
	 *
	 * @var object
	 */
	var $new_result;
	
	/**
	 * Rows
	 *
	 * @var array
	 */
	var $result;
	
	/**
	 * Caller Params
	 *
	 * @var array
	 */
	var $params = array();
	
	/**
	 * Status of selecting a databse
	 *
	 * @var boolean
	 */
	var $database_selection;
	
	/**
	 * Status of connection
	 *
	 * @var boolean
	 */
	var $connection_status;
	
	/**
	 * Number of rows in result set
	 *
	 * @var integer
	 */
	var $num_rows;
	
	/**
	 * Number of rows affected by insert/update/delete statements
	 *
	 * @var integer
	 */
	var $rows_affected;
	
	/**
	 * Microtime Start of Query
	 *
	 * @var unknown_type
	 */
	var $_start_time;
	
	/**
	 * Total Elapsed time of query
	 *
	 * @var unknown_type
	 */
	var $_total_time;

	/**
	 * Storage Array for components of sql queries
	 *
	 * @var array
	 */
	var $_sqlParams = array();
	
	/**
	 * Sql Statement
	 *
	 * @var string
	 */
	var $_sql_statement;
	
	/**
	 * Last Sql Statement
	 *
	 * @var string
	 */
	var $_last_sql_statement;
	
	function __construct($db_host, $db_name, $db_user, $db_password, $open_new_connection = true, $persistant = false) {
		
		$this->connectionParams = array('host' => $db_host,
		 								'user' => $db_user,
		 								'password' => $db_password,
		 								'name' => $db_name, 
		 								'open_new_connection' => $open_new_connection,
		 								'persistant' => $persistant); 
		
		return parent::__construct();
	}
	
	function __destruct() {
		
		$this->close();
	}
	
	function connect() {
	
		
		return false;
	}
	
	function pconnect() {
		
		return false;
	}
	
	function close() {
		
		return false;
	}
	
	function getConnectionParam($name) {
		
		if (array_key_exists($name, $this->connectionParams)) {
			return $this->connectionParams[$name];
		}
	}
	
	/**
	 * Prepare string
	 *
	 * @param string $string
	 * @return string
	 */
	function prepare_string($string) {
	
		$chars = array("\t", "\n");
		return str_replace($chars, " ", $string);
	}

	/**
	 * Starts the query microtimer
	 *
	 */
	function _timerStart() {
		
	  $mtime = microtime(); 
      //$mtime = explode(' ', $mtime); 
      //$this->_start_time = $mtime[1].substr(round($mtime[0], 4), 1);
	$this->_start_time = microtime();	
	return;
	}
	
	/**
	 * Ends the query microtimer and populates $this->_total_time
	 *
	 */
	function _timerEnd() {
		
		$mtime = microtime(); 
    	//$mtime = explode(" ", $mtime); 
    	//$endtime = $mtime[1].substr(round($mtime[0], 4), 1); 
		$endtime = microtime();
		//$this->_total_time = bcsub($endtime, $this->_start_time, 4); 
		$this->_total_time = number_format(((substr($endtime,0,9)) + (substr($endtime,-10)) - (substr($this->_start_time,0,9)) - (substr($this->_start_time,-10))),6);
		
		return;
		
	}
	
	function selectColumn($name, $as = '') {
	
		if (is_array($name)) {
			$as = $name[1];
			$name = $name[0];
		}
		
		$this->_sqlParams['select_values'][] = array('name' => $name, 'as' => $as);
		
		return;
	}
	
	function select($name, $as = '') {
		return $this->selectColumn($name, $as = '');
	}
	
	function where($name, $value, $operator = '') {
		
		if (empty($operator)):
			$operator = '=';
		endif;
		
		if (!empty($value)):
		
			// hack for intentional empty value
			if($value == ' '):
				$value = '';
			endif;
			
			$this->_sqlParams['where'][$name] = array('name' => $name, 'value' => $value, 'operator' => $operator);
		endif;
		
		return;
	}
	
	function multiWhere($where_array = array()) {
	
		if (!empty($where_array)):
		
			foreach ($where_array as $k => $v) {
				if (!empty($v)):
				
					if (empty($v['operator'])):
						$v['operator'] = '=';
					endif;
					
					$this->_sqlParams['where'][$k] = array('name' => $k, 'value' => $v['value'], 'operator' => $v['operator']);
				endif;
			}
			
		endif;
	}
	
	function groupBy($col) {
		
		$this->_sqlParams['groupby'][] = $col;
		return;
	}
	
	function orderBy($col, $flag = '') {
		
		$this->_sqlParams['orderby'][] = array($col, $flag);
		return;
	}
	
	function order($flag) {
		
		$this->_sqlParams['order'] = $flag;
		return;
	}
	
	function limit($value) {
		
		$this->_sqlParams['limit'] = $value;
		return;
	}
	
	function offset($value) {
		
		$this->_sqlParams['offset'] = $value;
		return;
	}
	
	function set($name, $value) {
		
		$this->_sqlParams['set_values'][] = array('name' => $name, 'value' => $value);
		return;
	}
		
	function executeQuery() {
		
		switch($this->_sqlParams['query_type']) {
		
			case 'insert':
				
				return $this->_insertQuery();
				
			case 'select':
			
				return $this->_selectQuery();
				
			case 'update':
				
				return $this->_updateQuery();
				
			case 'delete':
			
				return $this->_deleteQuery();
			
			default:
				
				return $this->_query();
		}
	}
	
	function getAllRows() {
		
		 return $this->_selectQuery();
	}
	
	function getOneRow() {
		
		 $ret = $this->_selectQuery();
		 return $ret[0];
	}
	
	function _setSql($sql) {
		$this->_sql_statement = $sql;
	}
	
	function selectFrom($name, $as = '') {
		
		if (is_array($name)) {
			$as = $name[1];
			$name = $name[0];
		}
		
		$this->_sqlParams['query_type'] = 'select';
		$this->_sqlParams['from'][$name] = array('name' => $name, 'as' => $as);
	}
	
	function from($name, $as = '') {
		return $this->selectFrom($name, $as = '');
	}
	
	function insertInto($table) {
		
		$this->_sqlParams['query_type'] = 'insert';
		$this->_sqlParams['table'] = $table;
	}
	
	function deleteFrom($table) {
		
		$this->_sqlParams['query_type'] = 'delete';
		$this->_sqlParams['table'] = $table;
	}
	
	function updateTable($table) {
		
		$this->_sqlParams['query_type'] = 'update';
		$this->_sqlParams['table'] = $table;
	}
	
	function _insertQuery() {
		owa_coreAPI::profile($this, __FUNCTION__, __LINE__);
		$params = $this->_fetchSqlParams('set_values');
		
		$count = count($params);
			
		$i = 0;
			
		$sql_cols = '';
		$sql_values = '';
						
		foreach ($params as $k => $v) {
				
			$sql_cols .= $v['name'];
			$sql_values .= "'".$this->prepare($v['value'])."'";
					
			$i++;
					
			// Add commas
			if ($i < $count):
					
				$sql_cols .= ", ";
				$sql_values .= ", ";
						
			endif;	
		}
		owa_coreAPI::profile($this, __FUNCTION__, __LINE__);
		$this->_setSql(sprintf(OWA_SQL_INSERT_ROW, $this->_sqlParams['table'], $sql_cols, $sql_values));
		owa_coreAPI::profile($this, __FUNCTION__, __LINE__);
		$ret = $this->_query();	
		owa_coreAPI::profile($this, __FUNCTION__, __LINE__);
		return $ret;
	
	}
	
	function _selectQuery() {
	
		$cols = '';
		$i = 0;
		$params = $this->_fetchSqlParams('select_values');
		$count = count($params);
		
		foreach ($params as $k => $v) {
			
			$cols .= $v['name'];
			
			// Add as
			if (!empty($v['as'])):
			
				$cols .= ' as '.$v['as'];
				
			endif;
			
			// Add commas
			if ($i < $count - 1):
				
				$cols .= ', ';
					
			endif;	
			
			$i++;
			
		}
		
		$this->_setSql(sprintf("SELECT %s FROM %s %s %s %s %s", 
										$cols, 
										$this->_makeFromClause(), 
										$this->_makeWhereClause(),
										$this->_makeGroupByClause(),
										$this->_makeOrderByClause(),
										$this->_makeLimitClause()
										));
		return $this->_query();
	
	}

		
	function _updateQuery() {
	
		$params = $this->_fetchSqlParams('set_values');
		
		$count = count($params);
		
		$i = 0;
		
		$sql_cols = '';
		$sql_values = '';
		$set = '';
					
		foreach ($params as $k => $v) {
			
			//$sql_cols = $sql_cols.$key;
			//$sql_values = $sql_values."'".$this->prepare($value)."'";
				
			// Add commas
			if ($i != 0):
				
				$set .= ', ';
					
			endif;	
			
			$set .= $v['name'] .' = \'' . $this->prepare($v['value']) . '\'';
			
			$i++;
		}
		
		$this->_setSql(sprintf(OWA_SQL_UPDATE_ROW, $this->_sqlParams['table'], $set, $this->_makeWhereClause()));
		
		return $this->_query();
		

	
	}
	
	function _deleteQuery() {
	
		$this->_setSql(sprintf(OWA_SQL_DELETE_ROW, $this->_sqlParams['table'], $this->_makeWhereClause()));
		
		return $this->_query();
	}
	
	function rawQuery($sql) {
	
		$this->_setSql($sql);
		
		return $this->_query();
	}
	
	function _fetchSqlParams($sql_params_name) {
	
		if (array_key_exists($sql_params_name, $this->_sqlParams)):
			if (!empty($this->_sqlParams[$sql_params_name])):
				return $this->_sqlParams[$sql_params_name];
			else:
				return false;
			endif;
		else:
			return false;
		endif;
	}
	
	function _makeWhereClause() {
	
		$params = $this->_fetchSqlParams('where');
		//print_r($params);
		if (!empty($params)):
		
			$count = count($params);
			
			$i = 0;
			
			$where = 'WHERE ';
			
			foreach ($params as $k => $v) {
				//print_r($v);	
				switch (strtolower($v['operator'])) {
					
					case '==':
						$where .= sprintf("%s = '%s'",$v['name'], $v['value']);
						break;
					
					case 'between':
					
						$where .= sprintf("%s BETWEEN '%s' AND '%s'", $v['name'], $v['value']['start'], $v['value']['end']);
						break;
						
					case '=~':
						$where .= sprintf("%s %s '%s'",$v['name'], OWA_SQL_REGEXP, $v['value']);
						break;
						
					case '!~':
						$where .= sprintf("%s %s '%s'",$v['name'], OWA_SQL_NOTREGEXP, $v['value']);
						break;
						
					case '=@':
						$where .= sprintf("LOCATE('%s', %s) > 0",$v['value'], $v['name']);
						break;
						
					case '!@':
						$where .= sprintf("LOCATE('%s', %s) = 0",$v['value'], $v['name']);
						break;
							
					default:
						$where .= sprintf("%s %s '%s'",$v['name'], $v['operator'], $v['value']);		
						break;
				}
					
						
					
				if ($i < $count - 1):
						
					$where .= " AND ";
						
				endif;
	
				$i++;	
				
					
			}
			
			return $where;
				
		else:
			
			return;
					
		endif;

	}
	
	function join($type, $table, $as, $foreign_key, $primary_key = '') {
		
		if (!$primary_key) {
		
			if (!$as) {
					$as = $table;
			} 
			
			$primary_key = $as.'.id';
		}
		
		
		
		$this->_sqlParams['joins'][$as] = array('type' => $type, 
											 'table' => $table, 
											 'as' => $as, 
											 'foreign_key' => $foreign_key, 
											 'primary_key' => $primary_key);
		
	}
	
	function _makeJoinClause() {
		
		$params = $this->_fetchSqlParams('joins');
	
		if (!empty($params)):
		
			$join_clause = '';
			
			foreach ($params as $k => $v) {
			
				if (!empty($v['as'])):
					$join_clause .= sprintf(" %s %s AS %s ON %s = %s", $v['type'], 
																 $v['table'], 
																 $v['as'], 
																 $v['foreign_key'], 
																 $v['primary_key']);
				else:
					$join_clause .= sprintf(" %s %s ON %s = %s", $v['type'], 
																 $v['table'], 																														 $v['foreign_key'], 
																 $v['primary_key']);
				endif;
				
					
			
			}
			
			return $join_clause;
		
		else:
			return;
		endif;
		
	}
	
	function _makeFromClause() {
	
		$from = '';
		$i = 0;
		$params = $this->_fetchSqlParams('from');
		
		if(!empty($params)):
		
			$count = count($params);
			
			foreach ($params as $k => $v) {
				
				$from .= $v['name'];
				
				// Add as
				if (!empty($v['as'])):
				
					$from .= ' as '.$v['as'];
					
				endif;
				
				// Add commas
				if ($i < $count - 1):
					
					$from .= ', ';
						
				endif;	
				
				$i++;
				
			}
			
			$from .= $this->_makeJoinClause();
			
			return $from;
		else:
			$this->e->debug("No SQL FROM params set.");
			return false;
		endif;
	
	}
	
	function _makeGroupByClause() {
		
		$params = $this->_fetchSqlParams('groupby');
		
		if (!empty($params)):
			
			return sprintf("GROUP BY %s", $this->_makeDelimitedValueList($params));
			
		else:
			return;	
		endif;

		
	}
	
	function _makeOrderByClause() {
		
		$sorts = $this->_fetchSqlParams('orderby');
		//print_r($sorts);
		if (!empty($sorts)):
		
			$order = $this->_fetchSqlParams('order');
			
			$i = 1;
			$sort_string = '';
			$count = count($sorts);
			foreach ($sorts as $sort) {
			
				// needed for backwards compatability.
				if (!isset($sort[1])) {
					$sort[1] = $order;
				}
				
				$sort_string .= sprintf("%s %s",$sort[0], $sort[1]);
				if ($i < $count) {
					$sort_string .= ', ';	
				}
				
				$i++;
			}
			
			return sprintf("ORDER BY %s", $sort_string);
			
		else:
			return;	
		endif;
		
				
	}
	
	function _makeLimitClause() {
	
		$param = $this->_fetchSqlParams('limit');
		
		if(!empty($param)):
			$limit = sprintf("LIMIT %d", $param);
			
			$offset = $this->_makeOffsetClause();
			
			$ret = $limit . ' ' . $offset;
					
			return $ret;
		else:
			return;
		endif;
		
	}
	
	function _makeOffsetClause() {
		
		$param = $this->_fetchSqlParams('offset');
		
		if(!empty($param)):
			return sprintf("OFFSET %d", $param);
		else:
			return;
		endif;
		
	}
	
	
	/**
	 * Creates a delimited value list from an array or arrays.
	 *
	 */
	function _makeDelimitedValueListArray($values, $delimiter = ', ', $inner_delimiter = ' ') {
		
		$items = '';
		$i = 0;
		$count = count($values);
		
		//print_r($values);
		
		foreach ($values as $k) {
				
			$items .= implode($inner_delimiter, $k);
			
			// Add commas
			if ($i < $count - 1):
				
				$items .= $delimiter;
					
			endif;	
			
			$i++;
			
		}
		
		return $items;
	
	}
	
	function _makeDelimitedValueList($values, $delimiter = ', ') {
		
		$items = '';
		$i = 0;
		$count = count($values);
		
		if (is_array($values)):
		
			foreach ($values as $k) {
				
				$items .= $k;
				
				// Add commas
				if ($i < $count - 1):
					
					$items .= $delimiter;
						
				endif;	
				
				$i++;
				
			}
			
		else:
		
			$items = $values;
		
		endif;
		
		return $items;
		
	}
	
	function _query() {
		
		switch($this->_sqlParams['query_type']) {
		
			case 'insert':
				
				$ret = $this->query($this->_sql_statement);
				break;
			case 'select':
			
				$ret = $this->get_results($this->_sql_statement);
				
				if (array_key_exists('result_format', $this->_sqlParams)):
					$ret = $this->_formatResults($ret);
				endif;
				
				break;
				
			case 'update':
				
				$ret = $this->query($this->_sql_statement);
				break;
			case 'delete':
			
				$ret = $this->query($this->_sql_statement);
				break;
		}
		
		$this->_last_sql_statement = $this->_sql_statement;
		$this->_sql_statement = '';
		$this->_sqlParams = array();
		return $ret;
		
	}

	function removeNs($string, $ns = '') {
		
		if (empty($ns)):
			$ns = $this->config['ns'];
		endif;
		
		$ns_len = strlen($ns);
		return substr($string, $ns_len);
		
	}
	
	function setFormat($value) {
		
		$this->_sqlParams['result_format'] = $value;
		return;
	}
	
	function _formatResults($results) {
		
		switch ($this->_sqlParams['result_format']) {
				
				case "single_array":
					return $results[0];
					break;
				case "single_row":
					return $results[0];
					break;	
				case "inverted_array":
					return owa_lib::deconstruct_assoc($results);
					break;
				default:
					return $results;
					break;
		}	
	
	}
	
		/**
	 * Drops a table
	 *
	 */
	function dropTable($table_name) {
	
		return $this->query(sprintf(OWA_SQL_DROP_TABLE, $table_name));
	
	}
	
	/**
	 * Change table type
	 *
	 */
	function alterTableType($table_name, $engine) {
	
		return $this->query(sprintf(OWA_SQL_ALTER_TABLE_TYPE, $table_name, $engine));
	
	}
	
	
	/**
	 * Rename a table
	 *
	 */
	function renameTable($table_name, $new_table_name) {
		
		return $this->query(sprintf(OWA_SQL_RENAME_TABLE, $table_name, $new_table_name));
	}
	
	/**
	 * Renames column
	 * idempotent
	 */
	function renameColumn($table_name, $old, $new, $defs) {
	
		return $this->query(sprintf(OWA_SQL_RENAME_COLUMN, $table_name, $old, $new, $defs));
	}

	
	/**
	 * Adds new column to table
	 * idempotent
	 */
	function addColumn($table_name, $column_name, $column_definition) {
	
		return $this->query(sprintf(OWA_SQL_ADD_COLUMN, $table_name, $column_name, $column_definition));
	}
	
	/**
	 * Drops a column from a table
	 *
	 */
	function dropColumn($table_name, $column_name) {
	
		return $this->query(sprintf(OWA_SQL_DROP_COLUMN, $table_name, $column_name));

	}
	
	/**
	 * Changes the definition of a column
	 *
	 */
	function modifyColumn($table_name, $column_name, $column_definition) {
	
		return $this->query(sprintf(OWA_SQL_MODIFY_COLUMN, $table_name, $column_name, $column_definition));
	}
	
	/**
	 * Adds index to a column
	 *
	 */
	function addIndex($table_name, $column_name, $index_definition = '') {
	
		return $this->query(sprintf(OWA_SQL_ADD_INDEX, $table_name, $column_name, $index_definition));
	}
	
	/**
	 * Adds index to a column
	 *
	 */
	function dropIndex($table_name, $column_name) {
	
		return $this->query(sprintf(OWA_SQL_DROP_INDEX, $column_name, $table_name));
	}
	
	/**
	 * Creates a new table
	 *
	 */
	function createTable($entity) {
	
		//create column defs
		
		$all_cols = $entity->getColumns();
		
		$columns = '';
	
		$table_defs = '';
		
		$i = 0;
		$count = count($all_cols);
		
		// Control loop
		
		foreach ($all_cols as $k => $v){
			
			// get column definition 
			$columns .= $v.' '.$entity->getColumnDefinition($v);
						
			// Add commas to column statement
			if ($i < $count - 1):
				
				$columns .= ', ';
					
			endif;	
			
			$i++;
				
		}
		
		// make table options
		$table_options = '';
		$options = $entity->getTableOptions();
		
		// table type
		switch ($options['table_type']) {
		
			case "disk":
				$table_type = OWA_DTD_TABLE_TYPE_DISK;
				break;
			case "memory":
				$table_type = OWA_DTD_TABLE_TYPE_MEMORY;
				break;
			default:
				$table_type = OWA_DTD_TABLE_TYPE_DEFAULT;
	
		}
		
		$table_options .= sprintf(OWA_DTD_TABLE_TYPE, $table_type);
		
		// character encoding type
		
		// just in case the propoerties is not i nthe array, add a default value.
		if (!array_key_exists('character_encoding', $options)) {
			
			$options['character_encoding'] = OWA_DTD_CHARACTER_ENCODING_UTF8;			
		}
	
		$table_options .= sprintf(' ' . OWA_DTD_TABLE_CHARACTER_ENCODING, $options['character_encoding']);
	
		return $this->query(sprintf(OWA_SQL_CREATE_TABLE, $entity->getTableName(), $columns, $table_options));
	}
	

	
	/**
	 * Begins a SQL transaction statement
	 *
	 */
	function beginTransaction() {
	
		return $this->query(OWA_SQL_BEGIN_TRANSACTION);
	}
	
	/**
	 * Ends a SQL transaction statement
	 *
	 */
	function endTransaction() {
	
		return $this->query(OWA_SQL_END_TRANSACTION);
	}

}

?>