[moodle.git] / lib / xmldb / classes / generators / oci8po / oci8po.class.php

<?php // $Id$

///////////////////////////////////////////////////////////////////////////
//                                                                       //
// NOTICE OF COPYRIGHT                                                   //
//                                                                       //
// Moodle - Modular Object-Oriented Dynamic Learning Environment         //
//          http://moodle.com                                            //
//                                                                       //
// Copyright (C) 2001-3001 Martin Dougiamas        http://dougiamas.com  //
//           (C) 2001-3001 Eloy Lafuente (stronk7) http://contiento.com  //
//                                                                       //
// This program is free software; you can redistribute it and/or modify  //
// it under the terms of the GNU General Public License as published by  //
// the Free Software Foundation; either version 2 of the License, or     //
// (at your option) any later version.                                   //
//                                                                       //
// This program is distributed in the hope that it will be useful,       //
// but WITHOUT ANY WARRANTY; without even the implied warranty of        //
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         //
// GNU General Public License for more details:                          //
//                                                                       //
//          http://www.gnu.org/copyleft/gpl.html                         //
//                                                                       //
///////////////////////////////////////////////////////////////////////////

/// This class generate SQL code to be used against Oracle
/// It extends XMLDBgenerator so everything can be
/// overriden as needed to generate correct SQL.

class XMLDBoci8po extends XMLDBgenerator {

/// Only set values that are different from the defaults present in XMLDBgenerator

    var $statement_end = "\n/"; // String to be automatically added at the end of each statement
                                // Using "/" because the standard ";" isn't good for stored procedures (triggers)

    var $number_type = 'NUMBER';    // Proper type for NUMBER(x) in this DB

    var $unsigned_allowed = false;    // To define in the generator must handle unsigned information
    var $default_for_char = ' ';      // To define the default to set for NOT NULLs CHARs without default (null=do nothing)
                                      // Using this whitespace here because Oracle doesn't distinguish empty and null! :-(

    var $drop_default_clause_required = true; //To specify if the generator must use some DEFAULT clause to drop defaults
    var $drop_default_clause = 'NULL'; //The DEFAULT clause required to drop defaults

    var $default_after_null = false;  //To decide if the default clause of each field must go after the null clause

    var $sequence_extra_code = true; //Does the generator need to add extra code to generate the sequence fields
    var $sequence_name = ''; //Particular name for inline sequences in this generator

    var $drop_table_extra_code = true; //Does the generatos need to add code after table drop

    var $enum_inline_code = false; //Does the generator need to add inline code in the column definition

    var $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY (COLUMNSPECS)'; //The SQL template to alter columns

    /**
     * Creates one new XMLDBpostgres7
     */
    function XMLDBoci8po() {
        parent::XMLDBgenerator();
        $this->prefix = '';
        $this->reserved_words = $this->getReservedWords();
    }

    /**
     * Given one XMLDB Type, lenght and decimals, returns the DB proper SQL type
     */
    function getTypeSQL ($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {

        switch ($xmldb_type) {
            case XMLDB_TYPE_INTEGER:    // From http://www.postgresql.org/docs/7.4/interactive/datatype.html
                if (empty($xmldb_length)) {
                    $xmldb_length = 10;
                }
                $dbtype = 'NUMBER(' .  $xmldb_length . ')';
                break;
            case XMLDB_TYPE_NUMBER:
                $dbtype = $this->number_type;
            /// 38 is the max allowed
                if ($xmldb_length > 38) {
                    $xmldb_length = 38;
                }
                if (!empty($xmldb_length)) {
                    $dbtype .= '(' . $xmldb_length;
                    if (!empty($xmldb_decimals)) {
                        $dbtype .= ',' . $xmldb_decimals;
                    }
                    $dbtype .= ')';
                }
                break;
            case XMLDB_TYPE_FLOAT:
                $dbtype = 'NUMBER';
                break;
            case XMLDB_TYPE_CHAR:
                $dbtype = 'VARCHAR2';
                if (empty($xmldb_length)) {
                    $xmldb_length='255';
                }
                $dbtype .= '(' . $xmldb_length . ')';
                break;
            case XMLDB_TYPE_TEXT:
                $dbtype = 'CLOB';
                break;
            case XMLDB_TYPE_BINARY:
                $dbtype = 'BLOB';
                break;
            case XMLDB_TYPE_DATETIME:
                $dbtype = 'DATE';
                break;
        }
        return $dbtype;
    }

    /**
     * Returns the code needed to create one enum for the xmldb_table and xmldb_field passes
     */
    function getEnumExtraSQL ($xmldb_table, $xmldb_field) {

        $sql = 'CONSTRAINT ' . $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'ck');
        $sql.= ' CHECK (' . $this->getEncQuoted($xmldb_field->getName()) . ' IN (' . implode(', ', $xmldb_field->getEnumValues()) . '))';

        return $sql;
    }

