<?php
/**
 * Simple table access
 *
 * $Id$
 *
 * @author gERD Schaufelberger <gerd@php-tools.net>
 * @license PHP License
 * @package WB
 * @subpackage db
 */

WBClass::load('WBDatasource_Callback'
            , 'WBLog'
            , 'WBString');

/**
 * Simple table access
 *
 * @version 0.5.0
 * @package WB
 * @subpackage db
 */
class WBDatasource_SQLCommon extends WBStdClass
{
   /**
    * The absulut maximum number for LIMIT
    * As this limit is too big for even big integers, it must be a string!
    * This hack is required to satify MySQL 4.1 :-/
    */
    const MAX_LIMT =    '18446744073709551614';

   /**
    * list of known tables
    * @var array
    */
    protected $tables  =   array();

    /**
     * translate table date
     * @var bool
     */
    private $doTranslation = true;

    /**
     * trigger events
     * @var bool
     */
    private $doTriggerEvent = true;

    /**
     * Prefix for triggered events
     * @var string
     */
    protected $eventPrefix  =   'sqlcommon';

    /**
     * empty constructor for future user
     *
     * @param unknown_type $parameter
     */
    public function __construct( $parameter = array() )
    {
    }

    /**
     * Switch on/off translation feature
     *
     * @param bool $switch
     * @deprecated because of typo :-(
     */
    public function switchTranlsation($switch = false)
    {
        $this->switchTranslation($switch);
    }

    /**
     * Switch on/off translation feature
     *
     * @param bool $switch
     */
    public function switchTranslation($switch = false)
    {
        $this->doTranslation    =   $switch;
    }

    /**
     * Switch on/off event trigger
     *
     * @param bool $switch
     */
    public function switchEventTrigger($switch = false)
    {
        $this->doTriggerEvent   =   $switch;
    }

    /**
     * Translate row from result
     *
     * Check translation-config of table and translate translatable columns
     *
     * @see switchTranlsation()
     * @param string $table
     * @param array $row
     */
    protected function translate($table, &$row)
    {
        if (!$this->doTranslation) {
            return;
        }

        $c  =   $this->getTableInfo($table);
        if (!isset($c['translatable']) || !is_array($c['translatable']) || empty($c['translatable'])) {
            return;
        }
        $domain =   null;
        if (!empty($c['translationdomain'])) {
            $domain =   $c['translationdomain'];
        }
        foreach ($c['translatable'] as $trans) {
            if (empty($row[$trans])) {
                continue;
            }
            $row[$trans]  =   patI18n::dgettext($domain, $row[$trans]);
        }
    }

    /**
     * load table definition
     *
     * Parameter:
     * - "tablelist": config file which contains the list of known tables.
     *
     * This class utilizes @link WBDatasource_SQL this implies that you may
     * use all constructor parameter of this class, as well.
     *
     * @param array $parameter
     */
    public function loadTables( $parameter = array() )
    {
        $conf       =   WBClass::create( 'WBConfig' );
        /** @var WBConfig $conf */

        $conf->load('config');
        $param      =   $conf->get('db');
        if (isset($parameter['tablelist'])) {
            $param['tablelist'] =   $parameter['tablelist'];
        }

        if (is_array($param['tablelist'])) {
            $this->setTables($param['tablelist']);
            return;
        }

        $conf->load($param['tablelist']);
        $this->setTables($conf->get());
    }

   /**
    * configure list of tables
    *
    * @param array $conf list of tables with configuration
    */
    public function setTables( $conf )
    {
        foreach( $conf as $t => $c ) {
            if( isset( $this->tables[$t] ) ) {
                $this->tables[$t]  =   array_merge( $this->tables[$t], $c );
                continue;
            }

            if (!isset($c['primary'])) {
                $c['primary']   =   null;
            }
            if (!isset($c['foreign'])) {
                $c['foreign']   =   array();
            }
            if (!is_array($c['foreign'])) {
                $c['foreign']   =   array($c['foreign']);
            }

            $this->tables[$t]   =   $c;
        }

        return true;
    }

    /**
     * Get list of tables
     *
     * Get list of table names or complete table configuration
     *
     * @param bool $withConfig return list of tables with configuration
     * @return array
     */
    public function getTables($withConfig = false)
    {
        if ($withConfig) {
            return $this->tables;
        }

        return array_keys($this->tables);
    }

