<?php
/**
 * YANA library
 *
 * @package yana
 * @licence http://creativecommons.org/licenses/by-nc-sa/2.5/
 *
 * @ignore
 */

if (!defined('DB_OK')) {
    /**
     * @ignore
     */
    define('DB_OK', 1);
}
if (!defined('DB_ERROR_UNSUPPORTED')) {
    /**
     * @ignore
     */
    define('DB_ERROR_UNSUPPORTED', 0);
}

/**
 * FileDb_connection
 *
 * Mapper for sql statements to datfile commands.
 * It implements only a required subset of the interface
 * of PEAR-DB as needed by the bufferedDbStream class
 *
 * @access      public
 * @package     yana
 * @subpackage  io
 *
 * @ignore
 */
class FileDb_connection extends SerializeableObject
{

    /**#@+
     * @ignore
     * @access  private
     */

    /** @var Counter       */ var $auto_increment = null;
    /** @var StructureFile */ var $structure      = null;
    /** @var string        */ var $database       = "";
    /** @var string        */ var $table          = "";
    /** @var array         */ var $src            = array();
    /** @var string        */ var $sort           = "";
    /** @var bool          */ var $desc           = false;
    /** @var bool          */ var $auto_commit    = true;
    /** @var string        */ var $BASE_DIR       = 'config/db/';

    /**#@-*/

    /**
     * constructor
     *
     * @param string $database (optional)
     */
    function FileDb_connection(/* string */ $database)
    {
        settype($database, "string");
        global $YANA;

        $database = strtolower(preg_replace('/([\w\d_]+)\.config$/', "\\1", $database));

        if (isset($YANA)) {
            $this->BASE_DIR = $YANA->getVar('DBDIR');
        }

        if (!empty($database)) {
            if (!is_dir($this->BASE_DIR . $database)) {
                mkdir($this->BASE_DIR . $database, 0600);
            }             
            if (is_readable($this->BASE_DIR . $database)) {
                $this->database = $database;
                $this->src[$database] = array();
            }
        }

    }

    /**
     * autoCommit
     *
     * @access public
     * @param boolean $mode (optional) default = false
     * @return int
     */
    function autoCommit($mode=false)
    {
        settype($mode, 'boolean');
        $this->auto_commit = $mode;
        return DB_OK;
    }

    /**
     * rollback
     *
     * @access public
     * @return int
     */
    function rollback()
    {
        $this->src[$this->database][$this->table]->reset();
        return DB_OK;
    }

    /**
     * commit
     *
     * @access public
     * @return boolean
     */
    function commit()
    {
        return $this->_write(true);
    }

    /**
     * getListOf
     *
     * @access public
     * @param string $type valid values for $type  are tables, databases
     * @return array
     */
    function getListOf($type)
    {
        settype($type, 'string');
        switch($type)
        {
            case 'tables':
                return $this->structure->getTables();
            break;
            case 'databases':
                $databases = dirlist($this->BASE_DIR,'*.config');
                for ($i=0;$i<count($databases);$i++)
                {
                    $databases[$i] = preg_replace('/\.config$/i', '', $databases[$i]);
                }
                return $databases;
            break;
            default:
                return DB_ERROR_UNSUPPORTED;
            break;
        }
    }

