Ego_Sql_Abstract.php

gehe zur Dokumentation dieser Datei

<?php
class Ego_Sql_Exception extends Exception
{
    const  QUERY_ERROR = 1; 
    const  CONNECT_ERROR = 2; 
    const  IMPORT_ERROR = 3; 
}

abstract class Ego_Sql_Abstract implements Iterator
{
    protected $escapeChar = "`"; 
    protected $_db; 
    public  $currentQuery; 
    public  $currentBind; 
    public  $Record; 
    protected  $_currentRow; 
    private  $_cacheRecords; 
    private  $_cacheIndex; 
    protected  $_cached = false; 
    protected  $_transaction = false; 
    abstract function connect($database='', $host='', $user='', $password='');

    function __construct($query=array(), $cache=false)
    {
        if (func_num_args() == 4)
        {
            // #206458 - über den Konstruktor andere Zugriffsdaten angeben
            $args = array(
                func_get_arg(0),
                func_get_arg(1),
                func_get_arg(2),
                func_get_arg(3)
            );
            $this->connect($args[0], $args[1], $args[2], $args[3]);

        } else
        {
            $this->connect();
            if ($query)
            {
                $this->select($query, $cache);
            }
        }
    }

    function __toString()
    {
        return get_class($this).'('.$this->currentQuery.')';
    }


    function query($query, $appendCurrent = false)
    {
        if ($appendCurrent) {
            $this->currentQuery .= $query;
        } else {
            $this->currentQuery = $query;
        }
        $this->_currentRow = -1;
        $this->_stmt = $this->_db->query($query);
        if ($this->_stmt === false) {
            $this->rollback();
            throw new Ego_Sql_Exception(implode(' ', $this->_db->errorInfo()), (int) $this->_db->errorCode());
        }
        return $this->_stmt;
    }

    protected function _nextRecord()
    {
        ++$this->_currentRow;
        $this->Record = $this->_stmt?$this->_stmt->fetch(PDO::FETCH_ASSOC):null;
        if ($this->Record===false) {
            $this->Record = null;
        }
        return $this->Record;
    }

    protected function _rewind()
    {
        $this->_currentRow = -1;
        $this->_stmt->closeCursor();
        $this->_stmt->execute();
    }

    function nextRecord()
    {
        set_time_limit(0); // Timeouts in Schleifen vermeiden.
        if ($this->_cached)
        { // Das Abfrageergebnis aus dem Cache laden.
            ++$this->_currentRow;
            ++$this->_cacheIndex;
            if ($this->_cacheIndex<sizeof($this->_cacheRecords))
            {
                return $this->Record = $this->_cacheRecords[$this->_cacheIndex];
            }
            return null;
        }
        return $this->_nextRecord();
    }

    protected function _numRecords()
    {
        return $this->_stmt?$this->_stmt->rowCount():0;
    }

    function numRecords()
    {
        return $this->_cached?sizeof($this->_cacheRecords):$this->_numRecords();
    }

    protected function _selectString($query)
    {
        if ($query['bitand']) {
            if (empty($query['where'])) {
                $query['where'] = '1=1';
            }
            foreach ($query['bitand'] as $bit) {
                $query['where'] .= ' AND '.$bit[0].'&'.$bit[1].'='.$bit[2];
            }
        }
        return 'SELECT '.($query['no_cache']?'SQL_NO_CACHE ':'')
        .($query['distinct']?'DISTINCT ':'')
        .($query['fields']?$query['fields']:'*').($query['fields2']?','.$query['fields2']:'')
        .' FROM ('.($query['table']?$query['table']:$query['from'])
        .($query['table2']?($query['table']?',':'').$query['table2']:'').')'
        .($query['inner']?' INNER JOIN '.(is_array($query['inner'])?
                implode(' INNER JOIN ', $query['inner']):$query['inner']):'')
        .($query['join']?' LEFT JOIN '.(is_array($query['join'])?
                implode(' LEFT JOIN ', $query['join']):$query['join']):'')
        .($query['where']?' WHERE '.$query['where']:'')
        .($query['group']?' GROUP BY '.$query['group']:'')
        .($query['having']?' HAVING '.$query['having']:'')
        .($query['order']?' ORDER BY '.$query['order']:'');
    }

