class.ilDB.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
 
//pear MDB2 abstraction layer
require_once('./Services/Database/lib/PEAR/MDB2.php');
require_once 'Services/Database/classes/QueryUtils/class.ilMySQLQueryUtils.php';
require_once 'Services/Database/interfaces/interface.ilDBInterface.php';
 
//echo "-".ilDBConstants::FETCHMODE_ASSOC."-";
//echo "+".ilDBConstants::FETCHMODE_OBJECT."+";
 
 
abstract class ilDB extends PEAR implements ilDBInterface
{
    const LOCK_WRITE = 1;
    const LOCK_READ  = 2;
    
    
    public $error_class;
 
    public $db;
 
    public $result;
 
 
    public $allowed_attributes = array(
            "text" => array("length", "notnull", "default", "fixed"),
            "integer" => array("length", "notnull", "default", "unsigned"),
            "float" => array("notnull", "default"),
            "date" => array("notnull", "default"),
            "time" => array("notnull", "default"),
            "timestamp" => array("notnull", "default"),
            "clob" => array("notnull", "default"),
            "blob" => array("notnull", "default")
        );
    
    public $sub_type;
 
    public function setDBUser($a_user)
    {
        $this->db_user = $a_user;
    }
    
    public function getDBUser()
    {
        return $this->db_user;
    }
 
    public function setDBPort($a_port)
    {
        $this->db_port = $a_port;
    }
    
    public function getDBPort()
    {
        return $this->db_port;
    }
 
    public function setDBHost($a_host)
    {
        $this->db_host = $a_host;
    }
    
    public function getDBHost()
    {
        return $this->db_host;
    }
 
    public function setDBPassword($a_password)
    {
        $this->db_password = $a_password;
    }
    
    public function getDBPassword()
    {
        return $this->db_password;
    }
 
    public function setDBName($a_name)
    {
        $this->db_name = $a_name;
    }
    
    public function getDBName()
    {
        return $this->db_name;
    }
 
    abstract public function getDSN();
    
    public function getDBVersion()
    {
        return "Unknown";
    }
 
    abstract public function getDBType();
 
    public static function getReservedWords()
    {
        return array();
    }
 
 
    public function enableResultBuffering($a_status)
    {
        $this->db->setOption('result_buffering', $a_status);
    }
 
    public function initFromIniFile($tmpClientIniFile = null)
    {
        global $ilClientIniFile;
        
        //overwrite global client ini file if local parameter is set
        if (is_object($tmpClientIniFile)) {
            $clientIniFile = $tmpClientIniFile;
        } else {
            $clientIniFile = $ilClientIniFile;
        }
            
        if (is_object($clientIniFile)) {
            $this->setDBUser($clientIniFile ->readVariable("db", "user"));
            $this->setDBHost($clientIniFile ->readVariable("db", "host"));
            $this->setDBPort($clientIniFile ->readVariable("db", "port"));
            $this->setDBPassword($clientIniFile ->readVariable("db", "pass"));
            $this->setDBName($clientIniFile ->readVariable("db", "name"));
        }
    }
    
    public function connect($a_return_false_for_error = false)
    {
        //set up error handling
        $this->error_class = new ilErrorHandling();
        $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
        //echo $this->getDSN();
        //check dsn
        if ($this->getDSN() == "") {
            $this->raisePearError("No DSN given");
        }
 
        //connect to database
        $this->doConnect();
        
        if ($a_return_false_for_error && MDB2::isError($this->db)) {
            return false;
        }
            
        $this->loadMDB2Extensions();
        
        // set empty value portability to PEAR::DB behaviour
        if (!$this->isDbError($this->db)) {
            $this->db->setOption('portability', MDB2_PORTABILITY_ALL);
        }
        //check error
        $this->handleError($this->db);
        
        // anything, that must be done to initialize the connection
        $this->initConnection();
 
        return true;
    }
    
    public function doConnect()
    {
        $this->db = MDB2::connect(
            $this->getDSN(),
            array("use_transactions" => true)
        );
    }
    
    public function disconnect()
    {
        $this->db->disconnect();
    }
    
    //
    // General and MDB2 related functions
    //
 
    protected function initConnection()
    {
    }
    
    public function getHostDSN()
    {
        return false;
    }
 
    public function connectHost()
    {
        //set up error handling
        $this->error_class = new ilErrorHandling();
        $this->setErrorHandling(PEAR_ERROR_CALLBACK, array($this->error_class,'errorHandler'));
        
        //check dsn
        if ($this->getHostDSN() == "") {
            $this->raisePearError("No Host DSN given");
        }
 
        //connect to database
        $this->db = MDB2::connect(
            $this->getHostDSN(),
            array("use_transactions" => true)
        );
        if ($a_return_false_for_error && MDB2::isError($this->db)) {
            return false;
        }
            
        $this->loadMDB2Extensions();
        
        // set empty value portability to PEAR::DB behaviour
        if (!$this->isDbError($this->db)) {
            $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_EMPTY_TO_NULL);
            $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
 
            $cur = ($this->db->getOption("portability") & MDB2_PORTABILITY_FIX_CASE);
            $this->db->setOption("portability", $this->db->getOption("portability") - $cur);
        }
 
        //check error
        $this->handleError($this->db);
        
        // anything, that must be done to initialize the connection
        $this->initHostConnection();
 
