<?php

/*

 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS

 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT

 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR

 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT

 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,

 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT

 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,

 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY

 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT

 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE

 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

 *

 * This software consists of voluntary contributions made by many individuals

 * and is licensed under the LGPL. For more information, see

 * <http://www.doctrine-project.org>.

 */

namespace Doctrine\DBAL\Platforms;

use Doctrine\DBAL\DBALException,

    Doctrine\DBAL\Schema\TableDiff,

    Doctrine\DBAL\Schema\Index,

    Doctrine\DBAL\Schema\Table;

/**

 * The MySqlPlatform provides the behavior, features and SQL dialect of the

 * MySQL database platform. This platform represents a MySQL 5.0 or greater platform that

 * uses the InnoDB storage engine.

 *

 * @since 2.0

 * @author Roman Borschel <roman@code-factory.org>

 * @author Benjamin Eberlei <kontakt@beberlei.de>

 * @todo Rename: MySQLPlatform

 */

class MySqlPlatform extends AbstractPlatform

{

    /**

     * Gets the character used for identifier quoting.

     *

     * @return string

     * @override

     */

    public function getIdentifierQuoteCharacter()

    {

        return '`';

    }

    /**

     * Returns the regular expression operator.

     *

     * @return string

     * @override

     */

    public function getRegexpExpression()

    {

        return 'RLIKE';

    }

    /**

     * Returns global unique identifier

     *

     * @return string to get global unique identifier

     * @override

     */

    public function getGuidExpression()

    {

        return 'UUID()';

    }

    /**

     * returns the position of the first occurrence of substring $substr in string $str

     *

     * @param string $substr    literal string to find

     * @param string $str       literal string

     * @param int    $pos       position to start at, beginning of string by default

     * @return integer

     */

    public function getLocateExpression($str, $substr, $startPos = false)

    {

        if ($startPos == false) {

            return 'LOCATE(' . $substr . ', ' . $str . ')';

        } else {

            return 'LOCATE(' . $substr . ', ' . $str . ', '.$startPos.')';

        }

    }

    /**

     * Returns a series of strings concatinated

     *

     * concat() accepts an arbitrary number of parameters. Each parameter

     * must contain an expression or an array with expressions.

     *

     * @param string|array(string) strings that will be concatinated.

     * @override

     */

    public function getConcatExpression()

    {

        $args = func_get_args();

        return 'CONCAT(' . join(', ', (array) $args) . ')';

    }

    public function getDateDiffExpression($date1, $date2)

    {

        return 'DATEDIFF(' . $date1 . ', ' . $date2 . ')';

    }

    public function getDateAddDaysExpression($date, $days)

    {

        return 'DATE_ADD(' . $date . ', INTERVAL ' . $days . ' DAY)';

    }

    public function getDateSubDaysExpression($date, $days)

    {

        return 'DATE_SUB(' . $date . ', INTERVAL ' . $days . ' DAY)';

    }

    public function getDateAddMonthExpression($date, $months)

    {

        return 'DATE_ADD(' . $date . ', INTERVAL ' . $months . ' MONTH)';

    }

    public function getDateSubMonthExpression($date, $months)

    {

        return 'DATE_SUB(' . $date . ', INTERVAL ' . $months . ' MONTH)';

    }

    public function getListDatabasesSQL()

    {

        return 'SHOW DATABASES';

    }

    public function getListTableConstraintsSQL($table)

    {

        return 'SHOW INDEX FROM ' . $table;

    }

    /**

     * Two approaches to listing the table indexes. The information_schema is 

     * prefered, because it doesn't cause problems with SQL keywords such as "order" or "table".

     * 

     * @param string $table

     * @param string $currentDatabase

     * @return string

     */

    public function getListTableIndexesSQL($table, $currentDatabase = null)

    {

        if ($currentDatabase) {

            return "SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, ".

                   "SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, ".

                   "CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, " .

                   "NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment " . 

                   "FROM information_schema.STATISTICS WHERE TABLE_NAME = '" . $table . "' AND TABLE_SCHEMA = '" . $currentDatabase . "'";

        } else {

            return 'SHOW INDEX FROM ' . $table;

        }

    }

    public function getListViewsSQL($database)

    {

        return "SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '".$database."'";

    }

    public function getListTableForeignKeysSQL($table, $database = null)

    {

        $sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ".

               "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ".

               "FROM information_schema.key_column_usage k /*!50116 ".

               "INNER JOIN information_schema.referential_constraints c ON ".

               "  c.constraint_name = k.constraint_name AND ".

               "  c.table_name = '$table' */ WHERE k.table_name = '$table'";

        if ($database) {

            $sql .= " AND k.table_schema = '$database' /*!50116 AND c.constraint_schema = '$database' */";

        }

        $sql .= " AND k.`REFERENCED_COLUMN_NAME` is not NULL";

        return $sql;

    }

    public function getCreateViewSQL($name, $sql)

    {

        return 'CREATE VIEW ' . $name . ' AS ' . $sql;

    }

    public function getDropViewSQL($name)

    {

        return 'DROP VIEW '. $name;

    }