    protected function _select($query)
    {
        if ($query['field_as'])
        {
            foreach ($query['field_as'] as $k=>$v)
            {
                if ($query['field_as_bitand'][$k]) {
                    foreach ($query['field_as_bitand'][$k] as $bit) {
                        $v .= ' AND '.$bit[0].'&'.$bit[1].'='.$bit[2];
                    }
                }
                $query['fields2'] = ($query['fields2']?$query['fields2'].',':'').'('.$v.') AS '.$k;
            }
            unset($query['field_as']);
        }
        if ($query['proposed_order'])
        {
            $query['order'] = 'FIELD('.$query['proposed_order']['field'].','.implode(',', $query['proposed_order']['values']).')';
        }
        if ($query['union'])
        {
            $unions = array();
            foreach ($query['union'] as $union)
            {
                $union = array_merge($query, $union);
                $union['where'] = $query['where']?'('.$query['where'].') AND ('.$union['where'].')':$union['where'];
                $union['join'] = $query['join']?array_merge_recursive($query['join'], $union['join']):$union['join'];
                $unions[] = '('.$this->_selectString($union).')';
            }
            $s = implode(' UNION ', $unions).($query['order']?' order by'.preg_replace('/([, ])[^, .]+?\./', '$1', ' '.$query['order']):'');
        } else {
            $s = $this->_selectString($query);
        }
        if ($query['limit'])
        {
            $limit = explode(',', $query['limit']);
            $s.= ' LIMIT '.$limit[0].(sizeof($limit)>1?','.$limit[1]:'');
        }
        if ($query['bind'])
        { // Prepared Statements
            $this->currentQuery = $s;
            $this->currentBind = $query['bind'];
            $this->_stmt = $this->_db->prepare($s);
            if ($this->_stmt->execute($query['bind']) === false) {
                $this->rollback();
                throw new Ego_Sql_Exception(implode(' ', $this->_stmt->errorInfo()), (int) $this->_stmt->errorCode());
            }
            return $this->_stmt;
        } else {
            return $this->query($s);
        }
    }

    function select($query, $cache=false)
    {
        assert(
            !(bool)$query['limit'] || (bool)$query['order'],
            'You have to set an order to use limit! Otherwise databases like MS SQL don`t work.'.
            print_r($query, true));

        if ($cache && $this->_cacheInit($query, $cache))
        {
            return true;
        }
        $GLOBALS['stats']['db_select']++;
        if ($GLOBALS['egotec_conf']['db']['log_select'])
        { // Datenbank select Abfragen messen.
            $start = microtime(true);
        }
        $return = $this->_select($query);
        if ($GLOBALS['egotec_conf']['db']['log_select'] || $query['log'])
        { // Datenbank select Abfragen aufzeichnen.
            $time = microtime(true)-$start;
            if ($time>$GLOBALS['egotec_conf']['db']['log_select_timeout'] || $query['log'])
            {
                $log = '=== '.date('Y-m-d H:i:s').' '.$_SERVER['REQUEST_URI'].' '.$time."\n".$this->currentQuery."\n".print_r($query, true);
                if ($GLOBALS['egotec_conf']['db']['profiling'] || $query['profile'])
                {
                    $log.= $this->getQueryInfo();
                }
                if ($GLOBALS['egotec_conf']['db']['log_select']==2)
                { // Der Aufzeichnung ein Backtrace hinzufügen.
                    $log.= "\n".get_backtrace();
                }
                Ego_System::log('db_select-'.date('Y-m'), $log."\n"); // Es wird pro Monat eine Logdatei angelegt.
            }
        }
        return $return;
    }

    function delete($query)
    {
        assert($query['table']||$query['from']);
        $s = 'DELETE '
            .($query['fields']?$query['fields'].' ':'')
            .'FROM '
            .($query['table']?$query['table']:$query['from'])
            .($query['inner']?' INNER JOIN '.(is_array($query['inner'])?
                implode(' INNER JOIN ', $query['inner']):$query['inner']):'')
            .($query['join']?' LEFT JOIN '.(is_array($query['join'])?
                implode(' LEFT JOIN ', $query['join']):$query['join']):'')
            .($query['where']?' WHERE '.$query['where']:'');
        if ($query['bind'])
        { // Prepared Statements
            $this->currentQuery = $s;
            $this->currentBind = $query['bind'];
            $this->_stmt = $this->_db->prepare($s);
            if ($this->_stmt->execute($query['bind']) === false) {
                $this->rollback();
                throw new Ego_Sql_Exception(implode(' ', $this->_stmt->errorInfo()), (int) $this->_stmt->errorCode());
            }
            return $this->_stmt;
        } else {
            return $this->query($s);
        }
    }