    /**
     * limitQuery
     *
     * @access public
     * @param string $sqlStmt
     * @param int $offset (optional) default = 0
     * @param int $limit (optional) default = 0
     * @return filedb_result
     */
    function limitQuery($sqlStmt, $offset=0, $limit=0)
    {
        settype($sqlStmt, "string");
        settype($offset,  "integer");
        settype($limit,   "integer");

        $sqlStmt = str_replace("\n", '\n', $sqlStmt);

        if (preg_match('/^select\s1\sfrom\s\"([\w\d_]+)\"\swhere\s(.*?)\s*;?$/si', $sqlStmt, $m)) {
            $from       = $this->_select( @ $m[1]);
            $where      = @ $m[2];

            if ($where === false || $from === false) {
                return new filedb_result(null, 'SQL ERROR: Syntax error.');
            }

            if ($this->_isset($from, $where)) {
                return new filedb_result(array(1));
            } else {
                return new filedb_result(array());
            }

        } else if (preg_match('/^select\s(.*?)\sfrom\s\"([\w\d_]+)\"(\swhere\s(.*?))?(\s*order by\s([\w\d_]+))?(\s*desc)?\s*;?$/si', $sqlStmt, $m)) {

            /* SELECT */
            $what       = trim(strtolower(@ $m[1]));
            $from       = $this->_select( @ $m[2]);
            if (!empty($m[4])) {
                $where  = $this->_where($m[4]);
            } else {
                $where  = array();
            }
            $this->sort = trim(strtoupper(@ $m[6]));
            $this->desc = ! @empty(         $m[7]);

            if ($where === false || $from === false) {
                return new filedb_result(null, 'SQL ERROR: Syntax error.');
            } else {
                return new filedb_result($this->_get($what, $where, $offset, $limit));
            }

        } else if (preg_match('/^insert into\s\"([\w\d_]+)\"\s*\((.*?)\)\s+VALUES\((.*?)\)\s*;?$/si', $sqlStmt, $m)) {

            /* INSERT */
            $from  = $this->_select(@ $m[1]);
            $set   = $this->_set(   @ $m[2], @ $m[3]);
            $set   = $this->_increment($set);

            if ($from === false) {
                return new filedb_result(null, 'SQL ERROR: Syntax error.');
            }

            $smlfile =& $this->src[$this->database][$this->table];

            $primary_id  = strtoupper($this->structure->getPrimaryKey($this->table));
            $primary_key = $set[$primary_id];
            unset($set[$primary_id]);
            foreach ($this->structure->getColumns($this->table) as $column)
            {
                $column = strtoupper($column);
                if ($this->structure->getType($this->table,$column) == 'array') {
                    $set[$column] = getConfigFile(explode('\n',$set[$column]));
                }
            }
            $set = array($primary_id => array($primary_key => $set));
            if (is_array($smlfile->get($primary_id.'.'.$primary_key))) {
                return new filedb_result(null, "SQL ERROR: Cannot insert entry with primary key = $primary_key. Another entry with this key already exists.");
            } else {
                $smlfile->insert($set);
                return $this->_write();
            }
/**
 * Prefered syntax for MSSQL.
 * '/^update\s\"([\w\d_]+)\"\s+\((.*?)\)\s+VALUES\((.*?)\)(\s+where\s(.*))?\s*;?$/Usi'
 *
 * @todo check if MSSQL allows standards compliant SQL92 syntax too
 */
        } else if (preg_match('/^update\s\"([\w\d_]+)\"\sset\s((?:\s*\w+\s*\=\s*".*?"\s*,?)+)(?:\s+where\s(.*))?\s*;?$/si', $sqlStmt, $m)) {

            /* UPDATE */
            $from  = $this->_select(   @$m[1]);
            $set   = $this->_updateSet(@$m[2]);
            if (!empty($m[3])) {
                $where  = $this->_where($m[3]);
            } else {
                $where  = array();
            }

            if ($where === false || $from === false) {
                return new filedb_result(null, 'SQL ERROR: Syntax error.');
            }

            assert('!isset($primary_key);');
            $primary_key = strtoupper($this->structure->getPrimaryKey($this->table));

            foreach ($this->structure->getColumns($this->table) as $column)
            {
                $column = strtoupper($column);
                if ($this->structure->getType($this->table,$column) == 'array') {
                    if (!empty($set[$column])) {
                        $set[$column] = getConfigFile(explode('\n',$set[$column]));
                    } else {
                        $set[$column] = array();
                    }
                }
            }  

            /* extract new primary key */
            foreach ($where as $rule)
            {
                if (strcasecmp($rule[0], $primary_key) == 0) {
                    $key = strtoupper($rule[1]);
                    break;
                }
            }
            unset($rule);

            $smlfile =& $this->src[$this->database][$this->table];

            /* if primary key is renamed, the old one has to be replaced */
            if (isset($set[$primary_key]) && $key != $set[$primary_key]) {
                $smlfile->remove("$primary_key.$key");
                $temp = $set[$primary_key];
                $set  = array($temp => $set);
                unset($set[$temp][$primary_key]);
                unset($temp);
            } else if (!empty($key)) {
                $set = array($key => $set);
            } else {
                return new filedb_result(null, "SQL ERROR: Syntax error $sqlStmt");
            }
            $set = array($primary_key => $set);

            return $smlfile->insert($set) && $this->_write();

        } else if (preg_match('/^delete\s+from\s\"([\w\d_]+)\"\swhere\s(.*)(\s*order by\s([\w\d_]+))?(\s*desc)?\s*;?$/Usi', $sqlStmt, $m)) {
            /* delete */

            $from       = $this->_select( @ $m[1]);
            if (!empty($m[2])) {
                $where  = $this->_where($m[2]);
            } else {
                $where  = array();
            }
            $this->sort = trim(strtoupper(@ $m[4]));
            $this->desc = ! @empty(         $m[5]);

            if ($where === false || $from === false) {
                return new filedb_result(null, 'SQL ERROR: Syntax error.');
            }

            $smlfile =& $this->src[$this->database][$this->table];
                                  
            $i = $this->_get($this->structure->getPrimaryKey($this->table), $where, 0, 1);

            if (empty($i)) {
                /* error */
                return new filedb_result(null, "SQL-ERROR: unable to delete; entry does not exist");
            } else {
                $i = $i[0][strtoupper($this->structure->getPrimaryKey($this->table))];
                $smlfile->remove($this->structure->getPrimaryKey($this->table).'.'.$i);
                return $this->_write();
            }

        } else {
            /* throw a line if unknown statement encountered */

            global $YANA;

            if (isset($YANA)) {
                $YANA->setVar("__PHP_DBASE_ERROR__", "Ress#id: @74a34b2b616b34f8eb3f5b2fbce7187c");
            }

            return new filedb_result(null, "SQL ERROR: Syntax error $sqlStmt");
        }
    }

