Ego_Sql_mysqli.php

gehe zur Dokumentation dieser Datei

<?php
require_once('base/Ego_Sql_Abstract.php');

class Ego_Sql_mysqli extends Ego_Sql_Abstract
{
    private  $_dbHandle; 
    private  $_queryHandle; 
    static  $_staticMysqliHandle; 
    function connect($database='', $host='', $user='', $password='')
    {
        if (func_num_args()==0)
        {
            if (Ego_Sql_mysqli::$_staticMysqliHandle)
            { // Nur die EGOTEC Standardverbindung wird global gespeichert.
                $this->_dbHandle = Ego_Sql_mysqli::$_staticMysqliHandle;
                return;
            } else {
                $this->_dbHandle = @mysqli_connect(
                    $GLOBALS['egotec_conf']['db']['host'],
                    $GLOBALS['egotec_conf']['db']['user'],
                    $GLOBALS['egotec_conf']['db']['password'],
                    $GLOBALS['egotec_conf']['db']['database'],
                    $GLOBALS['egotec_conf']['db']['port'],
                    $GLOBALS['egotec_conf']['db']['socket']
                ) or $this->onError('connect');
                Ego_Sql_mysqli::$_staticMysqliHandle = $this->_dbHandle; // Nur die EGOTEC Standardverbindung wird global gespeichert.
            }
        } else {
            $this->_dbHandle = @mysqli_connect($host, $user, $password, $database)
                or $this->onError('connect');
        }
        if (!$this->_dbHandle)
        {
            // Wenn der DB Handle nicht besteht wird eine Exception geworfen.
            throw new Ego_Sql_Exception( 'Konnte keine Datenbankverbindung herstellen.', Ego_Sql_Exception::CONNECT_ERROR );
        }
        $this->query('SET NAMES utf8');
        $this->query('SET collation_connection=\'utf8_bin\'');
        if ($GLOBALS['egotec_conf']['db']['profiling'])
        {
            $this->query('SET PROFILING=1');
        }
    }

    function query($query, $appendCurrent = false)
    {
        if ($appendCurrent) {
            $this->currentQuery .= $query;
        } else {
            $this->currentQuery = $query;
        }
        $this->_queryHandle = mysqli_query($this->_dbHandle, $query);
        if (!$this->_queryHandle) { // Die Abfrage ist fehlgeschlagen.
            throw new Ego_Sql_Exception($query."\n".mysqli_error($this->_dbHandle), Ego_Sql_Exception::QUERY_ERROR);
        }
        $this->_currentRow = -1;
        return $this->_queryHandle;
    }