    /**
     * Returns the code needed to create one sequence for the xmldb_table and xmldb_field passes
     */
    function getCreateSequenceSQL ($xmldb_table, $xmldb_field) {

        $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');

        $sequence = "CREATE SEQUENCE " . $sequence_name;
        $sequence.= "\n    START WITH 1";
        $sequence.= "\n    INCREMENT BY 1";
        $sequence.= "\n    NOMAXVALUE";

        $trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');

        $trigger = "CREATE TRIGGER " . $trigger_name;
        $trigger.= "\n    BEFORE INSERT";
        $trigger.= "\nON " . $this->getTableName($xmldb_table);
        $trigger.= "\n    FOR EACH ROW";
        $trigger.= "\nBEGIN";
        $trigger.= "\n    IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN';
        $trigger.= "\n        SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;";
        $trigger.= "\n    END IF;";
        $trigger.= "\nEND;";
        return array($sequence, $trigger);
    }

    /**
     * Returns the code needed to drop one sequence for the xmldb_table and xmldb_field passed
     * Can, optionally, specify if the underlying trigger will be also dropped
     */
    function getDropSequenceSQL ($xmldb_table, $xmldb_field, $include_trigger=false) {

        $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');

        $sequence = "DROP SEQUENCE " . $sequence_name;

        $trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');

        $trigger = "DROP TRIGGER " . $trigger_name;

        if ($include_trigger) {
            $result =  array($sequence, $trigger);
        } else {
            $result = array($sequence);
        }
        return $result;
    }

    /**
     * Returns the code (in array) needed to add one comment to the table
     */
    function getCommentSQL ($xmldb_table) {

        $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table);
        $comment.= " IS '" . substr($xmldb_table->getComment(), 0, 250) . "'";

