1 <?php

     2 /*

     3  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS

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

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

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

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

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

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

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

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

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

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

    14  *

    15  * This software consists of voluntary contributions made by many individuals

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

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

    18  */

    19 

    20 namespace Doctrine\DBAL\Platforms;

    21 

    22 use Doctrine\DBAL\DBALException;

    23 

    24 /**

    25  * The SqlitePlatform class describes the specifics and dialects of the SQLite

    26  * database platform.

    27  *

    28  * @since 2.0

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

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

    31  * @todo Rename: SQLitePlatform

    32  */

    33 class SqlitePlatform extends AbstractPlatform

    34 {

    35     /**

    36      * returns the regular expression operator

    37      *

    38      * @return string

    39      * @override

    40      */

    41     public function getRegexpExpression()

    42     {

    43         return 'RLIKE';

    44     }

    45 

    46     /**

    47      * Return string to call a variable with the current timestamp inside an SQL statement

    48      * There are three special variables for current date and time.

    49      *

    50      * @return string       sqlite function as string

    51      * @override

    52      */

    53     public function getNowExpression($type = 'timestamp')

    54     {

    55         switch ($type) {

    56             case 'time':

    57                 return 'time(\'now\')';

    58             case 'date':

    59                 return 'date(\'now\')';

    60             case 'timestamp':

    61             default:

    62                 return 'datetime(\'now\')';

    63         }

    64     }

    65 

    66     /**

    67      * Trim a string, leading/trailing/both and with a given char which defaults to space.

    68      *

    69      * @param string $str

    70      * @param int $pos

    71      * @param string $char

    72      * @return string

    73      */

    74     public function getTrimExpression($str, $pos = self::TRIM_UNSPECIFIED, $char = false)

    75     {

    76         $trimFn = '';

    77         $trimChar = ($char != false) ? (', ' . $char) : '';

    78 

    79         if ($pos == self::TRIM_LEADING) {

    80             $trimFn = 'LTRIM';

    81         } else if($pos == self::TRIM_TRAILING) {

    82             $trimFn = 'RTRIM';

    83         } else {

    84             $trimFn = 'TRIM';

    85         }

    86 

    87         return $trimFn . '(' . $str . $trimChar . ')';

    88     }

    89 

    90     /**

    91      * return string to call a function to get a substring inside an SQL statement

    92      *

    93      * Note: Not SQL92, but common functionality.

    94      *

    95      * SQLite only supports the 2 parameter variant of this function

    96      *

    97      * @param string $value         an sql string literal or column name/alias

    98      * @param integer $position     where to start the substring portion

    99      * @param integer $length       the substring portion length

   100      * @return string               SQL substring function with given parameters

   101      * @override

   102      */

   103     public function getSubstringExpression($value, $position, $length = null)

   104     {

   105         if ($length !== null) {

   106             return 'SUBSTR(' . $value . ', ' . $position . ', ' . $length . ')';

   107         }

   108         return 'SUBSTR(' . $value . ', ' . $position . ', LENGTH(' . $value . '))';

   109     }

   110 

   111     /**

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

   113      *

   114      * @param string $substr    literal string to find

   115      * @param string $str       literal string

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

   117      * @return integer

   118      */

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

   120     {

   121         if ($startPos == false) {

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

   123         } else {

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

   125         }

   126     }

   127 

   128     public function getDateDiffExpression($date1, $date2)

   129     {

   130         return 'ROUND(JULIANDAY('.$date1 . ')-JULIANDAY('.$date2.'))';

   131     }

   132 

   133     public function getDateAddDaysExpression($date, $days)

   134     {

   135         return "DATE(" . $date . ",'+". $days . " day')";

   136     }

   137 

   138     public function getDateSubDaysExpression($date, $days)

   139     {

   140         return "DATE(" . $date . ",'-". $days . " day')";

   141     }

   142 

   143     public function getDateAddMonthExpression($date, $months)

   144     {

   145         return "DATE(" . $date . ",'+". $months . " month')";

   146     }

   147 

   148     public function getDateSubMonthExpression($date, $months)

   149     {

   150         return "DATE(" . $date . ",'-". $months . " month')";

   151     }

   152 

   153     protected function _getTransactionIsolationLevelSQL($level)

   154     {

   155         switch ($level) {

   156             case \Doctrine\DBAL\Connection::TRANSACTION_READ_UNCOMMITTED:

   157                 return 0;

   158             case \Doctrine\DBAL\Connection::TRANSACTION_READ_COMMITTED:

   159             case \Doctrine\DBAL\Connection::TRANSACTION_REPEATABLE_READ:

   160             case \Doctrine\DBAL\Connection::TRANSACTION_SERIALIZABLE:

   161                 return 1;

   162             default:

   163                 return parent::_getTransactionIsolationLevelSQL($level);

   164         }

   165     }

   166 

   167     public function getSetTransactionIsolationSQL($level)

   168     {

   169         return 'PRAGMA read_uncommitted = ' . $this->_getTransactionIsolationLevelSQL($level);

   170     }

   171 

   172     /** 

   173      * @override 

   174      */

   175     public function prefersIdentityColumns()

   176     {

   177         return true;

   178     }

   179     

   180     /** 

   181      * @override 

   182      */

   183     public function getBooleanTypeDeclarationSQL(array $field)

   184     {

   185         return 'BOOLEAN';

   186     }

   187 

   188     /** 

   189      * @override 

   190      */

   191     public function getIntegerTypeDeclarationSQL(array $field)

   192     {

   193         return $this->_getCommonIntegerTypeDeclarationSQL($field);

   194     }

   195 

   196     /** 

   197      * @override 

   198      */

   199     public function getBigIntTypeDeclarationSQL(array $field)

   200     {

   201         return $this->_getCommonIntegerTypeDeclarationSQL($field);

   202     }

   203 

   204     /** 

   205      * @override 

   206      */

   207     public function getTinyIntTypeDeclarationSql(array $field)

   208     {

   209         return $this->_getCommonIntegerTypeDeclarationSQL($field);

   210     }

   211 

   212     /** 

   213      * @override 

   214      */

   215     public function getSmallIntTypeDeclarationSQL(array $field)

   216     {

   217         return $this->_getCommonIntegerTypeDeclarationSQL($field);

   218     }

   219 

   220     /** 

   221      * @override 

   222      */

   223     public function getMediumIntTypeDeclarationSql(array $field)

   224     {

   225         return $this->_getCommonIntegerTypeDeclarationSQL($field);

   226     }

   227 

   228     /** 

   229      * @override 

   230      */

   231     public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)

   232     {

   233         return 'DATETIME';

   234     }

   235     

   236     /**

   237      * @override

   238      */

   239     public function getDateTypeDeclarationSQL(array $fieldDeclaration)

   240     {

   241         return 'DATE';

   242     }

   243 

   244     /**

   245      * @override

   246      */

   247     public function getTimeTypeDeclarationSQL(array $fieldDeclaration)

   248     {

   249         return 'TIME';

   250     }

   251 

   252     /** 

   253      * @override 

   254      */

   255     protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)

   256     {

   257         $autoinc = ! empty($columnDef['autoincrement']) ? ' AUTOINCREMENT' : '';

   258         $pk = ! empty($columnDef['primary']) && ! empty($autoinc) ? ' PRIMARY KEY' : '';

   259 

   260         return 'INTEGER' . $pk . $autoinc;

   261     }

   262 

   263     /**

   264      * create a new table

   265      *

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

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

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

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

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

   271      *                          array(

   272      *                              'id' => array(

   273      *                                  'type' => 'integer',

   274      *                                  'unsigned' => 1

   275      *                                  'notnull' => 1

   276      *                                  'default' => 0

   277      *                              ),

   278      *                              'name' => array(

   279      *                                  'type' => 'text',

   280      *                                  'length' => 12

   281      *                              ),

   282      *                              'password' => array(

   283      *                                  'type' => 'text',

   284      *                                  'length' => 12

   285      *                              )

   286      *                          );

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

   288      *

   289      * @return void

   290      * @override

   291      */

   292     protected function _getCreateTableSQL($name, array $columns, array $options = array())

   293     {

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

   295 

   296         $autoinc = false;

   297         foreach($columns as $field) {

   298             if (isset($field['autoincrement']) && $field['autoincrement']) {

   299                 $autoinc = true;

   300                 break;

   301             }

   302         }

   303 

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

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

   306             $keyColumns = array_map(array($this, 'quoteIdentifier'), $keyColumns);

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

   308         }

   309 

   310         $query[] = 'CREATE TABLE ' . $name . ' (' . $queryFields . ')';

   311 

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

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

   314                 $query[] = $this->getCreateIndexSQL($indexDef, $name);

   315             }

   316         }

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

   318             foreach ($options['unique'] as $index => $indexDef) {

   319                 $query[] = $this->getCreateIndexSQL($indexDef, $name);

   320             }

   321         }

   322         return $query;

   323     }

   324 

   325     /**

   326      * {@inheritdoc}

   327      */

   328     protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)

   329     {

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

   331                 : ($length ? 'VARCHAR(' . $length . ')' : 'TEXT');

   332     }

   333     

   334     public function getClobTypeDeclarationSQL(array $field)

   335     {

   336         return 'CLOB';

   337     }

   338 

   339     public function getListTableConstraintsSQL($table)

   340     {

   341         return "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name = '$table' AND sql NOT NULL ORDER BY name";

   342     }

   343 

   344     public function getListTableColumnsSQL($table, $currentDatabase = null)

   345     {

   346         return "PRAGMA table_info($table)";

   347     }

   348 

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

   350     {

   351         return "PRAGMA index_list($table)";

   352     }

   353 

   354     public function getListTablesSQL()

   355     {

   356         return "SELECT name FROM sqlite_master WHERE type = 'table' AND name != 'sqlite_sequence' "

   357              . "UNION ALL SELECT name FROM sqlite_temp_master "

   358              . "WHERE type = 'table' ORDER BY name";

   359     }

   360 

   361     public function getListViewsSQL($database)

   362     {

   363         return "SELECT name, sql FROM sqlite_master WHERE type='view' AND sql NOT NULL";

   364     }

   365 

   366     public function getCreateViewSQL($name, $sql)

   367     {

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

   369     }

   370 

   371     public function getDropViewSQL($name)

   372     {

   373         return 'DROP VIEW '. $name;

   374     }

   375 

   376     /**

   377      * SQLite does support foreign key constraints, but only in CREATE TABLE statements...

   378      * This really limits their usefulness and requires SQLite specific handling, so

   379      * we simply say that SQLite does NOT support foreign keys for now...

   380      *

   381      * @return boolean FALSE

   382      * @override

   383      */

   384     public function supportsForeignKeyConstraints()

   385     {

   386         return false;

   387     }

   388 

   389     public function supportsAlterTable()

   390     {

   391         return false;

   392     }

   393 

   394     public function supportsIdentityColumns()

   395     {

   396         return true;

   397     }

   398 

   399     /**

   400      * Get the platform name for this instance

   401      *

   402      * @return string

   403      */

   404     public function getName()

   405     {

   406         return 'sqlite';

   407     }

   408 

   409     /**

   410      * @inheritdoc

   411      */

   412     public function getTruncateTableSQL($tableName, $cascade = false)

   413     {

   414         return 'DELETE FROM '.$tableName;

   415     }

   416 

   417     /**

   418      * User-defined function for Sqlite that is used with PDO::sqliteCreateFunction()

   419      *

   420      * @param  int|float $value

   421      * @return float

   422      */

   423     static public function udfSqrt($value)

   424     {

   425         return sqrt($value);

   426     }

   427 

   428     /**

   429      * User-defined function for Sqlite that implements MOD(a, b)

   430      */

   431     static public function udfMod($a, $b)

   432     {

   433         return ($a % $b);

   434     }

   435 

   436     /**

   437      * @param string $str

   438      * @param string $substr

   439      * @param int $offset

   440      */

   441     static public function udfLocate($str, $substr, $offset = 0)

   442     {

   443         $pos = strpos($str, $substr, $offset);

   444         if ($pos !== false) {

   445             return $pos+1;

   446         }

   447         return 0;

   448     }

   449 

   450     public function getForUpdateSql()

   451     {

   452         return '';

   453     }

   454 

   455     protected function initializeDoctrineTypeMappings()

   456     {

   457         $this->doctrineTypeMapping = array(

   458             'boolean'       => 'boolean',

   459             'tinyint'       => 'boolean',

   460             'smallint'      => 'smallint',

   461             'mediumint'     => 'integer',

   462             'int'           => 'integer',

   463             'integer'       => 'integer',

   464             'serial'        => 'integer',

   465             'bigint'        => 'bigint',

   466             'bigserial'     => 'bigint',

   467             'clob'          => 'text',

   468             'tinytext'      => 'text',

   469             'mediumtext'    => 'text',

   470             'longtext'      => 'text',

   471             'text'          => 'text',

   472             'varchar'       => 'string',

   473             'varchar2'      => 'string',

   474             'nvarchar'      => 'string',

   475             'image'         => 'string',

   476             'ntext'         => 'string',

   477             'char'          => 'string',

   478             'date'          => 'date',

   479             'datetime'      => 'datetime',

   480             'timestamp'     => 'datetime',

   481             'time'          => 'time',

   482             'float'         => 'float',

   483             'double'        => 'float',

   484             'real'          => 'float',

   485             'decimal'       => 'decimal',

   486             'numeric'       => 'decimal',

   487         );

   488     }

   489     

   490     protected function getReservedKeywordsClass()

   491     {

   492         return 'Doctrine\DBAL\Platforms\Keywords\SQLiteKeywords';

   493     }

   494 }