class.ilDBOracle.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */


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

class ilDBOracle extends ilDB
{
    const CLOB_BUFFER_SIZE = 2000;
    const LIMIT_EXPRESSIONS_IN_LIST = 1000;


    public function loadModule($module)
    {
        // TODO: Implement loadModule() method.
    }


    public function getDSN()
    {
        // TODO: check if there is another solution.
        // This works with 11g
        if (!isset($GLOBALS['_MDB2_dsninfo_default']['charset']) or
            $GLOBALS['_MDB2_dsninfo_default']['charset'] != 'utf8') {
            $GLOBALS['_MDB2_dsninfo_default']['charset'] = 'utf8';
        }

        return array('phptype' => 'oci8',
                        'hostspec' => $this->getDBHost(),
                        'username' => $this->getDBUser(),
                        'password' => $this->getDBPassword(),
                        'port' => $this->getDBPort(),
                        'service' => $this->getDBName()
                        );
        
        //return "oci8://".$this->getDBUser().":".$this->getDBPassword()."@".
        //      $this->getDBHost()."/?service=".$this->getDBName();
    }

    public function getDBType()
    {
        return "oracle";
    }
    
    public function getDBVersion()
    {
        $query = 'SELECT * FROM v$version';
        $res = $this->db->query($query);
        
        if (MDB2::isError($res)) {
            return parent::getDBVersion();
        }
        $row = $res->fetchRow(ilDBConstants::FETCHMODE_ASSOC);
        return isset($row['banner']) ? $row['banner'] : parent::getDBVersion();
    }
    
    public static function getReservedWords()
    {
        // version: 10g
        // url: http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_keywd.htm#g691972
        return array(
            "ACCESS", "ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC",
            "AUDIT", "BETWEEN", "BY", "CHAR", "CHECK", "CLUSTER", "COLUMN",
            "COMMENT", "COMPRESS", "CONNECT", "CREATE", "CURRENT", "DATE",
            "DECIMAL", "DEFAULT", "DELETE", "DESC", "DISTINCT", "DROP", "ELSE",
            "EXCLUSIVE", "EXISTS", "FILE", "FLOAT", "FOR", "FROM", "GRANT", "GROUP",
            "HAVING", "IDENTIFIED", "IMMEDIATE", "IN", "INCREMENT", "INDEX", "INITIAL",
            "INSERT", "INTEGER", "INTERSECT", "INTO", "IS", "LEVEL", "LIKE", "LOCK", "LONG",
            "MAXEXTENTS", "MINUS", "MLSLABEL", "MODE", "MODIFY", "NOAUDIT", "NOCOMPRESS", "NOT",
            "NOWAIT", "NULL", "NUMBER", "OF", "OFFLINE", "ON", "ONLINE","OPTION",
            "OR", "ORDER", "PCTFREE", "PRIOR", "PRIVILEGES", "PUBLIC", "RAW", "RENAME",
            "RESOURCE", "REVOKE", "ROW", "ROWID", "ROWNUM", "ROWS", "SELECT", "SESSION", "SET",
            "SHARE", "SIZE", "SMALLINT", "START", "SUCCESSFUL", "SYNONYM", "SYSDATE","TABLE",
            "THEN", "TO", "TRIGGER", "UID", "UNION", "UNIQUE", "UPDATE", "USER","VALIDATE",
            "VALUES", "VARCHAR", "VARCHAR2", "VIEW", "WHENEVER", "WHERE", "WITH"
        );
    }

    public function doConnect()
    {
        $this->db = MDB2::connect(
            $this->getDSN(),
            array("use_transactions" => true)
        );
    }

    public function initConnection()
    {
        $GLOBALS['_MDB2_dsninfo_default']['charset'] = 'utf8';
        $this->query("ALTER SESSION SET nls_length_semantics='CHAR'");
        $this->query("ALTER SESSION SET NLS_SORT = binary_ci");
    }

    /*  function manipulate($sql)
        {
    //echo "1";
    //if (!is_int(strpos($sql, "frm_thread_access")))
    //{
    //echo "2";
            return parent::manipulate($sql);
    //}
    //echo "3";
        }*/

    public function now()
    {
        return "LOCALTIMESTAMP";
    }
    
    public function fromUnixtime($a_expr, $a_to_text = true)
    {
        if (!$a_to_text) {
            return "TO_DATE('19700101000000','YYYYMMDDHH24MISS') " .
                "+ NUMTODSINTERVAL(" . $a_expr . ", 'SECOND')";
        } else {
            return "to_char(TO_DATE('19700101000000','YYYYMMDDHH24MISS') " .
                "+ NUMTODSINTERVAL(" . $a_expr . ", 'SECOND'), 'yyyy-mm-dd hh24:mi:ss')";
        }
    }

    public function unixTimestamp()
    {
        // sysdate should respect database time zone
        // current_date would respect session time zone
        return "ROUND((sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))";
    }

    public function constraintName($a_table, $a_constraint)
    {
        return $a_table . "_" . $a_constraint;
    }

    public function getPrimaryKeyIdentifier()
    {
        return "pk";
    }

    public function supportsFulltext()
    {
        return false;
    }

