DALMssqlConverter.inc

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

class DALMssqlConverter extends DALConverter
{

    protected $tableName = '';

    protected $name = 'MSSQL';

    protected $dataTypes = array(
                            'char'                => 'NCHAR',
                            'varchar'             => 'NVARCHAR',
                            'clob'                => 'NVARCHAR(4000)',
                            'blob'                => 'VARBINARY',
                            'integer'             => 'INT',
                            'smallint'            => 'SMALLINT',
                            'numeric'             => 'NUMERIC',
                            'float'               => 'FLOAT',
                            'real'                => 'REAL',
                            'double_precision'    => 'FLOAT',
                            'boolean'             => 'CHAR(1)',
                            'date'                => 'DATETIME',
                            'time'                => 'DATETIME',
                            'timestamp'           => 'DATETIME',
                            'time with time zone' => 'DATETIME',
                           );


    public function __construct()
    {

    }//end __construct()


    public function convertCreateTable(array $table)
    {
        $this->tableName = $table['table'];

        $sql  = $this->convertCreateHeader($table);
        $sql .= "\n".$this->convertCreateColumns($table);
        $sql .= $this->convertCreateFooter($table);

        if (isset($table['CONSTRAINTS']) === TRUE && empty($table['CONSTRAINTS']) === FALSE) {
            $constraints = $this->convertAlterQueryAddConstraint($table['CONSTRAINTS'], TRUE);
            $sql        .= "\n".$constraints;
        }

        $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 convertAlterQueryAddConstraint(array $constraints, $incHeader=FALSE)
    {
        $sql = '';
        if (empty($constraints) === FALSE) {
            $convertedCons = $this->convertConstraints($constraints, TRUE);
            $cons          = array();
            foreach ($convertedCons as $constraint) {
                $sql = '';
                if ($incHeader === TRUE) {
                    $sql .= $this->convertAlterQueryHeader($this->tableName);
                }

                $sql .= 'ADD '.$constraint;

                $cons[] = $sql;
            }

            $sql = implode(";\n", $cons).';';
        }

        return $sql;

    }//end convertAlterQueryAddConstraint()


    protected function convertCreateIndexes(array $table)
    {
        $sql     = '';
        $indexes = array();
        foreach ($table['INDEXES'] as $index) {
            $indexes[] = $this->convertSingleIndex($index, $table['table']);
        }

        if (empty($indexes) === FALSE) {
            $sql .= implode(";\n", $indexes).';';
        }

        return $sql;

    }//end convertCreateIndexes()


    protected function convertSingleIndex(array $idx, $tableName)
    {
        $sql  = 'CREATE INDEX '.$idx['name'].' ON '.$tableName.' (';
        $sql .= $this->separateFields($idx['COLUMNS']).')';
        return $sql;

    }//end convertSingleIndex()


    protected function convertSingleSequence($sequence)
    {
        // Create a new sequence table and a row.
        $sql = 'CREATE TABLE '.$sequence.' ( id INT NOT NULL);
        INSERT INTO '.$sequence.' VALUES(0);';

        return $sql;

    }//end convertSingleSequence()


    protected function handleFunctionSeqNextVal($seqName)
    {
        $sql = 'UPDATE '.$seqName[0].' SET id=id+1';
        return $sql;

    }//end handleFunctionSeqNextVal()


    protected function handleFunctionSeqCurrVal($seqName)
    {
        $sql = 'SELECT TOP 1 id FROM '.$seqName[0].'';
        return $sql;

    }//end handleFunctionSeqCurrVal()


    public function handleFunctionSequenceExists($sequenceName)
    {
        return $this->handleFunctionTableExists($sequenceName);

    }//end handleFunctionSequenceExists()


    public function convertDropSequence($sequenceName)
    {
        $sql = $this->convertDropTable($sequenceName);
        return $sql;

    }//end convertDropSequence()


    protected function handleFunctionConcat(array $args)
    {
        $sql = implode(' + ', $args);
        return $sql;

    }//end handleFunctionConcat()


    protected function handleFunctionToDate(array $args)
    {
        $iso8601_date = $this->convertSingleField($args[0]);

        // Explicitly convert using the "yyyy-mm-dd HH:mi:ss" style (which
        // is what the '120' magic number is for).
        $sql = 'CONVERT(DATETIME, '.$iso8601_date.', 120)';
        return $sql;

    }//end handleFunctionToDate()


    protected function convertUnionDisplayName($type)
    {
        if (strtoupper($type) === 'UNION-ALL') {
            return 'UNION ALL';
        }

        return $type;

    }//end convertUnionDisplayName()


    protected function convertInsertQueryValues(array $insert)
    {
        $sql = '';
        if (isset($insert['VALUES']['SELECT']) === FALSE) {
            $valuesList = array();
            foreach ($insert['FIELDS'] as $field) {
                if (isset($insert['VALUES'][$field]) === TRUE) {
                    $value = $insert['VALUES'][$field];
                } else {
                    $msg  = 'Could not convert INSERT query.';
                    $msg .= ' Field "'.$field.'" not found in values list.';
                    throw new DALConverterException($msg);
                }

                $valuesList[] = $this->convertSingleField($value);
            }

            $sql .= 'VALUES ('.$this->separateFields($valuesList).') ';
        } else {
            // Select statement.
            $sql .= $this->convertSelectQuery($insert['VALUES']);
        }

        return $sql;

    }//end convertInsertQueryValues()


    protected function convertCallQuery(array $callQuery)
    {
        $sql  = 'EXEC ';
        $sql .= $this->convertSingleFunction($callQuery, TRUE);
        return $sql;

    }//end convertCallQuery()


    protected function convertSingleFunction(array $function, $isProcedure=FALSE)
    {
        $sql       = '';
        $fnName    = $function['FUNCTION']['function'];
        $fnHandler = 'handleFunction'.$fnName;
        $args      = array();
        foreach ($function['FUNCTION']['ARGS'] as $arg) {
            $args[] = $this->convertSingleField($arg);
        }

        if (method_exists($this, $fnHandler) === FALSE) {
            $sql .= $function['FUNCTION']['function'];
            if ($isProcedure === TRUE) {
                $sql .= ' ';
            } else {
                $sql .= '(';
            }

            $sql .= $this->separateFields($args);

            if ($isProcedure === TRUE) {
                $sql .= ' ';
            } else {
                $sql .= ')';
            }

        } else {
            $sql .= $this->$fnHandler($args);
        }

        if (isset($function['FUNCTION']['alias']) === TRUE) {
            $sql .= ' as '.$function['FUNCTION']['alias'];
        }

        return $sql;

    }//end convertSingleFunction()


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

        if ($schema === NULL) {
            $schema = '\''.DAL::getDbName().'\'';
        }

        $sql = 'SELECT count(*) FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = '.$tableName.' AND TABLE_SCHEMA = \'dbo\' AND TABLE_CATALOG = '.$schema;
        return $sql;

    }//end handleFunctionTableExists()