   /**
    * Receive name of primary key, if any
    *
    * @param string $table
    * @return string|array|null column name(s) of primary key
    */
    public function getIdentifier($table = null)
    {
        if (!$table) {
            return null;
        }
        $c  =   $this->getTableInfo($table);
        return $c['primary'];
    }

    /**
     * Normalize Primary Key Settings
     *
     * @param array $info
     */
    protected function normalizePrimary(&$info)
    {
        if (empty($info['primary'])) {
            return;
        }
        if (!is_array($info['primary'])) {
            $info['primary']    =   array($info['primary']);
        }
    }

    /**
     * Inform event system
     *
     * Trigger event with prefix. In case event trigger is switched off, do nothing
     *
     * @param string $event,
     * @param string $table,
     * @param string $id
     * @param array $clause
     * @param array $save
     */
    protected function trigger($event, $table, $id = null, $clause = array(), $save = array())
    {
        if (!$this->doTriggerEvent) {
            return;
        }

        $eData  =   array(
                    'table'     =>  $table,
                    'id'        =>  $id,
                    'clause'    =>  $clause,
                    'save'      =>  $save
                );
        WBEvent::trigger($this->eventPrefix . ':' . $event . ':' . $table, 'Saved record in table', $eData);
    }

    /**
     * Add Column Option to Query
     *
     * @param string table name
     * @param array option list
     * @param array query list to build select string
     */
     protected function addColumn($table, $options, &$query)
     {
        $c  =   $this->getTableInfo($table);
        $this->normalizePrimary($c);

        // only select a few columns
        $columns    =   array('*');
        if( isset( $options['column'] ) ) {
            $columns    =   $options['column'];
            if (!is_array($columns)) {
                $columns    =   array($columns);
            }

            if (!empty($c['primary'])) {
                foreach ($c['primary'] as $p) {
                    if (in_array( $c['primary'], $columns)) {
                        continue;
                    }
                    $columns[]  =   $p;
                }
            }
        }

        foreach ($columns as &$col) {
            $col    =   $this->column2SelectString($table, $c, $col);
        }

        $distinct   =   $this->getDistinct($table, $options);
        if (!empty($distinct)) {
            // $columns[]  =   $distinct;
            $query[]    =   'DISTINCT';
        }
        $query[]    =   implode( ', ', $columns );
    }

    /**
     * Column Parameter 2 Select String
     *
     * @see addColumn()
     * @param string table name
     * @param array table config
     * @param array column option
     * @return string
     */
    private function column2SelectString($table, $c, $col)
    {
        if (!is_array($col)) {
            return $c['table'] . '.' . $col;
        }

        if (is_array($col['field'])) {
            $res    =   array();
            $tmp    =   $col;
            foreach ($col['field'] as $f) {
                $tmp['field']   =   $f;
                $res[]  =   $this->column2SelectString($table, $c, $tmp);
            }

            return implode(', ', $res);
        }

        $cc     =   $c;
        if (isset($col['table']) && $col['table'] != $table) {
            $cc =   $this->getTableInfo($col['table']);
        }

        $tmp    =   $cc['table'] . '.' . $col['field'];
        if (isset($col['function'])) {
            $funcFmt    =   '%s(%s)';
            if (!empty($col['format'])) {
                $funcFmt    =   $col['format'];
            }
            $tmp    =   sprintf($funcFmt, $col['function'], $tmp);
        }
        else if (isset($col['native'])) {
            $tmp    =   WBString::populate($col['native'], $c);
        }

        if (isset($col['as'])) {
            $tmp    .=  ' AS ' . $this->db->quote($col['as']);
        }
        return $tmp;
    }

   /**
    * Fetch table config
    *
    * This convenient function allows easy access to table config.
    *
    * @param string $table
    * @return array $config
    * @deprecated in favour of getTableInfo()
    */
    protected function _getTableInfo( $table )
    {
        return $this->getTableInfo($table);
    }

    /**
     * Fetch table config
     *
     * This convenient function allows easy access to table config.
     *
     * @param string $table
     * @return array $config
     */
    public function getTableInfo($table)
    {
        if (!isset($this->tables[$table]['table'])) {
            WBClass::load('WBException_Config');
            throw new WBException_Config('Could not find table "'. $table.'", maybe the config is corrupt.', 4, __CLASS__);
        }
        return $this->tables[$table];
    }