    protected function _nextRecord()
    {
        ++$this->_currentRow;
        return $this->Record = $this->_queryHandle?mysqli_fetch_assoc($this->_queryHandle):false;
    }

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

    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']);
        }
        // proposed Order #78743
        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 emulieren.
            $GLOBALS['prepare_execute_stm'] = array();
            $q = 'PREPARE STMT FROM "'.preg_replace_callback('#:([a-zA-Z][a-zA-Z0-9_]*)#ims', 'ego_sql_mysqli_prepare_callback', $s).'"';
            if (!empty($GLOBALS['prepare_execute_stm'])){
                if (!mysqli_real_query($this->_dbHandle, $q))
                {
                    throw new Ego_Sql_Exception($s."\n".mysqli_error($this->_dbHandle), Ego_Sql_Exception::QUERY_ERROR );
                }
                foreach ($query['bind'] as $k=>$v)
                {
                    $q = 'SET @'.$k.'=\''.mysqli_real_escape_string($this->_dbHandle, $v).'\'';
                    mysqli_real_query($this->_dbHandle, $q);
                }
                $s = 'EXECUTE STMT USING '.implode(',', $GLOBALS['prepare_execute_stm']);
            }
        }
        return $this->query($s);
    }

    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 emulieren.
            $GLOBALS['prepare_execute_stm'] = array();
            $q = 'PREPARE STMT FROM "'.preg_replace_callback('#:([a-zA-Z][a-zA-Z0-9_]*)#ims', 'ego_sql_mysqli_prepare_callback', $s).'"';
            if (!mysqli_real_query($this->_dbHandle, $q))
            {
                throw new Ego_Sql_Exception($s."\n".mysqli_error($this->_dbHandle), Ego_Sql_Exception::QUERY_ERROR );
            }
            foreach ($query['bind'] as $k=>$v)
            {
                $q = 'SET @'.$k.'=\''.mysqli_real_escape_string($this->_dbHandle, $v).'\'';
                mysqli_real_query($this->_dbHandle, $q);
            }
            $s = 'EXECUTE STMT USING '.implode(',', $GLOBALS['prepare_execute_stm']);
        }
        return $this->query($s);
    }

    private function _getUpdateField($query)
    {
        if (isset($query['key']))
        {
            $query['set'] = array_combine($query['key'], $query['val']);
        }
        $field = array();
        if (isset($query['set']))
        {
            foreach ($query['set'] as $k => $v)
            {
                if ($v === null) {
                    $field[] = '`'.$k.'`=NULL';
                } else {
                    $field[] = '`'.$k.'`=\''.str_replace(array('\\', '\''), array('\\\\', '\'\''), $v).'\'';
                }
            }
        }
        return $field;
    }

    function update($query)
    {
        $field = $this->_getUpdateField($query);
        if (isset($query['set_f']))
        {
            foreach ($query['set_f'] as $k => $v)
            {
                $field[] = '`'.$k.'`='.$v;
            }
        }
        $s = 'UPDATE '.($query['ignore']?'IGNORE ':'').// Durch ein Update auftretende doppelte Einträge ignorieren.
            $query['table'].
            ' SET '.implode(',', $field).
            ($query['where']?' WHERE '.$query['where']:'');
        if ($query['bind'])
        { // Prepared Statements emulieren.
            $GLOBALS['prepare_execute_stm'] = array();
            $q = 'PREPARE STMT FROM "'.preg_replace_callback('#:([a-zA-Z][a-zA-Z0-9_]*)#ims', 'ego_sql_mysqli_prepare_callback', $s).'"';
            if (!mysqli_real_query($this->_dbHandle, $q))
            {
                throw new Ego_Sql_Exception($s."\n".mysqli_error($this->_dbHandle), Ego_Sql_Exception::QUERY_ERROR );
            }
            foreach ($query['bind'] as $k=>$v)
            {
                $q = 'SET @'.$k.'=\''.mysqli_real_escape_string($this->_dbHandle, $v).'\'';
                mysqli_real_query($this->_dbHandle, $q);
            }
            $s = 'EXECUTE STMT USING '.implode(',', $GLOBALS['prepare_execute_stm']);
        }
        return $this->query($s);
    }

    function insert($query)
    {
        return $this->query(($query['replace']?'REPLACE':'INSERT').' INTO '.$query['table'].' SET '.implode(',', $this->_getUpdateField($query)));
    }

    private function _changeType($type)
    {
        switch ($type)
        {
            case 'smalltext':
                $type = 'TEXT';
                break;
            case 'text':
                $type = 'LONGTEXT';
                break;
            case 'tinyint':
            case 'int':
            case 'bigint':
                $type.= ' DEFAULT 0';
                break;
        }
        $type.= ' NOT NULL';
        return $type;
    }

    function createTable($table, $struct, $drop_flag=true, $notexists_flag=false)
    {
        if ($drop_flag)
        {
            $this->query('DROP TABLE IF EXISTS '.$table); // Eine evtl. vorhandene Tabelle entfernen.
        }
        $query = array();
        foreach ($struct as $name => $type)
        {
            $key = explode(' ', $name);
            if (sizeof($key)==1)
            { // Ein Feld erzeugen.
                $query[] = $name.' '.$this->_changeType($type);
            } else {
                switch ($key[0])
                {
                    case 'PRIMARY':
                        $query[] = 'PRIMARY KEY ('.$type.')';
                        break;
                    case 'KEY':
                        $query[] = 'KEY '.$key[1].' ('.$type.')';
                        break;
                    case 'UNIQUE':
                        $query[] = 'UNIQUE KEY '.$key[1].' ('.$type.')';
                        break;
                }
            }
        }
        $query_string = 'CREATE TABLE '.($notexists_flag?'IF NOT EXISTS ':'').$table.' ('.implode(',', $query).
            ') ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AVG_ROW_LENGTH=1048576 CHECKSUM=0 DELAY_KEY_WRITE=1 MIN_ROWS=1000;';
        $query_string = str_replace('/*bin*/', ' character set utf8 collate utf8_bin ', $query_string);
        $this->query($query_string);
    }

    function alterTable($table, $struct)
    {
        $query = array();
        foreach ($struct as $name => $type)
        {
            $key = explode(' ', $name);
            if (sizeof($key)==1)
            { // Ein Feld erzeugen.
                $query[] = 'ADD '.$name.' '.$this->_changeType($type);
            } else {
                switch ($key[0])
                {
                    case 'PRIMARY':
                        $query[] = 'ADD PRIMARY KEY ('.$type.')';
                        break;
                    case 'KEY':
                        $query[] = 'ADD KEY '.$key[1].' ('.$type.')';
                        break;
                    case 'UNIQUE':
                        $query[] = 'ADD UNIQUE KEY '.$key[1].' ('.$type.')';
                        break;
                    case 'FULLTEXT':
                        $query[] = 'ADD FULLTEXT '.$key[1].' ('.$type.')';
                        break;
                    case 'CHANGE':
                        $query[] = 'CHANGE '.$key[1].' '.$key[2].' '.$type;
                        break;
                }
            }
        }
        return $this->query('ALTER TABLE '.$table.' '.implode(',', $query));
    }

    function repair($table)
    {
        require_once('base/Ego_System.php');
        Ego_System::eternalCache(true);
        $this->query('repair table '.$table);
        Ego_System::eternalCache(false);
    }

    function getQueryInfo()
    {
        $result_handle = mysqli_query($this->_dbHandle, 'SHOW PROFILE CPU');
        $result = '';
        while ($r = mysqli_fetch_assoc($result_handle))
        {
            $result.= str_pad($r['Status'], 40)."\t".str_pad($r['Duration'], 8, '0')."\t".$r['CPU_user']."\t".$r['CPU_system']."\n";
        }
        return $result;
    }

    public function showColumns($table)
    {
        if (!$table) return array();
        $hdl = mysqli_query($this->_dbHandle, "SHOW COLUMNS FROM ".$table);
        $cols = array();
        while($record = mysqli_fetch_assoc($hdl))
        {
            $cols[] = array(
                'field' => $record['Field'],
                'type' => $record['Type']
            );
        }
        return $cols;
    }

    public function getVersion()
    {
        return mysqli_get_server_info($this->_dbHandle);
    }

    public function tableExists($table)
    {
        if (!$table)
        {
            return false;
        }
        $this->query("SHOW TABLES FROM `".$GLOBALS['egotec_conf']['db']['database']."` LIKE '$table'");

        return (bool)$this->nextRecord();
    }

    function getPrimary($table)
    {
        $db = clone $this;
        $db->select(array(
            'fields'    => 'cols.column_name,cols2.data_type',
            'table' => 'information_schema.table_constraints cons,information_schema.key_column_usage cols,information_schema.columns cols2',
            'where' => 'cols.table_name=\''.$table.'\' '.
                'AND cols.table_schema=\''.$GLOBALS['egotec_conf']['db']['database'].'\''.
                'AND cons.constraint_type=\'PRIMARY KEY\' '.
                'AND cols.constraint_name=cons.constraint_name AND cols.table_schema=cons.table_schema AND cols.table_name=cons.table_name '.
                'AND cols2.table_schema=cons.table_schema AND cols2.table_name=cons.table_name AND cols2.column_name=cols.column_name'
        ));
        $primary = array();
        while ($db->nextRecord())
        {
            if (strpos($db->Record['data_type'], 'int')!==false)
            {
                $type = 'int';
            } else {
                $type = 'string';
            }
            $primary[$db->Record['column_name']] = $type;
        }
        return $primary;
    }

    public function optimize($table)
    {
        require_once('base/Ego_System.php');
        Ego_System::eternalCache(true);
        $this->query("OPTIMIZE TABLE $table");
        Ego_System::eternalCache(false);
    }

    protected function _rewind()
    {
        $this->_currentRow = -1;
        mysqli_data_seek($this->_queryHandle, 0);
    }
}

function ego_sql_mysqli_prepare_callback($matches)
{
    $GLOBALS['prepare_execute_stm'][] = '@'.$matches[1];
    return '?';
}
?>