lib/Driver/Sqlite/SqliteDriver.php
<?php declare(strict_types=1);
/**
* ___ _ _
* | _ \__ _| |_ __ _| |__ __ _ ___ ___
* | _/ _` | _/ _` | '_ \/ _` (_-</ -_)
* |_| \__,_|\__\__,_|_.__/\__,_/__/\___|
*
* This file is part of Kristuff\Patabase.
* (c) Kristuff <kristuff@kristuff.fr>
*
* For the full copyright and license information, please view the LICENSE
* file that was distributed with this source code.
*
* @version 1.0.1
* @copyright 2017-2022 Christophe Buliard
*/
namespace Kristuff\Patabase\Driver\Sqlite;
use Kristuff\Patabase\Driver\DatabaseDriver;
/**
* Class SqliteDriver
*
* SQLite 3 Driver
*
* Data types
* INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8 => INTEGER
* CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55),
* NATIVE CHARACTER(70), NVARCHAR(100), CLOB, TEXT => TEXT
* BLOB, no datatype specified => BLOB
* REAL, DOUBLE, DOUBLE PRECISION, FLOAT => REAL
* NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME => NUMERIC
*/
class SqliteDriver extends DatabaseDriver
{
/**
* List of DSN attributes
*
* In Sqlite, the database attribute represent the full path to the database
* To create a database in memory, define the database to :memory:
*
* @access protected
* @var array
*/
protected $dsnAttributes = array(
'database'
);
/**
* Create a new PDO connection
*
* @access public
* @param array $settings
*
* @return void
*/
public function createConnection(array $settings): void
{
$this->pdo = new \PDO('sqlite:'.$settings['database']);
//$this->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
//$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
// Foreign key constraints are disabled by default (for backwards compatibility),
// so must be enabled separately for each database connection.
// @see https://www.sqlite.org/foreignkeys.html
$this->enableForeignKeys();
}
/**
* Escape an identifier
*
* @access public
* @param string $identifier
*
* @return string
*/
public function escapeIdentifier(string $identifier): string
{
return '"'.$identifier.'"';
}
/**
* Escape a value
*
* @access public
* @param string $value
*
* @return string
*/
public function escapeValue(string $value): string
{
return '"'.$value.'"';
}
/**
* Get last inserted id
*
* @access public
* @return string
*/
public function lastInsertedId(): string
{
return $this->pdo->lastInsertId();
}
/**
* Get whether foreign keys are enabled or not
*
* https://www.sqlite.org/foreignkeys.html
* Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for
* each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints
* enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by
* default but will instead enable or disable them as necessary.) The application can also use a PRAGMA foreign_keys
* statement to determine if foreign keys are currently enabled.
* @access public
* @return bool True if foreign keys are enabled, otherwise false
*/
public function isForeignKeyEnabled(): bool
{
$query = $this->pdo->prepare('PRAGMA foreign_keys');
return $query->execute() && (int) $query->fetchColumn() === 1;
}
/**
* Enable foreign keys
*
* @access public
* @return void
*/
public function enableForeignKeys(): void
{
$this->pdo->exec('PRAGMA foreign_keys = ON');
}
/**
* Disable foreign keys
*
* @access public
* @return void
*/
public function disableForeignKeys(): void
{
$this->pdo->exec('PRAGMA foreign_keys = OFF');
}
/**
* Add a foreign key
*
* This is not supported on sqlite and returns false.
*
* @access public
* @param string $fkName The constraint name
* @param string $srcTable The source table
* @param string $srcColumn The source column
* @param string $refTable The referenced table
* @param string $refColumn The referenced column
*
* @return bool True if the foreign key has been created, otherwise false
*/
public function addForeignKey(string $fkName, string $srcTable, string $srcColumn, string $refTable, string $refColumn): bool
{
return false;
}
/**
* Drop a foreign key
*
* This is not supported on sqlite and returns false.
*
* @access public
* @param string $fkName The constraint name
* @param string $tableName The source table
*
* @return bool True if the foreign key has been dropped, otherwise false
*/
public function dropForeignKey(string $fkName, string $tableName): bool
{
return false;
}
/**
* Get the SQL for show tables
*
* @access public
* @return string
*/
public function sqlShowTables(): string
{
return 'SELECT name FROM sqlite_master WHERE type = "table";';
}
/**
* Get the SQL for random function
*
* @access public
* @param mixed $seed Random seed. Default is null.
*
* @return string
*/
public function sqlRandom($seed = null): string
{
$seed = !empty($seed) ? $seed : '';
return sprintf('random(%s)', $seed);
}
/**
* Get the SQL for auto increment column
*
* @access public
* @param string $type The sql column type
*
* @return string
*/
public function sqlColumnAutoIncrement(string $type): string
{
// http://www.sqlite.org/datatypes.html
// One exception to the typelessness of SQLite is a column whose type is INTEGER PRIMARY KEY. (And you
// must use "INTEGER" not "INT". A column of type INT PRIMARY KEY is typeless just like any other.)
// INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any attempt to insert non-integer data
// will result in an error. INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT.
//
// https://sqlite.org/autoinc.html
// If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment
// algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose
// of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.
return 'INTEGER';
}
}