File size: 4,698 Bytes
e4f4821 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 |
<?php
namespace PicoDb\Driver;
use PDO;
use PDOException;
/**
* Sqlite Driver
*
* @package PicoDb\Driver
* @author Frederic Guillot
*/
class Sqlite extends Base
{
/**
* List of required settings options
*
* @access protected
* @var array
*/
protected $requiredAttributes = array('filename');
/**
* Create a new PDO connection
*
* @access public
* @param array $settings
*/
public function createConnection(array $settings)
{
$options = [];
// Set a default timeout of 30 seconds to reduce "database is locked" errors.
$options[PDO::ATTR_TIMEOUT] = (! empty($settings['timeout'])) ? $settings['timeout'] : 30;
$this->pdo = new PDO('sqlite:'.$settings['filename'], null, null, $options);
// Enabling WAL mode by default should also reduce the "database is locked" errors.
// Official docs: https://sqlite.org/wal.html
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();
}
/**
* Enable foreign keys
*
* @access public
*/
public function enableForeignKeys()
{
$this->pdo->exec('PRAGMA foreign_keys = ON');
}
/**
* Disable foreign keys
*
* @access public
*/
public function disableForeignKeys()
{
$this->pdo->exec('PRAGMA foreign_keys = OFF');
}
/**
* Return true if the error code is a duplicate key
*
* @access public
* @param integer $code
* @return boolean
*/
public function isDuplicateKeyError($code)
{
return $code == 23000;
}
/**
* Escape identifier
*
* @access public
* @param string $identifier
* @return string
*/
public function escape($identifier)
{
return '"'.$identifier.'"';
}
/**
* Get non standard operator
*
* @access public
* @param string $operator
* @return string
*/
public function getOperator($operator)
{
if ($operator === 'LIKE' || $operator === 'ILIKE') {
return 'LIKE';
}
return '';
}
/**
* Get last inserted id
*
* @access public
* @return integer
*/
public function getLastId()
{
return $this->pdo->lastInsertId();
}
/**
* Get current schema version
*
* @access public
* @return integer
*/
public function getSchemaVersion()
{
$rq = $this->pdo->prepare('PRAGMA user_version');
$rq->execute();
return (int) $rq->fetchColumn();
}
/**
* Set current schema version
*
* @access public
* @param integer $version
*/
public function setSchemaVersion($version)
{
$this->pdo->exec('PRAGMA user_version='.$version);
}
/**
* Upsert for a key/value variable
*
* @access public
* @param string $table
* @param string $keyColumn
* @param string $valueColumn
* @param array $dictionary
* @return bool False on failure
*/
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;
}
}
/**
* Run EXPLAIN command
*
* @access public
* @param string $sql
* @param array $values
* @return array
*/
public function explain($sql, array $values)
{
return $this->getConnection()->query('EXPLAIN QUERY PLAN '.$this->getSqlFromPreparedStatement($sql, $values))->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Get database version
*
* @access public
* @return array
*/
public function getDatabaseVersion()
{
return $this->getConnection()->query('SELECT sqlite_version()')->fetchColumn();
}
}
|