        return array($comment);
    }

    /**
     * Returns the code (array of statements) needed to execute extra statements on table drop
     */
    function getDropTableExtraSQL ($xmldb_table) {
        $xmldb_field = new XMLDBField('id'); // Fields having sequences should be exclusively, id.
        return $this->getDropSequenceSQL($xmldb_table, $xmldb_field, false);
    }

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
     * Oracle has some severe limits:
     *     - clob and blob fields doesn't allow type to be specified
     *     - error is dropped if the null/not null clause is specified and hasn't changed
     *     - changes in precision/decimals of numeric fields drop an ORA-1440 error
     */
    function getAlterFieldSQL($xmldb_table, $xmldb_field) {

        global $db;

        $results = array(); /// To store all the needed SQL commands

    /// Get the quoted name of the table and field
        $tablename = $this->getTableName($xmldb_table);
        $fieldname = $this->getEncQuoted($xmldb_field->getName());

    /// Take a look to field metadata
        $meta = array_change_key_case($db->MetaColumns($tablename));
        $metac = $meta[$fieldname];
        $oldtype = strtolower($metac->type);
        $oldmetatype = column_type($xmldb_table->getName(), $fieldname);
        $oldlength = $metac->max_length;
    /// To calculate the oldlength if the field is numeric, we need to perform one extra query
    /// because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883
        if ($oldmetatype == 'N') {
            $uppertablename = strtoupper($tablename);
            $upperfieldname = strtoupper($fieldname);
            if ($col = get_record_sql("SELECT cname, precision
                                   FROM col
                                   WHERE tname = '$uppertablename'
                                     AND cname = '$upperfieldname'")) {
                $oldlength = $col->precision;
            }
        }
        $olddecimals = empty($metac->scale) ? null : $metac->scale;
        $oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
        $olddefault = empty($metac->default_value) || strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value;

        $typechanged = true;  //By default, assume that the column type has changed
        $precisionchanged = true;  //By default, assume that the column precision has changed
        $decimalchanged = true;  //By default, assume that the column decimal has changed
        $defaultchanged = true;  //By default, assume that the column default has changed
        $notnullchanged = true;  //By default, assume that the column notnull has changed

        $from_temp_fields = false; //By default don't assume we are going to use temporal fields

    /// Detect if we are changing the type of the column
        if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I') ||
            ($xmldb_field->getType() == XMLDB_TYPE_NUMBER  && $oldmetatype == 'N') ||
            ($xmldb_field->getType() == XMLDB_TYPE_FLOAT   && $oldmetatype == 'F') ||
            ($xmldb_field->getType() == XMLDB_TYPE_CHAR    && substr($oldmetatype, 0, 1) == 'C') ||
            ($xmldb_field->getType() == XMLDB_TYPE_TEXT    && substr($oldmetatype, 0, 1) == 'X') ||
            ($xmldb_field->getType() == XMLDB_TYPE_BINARY  && $oldmetatype == 'B')) {
            $typechanged = false;
        } 
    /// Detect if precision has changed
        if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
            ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
            ($oldlength == -1) ||
            ($xmldb_field->getLength() == $oldlength)) {
            $precisionchanged = false;
        }
    /// Detect if decimal has changed
        if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) ||
            ($xmldb_field->getType() == XMLDB_TYPE_CHAR) ||
            ($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
            ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
            (!$xmldb_field->getDecimals()) ||
            (!$olddecimals) ||
            ($xmldb_field->getDecimals() == $olddecimals)) {
            $decimalchanged = false;
        }
    /// Detect if we are changing the default
        if (($xmldb_field->getDefault() === null && $olddefault === null) ||
            ($xmldb_field->getDefault() === $olddefault) ||             //Check both equality and
            ("'" . $xmldb_field->getDefault() . "'" === $olddefault)) {  //Equality with quotes because ADOdb returns the default with quotes
            $defaultchanged = false;
        }

    /// Detect if we are changing the nullability
        if (($xmldb_field->getNotnull() === $oldnotnull)) {
            $notnullchanged = false;
        }

    /// If type has changed or precision or decimal has changed and we are in one numeric field
    ///     - create one temp column with the new specs
    ///     - fill the new column with the values from the old one
    ///     - drop the old column
    ///     - rename the temp column to the original name
        if (($typechanged) || ($oldmetatype == 'N' && ($precisionchanged || $decimalchanged))) {
            $tempcolname = $xmldb_field->getName() . '_alter_column_tmp';
        /// Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints
            $this->alter_column_skip_notnull = true;
            $this->alter_column_skip_default = true;
            $xmldb_field->setName($tempcolname);
        /// Create the temporal column
            $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field));
        /// Copy contents from original col to the temporal one
            $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname;
        /// Drop the old column
            $xmldb_field->setName($fieldname); //Set back the original field name
            $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
        /// Rename the temp column to the original one
            $results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname;
        /// Mark we have performed one change based in temp fields
            $from_temp_fields = true;
        /// Re-enable the notnull and default sections so the general AlterFieldSQL can use it
            $this->alter_column_skip_notnull = false;
            $this->alter_column_skip_default = false;
        /// Dissable the type section because we have done it with the temp field
            $this->alter_column_skip_type = true;
        /// If new field is nullable, nullability hasn't changed
            if (!$xmldb_field->getNotnull()) {
                $notnullchanged = false;
            }
        /// If new field hasn't default, default hasn't changed
            if ($xmldb_field->getDefault() === null) {
                $defaultchanged = false;
            }
        }

    /// If type and precision and decimals hasn't changed, prevent the type clause
        if (!$typechanged && !$precisionchanged && !$decimalchanged) {
            $this->alter_column_skip_type = true;
        }

    /// If NULL/NOT NULL hasn't changed
    /// prevent null clause to be specified
        if (!$notnullchanged) {
            $this->alter_column_skip_notnull = true; /// Initially, prevent the notnull clause
        /// But, if we have used the temp field and the new field is not null, then enforce the not null clause
            if ($from_temp_fields &&  $xmldb_field->getNotnull()) {
                $this->alter_column_skip_notnull = false;
            }
        }
    /// If default hasn't changed
    /// prevent default clause to be specified
        if (!$defaultchanged) {
            $this->alter_column_skip_default = true; /// Initially, prevent the default clause
        /// But, if we have used the temp field and the new field has default clause, then enforce the default clause
            if ($from_temp_fields && $default_clause = $this->getDefaultClause($xmldb_field)) {
                $this->alter_column_skip_default = false;
            }
        }

    /// If arriving here, something is not being skiped (type, notnull, default), calculate the standar AlterFieldSQL
        if (!$this->alter_column_skip_type || !$this->alter_column_skip_notnull || !$this->alter_column_skip_default) {
            $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field));
            return $results;
        }

    /// Finally return results
        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its enum 
     * (usually invoked from getModifyEnumSQL()
     */
    function getCreateEnumSQL($xmldb_table, $xmldb_field) {
    /// All we have to do is to create the check constraint
        return array('ALTER TABLE ' . $this->getTableName($xmldb_table) . 
                     ' ADD ' . $this->getEnumExtraSQL($xmldb_table, $xmldb_field));
    }
                                                       
    /**     
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its enum 
     * (usually invoked from getModifyEnumSQL()
     */         
    function getDropEnumSQL($xmldb_table, $xmldb_field) {
    /// All we have to do is to drop the check constraint
        return array('ALTER TABLE ' . $this->getTableName($xmldb_table) . 
                     ' DROP CONSTRAINT ' . $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'ck'));
    }   

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its default 
     * (usually invoked from getModifyDefaultSQL()
     */
    function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
    /// Just a wrapper over the getAlterFieldSQL() function for Oracle that
    /// is capable of handling defaults
        return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
    }
                                                       
    /**     
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its default 
     * (usually invoked from getModifyDefaultSQL()
     */         
    function getDropDefaultSQL($xmldb_table, $xmldb_field) {
    /// Just a wrapper over the getAlterFieldSQL() function for Oracle that
    /// is capable of handling defaults
        return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
    }   

    /**
     * Returns an array of reserved words (lowercase) for this DB
     */
    function getReservedWords() {
    /// This file contains the reserved words for Oracle databases
    /// from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm
        $reserved_words = 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'
        );
        return $reserved_words;
    }
}

