DALConverter.inc

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

class DALConverter
{

    protected $dataTypes = array(
                            'char'             => 'CHAR',
                            'varchar'          => 'VARCHAR',
                            'clob'             => 'TEXT',
                            'blob'             => 'BYTEA',
                            'integer'          => 'INT',
                            'smallint'         => 'SMALLINT',
                            'numeric'          => 'NUMERIC',
                            'float'            => 'FLOAT',
                            'real'             => 'REAL',
                            'double_precision' => 'DOUBLE PRECISION',
                            'boolean'          => 'BOOLEAN',
                            'date'             => 'DATE',
                            'time'             => 'TIME',
                            'timestamp'        => 'TIMESTAMP',
                            'interval'         => 'INTERVAL',
                           );

    protected $sqlConstructs = array(
                                'select'     => 'SELECT',
                                'from'       => 'FROM',
                                'where'      => 'WHERE',
                                'in'         => 'IN',
                                'not-in'     => 'NOT IN',
                                'limit'      => 'LIMIT',
                                'group-by'   => 'GROUP BY',
                                'having'     => 'HAVING',
                                'order-by'   => 'ORDER BY',
                                'exists'     => 'EXISTS',
                                'not-exists' => 'NOT EXISTS',
                                'start-with' => 'START WITH',
                                'connect-by' => 'CONNECT BY PRIOR',
                               );

    protected $mathOps = array(
                          'SUBTRACT' => '-',
                          'ADD'      => '+',
                          'MULTIPLY' => '*',
                          'DIVIDE'   => '/',
                         );

    protected $query = array();

    protected $name = '';

    private $_whereConditions = array(
                                 'equal'            => '=',
                                 'not-equal'        => '!=',
                                 'equal-or-greater' => '>=',
                                 'equal-or-less'    => '<=',
                                 'greater'          => '>',
                                 'less'             => '<',
                                 'in'               => 'IN',
                                 'not-in'           => 'NOT IN',
                                 'like'             => 'LIKE',
                                 'not-like'         => 'NOT LIKE',
                                 'is-null'          => 'IS NULL',
                                 'not-null'         => 'NOT NULL',
                                );


    public function getDataType($type)
    {
        $dataType = strtolower($type);
        if (isset($this->dataTypes[$dataType]) === TRUE) {
            $dataType = $this->dataTypes[$dataType];
        }

        return $dataType;

    }//end getDataType()


    public function getName()
    {
        return $this->name;

    }//end getName()


    public function getConstructName($name)
    {
        $construct = '';
        if (isset($this->sqlConstructs[$name]) === TRUE) {
            $construct = $this->sqlConstructs[$name];
        }

        return $construct;

    }//end getConstructName()


    public function convertToSql(array $sqlArray)
    {
        $this->query = $sqlArray;

        $sql = '';
        if (isset($sqlArray['SELECT']) === TRUE) {
            $sql = $this->convertSelectQuery($sqlArray);
        } else if (isset($sqlArray['INSERT']) === TRUE) {
            $sql = $this->convertInsertQuery($sqlArray);
        } else if (isset($sqlArray['UPDATE']) === TRUE) {
            $sql = $this->convertUpdateQuery($sqlArray);
        } else if (isset($sqlArray['DELETE']) === TRUE) {
            $sql = $this->convertDeleteQuery($sqlArray);
        } else if (isset($sqlArray['TRUNCATE']) === TRUE) {
            $tableName = $sqlArray['TRUNCATE']['from'];
            $sql = $this->convertTruncateTable($tableName);
        } else if (isset($sqlArray['ALTER']) === TRUE) {
            $sql = $this->convertAlterQuery($sqlArray);
        } else if (isset($sqlArray['FUNCTION']) === TRUE) {
            $sql = $this->convertSingleFunction($sqlArray);
        } else if (isset($sqlArray['DROP']) === TRUE) {
            if (isset($sqlArray['DROP']['TABLE']) === TRUE) {
                $sql = $this->convertDropTable($sqlArray['DROP']['TABLE']);
            } else if (isset($sqlArray['DROP']['SEQUENCE']) === TRUE) {
                $sql = $this->convertDropSequence($sqlArray['DROP']['SEQUENCE']);
            }
        } else if (isset($sqlArray['WITH']) === TRUE) {
            $sql = $this->convertWithQuery($sqlArray['WITH']);
        } else if (isset($sqlArray['CALL']) === TRUE) {
            $sql = $this->convertCallQuery($sqlArray['CALL']);
        } else if (isset($sqlArray['AND']) === TRUE) {
            $sqlArray = array('WHERE' => $sqlArray);
            $sql      = $this->convertWhereClause($sqlArray['WHERE']);
        } else if (isset($sqlArray['FROM']) === TRUE) {
            $sql = $this->convertFromList($sqlArray['FROM']);
        }//end if

        return $sql;

    }//end convertToSql()