    public function replace($a_table, $a_pk_columns, $a_other_columns)
    {
        $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();
        $val_field = array();
        $a = array();
        $b = array();
        foreach ($a_columns as $k => $col) {
            if ($col[0] == 'clob' or $col[0] == 'blob') {
                $val_field[] = $this->quote($col[1], 'text') . " " . $k;
            } else {
                $val_field[] = $this->quote($col[1], $col[0]) . " " . $k;
            }
            $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;
            }
            $a[] = "a." . $k;
            $b[] = "b." . $k;
        }
        $abpk = array();
        $aboc = array();
        $delwhere = array();
        foreach ($a_pk_columns as $k => $col) {
            $abpk[] = "a." . $k . " = b." . $k;
            $delwhere[] = $k . " = " . $this->quote($col[1], $col[0]);
        }
        foreach ($a_other_columns as $k => $col) {
            $aboc[] = "a." . $k . " = b." . $k;
        }
        if ($lobs) {    // delete/insert
            $this->manipulate(
                "DELETE FROM " . $a_table . " WHERE " .
                implode($delwhere, " AND ")
            );
            $this->insert($a_table, $a_columns);
            
            //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
            $this->handleError($r, "replace, delete/insert(" . $a_table . ")");
        } else {        // if no lobs are used, use manipulate
            $q = "MERGE INTO " . $a_table . " a " .
                "USING (SELECT " . implode($val_field, ", ") . " " .
                "FROM DUAL) b ON (" . implode($abpk, " AND ") . ") " .
                "WHEN MATCHED THEN UPDATE SET " . implode($aboc, ", ") . " " .
                "WHEN NOT MATCHED THEN INSERT (" . implode($a, ",") . ") VALUES (" . implode($b, ",") . ")";
            $r = $this->manipulate($q);
        }
        return $r;
    }
    
    public function locate($a_needle, $a_string, $a_start_pos = 1)
    {
        $locate = ' INSTR( ';
        $locate .= ('SUBSTR(' . $a_string . ',0,' . self::CLOB_BUFFER_SIZE . ')');
        $locate .= ',';
        $locate .= $a_needle;
        $locate .= ',';
        $locate .= $a_start_pos;
        $locate .= ') ';
        return $locate;
    }
    
    public function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
    {
        if ($a_type == 'text') {
            return parent::like($a_col, $a_type, $a_value, $case_insensitive);
        }

        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(SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ")) LIKE(UPPER(?))";
            } else {
                return "SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ") LIKE(?)";
            }
        } else {
            if ($case_insensitive) {
                return " UPPER(SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ")) LIKE(UPPER(" . $this->quote($a_value, 'text') . "))";
            } else {
                return " SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ") LIKE(" . $this->quote($a_value, 'text') . ")";
            }
        }
    }
    
    public function concat(array $a_values, $a_allow_null = true)
    {
        if (count($a_values) <= 2) {
            return parent::concat($a_values, false);
        }
        
        $first = true;
        foreach ($a_values as $field_info) {
            if (!$first) {
                $concat_value = parent::concat(
                    array(
                        array($concat_value,$concat_type),
                        array($field_info[0],$field_info[1])),
                    false
                );
            } else {
                $first = false;
                $concat_value = $field_info[0];
                $concat_type = $field_info[1];
            }
        }
        return $concat_value;
    }
    
    public function in($a_field, $a_values, $negate = false, $a_type = "")
    {
        if (count($a_values) <= self::LIMIT_EXPRESSIONS_IN_LIST) {
            return parent::in($a_field, $a_values, $negate, $a_type);
        }
        
        $first = true;
        $concat = $negate ? ' AND ' : ' OR ';
        $in = '(';
        do {
            if (!$first) {
                $in .= $concat;
            }
            $first = false;
            
            $spliced = array_splice($a_values, 0, self::LIMIT_EXPRESSIONS_IN_LIST);
            $in .= parent::in($a_field, $spliced, $negate, $a_type);
        } while ($a_values);

        return $in . " ) ";
    }
    
    
    public function lockTables($a_tables)
    {
        global $ilLog;
        
        $locks = array();

        $counter = 0;
        foreach ($a_tables as $table) {
            $lock = 'LOCK TABLE ';

            $lock .= ($table['name'] . ' ');

            switch ($table['type']) {
                case ilDBConstants::LOCK_READ:
                    $lock .= ' IN SHARE MODE ';
                    break;
                
                case ilDBConstants::LOCK_WRITE:
                    $lock .= ' IN EXCLUSIVE MODE ';
                    break;
            }
            
            $locks[] = $lock;
        }

        // @TODO use and store a unique identifier to allow nested lock/unlocks
        $this->db->beginTransaction();
        foreach ($locks as $lock) {
            $this->db->query($lock);
            $ilLog->write(__METHOD__ . ': ' . $lock);
        }
        return true;
    }

    public function unlockTables()
    {
        $this->db->commit();
    }
    
    public function dropPrimaryKey($a_table)
    {
        // oracle 10: dropping constraint may not drop attached index
        // http://www.orafaq.com/forum/t/59807/0/
        
        $query = "ALTER TABLE " . $a_table . " DROP PRIMARY KEY DROP INDEX";
        $res = $this->db->query($query);
        
        return $this->handleError($res, "dropPrimaryKey(" . $a_table . ")");
    }


    public function setStorageEngine($storage_engine)
    {
        unset($storage_engine);
    }


    public function getStorageEngine()
    {
        return null;
    }


    public function dropFulltextIndex($a_table, $a_name)
    {
        return false;
    }
}