    protected function convertSelectFieldList(array $fields, $level=1)
    {
        $sql      = '';
        $sqlFlist = '';
        if (isset($this->query['LIMIT']) === TRUE) {
            $offset = $this->query['LIMIT']['offset'];
            if ($offset === 0) {
                $offset = 1;
            }

            if (isset($this->query['ORDER-BY']) === TRUE) {
                $orderBy = $this->query['ORDER-BY'];
                foreach ($orderBy as $key => $field) {
                    if ($key !== 'direction') {
                        if (is_array($field) === TRUE) {
                            $fields[] = $this->convertSingleField($field);
                        } else {
                            $fields[] = $field;
                        }
                    }
                }
            }

            $sql .= '* FROM (SELECT TOP '.$offset;
            $sql .= ' * FROM (';

            $sqlFlist = 'SELECT TOP '.$this->query['LIMIT']['rows'].' ';
        }//end if

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

        $list = array_unique($list);

        $sqlFlist .= $this->addSpaces($level).$this->separateFields($list);


        $sql .= $sqlFlist;


        return $sql;

    }//end convertSelectFieldList()


    protected function convertLimit(array $limit)
    {
        // Reverse order by direction.
        $sql = '';
        if (isset($this->query['ORDER-BY']) === TRUE) {
            $orderBy = $this->query['ORDER-BY'];

            foreach ($orderBy as $key => $field) {
                if (isset($field['table']) === TRUE) {
                    $field['table'] = '';
                }

                $orderBy[$key] = $field;
            }

            for ($i = 0; $i < 2; $i++) {
                if (isset($orderBy['direction']) === TRUE) {
                    if (strtolower($orderBy['direction']) === 'desc') {
                        $orderBy['direction'] = '';
                    } else {
                        $orderBy['direction'] = 'desc';
                    }
                }

                $sql .= ') as limCl '.$this->convertOrderBy($orderBy);
            }
        }//end if

        return $sql;

    }//end convertLimit()


    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 = "SUBSTRING($input, $start $length)";

        return $sql;

    }//end handleFunctionSubstring()


}//end class
?>