    protected function convertSelectQuery(array $sqlArray, $level=0, array $ignore=array())
    {
        $sql  = $this->addSpaces($level);
        $sql .= $this->getConstructName('select');

        // Add Distinct clause?
        if (isset($sqlArray['SELECT']['distinct']) === TRUE) {
            if ($sqlArray['SELECT']['distinct'] === TRUE) {
                $sql .= ' DISTINCT ';
            }
        }

        $sql .= "\n".$this->convertSelectFieldList($sqlArray['SELECT']['FIELDS'], ($level + 1));
        $sql .= "\n".$this->addSpaces($level);
        if (isset($sqlArray['FROM']) === TRUE) {
            $sql .= $this->getConstructName('from');
            $sql .= $this->convertFromList($sqlArray['FROM'], ($level + 1));
        }

        $sql .= "\n".$this->addSpaces($level);

        if (in_array('join', $ignore) === FALSE) {
            $joins = '';
            if (isset($sqlArray['JOIN']) === TRUE) {
                $joins = $this->convertJoins($sqlArray['JOIN']);
            }

            if ($joins !== '') {
                $sql .= $this->getConstructName('where');
                $sql .= "\n".$joins."\n";
            }
        }

        if (in_array('where', $ignore) === FALSE) {
            if (isset($sqlArray['WHERE']) === TRUE) {
                if ($joins === '') {
                    $sql .= $this->getConstructName('where');
                } else {
                    $sql .= 'AND';
                }

                $sql .= "\n".$this->convertWhereClause($sqlArray['WHERE'], $level);
            }
        }

        $sql .= $this->addSqlFilters($sqlArray);

        return $sql;

    }//end convertSelectQuery()


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

        // 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 convertSelectFieldList(array $fields, $level=1)
    {
        $list = array();
        foreach ($fields as $field) {
            $list[] = $this->convertSingleField($field);
        }

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

        return $sql;

    }//end convertSelectFieldList()


    protected function convertSingleField($field)
    {
        $sql = '';
        if (is_array($field) === TRUE) {
            if (isset($field['FUNCTION']) === TRUE) {
                // Function call (i.e. CONCAT()).
                $sql = $this->convertSingleFunction($field);
            } else if (isset($field['table']) === TRUE) {
                // Table column.
                $sql = $this->convertField($field);
            } else if (isset($field['MATH-OP']) === TRUE) {
                $sql = $this->convertMathOperation($field);
            } else if (isset($field['SELECT']) === TRUE) {
                $sql = '('.$this->convertSelectQuery($field).')';
            } else {
                $sql = $this->convertSingleValue($field);
            }
        } else {
            // Must be a single value.
            $sql = $this->convertSingleValue($field);
        }

        return $sql;

    }//end convertSingleField()


    protected function getSelectFieldName(array $field)
    {
        $name = '';
        if (isset($field['FUNCTION']) === TRUE) {
            // Function call (i.e. CONCAT()).
            if (isset($function['FUNCTION']['alias']) === TRUE) {
                $name = $function['FUNCTION']['alias'];
            }
        } else if (isset($field['table']) === TRUE) {
            // Table column.
            if (isset($field['alias']) === TRUE) {
                $name = $field['alias'];
            } else {
                $name = $field['column'];
            }
        } else if (isset($field['MATH-OP']) === TRUE) {
            if (isset($mathOp['MATH-OP']['alias']) === TRUE) {
                $name = $mathOp['MATH-OP']['alias'];
            }
        } else if (isset($field['SELECT']) === TRUE) {
            $name = '';
        } else {
            if (isset($field['alias']) === TRUE) {
                $name = $field['alias'];
            } else {
                $name = $field['value'];
            }
        }//end if()

        return $name;

    }//end getSelectFieldName()


    protected function setSelectFieldAlias(array $field, $alias)
    {
        if (isset($field['FUNCTION']) === TRUE) {
            // Function call (i.e. CONCAT()).
            $function['FUNCTION']['alias'] = $alias;
        } else if (isset($field['table']) === TRUE) {
            // Table column.
            $field['alias'] = $alias;
        } else if (isset($field['MATH-OP']) === TRUE) {
            $mathOp['MATH-OP']['alias'] = $alias;
        } else if (isset($field['SELECT']) === TRUE) {
            // TODO: Aliases for SELECTs.
        } else {
            $field['alias'] = $alias;
        }

        return $field;

    }//end setSelectFieldAlias()


