silverstripe-sqlite3/code/SQLite3Database.php

754 lines
23 KiB
PHP
Raw Permalink Normal View History

2015-12-17 19:11:01 +01:00
<?php
namespace SilverStripe\SQLite;
2016-08-29 05:56:13 +02:00
use SilverStripe\Assets\File;
use SilverStripe\Core\Config\Configurable;
use SilverStripe\Core\Convert;
use SilverStripe\Dev\Deprecation;
use SilverStripe\ORM\ArrayList;
2016-09-09 05:46:48 +02:00
use SilverStripe\ORM\Connect\Database;
2016-08-29 05:56:13 +02:00
use SilverStripe\ORM\DataList;
2016-08-11 07:18:20 +02:00
use SilverStripe\ORM\DataObject;
2016-08-29 05:56:13 +02:00
use SilverStripe\ORM\PaginatedList;
use SilverStripe\ORM\Queries\SQLSelect;
2015-12-17 19:11:01 +01:00
/**
* SQLite database controller class
*/
2016-09-09 05:46:48 +02:00
class SQLite3Database extends Database
2015-12-17 19:11:01 +01:00
{
2016-08-29 05:56:13 +02:00
use Configurable;
/**
* Global environment config for setting 'path'
*/
const ENV_PATH = 'SS_SQLITE_DATABASE_PATH';
/**
* Global environment config for setting 'key'
*/
const ENV_KEY = 'SS_SQLITE_DATABASE_KEY';
2016-08-29 05:56:13 +02:00
/**
* Extension added to every database name
*
* @config
* @var string
*/
private static $database_extension = '.sqlite';
2015-12-17 19:11:01 +01:00
/**
* Database schema manager object
*
2015-12-17 19:11:01 +01:00
* @var SQLite3SchemaManager
*/
protected $schemaManager = null;
/*
* This holds the parameters that the original connection was created with,
* so we can switch back to it if necessary (used for unit tests)
*
2015-12-17 19:11:01 +01:00
* @var array
*/
protected $parameters;
/*
* if we're on a In-Memory db
*
2015-12-17 19:11:01 +01:00
* @var boolean
*/
protected $livesInMemory = false;
2018-02-09 12:24:35 +01:00
/**
* @var bool
*/
protected $transactionNesting = 0;
/**
* @var array
*/
protected $transactionSavepoints = [];
2015-12-17 19:11:01 +01:00
/**
* List of default pragma values
*
2015-12-17 19:11:01 +01:00
* @todo Migrate to SS config
*
* @var array
*/
public static $default_pragma = array(
'encoding' => '"UTF-8"',
'locking_mode' => 'NORMAL'
);
/**
* Extension used to distinguish between sqllite database files and other files.
* Required to handle multiple databases.
*
2015-12-17 19:11:01 +01:00
* @return string
*/
public static function database_extension()
{
2016-08-29 05:56:13 +02:00
return static::config()->get('database_extension');
2015-12-17 19:11:01 +01:00
}
/**
* Check if a database name has a valid extension
*
2015-12-17 19:11:01 +01:00
* @param string $name
* @return boolean
*/
public static function is_valid_database_name($name)
{
$extension = self::database_extension();
if (empty($extension)) {
return true;
}
return substr_compare($name, $extension, -strlen($extension), strlen($extension)) === 0;
}
/**
* Connect to a SQLite3 database.
* @param array $parameters An map of parameters, which should include:
* - database: The database to connect to, with the correct file extension (.sqlite)
* - path: the path to the SQLite3 database file
* - key: the encryption key (needs testing)
* - memory: use the faster In-Memory database for unit tests
*/
public function connect($parameters)
{
if (!empty($parameters['memory'])) {
Deprecation::notice(
'1.4.0',
"\$databaseConfig['memory'] is deprecated. Use \$databaseConfig['path'] = ':memory:' instead.",
Deprecation::SCOPE_GLOBAL
);
unset($parameters['memory']);
$parameters['path'] = ':memory:';
}
2015-12-17 19:11:01 +01:00
//We will store these connection parameters for use elsewhere (ie, unit tests)
$this->parameters = $parameters;
$this->schemaManager->flushCache();
// Ensure database name is set
if (empty($parameters['database'])) {
2016-08-29 05:56:13 +02:00
$parameters['database'] = 'database';
2015-12-17 19:11:01 +01:00
}
// use the very lightspeed SQLite In-Memory feature for testing
if ($this->getLivesInMemory()) {
$file = ':memory:';
} else {
// Ensure path is given
$path = $this->getPath();
2015-12-17 19:11:01 +01:00
//assumes that the path to dbname will always be provided:
$file = $path . '/' . $parameters['database'] . self::database_extension();
if (!file_exists($path)) {
SQLiteDatabaseConfigurationHelper::create_db_dir($path);
SQLiteDatabaseConfigurationHelper::secure_db_dir($path);
2015-12-17 19:11:01 +01:00
}
}
2015-12-17 19:11:01 +01:00
// 'path' and 'database' are merged into the full file path, which
// is the format that connectors such as PDOConnector expect
$parameters['filepath'] = $file;
// Ensure that driver is available (required by PDO)
if (empty($parameters['driver'])) {
$parameters['driver'] = $this->getDatabaseServer();
}
$this->connector->connect($parameters, true);
foreach (self::$default_pragma as $pragma => $value) {
$this->setPragma($pragma, $value);
}
if (empty(self::$default_pragma['locking_mode'])) {
self::$default_pragma['locking_mode'] = $this->getPragma('locking_mode');
}
}
/**
* Retrieve parameters used to connect to this SQLLite database
*
2015-12-17 19:11:01 +01:00
* @return array
*/
public function getParameters()
{
return $this->parameters;
}
/**
* Determine if this Db is in memory
*
* @return bool
*/
2015-12-17 19:11:01 +01:00
public function getLivesInMemory()
{
return isset($this->parameters['path']) && $this->parameters['path'] === ':memory:';
}
/**
* Get file path. If in memory this is null
*
* @return string|null
*/
public function getPath()
{
if ($this->getLivesInMemory()) {
return null;
}
if (empty($this->parameters['path'])) {
return ASSETS_PATH . '/.sqlitedb';
}
return $this->parameters['path'];
}
2015-12-17 19:11:01 +01:00
public function supportsCollations()
{
return true;
}
public function supportsTimezoneOverride()
{
return false;
}
/**
* Execute PRAGMA commands.
*
* @param string $pragma name
* @param string $value to set
2015-12-17 19:11:01 +01:00
*/
public function setPragma($pragma, $value)
{
$this->query("PRAGMA $pragma = $value");
}
/**
* Gets pragma value.
*
* @param string $pragma name
2015-12-17 19:11:01 +01:00
* @return string the pragma value
*/
public function getPragma($pragma)
{
return $this->query("PRAGMA $pragma")->value();
}
public function getDatabaseServer()
{
return "sqlite";
}
public function selectDatabase($name, $create = false, $errorLevel = E_USER_ERROR)
{
if (!$this->schemaManager->databaseExists($name)) {
// Check DB creation permisson
if (!$create) {
if ($errorLevel !== false) {
user_error("Attempted to connect to non-existing database \"$name\"", $errorLevel);
}
// Unselect database
$this->connector->unloadDatabase();
return false;
}
$this->schemaManager->createDatabase($name);
}
// Reconnect using the existing parameters
$parameters = $this->parameters;
$parameters['database'] = $name;
$this->connect($parameters);
return true;
}
public function now()
{
return "datetime('now', 'localtime')";
}
public function random()
{
return 'random()';
}
/**
* The core search engine configuration.
* @todo There is a fulltext search for SQLite making use of virtual tables, the fts3 extension and the
* MATCH operator
* there are a few issues with fts:
* - shared cached lock doesn't allow to create virtual tables on versions prior to 3.6.17
* - there must not be more than one MATCH operator per statement
* - the fts3 extension needs to be available
* for now we use the MySQL implementation with the MATCH()AGAINST() uglily replaced with LIKE
*
* @param array $classesToSearch
2015-12-17 19:11:01 +01:00
* @param string $keywords Keywords as a space separated string
* @param int $start
* @param int $pageLength
* @param string $sortBy
* @param string $extraFilter
* @param bool $booleanSearch
* @param string $alternativeFileFilter
* @param bool $invertedMatch
* @return PaginatedList DataObjectSet of result pages
2015-12-17 19:11:01 +01:00
*/
public function searchEngine(
$classesToSearch,
$keywords,
$start,
$pageLength,
$sortBy = "Relevance DESC",
$extraFilter = "",
$booleanSearch = false,
$alternativeFileFilter = "",
$invertedMatch = false
2015-12-17 19:11:01 +01:00
) {
$start = (int)$start;
$pageLength = (int)$pageLength;
2015-12-17 19:11:01 +01:00
$keywords = $this->escapeString(str_replace(array('*', '+', '-', '"', '\''), '', $keywords));
$htmlEntityKeywords = htmlentities(utf8_decode($keywords));
2016-08-11 07:18:20 +02:00
$pageClass = 'SilverStripe\\CMS\\Model\\SiteTree';
$fileClass = 'SilverStripe\\Assets\\File';
2016-08-11 07:18:20 +02:00
$extraFilters = array($pageClass => '', $fileClass => '');
2015-12-17 19:11:01 +01:00
if ($extraFilter) {
2016-08-11 07:18:20 +02:00
$extraFilters[$pageClass] = " AND $extraFilter";
2015-12-17 19:11:01 +01:00
if ($alternativeFileFilter) {
2016-08-11 07:18:20 +02:00
$extraFilters[$fileClass] = " AND $alternativeFileFilter";
2015-12-17 19:11:01 +01:00
} else {
2016-08-11 07:18:20 +02:00
$extraFilters[$fileClass] = $extraFilters[$pageClass];
2015-12-17 19:11:01 +01:00
}
}
// Always ensure that only pages with ShowInSearch = 1 can be searched
2016-08-11 07:18:20 +02:00
$extraFilters[$pageClass] .= ' AND ShowInSearch <> 0';
// File.ShowInSearch was added later, keep the database driver backwards compatible
2015-12-17 19:11:01 +01:00
// by checking for its existence first
2016-10-10 00:13:18 +02:00
if (File::singleton()->getSchema()->fieldSpec(File::class, 'ShowInSearch')) {
2016-08-11 07:18:20 +02:00
$extraFilters[$fileClass] .= " AND ShowInSearch <> 0";
2015-12-17 19:11:01 +01:00
}
$limit = $start . ", " . $pageLength;
2015-12-17 19:11:01 +01:00
$notMatch = $invertedMatch ? "NOT " : "";
if ($keywords) {
$match[$pageClass] =
"(Title LIKE '%$keywords%' OR MenuTitle LIKE '%$keywords%' OR Content LIKE '%$keywords%'"
. " OR MetaDescription LIKE '%$keywords%' OR Title LIKE '%$htmlEntityKeywords%'"
. " OR MenuTitle LIKE '%$htmlEntityKeywords%' OR Content LIKE '%$htmlEntityKeywords%'"
. " OR MetaDescription LIKE '%$htmlEntityKeywords%')";
2016-08-11 07:18:20 +02:00
$fileClassSQL = Convert::raw2sql($fileClass);
$match[$fileClass] =
"(Name LIKE '%$keywords%' OR Title LIKE '%$keywords%') AND ClassName = '$fileClassSQL'";
2015-12-17 19:11:01 +01:00
// We make the relevance search by converting a boolean mode search into a normal one
$relevanceKeywords = $keywords;
$htmlEntityRelevanceKeywords = $htmlEntityKeywords;
$relevance[$pageClass] =
"(Title LIKE '%$relevanceKeywords%' OR MenuTitle LIKE '%$relevanceKeywords%'"
. " OR Content LIKE '%$relevanceKeywords%' OR MetaDescription LIKE '%$relevanceKeywords%')"
. " + (Title LIKE '%$htmlEntityRelevanceKeywords%' OR MenuTitle LIKE '%$htmlEntityRelevanceKeywords%'"
2018-02-13 02:18:48 +01:00
. " OR Content LIKE '%$htmlEntityRelevanceKeywords%' OR MetaDescription "
. " LIKE '%$htmlEntityRelevanceKeywords%')";
2016-08-11 07:18:20 +02:00
$relevance[$fileClass] = "(Name LIKE '%$relevanceKeywords%' OR Title LIKE '%$relevanceKeywords%')";
2015-12-17 19:11:01 +01:00
} else {
2016-08-11 07:18:20 +02:00
$relevance[$pageClass] = $relevance[$fileClass] = 1;
$match[$pageClass] = $match[$fileClass] = "1 = 1";
2015-12-17 19:11:01 +01:00
}
2016-08-11 07:18:20 +02:00
// Generate initial queries
2015-12-17 19:11:01 +01:00
$queries = array();
foreach ($classesToSearch as $class) {
$queries[$class] = DataList::create($class)
->where($notMatch . $match[$class] . $extraFilters[$class])
->dataQuery()
->query();
2015-12-17 19:11:01 +01:00
}
// Make column selection lists
$select = array(
2016-08-11 07:18:20 +02:00
$pageClass => array(
2015-12-17 19:11:01 +01:00
"\"ClassName\"",
"\"ID\"",
"\"ParentID\"",
"\"Title\"",
"\"URLSegment\"",
"\"Content\"",
"\"LastEdited\"",
"\"Created\"",
"NULL AS \"Name\"",
"\"CanViewType\"",
2016-08-11 07:18:20 +02:00
$relevance[$pageClass] . " AS Relevance"
2015-12-17 19:11:01 +01:00
),
2016-08-11 07:18:20 +02:00
$fileClass => array(
2015-12-17 19:11:01 +01:00
"\"ClassName\"",
"\"ID\"",
"NULL AS \"ParentID\"",
"\"Title\"",
"NULL AS \"URLSegment\"",
"NULL AS \"Content\"",
"\"LastEdited\"",
"\"Created\"",
"\"Name\"",
"NULL AS \"CanViewType\"",
2016-08-11 07:18:20 +02:00
$relevance[$fileClass] . " AS Relevance"
2015-12-17 19:11:01 +01:00
)
);
// Process queries
foreach ($classesToSearch as $class) {
// There's no need to do all that joining
2016-08-11 07:18:20 +02:00
$queries[$class]->setFrom('"'.DataObject::getSchema()->baseDataTable($class).'"');
2015-12-17 19:11:01 +01:00
$queries[$class]->setSelect(array());
foreach ($select[$class] as $clause) {
2022-07-20 09:17:01 +02:00
if (preg_match('/^(.*) +AS +"?([^"]*)"?/i', $clause ?? '', $matches)) {
2015-12-17 19:11:01 +01:00
$queries[$class]->selectField($matches[1], $matches[2]);
} else {
$queries[$class]->selectField(str_replace('"', '', $clause));
}
}
$queries[$class]->setOrderBy(array());
}
// Combine queries
$querySQLs = array();
$queryParameters = array();
$totalCount = 0;
foreach ($queries as $query) {
/** @var SQLSelect $query */
2015-12-17 19:11:01 +01:00
$querySQLs[] = $query->sql($parameters);
$queryParameters = array_merge($queryParameters, $parameters);
$totalCount += $query->unlimitedRowCount();
}
$fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY $sortBy LIMIT $limit";
// Get records
$records = $this->preparedQuery($fullQuery, $queryParameters);
foreach ($records as $record) {
$objects[] = new $record['ClassName']($record);
}
if (isset($objects)) {
$doSet = new ArrayList($objects);
} else {
$doSet = new ArrayList();
}
$list = new PaginatedList($doSet);
$list->setPageStart($start);
$list->setPageLength($pageLength);
2015-12-17 19:11:01 +01:00
$list->setTotalItems($totalCount);
return $list;
}
/*
* Does this database support transactions?
*/
public function supportsTransactions()
{
return version_compare($this->getVersion(), '3.6', '>=');
}
/**
* Does this database support transaction modes?
*
* SQLite doesn't support transaction modes.
*
* @param string $mode
* @return bool
*/
public function supportsTransactionMode(string $mode): bool
{
return false;
}
2015-12-17 19:11:01 +01:00
public function supportsExtensions($extensions = array('partitions', 'tablespaces', 'clustering'))
{
if (isset($extensions['partitions'])) {
return true;
} elseif (isset($extensions['tablespaces'])) {
return true;
} elseif (isset($extensions['clustering'])) {
return true;
} else {
return false;
}
}
public function transactionStart($transaction_mode = false, $session_characteristics = false)
{
if ($this->transactionDepth()) {
$this->transactionSavepoint('NESTEDTRANSACTION' . $this->transactionDepth());
2018-02-09 12:24:35 +01:00
} else {
$this->query('BEGIN');
$this->transactionDepthIncrease();
2018-02-09 12:24:35 +01:00
}
2015-12-17 19:11:01 +01:00
}
public function transactionSavepoint($savepoint)
{
$this->query("SAVEPOINT \"$savepoint\"");
$this->transactionDepthIncrease($savepoint);
2015-12-17 19:11:01 +01:00
}
/**
* Fetch the name of the most recent savepoint
*
* @return string
*/
protected function getTransactionSavepointName()
{
return end($this->transactionSavepoints);
}
2015-12-17 19:11:01 +01:00
public function transactionRollback($savepoint = false)
{
// Named transaction
2015-12-17 19:11:01 +01:00
if ($savepoint) {
$this->query("ROLLBACK TO $savepoint;");
$this->transactionDepthDecrease();
return true;
}
// Fail if transaction isn't available
if (!$this->transactionDepth()) {
return false;
}
if ($this->transactionIsNested()) {
$this->transactionRollback($this->getTransactionSavepointName());
2015-12-17 19:11:01 +01:00
} else {
$this->query('ROLLBACK;');
$this->transactionDepthDecrease();
2015-12-17 19:11:01 +01:00
}
return true;
}
public function transactionDepth()
{
return $this->transactionNesting;
2015-12-17 19:11:01 +01:00
}
public function transactionEnd($chain = false)
{
// Fail if transaction isn't available
if (!$this->transactionDepth()) {
return false;
}
if ($this->transactionIsNested()) {
$savepoint = $this->getTransactionSavepointName();
$this->query('RELEASE ' . $savepoint);
$this->transactionDepthDecrease();
} else {
$this->query('COMMIT;');
$this->resetTransactionNesting();
}
if ($chain) {
$this->transactionStart();
}
return true;
}
/**
* Indicate whether or not the current transaction is nested
* Returns false if there are no transactions, or the open
* transaction is the 'outer' transaction, i.e. not nested.
*
* @return bool
*/
protected function transactionIsNested()
{
return $this->transactionNesting > 1;
}
/**
* Increase the nested transaction level by one
* savepoint tracking is optional because BEGIN
* opens a transaction, but is not a named reference
*
* @param string $savepoint
*/
protected function transactionDepthIncrease($savepoint = null)
{
++$this->transactionNesting;
if ($savepoint) {
array_push($this->transactionSavepoints, $savepoint);
}
}
/**
* Decrease the nested transaction level by one
* and reduce the savepoint tracking if we are
* nesting, as the last one is no longer valid
*/
protected function transactionDepthDecrease()
{
if ($this->transactionIsNested()) {
array_pop($this->transactionSavepoints);
}
--$this->transactionNesting;
}
/**
* In error condition, set transactionNesting to zero
*/
protected function resetTransactionNesting()
{
$this->transactionNesting = 0;
$this->transactionSavepoints = [];
}
public function query($sql, $errorLevel = E_USER_ERROR)
{
return parent::query($sql, $errorLevel);
}
public function preparedQuery($sql, $parameters, $errorLevel = E_USER_ERROR)
{
return parent::preparedQuery($sql, $parameters, $errorLevel);
}
/**
* Inspect a SQL query prior to execution
* @deprecated 2.2.0:3.0.0
* @param string $sql
*/
protected function inspectQuery($sql)
{
// no-op
2015-12-17 19:11:01 +01:00
}
public function clearTable($table)
{
$this->query("DELETE FROM \"$table\"");
}
public function comparisonClause(
$field,
$value,
$exact = false,
$negate = false,
$caseSensitive = null,
2015-12-17 19:11:01 +01:00
$parameterised = false
) {
if ($exact && !$caseSensitive) {
$comp = ($negate) ? '!=' : '=';
} else {
if ($caseSensitive) {
// GLOB uses asterisks as wildcards.
// Replace them in search string, without replacing escaped percetage signs.
$comp = 'GLOB';
$value = preg_replace('/^%([^\\\\])/', '*$1', $value);
$value = preg_replace('/([^\\\\])%$/', '$1*', $value);
$value = preg_replace('/([^\\\\])%/', '$1*', $value);
} else {
$comp = 'LIKE';
}
if ($negate) {
$comp = 'NOT ' . $comp;
}
}
if ($parameterised) {
return sprintf("%s %s ?", $field, $comp);
} else {
return sprintf("%s %s '%s'", $field, $comp, $value);
}
}
public function formattedDatetimeClause($date, $format)
{
2022-07-20 09:17:01 +02:00
preg_match_all('/%(.)/', $format ?? '', $matches);
2015-12-17 19:11:01 +01:00
foreach ($matches[1] as $match) {
if (array_search($match, array('Y', 'm', 'd', 'H', 'i', 's', 'U')) === false) {
user_error('formattedDatetimeClause(): unsupported format character %' . $match, E_USER_WARNING);
}
}
$translate = array(
'/%i/' => '%M',
'/%s/' => '%S',
'/%U/' => '%s',
);
$format = preg_replace(array_keys($translate), array_values($translate), $format);
$modifiers = array();
if ($format == '%s' && $date != 'now') {
$modifiers[] = 'utc';
}
if ($format != '%s' && $date == 'now') {
$modifiers[] = 'localtime';
}
2022-07-20 09:17:01 +02:00
if (preg_match('/^now$/i', $date ?? '')) {
2015-12-17 19:11:01 +01:00
$date = "'now'";
2022-07-20 09:17:01 +02:00
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date ?? '')) {
2015-12-17 19:11:01 +01:00
$date = "'$date'";
}
$modifier = empty($modifiers) ? '' : ", '" . implode("', '", $modifiers) . "'";
return "strftime('$format', $date$modifier)";
}
public function datetimeIntervalClause($date, $interval)
{
$modifiers = array();
if ($date == 'now') {
$modifiers[] = 'localtime';
}
2022-07-20 09:17:01 +02:00
if (preg_match('/^now$/i', $date ?? '')) {
2015-12-17 19:11:01 +01:00
$date = "'now'";
2022-07-20 09:17:01 +02:00
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date ?? '')) {
2015-12-17 19:11:01 +01:00
$date = "'$date'";
}
$modifier = empty($modifiers) ? '' : ", '" . implode("', '", $modifiers) . "'";
return "datetime($date$modifier, '$interval')";
}
public function datetimeDifferenceClause($date1, $date2)
{
$modifiers1 = array();
$modifiers2 = array();
if ($date1 == 'now') {
$modifiers1[] = 'localtime';
}
if ($date2 == 'now') {
$modifiers2[] = 'localtime';
}
2022-07-20 09:17:01 +02:00
if (preg_match('/^now$/i', $date1 ?? '')) {
2015-12-17 19:11:01 +01:00
$date1 = "'now'";
2022-07-20 09:17:01 +02:00
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date1 ?? '')) {
2015-12-17 19:11:01 +01:00
$date1 = "'$date1'";
}
2022-07-20 09:17:01 +02:00
if (preg_match('/^now$/i', $date2 ?? '')) {
2015-12-17 19:11:01 +01:00
$date2 = "'now'";
2022-07-20 09:17:01 +02:00
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date2 ?? '')) {
2015-12-17 19:11:01 +01:00
$date2 = "'$date2'";
}
$modifier1 = empty($modifiers1) ? '' : ", '" . implode("', '", $modifiers1) . "'";
$modifier2 = empty($modifiers2) ? '' : ", '" . implode("', '", $modifiers2) . "'";
return "strftime('%s', $date1$modifier1) - strftime('%s', $date2$modifier2)";
}
}