class.ilDBMySQL.php

Go to the documentation of this file.

<?php

/* Copyright (c) 1998-2012 ILIAS open source, Extended GPL, see docs/LICENSE */

include_once("./Services/Database/classes/MDB2/class.ilDB.php");

class ilDBMySQL extends ilDB
{
        var $max_allowed_packet_size;
        protected $slave_active = false;
        protected $use_slave = false;

        function supportsSlave()
        {
                return true;
        }

        function setDBSlaveActive($a_val)
        {
                $this->slave_active = $a_val;
        }
        
        function getDBSlaveActive()
        {
                return $this->slave_active;
        }
        
        function setDBSlaveUser($a_user)
        {
                $this->slave_user = $a_user;
        }
        
        function getDBSlaveUser()
        {
                return $this->slave_user;
        }

        function setDBSlavePort($a_port)
        {
                $this->slave_port = $a_port;
        }
        
        function getDBSlavePort()
        {
                return $this->slave_port;
        }

        function setDBSlaveHost($a_host)
        {
                $this->slave_host = $a_host;
        }
        
        function getDBSlaveHost()
        {
                return $this->slave_host;
        }

        function setDBSlavePassword($a_password)
        {
                $this->slave_password = $a_password;
        }
        
        function getDBSlavePassword()
        {
                return $this->slave_password;
        }

        function setDBSlaveName($a_name)
        {
                $this->slave_name = $a_name;
        }
        
        function getDBSlaveName()
        {
                return $this->slave_name;
        }

        function getDSN()
        {
                return $this->__buildDSN($this->getDBHost(), $this->getDBName(),
                        $this->getDBUser(), $this->getDBPassword(), $this->getDBPort());
        }

        function getSlaveDSN()
        {
                return $this->__buildDSN($this->getDBSlaveHost(), $this->getDBSlaveName(),
                        $this->getDBSlaveUser(), $this->getDBSlavePassword(), $this->getDBSlavePort());
        }

        protected function __buildDSN($a_host, $a_name, $a_user, $a_pass, $a_port = "")
        {
                $db_port_str = "";
                if (trim($a_port) != "")
                {
                        $db_port_str = ":".$a_port;
                }
                
                $driver = $this->isMySQLi() ? "mysqli" : "mysql";      
                
                return $driver."://".$a_user.":".$a_pass.
                        "@".$a_host.$db_port_str."/".$a_name;
        }
        
        protected function isMySQLi()
        {
                return ($this->getSubType() == "mysqli");
        }

        function getHostDSN()
        {               
                $driver = $this->isMySQLi() ? "mysqli" : "mysql"; 
                
                return $driver."://".$this->getDBUser().":".$this->getDBPassword().
                        "@".$this->getdbHost();
        }

        function getDBType()
        {
                return "mysql";
        }
        
        function setStorageEngine($a_storage_engine) {
                $storage_engine_var = ($this->isMysql5_6OrHigher()) ? "DEFAULT_STORAGE_ENGINE" : "STORAGE_ENGINE";
                $this->query("SET SESSION " . $storage_engine_var . " = '" . $a_storage_engine . "'");
        }
        