?>
Commit	Line	Data
d7444bfc	1	<?php // $Id$
	2
	3	///////////////////////////////////////////////////////////////////////////
	4	// //
	5	// NOTICE OF COPYRIGHT //
	6	// //
	7	// Moodle - Modular Object-Oriented Dynamic Learning Environment //
	8	// http://moodle.com //
	9	// //
	10	// Copyright (C) 2001-3001 Martin Dougiamas http://dougiamas.com //
	11	// (C) 2001-3001 Eloy Lafuente (stronk7) http://contiento.com //
	12	// //
	13	// This program is free software; you can redistribute it and/or modify //
	14	// it under the terms of the GNU General Public License as published by //
	15	// the Free Software Foundation; either version 2 of the License, or //
	16	// (at your option) any later version. //
	17	// //
	18	// This program is distributed in the hope that it will be useful, //
	19	// but WITHOUT ANY WARRANTY; without even the implied warranty of //
	20	// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the //
	21	// GNU General Public License for more details: //
	22	// //
	23	// http://www.gnu.org/copyleft/gpl.html //
	24	// //
	25	///////////////////////////////////////////////////////////////////////////
	26
	27	/// This class generate SQL code to be used against Oracle
	28	/// It extends XMLDBgenerator so everything can be
	29	/// overriden as needed to generate correct SQL.
	30
	31	class XMLDBoci8po extends XMLDBgenerator {
	32
	33	/// Only set values that are different from the defaults present in XMLDBgenerator
	34
0513f3bf	35	var $statement_end = "\n/"; // String to be automatically added at the end of each statement
	36	// Using "/" because the standard ";" isn't good for stored procedures (triggers)
	37
d7444bfc	38	var $number_type = 'NUMBER'; // Proper type for NUMBER(x) in this DB
	39
	40	var $unsigned_allowed = false; // To define in the generator must handle unsigned information
2efaf3f8	41	var $default_for_char = ' '; // To define the default to set for NOT NULLs CHARs without default (null=do nothing)
2efaf3f8	42	// Using this whitespace here because Oracle doesn't distinguish empty and null! :-(
d7444bfc	43
812e363a	44	var $drop_default_clause_required = true; //To specify if the generator must use some DEFAULT clause to drop defaults
	45	var $drop_default_clause = 'NULL'; //The DEFAULT clause required to drop defaults
	46
f075bac4	47	var $default_after_null = false; //To decide if the default clause of each field must go after the null clause
f075bac4	48
d7444bfc	49	var $sequence_extra_code = true; //Does the generator need to add extra code to generate the sequence fields
	50	var $sequence_name = ''; //Particular name for inline sequences in this generator
	51
3a8c55c3	52	var $drop_table_extra_code = true; //Does the generatos need to add code after table drop
3a8c55c3	53
d7444bfc	54	var $enum_inline_code = false; //Does the generator need to add inline code in the column definition
d7444bfc	55
19c8321e	56	var $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY (COLUMNSPECS)'; //The SQL template to alter columns
19c8321e	57
d7444bfc	58	/**
	59	* Creates one new XMLDBpostgres7
	60	*/
	61	function XMLDBoci8po() {
	62	parent::XMLDBgenerator();
	63	$this->prefix = '';
	64	$this->reserved_words = $this->getReservedWords();
	65	}
	66
	67	/**
	68	* Given one XMLDB Type, lenght and decimals, returns the DB proper SQL type
	69	*/
	70	function getTypeSQL ($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
	71
	72	switch ($xmldb_type) {
	73	case XMLDB_TYPE_INTEGER: // From http://www.postgresql.org/docs/7.4/interactive/datatype.html
	74	if (empty($xmldb_length)) {
	75	$xmldb_length = 10;
	76	}
	77	$dbtype = 'NUMBER(' . $xmldb_length . ')';
	78	break;
	79	case XMLDB_TYPE_NUMBER:
	80	$dbtype = $this->number_type;
4782a1f8	81	/// 38 is the max allowed
	82	if ($xmldb_length > 38) {
	83	$xmldb_length = 38;
	84	}
d7444bfc	85	if (!empty($xmldb_length)) {
	86	$dbtype .= '(' . $xmldb_length;
	87	if (!empty($xmldb_decimals)) {
	88	$dbtype .= ',' . $xmldb_decimals;
	89	}
	90	$dbtype .= ')';
	91	}
	92	break;
	93	case XMLDB_TYPE_FLOAT:
	94	$dbtype = 'NUMBER';
	95	break;
	96	case XMLDB_TYPE_CHAR:
	97	$dbtype = 'VARCHAR2';
	98	if (empty($xmldb_length)) {
	99	$xmldb_length='255';
	100	}
	101	$dbtype .= '(' . $xmldb_length . ')';
	102	break;
	103	case XMLDB_TYPE_TEXT:
	104	$dbtype = 'CLOB';
	105	break;
	106	case XMLDB_TYPE_BINARY:
	107	$dbtype = 'BLOB';
	108	break;
	109	case XMLDB_TYPE_DATETIME:
	110	$dbtype = 'DATE';
	111	break;
	112	}
	113	return $dbtype;
	114	}
	115
eef868d1	116	/**
d7444bfc	117	* Returns the code needed to create one enum for the xmldb_table and xmldb_field passes
eef868d1	118	*/
d7444bfc	119	function getEnumExtraSQL ($xmldb_table, $xmldb_field) {
eef868d1	120
d7444bfc	121	$sql = 'CONSTRAINT ' . $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'ck');
0dd87cfa	122	$sql.= ' CHECK (' . $this->getEncQuoted($xmldb_field->getName()) . ' IN (' . implode(', ', $xmldb_field->getEnumValues()) . '))';
d7444bfc	123
	124	return $sql;
	125	}
	126
	127	/**
	128	* Returns the code needed to create one sequence for the xmldb_table and xmldb_field passes
	129	*/
	130	function getCreateSequenceSQL ($xmldb_table, $xmldb_field) {
	131
465a8029	132	$sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
eef868d1	133
465a8029	134	$sequence = "CREATE SEQUENCE " . $sequence_name;
d7444bfc	135	$sequence.= "\n START WITH 1";
d7444bfc	136	$sequence.= "\n INCREMENT BY 1";
9dcc6300	137	$sequence.= "\n NOMAXVALUE";
d7444bfc	138
d7444bfc	139	$trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
eef868d1	140
1d5071a5	141	$trigger = "CREATE TRIGGER " . $trigger_name;
d7444bfc	142	$trigger.= "\n BEFORE INSERT";
9af19c72	143	$trigger.= "\nON " . $this->getTableName($xmldb_table);
d7444bfc	144	$trigger.= "\n FOR EACH ROW";
d7444bfc	145	$trigger.= "\nBEGIN";
4782a1f8	146	$trigger.= "\n IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN';
b8851b80	147	$trigger.= "\n SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;";
b8851b80	148	$trigger.= "\n END IF;";
0513f3bf	149	$trigger.= "\nEND;";
9dcc6300	150	return array($sequence, $trigger);
d7444bfc	151	}
d7444bfc	152
3a8c55c3	153	/**
	154	* Returns the code needed to drop one sequence for the xmldb_table and xmldb_field passed
	155	* Can, optionally, specify if the underlying trigger will be also dropped
	156	*/
	157	function getDropSequenceSQL ($xmldb_table, $xmldb_field, $include_trigger=false) {
	158
	159	$sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
eef868d1	160
3a8c55c3	161	$sequence = "DROP SEQUENCE " . $sequence_name;
	162
	163	$trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
eef868d1	164
3a8c55c3	165	$trigger = "DROP TRIGGER " . $trigger_name;
	166
	167	if ($include_trigger) {
	168	$result = array($sequence, $trigger);
	169	} else {
	170	$result = array($sequence);
	171	}
	172	return $result;
	173	}
	174
	175	/**
	176	* Returns the code (in array) needed to add one comment to the table
	177	*/
	178	function getCommentSQL ($xmldb_table) {
	179
9af19c72	180	$comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table);
3a8c55c3	181	$comment.= " IS '" . substr($xmldb_table->getComment(), 0, 250) . "'";
d7444bfc	182
3a8c55c3	183	return array($comment);
3a8c55c3	184	}
d7444bfc	185
3a8c55c3	186	/**
	187	* Returns the code (array of statements) needed to execute extra statements on table drop
	188	*/
	189	function getDropTableExtraSQL ($xmldb_table) {
	190	$xmldb_field = new XMLDBField('id'); // Fields having sequences should be exclusively, id.
	191	return $this->getDropSequenceSQL($xmldb_table, $xmldb_field, false);
	192	}
