[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 $default_after_null = false;  //To decide if the default clause of each field must go after the null clause

    var $foreign_keys = false; // Does the generator build foreign keys

    var $primary_index = false;// Does the generator need to build one index for primary keys
    var $unique_index = false;  // Does the generator need to build one index for unique keys
    var $foreign_index = true; // Does the generator need to build one index for foreign keys

    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

    /**
     * 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 OR REPLACE TRIGGER " . $trigger_name;
        $trigger.= "\n    BEFORE INSERT";
        $trigger.= "\nON " . $this->getEncQuoted($this->prefix . $xmldb_table->getName());
        $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->getEncQuoted($this->prefix . $xmldb_table->getName());
        $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);
    }

    /**
     * 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
f075bac4	44	var $default_after_null = false; //To decide if the default clause of each field must go after the null clause
f075bac4	45
d7444bfc	46	var $foreign_keys = false; // Does the generator build foreign keys
	47
	48	var $primary_index = false;// Does the generator need to build one index for primary keys
1bf63d33	49	var $unique_index = false; // Does the generator need to build one index for unique keys
d7444bfc	50	var $foreign_index = true; // Does the generator need to build one index for foreign keys
	51
	52	var $sequence_extra_code = true; //Does the generator need to add extra code to generate the sequence fields
	53	var $sequence_name = ''; //Particular name for inline sequences in this generator
	54
3a8c55c3	55	var $drop_table_extra_code = true; //Does the generatos need to add code after table drop
3a8c55c3	56
d7444bfc	57	var $enum_inline_code = false; //Does the generator need to add inline code in the column definition
	58
	59	/**
	60	* Creates one new XMLDBpostgres7
	61	*/
	62	function XMLDBoci8po() {
	63	parent::XMLDBgenerator();
	64	$this->prefix = '';
	65	$this->reserved_words = $this->getReservedWords();
	66	}
	67
	68	/**
	69	* Given one XMLDB Type, lenght and decimals, returns the DB proper SQL type
	70	*/
	71	function getTypeSQL ($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
	72
	73	switch ($xmldb_type) {
	74	case XMLDB_TYPE_INTEGER: // From http://www.postgresql.org/docs/7.4/interactive/datatype.html
	75	if (empty($xmldb_length)) {
	76	$xmldb_length = 10;
	77	}
	78	$dbtype = 'NUMBER(' . $xmldb_length . ')';
	79	break;
	80	case XMLDB_TYPE_NUMBER:
	81	$dbtype = $this->number_type;
4782a1f8	82	/// 38 is the max allowed
	83	if ($xmldb_length > 38) {
	84	$xmldb_length = 38;
	85	}
d7444bfc	86	if (!empty($xmldb_length)) {
	87	$dbtype .= '(' . $xmldb_length;
	88	if (!empty($xmldb_decimals)) {
	89	$dbtype .= ',' . $xmldb_decimals;
	90	}
	91	$dbtype .= ')';
	92	}
	93	break;
	94	case XMLDB_TYPE_FLOAT:
	95	$dbtype = 'NUMBER';
	96	break;
	97	case XMLDB_TYPE_CHAR:
	98	$dbtype = 'VARCHAR2';
	99	if (empty($xmldb_length)) {
	100	$xmldb_length='255';
	101	}
	102	$dbtype .= '(' . $xmldb_length . ')';
	103	break;
	104	case XMLDB_TYPE_TEXT:
	105	$dbtype = 'CLOB';
	106	break;
	107	case XMLDB_TYPE_BINARY:
	108	$dbtype = 'BLOB';
	109	break;
	110	case XMLDB_TYPE_DATETIME:
	111	$dbtype = 'DATE';
	112	break;
	113	}
	114	return $dbtype;
	115	}
	116
	117	/**
	118	* Returns the code needed to create one enum for the xmldb_table and xmldb_field passes
	119	*/
	120	function getEnumExtraSQL ($xmldb_table, $xmldb_field) {
	121
	122	$sql = 'CONSTRAINT ' . $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'ck');
0dd87cfa	123	$sql.= ' CHECK (' . $this->getEncQuoted($xmldb_field->getName()) . ' IN (' . implode(', ', $xmldb_field->getEnumValues()) . '))';
d7444bfc	124
	125	return $sql;
	126	}
	127
	128	/**
	129	* Returns the code needed to create one sequence for the xmldb_table and xmldb_field passes
	130	*/
	131	function getCreateSequenceSQL ($xmldb_table, $xmldb_field) {
	132
465a8029	133	$sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
	134
	135	$sequence = "CREATE SEQUENCE " . $sequence_name;
d7444bfc	136	$sequence.= "\n START WITH 1";
d7444bfc	137	$sequence.= "\n INCREMENT BY 1";
9dcc6300	138	$sequence.= "\n NOMAXVALUE";
d7444bfc	139
	140	$trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
	141
9dcc6300	142	$trigger = "CREATE OR REPLACE TRIGGER " . $trigger_name;
d7444bfc	143	$trigger.= "\n BEFORE INSERT";
	144	$trigger.= "\nON " . $this->getEncQuoted($this->prefix . $xmldb_table->getName());
	145	$trigger.= "\n FOR EACH ROW";
	146	$trigger.= "\nBEGIN";
4782a1f8	147	$trigger.= "\n IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN';
b8851b80	148	$trigger.= "\n SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;";
b8851b80	149	$trigger.= "\n END IF;";
0513f3bf	150	$trigger.= "\nEND;";
9dcc6300	151	return array($sequence, $trigger);
d7444bfc	152	}
d7444bfc	153
3a8c55c3	154	/**
	155	* Returns the code needed to drop one sequence for the xmldb_table and xmldb_field passed
	156	* Can, optionally, specify if the underlying trigger will be also dropped
	157	*/
	158	function getDropSequenceSQL ($xmldb_table, $xmldb_field, $include_trigger=false) {
	159
	160	$sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
	161
	162	$sequence = "DROP SEQUENCE " . $sequence_name;
	163
	164	$trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
	165
	166	$trigger = "DROP TRIGGER " . $trigger_name;
	167
	168	if ($include_trigger) {
	169	$result = array($sequence, $trigger);
	170	} else {
	171	$result = array($sequence);
	172	}
	173	return $result;
	174	}
	175
	176	/**
	177	* Returns the code (in array) needed to add one comment to the table
	178	*/
	179	function getCommentSQL ($xmldb_table) {
	180
	181	$comment = "COMMENT ON TABLE " . $this->getEncQuoted($this->prefix . $xmldb_table->getName());
	182	$comment.= " IS '" . substr($xmldb_table->getComment(), 0, 250) . "'";
d7444bfc	183
3a8c55c3	184	return array($comment);
3a8c55c3	185	}
d7444bfc	186
3a8c55c3	187	/**
	188	* Returns the code (array of statements) needed to execute extra statements on table drop
	189	*/
	190	function getDropTableExtraSQL ($xmldb_table) {
	191	$xmldb_field = new XMLDBField('id'); // Fields having sequences should be exclusively, id.
	192	return $this->getDropSequenceSQL($xmldb_table, $xmldb_field, false);
	193	}