    /**
     * Get list of all tables related to given one
     *
     * Collect all tables that have the table in their foreign list
     *
     * @param string $table
     * @return array
     */
    public function getRelationTables($table)
    {
        if (!isset($this->tables[$table]['table'])) {
            WBClass::load('WBException_Config');
            throw new WBException_Config('Could not find table "'. $table.'", maybe the config is corrupt.', 5, __CLASS__);
        }

        $rel    =   array();
        foreach ($this->tables as $t => $info) {
            if (!isset($info['foreign']) || !is_array($info['foreign']) || empty($info['foreign'])) {
                continue;
            }
            if (!in_array($table, $info['foreign'])) {
                continue;
            }

            $rel[]  =   $t;
        }

        return $rel;
    }

    /**
     * Support for simple joins
     *
     *
     * @param string $table
     * @param array $options
     * @param array $query append join string to this query
     * @return bool always true
     */
    protected function addJoin($table, $options, &$query)
    {
        if (!isset($options['join']) || !is_array($options['join'])) {
            return true;
        }

        foreach ($options['join'] as $join) {

            $other      =   $this->getTableInfo($join['table']);
            $left       =   '';
            if (isset($join['left']) && !empty($join['left'])) {
                $left   =   'LEFT ';
                if ('right' == $join['left']) {
                    $left   =   'RIGHT ';
                }
            }

            if (isset($join['outer']) && !empty($join['outer'])) {
                $left   .=   'OUTER ';
            }

            if (isset($join['using'])) {
                if ('__auto' == $join['using']) {
                    $join['using']  =   $this->getIdentifier($table);
                }
                $query[]    =   sprintf('%sJOIN %s USING (%s)', $left, $other['table'], $join['using']);
            }
            else if (isset($join['onleft'])) {
                $info       =   $this->getTableInfo($table);
                $tLeft       =   $info;
                if (isset($join['lefttable']) && !empty($join['lefttable'])) {
                    $tLeft   =   $this->getTableInfo($join['lefttable']);
                }
                $query[]    =   sprintf('%sJOIN %s ON %s.%s=%s.%s', $left, $other['table'], $tLeft['table'], $join['onleft'], $other['table'], $join['onright']);
            }
            else {
                $alias      =   '';
                if (!empty($join['alias'])) {
                    $alias  =   ' ' . $join['alias'];
                }

                $query[]    =   sprintf('%sJOIN %s%s', $left, $other['table'], $alias);
            }
        }

        return true;
    }

    /**
     * Simple support for grouping resust sets
     *
     *
     * @param string $table
     * @param array $options
     * @param array $query append order string to this query
     * @return bool always true
     */
    protected function addGroupBy($table, $options, &$query)
    {
        if (!isset($options['groupby']) || empty($options['groupby']) || !is_array($options['groupby'])) {
            return true;
        }

        if (!isset($options['groupby'][0])) {
            $options['groupby'] =   array($options['groupby']);
        }
        $c  =   $this->getTableInfo($table);

        $string =   array();
        foreach ($options['groupby'] as $groupby) {
            $gTable   =   $c['table'];
            if (isset($groupby['table'])) {
                if (empty($groupby['table'])) {
                    $gTable =   '';
                }
                else {
                    $oc     =   $this->getTableInfo($groupby['table']);
                    $gTable =   $oc['table'];
                }
            }
            $having  =   '';
            if (isset($groupby['having'])) {
                $having =   'HAVING ' . $groupby['having'];
            }

            if ('__auto' == $groupby['field']) {
                $groupby['field']   =   $this->getIdentifier($table);
            }

            // allow to group by non column fields
            if (empty($gTable)) {
                $string[]   =   sprintf('%s %s', $groupby['field'], $having);
            }
            else {
                $string[]   =   sprintf('%s.%s %s', $gTable, $groupby['field'], $having);
            }
        }

        $query[]    =   'GROUP BY ' . implode(', ', $string);
        return true;
    }