d7444bfc	193
19c8321e	194	/**
	195	* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
	196	* Oracle has some severe limits:
	197	* - clob and blob fields doesn't allow type to be specified
	198	* - error is dropped if the null/not null clause is specified and hasn't changed
11b75afe	199	* - changes in precision/decimals of numeric fields drop an ORA-1440 error
19c8321e	200	*/
	201	function getAlterFieldSQL($xmldb_table, $xmldb_field) {
	202
11b75afe	203	global $db;
	204
	205	$results = array(); /// To store all the needed SQL commands
	206
	207	/// Get the quoted name of the table and field
9af19c72	208	$tablename = $this->getTableName($xmldb_table);
11b75afe	209	$fieldname = $this->getEncQuoted($xmldb_field->getName());
	210
	211	/// Take a look to field metadata
	212	$meta = array_change_key_case($db->MetaColumns($tablename));
	213	$metac = $meta[$fieldname];
	214	$oldtype = strtolower($metac->type);
	215	$oldmetatype = column_type($xmldb_table->getName(), $fieldname);
	216	$oldlength = $metac->max_length;
	217	/// To calculate the oldlength if the field is numeric, we need to perform one extra query
	218	/// because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883
	219	if ($oldmetatype == 'N') {
	220	$uppertablename = strtoupper($tablename);
	221	$upperfieldname = strtoupper($fieldname);
	222	if ($col = get_record_sql("SELECT cname, precision
	223	FROM col
	224	WHERE tname = '$uppertablename'
	225	AND cname = '$upperfieldname'")) {
	226	$oldlength = $col->precision;
	227	}
	228	}
	229	$olddecimals = empty($metac->scale) ? null : $metac->scale;
	230	$oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
812e363a	231	$olddefault = empty($metac->default_value) \|\| strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value;
11b75afe	232
	233	$typechanged = true; //By default, assume that the column type has changed
	234	$precisionchanged = true; //By default, assume that the column precision has changed
	235	$decimalchanged = true; //By default, assume that the column decimal has changed
	236	$defaultchanged = true; //By default, assume that the column default has changed
	237	$notnullchanged = true; //By default, assume that the column notnull has changed
	238
	239	$from_temp_fields = false; //By default don't assume we are going to use temporal fields
	240
	241	/// Detect if we are changing the type of the column
	242	if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I') \|\|
	243	($xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N') \|\|
	244	($xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F') \|\|
	245	($xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C') \|\|
	246	($xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == 'X') \|\|
	247	($xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B')) {
	248	$typechanged = false;
	249	}
	250	/// Detect if precision has changed
	251	if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) \|\|
	252	($xmldb_field->getType() == XMLDB_TYPE_BINARY) \|\|
	253	($oldlength == -1) \|\|
	254	($xmldb_field->getLength() == $oldlength)) {
	255	$precisionchanged = false;
	256	}
	257	/// Detect if decimal has changed
	258	if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) \|\|
	259	($xmldb_field->getType() == XMLDB_TYPE_CHAR) \|\|
	260	($xmldb_field->getType() == XMLDB_TYPE_TEXT) \|\|
	261	($xmldb_field->getType() == XMLDB_TYPE_BINARY) \|\|
	262	(!$xmldb_field->getDecimals()) \|\|
	263	(!$olddecimals) \|\|
	264	($xmldb_field->getDecimals() == $olddecimals)) {
	265	$decimalchanged = false;
	266	}
	267	/// Detect if we are changing the default
	268	if (($xmldb_field->getDefault() === null && $olddefault === null) \|\|
	269	($xmldb_field->getDefault() === $olddefault) \|\| //Check both equality and
	270	("'" . $xmldb_field->getDefault() . "'" === $olddefault)) { //Equality with quotes because ADOdb returns the default with quotes
	271	$defaultchanged = false;
	272	}
812e363a	273
11b75afe	274	/// Detect if we are changing the nullability
	275	if (($xmldb_field->getNotnull() === $oldnotnull)) {
	276	$notnullchanged = false;
	277	}
	278
	279	/// If type has changed or precision or decimal has changed and we are in one numeric field
	280	/// - create one temp column with the new specs
	281	/// - fill the new column with the values from the old one
	282	/// - drop the old column
	283	/// - rename the temp column to the original name
	284	if (($typechanged) \|\| ($oldmetatype == 'N' && ($precisionchanged \|\| $decimalchanged))) {
	285	$tempcolname = $xmldb_field->getName() . '_alter_column_tmp';
	286	/// Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints
	287	$this->alter_column_skip_notnull = true;
	288	$this->alter_column_skip_default = true;
	289	$xmldb_field->setName($tempcolname);
	290	/// Create the temporal column
	291	$results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field));
	292	/// Copy contents from original col to the temporal one
	293	$results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname;
	294	/// Drop the old column
	295	$xmldb_field->setName($fieldname); //Set back the original field name
	296	$results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
	297	/// Rename the temp column to the original one
	298	$results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname;
	299	/// Mark we have performed one change based in temp fields
	300	$from_temp_fields = true;
	301	/// Re-enable the notnull and default sections so the general AlterFieldSQL can use it
	302	$this->alter_column_skip_notnull = false;
	303	$this->alter_column_skip_default = false;