d7444bfc	194
	195	/**
	196	* Returns an array of reserved words (lowercase) for this DB
	197	*/
	198	function getReservedWords() {
6aa7885e	199	/// This file contains the reserved words for Oracle databases
6aa7885e	200	/// from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm
d7444bfc	201	$reserved_words = array (
	202	'access', 'add', 'all', 'alter', 'and', 'any',
	203	'as', 'asc', 'audit', 'between', 'by', 'char',
	204	'check', 'cluster', 'column', 'comment',
	205	'compress', 'connect', 'create', 'current',
	206	'date', 'decimal', 'default', 'delete', 'desc',
	207	'distinct', 'drop', 'else', 'exclusive', 'exists',
	208	'file', 'float', 'for', 'from', 'grant', 'group',
	209	'having', 'identified', 'immediate', 'in',
	210	'increment', 'index', 'initial', 'insert',
	211	'integer', 'intersect', 'into', 'is', 'level',
	212	'like', 'lock', 'long', 'maxextents', 'minus',
	213	'mlslabel', 'mode', 'modify', 'noaudit',
	214	'nocompress', 'not', 'nowait', 'null', 'number',
	215	'of', 'offline', 'on', 'online', 'option', 'or',
	216	'order', 'pctfree', 'prior', 'privileges',
	217	'public', 'raw', 'rename', 'resource', 'revoke',
	218	'row', 'rowid', 'rownum', 'rows', 'select',
	219	'session', 'set', 'share', 'size', 'smallint',
	220	'start', 'successful', 'synonym', 'sysdate',
	221	'table', 'then', 'to', 'trigger', 'uid', 'union',
	222	'unique', 'update', 'user', 'validate', 'values',
	223	'varchar', 'varchar2', 'view', 'whenever',
	224	'where', 'with'
	225	);
	226	return $reserved_words;
	227	}
	228	}
	229
	230	?>