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.
15 * This software consists of voluntary contributions made by many individuals
16 * and is licensed under the MIT license. For more information, see
17 * <http://www.doctrine-project.org>.
20 namespace Doctrine\DBAL\Platforms;
22 use Doctrine\DBAL\DBALException;
25 * The SqlitePlatform class describes the specifics and dialects of the SQLite
29 * @author Roman Borschel <roman@code-factory.org>
30 * @author Benjamin Eberlei <kontakt@beberlei.de>
31 * @todo Rename: SQLitePlatform
33 class SqlitePlatform extends AbstractPlatform
38 public function getRegexpExpression()
46 public function getNowExpression($type = 'timestamp')
50 return 'time(\'now\')';
52 return 'date(\'now\')';
55 return 'datetime(\'now\')';
62 public function getTrimExpression($str, $pos = self::TRIM_UNSPECIFIED, $char = false)
64 $trimChar = ($char != false) ? (', ' . $char) : '';
67 case self::TRIM_LEADING:
71 case self::TRIM_TRAILING:
79 return $trimFn . '(' . $str . $trimChar . ')';
85 * SQLite only supports the 2 parameter variant of this function
87 public function getSubstringExpression($value, $position, $length = null)
89 if ($length !== null) {
90 return 'SUBSTR(' . $value . ', ' . $position . ', ' . $length . ')';
93 return 'SUBSTR(' . $value . ', ' . $position . ', LENGTH(' . $value . '))';
99 public function getLocateExpression($str, $substr, $startPos = false)
101 if ($startPos == false) {
102 return 'LOCATE('.$str.', '.$substr.')';
105 return 'LOCATE('.$str.', '.$substr.', '.$startPos.')';
111 public function getDateDiffExpression($date1, $date2)
113 return 'ROUND(JULIANDAY('.$date1 . ')-JULIANDAY('.$date2.'))';
119 public function getDateAddDaysExpression($date, $days)
121 return "DATE(" . $date . ",'+". $days . " day')";
127 public function getDateSubDaysExpression($date, $days)
129 return "DATE(" . $date . ",'-". $days . " day')";
135 public function getDateAddMonthExpression($date, $months)
137 return "DATE(" . $date . ",'+". $months . " month')";
143 public function getDateSubMonthExpression($date, $months)
145 return "DATE(" . $date . ",'-". $months . " month')";
151 protected function _getTransactionIsolationLevelSQL($level)
154 case \Doctrine\DBAL\Connection::TRANSACTION_READ_UNCOMMITTED:
156 case \Doctrine\DBAL\Connection::TRANSACTION_READ_COMMITTED:
157 case \Doctrine\DBAL\Connection::TRANSACTION_REPEATABLE_READ:
158 case \Doctrine\DBAL\Connection::TRANSACTION_SERIALIZABLE:
161 return parent::_getTransactionIsolationLevelSQL($level);
168 public function getSetTransactionIsolationSQL($level)
170 return 'PRAGMA read_uncommitted = ' . $this->_getTransactionIsolationLevelSQL($level);
176 public function prefersIdentityColumns()
184 public function getBooleanTypeDeclarationSQL(array $field)
192 public function getIntegerTypeDeclarationSQL(array $field)
194 return $this->_getCommonIntegerTypeDeclarationSQL($field);
200 public function getBigIntTypeDeclarationSQL(array $field)
202 return $this->_getCommonIntegerTypeDeclarationSQL($field);
208 public function getTinyIntTypeDeclarationSql(array $field)
210 return $this->_getCommonIntegerTypeDeclarationSQL($field);
216 public function getSmallIntTypeDeclarationSQL(array $field)
218 return $this->_getCommonIntegerTypeDeclarationSQL($field);
224 public function getMediumIntTypeDeclarationSql(array $field)
226 return $this->_getCommonIntegerTypeDeclarationSQL($field);
232 public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
240 public function getDateTypeDeclarationSQL(array $fieldDeclaration)
248 public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
256 protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
264 protected function _getCreateTableSQL($name, array $columns, array $options = array())
266 $name = str_replace(".", "__", $name);
267 $queryFields = $this->getColumnDeclarationListSQL($columns);
269 if (isset($options['primary']) && ! empty($options['primary'])) {
270 $keyColumns = array_unique(array_values($options['primary']));
271 $queryFields.= ', PRIMARY KEY('.implode(', ', $keyColumns).')';
274 $query[] = 'CREATE TABLE ' . $name . ' (' . $queryFields . ')';
276 if (isset($options['indexes']) && ! empty($options['indexes'])) {
277 foreach ($options['indexes'] as $index => $indexDef) {
278 $query[] = $this->getCreateIndexSQL($indexDef, $name);
282 if (isset($options['unique']) && ! empty($options['unique'])) {
283 foreach ($options['unique'] as $index => $indexDef) {
284 $query[] = $this->getCreateIndexSQL($indexDef, $name);
294 protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
296 return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)')
297 : ($length ? 'VARCHAR(' . $length . ')' : 'TEXT');
303 public function getClobTypeDeclarationSQL(array $field)
308 public function getListTableConstraintsSQL($table)
310 $table = str_replace(".", "__", $table);
312 return "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name = '$table' AND sql NOT NULL ORDER BY name";
315 public function getListTableColumnsSQL($table, $currentDatabase = null)
317 $table = str_replace(".", "__", $table);
319 return "PRAGMA table_info($table)";
325 public function getListTableIndexesSQL($table, $currentDatabase = null)
327 $table = str_replace(".", "__", $table);
329 return "PRAGMA index_list($table)";
332 public function getListTablesSQL()
334 return "SELECT name FROM sqlite_master WHERE type = 'table' AND name != 'sqlite_sequence' AND name != 'geometry_columns' AND name != 'spatial_ref_sys' "
335 . "UNION ALL SELECT name FROM sqlite_temp_master "
336 . "WHERE type = 'table' ORDER BY name";
342 public function getListViewsSQL($database)
344 return "SELECT name, sql FROM sqlite_master WHERE type='view' AND sql NOT NULL";
347 public function getCreateViewSQL($name, $sql)
349 return 'CREATE VIEW ' . $name . ' AS ' . $sql;
352 public function getDropViewSQL($name)
354 return 'DROP VIEW '. $name;
360 * SQLite does support foreign key constraints, but only in CREATE TABLE statements...
361 * This really limits their usefulness and requires SQLite specific handling, so
362 * we simply say that SQLite does NOT support foreign keys for now...
364 public function supportsForeignKeyConstraints()
372 public function supportsAlterTable()
380 public function supportsIdentityColumns()
388 public function getName()
396 public function getTruncateTableSQL($tableName, $cascade = false)
398 $tableName = str_replace(".", "__", $tableName);
399 return 'DELETE FROM '.$tableName;
403 * User-defined function for Sqlite that is used with PDO::sqliteCreateFunction()
405 * @param int|float $value
409 static public function udfSqrt($value)
415 * User-defined function for Sqlite that implements MOD(a, b)
422 static public function udfMod($a, $b)
429 * @param string $substr
430 * @param integer $offset
434 static public function udfLocate($str, $substr, $offset = 0)
436 $pos = strpos($str, $substr, $offset);
437 if ($pos !== false) {
444 public function getForUpdateSql()
452 protected function initializeDoctrineTypeMappings()
454 $this->doctrineTypeMapping = array(
455 'boolean' => 'boolean',
456 'tinyint' => 'boolean',
457 'smallint' => 'smallint',
458 'mediumint' => 'integer',
460 'integer' => 'integer',
461 'serial' => 'integer',
462 'bigint' => 'bigint',
463 'bigserial' => 'bigint',
465 'tinytext' => 'text',
466 'mediumtext' => 'text',
467 'longtext' => 'text',
469 'varchar' => 'string',
470 'longvarchar' => 'string',
471 'varchar2' => 'string',
472 'nvarchar' => 'string',
477 'datetime' => 'datetime',
478 'timestamp' => 'datetime',
482 'double precision' => 'float',
484 'decimal' => 'decimal',
485 'numeric' => 'decimal',
493 protected function getReservedKeywordsClass()
495 return 'Doctrine\DBAL\Platforms\Keywords\SQLiteKeywords';
501 public function getBlobTypeDeclarationSQL(array $field)
509 public function getTemporaryTableName($tableName)
511 $tableName = str_replace(".", "__", $tableName);
519 * Sqlite Platform emulates schema by underscoring each dot and generating tables
520 * into the default database.
522 * This hack is implemented to be able to use SQLite as testdriver when
523 * using schema supporting databases.
525 public function canEmulateSchemas()