        static function getReservedWords()
        {
                // version: 5.1
                // url: http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
                return array(
                "ACCESSIBLE", "ADD", "ALL", "ALTER", "ANALYZE", "AND",
                "AS", "ASC", "ASENSITIVE", "BEFORE", "BETWEEN", "BIGINT",
                "BINARY", "BLOB", "BOTH", "BY", "CALL", "CASCADE",
                "CASE", "CHANGE", "CHAR", "CHARACTER", "CHECK", "COLLATE",
                "COLUMN", "CONDITION", "CONSTRAINT", "CONTINUE", "CONVERT", "CREATE",
                "CROSS", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR",
                "DATABASE", "DATABASES", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE", "DAY_SECOND",
                "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DELAYED", "DELETE",
                "DESC", "DESCRIBE", "DETERMINISTIC", "DISTINCT", "DISTINCTROW", "DIV",
                "DOUBLE", "DROP", "DUAL", "EACH", "ELSE", "ELSEIF",
                "ENCLOSED", "ESCAPED", "EXISTS", "EXIT", "EXPLAIN", "FALSE",
                "FETCH", "FLOAT", "FLOAT4", "FLOAT8", "FOR", "FORCE",
                "FOREIGN", "FROM", "FULLTEXT", "GRANT", "GROUP", "HAVING",
                "HIGH_PRIORITY", "HOUR_MICROSECOND", "HOUR_MINUTE", "HOUR_SECOND", "IF", "IGNORE",
                "IN", "INDEX", "INFILE", "INNER", "INOUT", "INSENSITIVE",
                "INSERT", "INT", "INT1", "INT2", "INT3", "INT4",
                "INT8", "INTEGER", "INTERVAL", "INTO", "IS", "ITERATE",
                "JOIN", "KEY", "KEYS", "KILL", "LEADING", "LEAVE",
                "LEFT", "LIKE", "LIMIT", "LINEAR", "LINES", "LOAD",
                "LOCALTIME", "LOCALTIMESTAMP", "LOCK", "LONG", "LONGBLOB", "LONGTEXT",
                "LOOP", "LOW_PRIORITY", "MASTER_SSL_VERIFY_SERVER_CERT", "MATCH", "MEDIUMBLOB", "MEDIUMINT",
                "MEDIUMTEXT", "MIDDLEINT", "MINUTE_MICROSECOND", "MINUTE_SECOND", "MOD", "MODIFIES",
                "NATURAL", "NOT", "NO_WRITE_TO_BINLOG", "NULL", "NUMERIC", "ON",
                "OPTIMIZE", "OPTION", "OPTIONALLY", "OR", "ORDER", "OUT",
                "OUTER", "OUTFILE", "PRECISION", "PRIMARY", "PROCEDURE", "PURGE",
                "RANGE", "READ", "READS", "READ_WRITE", "REAL", "REFERENCES",
                "REGEXP", "RELEASE", "RENAME", "REPEAT", "REPLACE", "REQUIRE",
                "RESTRICT", "RETURN", "REVOKE", "RIGHT", "RLIKE", "SCHEMA",
                "SCHEMAS", "SECOND_MICROSECOND", "SELECT", "SENSITIVE", "SEPARATOR", "SET",
                "SHOW", "SMALLINT", "SPATIAL", "SPECIFIC", "SQL", "SQLEXCEPTION",
                "SQLSTATE", "SQLWARNING", "SQL_BIG_RESULT", "SQL_CALC_FOUND_ROWS", "SQL_SMALL_RESULT", "SSL",
                "STARTING", "STRAIGHT_JOIN", "TABLE", "TERMINATED", "THEN", "TINYBLOB",
                "TINYINT", "TINYTEXT", "TO", "TRAILING", "TRIGGER", "TRUE",
                "UNDO", "UNION", "UNIQUE", "UNLOCK", "UNSIGNED", "UPDATE",
                "USAGE", "USE", "USING", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP",
                "VALUES", "VARBINARY", "VARCHAR", "VARCHARACTER", "VARYING", "WHEN",
                "WHERE", "WHILE", "WITH", "WRITE", "XOR", "YEAR_MONTH",
                "ZEROFILL"
                );
        }
        
        function initFromIniFile($tmpClientIniFile = null)
        {
                global $ilClientIniFile;
                
                parent::initFromIniFile($tmpClientIniFile);
                
                //overwrite global client ini file if local parameter is set 
                if (is_object($tmpClientIniFile))
                        $clientIniFile = $tmpClientIniFile;
                else 
                        $clientIniFile = $ilClientIniFile;      
                        
                if (is_object($clientIniFile ))
                {
                        if ($clientIniFile->readVariable("db", "slave_active") == 1)
                        {
                                $this->setDBSlaveActive(true);
                                $this->setDBSlaveUser($clientIniFile->readVariable("db", "slave_user"));
                                $this->setDBSlaveHost($clientIniFile->readVariable("db", "slave_host"));
                                $this->setDBSlavePort($clientIniFile->readVariable("db", "slave_port"));
                                $this->setDBSlavePassword($clientIniFile->readVariable("db", "slave_pass"));
                                $this->setDBSlaveName($clientIniFile->readVariable("db", "slave_name"));
                        }
                }
        }

        function doConnect()
        {
                parent::doConnect();
                if ($this->getDBSlaveActive())
                {
                        $this->slave = MDB2::connect($this->getSlaveDSN(),
                                array("use_transactions" => false));
                }
        }

        
        function initConnection()
        {
                // SET 'max_allowed_packet' (only possible for mysql version 4)
                $this->setMaxAllowedPacket();
                
                // NOTE: Two sourcecodes use this or a similar handling:
                // - classes/class.ilDB.php
                // - setup/classes/class.ilClient.php

                $this->query("SET NAMES utf8");
                if (DEVMODE == 1)
                {
                        $this->query("SET SESSION SQL_MODE = 'ONLY_FULL_GROUP_BY'");
                }
                $this->setStorageEngine('MYISAM');
        }
        
        function now()
        {
                return "NOW()";
        }


        function getDBVersion()
        {
                if(!$this->isMySQLi())
                {
                        $vers = @mysql_get_server_info();
                }
                else
                {
                    $vers = @mysqli_get_server_info($this->db->connection);
                }               
                if (trim($vers) == "")
                {
                        $vers = "Unknown";
                }
                return $vers;
        }
        

        function isMysql4_0OrHigher()
        {
                $version = explode(".", $this->getDBVersion());
                if((int) $version[0] < 4)
                {
                        return false;
                }
                return true;
        }

        function isMysql4_1()
        {
                $version = explode(".", $this->getDBVersion());
                if ($version[0] == "4" && $version[1] == "1")
                {
                        return true;
                }
                
                return false;
        }

        function isMysql4_1OrHigher()
        {
                $version = explode(".", $this->getDBVersion());
                if ((int)$version[0] >= 5 ||
                        ((int)$version[0] == 4 && (int)$version[1] >= 1))
                {
                        return true;
                }
                
                return false;
        }
        
