<?php
/**
 * File containing the ezcDbSchemaOracleWriter class.
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 * @package DatabaseSchema
 * @version //autogentag//
 * @license http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
 */

/**
 * Handler for storing database schemas and applying differences that uses Oracle as backend.
 *
 * @package DatabaseSchema
 * @version //autogentag//
 */
class ezcDbSchemaOracleWriter extends ezcDbSchemaCommonSqlWriter implements ezcDbSchemaDbWriter, ezcDbSchemaDiffDbWriter
{
    /**
     * Contains a type map from DbSchema types to Oracle native types.
     *
     * @var array
     */
    private $typeMap = array(
        'integer' => 'number',
        'boolean' => 'char',
        'float' => 'float',
        'decimal' => 'number',
        'date' => 'date',
        'timestamp' => 'timestamp',
        'text' => 'varchar2',
        'blob' => 'blob',
        'clob' => 'clob'
    );

    /**
     * Checks if query allowed.
     *
     * Perform testing if table exist for DROP TABLE query 
     * to avoid stoping execution while try to drop not existent table.
     * 
     * @param ezcDbHandler $db
     * @param string       $query
     * 
     * @return boolean false if query should not be executed.
     */
    public function isQueryAllowed( ezcDbHandler $db, $query )
    {
        if ( strstr( $query, 'AUTO_INCREMENT' ) ) // detect AUTO_INCREMENT and return imediately. Will process later.
        {
            return false;
        }

        if ( substr( $query, 0, 10 ) == 'DROP TABLE' )
        {
            $tableName = substr($query, strlen( 'DROP TABLE "' ), -1 ); // get table name without quotes

            $result = $db->query( "SELECT count( table_name ) AS count FROM user_tables WHERE table_name='$tableName'" )->fetchAll();
            if ( $result[0]['count'] == 1 )
            {
                $sequences = $db->query( "SELECT sequence_name FROM user_sequences" )->fetchAll();
                array_walk( $sequences, create_function( '&$item,$key', '$item = $item[0];' ) );
                foreach ( $sequences as $sequenceName )
                {
                    // try to drop sequences related to dropped table.
                    if ( substr( $sequenceName, 0, strlen($tableName) ) == $tableName )
                    {
                        $db->query( "DROP SEQUENCE \"{$sequenceName}\"" );
                    }
                }
                return true;
            }
            else
            {
                return false;
            }
        }

        return true;
    }

    /**
     * Returns what type of schema difference writer this class implements.
     *
     * This method always returns ezcDbSchema::DATABASE
     *
     * @return int
     */
    public function getDiffWriterType()
    {
        return ezcDbSchema::DATABASE;
    }

    /**
     * Applies the differences defined in $dbSchemaDiff to the database referenced by $db.
     *
     * This method uses {@link convertDiffToDDL} to create SQL for the
     * differences and then executes the returned SQL statements on the
     * database handler $db.
     *
     * @todo check for failed transaction
     *
     * @param ezcDbHandler    $db
     * @param ezcDbSchemaDiff $dbSchemaDiff
     */
    public function applyDiffToDb( ezcDbHandler $db, ezcDbSchemaDiff $dbSchemaDiff )
    {
        $db->beginTransaction();
        foreach ( $this->convertDiffToDDL( $dbSchemaDiff, $db ) as $query )
        {
            if ( $this->isQueryAllowed( $db, $query ) )
            {
                $db->exec( $query );
            }
            else
            {
                if ( strstr($query, 'AUTO_INCREMENT') ) // detect AUTO_INCREMENT and emulate it by adding sequence and trigger
                {
                    $db->commit();
                    $db->beginTransaction();
                    if ( preg_match ( "/ALTER TABLE (.*) MODIFY (.*?) (.*) AUTO_INCREMENT/" , $query, $matches ) ) 
                    {
                        $tableName = trim( $matches[1], '"' );
                        $autoIncrementFieldName = trim( $matches[2], '"' );
                        $autoIncrementFieldType = trim( $matches[3], '"' );
                        $this->addAutoIncrementField( $db, $tableName, $autoIncrementFieldName, $autoIncrementFieldType );
                    }
                }
                $db->commit();
                $db->beginTransaction();
            }
        }
        $db->commit();
    }

