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
include_once ("MDB2.php");

define("DB_FETCHMODE_ASSOC", MDB2_FETCHMODE_ASSOC);
define("DB_FETCHMODE_OBJECT", MDB2_FETCHMODE_OBJECT);

//echo "-".DB_FETCHMODE_ASSOC."-";
//echo "+".DB_FETCHMODE_OBJECT."+";


abstract class ilDB extends PEAR
{
        const LOCK_WRITE = 1;
        const LOCK_READ  = 2;
        
        
        var $error_class;

        var $db;

        var $result;


        var $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")
                );
        
        var $sub_type;

        function ilDB()
        {
        }
        
        function setDBUser($a_user)
        {
                $this->db_user = $a_user;
        }
        
        function getDBUser()
        {
                return $this->db_user;
        }

        function setDBPort($a_port)
        {
                $this->db_port = $a_port;
        }
        
        function getDBPort()
        {
                return $this->db_port;
        }

        function setDBHost($a_host)
        {
                $this->db_host = $a_host;
        }
        
        function getDBHost()
        {
                return $this->db_host;
        }

        function setDBPassword($a_password)
        {
                $this->db_password = $a_password;
        }
        
        function getDBPassword()
        {
                return $this->db_password;
        }

        function setDBName($a_name)
        {
                $this->db_name = $a_name;
        }
        
        function getDBName()
        {
                return $this->db_name;
        }

        abstract function getDSN();
        
        function getDBVersion()
        {
                return "Unknown";
        }

        abstract function getDBType();

        abstract static function getReservedWords();


        public function enableResultBuffering($a_status)
        {
                $this->db->setOption('result_buffering',$a_status);
        }

        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"));
                }
        }
        
        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;
        }
        
        function doConnect()
        {
                $this->db = MDB2::connect($this->getDSN(),
                        array("use_transactions" => true));
        }
        
        function disconnect()
        {
                $this->db->disconnect();
        }
        
        //
        // General and MDB2 related functions
        //

        protected function initConnection()
        {
        }
        
        function getHostDSN()
        {
                return false;
        }

        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()
        {
        }

        function supportsFulltext()
        {
                return false;
        }

        function supportsSlave()
        {
                return false;
        }

        function useSlave($a_val = true)
        {
                if (!$this->supportsSlave())
                {
                        return false;
                }
                $this->use_slave = $a_val;
        }

        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);                                                 
                        }
                        /*
                        else
                        {
                                $error = $this->db->errorInfo($a_res->getCode());       
                                
                                $mess = $a_info.
                                        " ### ".$a_res->getMessage().
                                        " ### ".$a_res->getUserInfo();
                                
                                $exception = new $this->exception($a_res->getUserInfo(), $error[0]);
                                
                                if($exception instanceof ilADTDBException)
                                {
                                        // try to find offending column (primary is set AS "PRIMARY")
                                        if($error[0] == MDB2_ERROR_CONSTRAINT && $error[1] == 1062)
                                        {                               
                                                $col = explode("'", $error[2]);
                                                array_pop($col);
                                                $col = array_pop($col);
                                                $exception->setColumn($col);
                                        }
                                }
                                
                                throw $exception;
                        }                                
                        */                                      
                }
                /* :TODO: mysql(i) warnings (experimental, jluetzen)
                else if(DEVMODE && $this instanceof ilDBMySQL)
                {                                                       
                        $j = mysqli_warning_count($this->db->connection);                       
                        if($j > 0) 
                        {                                                               
                                $e = mysqli_get_warnings($this->db->connection);
                                for($i = 0; $i < $j; $i++) 
                                {
                                        trigger_error("MYSQLi warning: "."(".$e->errno.") ".$e->message, E_USER_NOTICE);                                        
                                        $e->next();
                                }                                                               
                        }                                               
                } 
                */

                return $a_res;
        }
        
        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);
                }
        }

        static function isDbError($a_res)
        {
                return MDB2::isError($a_res);
        }

        //
        // Data Definition Methods
        //
        
        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);
        }
        
        
        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();
        }
        
        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.")");
        }
        
        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.")");
        }

        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.")");
        }

        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.")");
        }
        
        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.")");
        }

        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.")");
        }

        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);
                
                $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.")");
        }

        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.")");
        }
        
        function getPrimaryKeyIdentifier()
        {
                return "PRIMARY";
        }
        
        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.")");
        }

        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.")");
        }

        function addFulltextIndex($a_table, $a_fields, $a_name = "in")
        {
                return false;
        }

        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;
                
        }
        
        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.")");
        }

        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.")");
        }

        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.")");
        }
        
        
        function dropSequence($a_table_name)
        {
                $manager = $this->db->loadModule('Manager');
                
                $r = $manager->dropSequence($a_table_name);

                return $this->handleError($r, "dropSequence(".$a_table_name.")");
        }

        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 lenght is 22 bytes.";
                        return false;
                }
                
                return true;
        }
        
        function checkTableColumns($a_cols)
        {
                foreach ($a_cols as $col => $def)
                {
                        if (!$this->checkColumn($col, $def))
                        {
                                return false;
                        }
                }
                
                return true;
        }
        
        function checkColumn($a_col, $a_def)
        {
                if (!$this->checkColumnName($a_col))
                {
                        return false;
                }

                if (!$this->checkColumnDefinition($a_def))
                {
                        return false;
                }
                
                return true;
        }
        
        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;
        }
        
        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 lenght is 30 bytes.";
                        return false;
                }

                return true;
        }

        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 lenght is 3 bytes.";
                        return false;
                }

                return true;
        }

        function getAllowedAttributes()
        {
                return $this->allowed_attributes;
        }
        
        function constraintName($a_table, $a_constraint)
        {
                return $a_constraint;
        }

        static function isReservedWord($a_word)
        {
                include_once("./Services/Database/classes/class.ilDBMySQL.php");
                $mysql_reserved_words = ilDBMySQL::getReservedWords();
                if (in_array(strtoupper($a_word), $mysql_reserved_words))
                {
                        return true;
                }
                include_once("./Services/Database/classes/class.ilDBOracle.php");
                $oracle_reserved_words = ilDBOracle::getReservedWords();
                if (in_array(strtoupper($a_word), $oracle_reserved_words))
                {
                        return true;
                }
                include_once("./Services/Database/classes/class.ilDBPostgreSQL.php");
                $postgres_reserved_words = ilDBPostgreSQL::getReservedWords();
                if (in_array(strtoupper($a_word), $postgres_reserved_words))
                {
                        return true;
                }
        }
        
        //
        // Data query and manupilation functions
        //
        
        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;
        }

        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);
        }
        
        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);
        }

        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>";
                }
        }
        
        function setLimit($a_limit, $a_offset = 0)
        {
                $this->db->setLimit($a_limit, $a_offset);
        }
        
        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.")");
        }

        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.")");
        }

        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.")");
        }

        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.")");
        }

        function execute($a_stmt, $a_data = null)
        {
                $res = $a_stmt->execute($a_data);

                return $this->handleError($res, "execute(".$a_stmt->query.")");
        }

        function executeMultiple($a_stmt, $a_data)
        {
                $res = $this->db->extended->executeMultiple($a_stmt,$a_data);

                return $this->handleError($res, "executeMultiple(".$a_stmt->query.")");
        }
        
        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;
        }
        
        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;
        }

        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;
        }

        function fetchAssoc($a_set)
        {
                return $a_set->fetchRow(DB_FETCHMODE_ASSOC);
        }
        
        function free($a_st)
        {
                return $a_st->free();
        }

        function fetchObject($a_set)
        {
                return $a_set->fetchRow(DB_FETCHMODE_OBJECT);
        }

        function numRows($a_set)
        {
                return $a_set->numRows();
        }

        //
        // function and clauses abstraction
        //
        
        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;
        }
        
        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);
        }
        
        function now()
        {
                return "now()";
        }
        
        
        public function concat($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;
        }
        
        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.") ";
        }
        
        function upper($a_exp)
        {
                return " UPPER(".$a_exp.") ";
        }

        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;
        }
        
        
        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').")";
                        }
                }
        }
        

        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)";
                }
        }
        
        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)";
                }
        }

        function fromUnixtime($a_expr, $a_to_text = true)
        {
                return "FROM_UNIXTIME(".$a_expr.")";
        }
        
        function unixTimestamp()
        {
                return "UNIX_TIMESTAMP()";
        }
        
        function optimizeTable($a_table)
        {
                // needs to be overwritten in DBMS specific class
                // if necessary and possible
        }
        
        //
        // Schema related functions
        //
        
        function tableExists($a_table)
        {
                $tables = $this->listTables();

                if (is_array($tables))
                {
                        if (in_array($a_table, $tables))
                        {
                                return true;
                        }
                }
                return false;
        }
        
        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;
        }

        function uniqueConstraintExists($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 true;
                                }
                        }
                }
                return false;
        }


        function listTables()
        {
                $manager = $this->db->loadModule('Manager');
                $r = $manager->listTables();

                if (!MDB2::isError($r))
                {
                        return $r;
                }
                
                return false;
        }

        function sequenceExists($a_sequence)
        {
                $sequences = $this->listSequences();
                
                if (is_array($sequences))
                {
                        if (in_array($a_sequence, $sequences))
                        {
                                return true;
                        }
                }
                return false;
        }
        
        function listSequences()
        {
                $manager = $this->db->loadModule('Manager');
                $r = $manager->listSequences();

                if (!MDB2::isError($r))
                {
                        return $r;
                }
                
                return false;
        }
        
        
        //
        // Quote Functions
        //
        
        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);
        }
        
        function quoteIdentifier($a_identifier)
        {
                return $this->db->quoteIdentifier($a_identifier);
        }
        

        //
        // Transaction and Locking methods
        //
        
        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()");
        }
        
        function commit()
        {
                $res = $this->db->commit();
                
                return $this->handleError($res, "commit()");
        }

        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.
//
//

        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.
//
//

        function getLastInsertId()
        {
                $res = $this->db->lastInsertId();
                if(MDB2::isError($res))
                {
                        return false;
                }
                return $res;
        }

        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(DB_FETCHMODE_ASSOC);
        
                        return $r[0];
                }
        }

        function getRow($sql,$mode = DB_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
                
        function setSubType($a_value)
        {
                $this->sub_type = (string)$a_value;
        }
        
        function getSubType()
        {
                return $this->sub_type;
        }

} //end Class
?>