        return true;
    }
    
    protected function initHostConnection()
    {
    }
 
    public function supportsFulltext()
    {
        return false;
    }
 
    public function supportsSlave()
    {
        return false;
    }
 
    public function supports($feature)
    {
        switch ($feature) {
            case 'transaction':
                return $this->supportsTransactions();
            case 'fulltext':
                return $this->supportsFulltext();
            case 'slave':
                return $this->supportsSlave();
            default:
                return false;
        }
    }
 
 
    public function supportsTransactions()
    {
        // we generally do not want ilDB to support transactions, only PDO-instances
        return false;
    }
 
    public function useSlave($a_val = true)
    {
        if (!$this->supportsSlave()) {
            return false;
        }
        $this->use_slave = $a_val;
    }
 
    public function handleError($a_res, $a_info = "", $a_level = "")
    {
        global $ilLog;
 
        if (MDB2::isError($a_res)) {
            if ($a_level == "") {
                $a_level = $this->error_class->FATAL;
            }
 
            // :TODO: ADT (jluetzen)
 
            // if(!$this->exception)
            if (true) {
                // Show stack
                try {
                    throw new Exception();
                } catch (Exception $e) {
                    $stack = $e->getTraceAsString();
                }
 
                if (is_object($ilLog)) {
                    $ilLog->logStack();
                }
                //                              $this->raisePearError("ilDB Error: " . $a_info . "<br />" . $a_res->getMessage() . "<br />" . $a_res->getUserInfo() . "<br />"
                //                                                    . $stack, $a_level);
 
                throw new ilDatabaseException("ilDB Error: " . $a_info . "<br />" . $a_res->getMessage() . "<br />" . $a_res->getUserInfo() . "<br />"
                                              . $stack, $a_level);
            }
        }
 
        return $a_res;
    }
    
    public function raisePearError($a_message, $a_level = "")
    {
        if ($a_level == "") {
            $a_level = $this->error_class->FATAL;
        }
        //echo "<br>-ilDB:raising-$a_message-$a_level-";
        $this->raiseError($a_message, $a_level);
    }
    
    protected function loadMDB2Extensions()
    {
        if (!$this->isDbError($this->db)) {
            $this->db->loadModule('Extended');
            define('DB_AUTOQUERY_SELECT', MDB2_AUTOQUERY_SELECT);
            define('DB_AUTOQUERY_INSERT', MDB2_AUTOQUERY_INSERT);
            define('DB_AUTOQUERY_UPDATE', MDB2_AUTOQUERY_UPDATE);
            define('DB_AUTOQUERY_DELETE', MDB2_AUTOQUERY_DELETE);
        }
    }
 
    public static function isDbError($a_res)
    {
        return MDB2::isError($a_res);
    }
 
    //
    // Data Definition Methods
    //
    
    public function createDatabase($a_name, $a_charset = "utf8", $a_collation = "")
    {
        if ($a_collation != "") {
            $sql = "CREATE DATABASE " . $a_name .
                " CHARACTER SET " . $a_charset .
                " COLLATE " . $a_collation;
        } else {
            $sql = "CREATE DATABASE " . $a_name .
                " CHARACTER SET " . $a_charset;
        }
 
        return $this->query($sql, false);
    }
    
    
    public function createTable(
        $a_name,
        $a_definition_array,
        $a_drop_table = false,
        $a_ignore_erros = false
    ) {
        // check table name
        if (!$this->checkTableName($a_name) && !$a_ignore_erros) {
            $this->raisePearError("ilDB Error: createTable(" . $a_name . ")<br />" .
                $this->error_str);
        }
        
        // check definition array
        if (!$this->checkTableColumns($a_definition_array) && !$a_ignore_erros) {
            $this->raisePearError("ilDB Error: createTable(" . $a_name . ")<br />" .
                $this->error_str);
        }
 
        if ($a_drop_table) {
            $this->dropTable($a_name, false);
        }
        
        $options = $this->getCreateTableOptions();
        
        $manager = $this->db->loadModule('Manager');
        $r = $manager->createTable($a_name, $a_definition_array, $options);
 
        return $this->handleError($r, "createTable(" . $a_name . ")");
    }
    
    protected function getCreateTableOptions()
    {
        return array();
    }
    
    public function dropTable($a_name, $a_error_if_not_existing = true)
    {
        if (!$a_error_if_not_existing) {
            $tables = $this->listTables();
            if (!in_array($a_name, $tables)) {
                return;
            }
        }
        
        $manager = $this->db->loadModule('Manager');
 
        if ($this->getDBType() == "oracle") {
            // drop table constraints
            $constraints = $manager->listTableConstraints($a_name);
            $this->handleError($constraints, "dropTable(" . $a_name . "), listTableConstraints");
            foreach ($constraints as $c) {
                if (substr($c, 0, 4) != "sys_") {
                    $r = $manager->dropConstraint($a_name, $c);
                    $this->handleError($r, "dropTable(" . $a_name . "), dropConstraint");
                }
            }
 
            // drop table indexes
            $indexes = $manager->listTableIndexes($a_name);
            $this->handleError($indexes, "dropTable(" . $a_name . "), listTableIndexes");
            foreach ($indexes as $i) {
                $r = $manager->dropIndex($a_name, $i);
                $this->handleError($r, "dropTable(" . $a_name . "), dropIndex");
            }
        }
 
        // drop sequence
        $seqs = $manager->listSequences();
        if (in_array($a_name, $seqs)) {
            $r = $manager->dropSequence($a_name);
            $this->handleError($r, "dropTable(" . $a_name . "), dropSequence");
        }
        
        // drop table
        $r = $manager->dropTable($a_name);
 
        return $this->handleError($r, "dropTable(" . $a_name . ")");
    }
    
    public function alterTable($a_name, $a_changes)
    {
        if ($a_options == "") {
            $a_options = array();
        }
        
        $manager = $this->db->loadModule('Manager');
        $r = $manager->alterTable($a_name, $a_changes, false);
 
        return $this->handleError($r, "alterTable(" . $a_name . ")");
    }
 
    public function addTableColumn($a_table, $a_column, $a_attributes)
    {
        $manager = $this->db->loadModule('Manager');
 
        if (!$this->checkColumnName($a_column)) {
            $this->raisePearError("ilDB Error: addTableColumn(" . $a_table . ", " . $a_column . ")<br />" .
                $this->error_str);
        }
        if (!$this->checkColumnDefinition($a_attributes)) {
            $this->raisePearError("ilDB Error: addTableColumn(" . $a_table . ", " . $a_column . ")<br />" .
                $this->error_str);
        }
        
        $changes = array(
            "add" => array(
                $a_column => $a_attributes
                )
            );
 
        $r = $manager->alterTable($a_table, $changes, false);
 
        return $this->handleError($r, "addTableColumn(" . $a_table . ", " . $a_column . ")");
    }
 
    public function dropTableColumn($a_table, $a_column)
    {
        $manager = $this->db->loadModule('Manager');
 
        $changes = array(
            "remove" => array(
                $a_column => array()
                )
            );
 
        $r = $manager->alterTable($a_table, $changes, false);
 
        return $this->handleError($r, "dropTableColumn(" . $a_table . ", " . $a_column . ")");
    }
    
    public function modifyTableColumn($a_table, $a_column, $a_attributes)
    {
        $manager = $this->db->loadModule('Manager');
        $reverse = $this->db->loadModule('Reverse');
        $def = $reverse->getTableFieldDefinition($a_table, $a_column);
        
        $this->handleError($def, "modifyTableColumn(" . $a_table . ")");
 
        if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php")) {
            include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
        } else {
            include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
        }
        $analyzer = new ilDBAnalyzer();
        $best_alt = $analyzer->getBestDefinitionAlternative($def);
        $def = $def[$best_alt];
        unset($def["nativetype"]);
        unset($def["mdb2type"]);
 
        // check attributes
        $type = ($a_attributes["type"] != "")
            ? $a_attributes["type"]
            : $def["type"];
        foreach ($def as $k => $v) {
            if ($k != "type" && !in_array($k, $this->allowed_attributes[$type])) {
                unset($def[$k]);
            }
        }
        $check_array = $def;
        foreach ($a_attributes as $k => $v) {
            $check_array[$k] = $v;
        }
        if (!$this->checkColumnDefinition($check_array, true)) {
            $this->raisePearError("ilDB Error: modifyTableColumn(" . $a_table . ", " . $a_column . ")<br />" .
                $this->error_str);
        }
 
        // oracle workaround: do not set null, if null already given
        if ($this->getDbType() == "oracle") {
            if ($def["notnull"] == true && ($a_attributes["notnull"] == true
                || !isset($a_attributes["notnull"]))) {
                unset($def["notnull"]);
                unset($a_attributes["notnull"]);
            }
            if ($def["notnull"] == false && ($a_attributes["notnull"] == false
                || !isset($a_attributes["notnull"]))) {
                unset($def["notnull"]);
                unset($a_attributes["notnull"]);
            }
        }
        foreach ($a_attributes as $a => $v) {
            $def[$a] = $v;
        }
        
        $a_attributes["definition"] = $def;
        
        $changes = array(
            "change" => array(
                $a_column => $a_attributes
                )
            );
 
        $r = $manager->alterTable($a_table, $changes, false);
 
        return $this->handleError($r, "modifyTableColumn(" . $a_table . ")");
    }
 
    public function renameTableColumn($a_table, $a_column, $a_new_column)
    {
        // check table name
        if (!$this->checkColumnName($a_new_column)) {
            $this->raisePearError("ilDB Error: renameTableColumn(" . $a_table . "," . $a_column . "," . $a_new_column . ")<br />" .
                $this->error_str);
        }
 
        $manager = $this->db->loadModule('Manager');
        $reverse = $this->db->loadModule('Reverse');
        $def = $reverse->getTableFieldDefinition($a_table, $a_column);
        
        $this->handleError($def, "renameTableColumn(" . $a_table . "," . $a_column . "," . $a_new_column . ")");
 
        if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php")) {
            include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
        } else {
            include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
        }
 
        $analyzer = new ilDBAnalyzer();
        $best_alt = $analyzer->getBestDefinitionAlternative($def);
        $def = $def[$best_alt];
        unset($def["nativetype"]);
        unset($def["mdb2type"]);
        
        $f["definition"] = $def;
        $f["name"] = $a_new_column;
        
        $changes = array(
            "rename" => array(
                $a_column => $f
                )
            );
 
        $r = $manager->alterTable($a_table, $changes, false);
 
        return $this->handleError($r, "renameTableColumn(" . $a_table . "," . $a_column . "," . $a_new_column . ")");
    }
 
    public function renameTable($a_name, $a_new_name)
    {
        // check table name
        if (!$this->checkTableName($a_new_name)) {
            $this->raisePearError("ilDB Error: renameTable(" . $a_name . "," . $a_new_name . ")<br />" .
                $this->error_str);
        }
 
        $manager = $this->db->loadModule('Manager');
        $r = $manager->alterTable($a_name, array("name" => $a_new_name), false);
        
        // The abstraction_progress is no longer used in ILIAS, see http://www.ilias.de/mantis/view.php?id=19513
        //              $query = "UPDATE abstraction_progress ".
        //                      "SET table_name = ".$this->db->quote($a_new_name,'text')." ".
        //                      "WHERE table_name = ".$this->db->quote($a_name,'text');
        //              $this->db->query($query);
 
        return $this->handleError($r, "renameTable(" . $a_name . "," . $a_new_name . ")");
    }
 
    public function addPrimaryKey($a_table, $a_fields)
    {
        $manager = $this->db->loadModule('Manager');
        
        $fields = array();
        foreach ($a_fields as $f) {
            $fields[$f] = array();
        }
        $definition = array(
            'primary' => true,
            'fields' => $fields
        );
        $r = $manager->createConstraint(
            $a_table,
            $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()),
            $definition
        );
 
        return $this->handleError($r, "addPrimaryKey(" . $a_table . ")");
    }
    
    public function getPrimaryKeyIdentifier()
    {
        return "PRIMARY";
    }
    
    public function dropPrimaryKey($a_table)
    {
        $manager = $this->db->loadModule('Manager');
        
        $r = $manager->dropConstraint(
            $a_table,
            $this->constraintName($a_table, $this->getPrimaryKeyIdentifier()),
            true
        );
 
        return $this->handleError($r, "dropPrimaryKey(" . $a_table . ")");
    }
 
    public function addIndex($a_table, $a_fields, $a_name = "in", $a_fulltext = false)
    {
        $manager = $this->db->loadModule('Manager');
        
        // check index name
        if (!$this->checkIndexName($a_name)) {
            $this->raisePearError("ilDB Error: addIndex(" . $a_table . "," . $a_name . ")<br />" .
                $this->error_str);
        }
        
        $fields = array();
        foreach ($a_fields as $f) {
            $fields[$f] = array();
        }
        $definition = array(
            'fields' => $fields
        );
        
        if (!$a_fulltext) {
            $r = $manager->createIndex($a_table, $this->constraintName($a_table, $a_name), $definition);
        } else {
            if ($this->supportsFulltext()) {
                $this->addFulltextIndex($a_table, $a_fields, $a_name);
            }
        }
 
        return $this->handleError($r, "addIndex(" . $a_table . ")");
    }
 
    public function addFulltextIndex($a_table, $a_fields, $a_name = "in")
    {
        return false;
    }
 
    public function isFulltextIndex($a_table, $a_name)
    {
        return false;
    }
    
    
    public function indexExistsByFields($a_table, $a_fields)
    {
        $manager = $this->db->loadModule('Manager');
        $reverse = $this->db->loadModule('Reverse');
        if ($manager) {
            foreach ($manager->listTableIndexes($a_table) as $idx_name) {
                $def = $reverse->getTableIndexDefinition($a_table, $idx_name);
                $idx_fields = array_keys((array) $def['fields']);
                
                if ($idx_fields === $a_fields) {
                    return true;
                }
            }
        }
        return false;
    }
    
    public function dropIndexByFields($a_table, $a_fields)
    {
        $manager = $this->db->loadModule('Manager');
        $reverse = $this->db->loadModule('Reverse');
        if ($manager) {
            foreach ($manager->listTableIndexes($a_table) as $idx_name) {
                $def = $reverse->getTableIndexDefinition($a_table, $idx_name);
                $idx_fields = array_keys((array) $def['fields']);
                
                if ($idx_fields === $a_fields) {
                    return $this->dropIndex($a_table, $idx_name);
                }
            }
        }
        return false;
    }
    
    public function dropIndex($a_table, $a_name = "in")
    {
        $manager = $this->db->loadModule('Manager');
        
        if (!$this->isFulltextIndex($a_table, $a_name)) {
            $r = $manager->dropIndex($a_table, $this->constraintName($a_table, $a_name));
        } else {
            $this->dropFulltextIndex($a_table, $a_name);
        }
 
        return $this->handleError($r, "dropIndex(" . $a_table . ")");
    }
 
    public function addUniqueConstraint($a_table, $a_fields, $a_name = "con")
    {
        $manager = $this->db->loadModule('Manager');
        
        // check index name
        if (!$this->checkIndexName($a_name)) {
            $this->raisePearError("ilDB Error: addUniqueConstraint(" . $a_table . "," . $a_name . ")<br />" .
                $this->error_str);
        }
        
        $fields = array();
        foreach ($a_fields as $f) {
            $fields[$f] = array();
        }
        $definition = array(
            'unique' => true,
            'fields' => $fields
        );
        
        $r = $manager->createConstraint($a_table, $this->constraintName($a_table, $a_name), $definition);
 
        return $this->handleError($r, "addUniqueConstraint(" . $a_table . ")");
    }
 
    public function dropUniqueConstraint($a_table, $a_name = "con")
    {
        $manager = $this->db->loadModule('Manager');
 
        $r = $manager->dropConstraint(
            $a_table,
            $this->constraintName($a_table, $a_name),
            false
        );
 
        return $this->handleError($r, "dropUniqueConstraint(" . $a_table . ")");
    }
 
    public function dropUniqueConstraintByFields($a_table, $a_fields)
    {
        if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php")) {
            include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
        } else {
            include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
        }
        $analyzer = new ilDBAnalyzer();
        $cons = $analyzer->getConstraintsInformation($a_table);
        foreach ($cons as $c) {
            if ($c["type"] == "unique" && count($a_fields) == count($c["fields"])) {
                $all_in = true;
                foreach ($a_fields as $f) {
                    if (!isset($c["fields"][$f])) {
                        $all_in = false;
                    }
                }
                if ($all_in) {
                    return $this->dropUniqueConstraint($a_table, $c['name']);
                }
            }
        }
        return false;
    }
 
    public function createSequence($a_table_name, $a_start = 1)
    {
        $manager = $this->db->loadModule('Manager');
 
        $r = $manager->createSequence($a_table_name, $a_start);
 
        return $this->handleError($r, "createSequence(" . $a_table_name . ")");
    }
    
    
    public function dropSequence($a_table_name)
    {
        $manager = $this->db->loadModule('Manager');
        
        $r = $manager->dropSequence($a_table_name);
 
        return $this->handleError($r, "dropSequence(" . $a_table_name . ")");
    }
 
    public function checkTableName($a_name)
    {
        if (!preg_match("/^[a-z]+[_a-z0-9]*$/", $a_name)) {
            $this->error_str = "Table name must only contain _a-z0-9 and must start with a-z.";
            return false;
        }
        
        if ($this->isReservedWord($a_name)) {
            $this->error_str = "Invalid table name '" . $a_name . "' (Reserved Word).";
            return false;
        }
 
        if (strtolower(substr($a_name, 0, 4)) == "sys_") {
            $this->error_str = "Invalid table name '" . $a_name . "'. Name must not start with 'sys_'.";
            return false;
        }
 
        if (strlen($a_name) > 22) {
            $this->error_str = "Invalid table name '" . $a_name . "'. Maximum table identifer length is 22 bytes.";
            return false;
        }
        
        return true;
    }
    
    public function checkTableColumns($a_cols)
    {
        foreach ($a_cols as $col => $def) {
            if (!$this->checkColumn($col, $def)) {
                return false;
            }
        }
        
        return true;
    }
    
    public function checkColumn($a_col, $a_def)
    {
        if (!$this->checkColumnName($a_col)) {
            return false;
        }
 
        if (!$this->checkColumnDefinition($a_def)) {
            return false;
        }
        
        return true;
    }
    
    public function checkColumnDefinition($a_def, $a_modify_mode = false)
    {
        // check valid type
        if (!in_array($a_def["type"], array("text", "integer", "float", "date", "time", "timestamp", "clob", "blob"))) {
            switch ($a_def["type"]) {
                case "boolean":
                    $this->error_str = "Invalid column type '" . $a_def["type"] . "'. Use integer(1) instead.";
                    break;
 
                case "decimal":
                    $this->error_str = "Invalid column type '" . $a_def["type"] . "'. Use float or integer instead.";
                    break;
                    
                default:
                    $this->error_str = "Invalid column type '" . $a_def["type"] . "'. Allowed types are: " .
                        "text, integer, float, date, time, timestamp, clob and blob.";
            }
        }
        
        // check used attributes
        $allowed_attributes = $this->allowed_attributes;
        
        foreach ($a_def as $k => $v) {
            if ($k != "type" && !in_array($k, $allowed_attributes[$a_def["type"]])) {
                $this->error_str = "Attribute '" . $k . "' is not allowed for column type '" . $a_def["type"] . "'.";
                return false;
            }
        }
        
        // type specific checks
        switch ($a_def["type"]) {
            case "text":
                if ($a_def["length"] < 1 || $a_def["length"] > 4000) {
                    if (!$a_modify_mode || isset($a_def["length"])) {
                        $this->error_str = "Invalid length '" . $a_def["length"] . "' for type text." .
                            " Length must be >=1 and <= 4000.";
                        return false;
                    }
                }
                break;
 
            case "integer":
                if (!in_array($a_def["length"], array(1, 2, 3, 4, 8))) {
                    if (!$a_modify_mode || isset($a_def["length"])) {
                        $this->error_str = "Invalid length '" . $a_def["length"] . "' for type integer." .
                            " Length must be 1, 2, 3, 4 or 8 (bytes).";
                        return false;
                    }
                }
                if ($a_def["unsigned"]) {
                    $this->error_str = "Unsigned attribut must not be true for type integer.";
                    return false;
                }
                break;
        }
        
        return true;
    }
    
    public function checkColumnName($a_name)
    {
        if (!preg_match("/^[a-z]+[_a-z0-9]*$/", $a_name)) {
            $this->error_str = "Invalid column name '" . $a_name . "'. Column name must only contain _a-z0-9 and must start with a-z.";
            return false;
        }
        
        if ($this->isReservedWord($a_name)) {
            $this->error_str = "Invalid column name '" . $a_name . "' (Reserved Word).";
            return false;
        }
        
        if (strtolower(substr($a_name, 0, 4)) == "sys_") {
            $this->error_str = "Invalid column name '" . $a_name . "'. Name must not start with 'sys_'.";
            return false;
        }
 
        if (strlen($a_name) > 30) {
            $this->error_str = "Invalid column name '" . $a_name . "'. Maximum column identifer length is 30 bytes.";
            return false;
        }
 
        return true;
    }
 
    public function checkIndexName($a_name)
    {
        if (!preg_match("/^[a-z]+[_a-z0-9]*$/", $a_name)) {
            $this->error_str = "Invalid column name '" . $a_name . "'. Column name must only contain _a-z0-9 and must start with a-z.";
            return false;
        }
        
        if ($this->isReservedWord($a_name)) {
            $this->error_str = "Invalid column name '" . $a_name . "' (Reserved Word).";
            return false;
        }
        
        if (strlen($a_name) > 3) {
            $this->error_str = "Invalid index name '" . $a_name . "'. Maximum index identifer length is 3 bytes.";
            return false;
        }
 
        return true;
    }
 
    public function getAllowedAttributes()
    {
        return $this->allowed_attributes;
    }
    
    public function constraintName($a_table, $a_constraint)
    {
        return $a_constraint;
    }
 
    public static function isReservedWord($a_word)
    {
        require_once('./Services/Database/classes/PDO/FieldDefinition/class.ilDBPdoMySQLFieldDefinition.php');
        global $DIC;
        $ilDBPdoMySQLFieldDefinition = new ilDBPdoMySQLFieldDefinition($DIC['ilDB']);
 
        return $ilDBPdoMySQLFieldDefinition->isReserved($a_word);
    }
    
    //
    // Data query and manupilation functions
    //
    
    public function query($sql, $a_handle_error = true)
    {
        global $ilBench;
 
        if (is_object($ilBench)) {
            $ilBench->startDbBench($sql);
        }
        $r = $this->db->query($sql);
        if (is_object($ilBench)) {
            $ilBench->stopDbBench();
        }
        
        if ($a_handle_error) {
            return $this->handleError($r, "query(" . $sql . ")");
        }
 
        return $r;
    }
 
    public function queryF($a_query, $a_types, $a_values)
    {
        if (!is_array($a_types) || !is_array($a_values) ||
            count($a_types) != count($a_values)) {
            $this->raisePearError("ilDB::queryF: Types and values must be arrays of same size. ($a_query)");
        }
        $quoted_values = array();
        foreach ($a_types as $k => $t) {
            $quoted_values[] = $this->quote($a_values[$k], $t);
        }
        $query = vsprintf($a_query, $quoted_values);
        
        return $this->query($query);
    }
    
    public function manipulateF($a_query, $a_types, $a_values)
    {
        if (!is_array($a_types) || !is_array($a_values) ||
            count($a_types) != count($a_values)) {
            $this->raisePearError("ilDB::manipulateF: types and values must be arrays of same size. ($a_query)");
        }
        $quoted_values = array();
        foreach ($a_types as $k => $t) {
            $quoted_values[] = $this->quote($a_values[$k], $t);
        }
        $query = vsprintf($a_query, $quoted_values);
        
        return $this->manipulate($query);
    }
 
    public function logStatement($sql)
    {
        $pos1 = strpos(strtolower($sql), "from ");
        $table = "";
        if ($pos1 > 0) {
            $tablef = substr($sql, $pos1+5);
            $pos2 = strpos(strtolower($tablef), " ");
            if ($pos2 > 0) {
                $table =substr($tablef, 0, $pos2);
            } else {
                $table = $tablef;
            }
        }
        if (trim($table) != "") {
            if (!is_array($this->ttt) || !in_array($table, $this->ttt)) {
                echo "<br>" . $table;
                $this->ttt[] = $table;
            }
        } else {
            echo "<br><b>" . $sql . "</b>";
        }
    }
    
    public function setLimit($a_limit, $a_offset = 0)
    {
        $this->db->setLimit($a_limit, $a_offset);
    }
    
    public function nextId($a_table_name)
    {
        // we do not create missing sequences automatically here
        // otherwise misspelled statements result in additional tables
        // please create sequences explicitly in the db update script
        $r = $this->db->nextId($a_table_name, false);
 
        return $this->handleError($r, "nextId(" . $a_table_name . ")");
    }
 
    public function manipulate($sql)
    {
        global $ilBench;
 
        if (is_object($ilBench)) {
            $ilBench->startDbBench($sql);
        }
        $r = $this->db->exec($sql);
        if (is_object($ilBench)) {
            $ilBench->stopDbBench();
        }
 
        return $this->handleError($r, "manipulate(" . $sql . ")");
    }
 
    public function prepare($a_query, $a_types = null, $a_result_types = null)
    {
        $res = $this->db->prepare($a_query, $a_types, $a_result_types);
        
        return $this->handleError($res, "prepare(" . $a_query . ")");
    }
 
    public function prepareManip($a_query, $a_types = null)
    {
        $res = $this->db->prepare($a_query, $a_types, MDB2_PREPARE_MANIP);
        
        return $this->handleError($res, "prepareManip(" . $a_query . ")");
    }
 
    public function execute($a_stmt, $a_data = null)
    {
        $res = $a_stmt->execute($a_data);
 
        return $this->handleError($res, "execute(" . $a_stmt->query . ")");
    }
 
    public function executeMultiple($a_stmt, $a_data)
    {
        $res = $this->db->extended->executeMultiple($a_stmt, $a_data);
 
        return $this->handleError($res, "executeMultiple(" . $a_stmt->query . ")");
    }
    
    public function insert($a_table, $a_columns)
    {
        $fields = array();
        $field_values = array();
        $placeholders = array();
        $types = array();
        $values = array();
        $lobs = false;
        $lob = array();
        foreach ($a_columns as $k => $col) {
            $fields[] = $k;
            $placeholders[] = "%s";
            $placeholders2[] = ":$k";
            $types[] = $col[0];
            
            // integer auto-typecast (this casts bool values to integer)
            if ($col[0] == 'integer' && !is_null($col[1])) {
                $col[1] = (int) $col[1];
            }
            
            $values[] = $col[1];
            $field_values[$k] = $col[1];
            if ($col[0] == "blob" || $col[0] == "clob") {
                $lobs = true;
                $lob[$k] = $k;
            }
        }
        if ($lobs) {    // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
            $st = $this->db->prepare("INSERT INTO " . $a_table . " (" . implode($fields, ",") . ") VALUES (" .
                implode($placeholders2, ",") . ")", $types, MDB2_PREPARE_MANIP, $lob);
            
            $this->handleError($st, "insert / prepare/execute(" . $a_table . ")");
            
            $r = $st->execute($field_values);
            
            
            //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
            $this->handleError($r, "insert / prepare/execute(" . $a_table . ")");
            $this->free($st);
        } else {        // if no lobs are used, take simple manipulateF
            $q = "INSERT INTO " . $a_table . " (" . implode($fields, ",") . ") VALUES (" .
                implode($placeholders, ",") . ")";
            $r = $this->manipulateF($q, $types, $values);
        }
        return $r;
    }
    
    public function update($a_table, $a_columns, $a_where)
    {
        $fields = array();
        $field_values = array();
        $placeholders = array();
        $types = array();
        $values = array();
        $lobs = false;
        $lob = array();
        foreach ($a_columns as $k => $col) {
            $fields[] = $k;
            $placeholders[] = "%s";
            $placeholders2[] = ":$k";
            $types[] = $col[0];
            
            // integer auto-typecast (this casts bool values to integer)
            if ($col[0] == 'integer' && !is_null($col[1])) {
                $col[1] = (int) $col[1];
            }
 
            $values[] = $col[1];
            $field_values[$k] = $col[1];
            if ($col[0] == "blob" || $col[0] == "clob") {
                $lobs = true;
                $lob[$k] = $k;
            }
        }
        
        if ($lobs) {
            $q = "UPDATE " . $a_table . " SET ";
            $lim = "";
            foreach ($fields as $k => $field) {
                $q.= $lim . $field . " = " . $placeholders2[$k];
                $lim = ", ";
            }
            $q.= " WHERE ";
            $lim = "";
            foreach ($a_where as $k => $col) {
                $q.= $lim . $k . " = " . $this->quote($col[1], $col[0]);
                $lim = " AND ";
            }
            $st = $this->db->prepare($q, $types, MDB2_PREPARE_MANIP, $lob);
            $r = $st->execute($field_values);
            
            //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
            $this->handleError($r, "update / prepare/execute(" . $a_table . ")");
            $this->free($st);
        } else {
            foreach ($a_where as $k => $col) {
                $types[] = $col[0];
                $values[] = $col[1];
                $field_values[$k] = $col;
            }
            $q = "UPDATE " . $a_table . " SET ";
            $lim = "";
            foreach ($fields as $k => $field) {
                $q.= $lim . $field . " = " . $placeholders[$k];
                $lim = ", ";
            }
            $q.= " WHERE ";
            $lim = "";
            foreach ($a_where as $k => $col) {
                $q.= $lim . $k . " = %s";
                $lim = " AND ";
            }
            
            $r = $this->manipulateF($q, $types, $values);
        }
        return $r;
    }
 
    public function replace($a_table, $a_pk_columns, $a_other_columns)
    {
        // this is the mysql implementation
        $a_columns = array_merge($a_pk_columns, $a_other_columns);
        $fields = array();
        $field_values = array();
        $placeholders = array();
        $types = array();
        $values = array();
        $lobs = false;
        $lob = array();
        foreach ($a_columns as $k => $col) {
            $fields[] = $k;
            $placeholders[] = "%s";
            $placeholders2[] = ":$k";
            $types[] = $col[0];
            
            // integer auto-typecast (this casts bool values to integer)
            if ($col[0] == 'integer' && !is_null($col[1])) {
                $col[1] = (int) $col[1];
            }
            
            $values[] = $col[1];
            $field_values[$k] = $col[1];
            if ($col[0] == "blob" || $col[0] == "clob") {
                $lobs = true;
                $lob[$k] = $k;
            }
        }
        if ($lobs) {    // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1)
            $st = $this->db->prepare("REPLACE INTO " . $a_table . " (" . implode($fields, ",") . ") VALUES (" .
                implode($placeholders2, ",") . ")", $types, MDB2_PREPARE_MANIP, $lob);
            $this->handleError($st, "insert / prepare/execute(" . $a_table . ")");
            $r = $st->execute($field_values);
            //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
            $this->handleError($r, "insert / prepare/execute(" . $a_table . ")");
            $this->free($st);
        } else {        // if no lobs are used, take simple manipulateF
            $q = "REPLACE INTO " . $a_table . " (" . implode($fields, ",") . ") VALUES (" .
                implode($placeholders, ",") . ")";
            $r = $this->manipulateF($q, $types, $values);
        }
        return $r;
    }
 
    public function fetchAssoc($a_set)
    {
        return $a_set->fetchRow(ilDBConstants::FETCHMODE_ASSOC);
    }
    
    public function free($a_st)
    {
        return $a_st->free();
    }
 
    public function fetchObject($a_set)
    {
        return $a_set->fetchRow(ilDBConstants::FETCHMODE_OBJECT);
    }
 
    public function numRows($a_set)
    {
        return $a_set->numRows();
    }
 
    //
    // function and clauses abstraction
    //
    
    public function in($a_field, $a_values, $negate = false, $a_type = "")
    {
        if (count($a_values) == 0) {
            // BEGIN fixed mantis #0014191:
            //return " 1=2 ";           // return a false statement on empty array
            return $negate ? ' 1=1 ' : ' 1=2 ';
            // END fixed mantis #0014191:
        }
        if ($a_type == "") {            // untyped: used ? for prepare/execute
            $str = $a_field . (($negate) ? " NOT" : "") . " IN (?" . str_repeat(",?", count($a_values) - 1) . ")";
        } else {                                        // typed, use values for query/manipulate
            $str = $a_field . (($negate) ? " NOT" : "") . " IN (";
            $sep = "";
            foreach ($a_values as $v) {
                $str.= $sep . $this->quote($v, $a_type);
                $sep = ",";
            }
            $str.= ")";
        }
 
        return $str;
    }
    
    public function addTypesToArray($a_arr, $a_type, $a_cnt)
    {
        if (!is_array($a_arr)) {
            $a_arr = array();
        }
        if ($a_cnt > 0) {
            $type_arr = array_fill(0, $a_cnt, $a_type);
        } else {
            $type_arr = array();
        }
        return array_merge($a_arr, $type_arr);
    }
    
    public function now()
    {
        return "now()";
    }
    
    
    public function concat(array $a_values, $a_allow_null = true)
    {
        if (!count($a_values)) {
            return ' ';
        }
 
        $concat = ' CONCAT(';
        $first = true;
        foreach ($a_values as $field_info) {
            $val = $field_info[0];
            
            if (!$first) {
                $concat .= ',';
            }
            
            if ($a_allow_null) {
                $concat .= 'COALESCE(';
            }
            $concat .= $val;
            
            if ($a_allow_null) {
                $concat .= ",''";
                $concat .= ')';
            }
            
            $first = false;
        }
        $concat .= ') ';
        return $concat;
    }
    
    public function substr($a_exp, $a_pos = 1, $a_len = -1)
    {
        $lenstr = "";
        if ($a_len > -1) {
            $lenstr = ", " . $a_len;
        }
        return " SUBSTR(" . $a_exp . ", " . $a_pos . $lenstr . ") ";
    }
    
    public function upper($a_exp)
    {
        return " UPPER(" . $a_exp . ") ";
    }
 
    public function lower($a_exp)
    {
        return " LOWER(" . $a_exp . ") ";
    }
    
    public function locate($a_needle, $a_string, $a_start_pos = 1)
    {
        $locate = ' LOCATE( ';
        $locate .= $a_needle;
        $locate .= ',';
        $locate .= $a_string;
        $locate .= ',';
        $locate .= $a_start_pos;
        $locate .= ') ';
        return $locate;
    }
 
 
    public function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
    {
        if (!in_array($a_type, array("text", "clob", "blob"))) {
            $this->raisePearError("Like: Invalid column type '" . $a_type . "'.", $this->error_class->FATAL);
        }
        if ($a_value == "?") {
            if ($case_insensitive) {
                return "UPPER(" . $a_col . ") LIKE(UPPER(?))";
            } else {
                return $a_col . " LIKE(?)";
            }
        } else {
            if ($case_insensitive) {
                // Always quote as text
                return " UPPER(" . $a_col . ") LIKE(UPPER(" . $this->quote($a_value, 'text') . "))";
            } else {
                // Always quote as text
                return " " . $a_col . " LIKE(" . $this->quote($a_value, 'text') . ")";
            }
        }
    }
    
 
    public function equals($a_col, $a_value, $a_type, $a_empty_or_null = false)
    {
        if (!$a_empty_or_null || $a_value != "") {
            return $a_col . " = " . $this->quote($a_value, $a_type);
        } else {
            return "(" . $a_col . " = '' OR $a_col IS NULL)";
        }
    }
    
    public function equalsNot($a_col, $a_value, $a_type, $a_empty_or_null = false)
    {
        if (!$a_empty_or_null) {
            return $a_col . " <> " . $this->quote($a_value, $a_type);
        }
        if ($a_value != "") {
            return "(" . $a_col . " <> " . $this->quote($a_value, $a_type) . " OR " .
                $a_col . " IS NULL)";
        } else {
            return "(" . $a_col . " <> '' AND $a_col IS NOT NULL)";
        }
    }
 
    public function fromUnixtime($a_expr, $a_to_text = true)
    {
        return "FROM_UNIXTIME(" . $a_expr . ")";
    }
    
    public function unixTimestamp()
    {
        return "UNIX_TIMESTAMP()";
    }
 
    
    //
    // Schema related functions
    //
    
    public function tableExists($a_table)
    {
        $tables = $this->listTables();
 
        if (is_array($tables)) {
            if (in_array($a_table, $tables)) {
                return true;
            }
        }
        return false;
    }
    
    public function tableColumnExists($a_table, $a_column_name)
    {
        $column_visibility = false;
        $manager = $this->db->loadModule('Manager');
        $r = $manager->listTableFields($a_table);
 
        if (!MDB2::isError($r)) {
            foreach ($r as $field) {
                if ($field == $a_column_name) {
                    $column_visibility = true;
                }
            }
        }
 
        return $column_visibility;
    }
 
    public function uniqueConstraintExists($a_table, array $a_fields)
    {
        if (is_file("./Services/Database/classes/class.ilDBAnalyzer.php")) {
            include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
        } else {
            include_once("../Services/Database/classes/class.ilDBAnalyzer.php");
        }
        $analyzer = new ilDBAnalyzer();
        $cons = $analyzer->getConstraintsInformation($a_table);
        foreach ($cons as $c) {
            if ($c["type"] == "unique" && count($a_fields) == count($c["fields"])) {
                $all_in = true;
                foreach ($a_fields as $f) {
                    if (!isset($c["fields"][$f])) {
                        $all_in = false;
                    }
                }
                if ($all_in) {
                    return true;
                }
            }
        }
        return false;
    }
 
 
    public function listTables()
    {
        $manager = $this->db->loadModule('Manager');
        $r = $manager->listTables();
 
        if (!MDB2::isError($r)) {
            return $r;
        }
        
        return false;
    }
 
    public function sequenceExists($a_sequence)
    {
        $sequences = $this->listSequences();
        
        if (is_array($sequences)) {
            if (in_array($a_sequence, $sequences)) {
                return true;
            }
        }
        return false;
    }
    
    public function listSequences()
    {
        $manager = $this->db->loadModule('Manager');
        $r = $manager->listSequences();
 
        if (!MDB2::isError($r)) {
            return $r;
        }
        
        return false;
    }
    
    
    //
    // Quote Functions
    //
    
    public function quote($a_query, $a_type = null)
    {
        if ($a_query == "" && is_null($a_type)) {
            $a_query = "";
        }
        
        // Performance fix
        if ($a_type == 'integer' && !is_null($a_query)) {
            return (int) $a_query;
        }
        
        if ($a_type == "blob" || $a_type == "clob") {
            $this->raisePearError("ilDB::quote: Quoting not allowed on type '" . $a_type . "'. Please use ilDB->insert and ilDB->update to write clobs.", $this->error_class->FATAL);
        }
 
        return $this->db->quote($a_query, $a_type);
    }
    
    public function quoteIdentifier($a_identifier, $check_option = false)
    {
        return $this->db->quoteIdentifier($a_identifier);
    }
    
 
    //
    // Transaction and Locking methods
    //
    
    public function beginTransaction()
    {
        if (!$this->db->supports('transactions')) {
            $this->raisePearError("ilDB::beginTransaction: Transactions are not supported.", $this->error_class->FATAL);
        }
        $res = $this->db->beginTransaction();
        
        return $this->handleError($res, "beginTransaction()");
    }
    
    public function commit()
    {
        $res = $this->db->commit();
        
        return $this->handleError($res, "commit()");
    }
 
    public function rollback()
    {
        $res = $this->db->rollback();
        
        return $this->handleError($res, "rollback()");
    }
    
    abstract public function lockTables($a_tables);
    
    abstract public function unlockTables();
 
    