    protected function getSelectFieldNames(array $fields)
    {
        $names = array();
        foreach ($fields as $field) {
            $names[] = $this->getSelectFieldName($field);
        }

        return $names;

    }//end getSelectFieldNames()


    protected function convertField(array $field)
    {
        $sql = '';
        if ($field['table'] !== '') {
            $sql .= $field['table'];
            if ($field['column'] !== '') {
                $sql .= '.';
            }
        }

        if ($field['column'] !== '') {
            $sql .= $field['column'];
            if (isset($field['alias']) === TRUE) {
                $sql .= ' as '.$field['alias'];
            }
        }

        return $sql;

    }//end convertField()


    protected function convertSingleValue($field)
    {
        $sql = '';
        if (is_array($field) === TRUE) {
            if ((isset($field['value']) === TRUE) && (isset($field['alias']) === TRUE)) {
                $sql .= $field['value'].' as '.$field['alias'];
            }
        } else {
            if (empty($field) === FALSE) {
                $sql = $field;
            } else {
                if (is_numeric($field) === FALSE) {
                    $sql = "'".$field."'";
                } else {
                    $sql = $field;
                }
            }
        }

        return $sql;

    }//end convertSingleValue()


    protected function convertSingleFunction(array $function)
    {
        $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'].'(';
            $sql .= $this->separateFields($args);
            $sql .= ')';
        } else {
            $sql .= $this->$fnHandler($args);
        }

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

        return $sql;

    }//end convertSingleFunction()