   /**
    * create ORDER BY string
    *
    * Use options as well as table config to create ORDER BY string
    *
    * Order by just one column
    * <code>
    * $options['order'] =  array( 'field' => 'name' );
    * </code>
    * ...or by more colums
    * <code>
    * $options['order'] = array(
    *                       array( 'field' => 'name' ),
    *                       array( 'field' => 'email' ),
    *                       array( 'table' => 'othertable', 'field' => 'orderpos' ),
    *                       array( 'field' => 'created', 'asc' => false )
    *                       array( 'field' => 'name', 'asc' => true, 'function' => 'soundex' )
    * );
    * </code>
    *
    * @param string $table
    * @param array $options
    * @param array $query append order string to this query
    * @return bool always true
    */
    protected function _addOrderBy( $table, $options, &$query )
    {
        $c  =   $this->getTableInfo( $table );

        // get order options
        $order  =   array();
        if( isset( $options['order'] ) && !empty( $options['order'] ) ) {
            $order  = $options['order'];
        } else if( isset( $c['order'] ) ) {
            $order  = $c['order'];
        }

        // nothing to do
        if( empty( $order ) ) {
            return true;
        }

        // force list of order arrays
        if( !isset( $order[0] ) )  {
            $order  =   array( $order );
        }

        // build order string
        $string    = array();
        foreach( $order as $o ) {
            if( !isset( $o['field'] ) || empty( $o['field'] ) ) {
                continue;
            }

            $tmp    =   $c['table'] . '.' . $o['field'];
            if (isset($o['table']) && !empty($o['table'])) {
                $tmp    =   $this->tables[$o['table']]['table'] . '.' . $o['field'];
            }

            if (isset($o['function']) && !empty($o['function'])) {
                $tmp    =   $o['function'] . '(' . $tmp . ')';
            }

            if (isset($o['native']) && !empty($o['native'])) {
                $tmp    =   $o['native'];
            }

            $dir    = 'ASC';
            if( isset( $o['asc'] ) && !$o['asc'] ) {
                $dir    = 'DESC';
            }
            $tmp        .=  ' ' . $dir;
            $string[]   =   $tmp;

        }

        $query[] =  'ORDER BY ' . implode( ', ', $string );
        return true;
    }

   /**
    * create LIMIT string
    *
    * Use options as well as table config to create LIMIT string
    *
    * @param string $table
    * @param array $options
    * @param array $query append limit string to this query
    * @return bool always true
    */
    protected function _addLimit( $table, $options, &$query )
    {
        $c      =   $this->getTableInfo( $table );
        $limit  =   self::MAX_LIMT;

        if( isset( $options['limit'] ) ) {
            if( $options['limit'] > 0 ) {
                $limit  =   $options['limit'];
            }
        }
        else if( isset( $c['limit'] ) ) {
            $limit  =   $c['limit'];
        }

        $offset  =   null;
        if( isset( $options['offset'] ) ) {
            $offset = null;
            if( $options['offset'] > 0 ) {
                $offset  =   $options['offset'];
            }
        }
        else if( isset( $c['offset'] ) ) {
            $offset  =  $c['offset'];
        }

        // really add limit?
        if( $limit == self::MAX_LIMT && $offset === null ) {
            return true;
        }

        if( $offset === null ) {
            $offset = 0;
        }
        $query[]    =   'LIMIT ' . $offset . ',' . $limit;
        return true;
    }

   /**
    * Append foreign key relation to clause
    *
    * Helper function used for many selects
    *
    * @param string $table
    * @param string|null $foreign
    * @param array $clause
    * @return bool always true
    */
    protected function _addForeignKey2Clause($table, $foreign, &$clause)
    {
        if ($foreign === null) {
            return true;
        }

        $f  =   $this->tables[$table]['foreign'];
        if (empty($f)) {
            // there is no proper foreign table defined
            return true;
        }

        // add first foreign key to clause
        $fKey       =   $this->tables[$f[0]]['primary'];
        $clause[]   =   array(
                        'field' => $fKey,
                        'value' => $foreign
                    );

        return true;
    }