812e363a	304	/// Dissable the type section because we have done it with the temp field
11b75afe	305	$this->alter_column_skip_type = true;
599caff8	306	/// If new field is nullable, nullability hasn't changed
	307	if (!$xmldb_field->getNotnull()) {
	308	$notnullchanged = false;
	309	}
	310	/// If new field hasn't default, default hasn't changed
	311	if ($xmldb_field->getDefault() === null) {
	312	$defaultchanged = false;
	313	}
11b75afe	314	}
	315
	316	/// If type and precision and decimals hasn't changed, prevent the type clause
	317	if (!$typechanged && !$precisionchanged && !$decimalchanged) {
	318	$this->alter_column_skip_type = true;
	319	}
	320
	321	/// If NULL/NOT NULL hasn't changed
	322	/// prevent null clause to be specified
	323	if (!$notnullchanged) {
	324	$this->alter_column_skip_notnull = true; /// Initially, prevent the notnull clause
	325	/// But, if we have used the temp field and the new field is not null, then enforce the not null clause
	326	if ($from_temp_fields && $xmldb_field->getNotnull()) {
	327	$this->alter_column_skip_notnull = false;
	328	}
	329	}
	330	/// If default hasn't changed
	331	/// prevent default clause to be specified
	332	if (!$defaultchanged) {
	333	$this->alter_column_skip_default = true; /// Initially, prevent the default clause
	334	/// But, if we have used the temp field and the new field has default clause, then enforce the default clause
	335	if ($from_temp_fields && $default_clause = $this->getDefaultClause($xmldb_field)) {
	336	$this->alter_column_skip_default = false;
	337	}
	338	}
	339
	340	/// If arriving here, something is not being skiped (type, notnull, default), calculate the standar AlterFieldSQL
	341	if (!$this->alter_column_skip_type \|\| !$this->alter_column_skip_notnull \|\| !$this->alter_column_skip_default) {
	342	$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field));
	343	return $results;
	344	}
	345
	346	/// Finally return results
	347	return $results;