    /**
    * Performs changing field in Oracle table.
    * (workaround for "ALTER TABLE table MODIFY field fieldType AUTO_INCREMENT " that not alowed in Oracle ).
    * 
    * @param ezcDbHandler    $db
    * @param string          $tableName
    * @param string          $autoIncrementFieldName
    * @param string          $autoIncrementFieldType
    */
    private function addAutoIncrementField( $db, $tableName, $autoIncrementFieldName, $autoIncrementFieldType )
    {
        // fetching field info from Oracle, getting column position of autoincrement field

        // @apichange This code piece would become orphan, with the new 
        // implementation. We still need it to drop the old sequences.
        // Remove until --END-- to not take care of them.
        $resultArray = $db->query( "SELECT   a.column_name AS field, " .    
                                   "         a.column_id AS field_pos " .
                                   "FROM     user_tab_columns a " .
                                   "WHERE    a.table_name = '{$tableName}' AND a.column_name = '{$autoIncrementFieldName}'" .
                                   "ORDER BY a.column_id" );
        $resultArray->setFetchMode( PDO::FETCH_ASSOC );

        if ( count( $resultArray) != 1 )
        {
            return;
        }

        $result = $resultArray->fetch();
        $fieldPos = $result['field_pos'];

        // emulation of autoincrement through adding sequence, trigger and constraint
        $oldName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName( $tableName, $fieldPos, "seq" );
        $oldNameTrigger = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName( $tableName, $fieldPos, "trg" );
        $sequence = $db->query( "SELECT sequence_name FROM user_sequences WHERE sequence_name = '{$oldName}'" )->fetchAll();
        if ( count( $sequence) > 0  )
        {
            // assuming that if the seq exists, the trigger exists too
            $db->query( "DROP SEQUENCE \"{$oldName}\"" );
            $db->query( "DROP TRIGGER \"{$oldNameTrigger}\"" );
        }
        // --END--

        // New sequence names, using field names
        $newName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName( $tableName, $fieldPos, "seq" );
        $newNameTrigger = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName( $tableName, $fieldPos, "seq" );
        // Emulation of autoincrement through adding sequence, trigger and constraint
        $sequences = $db->query( "SELECT sequence_name FROM user_sequences WHERE sequence_name = '{$newName}'" )->fetchAll();
        if ( count( $sequences ) > 0  )
        {
            $db->query( "DROP SEQUENCE \"{$newName}\"" );
        }

        $db->exec( "CREATE SEQUENCE \"{$newName}\" start with 1 increment by 1 nomaxvalue" );
        $db->exec( "CREATE OR REPLACE TRIGGER \"{$newNameTrigger}\" ".
                                  "before insert on \"{$tableName}\" for each row ".
                                  "begin ".
                                  "select \"{$newName}\".nextval into :new.\"{$autoIncrementFieldName}\" from dual; ".
                                  "end;" );

        $constraintName = ezcDbSchemaOracleHelper::generateSuffixedIdentName( array( $tableName ), "pkey" );
        $constraint = $db->query( "SELECT constraint_name FROM user_cons_columns WHERE constraint_name = '{$constraintName}'" )->fetchAll();
        if ( count( $constraint) > 0  )
        {
            $db->query( "ALTER TABLE \"$tableName\" DROP CONSTRAINT \"{$constraintName}\"" );
        }
        $db->exec( "ALTER TABLE \"{$tableName}\" ADD CONSTRAINT \"{$constraintName}\" PRIMARY KEY ( \"{$autoIncrementFieldName}\" )" );
        $this->context['skip_primary'] = true;
    }