    function update($query)
    {
        $field = array();
        if (isset($query['key']))
        {
            $set = array_combine($query['key'], $query['val']);
            unset($query['val']);
        } elseif (isset($query['set'])) {
            $query['key'] = array_keys($query['set']);
            $set = $query['set'];
            unset($query['set']);
        }
        if (is_array($query['key'])) {
            foreach ($query['key'] as $k) { // prepared statement vorbereiten
                $field[] = $this->escapeChar . $k . $this->escapeChar . '=:' . $k;
            }
        }
        if (isset($query['set_f']))
        {
            foreach ($query['set_f'] as $k => $v)
            {
                $field[] = $this->escapeChar.$k.$this->escapeChar.'='.$v;
            }
        }
        $s = 'UPDATE '.($query['ignore']?'IGNORE ':'').// Durch ein Update auftretende doppelte Einträge ignorieren.
            $query['table'].
            ' SET '.implode(',', $field).
            ($query['where']?' WHERE '.$query['where']:'');
        $this->currentQuery = array('query' => $s);
        $this->_stmt = $this->_db->prepare($s);
        $this->currentQuery['set'] = $set;
        if ($query['bind']) {
            $set = array_merge($set, $query['bind']);
        }
        if ($this->_stmt === false) {
            $this->rollback();
            throw new Ego_Sql_Exception(implode(' ', $this->_db->errorInfo()), (int) $this->_db->errorCode());
        }
        if ($this->_stmt->execute($set) === false) {
            $this->rollback();
            throw new Ego_Sql_Exception(implode(' ', $this->_stmt->errorInfo()), (int) $this->_stmt->errorCode());
        }
        return $this->_stmt;
    }

    function insert($query)
    {
        if (isset($query['key']))
        {
            $set = array_combine($query['key'], $query['val']);
            unset($query['val']);
        } else {
            $query['key'] = array_keys($query['set']);
            $set = $query['set'];
            unset($query['set']);
        }
        $this->currentQuery = $query;
        $insert_table = $query['table'].'-'.sizeof($query['key']).'-'.$query['replace'];
        if ($this->_insertTable != $insert_table)
        {
            $s = ($query['replace']?'REPLACE':'INSERT')." INTO $query[table]".
                " (".$this->escapeChar.implode($this->escapeChar.','.$this->escapeChar, $query['key']).$this->escapeChar.")".
                " VALUES (:".implode(',:', $query['key']).")";
            $this->_insertstmt = $this->_db->prepare($s);
            $this->_insertTable = $insert_table;
        }
        $this->currentQuery['set'] = $set;
        if ($this->_insertstmt === false) {
            $this->rollback();
            throw new Ego_Sql_Exception(implode(' ', $this->_db->errorInfo()), (int) $this->_db->errorCode());
        }
        if ($this->_insertstmt->execute($set) === false) {
            $this->rollback();
            throw new Ego_Sql_Exception(implode(' ', $this->_insertstmt->errorInfo()), (int) $this->_insertstmt->errorCode());
        }
        return $this->_insertstmt;
    }

    function replace($query)
    {
        assert(!$query['replace'] || $query['primary']);
        return $this->insert(array_merge($query, array('replace' => true)));
    }

    abstract function showColumns($table);

    function current()
    {
        return $this->Record;
    }

    function next()
    {
        return $this->nextRecord();
    }

    function key()
    {
        return $this->_currentRow;
    }

    function valid()
    {
        return isset($this->Record);
    }

    function rewind()
    {
        if ($this->_cached)
        {
            $this->_currentRow = -1;
            $this->_cacheIndex = -1;
        } else {
            $this->_rewind();
        }
        return $this->next();
    }

    function numRows()
    {
        return $this->numRecords();
    }

    protected function _cacheInit($query, $cache)
    {
        if (!isset($GLOBALS['egotec_conf']['no_query_cache']) || $GLOBALS['egotec_conf']['no_query_cache']!=1)
        {
            $query_hash = $query['hash']?$query['hash']:md5(serialize($query));
            $this->_cacheRecords = $cache->get($query_hash);
            if ($this->_cacheRecords === null)
            { // Nicht im Cache, dann von der Datenbank erfragen.
                $this->select($query);
                $this->_cacheRecords = array();
                while ($this->nextRecord())
                {
                    $this->_cacheRecords[] = $this->Record;
                }
                $cache->set($query_hash, $this->_cacheRecords);
            } else {
                $GLOBALS['stats']['db_cache']++;
            }
            $this->_cached = true;
            $this->_cacheIndex = -1;
            $this->_currentRow = -1;
            return true;
        } else {
            return false;
        }
    }