    /**
     * query
     *
     * alias of limitQuery() with $offset and $limit params stripped
     *
     * @access public
     * @param string $sqlStmt
     * @return filedb_result
     */
    function query($sqlStmt)
    {
        assert('is_string($sqlStmt);');
        settype($sqlStmt, "string");
        return $this->limitQuery($sqlStmt);
    }

    /**
     * setStructure
     *
     * @access public
     * @param StructureFile &$structure
     */
    function setStructure(&$structure)
    {
        /* this is a simple work-around to use PHP5-like type-checking in PHP4 */
        assert('is_a($structure, "StructureFile");');
        $this->structure =& $structure;
    }

    /**
     * _sort
     *
     * @access private
     * @param array $a
     * @param array $b
     * @return int
     * @ignore
     */
    function _sort($a, $b)
    {
        $result = 0;
        if (!is_array($a) || !is_array($b) || (!isset($a[$this->sort]) && !isset($b[$this->sort]))) {
            $result = 0;
        } else if (!isset($a[$this->sort])) {
            $result = -1;
        } else if (!isset($b[$this->sort])) {
            $result = 1;
        } else if ($a[$this->sort]<$b[$this->sort]) {
            $result = -1;
        } else if ($a[$this->sort]>$b[$this->sort]) {
            $result = 1;
        } else {
            $result = 0;
        }
        
        if ($this->desc) {
            return - $result;
        } else {
            return $result;
        }

    }

    /**
     * _select
     *
     * loads a table and returns table name
     *
     * NOTE: returns boolean(false) on error
     *
     * @access private
     * @param string $select
     * @return string
     * @ignore
     */
    function _select($select)
    {
        assert('is_string($select);');
        settype($select, 'string');
        $select = strtolower(trim($select));

        if (empty($select)) {
            return false;
        } else if (!isset($this->src[$this->database][$select])) {
            $this->table = $select;
            $smlfile = new smlfile($this->BASE_DIR.$this->database.'/'.$this->table.'.sml');
            $smlfile->init();
            if (!$smlfile->exists()) {
                $smlfile->create();
                $smlfile = new smlfile($this->BASE_DIR.$this->database.'/'.$this->table.'.sml');
            }
            assert('is_string($this->database) && !empty($this->database);');
            assert('is_string($this->table) && !empty($this->table);');
            $this->src[$this->database][$this->table] =& $smlfile;
            return $this->table;
        } else {
            $this->table = $select;
            assert('is_string($this->table);');
            return $this->table;
        }
    }