    protected function convertFromList(array $from, $level=1)
    {
        $sql = "\n".$this->addSpaces($level);
        if ((isset($from['UNION']) !== TRUE) && (isset($from['UNION-ALL']) !== TRUE)) {
            $tables    = array();
            $subTables = '';
            foreach ($from as $table) {
                $newTable = '';
                if (is_array($table) === TRUE && isset($table['FROM']) === TRUE) {
                    $subTables = $this->convertFromList($table['FROM']);
                    if ($subTables !== '') {
                        $subTables = ','.$subTables;
                    }
                } else {
                    if (is_array($table) === TRUE) {
                        if (isset($table['name']) === TRUE) {
                            // This is could be a derived table.
                            if (is_array($table['name']) === TRUE && isset($table['name']['SELECT']) === TRUE) {
                                $newTable = '('.$this->convertSelectQuery($table['name']).')';
                            } else {
                                $newTable = $table['name'];
                            }

                            // Append the alias.
                            if (isset($table['alias']) === TRUE) {
                                $newTable .= $this->convertTableAlias($table['alias']);
                            }
                        } else if (isset($table['FUNCTION']) === TRUE) {
                            $newTable = $this->convertSingleFunction($table);
                        }
                    } else {
                        $newTable = $table;
                    }

                    if (strpos($newTable, 'HOOKID') === FALSE) {
                        $tables[] = $newTable;
                    }
                }//end if
            }//end foreach

            $sql .= $this->separateTables($tables).$subTables;
        } else {
            // We have unions in from clause.
            $sql .= $this->convertUnionsInFromClause($from);
        }//end if

        return $sql;

    }//end convertFromList()


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

    }//end convertTableAlias()


    protected function convertMathOperation(array $mathOp)
    {
        // If this is the begining tag then jump to the 1st op.
        $alias = '';
        if (isset($mathOp['MATH-OP']) === TRUE) {
            if (isset($mathOp['MATH-OP']['alias']) === TRUE) {
                $alias = ' as '.$mathOp['MATH-OP']['alias'];
            }

            $mathOp = $mathOp['MATH-OP'][0];
        }

        $operations = array();
        foreach ($mathOp as $currentOp => $args) {
            if (is_array($args) === TRUE) {
                foreach ($args as $value) {
                    $field = TRUE;
                    if (is_array($value) === TRUE) {
                        $keys = array_keys($value);
                        $op   = array_shift($keys);
                        if (isset($this->mathOps[$op]) === TRUE) {
                            $field        = FALSE;
                            $operations[] = $this->convertMathOperation($value);
                        }
                    }

                    if ($field === TRUE) {
                        if (is_array($value) === TRUE && isset($value['SELECT']) === TRUE) {
                            $operations[] = '('.$this->convertSelectQuery($value).')';
                        } else {
                            $operations[] = $this->convertSingleField($value);
                        }
                    }
                }
            }//end if
        }//end foreach

        $sql = '('.implode(' '.$this->mathOps[$currentOp].' ', $operations).')';
        // If we have a alias then we should have something like (3+4) as ALIAS.
        $sql .= $alias;

        return $sql;

    }//end convertMathOperation()


    protected function separateFields(array $fields)
    {
        return implode(', ', $fields);

    }//end separateFields()


    protected function separateTables(array $tables)
    {
        return implode(', ', $tables);

    }//end separateTables()


    protected function convertWhereClause(array $where, $level=0, $addBrackets=TRUE)
    {
        $sql = $this->addSpaces($level);
        if ($addBrackets === TRUE) {
            $sql .= "(\n";
        }

        $wc = $this->getComparisonOperators();
        foreach ($where as $condType => $conditions) {
            $beg = TRUE;
            foreach ($conditions as $condition) {
                $sql .= str_repeat(' ', (($level + 1) * 4));
                if ($beg === FALSE) {
                    $sql .= $condType.' ';
                } else {
                    $beg = FALSE;
                }

                if (is_array($condition) === FALSE) {
                    // This is a hook.
                    $sql .= '(1=1)'."\n";
                } else if (isset($condition['IN']) === TRUE) {
                    // Get IN clause.
                    $sql .= $this->convertInCondition($condition, ($level + 1));
                } else if (isset($condition['NOT-IN']) === TRUE) {
                    // Get NOT IN clause.
                    $sql .= $this->convertInCondition($condition, ($level + 1), 'NOT-IN');
                } else if (isset($condition['EXISTS']) === TRUE) {
                    $sql .= $this->convertExistsCondition($condition, ($level + 1));
                } else if (isset($condition['NOT-EXISTS']) === TRUE) {
                    $sql .= $this->convertExistsCondition($condition, ($level + 1), 'NOT-EXISTS');
                } else if (isset($condition['OR']) === TRUE || isset($condition['AND']) === TRUE) {
                    // Get OR/AND conditions.
                    $sql .= "\n".$this->convertWhereClause($condition, ($level + 1));
                } else if ((isset($condition['type']) === TRUE) && ($condition['type'] === 'is-null')) {
                    $sql .= $this->convertIsNull($condition);
                    $sql .= "\n";
                } else if ((isset($condition['type']) === TRUE) && ($condition['type'] === 'not-null')) {
                    $sql .= $this->convertNotNull($condition);
                    $sql .= "\n";
                } else if ((isset($condition['type']) === TRUE) && ($condition['type'] === 'like')) {
                    $sql .= $this->convertLikeCondition($condition);
                    $sql .= "\n";
                } else if (isset($condition['compare']) === TRUE) {
                    // Basic condition block.
                    $sql .= $this->convertSingleField($condition['compare']);
                    $sql .= ' '.$wc[$condition['type']];
                    $sql .= ' ('.$this->convertSingleField($condition['to']).')';
                    $sql .= "\n";
                } else {
                    $msg = 'Unable to convert WHERE/HAVING condition.';
                    throw new DALConverterException($msg);
                }//end if
            }//end foreach
        }//end foreach

        if ($addBrackets === TRUE) {
            $sql .= str_repeat(' ', ($level * 4)).")\n";
        }

        return $sql;

    }//end convertWhereClause()


    protected function convertInCondition(array $in, $level=0, $type='IN')
    {
        $sql  = '';
        $sql .= $in[$type]['table'].'.'.$in[$type]['column'];
        $sql .= ' '.$this->getConstructName(strtolower($type)).'(';

        // Get the elements of IN().
        if (isset($in[$type]['CONDITIONS']['SELECT']) === TRUE) {
            $sql .= "\n".$this->convertSelectQuery($in[$type]['CONDITIONS'], ($level + 1));
            $sql .= str_repeat(' ', ($level * 4));
        } else {
            $conditions = array();
            foreach ($in[$type]['CONDITIONS'] as $condition) {
                $conditions[] = $this->convertSingleField($condition);
            }

            $sql .= $this->separateFields($conditions);
        }

        $sql .= ")\n";

        return $sql;

    }//end convertInCondition()


    protected function convertExistsCondition(array $exists, $level=0, $type='EXISTS')
    {
        $sql  = '';
        $sql .= $this->getConstructName(strtolower($type))."(\n";
        $sql .= $this->convertSelectQuery($exists[$type]['CONDITIONS'], ($level + 1));
        $sql .= str_repeat(' ', ($level * 4)).")\n";

        return $sql;

    }//end convertExistsCondition()


    protected function convertIsNull(array $isNull)
    {
        $sql = '';
        if (isset($isNull['compare']) === TRUE) {
            if (empty($isNull['compare']) === FALSE) {
                $sql .= $this->convertSingleField($isNull['compare']);
                $sql .= ' IS NULL';
            } else {
                $msg = 'is-null value cannot be empty.';
                throw new DALConverterException($msg);
            }
        } else {
            $msg = 'Found is-null tag with no value.';
            throw new DALConverterException($msg);
        }

        return $sql;

    }//end convertIsNull()


    protected function convertNotNull(array $notNull)
    {
        $sql = '';
        if (isset($notNull['compare']) === TRUE) {
            if (empty($notNull['compare']) === FALSE) {
                $sql .= $this->convertSingleField($notNull['compare']);
                $sql .= ' IS NOT NULL';
            } else {
                $msg = 'not-null value cannot be empty.';
                throw new DALConverterException($msg);
            }
        } else {
            $msg = 'Found not-null tag with no value.';
            throw new DALConverterException($msg);
        }

        return $sql;

    }//end convertNotNull()


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

    }//end convertUnionsInFromClause()


    protected function getUnionType(array $union)
    {
        $type = '';
        if (isset($union['UNION']) === TRUE) {
            $type = 'UNION';
        } else if (isset($union['UNION-ALL']) === TRUE) {
            $type = 'UNION-ALL';
        }

        return $type;

    }//end getUnionType()


    protected function convertUnions(array $union, $level=1)
    {
        $sql         = '';
        $type        = $this->getUnionType($union);
        $displayType = $this->convertUnionDisplayName($type);
        if (isset($union[$type]['SELECTS']) === TRUE) {
            $selectSqls = array();
            foreach ($union[$type]['SELECTS'] as $select) {
                if (is_array($select) === TRUE) {
                    $selectSqls[] = $this->convertSelectQuery($select, $level);
                } else {
                    $selectSqls[] = $this->addSpaces($level).$select."\n";
                }
            }

            $spaces = "\n".$this->addSpaces($level);
            $sql   .= implode($spaces.$displayType."\n\n", $selectSqls);
        }

        return $sql;

    }//end convertUnions()


    protected function convertUnionDisplayName($type)
    {
        return $type;

    }//end convertUnionDisplayName()


    protected function convertGroupBy(array $groupBy)
    {
        $sql  = 'GROUP BY ';
        $list = array();
        foreach ($groupBy as $field) {
            if (is_array($field) === TRUE) {
                $list[] = $this->convertSingleField($field);
            } else {
                $list[] = $field;
            }
        }

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

        return $sql;

    }//end convertGroupBy()


    protected function convertOrderBy(array $orderBy)
    {
        $sql       = 'ORDER BY ';
        $list      = array();
        $direction = '';

        foreach ($orderBy as $key => $field) {
            if ($key !== 'direction') {
                if (is_array($field) === TRUE) {
                    $list[] = $this->convertSingleField($field);
                } else {
                    $list[] = $field;
                }
            } else {
                $direction = ' '.$field;
            }
        }

        $sql .= $this->separateFields($list).' '.$direction;

        return $sql;

    }//end convertOrderBy()


    protected function convertLimit(array $limit)
    {
        $sql  = 'LIMIT ';
        $sql .= $limit['rows'].' OFFSET '.$limit['offset'];

        return $sql;

    }//end convertLimit()


    protected function convertJoins(array $joins)
    {
        $sql  = '';
        $list = array();
        foreach ($joins as $join) {
            $list[] = $this->convertSingleJoin($join);
        }

        $sql = implode("\nAND ", $list);

        return $sql;

    }//end convertJoins()


    protected function convertSingleJoin(array $join)
    {
        $sql = '';
        if (isset($join['ARGS']) === TRUE) {
            // Each join must have 2 elements.
            $sql  = $join['ARGS'][0]['table'].'.'.$join['ARGS'][0]['column'];
            $sql .= ' '.$this->getComparisonOperators($join['type']).' ';
            $sql .= $join['ARGS'][1]['table'].'.'.$join['ARGS'][1]['column'];
        }

        return $sql;

    }//end convertSingleJoin()


    /*
        Convert Insert Query
    */


    protected function convertInsertQuery(array $sqlArray)
    {
        $sql  = 'INSERT INTO '.$sqlArray['INSERT']['into'];
        $sql .= $this->convertInsertQueryFields($sqlArray['INSERT']['FIELDS']);
        $sql .= "\n";
        $sql .= $this->convertInsertQueryValues($sqlArray['INSERT']);
        return $sql;

    }//end convertInsertQuery()


    protected function addSpaces($level=0, $spaces=4)
    {
        return str_repeat(' ', ($level * 4));

    }//end addSpaces()


    protected function convertInsertQueryFields(array $fields)
    {
        $sql = '';
        if (empty($fields) === FALSE) {
            $sql = '('.$this->separateFields($fields).') ';
        }

        return $sql;

    }//end convertInsertQueryFields()


    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()


    /*
        Convert Update Query
    */


    protected function convertUpdateQuery(array $sqlArray)
    {
        $sql  = 'UPDATE '.$sqlArray['UPDATE']['table'];
        $sql .= "\n".'SET';
        $sql .= "\n".$this->convertUpdateQuerySetValues($sqlArray['UPDATE']['VALUES']);
        if (isset($sqlArray['UPDATE']['WHERE']) === TRUE) {
            $sql .= "\n".'WHERE ';
            $sql .= $this->convertWhereClause($sqlArray['UPDATE']['WHERE']);
        }

        return $sql;

    }//end convertUpdateQuery()


    protected function convertUpdateQuerySetValues(array $values)
    {
        $vals = array();
        foreach ($values as $col => $val) {
            $val    = $this->convertSingleField($val);
            $vals[] = $col.' = '.$val;
        }

        $spaces = $this->addSpaces(1);
        $sql    = $spaces.implode(",\n".$spaces, $vals);

        return $sql;

    }//end convertUpdateQuerySetValues()


    /*
        Convert Delete Query
    */


    protected function convertDeleteQuery(array $sqlArray)
    {
        $sql  = 'DELETE FROM '.$sqlArray['DELETE']['from'];
        $sql .= "\n".$this->getConstructName('where');
        $sql .= $this->convertWhereClause($sqlArray['DELETE']['WHERE']);

        return $sql;

    }//end convertDeleteQuery()


    /*
        Convert ALTER TABLE Query
    */


    protected function convertAlterQueryHeader($tableName)
    {
        $sql = 'ALTER TABLE '.$tableName."\n";
        return $sql;

    }//end convertAlterQueryHeader()


    protected function convertAlterQuery(array $sqlArray)
    {
        $sql = $this->convertAlterQueryHeader($sqlArray['ALTER']['table']);

        foreach ($sqlArray['ALTER'] as $key => $value) {
            if ($key !== 'table') {
                $key       = ucwords(str_replace('-', ' ', strtolower($key)));
                $alterType = 'convertAlterQuery'.str_replace(' ', '', $key);
                $sql      .= $this->$alterType($value);
            }
        }

        return $sql;

    }//end convertAlterQuery()


    protected function convertAlterQueryAddColumn(array $addColumn)
    {
        $sql = 'ADD (';

        if (isset($addColumn['COLUMNS']) === TRUE) {
            $cols = array();
            foreach ($addColumn['COLUMNS'] as $column) {
                $cols[] = $this->convertSingleCreateColumn($column);
            }

            $sql .= implode(', ', $cols);
        } else {
            $msg = 'Cannot convert ALTER TABLE ADD COLUMN without columns.';
            throw new DALConverterException($msg);
        }

        $sql .= ')';

        return $sql;

    }//end convertAlterQueryAddColumn()


    protected function convertAlterQueryDropColumn($column)
    {
        $sql = 'DROP COLUMN ';
        if (($column !== '') && (is_string($column) === TRUE)) {
            $sql .= $column;
        } else {
            $msg = 'Cannot convert ALTER TABLE DROP COLUMN without column name.';
            throw new DALConverterException($msg);
        }

        return $sql;

    }//end convertAlterQueryDropColumn()


    protected function convertAlterQueryRenameColumn(array $renameColumn)
    {
        $sql = 'RENAME COLUMN ';
        if ((isset($renameColumn['OLD']) === TRUE) && (isset($renameColumn['NEW'])) === TRUE) {
            $sql .= $renameColumn['OLD'].' TO '.$renameColumn['NEW'];
        } else {
            $msg = 'Cannot convert ALTER TABLE RENAME COLUMN.';
            throw new DALConverterException($msg);
        }

        return $sql;

    }//end convertAlterQueryRenameColumn()


    protected function convertAlterQueryModifyColumn(array $modifyColumns)
    {
        $sql = 'MODIFY (';

        if (isset($modifyColumns['COLUMNS']) === TRUE) {
            $cols = array();
            foreach ($modifyColumns['COLUMNS'] as $column) {
                $cols[] = $this->convertSingleCreateColumn($column);
            }

            $sql .= implode(', ', $cols);
        } else {
            $msg = 'Cannot convert ALTER TABLE MODIFY COLUMN without columns.';
            throw new DALConverterException($msg);
        }

        $sql .= ')';

        return $sql;

    }//end convertAlterQueryModifyColumn()


    protected function convertAlterQueryAddConstraint(array $constraints, $tableName=NULL)
    {
        $sql = '';
        if (empty($constraints) === FALSE) {
            if ($tableName !== NULL) {
                $sql .= $this->convertAlterQueryHeader($tableName)."\n";
            }

            $sql .= 'ADD ';
            $sql .= $this->convertConstraints($constraints);
        }

        return $sql;

    }//end convertAlterQueryAddConstraint()


    protected function convertAlterQueryDropConstraint($dropConstraint)
    {
        $sql = '';
        if ($dropConstraint !== '') {
            $sql .= 'DROP '.$dropConstraint;
        } else {
            $msg = 'Cannot convert ALTER TABLE DROP CONSTRAINT without constraint name.';
            throw new DALConverterException($msg);
        }

        return $sql;

    }//end convertAlterQueryDropConstraint()


    protected function convertAlterQueryRenameTable(array $renameTable)
    {
        $sql = 'RENAME ';
        if ((isset($renameTable['OLD']) === TRUE) && (isset($renameTable['NEW'])) === TRUE) {
            $sql .= $renameTable['OLD'].' TO '.$renameTable['NEW'];
        } else {
            $msg = 'Cannot convert ALTER TABLE RENAME.';
            throw new DALConverterException($msg);
        }

        return $sql;

    }//end convertAlterQueryRenameTable()


    /*
        Convert Schema
    */


    public function convertCreateTable(array $table)
    {
        $sql  = $this->convertCreateHeader($table);
        $sql .= "\n".$this->convertCreateColumns($table);

        $constraints = $this->convertConstraints($table['CONSTRAINTS']);
        if ($constraints !== '') {
            $sql .= ",\n".$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 convertCreateHeader(array $table)
    {
        $sql  = 'CREATE TABLE '.$table['table'];
        $sql .= ' (';
        return $sql;

    }//end convertCreateHeader()


    protected function convertCreateFooter(array $table)
    {
        $sql = ');';
        return $sql;

    }//end convertCreateFooter()


    protected function convertCreateColumns(array $table)
    {
        $sql = array();
        foreach ($table['COLUMNS'] as $col) {
            $sql[] = $this->convertSingleCreateColumn($col);
        }

        $sql = implode(",\n", $sql);

        return $sql;

    }//end convertCreateColumns()


    protected function convertSingleCreateColumn(array $col)
    {
        // Add column's name.
        $sql = $col['name'].' '.$this->getDataType($col['type']);

        // Add the size to the end of the type.
        if ($col['size'] !== '') {
            $sql .= '('.$col['size'];
            if ($col['scale'] !== '') {
                $sql .= ','.$col['scale'];
            }

            $sql .= ')';
        }

        // Add column's default value if it has one.
        if ($col['default'] !== '') {
            $sql .= ' DEFAULT '.$col['default'];
        }

        // Add NOT NULL if allow-null is set to false.
        if ($col['allow-null'] === 'false') {
            $sql .= ' NOT NULL';
        } else {
            $sql .= ' NULL';
        }

        return $sql;

    }//end convertSingleCreateColumn()


    protected function convertConstraints(array $constraintsList, $asArray=FALSE)
    {
        $sql         = '';
        $constraints = array();

        // Primary keys.
        foreach ($constraintsList['PRIMARY-KEYS'] as $pk) {
            $constraints[] = $this->convertSinglePrimaryKey($pk);
        }

        // Foreign keys.
        if (isset($constraintsList['FOREIGN-KEYS']) === TRUE) {
            foreach ($constraintsList['FOREIGN-KEYS'] as $fk) {
                $constraints[] = $this->convertSingleForeignKey($fk);
            }
        }

        if (isset($constraintsList['UNIQUES']) === TRUE) {
            foreach ($constraintsList['UNIQUES'] as $un) {
                $constraints[] = $this->convertSingleUnique($un);
            }
        }

        if ($asArray === FALSE) {
            $sql .= implode(",\n", $constraints);
            return $sql;
        } else {
            return $constraints;
        }

    }//end convertConstraints()


    protected function convertSinglePrimaryKey(array $pk)
    {
        $sql  = 'CONSTRAINT '.$pk['name'].' PRIMARY KEY (';
        $sql .= $this->separateFields($pk['COLUMNS']).')';
        return $sql;

    }//end convertSinglePrimaryKey()


    protected function convertSingleForeignKey(array $fk)
    {
        $sql  = 'CONSTRAINT '.$fk['name'].' FOREIGN KEY (';
        $cols = array();
        $refs = array();
        foreach ($fk['COLUMNS'] as $col) {
            $cols[] = $col['name'];
            $refs[] = $col['references'];
        }

        $sql .= $this->separateFields($cols).') ';
        $sql .= 'REFERENCES '.$fk['table'].'(';
        $sql .= $this->separateFields($refs).')';
        if ($fk['on-delete'] !== 'NO ACTION') {
            $sql .= ' ON DELETE '.$fk['on-delete'];
        }

        return $sql;

    }//end convertSingleForeignKey()


    protected function convertSingleUnique(array $un)
    {
        $sql  = 'CONSTRAINT '.$un['name'].' UNIQUE (';
        $sql .= $this->separateFields($un['COLUMNS']).')';
        return $sql;

    }//end convertSingleUnique()


    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 convertCreateSequences(array $sequences)
    {
        $sql  = '';
        $seqs = array();
        if (empty($sequences) === FALSE) {
            foreach ($sequences as $sequence) {
                $seqs[] = $this->convertSingleSequence($sequence);
            }

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

        return $sql;

    }//end convertCreateSequences()


    protected function convertSingleSequence($sequence)
    {
        $sql  = 'CREATE SEQUENCE ';
        $sql .= $sequence;

        return $sql;

    }//end convertSingleSequence()


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

        return $sql;

    }//end convertDropTable()


    public function convertTruncateTable($tableName)
    {
        $sql = 'delete from '.$tableName;
        return $sql;

    }//end convertTruncateTable()


    public function convertDropSequence($sequenceName)
    {
        $sql = 'DROP SEQUENCE '.$sequenceName;
        return $sql;

    }//end convertDropSequence()


    public function convertResetSequence($sequenceName)
    {
        // Need to sort out the min_val from the sequence.
        $sql = "SELECT setval('$sequenceName', 1); ";
        return $sql;

    }//end convertResetSequence()


    protected function convertWithQuery(array $withQuery)
    {
        $sql  = 'WITH ';
        $sql .= $this->convertSingleFunction($withQuery);
        $sql .= "\nAS\n(\n";
        $sql .= $this->convertUnions($withQuery);
        $sql .= "\n)\n";
        $sql .= $this->convertSelectQuery($withQuery['AFTER']);

        return $sql;

    }//end convertWithQuery()


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

    }//end convertCallQuery()


    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 = '.$schema;
        return $sql;

    }//end handleFunctionTableExists()


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

    }//end handleFunctionConcat()


    protected function handleFunctionArray(array $args)
    {
        $sql = 'ARRAY('.$args[0].')';
        return $sql;

    }//end handleFunctionArray()


    protected function convertLikeCondition(array $condition)
    {
        $compare = $this->convertSingleField($condition['compare']);
        $to      = $this->convertSingleField($condition['to']);
        $sql     = $compare.' LIKE '.$to;

        return $sql;

    }//end convertLikeCondition()


    protected function handleFunctionCast(array $args)
    {
        $sql = 'CAST('.$args[0].' AS '.$this->getDataType($args[1]).')';
        return $sql;

    }//end handleFunctionCast()


    protected function handleFunctionBindcast(array $args)
    {
        $sql = $args[0];
        return $sql;

    }//end handleFunctionBindcast()


    protected function handleFunctionLength(array $args)
    {
        $sql = 'LENGTH('.$this->convertSingleField($args[0]).')';
        return $sql;

    }//end handleFunctionLength()


    protected function handleFunctionCoalesce(array $args)
    {
        $sql    = 'COALESCE(';
        $fnArgs = array();
        foreach ($args as $arg) {
            $fnArgs[] = $this->convertSingleField($arg);
        }

        $sql .= implode(',', $fnArgs).')';

        return $sql;

    }//end handleFunctionCoalesce()


    protected function handleFunctionMax(array $args)
    {
        if (count($args) !== 1) {
            $msg = 'The MAX() aggregate function accepts only one parameter: the expression to find the maximum value of in the table.';
            throw new DALConverterException($msg);
        }
        $sql    = 'MAX(';
        $fnArg = $this->convertSingleField($args[0]);

        $sql .= $fnArg.')';

        return $sql;

    }//end handleFunctionMax()


    protected function handleFunctionMin(array $args)
    {
        if (count($args) !== 1) {
            $msg = 'The MIN() aggregate function accepts only one parameter: the expression to find the maximum value of in the table.';
            throw new DALConverterException($msg);
        }
        $sql    = 'MIN(';
        $fnArg = $this->convertSingleField($args[0]);

        $sql .= $fnArg.')';

        return $sql;

    }//end handleFunctionMin()


    protected function handleFunctionCount(array $args)
    {
        if (count($args) != 1) {
            $msg = 'The COUNT() aggregate function accepts exactly one parameter.';
            throw new DALConverterException($msg);
        }

        $sql    = 'COUNT(';
        $fnArg = $this->convertSingleField($args[0]);

        $sql .= $fnArg.')';

        return $sql;

    }//end handleFunctionCount()


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

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

        return $sql;

    }//end handleFunctionSubstring()


    protected function getComparisonOperators($type=NULL)
    {
        if ($type !== NULL) {
            if (isset($this->_whereConditions[$type]) === TRUE) {
                return $this->_whereConditions[$type];
            } else {
                return '';
            }
        }

        return $this->_whereConditions;

    }//end getComparisonOperators()


}//end class
?>