    /**

     * Gets the SQL snippet used to declare a VARCHAR column on the MySql platform.

     *

     * @params array $field

     */

    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)

    {

        return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)')

                : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');

    }

    /** @override */

    public function getClobTypeDeclarationSQL(array $field)

    {

        if ( ! empty($field['length']) && is_numeric($field['length'])) {

            $length = $field['length'];

            if ($length <= 255) {

                return 'TINYTEXT';

            } else if ($length <= 65532) {

                return 'TEXT';

            } else if ($length <= 16777215) {

                return 'MEDIUMTEXT';

            }

        }

        return 'LONGTEXT';

    }

    /**

     * @override

     */

    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)

    {

        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] == true) {

            return 'TIMESTAMP';

        } else {

            return 'DATETIME';

        }

    }

    /**

     * @override

     */

    public function getDateTypeDeclarationSQL(array $fieldDeclaration)

    {

        return 'DATE';

    }

    /**

     * @override

     */

    public function getTimeTypeDeclarationSQL(array $fieldDeclaration) 

    {

        return 'TIME';

    }	

    /**

     * @override

     */

    public function getBooleanTypeDeclarationSQL(array $field)

    {

        return 'TINYINT(1)';

    }

    /**

     * Obtain DBMS specific SQL code portion needed to set the COLLATION

     * of a field declaration to be used in statements like CREATE TABLE.

     *

     * @param string $collation   name of the collation

     * @return string  DBMS specific SQL code portion needed to set the COLLATION

     *                 of a field declaration.

     */

    public function getCollationFieldDeclaration($collation)

    {

        return 'COLLATE ' . $collation;

    }

    /**

     * Whether the platform prefers identity columns for ID generation.

     * MySql prefers "autoincrement" identity columns since sequences can only

     * be emulated with a table.

     *

     * @return boolean

     * @override

     */

    public function prefersIdentityColumns()

    {

        return true;

    }

    /**

     * Whether the platform supports identity columns.

     * MySql supports this through AUTO_INCREMENT columns.

     *

     * @return boolean

     * @override

     */

    public function supportsIdentityColumns()

    {

        return true;

    }

    public function supportsInlineColumnComments()

    {

        return true;

    }

    public function getShowDatabasesSQL()

    {

        return 'SHOW DATABASES';

    }

    public function getListTablesSQL()

    {

        return "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'";

    }

    public function getListTableColumnsSQL($table, $database = null)

    {

        if ($database) {

            return "SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, ".

                   "COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, " .

                   "CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS CollactionName ".

                   "FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" . $database . "' AND TABLE_NAME = '" . $table . "'";

        } else {

            return 'DESCRIBE ' . $table;

        }

    }

    /**

     * create a new database

     *

     * @param string $name name of the database that should be created

     * @return string

     * @override

     */

    public function getCreateDatabaseSQL($name)

    {

        return 'CREATE DATABASE ' . $name;

    }

    /**

     * drop an existing database

     *

     * @param string $name name of the database that should be dropped

     * @return string

     * @override

     */

    public function getDropDatabaseSQL($name)

    {

        return 'DROP DATABASE ' . $name;

    }

    /**

     * create a new table

     *

     * @param string $tableName   Name of the database that should be created

     * @param array $columns  Associative array that contains the definition of each field of the new table

     *                       The indexes of the array entries are the names of the fields of the table an

     *                       the array entry values are associative arrays like those that are meant to be

     *                       passed with the field definitions to get[Type]Declaration() functions.

     *                          array(

     *                              'id' => array(

     *                                  'type' => 'integer',

     *                                  'unsigned' => 1

     *                                  'notnull' => 1

     *                                  'default' => 0

     *                              ),

     *                              'name' => array(

     *                                  'type' => 'text',

     *                                  'length' => 12

     *                              ),

     *                              'password' => array(

     *                                  'type' => 'text',

     *                                  'length' => 12

     *                              )

     *                          );

     * @param array $options  An associative array of table options:

     *                          array(

     *                              'comment' => 'Foo',

     *                              'charset' => 'utf8',

     *                              'collate' => 'utf8_unicode_ci',

     *                              'engine' => 'innodb',

     *                              'foreignKeys' => array(

     *                                  new ForeignKeyConstraint(),

     *                                  new ForeignKeyConstraint(),

     *                                  new ForeignKeyConstraint(),

     *                                  // etc

     *                              )

     *                          );

     *

     * @return void

     * @override

     */

    protected function _getCreateTableSQL($tableName, array $columns, array $options = array())

    {

        $queryFields = $this->getColumnDeclarationListSQL($columns);

        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {

            foreach ($options['uniqueConstraints'] as $index => $definition) {

                $queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL($index, $definition);

            }

        }

        // add all indexes

        if (isset($options['indexes']) && ! empty($options['indexes'])) {

            foreach($options['indexes'] as $index => $definition) {

                $queryFields .= ', ' . $this->getIndexDeclarationSQL($index, $definition);

            }

        }

        // attach all primary keys

        if (isset($options['primary']) && ! empty($options['primary'])) {

            $keyColumns = array_unique(array_values($options['primary']));

            $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')';

        }

        $query = 'CREATE ';

        if (!empty($options['temporary'])) {

            $query .= 'TEMPORARY ';

        }

        $query.= 'TABLE ' . $tableName . ' (' . $queryFields . ')';

        $optionStrings = array();

        if (isset($options['comment'])) {

            $optionStrings['comment'] = 'COMMENT = ' . $options['comment'];

        }

        if (isset($options['charset'])) {

            $optionStrings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];

            if (isset($options['collate'])) {

                $optionStrings['charset'] .= ' COLLATE ' . $options['collate'];

            }

        }

        // get the type of the table

        if (isset($options['engine'])) {

            $optionStrings[] = 'ENGINE = ' . $options['engine'];

        } else {

            // default to innodb

            $optionStrings[] = 'ENGINE = InnoDB';

        }

        if ( ! empty($optionStrings)) {

            $query.= ' '.implode(' ', $optionStrings);

        }

        $sql[] = $query;

        if (isset($options['foreignKeys'])) {

            foreach ((array) $options['foreignKeys'] as $definition) {

                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);

            }

        }

        return $sql;

    }