19c8321e	348	}
19c8321e	349
b899d9bf	350	/**
	351	* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its enum
	352	* (usually invoked from getModifyEnumSQL()
	353	*/
	354	function getCreateEnumSQL($xmldb_table, $xmldb_field) {
	355	/// All we have to do is to create the check constraint
	356	return array('ALTER TABLE ' . $this->getTableName($xmldb_table) .
	357	' ADD ' . $this->getEnumExtraSQL($xmldb_table, $xmldb_field));
	358	}
	359
	360	/**
	361	* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its enum
	362	* (usually invoked from getModifyEnumSQL()
	363	*/
	364	function getDropEnumSQL($xmldb_table, $xmldb_field) {
	365	/// All we have to do is to drop the check constraint
	366	return array('ALTER TABLE ' . $this->getTableName($xmldb_table) .
	367	' DROP CONSTRAINT ' . $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'ck'));
	368	}
	369
812e363a	370	/**
	371	* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its default
	372	* (usually invoked from getModifyDefaultSQL()
	373	*/
	374	function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
	375	/// Just a wrapper over the getAlterFieldSQL() function for Oracle that
	376	/// is capable of handling defaults
	377	return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
	378	}
	379
	380	/**
	381	* Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its default
	382	* (usually invoked from getModifyDefaultSQL()
	383	*/
	384	function getDropDefaultSQL($xmldb_table, $xmldb_field) {
	385	/// Just a wrapper over the getAlterFieldSQL() function for Oracle that
	386	/// is capable of handling defaults
	387	return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
	388	}
	389