   /**
    * Insert delete flag to clause
    *
    * Looks for "deleted" flag in config and automatically adds ist to clause.
    * Use options to switch off this feature
    *
    * @param string $table
    * @param array $clause
    * @param array $options
    * @return bool always true
    */
    protected function _addDeletedFlag2Clause($table, &$clause, $options)
    {
        // see whether check for deleted flag
        if (isset($options['deleted']) && 0 < $options['deleted']) {
            return true;
        }
    	$info	=	$this->getTableInfo($table);
        if (!isset($info['delete']) || empty($info['delete'])) {
            return true;
        }

        $clause[]	=	array(
        				'field'	=>	trim($info['delete']),
        				'value'	=>	'0'
        				);

        return true;
    }

   /**
    * Helper function translates clause-array to where array
    *
    * @see clause2Where()
    * @param string $table named table as configured - which is just the default value
    * @param array $clause list of criterias
    * @param array $where list of where clause fragments
    * @return bool true on success, false otherwise
    */
    protected function _clause2Where($table, $clause, &$where)
    {
        return $this->clause2Where($table, $clause, $where);
    }

   /**
    * Helper function translates clause-array to where array
    *
    *
    * Available keys of accotiative array:
    * - "field" tells which field the criteria affects
    * - "table" need to be specified in case you want to use a field of another table
    * - "value" the value for comparison
    * - "type" either "simple" (default), "native" or "complex".
    * - "bond" defines how to join complex clauses, this is either "and" or "or" (default)
    * - "relation" allows you to something different than "="
    * - "valuetype" can be "function", "foreign" or "default"
    *
    * @param string $table named table as configured - which is just the default value
    * @param array $clause list of criterias
    * @param array $where list of where clause fragments
    * @return bool true on success, false otherwise
    */
    public function clause2Where($table, $clause, &$where)
    {
        if( !is_array( $clause ) || empty( $clause ) ) {
            return true;
        }

        // wrong argument!
        if( !isset( $clause[0] ) ) {
            WBClass::load( 'WBException_Argument' );
            throw new WBException_Argument( 'Clause must be an indexed array!', 6, __CLASS__  );
            return false;
        }

        // manage clause
        for( $i = 0; $i < count( $clause ); ++$i ) {

            if( !isset( $clause[$i]['table'] ) ) {
                $clause[$i]['table'] = $table;
            }

            $type   =   'simple';
            if( isset( $clause[$i]['type'] ) ) {
                $type   =   $clause[$i]['type'];
            }

            $whereClause    =   '';
            switch( strtolower( $type ) ) {
                case    'complex':
                    $myWhere    =   array();
                    $this->clause2Where( $table, $clause[$i]['clause'], $myWhere );
                    $bond   =   'OR';
                    if( isset( $clause[$i]['bond'] ) ) {
                        $bond   =   strtoupper( $clause[$i]['bond'] );
                    }
                    $whereClause    =   '( ' . implode( ' ' . $bond . ' ', $myWhere ) . ' )';
                    break;

                case    'native':
                    if( !isset( $clause[$i]['clause'] ) || empty( $clause[$i]['clause'] ) ) {
                        break;
                    }

                    if( !isset( $clause[$i]['values'] ) || empty( $clause[$i]['values'] ) ) {
                        $whereClause    =   '(' . $clause[$i]['clause'] . ')';
                        break;
                    }

                    $values = array( $clause[$i]['values'] );
                    if( !is_array( $values ) ) {
                        $values =   array( $values );
                    }
                    foreach( $clause[$i]['values'] as $value ) {
                        $values[]   =   $this->db->quote( $value );
                    }
                    $whereClause    =   '(' . vsprintf( $clause[$i]['clause'], $values ) . ')';
                    break;

                case    'simple';
                default:
                    $whereClause    =   $this->getWhereClause( $clause[$i] );
                    break;
            }

            if (!empty($whereClause)) {
                $where[]    =   $whereClause;
            }
        }

        return true;
    }

    protected function _getWhereClause( $clause )
    {
        return $this->getWhereClause($clause);
    }