//
//
    // Older functions. Must be checked.
//
//
 
    public function autoExecute($a_tablename, $a_fields, $a_mode = MDB2_AUTOQUERY_INSERT, $a_where = false)
    {
        $res = $this->db->autoExecute($a_tablename, $a_fields, $a_mode, $a_where);
 
        return $this->handleError($res, "autoExecute(" . $a_tablename . ")");
    }
 
//
//
    // Deprecated functions.
//
//
 
    public function getLastInsertId()
    {
        $res = $this->db->lastInsertId();
        if (MDB2::isError($res)) {
            return false;
        }
        return $res;
    }
 
    public function getOne($sql)
    {
        //$r = $this->db->getOne($sql);
        $set = $this->db->query($sql);
        
        $this->handleError($set, "getOne(" . $sql . ")");
        
        if (!MDB2::isError($set)) {
            $r = $set->fetchRow(ilDBConstants::FETCHMODE_ASSOC);
    
            return $r[0];
        }
    }
 
    public function getRow($sql, $mode = ilDBConstants::FETCHMODE_OBJECT)
    {
        $set = $this->query($sql);
        $r = $set->fetchRow($mode);
        //$r = $this->db->getrow($sql,$mode);
 
        $this->handleError($r, "getRow(" . $sql . ")");
        
        return $r;
    } //end function
 
    public function fetchAll($query_result, $fetch_mode = ilDBConstants::FETCHMODE_ASSOC)
    {
        $return = array();
        while ($data = $query_result->fetch($fetch_mode)) {
            $return[] = $data;
        }
 
        return $return;
    }
        
    public function setSubType($a_value)
    {
        $this->sub_type = (string) $a_value;
    }
    
    public function getSubType()
    {
        return $this->sub_type;
    }
 
 
    public function migrateAllTablesToEngine($engine = ilDBConstants::MYSQL_ENGINE_INNODB)
    {
        return array();
    }
 
 
    public function supportsEngineMigration()
    {
        return false;
    }
 
    
    public function getSequenceName($table_name)
    {
        return $this->db->getSequenceName($table_name);
    }
 
 
    public function buildAtomQuery()
    {
        require_once('./Services/Database/classes/Atom/class.ilAtomQueryLock.php');
 
        return new ilAtomQueryLock($this);
    }
 
 
    public function sanitizeMB4StringIfNotSupported($query)
    {
        if (!$this->doesCollationSupportMB4Strings()) {
            $query_replaced = preg_replace(
                '/[\x{10000}-\x{10FFFF}]/u',
                ilDBConstants::MB4_REPLACEMENT,
                $query
            );
            if (!empty($query_replaced)) {
                return $query_replaced;
            }
        }
 
        return $query;
    }
 
 
    public function doesCollationSupportMB4Strings()
    {
        return false;
    }
 
 
    public function cast($a_field_name, $a_dest_type)
    {
        $manager = $this->db->loadModule('Manager');
        return $manager->getQueryUtils()->cast($a_field_name, $a_dest_type);
    }
}