        public function isMysql5_6OrHigher()
        {
                $version = explode(".", $this->getDBVersion());
                if(
                        (int) $version[0] > 5 ||
                        ((int) $version[0] == 5 && (int) $version[1] >= 6))
                {
                        return true;
                }               
                return false;
        }
        
        function checkQuerySize($a_query)
        {
                global $lang;

                if(strlen($a_query) >= $this->max_allowed_packet_size)
                {
                        return false;
                }
                else
                {
                        return true;
                }
        }

        protected function setMaxAllowedPacket()
        {
                $version = $this->getDBVersion();

                // CHANG VALUE IF MYSQL VERSION > 4.0
                // Switched back to "SET GLOBAL ..."
                // @see http://bugs.mysql.com/bug.php?id=22891
                // smeyer 2009 07 30
                if (substr($version,0,1) == "4")
                {
                        ini_get("post_max_size");
                        $query = "SET GLOBAL max_allowed_packet = ".(int) ini_get("post_max_size") * 1024 * 1024;
//echo "-".$query."-";
                        $this->query($query);
                }
                // STORE NEW max_size in member variable
                $query = "SHOW VARIABLES LIKE 'max_allowed_packet'";
                $res = $this->query($query);

                while($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT))
                {
                        $this->max_allowed_packet_size = $row->value;
                }
//echo "-".$this->max_allowed_packet_size."-";
                return true;
        }

        function supportsFulltext()
        {
                return true;
        }

        function addFulltextIndex($a_table, $a_fields, $a_name = "in")
        {
                $i_name = $this->constraintName($a_table, $a_name)."_idx";
                $f_str = implode($a_fields, ",");
                $q = "ALTER TABLE $a_table ADD FULLTEXT $i_name ($f_str)";
                $this->query($q);
        }

        function dropFulltextIndex($a_table, $a_name)
        {
                $i_name = $this->constraintName($a_table, $a_name)."_idx";
                $this->query("ALTER TABLE $a_table DROP FULLTEXT $i_name");
        }

        function isFulltextIndex($a_table, $a_name)
        {
                $set = $this->query("SHOW INDEX FROM ".$a_table);
                while ($rec = $this->fetchAssoc($set))
                {
                        if ($rec["Key_name"] == $a_name && $rec["Index_type"] == "FULLTEXT")
                        {
                                return true;
                        }
                }
        }
        
        public function lockTables($a_tables)
        {
                global $ilLog;
                
                $lock = 'LOCK TABLES ';

                $counter = 0;
                foreach($a_tables as $table)
                {
                        if($counter++)
                        {
                                $lock .= ', ';
                        }
                        
                        if( isset($table['sequence']) && $table['sequence'] )
                        {
                                $tableName = $this->db->getSequenceName($table['name']);
                        }
                        else
                        {
                                $tableName = $table['name'];
                        }
                        
                        $lock .= ($tableName.' ');
                        
                        if($table['alias'])
                        {
                                $lock .= ($table['alias'].' ');
                        }

                        switch($table['type'])
                        {
                                case ilDBConstants::LOCK_READ:
                                        $lock .= ' READ ';
                                        break;
                                
                                case ilDBConstants::LOCK_WRITE:
                                        $lock .= ' WRITE ';
                                        break;
                        }
                }
                if($ilLog instanceof ilLog) {
                        $ilLog->write(__METHOD__.': '.$lock);
                }

                $this->query($lock);
        }
        
        public function unlockTables()
        {
                $this->query('UNLOCK TABLES');
        }
        
        protected function getCreateTableOptions()
        {
                // InnoDB is default engine for MySQL >= 5.5
                return array('type' => 'MyISAM');
        }
        
        public function getErrorNo()
        {
                if(!$this->isMySQLi())
                {
                        return mysql_errno();
                }
                else
                {
                        return mysqli_errno($this->db->connection);
                }
        }
        
        public function getLastError()
        {
                if(!$this->isMySQLi())
                {
                        return mysql_error();
                }
                else
                {
                        return mysqli_error($this->db->connection);
                }
        }


        function query($sql, $a_handle_error = true)
        {
                if (!$this->use_slave || !$this->getDBSlaveActive())
                {
                        return parent::query($sql, $a_handle_error);
                }
                
                $r = $this->slave->query($sql);
                
                if ($a_handle_error)
                {
                        return $this->handleError($r, "query(".$sql.")");
                }

                return $r;

        }


        public function loadModule($module) {
                return $this->db->loadModule($module);
        }


        public function getStorageEngine() {
                return 'MyISAM';
        }

        public function groupConcat($a_field_name, $a_seperator = ",", $a_order = NULL) {
                if ($a_order === NULL) {
                        $sql = "GROUP_CONCAT(" . $a_field_name . " SEPARATOR " . $this->quote($a_seperator, "text") . ")";
                } else {
                        $sql = "GROUP_CONCAT(" . $a_field_name . " ORDER BY " . $a_order . " SEPARATOR " . $this->quote($a_seperator, "text"). ")";
                        
                }
                return $sql;
        }
}