    /**
     * Returns the differences definition in $dbSchema as database specific SQL DDL queries.
     *
     * @param ezcDbSchemaDiff $dbSchemaDiff
     * @param ezcDbHandler    $db
     *
     * @return array(string)
     */
    public function convertDiffToDDL( ezcDbSchemaDiff $dbSchemaDiff, ezcDbHandler $db = null )
    {
        $this->diffSchema = $dbSchemaDiff;

        // reset queries
        $this->queries = array();
        $this->context = array();

        // Find sequences which require explicit drop statesments, see bug 
        // #16222
        if ( $db !== null )
        {
            $this->generateAdditionalDropSequenceStatements( $dbSchemaDiff, $db );
        }

        $this->generateDiffSchemaAsSql();
        return $this->queries;
    }

    /**
     * Generate additional drop sequence statements
     *
     * Some sequences might not be dropped automatically, this method generates 
     * additional DROP SEQUENCE queries for those.
     *
     * Since Oracle only allows sequence identifiers up to 30 characters 
     * sequences for long table / column names may be shortened. In this case 
     * the sequence name does not started with the table name any more, thus 
     * does not get dropped together with the table automatically.
     *
     * This method requires a DB connection to check which sequences have been 
     * defined in the database, because the information about fields is not 
     * available otherwise.
     * 
     * @param ezcDbSchemaDiff $dbSchemaDiff 
     * @param ezcDbHandler $db 
     * @return void
     */
    protected function generateAdditionalDropSequenceStatements( ezcDbSchemaDiff $dbSchemaDiff, ezcDbHandler $db )
    {
        $reader = new ezcDbSchemaOracleReader();
        $schema = $reader->loadFromDb( $db )->getSchema();
        foreach ( $dbSchemaDiff->removedTables as $table => $true )
        {
            foreach ( $schema[$table]->fields as $name => $field )
            {
                if ( $field->autoIncrement !== true )
                {
                    continue;
                }

                $seqName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName( $table, $name, "seq" );
                if ( strpos( $seqName, $table ) !== 0 )
                {
                    $this->queries[] = "DROP SEQUENCE \"$seqName\"";
                }
            }
        }
    }

    /**
     * Adds a "drop table" query for the table $tableName to the internal list of queries.
     *
     * @param string $tableName
     */
    protected function generateDropTableSql( $tableName )
    {
        $this->queries[] = "DROP TABLE \"$tableName\"";
    }

    /**
     * Converts the generic field type contained in $fieldDefinition to a database specific field definition.
     *
     * @param ezcDbSchemaField $fieldDefinition
     * @return string
     */
    protected function convertFromGenericType( ezcDbSchemaField $fieldDefinition )
    {
        $typeAddition = '';
        if ( in_array( $fieldDefinition->type, array( 'decimal', 'text' ) ) )
        {
            if ( $fieldDefinition->length !== false && $fieldDefinition->length !== 0 )
            {
                $typeAddition = "({$fieldDefinition->length})";
            } 
            else
            {
                $typeAddition = "(4000)"; // default length for varchar2 in Oracle
            }
        }
        if ( $fieldDefinition->type == 'boolean' )
        {
            $typeAddition = "(1)";
            if ( $fieldDefinition->default )
            {
                $fieldDefinition->default = ( $fieldDefinition->default == 'true' ) ? '1': '0';
            }
        }

        if ( !isset( $this->typeMap[$fieldDefinition->type] ) )
        {
            throw new ezcDbSchemaUnsupportedTypeException( 'Oracle', $fieldDefinition->type );
        }
        $type = $this->typeMap[$fieldDefinition->type];

        return "$type$typeAddition";
    }

