/moodle/lib/xmldb/classes/generators/db2/db2.class.php

Large files files are truncated, but you can click here to view the full file

<?php // $Id: db2.class.php,v 1.95 2009/10/20 21:01:30 hblove1 Exp $

///////////////////////////////////////////////////////////////////////////
//                                                                       //
// NOTICE OF COPYRIGHT                                                   //
//                                                                       //
// Moodle - Modular Object-Oriented Dynamic Learning Environment         //
//          http://moodle.com                                            //
//                                                                       //
// Copyright (C) 1999 onwards 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 DB2
/// It extends XMLDBgenerator so everything can be
/// overriden as needed to generate correct SQL.

class XMLDBdb2 extends XMLDBgenerator {

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

    var $quote_string = '';   // String used to quote names
	

//    var $quote_all    = false; // To decide if we want to quote all the names or only the reserved ones

//    var $statement_end = ';'; // String to be automatically added at the end of each statement
	// based on research: ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2s1e80.pdf  pg. 553
	var $statement_end = '';

//    var $integer_to_number = false;  // To create all the integers as NUMBER(x) (also called DECIMAL, NUMERIC...)
//    var $float_to_number   = false;  // To create all the floats as NUMBER(x) (also called DECIMAL, NUMERIC...)

//    var $number_type = 'NUMERIC';    // Proper type for NUMBER(x) in this DB
	// based on research: ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2s1e80.pdf  pg. 94
	var $number_type = 'DECIMAL';

//    var $unsigned_allowed = true;    // To define in the generator must handle unsigned information
	// based on research: ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2s1e80.pdf  pg. 94
	var $unsigned_allowed = false;    // To define in the generator must handle unsigned information
	
//    var $default_for_char = null;      // To define the default to set for NOT NULLs CHARs without default (null=do nothing)
    var $default_for_char = '';

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

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

//    var $specify_nulls = false;  //To force the generator if NULL clauses must be specified. It shouldn't be necessary
                                 //but some mssql drivers require them or everything is created as NOT NULL :-(

//    var $primary_key_name = null; //To force primary key names to one string (null=no force)

//    var $primary_keys = true;  // Does the generator build primary keys
//    var $unique_keys = false;  // Does the generator build unique keys
//    var $foreign_keys = false; // Does the generator build foreign keys

// *********************************************************** //
// ******************** COME BACK TO THIS ******************** //
// ************************ DROP KEYS ************************ //
// *********************************************************** //

//    var $drop_primary_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME'; // Template to drop PKs
                               // with automatic replace for TABLENAME and KEYNAME

//    var $drop_unique_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME'; // Template to drop UKs
                               // with automatic replace for TABLENAME and KEYNAME

//    var $drop_foreign_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME'; // Template to drop FKs
                               // with automatic replace for TABLENAME and KEYNAME

//    var $sequence_extra_code = true; //Does the generator need to add extra code to generate the sequence fields
    var $sequence_extra_code = false; //Does the generator need to add extra code to generate the sequence fields

//    var $sequence_name = 'auto_increment'; //Particular name for inline sequences in this generator
	// based on research: http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp
	var $sequence_name = 'GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)';
//    var $sequence_name_small = false; //Different name for small (4byte) sequences or false if same
//    var $sequence_only = false; //To avoid to output the rest of the field specs, leaving only the name and the sequence_name variable

//    var $enum_inline_code = true; //Does the generator need to add inline code in the column definition
//    var $enum_extra_code = true; //Does the generator need to add extra code to generate code for the enums in the table
	// based on research: http://stackoverflow.com/questions/336997/which-database-systems-support-an-enum-data-type-which-dont
	var $enum_inline_code = false; //Does the generator need to add inline code in the column definition

//    var $add_table_comments  = true;  // Does the generator need to add code for table comments
	var $add_table_comments = false;

//    var $add_after_clause = false; // Does the generator need to add the after clause for fields

//    var $prefix_on_names = true; //Does the generator need to prepend the prefix to all the key/index/sequence/trigger/check names

//    var $names_max_length = 30; //Max length for key/index/sequence/trigger/check names (keep 30 for all!)

//    var $concat_character = '||'; //Characters to be used as concatenation operator. If not defined
                                  //MySQL CONCAT function will be used
//	var $concat_character = null;

// *********************************************************** //
// ******************** COME BACK TO THIS ******************** //
// ********************** RENAME TABLES ********************** //
// *********************************************************** //

//    var $rename_table_sql = 'ALTER TABLE OLDNAME RENAME TO NEWNAME'; //SQL sentence to rename one table, both
                                  //OLDNAME and NEWNAME are dinamically replaced

//    var $rename_table_extra_code = false; //Does the generator need to add code after table rename

// *********************************************************** //
// ******************** COME BACK TO THIS ******************** //
// *********************** DROP TABLES *********************** //
// *********************************************************** //

//    var $drop_table_sql = 'DROP TABLE TABLENAME'; //SQL sentence to drop one table
                                  //TABLENAME is dinamically replaced

//    var $drop_table_extra_code = false; //Does the generator need to add code after table drop

// *********************************************************** //
// ******************** COME BACK TO THIS ******************** //
// ********************** ALTER COLUMNS ********************** //
// *********************************************************** //

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

//    var $alter_column_skip_default = false; //The generator will skip the default clause on alter columns

//    var $alter_column_skip_type = false; //The generator will skip the type clause on alter columns

//    var $alter_column_skip_notnull = false; //The generator will skip the null/notnull clause on alter columns

// *********************************************************** //
// ******************** COME BACK TO THIS ******************** //
// ********************** RENAME COLUMN ********************** //
// *********************************************************** //

//    var $rename_column_sql = 'ALTER TABLE TABLENAME RENAME COLUMN OLDFIELDNAME TO NEWFIELDNAME';
                                  ///TABLENAME, OLDFIELDNAME and NEWFIELDNAME are dianmically replaced

//    var $rename_column_extra_code = false; //Does the generator need to add code after column rename

// *********************************************************** //
// ******************** COME BACK TO THIS ******************** //
// ************************* INDEXES ************************* //
// *********************** DROP,RENAME *********************** //
// *********************************************************** //

//    var $drop_index_sql = 'DROP INDEX INDEXNAME'; //SQL sentence to drop one index
                                  //TABLENAME, INDEXNAME are dinamically replaced

//    var $rename_index_sql = 'ALTER INDEX OLDINDEXNAME RENAME TO NEWINDEXNAME'; //SQL sentence to rename one index
                                  //TABLENAME, OLDINDEXNAME, NEWINDEXNAME are dinamically replaced

// *********************************************************** //
// ******************** COME BACK TO THIS ******************** //
// *********************** RENAME KEYS *********************** //
// *********************************************************** //
								  
//    var $rename_key_sql = 'ALTER TABLE TABLENAME CONSTRAINT OLDKEYNAME RENAME TO NEWKEYNAME'; //SQL sentence to rename one key
                                  //TABLENAME, OLDKEYNAME, NEWKEYNAME are dinamically replaced

//    var $prefix;         // Prefix to be used for all the DB objects

//    var $reserved_words; // List of reserved words (in order to quote them properly)

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

/// ALL THESE FUNCTION ARE SHARED BY ALL THE XMLDGenerator classes