d7444bfc	390	/**
	391	* Returns an array of reserved words (lowercase) for this DB
	392	*/
	393	function getReservedWords() {
6aa7885e	394	/// This file contains the reserved words for Oracle databases
6aa7885e	395	/// from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm
d7444bfc	396	$reserved_words = array (
	397	'access', 'add', 'all', 'alter', 'and', 'any',
	398	'as', 'asc', 'audit', 'between', 'by', 'char',
	399	'check', 'cluster', 'column', 'comment',
	400	'compress', 'connect', 'create', 'current',
	401	'date', 'decimal', 'default', 'delete', 'desc',
	402	'distinct', 'drop', 'else', 'exclusive', 'exists',
	403	'file', 'float', 'for', 'from', 'grant', 'group',
	404	'having', 'identified', 'immediate', 'in',
	405	'increment', 'index', 'initial', 'insert',
	406	'integer', 'intersect', 'into', 'is', 'level',
	407	'like', 'lock', 'long', 'maxextents', 'minus',
	408	'mlslabel', 'mode', 'modify', 'noaudit',
	409	'nocompress', 'not', 'nowait', 'null', 'number',
	410	'of', 'offline', 'on', 'online', 'option', 'or',
	411	'order', 'pctfree', 'prior', 'privileges',
	412	'public', 'raw', 'rename', 'resource', 'revoke',
	413	'row', 'rowid', 'rownum', 'rows', 'select',
	414	'session', 'set', 'share', 'size', 'smallint',
	415	'start', 'successful', 'synonym', 'sysdate',
	416	'table', 'then', 'to', 'trigger', 'uid', 'union',
	417	'unique', 'update', 'user', 'validate', 'values',
	418	'varchar', 'varchar2', 'view', 'whenever',
	419	'where', 'with'
eef868d1	420	);
d7444bfc	421	return $reserved_words;
	422	}
	423	}
	424
	425	?>