    /**
     * Adds a "create table" query for the table $tableName with 
     * definition $tableDefinition to the internal list of queries.
     * 
     * Adds additional CREATE queries for sequences and triggers
     * to implement autoincrement fields that not supported in Oracle directly.
     * 
     * @param string           $tableName
     * @param ezcDbSchemaTable $tableDefinition
     */
    protected function generateCreateTableSql( $tableName, ezcDbSchemaTable $tableDefinition )
    {
        $sql = '';
        $sql .= "CREATE TABLE \"{$tableName}\" (\n";
        $this->context['skip_primary'] = false;

        // dump fields
        $fieldsSQL = array();
        $autoincrementSQL = array();
        $fieldCounter = 1;

        foreach ( $tableDefinition->fields as $fieldName => $fieldDefinition )
        {
            $fieldsSQL[] = "\t" . $this->generateFieldSql( $fieldName, $fieldDefinition );

            if ( $fieldDefinition->autoIncrement && !$this->context['skip_primary'] )
            {
                $sequenceName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName( $tableName, $fieldName, "seq" );
                $triggerName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName( $tableName, $fieldName, "trg" );
                $constraintName = ezcDbSchemaOracleHelper::generateSuffixedIdentName( array( $tableName ), "pkey" );
                $autoincrementSQL[] = "CREATE SEQUENCE \"{$sequenceName}\" start with 1 increment by 1 nomaxvalue";
                $autoincrementSQL[] = "CREATE OR REPLACE TRIGGER \"{$triggerName}\" ".
                                          "before insert on \"{$tableName}\" for each row ".
                                          "begin ".
                                          "select \"{$sequenceName}\".nextval into :new.\"{$fieldName}\" from dual; ".
                                          "end;";
                $autoincrementSQL[] = "ALTER TABLE \"{$tableName}\" ADD CONSTRAINT \"{$constraintName}\" PRIMARY KEY ( \"{$fieldName}\" )";
                $this->context['skip_primary'] = true;
            }
            $fieldCounter++;
        }

        $sql .= join( ",\n", $fieldsSQL );
        $sql .= "\n)";

        $this->queries[] = $sql;

        if ( count( $autoincrementSQL ) > 0 ) // adding autoincrement emulation queries if exists
        {
            $this->queries = array_merge( $this->queries, $autoincrementSQL );
        }

        // dump indexes
        foreach ( $tableDefinition->indexes as $indexName => $indexDefinition)
        {
            $fieldsSQL[] = $this->generateAddIndexSql( $tableName, $indexName, $indexDefinition );
        }
    }

    /**
     * Generates queries to upgrade a the table $tableName with the differences in $tableDiff.
     *
     * This method generates queries to migrate a table to a new version
     * with the changes that are stored in the $tableDiff property. It
     * will call different subfunctions for the different types of changes, and
     * those functions will add queries to the internal list of queries that is
     * stored in $this->queries.
     *
     * @param string $tableName
     * @param ezcDbSchemaTableDiff $tableDiff
     */
    protected function generateDiffSchemaTableAsSql( $tableName, ezcDbSchemaTableDiff $tableDiff )
    {
        $this->context['skip_primary'] = false;
        parent::generateDiffSchemaTableAsSql( $tableName, $tableDiff );
    }

    /**
     * Adds a "alter table" query to add the field $fieldName to $tableName with the definition $fieldDefinition.
     *
     * @param string           $tableName
     * @param string           $fieldName
     * @param ezcDbSchemaField $fieldDefinition
     */
    protected function generateAddFieldSql( $tableName, $fieldName, ezcDbSchemaField $fieldDefinition )
    {
        $this->queries[] = "ALTER TABLE \"$tableName\" ADD " . $this->generateFieldSql( $fieldName, $fieldDefinition );
    }

    /**
     * Adds a "alter table" query to change the field $fieldName to $tableName with the definition $fieldDefinition.
     *
     * @param string           $tableName
     * @param string           $fieldName
     * @param ezcDbSchemaField $fieldDefinition
     */
    protected function generateChangeFieldSql( $tableName, $fieldName, ezcDbSchemaField $fieldDefinition )
    {
        if ( !$fieldDefinition->autoIncrement )
        {
            $this->queries[] = "ALTER TABLE \"$tableName\" MODIFY " .
                               $this->generateFieldSql( $fieldName, $fieldDefinition );
        }
        else
        {    // mark query to make autoincrement emulation when executing
            $this->queries[] = "ALTER TABLE \"$tableName\" MODIFY " .
                               $this->generateFieldSql( $fieldName, $fieldDefinition ) .
                               " AUTO_INCREMENT";
        }
    }