    /**
     * Set the prefix
     *//*
    function setPrefix($prefix) {
        if ($this->prefix_on_names) { // Only if we want prefix on names
            $this->prefix = $prefix;
        }
    }*/

    /**
     * Given one XMLDBTable, returns it's correct name, depending of all the parametrization
     *
     * @param XMLDBTable table whose name we want
     * @param boolean to specify if the name must be quoted (if reserved word, only!)
     * @return string the correct name of the table
     *//*
    function getTableName($xmldb_table, $quoted = true) {

        $prefixtouse = $this->prefix;
    /// Determinate if this table must have prefix or no
        if (in_array($xmldb_table->getName(), $this->getTablesWithoutPrefix())) {
            $prefixtouse = '';
        }
    /// Add Schema name for DB2
        $prefixtouse = $this->schema . '.' . $prefixtouse;
    /// Get the name
        $tablename = $prefixtouse . $xmldb_table->getName();
    /// Apply quotes conditionally
        if ($quoted) {
            $tablename = $this->getEncQuoted($tablename);
        }

        return $tablename;
    }

    /**
     * Given one correct XMLDBTable, returns the SQL statements
     * to create it (inside one array)
     *//*
    function getCreateTableSQL($xmldb_table) {

        $results = array();  //Array where all the sentences will be stored

    /// Table header
        $table = 'CREATE TABLE ' . $this->getTableName($xmldb_table) . ' (';

        if (!$xmldb_fields = $xmldb_table->getFields()) {
            return $results;
        }

    /// Prevent tables without prefix to be duplicated (part of MDL-6614)
        if (in_array($xmldb_table->getName(), $this->getTablesWithoutPrefix()) &&
            table_exists($xmldb_table)) {
            return $results; // false here would break the install, empty array is better ;-)
        }

    /// Add the fields, separated by commas
        foreach ($xmldb_fields as $xmldb_field) {
            $table .= "\n    " . $this->getFieldSQL($xmldb_field);
            $table .= ',';
        }
    /// Add the keys, separated by commas
        if ($xmldb_keys = $xmldb_table->getKeys()) {
            foreach ($xmldb_keys as $xmldb_key) {
                if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) {
                    $table .= "\nCONSTRAINT " . $keytext . ',';
                }
            /// If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too
                if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE) {
                ///Duplicate the key
                    $xmldb_key->setType(XMLDB_KEY_UNIQUE);
                    if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) {
                        $table .= "\nCONSTRAINT " . $keytext . ',';
                    }
                }
            }
        }
    /// Add enum extra code if needed
        if ($this->enum_extra_code) {
        /// Iterate over fields looking for enums
            foreach ($xmldb_fields as $xmldb_field) {
                if ($xmldb_field->getEnum()) {
                    $table .= "\n" . $this->getEnumExtraSQL($xmldb_table, $xmldb_field) . ',';
                }
            }
        }
    /// Table footer, trim the latest comma
        $table = trim($table,',');
        $table .= "\n)";

    /// Add the CREATE TABLE to results
        $results[] = $table;

    /// Add comments if specified and it exists
        if ($this->add_table_comments && $xmldb_table->getComment()) {
            $comment = $this->getCommentSQL ($xmldb_table);
        /// Add the COMMENT to results
            $results = array_merge($results, $comment);
        }

    /// Add the indexes (each one, one statement)
        if ($xmldb_indexes = $xmldb_table->getIndexes()) {
            foreach ($xmldb_indexes as $xmldb_index) {
            ///Only process all this if the index doesn't exist in DB
                if (!index_exists($xmldb_table, $xmldb_index)) {
                    if ($indextext = $this->getCreateIndexSQL($xmldb_table, $xmldb_index)) {
                        $results = array_merge($results, $indextext);
                    }
                }
            }
        }

    /// Also, add the indexes needed from keys, based on configuration (each one, one statement)
        if ($xmldb_keys = $xmldb_table->getKeys()) {
            foreach ($xmldb_keys as $xmldb_key) {
            /// If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 
            /// automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists)
                if (!$this->getKeySQL($xmldb_table, $xmldb_key) || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
                /// Create the interim index   
                    $index = new XMLDBIndex('anyname');
                    $index->setFields($xmldb_key->getFields());
                ///Only process all this if the index doesn't exist in DB
                    if (!index_exists($xmldb_table, $index)) {
                        $createindex = false; //By default
                        switch ($xmldb_key->getType()) {
                            case XMLDB_KEY_UNIQUE:
                            case XMLDB_KEY_FOREIGN_UNIQUE:
                                $index->setUnique(true);
                                $createindex = true;
                                break;
                            case XMLDB_KEY_FOREIGN:
                                $index->setUnique(false);
                                $createindex = true;
                                break;
                        }
                        if ($createindex) {
                            if ($indextext = $this->getCreateIndexSQL($xmldb_table, $index)) {
                            /// Add the INDEX to the array
                                $results = array_merge($results, $indextext);
                            }
                        }
                    }
                }
            }
        }

    /// Add sequence extra code if needed
        if ($this->sequence_extra_code) {
        /// Iterate over fields looking for sequences
            foreach ($xmldb_fields as $xmldb_field) {
                if ($xmldb_field->getSequence()) {
                /// returns an array of statements needed to create one sequence
                    $sequence_sentences = $this->getCreateSequenceSQL($xmldb_table, $xmldb_field);
                /// Add the SEQUENCE to the array
                    $results = array_merge($results, $sequence_sentences);
                }
            }
        }

        return $results;
    }

    /**
     * Given one correct XMLDBIndex, returns the SQL statements
     * needed to create it (in array)
     *//*
    function getCreateIndexSQL ($xmldb_table, $xmldb_index) {

        $unique = '';
        $suffix = 'ix';
        if ($xmldb_index->getUnique()) {
            $unique = ' UNIQUE';
            $suffix = 'uix';
        }

        $index = 'CREATE' . $unique . ' INDEX ';
        $index .= $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_index->getFields()), $suffix);
        $index .= ' ON ' . $this->getTableName($xmldb_table);
        $index .= ' (' . implode(', ', $this->getEncQuoted($xmldb_index->getFields())) . ')';

        return array($index);
    }

    /**
     * Given one correct XMLDBField, returns the complete SQL line to create it
     *//*
    function getFieldSQL($xmldb_field, $skip_type_clause = false, $skip_default_clause = false, $skip_notnull_clause = false)  {

    /// First of all, convert integers to numbers if defined
        if ($this->integer_to_number) {
            if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER) {
                $xmldb_field->setType(XMLDB_TYPE_NUMBER);
            }
        }
    /// Same for floats
        if ($this->float_to_number) {
            if ($xmldb_field->getType() == XMLDB_TYPE_FLOAT) {
                $xmldb_field->setType(XMLDB_TYPE_NUMBER);
            }
        }

    /// The name
        $field = $this->getEncQuoted($xmldb_field->getName());
    /// The type and length only if we don't want to skip it
        if (!$skip_type_clause) {
        /// The type and length (if the field isn't enum)
            if (!$xmldb_field->getEnum() || $this->enum_inline_code == false) {
                $field .= ' ' . $this->getTypeSQL($xmldb_field->getType(), $xmldb_field->getLength(), $xmldb_field->getDecimals());
            } else {
            /// call to custom function
                $field .= ' ' . $this->getEnumSQL($xmldb_field);
            }
        }
    /// The unsigned if supported
        if ($this->unsigned_allowed && ($xmldb_field->getType() == XMLDB_TYPE_INTEGER ||
                                      $xmldb_field->getType() == XMLDB_TYPE_NUMBER ||
                                      $xmldb_field->getType() == XMLDB_TYPE_FLOAT)) {
            if ($xmldb_field->getUnsigned()) {
                $field .= ' unsigned';
            }
        }
    /// Calculate the not null clause
        $notnull = '';
    /// Only if we don't want to skip it
        if (!$skip_notnull_clause) {
            if ($xmldb_field->getNotNull()) {
                $notnull = ' NOT NULL';
            } else {
                if ($this->specify_nulls) {
                    $notnull = ' NULL';
                }
            }
        }
    /// Calculate the default clause
        if (!$skip_default_clause) { //Only if we don't want to skip it
            $default = $this->getDefaultClause($xmldb_field);
        } else {
            $default = '';
        }
    /// Based on default_after_null, set both clauses properly
        if ($this->default_after_null) {
            $field .= $notnull . $default;
        } else {
            $field .= $default . $notnull;
        }
    /// The sequence
        if ($xmldb_field->getSequence()) {
            if($xmldb_field->getLength()<=9 && $this->sequence_name_small) {
                $sequencename=$this->sequence_name_small;
            } else {
                $sequencename=$this->sequence_name;
            }
            $field .= ' ' . $sequencename;
            if ($this->sequence_only) {
            /// We only want the field name and sequence name to be printed
            /// so, calculate it and return
                return $this->getEncQuoted($xmldb_field->getName()) . ' ' . $sequencename;
            }
        }
        return $field;
    }

    /**
     * Given one correct XMLDBKey, returns its specs
     *//*
    function getKeySQL ($xmldb_table, $xmldb_key) {

        $key = '';

        switch ($xmldb_key->getType()) {
            case XMLDB_KEY_PRIMARY:
                if ($this->primary_keys) {
                    if ($this->primary_key_name !== null) {
                        $key = $this->getEncQuoted($this->primary_key_name);
                    } else {
                        $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'pk');
                    }
                    $key .= ' PRIMARY KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
                }
                break;
            case XMLDB_KEY_UNIQUE:
                if ($this->unique_keys) {
                    $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'uk');
                    $key .= ' UNIQUE (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
                }
                break;
            case XMLDB_KEY_FOREIGN:
            case XMLDB_KEY_FOREIGN_UNIQUE:
                if ($this->foreign_keys) {
                    $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'fk');
                    $key .= ' FOREIGN KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
                    $key .= ' REFERENCES ' . $this->getEncQuoted($this->prefix . $xmldb_key->getRefTable());
                    $key .= ' (' . implode(', ', $this->getEncQuoted($xmldb_key->getRefFields())) . ')';
                }
                break;
        }

        return $key;
    }

    /**
     * Give one XMLDBField, returns the correct "default value" for the current configuration
     *//*
    function getDefaultValue ($xmldb_field) {

        $default = null;

        if ($xmldb_field->getDefault() !== NULL) {
            if ($xmldb_field->getType() == XMLDB_TYPE_CHAR ||
                $xmldb_field->getType() == XMLDB_TYPE_TEXT) {
                    $default = "'" . addslashes($xmldb_field->getDefault()) . "'";
            } else {
                $default = $xmldb_field->getDefault();
            }
        } else {
        /// We force default '' for not null char columns without proper default
        /// some day this should be out!
            if ($this->default_for_char !== NULL &&
                $xmldb_field->getType() == XMLDB_TYPE_CHAR &&
                $xmldb_field->getNotNull()) {
                $default = "'" . $this->default_for_char . "'";
            } else {
            /// If the DB requires to explicity define some clause to drop one default, do it here
            /// never applying defaults to TEXT and BINARY fields
                if ($this->drop_default_clause_required &&
                    $xmldb_field->getType() != XMLDB_TYPE_TEXT &&
                    $xmldb_field->getType() != XMLDB_TYPE_BINARY && !$xmldb_field->getNotNull()) {
                    $default = $this->drop_default_clause;
                }
            }
        }
        return $default;
    }

    /**
     * Given one XMLDBField, returns the correct "default clause" for the current configuration
     *//*
    function getDefaultClause ($xmldb_field) {

        $defaultvalue = $this->getDefaultValue ($xmldb_field);

        if ($defaultvalue !== null) {
            return ' DEFAULT ' . $defaultvalue;
        } else {
            return null;
        }
    }

    /**
     * Given one correct XMLDBTable and the new name, returns the SQL statements
     * to rename it (inside one array)
     *//*

    function getRenameTableSQL($xmldb_table, $newname) {

        $results = array();  //Array where all the sentences will be stored

        $newt = new XMLDBTable($newname); //Temporal table for name calculations

        $rename = str_replace('OLDNAME', $this->getTableName($xmldb_table), $this->rename_table_sql);
        $rename = str_replace('NEWNAME', $this->getTableName($newt), $rename);

        $results[] = $rename;

    /// Call to getRenameTableExtraSQL() if $rename_table_extra_code is enabled. It will add sequence regeneration code.
        if ($this->rename_table_extra_code) {
            $extra_sentences = $this->getRenameTableExtraSQL($xmldb_table, $newname);
            $results = array_merge($results, $extra_sentences);
        }

        return $results;
    }

    /**
     * Given one correct XMLDBTable and the new name, returns the SQL statements
     * to drop it (inside one array)
     *//*
    function getDropTableSQL($xmldb_table) {

        $results = array();  //Array where all the sentences will be stored

        $drop = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_table_sql);

        $results[] = $drop;

    /// call to getDropTableExtraSQL() if $drop_table_extra_code is enabled. It will add sequence/trigger drop code.
        if ($this->drop_table_extra_code) {
            $extra_sentences = $this->getDropTableExtraSQL($xmldb_table);
            $results = array_merge($results, $extra_sentences);
        }

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to add the field to the table
     *//*
    function getAddFieldSQL($xmldb_table, $xmldb_field) {

        $results = array();

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

    /// Build the standard alter table add
        $altertable = 'ALTER TABLE ' . $tablename . ' ADD ' . 
                           $this->getFieldSQL($xmldb_field, $this->alter_column_skip_type,
                                                            $this->alter_column_skip_default,
                                                            $this->alter_column_skip_notnull);
    /// Add the after clause if necesary
        if ($this->add_after_clause && $xmldb_field->getPrevious()) {
            $altertable .= ' after ' . $this->getEncQuoted($xmldb_field->getPrevious());
        }
        $results[] = $altertable;

    /// If the DB has extra enum code
        if ($this->enum_extra_code) {
        /// If it's enum add the extra code
            if ($xmldb_field->getEnum()) {
                $results[] = 'ALTER TABLE ' . $tablename . ' ADD ' . $this->getEnumExtraSQL($xmldb_table, $xmldb_field);
            }
        }

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop the field from the table
     *//*
    function getDropFieldSQL($xmldb_table, $xmldb_field) {

        $results = array();

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

    /// Build the standard alter table drop
        $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname;

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
     *//*
    function getAlterFieldSQL($xmldb_table, $xmldb_field) {

        $results = array();

    /// Always specify NULLs in alter fields because we can change not nulls to nulls
        $this->specify_nulls = true;

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

    /// Build de alter sentence using the alter_column_sql template
        $alter = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->alter_column_sql);
        $alter = str_replace('COLUMNSPECS', $this->getFieldSQL($xmldb_field, $this->alter_column_skip_type,
                                                                             $this->alter_column_skip_default,
                                                                             $this->alter_column_skip_notnull), $alter);

    /// Add the after clause if necesary
        if ($this->add_after_clause && $xmldb_field->getPrevious()) {
            $alter .= ' after ' . $this->getEncQuoted($xmldb_field->getPrevious());
        }

    /// Build the standard alter table modify
        $results[] = $alter;

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to modify the enum of the field in the table
     *//*
    function getModifyEnumSQL($xmldb_table, $xmldb_field) {

        $results = array();

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

    /// Decide if we are going to create or to drop the enum (based exclusively in the values passed!)
        if (!$xmldb_field->getEnum()) {
            $results = $this->getDropEnumSQL($xmldb_table, $xmldb_field); //Drop
        } else {
            $results = $this->getCreateEnumSQL($xmldb_table, $xmldb_field); //Create/modify
        }

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to modify the default of the field in the table
     *//*
    function getModifyDefaultSQL($xmldb_table, $xmldb_field) {

        $results = array();

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

    /// Decide if we are going to create/modify or to drop the default
        if ($xmldb_field->getDefault() === null) {
            $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop
        } else {
            $results = $this->getCreateDefaultSQL($xmldb_table, $xmldb_field); //Create/modify
        }

        return $results;
    }

    /**
     * Given one correct XMLDBField and the new name, returns the SQL statements
     * to rename it (inside one array)
     *//*
    function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {

        $results = array();  //Array where all the sentences will be stored

    /// Although this is checked in ddllib - rename_field() - double check
    /// that we aren't trying to rename one "id" field. Although it could be
    /// implemented (if adding the necessary code to rename sequences, defaults,
    /// triggers... and so on under each getRenameFieldExtraSQL() function, it's
    /// better to forbide it, mainly because this field is the default PK and
    /// in the future, a lot of FKs can be pointing here. So, this field, more
    /// or less, must be considered inmutable!
        if ($xmldb_field->getName() == 'id') {
            return array();
        }

        $rename = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_column_sql);
        $rename = str_replace('OLDFIELDNAME', $this->getEncQuoted($xmldb_field->getName()), $rename);
        $rename = str_replace('NEWFIELDNAME', $this->getEncQuoted($newname), $rename);

        $results[] = $rename;

    /// Call to getRenameFieldExtraSQL() if $rename_column_extra_code is enabled (will add some required sentences)
        if ($this->rename_column_extra_code) {
            $extra_sentences = $this->getRenameFieldExtraSQL($xmldb_table, $xmldb_field, $newname);
            $results = array_merge($results, $extra_sentences);
        }

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBKey, return the SQL statements needded to add the key to the table
     * note that undelying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters
     *//*
    function getAddKeySQL($xmldb_table, $xmldb_key) {

        $results = array();

    /// Just use the CreateKeySQL function
        if ($keyclause = $this->getKeySQL($xmldb_table, $xmldb_key)) {
            $key = 'ALTER TABLE ' . $this->getTableName($xmldb_table) .
               ' ADD CONSTRAINT ' . $keyclause;
            $results[] = $key;
        }

    /// If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 
    /// automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists)
        if (!$keyclause || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
        /// Only if they don't exist
            if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN) {  ///Calculate type of index based on type ok key
                $indextype = XMLDB_INDEX_NOTUNIQUE;
            } else {
                $indextype = XMLDB_INDEX_UNIQUE;
            }
            $xmldb_index = new XMLDBIndex('anyname');
            $xmldb_index->setAttributes($indextype, $xmldb_key->getFields());
            if (!index_exists($xmldb_table, $xmldb_index)) {
                $results = array_merge($results, $this->getAddIndexSQL($xmldb_table, $xmldb_index));
            }
        }

    /// If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too
        if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) {
        ///Duplicate the key
            $xmldb_key->setType(XMLDB_KEY_UNIQUE);
            $results = array_merge($results, $this->getAddKeySQL($xmldb_table, $xmldb_key));
        }
        
    /// Return results
        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBIndex, return the SQL statements needded to drop the index from the table
     *//*
    function getDropKeySQL($xmldb_table, $xmldb_key) {

        $results = array();

    /// Get the key name (note that this doesn't introspect DB, so could cause some problems sometimes!)
    /// TODO: We'll need to overwrite the whole getDropKeySQL() method inside each DB to do the proper queries
    /// against the dictionary or require ADOdb to support it or change the find_key_name() method to
    /// perform DB introspection directly. But, for now, as we aren't going to enable referential integrity
    /// it won't be a problem at all
        $dbkeyname = find_key_name($xmldb_table, $xmldb_key);

    /// Only if such type of key generation is enabled
        $dropkey = false;
        switch ($xmldb_key->getType()) {
            case XMLDB_KEY_PRIMARY:
                if ($this->primary_keys) {
                    $template = $this->drop_primary_key;
                    $dropkey = true;
                }
                break;
            case XMLDB_KEY_UNIQUE:
                if ($this->unique_keys) {
                    $template = $this->drop_unique_key;
                    $dropkey = true;
                }
                break;
            case XMLDB_KEY_FOREIGN_UNIQUE:
            case XMLDB_KEY_FOREIGN:
                if ($this->foreign_keys) {
                    $template = $this->drop_foreign_key;
                    $dropkey = true;
                }
                break;
        }
    /// If we have decided to drop the key, let's do it
        if ($dropkey) {
        /// Replace TABLENAME, CONSTRAINTTYPE and KEYNAME as needed
            $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $template);
            $dropsql = str_replace('KEYNAME', $dbkeyname, $dropsql);

            $results[] = $dropsql;
        }

    /// If we aren't dropping the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 
    /// automatically by the RDBMS) drop the underlying (created by us) index (if exists)
        if (!$dropkey || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
        /// Only if they exist
            $xmldb_index = new XMLDBIndex('anyname');
            $xmldb_index->setAttributes(XMLDB_INDEX_UNIQUE, $xmldb_key->getFields());
            if (index_exists($xmldb_table, $xmldb_index)) {
                $results = array_merge($results, $this->getDropIndexSQL($xmldb_table, $xmldb_index));
            }
        }

    /// If the key is XMLDB_KEY_FOREIGN_UNIQUE, drop the UNIQUE too
        if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) {
        ///Duplicate the key
            $xmldb_key->setType(XMLDB_KEY_UNIQUE);
            $results = array_merge($results, $this->getDropKeySQL($xmldb_table, $xmldb_key));
        }
        
    /// Return results
        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBKey, return the SQL statements needded to rename the key in the table
     * Experimental! Shouldn't be used at all!
     *//*
    function getRenameKeySQL($xmldb_table, $xmldb_key, $newname) {

        $results = array();

    /// Get the real key name
        $dbkeyname = find_key_name($xmldb_table, $xmldb_key);

    /// Check we are really generating this type of keys
        if (($xmldb_key->getType() == XMLDB_KEY_PRIMARY && !$this->primary_keys) ||
            ($xmldb_key->getType() == XMLDB_KEY_UNIQUE && !$this->unique_keys) ||
            ($xmldb_key->getType() == XMLDB_KEY_FOREIGN && !$this->foreign_keys) ||
            ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && !$this->unique_keys && !$this->foreign_keys)) {
        /// We aren't generating this type of keys, delegate to child indexes
            $xmldb_index = new XMLDBIndex($xmldb_key->getName());
            $xmldb_index->setFields($xmldb_key->getFields());
            return $this->getRenameIndexSQL($xmldb_table, $xmldb_index, $newname);
        }

    /// Arrived here so we are working with keys, lets rename them
    /// Replace TABLENAME and KEYNAME as needed
        $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_key_sql);
        $renamesql = str_replace('OLDKEYNAME', $dbkeyname, $renamesql);
        $renamesql = str_replace('NEWKEYNAME', $newname, $renamesql);

    /// Some DB doesn't support key renaming so this can be empty
        if ($renamesql) {
            $results[] = $renamesql;
        }

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBIndex, return the SQL statements needded to add the index to the table
     *//*
    function getAddIndexSQL($xmldb_table, $xmldb_index) {

    /// Just use the CreateIndexSQL function
        return $this->getCreateIndexSQL($xmldb_table, $xmldb_index);
    }

    /**
     * Given one XMLDBTable and one XMLDBIndex, return the SQL statements needded to drop the index from the table
     *//*
    function getDropIndexSQL($xmldb_table, $xmldb_index) {

        $results = array();

    /// Get the real index name
        $dbindexname = find_index_name($xmldb_table, $xmldb_index);

    /// Replace TABLENAME and INDEXNAME as needed
        $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_index_sql);
        $dropsql = str_replace('INDEXNAME', $dbindexname, $dropsql);

        $results[] = $dropsql;

        return $results;
    }

    /**
     * Given one XMLDBTable and one XMLDBIndex, return the SQL statements needded to rename the index in the table
     * Experimental! Shouldn't be used at all!
     *//*
    function getRenameIndexSQL($xmldb_table, $xmldb_index, $newname) {

        $results = array();

    /// Get the real index name
        $dbindexname = find_index_name($xmldb_table, $xmldb_index);

    /// Replace TABLENAME and INDEXNAME as needed
        $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_index_sql);
        $renamesql = str_replace('OLDINDEXNAME', $dbindexname, $renamesql);
        $renamesql = str_replace('NEWINDEXNAME', $newname, $renamesql);

    /// Some DB doesn't support index renaming (MySQL) so this can be empty
        if ($renamesql) {
            $results[] = $renamesql;
        }

        return $results;
    }

    /**
     * Given three strings (table name, list of fields (comma separated) and suffix),
     * create the proper object name quoting it if necessary.
     *
     * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED,
     *            NEVER TO GUESS NAMES of EXISTING objects!!!
     *//*
    function getNameForObject($tablename, $fields, $suffix='') {

        $name = '';

    /// Implement one basic cache to avoid object name duplication
    /// and to speed up repeated queries for the same objects
        if (!isset($used_names)) {
            static $used_names = array();
        }

    /// If this exact object has been requested, return it
        if (array_key_exists($tablename.'-'.$fields.'-'.$suffix, $used_names)) {
            return $used_names[$tablename.'-'.$fields.'-'.$suffix];
        }

    /// Use standard naming. See http://docs.moodle.org/en/XMLDB_key_and_index_naming
        $tablearr = explode ('_', $tablename);
        foreach ($tablearr as $table) {
            $name .= substr(trim($table),0,4);
        }
        $name .= '_';
        $fieldsarr = explode (',', $fields);
        foreach ($fieldsarr as $field) {
            $name .= substr(trim($field),0,3);
        }
    /// Prepend the prefix
        $name = $this->prefix . $name;

        $name = substr(trim($name), 0, $this->names_max_length - 1 - strlen($suffix)); //Max names_max_length

    /// Add the suffix
        $namewithsuffix = $name;
        if ($suffix) {
            $namewithsuffix = $namewithsuffix . '_' . $suffix;
        }

    /// If the calculated name is in the cache, or if we detect it by introspecting the DB let's modify if
        if (in_array($namewithsuffix, $used_names) || $this->isNameInUse($namewithsuffix, $suffix, $tablename)) {
            $counter = 2;
        /// If have free space, we add 2
            if (strlen($namewithsuffix) < $this->names_max_length) {
                $newname = $name . $counter;
        /// Else replace the last char by 2
            } else {
                $newname = substr($name, 0, strlen($name)-1) . $counter;
            }
            $newnamewithsuffix = $newname;
            if ($suffix) {
                $newnamewithsuffix = $newnamewithsuffix . '_' . $suffix;
            }
        /// Now iterate until not used name is found, incrementing the counter
            while (in_array($newnamewithsuffix, $used_names) || $this->isNameInUse($newnamewithsuffix, $suffix, $tablename)) {
                $counter++;
                $newname = substr($name, 0, strlen($newname)-1) . $counter;
                $newnamewithsuffix = $newname;
                if ($suffix) {
                    $newnamewithsuffix = $newnamewithsuffix . '_' . $suffix;
                }
            }
            $namewithsuffix = $newnamewithsuffix;
        }

    /// Add the name to the cache
        $used_names[$tablename.'-'.$fields.'-'.$suffix] = $namewithsuffix;

    /// Quote it if necessary (reserved words)
        $namewithsuffix = $this->getEncQuoted($namewithsuffix);

        return $namewithsuffix;
    }

    /**
     * Given any string (or one array), enclose it by the proper quotes
     * if it's a reserved word
     *//*
    function getEncQuoted($input) {

        if (is_array($input)) {
            foreach ($input as $key=>$content) {
                $input[$key] = $this->getEncQuoted($content);
            }
            return $input;
        } else {
        /// Always lowercase
            $input = strtolower($input);
        /// if reserved or quote_all, quote it
            if ($this->quote_all || in_array($input, $this->reserved_words)) {
                $input = $this->quote_string . $input . $this->quote_string;
            }
            return $input;
        }
    }

    /**
     * Given one XMLDB Statement, build the needed SQL insert sentences to execute it
     *//*
    function getExecuteInsertSQL($statement) {

         $results = array();  //Array where all the sentences will be stored

         if ($sentences = $statement->getSentences()) {
             foreach ($sentences as $sentence) {
             /// Get the list of fields
                 $fields = $statement->getFieldsFromInsertSentence($sentence);
             /// Get the values of fields
                 $values = $statement->getValuesFromInsertSentence($sentence);
             /// Look if we have some CONCAT value and transform it dinamically
                 foreach($values as $key => $value) {
                 /// Trim single quotes
                     $value = trim($value,"'");
                     if (stristr($value, 'CONCAT') !== false){
                     /// Look for data between parentesis
                         preg_match("/CONCAT\s*\((.*)\)$/is", trim($value), $matches);
                         if (isset($matches[1])) {
                             $part = $matches[1];
                         /// Convert the comma separated string to an array
                             $arr = XMLDBObject::comma2array($part);
                             if ($arr) {
                                 $value = $this->getConcatSQL($arr);
                             }
                         }
                     }
                 /// Values to be sent to DB must be properly escaped
                     $value = addslashes($value);
                 /// Back trimmed quotes
                     $value = "'" . $value . "'";
                 /// Back to the array
                     $values[$key] = $value;
                 }

             /// Iterate over fields, escaping them if necessary
                 foreach($fields as $key => $field) {
                     $fields[$key] = $this->getEncQuoted($field);
                 }
             /// Build the final SQL sentence and add it to the array of results
             $sql = 'INSERT INTO ' . $this->getEncQuoted($this->prefix . $statement->getTable()) .
                         '(' . implode(', ', $fields) . ') ' .
                         'VALUES (' . implode(', ', $values) . ')';
                 $results[] = $sql;
             }

         }
         return $results;
    }

    /**
     * Given one array of elements, build de proper CONCAT expresion, based
     * in the $concat_character setting. If such setting is empty, then
     * MySQL's CONCAT function will be used instead
     *//*
    function getConcatSQL($elements) {

    /// Replace double quoted elements by single quotes
        foreach($elements as $key => $element) {
            $element = trim($element);
            if (substr($element, 0, 1) == '"' &&
                substr($element, -1, 1) == '"') {
                    $elements[$key] = "'" . trim($element, '"') . "'";
            }
        }

    /// Now call the standard sql_concat() DML function
        return call_user_func_array('sql_concat', $elements);
    }

    /**
     * Given one string (or one array), ends it with statement_end
     *//*
    function getEndedStatements ($input) {

        if (is_array($input)) {
            foreach ($input as $key=>$content) {
                $input[$key] = $this->getEndedStatements($content);
            }
            return $input;
        } else {
            $input = trim($input) . $this->statement_end;
            return $input;
        }
    }

    /**
     * Returns the name (string) of the sequence used in the table for the autonumeric pk
     * Only some DB have this implemented
     *//*
    function getSequenceFromDB($xmldb_table) {
        return false;
    }

    /**
     * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg)
     * return if such name is currently in use (true) or no (false)
     * (MySQL requires the whole XMLDBTable object to be specified, so we add it always)
     * (invoked from getNameForObject()
     * Only some DB have this implemented
     *//*
    function isNameInUse($object_name, $type, $table_name) {
        return false; //For generators not implementing introspecion, 
                      //we always return with the name being free to be used
    }


/// ALL THESE FUNCTION MUST BE CUSTOMISED BY ALL THE XMLDGenerator classes

    /**
     * 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:
                //Integer
                if (empty($xmldb_length)) {
                    $xmldb_length = 10;
                }
                if ($xmldb_length > 9) {
                    $dbtype = 'BIGINT';
                } else if ($xmldb_length > 4) {
                    $dbt…

Large files files are truncated, but you can click here to view the full file