    function export($table)
    {
        $content = array();
        $db = clone $this;
        $db->select(array('table' => $table));
        while ($db->nextRecord())
        {
            $content[] = $db->Record;
        }
        return $content;
    }

    function import($table, $content, $primary=array(), $c_date_fieldname='', $c_date_value='')
    {
        if (!is_array($content))
        {
            throw new Ego_Sql_Exception('Der Parameter $content muss ein array sein!', Ego_Sql_Exception::IMPORT_ERROR);
        }
        if (!$primary)
        {
            $primary = $this->getPrimary($table);
        }

        $db = clone $this;
        $db->begin();
        $db->select(array('table' => $table));
        $old_content = array();
        while ($db->nextRecord())
        {
            $old_content[md5(serialize($db->Record))] = $db->Record;
        }
        $insert = array();
        foreach ($content as $record)
        {
            $md5 = md5(serialize($record));
            if ($old_content[$md5])
            { // Falls ein Eintrag schon vorhanden ist,
                //egotec_error_log("=eq=".serialize($record));
                unset($old_content[$md5]); // muss nichts gemacht werden.
            } else { // Ist ein Eintrag noch nicht vorhanden,
                if (sizeof($primary)==1)
                {
                    $keys = array_keys($primary);
                    $insert[$record[$keys[0]]] = $record;
                } else {
                    $insert[] = $record;
                }
            }
        }
        foreach ($old_content as $record)
        {
            if (!$c_date_fieldname || $record[$c_date_fieldname]<$c_date_value)
            {
            //egotec_error_log("=rm=".serialize($record));
            $where = array();
            $bind = array();
            foreach ($record as $key=>$val)
            {
                if ($primary)
                {
                    if ($primary[$key])
                    {
                        if ($primary[$key]=='string')
                        {
                            $where[] = $key.'=\''.$val.'\'';
                        } else {
                            $where[] = $key.'='.$val;
                        }
                    }
                } else {
                    if (is_numeric($val))
                    {
                        $where[] = $key.'='.$val;
                    } else {
                        $where[] = $key.'=\''.$val.'\'';
                    }
                }
            }
            $where = implode(' AND ', $where);
            $db->delete(array(
                'table' => $table,
                'where' => $where
            ));
            } else {
                $record[$c_date_fieldname] = date('Y-m-d H:i:s');
                $db->replace(array(
                    'table' => $table,
                    'set'   => $record,
                    'primary'   => $primary
                ));
                if (sizeof($primary)==1)
                {
                    $keys = array_keys($primary);
                    unset($insert[$record[$keys[0]]]);
                }
            }
        }
        foreach ($insert as $record)
        {
            try { // Um duplicate entries zu vermeiden, erfolgen die insertes erst nach den deletes.
                $db->insert(array('set' => $record, 'table' => $table)); // wo wird dieser eingefügt.
            } catch (Exception $e)
            {
                egotec_error_log($e->getMessage());
            }
        }
        $db->commit();
    }

    abstract function createTable($table, $struct, $drop_flag=true);

    abstract function alterTable($table, $struct);

    function dropTable($table)
    {
        $this->query('DROP TABLE '.$table);
    }

    function begin()
    {}

    function commit()
    {}

    function rollback()
    {}
    
    function repair($table)
    {
        ;
    }

    function getQueryInfo()
    {
        ;
    }

    function tableExists($table)
    {
        ;
    }

    function optimize($table)
    {
        ;
    }

    protected function onError($action) {
        $file = $GLOBALS['egotec_conf']['var_dir'].'lib/db.php';
        if (Ego_System::file_exists($file)) {
            require_once($file);
            $func = 'db_'.$action;
            if (function_exists($func)) {
                $func($this);
            }
        }
        egotec_error_log('Connection failed');
        return null;
    }

    public abstract function getPrimary($table);

    public function getTables($like = '')
    {
        if ($like)  {
            $this->query("SHOW TABLES FROM ".$GLOBALS['egotec_conf']['db']['database']." LIKE '$like%'");
        } else {
            $this->query("SHOW TABLES FROM ".$GLOBALS['egotec_conf']['db']['database']);
        }

        $all_table = array();
        $count = 0;

        while($table = $this->nextRecord()) {
            $all_table[$count++] = array_pop($this->Record);
        }
        return $all_table;
    }

    public abstract function getVersion();
}
?>