    /**
     * _where
     *
     * NOTE: returns boolean(false) on error
     *
     * @access private
     * @param string $where
     * @return array
     * @ignore
     */
    function _where($where)
    {
        assert('is_string($where);');
        settype($where, "string");

        $where = explode(' AND ', trim($where));
        if (empty($where)) {
            return array();
        } else {
            foreach($where as $i => $val)
            {
                if (!empty($val)) {
                    if (preg_match('/^\s*(\S+)\s?(=|LIKE)\s?\"(.+)\"\s*;?$/', $val, $m)) {
                        $where[$i]    = array();
                        $where[$i][0] = strtoupper($m[1]);
                        $where[$i][1] = toRegExp($m[3]);
                        $where[$i][1] = str_replace('%','.*',$where[$i][1]);
                        $where[$i][1] = str_replace('_','.?',$where[$i][1]);
                        $where[$i][2] = ($m[2] == '=');
                    } else {
                        /* syntax error */
                        return false;
                    }
                }
            }
            return $where;
        }
    }

    /**
     * _updateSet
     *
     * @access private
     * @param string $values
     * @return array
     * @ignore
     */
    function _updateSet($input)
    {
        assert('is_string($input);');
        settype($input, 'string');
        
        $nr_of_hits = preg_match_all('/\s*(\w+)\s*\=\s*"(.*?)"\s*,?/s', $input, $m);

        if ($nr_of_hits === false || $nr_of_hits < 1) {
            trigger_error("SYNTAX ERROR: '".print_r($input, true)."' is not a valid value string in the provided SQL-statement.", E_USER_WARNING);
            return array();
        } else {
            /* array_combine() became available in PHP 5, here's a code to simulate this behaviour for PHP 4 */
            $update = array();
            $keys   =& $m[1];
            $values =& $m[2];
            for ($i=0;$i<count($keys);$i++)
            {
                $update[strtoupper($keys[$i])] = $values[$i];
            }
    
            assert('is_array($update)');
            return $update;
        }
    }

    /**
     * _set
     *
     * @access private
     * @param string $keys
     * @param string $values
     * @return array
     * @ignore
     */
    function _set($keys, $values)
    {
        assert('is_string($keys);');
        assert('is_string($values);');
        settype($keys, "string");
        settype($values, "string");
        
        if (!preg_match("/^\".*?(\",\".*?)*\"$/", $values)) {
            trigger_error("SYNTAX ERROR: '$values' is not a valid value string in the provided SQL-statement.", E_USER_WARNING);
            return array();
        }

        $keys = explode(',',$keys);
        $values = explode('","', substr($values, 1, -1));
        assert('count($keys) == count($values);');
        /* array_combine() became available in PHP 5, here's a code to simulate this behaviour for PHP 4 */
        $insert = array();
        for ($i=0;$i<count($keys);$i++)
        {
            $insert[strtoupper($keys[$i])] = $values[$i];
        }

        assert('is_array($insert)');
        return $insert;
    }

    /**
     * _increment
     *
     * simulate MySQL's auto-increment feature
     *
     * @access private
     * @param array $res
     * @return array
     * @ignore
     */
    function _increment($res)
    {
        settype($res, "array");   
        $primary_key = strtoupper($this->structure->getPrimaryKey($this->table));

        if (is_null($this->auto_increment)) {
         $this->auto_increment = new counter('file_db_auto_increment',YANA_COUNTER_IGNORE_IP);
        }

        /* simulate auto-increment */
        if (empty($res[$primary_key])) {
            $index = $this->auto_increment->count($this->database.'__'.$this->table);
            $res[$primary_key] = $index;
        }

        return $res;
    }
    
