<?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.'/owa_base.php');
require_once(OWA_BASE_CLASS_DIR.'sanitize.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_port, $db_name, $db_user, $db_password, $open_new_connection = true, $persistant = false) {

        $this->connectionParams = array('host' => $db_host,
                                        'port' => $db_port,
                                         'user' => $db_user,
                                         'password' => $db_password,
                                         'name' => $db_name,
                                         'open_new_connection' => $open_new_connection,
                                         'persistant' => $persistant);
                                                                          
        return parent::__construct();
    }

    function __destruct() {

        if ( $this->isConnectionEstablished() ) {

            $this->close();
        }
    }

    function connect() {


        return false;
    }

    function pconnect() {

        return false;
    }

    function close() {

        return false;
    }

    function isConnectionEstablished() {

        return $this->connection_status;
    }

    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 ( ! owa_lib::isEmpty( $value ) ) {

            // hack for intentional empty value
            if($value == ' '){
                $value = '';
            }

            $this->_sqlParams['where'][$name] = array('name' => $name, 'value' => $value, 'operator' => $operator);
        }
    }

    function having($name, $value, $operator = '=') {

        if ( ! owa_lib::isEmpty( $value ) ) {

            // hack for intentional empty value
            if($value == ' ') {
                $value = '';
            }

            $this->_sqlParams['having'][$name] = array('name' => $name, 'value' => $value, 'operator' => $operator);
        }
    }

    function multiWhere($where_array = array()) {

        if (!empty($where_array)):

            foreach ($where_array as $k => $v) {
                if ( ! owa_lib::isEmpty($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() {

        $this->limit(1);
        $ret = $this->_selectQuery();
        return (is_array($ret) && isset($ret[0])) ? $ret[0] : null;
        //return is_null($ret)?null:$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 generateSelectQuerySql() {

        $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 '.$this->prepare( $v['as']);

            endif;

            // Add commas
            if ($i < $count - 1):

                $cols .= ', ';

            endif;

            $i++;

        }

        $sql = sprintf("SELECT %s FROM %s %s %s %s %s %s",
                                        $cols,
                                        $this->_makeFromClause(),
                                        $this->_makeWhereClause(),
                                        $this->_makeGroupByClause(),
                                        $this->_makeHavingClause(),
                                        $this->_makeOrderByClause(),
                                        $this->_makeLimitClause()
                                        );
        $this->_setSql($sql);
        return $sql;
    }

    function _selectQuery() {

        $this->generateSelectQuerySql();
        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 .= $this->prepare( $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');

        if ( ! empty( $params ) ) {

            return $this->_makeConstraintClause('WHERE', $params);
        }
    }

    function _makeHavingClause() {

        $params = $this->_fetchSqlParams('having');

        if ( ! empty( $params ) ) {

            return $this->_makeConstraintClause('HAVING', $params);
        }
    }
    
    /**
     *  Generates the SQL constraint string
     *  @type string    'WHERE' || 'HAVING'
     */
    function _makeConstraintClause( $type, $params ) {
         
        if ( ! empty( $params ) ) {

            $count = count( $params );
            $i = 0;

            $constraint = $type.' ';

            foreach ($params as $k => $v) {
                owa_coreAPI::debug($v);
                switch (strtolower($v['operator'])) {
                
                    case '==':
                        $constraint .= sprintf("%s = '%s'", $this->prepare( $v['name'] ), $this->prepare( $v['value'] ) );
                        break;

                    case 'between':
                        $constraint .= sprintf("%s BETWEEN '%s' AND '%s'", $this->prepare( $v['name'] ), $this->prepare( $v['value']['start'] ), $this->prepare( $v['value']['end'] ) );
                        break;

                    case '=~':
                        $constraint .= sprintf("%s %s '%s'", $this->prepare( $v['name'] ), OWA_SQL_REGEXP, $this->prepare( $v['value'] ) );
                        break;

                    case '!~':
                        $constraint .= sprintf("%s %s '%s'",$this->prepare( $v['name'] ), OWA_SQL_NOTREGEXP, $this->prepare( $v['value'] ) );
                        break;

                    case '=@':
                        $constraint .= sprintf("LOCATE('%s', %s) > 0",$this->prepare( $v['value'] ), $this->prepare( $v['name'] ) );
                        break;

                    case '!@':
                        $constraint .= sprintf("LOCATE('%s', %s) = 0",$this->prepare( $v['value'] ), $this->prepare( $v['name'] ) );
                        break;

                    default:
                        $constraint .= sprintf("%s %s '%s'",$this->prepare( $v['name'] ), $v['operator'], $this->prepare( $v['value'] ) );
                        break;
                }

                if ($i < $count - 1) {

                    $constraint .= " AND ";
                }

                $i++;
            }

            return $constraint;
        }
    }

    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 prepare ( $string ) {

        return owa_sanitize::stripSql( $string );
    }

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

    function count($column_name) {

        return sprintf(OWA_SQL_COUNT, $column_name);
    }

    function sum($column_name) {

        return sprintf(OWA_SQL_SUM, $column_name);
    }

    function distinct($column_name) {

        return sprintf(OWA_SQL_DISTINCT, $column_name);
    }

    function division($numerator, $denominator) {

        return sprintf(OWA_SQL_DIVISION, $numerator, $denominator);
    }

    function round($value) {

        return sprintf(OWA_SQL_ROUND, $value);
    }

    function average($value) {

        return sprintf(OWA_SQL_AVERAGE, $value);
    }

    function getAffectedRows() {

        return false;
    }
}

?>
