|
<?php |
|
|
|
namespace PicoDb\Driver; |
|
|
|
use PDO; |
|
use PDOException; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
class Sqlite extends Base |
|
{ |
|
|
|
|
|
|
|
|
|
|
|
|
|
protected $requiredAttributes = array('filename'); |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function createConnection(array $settings) |
|
{ |
|
$options = []; |
|
|
|
|
|
$options[PDO::ATTR_TIMEOUT] = (! empty($settings['timeout'])) ? $settings['timeout'] : 30; |
|
|
|
$this->pdo = new PDO('sqlite:'.$settings['filename'], null, null, $options); |
|
|
|
|
|
|
|
if (isset($settings['wal_mode']) && $settings['wal_mode'] === true) { |
|
$this->pdo->exec('PRAGMA journal_mode=wal'); |
|
$this->pdo->exec('PRAGMA wal_autocheckpoint = 0'); |
|
$this->pdo->exec('PRAGMA synchronous=NORMAL'); |
|
} |
|
|
|
$this->enableForeignKeys(); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
public function enableForeignKeys() |
|
{ |
|
$this->pdo->exec('PRAGMA foreign_keys = ON'); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
public function disableForeignKeys() |
|
{ |
|
$this->pdo->exec('PRAGMA foreign_keys = OFF'); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function isDuplicateKeyError($code) |
|
{ |
|
return $code == 23000; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function escape($identifier) |
|
{ |
|
return '"'.$identifier.'"'; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function getOperator($operator) |
|
{ |
|
if ($operator === 'LIKE' || $operator === 'ILIKE') { |
|
return 'LIKE'; |
|
} |
|
|
|
return ''; |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function getLastId() |
|
{ |
|
return $this->pdo->lastInsertId(); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function getSchemaVersion() |
|
{ |
|
$rq = $this->pdo->prepare('PRAGMA user_version'); |
|
$rq->execute(); |
|
|
|
return (int) $rq->fetchColumn(); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function setSchemaVersion($version) |
|
{ |
|
$this->pdo->exec('PRAGMA user_version='.$version); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function upsert($table, $keyColumn, $valueColumn, array $dictionary) |
|
{ |
|
try { |
|
$this->pdo->beginTransaction(); |
|
|
|
foreach ($dictionary as $key => $value) { |
|
|
|
$sql = sprintf( |
|
'INSERT OR REPLACE INTO %s (%s, %s) VALUES (?, ?)', |
|
$this->escape($table), |
|
$this->escape($keyColumn), |
|
$this->escape($valueColumn) |
|
); |
|
|
|
$rq = $this->pdo->prepare($sql); |
|
$rq->execute(array($key, $value)); |
|
} |
|
|
|
$this->pdo->commit(); |
|
|
|
return true; |
|
} |
|
catch (PDOException $e) { |
|
$this->pdo->rollBack(); |
|
return false; |
|
} |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function explain($sql, array $values) |
|
{ |
|
return $this->getConnection()->query('EXPLAIN QUERY PLAN '.$this->getSqlFromPreparedStatement($sql, $values))->fetchAll(PDO::FETCH_ASSOC); |
|
} |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
public function getDatabaseVersion() |
|
{ |
|
return $this->getConnection()->query('SELECT sqlite_version()')->fetchColumn(); |
|
} |
|
} |
|
|