    /**
     * Adds a "alter table" query to drop the field $fieldName from $tableName.
     *
     * @param string $tableName
     * @param string $fieldName
     */
    protected function generateDropFieldSql( $tableName, $fieldName )
    {
        $this->queries[] = "ALTER TABLE \"$tableName\" DROP COLUMN \"$fieldName\"";
    }

    /**
     * Returns a column definition for $fieldName with definition $fieldDefinition.
     *
     * @param  string           $fieldName
     * @param  ezcDbSchemaField $fieldDefinition
     * @param  string           $autoincrementField
     * @return string
     */
    protected function generateFieldSql( $fieldName, ezcDbSchemaField $fieldDefinition )
    {
        $sqlDefinition = '"'.$fieldName.'" ';
        $defList = array();

        $type = $this->convertFromGenericType( $fieldDefinition );
        $defList[] = $type;

        if ( !is_null( $fieldDefinition->default ) && !$fieldDefinition->autoIncrement )
        {
            $default = $this->generateDefault( $fieldDefinition->type, $fieldDefinition->default );
            $defList[] = "DEFAULT $default";
        }

        if ( $fieldDefinition->notNull )
        {
            $defList[] = 'NOT NULL';
        }

        $sqlDefinition .= join( ' ', $defList );

        return $sqlDefinition;
    }

    /**
     * Returns an appropriate default value for $type with $value.
     *
     * @param string $type
     * @param mixed  $value
     * @return string
     */
    protected function generateDefault( $type, $value )
    {
        switch ( $type )
        {
            case 'boolean':
                return ( $value && $value != 'false' ) ? '1' : '0';

            case 'integer':
                return (int) $value;

            case 'float':
            case 'decimal':
                return (float) $value;

            default:
                return "'$value'";
        }
    }

    /**
     * Adds a "alter table" query to add the index $indexName to the table $tableName with definition $indexDefinition to the internal list of queries
     *
     * @param string           $tableName
     * @param string           $indexName
     * @param ezcDbSchemaIndex $indexDefinition
     */
    protected function generateAddIndexSql( $tableName, $indexName, ezcDbSchemaIndex $indexDefinition )
    {

        $sql = "";
        if ( $indexDefinition->primary )
        {
            if ( $this->context['skip_primary'] )
            {
                return;
            }
            $sql = "ALTER TABLE \"$tableName\" ADD CONSTRAINT \"{$tableName}_pkey\" PRIMARY KEY";
        }
        else if ( $indexDefinition->unique )
        {
            $sql = "CREATE UNIQUE INDEX \"$indexName\" ON \"$tableName\"";
        }
        else
        {
            $sql = "CREATE INDEX \"$indexName\" ON \"$tableName\"";
        }

        $sql .= " ( ";

        $indexFieldSql = array();
        foreach ( $indexDefinition->indexFields as $indexFieldName => $dummy )
        {
                $indexFieldSql[] = "\"$indexFieldName\"";
        }
        $sql .= join( ', ', $indexFieldSql ) . " )";

        $this->queries[] = $sql;
    }
    
    /**
     * Adds a "alter table" query to remote the index $indexName from the table $tableName to the internal list of queries.
     *
     * @param string           $tableName
     * @param string           $indexName
     */
    protected function generateDropIndexSql( $tableName, $indexName )
    {
        if ( $indexName == 'primary' ) // handling primary indexes
        {
            $constraintName = ezcDbSchemaOracleHelper::generateSuffixedIdentName( array( $tableName ), "pkey");
            $this->queries[] = "ALTER TABLE \"$tableName\" DROP CONSTRAINT \"{$constraintName}\"";
        }
        else
        {
            $this->queries[] = "DROP INDEX \"$indexName\"";
        }
    }
}
?>