DALOracleConverter.inc

<?php
require_once dirname(__FILE__).'/DALConverter.inc';

class DALOracleConverter extends DALConverter
{

    protected $name = 'Oracle';

    protected $dataTypes = array(
                            'char'                => 'CHAR',
                            'varchar'             => 'VARCHAR2',
                            'clob'                => 'CLOB',
                            'blob'                => 'BLOB',
                            'integer'             => 'INT',
                            'smallint'            => 'SMALLINT',
                            'numeric'             => 'NUMERIC',
                            'float'               => 'FLOAT',
                            'real'                => 'REAL',
                            'double_precision'    => 'DOUBLE PRECISION',
                            'boolean'             => 'CHAR(1)',
                            'date'                => 'DATE',
                            'time'                => 'TIMESTAMP',
                            'timestamp'           => 'TIMESTAMP',
                            'interval'            => '',
                            'time with time zone' => 'TIMESTAMP',
                           );


    public function __construct()
    {

    }//end __construct()


    public function convertCreateTable(array $table)
    {
        $sql  = $this->convertCreateHeader($table);
        $sql .= "\n".$this->convertCreateColumns($table).',';
        $sql .= "\n".$this->convertConstraints($table['CONSTRAINTS']);
        $sql .= $this->convertCreateFooter($table);

        $indexes = $this->convertCreateIndexes($table);
        if ($indexes !== '') {
            $sql .= "\n".$indexes;
        }

        if (isset($table['SEQUENCES']) === TRUE) {
            $sql .= "\n".$this->convertCreateSequences($table['SEQUENCES']);
        }

        return $sql;

    }//end convertCreateTable()


    protected function convertSingleCreateColumn(array $col)
    {
        if (strtolower($col['type']) === 'boolean') {
            if ($col['default'] !== '') {
                if (strtolower($col['default']) === 'false') {
                    $col['default'] = "'0'";
                } else if (strtolower($col['default']) === 'true') {
                    $col['default'] = "'1'";
                }
            }
        }

        return parent::convertSingleCreateColumn($col);

    }//end convertSingleCreateColumn()


    protected function handleFunctionSeqNextVal($seqName)
    {
        $sql = $seqName[0].'.nextval';
        return $sql;

    }//end handleFunctionSeqNextVal()


    protected function handleFunctionSeqCurrVal($seqName)
    {
        $sql = $seqName[0].'.currval';
        return $sql;

    }//end handleFunctionSeqCurrVal()


    public function handleFunctionTableExists($tableName, $schema=NULL)
    {
        if (is_array($tableName) === TRUE) {
            $tableName = $tableName[0];
        }

        $sql = 'SELECT count(table_name) FROM user_tables WHERE table_name = UPPER('.$tableName.')';
        return $sql;

    }//end handleFunctionTableExists()


    public function handleFunctionSequenceExists($sequenceName)
    {
        if (is_array($sequenceName) === TRUE) {
            $sequenceName = $sequenceName[0];
        }

        $sql = 'SELECT count(sequence_name) FROM user_sequences WHERE sequence_name = UPPER('.$sequenceName.')';
        return $sql;

    }//end handleFunctionSequenceExists()


    protected function handleFunctionToDate(array $args)
    {
        $iso8601_date = $this->convertSingleField($args[0]);
        $sql = 'TO_DATE('.$iso8601_date.', \'YYYY-MM-DD HH24:MI:SS\')';
        return $sql;

    }//end handleFunctionToDate()


    protected function convertWithQuery(array $withQuery)
    {
        $firstSelectQuery = $withQuery['AFTER'];

        $sql  = '';
        $sql .= $this->convertSelectQuery($firstSelectQuery, 0, array('where'));

        $secondSelectQuery = $withQuery['UNION-ALL']['SELECTS'][1];

        $sql .= 'CONNECT BY PRIOR ';
        $sql .= $this->convertWhereClause($withQuery['CONNECT-BY-PRIOR'], 0, FALSE);

        $sql .= 'START WITH ';
        if (isset($firstSelectQuery['WHERE']) === TRUE) {
            $sql .= $this->convertWhereClause($firstSelectQuery['WHERE'], 0, FALSE);
        }

        return $sql;

    }//end convertWithQuery()


    public function convertDropTable($tableName, $cascade=FALSE)
    {
        $sql = 'DROP TABLE '.$tableName;
        if ($cascade === TRUE) {
            $sql .= ' CASCADE CONSTRAINTS';
        }

        return $sql;

    }//end convertDropTable()