    /**
     * _isset
     *
     * simulate select 1 from ... where ...
     *
     * @access private
     * @param string $tableName
     * @param string $where
     * @return boolean
     * @ignore
     */
    function _isset($tableName, $where)
    {
        assert('is_string($tableName);');
        assert('is_string($where);');
        if (preg_match('/^(\S+) = \"(\S+)\"( AND (\S+) IS NOT NULL)?$/', $where, $m)) {
            $key = $m[1].'.'.$m[2];
            if (!empty($m[4])) {
                $key .= '.'.$m[4];
            }
            assert('preg_match("/^[^\s\.]+\.[^\s\.]+(\.[^\s\.]+)?$/", $key);');
            $smlfile =& $this->src[$this->database][$tableName];
            if ($smlfile->exists($key)) {
                return true;
            } else {
                return false;
            }
        } else if (preg_match('/^(\S+) IS NOT NULL?$/', $where, $m)) {
            $result = $this->_get($m[1]);
            return (count($result));
        } else {
            /* syntax error */
            return false;
        }

    }

    /**
     * _get
     *
     * @access private
     * @param string $what
     * @return array
     * @ignore
     */ 
     function _get($what = "*", $where = array(), $offset=0, $limit=0)
    {
        assert('is_string($what);');
        assert('is_array($where);');
        assert('is_int($offset) && $offset >= 0;');
        assert('is_int($limit) && $limit >= 0;');
        settype($what,   "string");
        settype($where,  "array");
        settype($limit,  "integer");
        settype($offset, "integer");

        $result      =  array();
        $smlfile     =& $this->src[$this->database][$this->table];
        $primary_key =  $this->structure->getPrimaryKey($this->table);

        $data = $smlfile->get($primary_key);
        if (is_array($data)) {
            foreach ($data as $i => $current)
            {
                $current[strtoupper($primary_key)] = $i;

                /* where */
                $test = true;
                if (!empty($where)) {
                    foreach ($where as $value)
                    {
                        /* if all required information is provided */
                        if (count($value) == 3) {
                            /* fuzzy match - only supported for data rows (not keys) */
                            if (($value[2] == true && strtoupper($primary_key) != strtoupper($value[0])) && !preg_match('/^'.$value[1].'$/', @$current[strtoupper($value[0])])) {
                                $test = false;
                            /* exact match - used for all keys */
                            } else if (($value[2] == false || strtoupper($primary_key) == strtoupper($value[0])) && !preg_match('/^'.$value[1].'$/i',@$current[strtoupper($value[0])])) {
                                $test = false;
                            } else {
                                /* do not set $test here */
                            }
                        }
                    }
                }

                /* what */
                if ($test === true) {
                    if ($what == '*' || empty($what)) {
                        $result[count($result)] = $current;
                    } else if (isset($current[strtoupper($what)])) {
                        $result[count($result)] = array(strtoupper($what) => $current[strtoupper($what)]);
                    }
                }
            }
        } else {
            /* result is empty */
            return array();
        }

        /* sort */
        if (!empty($this->sort)) {
            usort($result, array($this, '_sort'));
        }

        /* limits and offsets */
        if ($limit>0 || $offset>0) {
            if ($limit<=0) {
                $limit = count($result);
            }
            if ($offset<0) {
                $offset = 0;
            }
            $result = array_slice($result, $offset, $limit);
        }
                 
        return $result;
    }

    /**
     * _write
     *
     * @access private
     * @param boolean $commit (optional) default = false
     * @return filedb_result
     * @ignore
     */ 
    function _write($commit = false)
    {
        $smlfile =& $this->src[$this->database][$this->table];
        if (!$commit && !$this->auto_commit) {
            /* wait for commit */
            return new filedb_result(array());
        } else {
            /* auto commit */
            if (!$smlfile->write()) {
                /* error */
                return new filedb_result(null, 'unable to save changes');
            } else {
                /* success */
                return new filedb_result(array());
            }
        }
    }

}

?>