   /**
    * build where string
    *
    * Supported relations:
    * - "equal" (default)
    * - "begin"
    * - "like"
    * - "in_set"
    * - "in"
    * - "native"
    * - "gt"  greater than
    * - "ge" greater or equal
    * - "not"
    * - "not_in_set"
    * - "le" lower or equal
    * - "lt" lower than
    *
    * @param array $clause
    * @return string partial clause
    * @see clause2Where
    */
    protected function getWhereClause( $clause )
    {
        if( !isset( $clause['valuetype'] ) ) {
            $clause['valuetype']    =   '_default_';
        }

        $quote  =   false;
        // $dbc    =   $this->_connect();

        switch( strtolower( $clause['valuetype'] ) ) {
            case 'function':
                $value  =   $clause['value'];
                break;

            case 'foreign':
                $foreign    =   $this->tables[$clause['foreign']]['table'];
                $value      =   $foreign . '.' . $clause['value'];
                break;

            case 'native':
                $value  =   $clause['value'];
                break;

            default:
                $value  =   $clause['value'];
                $quote  =   true;
                break;
        }

        if( !isset( $clause['relation'] ) ) {
            $clause['relation'] =   '_default_';
        }

        // select relation
        switch( strtolower( $clause['relation'] ) )  {
            case    'native':
                $rel    = '%s ' . $clause['operation'] . ' %s';
                break;

            case    'begin':
            case    'begins':
                $rel    =   "%s LIKE %s";
                $value  =   "'" . $this->db->quote( $value, true ) . "%'";
                $quote  = false;
                break;

            case    'not_begin':
            case    'not_begins':
                $rel    =   "%s NOT LIKE %s";
                $value  =   "'" . $this->db->quote( $value, true ) . "%'";
                $quote  = false;
                break;

            case    'like':
                $rel    =   "%s LIKE %s";
                $value  =   "'%" . $this->db->quote( $value, true ) . "%'";
                $quote  =   false;
                break;

            case    'not_like':
                $rel    =   "%s NOT LIKE %s";
                $value  =   "'%" . $this->db->quote( $value, true ) . "%'";
                $quote  =   false;
                break;

            case 'in_set':
                $rel    =   'FIND_IN_SET( %2$s, %1$s )';
                break;

            case 'not_in_set':
                $rel    =   'NOT FIND_IN_SET( %2$s, %1$s )';
                break;

            case    'not_in':
            case    'in':
                $rel    = '%s IN (%s)';
                if (strtolower($clause['relation']) == 'not_in') {
                    $rel    = '%s NOT IN (%s)';
                }
                if( !is_array( $value ) ) {
                    $value  =   array( $value );
                }
                $tmp    = array();
                foreach( $value as $v ) {
                    $tmp[]  =   $this->db->quote( $v );
                }
                $value  =   implode( ', ', $tmp );
                $quote  =   false;
                break;

            case    'lt':
            case    '<':
                $rel    =   '%s<%s';
                break;

            case    'le':
            case    '<=':
                $rel    =   '%s<=%s';
                break;

            case    'gt':
            case    '>':
                $rel    =   '%s>%s';
                break;

            case    'ge':
            case    '>=':
                $rel    =   '%s>=%s';
                break;

            case    'ne':
            case    '<>':
            case    'not':
                $rel    =   '%s<>%s';
                break;

            case    'null':
                $rel    =   '%s IS %s NULL';
                $quote  =   false;
                if( $value ) {
                    $value = '';
                } else {
                    $value = 'NOT';
                }
                break;

            case    'eq':
            case    'equal':
            default:
                $rel    =   '%s=%s';
                if (isset($clause['cast']) && !empty($clause['cast'])) {
                    $value  =   sprintf('cast(%s AS %s)', $this->db->quote($value), $clause['cast']);
                    $quote  =   false;
                }
                break;
        }

        if( $quote ) {
            $value  =   $this->db->quote( $value );
        }

        return sprintf( $rel, $this->tables[$clause['table']]['table'] . '.' . $clause['field'], $value );
    }

    /**
     * Get Distinct Snippet
     *
     * @param string primary table name
     * @param array option list
     * @return string
     */
    protected function getDistinct($table, $option)
    {
        if (empty($option['distinct'])) {
            return '';
        }

        if (!is_array($option['distinct'])) {
            $option['distinct'] =   array($option['distinct']);
        }

        $distinct   =   array();
        foreach ($option['distinct'] as $d) {
            if (!is_array($d)) {
                $d  =   array('field'   =>   $d);
            }

            if (empty($d['table'])) {
                $d['table'] =   $table;
            }

            $c          =   $this->getTableInfo($d['table']);
            $distinct[] =   sprintf('%s.%s', $c['table'], $d['field']);
        }
        return sprintf('DISTINCT %s', implode(', ', $distinct));
    }
}