    protected function convertSelectQuery(array $sqlArray, $level=0, array $ignore=array())
    {
        // If there are no tables specified in a SELECT query,
        // use DUAL automatically.
        if (empty($sqlArray['FROM'])) {
            $sqlArray['FROM'] = Array('dual');
        }

        $sql = parent::convertSelectQuery($sqlArray, $level, $ignore);

        // Start with.
        if (isset($sqlArray['START-WITH']) === TRUE) {
            $sql      .= "\n".$this->getConstructName('start-with');
            $startWith = $this->convertWhereClause($sqlArray['START-WITH'], $level);
            $startWith = trim($startWith);
            $startWith = trim($startWith, '()');
            $sql      .= "\n".$startWith;
        }

        // Start with.
        if (isset($sqlArray['CONNECT-BY']) === TRUE) {
            $sql      .= "\n".$this->getConstructName('connect-by');
            $connectBy = $this->convertWhereClause($sqlArray['CONNECT-BY'], $level);
            $connectBy = trim($connectBy);
            $connectBy = trim($connectBy, '()');
            $sql      .= "\n".$connectBy;
        }

        return $sql;

    }//end convertSelectQuery()


    protected function convertTableAlias($alias)
    {
        return ' '.$alias;

    }//end convertTableAlias()


    protected function convertUnionsInFromClause(array $union, $level=0)
    {
        $type = $this->getUnionType($union);
        $sql  = "(\n".$this->addSpaces($level);
        $sql .= $this->convertUnions($union, ($level + 2));
        $sql .= ') '.$union[$type]['alias'];
        return $sql;

    }//end convertUnionsInFromClause()


    protected function convertLimit(array $limit)
    {
        $sql  = ')';
        $sql .= ' WHERE row_num >= '.($limit['offset'] + 1);

        return $sql;

    }//end convertLimit()


    protected function convertSelectFieldList(array $fields, $level=1)
    {
        $sql = '';
        // Get Select field names.
        if (isset($this->query['LIMIT']) === TRUE) {
            $names    = $this->getSelectFieldNames($fields);
            $namesStr = array();
            $counter  = 0;
            foreach ($names as $name) {
                $namesStr[] = 'col_'.($counter++).' as '.$name;
            }

            $nameStr = implode(', ', $namesStr);
            $sql     = $nameStr.' FROM (SELECT ';

            $counter = 0;
            foreach ($fields as $field) {
                $field  = $this->setSelectFieldAlias($field, ('col_'.$counter++));
                $list[] = $this->convertSingleField($field);
            }

            $sql .= $this->addSpaces($level).$this->separateFields($list);
            $sql .= ', rownum as row_num';

        } else {
            $list = array();
            foreach ($fields as $field) {
                $list[] = $this->convertSingleField($field);
            }

            $sql = $this->addSpaces($level).$this->separateFields($list);
        }//end if


        return $sql;

    }//end convertSelectFieldList()


    protected function addSqlFilters(array $sqlArray)
    {
        $sql = '';

        if (isset($this->query['LIMIT']) === TRUE) {
            $limit = $this->query['LIMIT'];
            if (isset($this->query['WHERE']) === TRUE) {
                $sql .= ' AND ';
            } else {
                $sql .= ' WHERE ';
            }

            $sql .= ' rownum  <= '.($limit['rows'] + $limit['offset']);
        }

        // Group by.
        if (isset($sqlArray['GROUP-BY']) === TRUE) {
            $sql .= $this->convertGroupBy($sqlArray['GROUP-BY']);
        }

        // Having.
        if (isset($sqlArray['HAVING']) === TRUE) {
            $sql .= "\n".$this->getConstructName('having');
            $sql .= "\n".$this->convertWhereClause($sqlArray['HAVING']);
        }

        // Order By.
        if (isset($sqlArray['ORDER-BY']) === TRUE) {
            $sql .= "\n".$this->convertOrderBy($sqlArray['ORDER-BY']);
        }

        // Limit.
        if (isset($sqlArray['LIMIT']) === TRUE) {
            $sql .= "\n".$this->convertLimit($sqlArray['LIMIT']);
        }

        return $sql;

    }//end addSqlFilters()


    protected function handleFunctionSubstring(array $args)
    {
        // SUBSTRING(input FROM start-position [FOR length])
        if (count($args) != 2 && count($args) != 3) {
            $msg = 'The SUBSTR() function only accepts two or three parameters.';
            throw new DALConverterException($msg);
        }

        $input = $this->convertSingleField($args[0]);
        $start = $args[1];
        $length = isset($args[2]) ? ",$args[2]" : '';
        $sql = "SUBSTR($input, $start $length)";

        return $sql;

    }//end handleFunctionSubstring()


}//end class
?>