|
1
|
|
|
<?php |
|
2
|
|
|
namespace Maphper\DataSource; |
|
3
|
|
|
|
|
4
|
|
|
class SqliteAdapter implements DatabaseAdapter { |
|
5
|
|
|
private $pdo; |
|
6
|
|
|
private $queryCache = []; |
|
7
|
|
|
|
|
8
|
|
|
public function __construct(\PDO $pdo) { |
|
9
|
|
|
$this->pdo = $pdo; |
|
10
|
|
|
} |
|
11
|
|
|
|
|
12
|
|
|
public function quote($str) { |
|
13
|
|
|
return '`' . str_replace('.', '`.`', trim($str, '`')) . '`'; |
|
14
|
|
|
} |
|
15
|
|
|
|
|
16
|
|
|
private function getCachedStmt($sql) { |
|
17
|
|
|
$queryId = md5($sql); |
|
18
|
|
|
if (isset($this->queryCache[$queryId])) $stmt = $this->queryCache[$queryId]; |
|
19
|
|
|
else { |
|
20
|
|
|
$stmt = $this->pdo->prepare($sql, [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY]); |
|
21
|
|
|
if ($stmt) $this->queryCache[$queryId] = $stmt; |
|
22
|
|
|
} |
|
23
|
|
|
return $stmt; |
|
24
|
|
|
} |
|
25
|
|
|
|
|
26
|
|
|
public function query(\Maphper\Lib\Query $query) { |
|
27
|
|
|
$queryId = md5($query->getSql()); |
|
28
|
|
|
$stmt = $this->getCachedStmt($query->getSql()); |
|
29
|
|
|
$args = $query->getArgs(); |
|
30
|
|
|
|
|
31
|
|
|
//Handle SQLite when PDO_ERRMODE is set to SILENT |
|
32
|
|
|
if ($stmt === false) throw new \Exception('Invalid query'); |
|
33
|
|
|
|
|
34
|
|
|
$stmt->execute($args); |
|
35
|
|
|
if ($stmt->errorCode() !== '00000' && $stmt->errorInfo()[2] == 'database schema has changed') { |
|
36
|
|
|
unset($this->queryCache[$queryId]); |
|
37
|
|
|
return $this->query($query); |
|
38
|
|
|
} |
|
39
|
|
|
|
|
40
|
|
|
if (substr($query->getSql(), 0, 6) === 'SELECT') return $stmt->fetchAll(\PDO::FETCH_OBJ); |
|
41
|
|
|
else return $stmt; |
|
42
|
|
|
} |
|
43
|
|
|
|
|
44
|
|
|
public function lastInsertId() { |
|
45
|
|
|
return $this->pdo->lastInsertId(); |
|
46
|
|
|
} |
|
47
|
|
|
|
|
48
|
|
|
private function getType($val) { |
|
49
|
|
|
if ($val instanceof \DateTime) return 'DATETIME'; |
|
50
|
|
|
else if (is_int($val)) return 'INTEGER'; |
|
51
|
|
|
else if (is_double($val)) return 'DECIMAL(9,' . strlen($val) - strrpos($val, '.') - 1 . ')'; |
|
52
|
|
|
else if (is_string($val) && strlen($val) < 256) return 'VARCHAR(255)'; |
|
53
|
|
|
else if (is_string($val) && strlen($val) > 256) return 'LONGBLOG'; |
|
54
|
|
|
else return 'VARCHAR(255)'; |
|
55
|
|
|
} |
|
56
|
|
|
|
|
57
|
|
|
private function tableExists($name) { |
|
58
|
|
|
$result = $this->pdo->query('SELECT name FROM sqlite_master WHERE type="table" and name="'. $name.'"'); |
|
59
|
|
|
return count($result->fetchAll()) == 1; |
|
60
|
|
|
} |
|
61
|
|
|
|
|
62
|
|
|
private function getColumns($table) { |
|
63
|
|
|
$result = $this->pdo->query('PRAGMA table_info(' . $table . ');')->fetchAll(\PDO::FETCH_OBJ); |
|
64
|
|
|
$return = []; |
|
65
|
|
|
foreach ($result as $row) { |
|
66
|
|
|
$return[] = $row->name; |
|
67
|
|
|
} |
|
68
|
|
|
return $return; |
|
69
|
|
|
} |
|
70
|
|
|
|
|
71
|
|
|
//Alter the database so that it can store $data |
|
72
|
|
|
public function alterDatabase($table, array $primaryKey, $data) { |
|
73
|
|
|
//Unset query cache, otherwise it causes: |
|
74
|
|
|
// SQLSTATE[HY000]: General error: 17 database schema has changed |
|
75
|
|
|
$this->queryCache = []; |
|
76
|
|
|
|
|
77
|
|
|
$affix = '_'.substr(md5($table), 0, 6); |
|
78
|
|
|
$this->createTable($table . $affix, $primaryKey, $data); |
|
79
|
|
|
$fields = []; |
|
80
|
|
|
foreach ($data as $key => $value) { $fields[] = $key; } |
|
81
|
|
|
try { |
|
82
|
|
|
if ($this->tableExists($table)) { |
|
83
|
|
|
$columns = implode(', ', $this->getColumns($table)); |
|
84
|
|
|
|
|
85
|
|
|
$this->pdo->query('INSERT INTO ' . $this->quote($table . $affix) . '(' . $columns . ') SELECT ' . $columns . ' FROM ' . $this->quote($table)); |
|
86
|
|
|
$this->pdo->query('DROP TABLE IF EXISTS ' . $table ); |
|
87
|
|
|
} |
|
88
|
|
|
} |
|
89
|
|
|
catch (\PDOException $e) { |
|
90
|
|
|
// No data to copy |
|
91
|
|
|
echo $e->getMessage(); |
|
92
|
|
|
} |
|
93
|
|
|
|
|
94
|
|
|
$this->pdo->query('DROP TABLE IF EXISTS ' . $table ); |
|
95
|
|
|
$this->pdo->query('ALTER TABLE ' . $table . $affix. ' RENAME TO '. $table ); |
|
96
|
|
|
|
|
97
|
|
|
} |
|
98
|
|
|
|
|
99
|
|
|
public function createTable($table, array $primaryKey, $data) { |
|
100
|
|
|
$parts = []; |
|
101
|
|
|
foreach ($primaryKey as $key) { |
|
102
|
|
|
$pk = $data->$key; |
|
103
|
|
|
if ($pk == null) $parts[] = $key . ' INTEGER'; |
|
104
|
|
|
else $parts[] = $key . ' ' . $this->getType($pk) . ' NOT NULL'; |
|
105
|
|
|
} |
|
106
|
|
|
|
|
107
|
|
|
$pkField = implode(', ', $parts) . ', PRIMARY KEY(' . implode(', ', $primaryKey) . ')'; |
|
108
|
|
|
|
|
109
|
|
|
$this->pdo->query('DROP TABLE IF EXISTS ' . $table ); |
|
110
|
|
|
$this->pdo->query('CREATE TABLE ' . $table . ' (' . $pkField . ')'); |
|
111
|
|
|
|
|
112
|
|
|
foreach ($data as $key => $value) { |
|
113
|
|
|
if (is_array($value) || (is_object($value) && !($value instanceof \DateTime))) continue; |
|
114
|
|
|
if (in_array($key, $primaryKey)) continue; |
|
115
|
|
|
|
|
116
|
|
|
$type = $this->getType($value); |
|
117
|
|
|
|
|
118
|
|
|
$this->pdo->query('ALTER TABLE ' . $table . ' ADD ' . $this->quote($key) . ' ' . $type); |
|
119
|
|
|
} |
|
120
|
|
|
} |
|
121
|
|
|
|
|
122
|
|
|
|
|
123
|
|
|
public function addIndex($table, array $fields) { |
|
124
|
|
|
if (empty($fields)) return false; |
|
125
|
|
|
|
|
126
|
|
|
//SQLite doesn't support ASC/DESC indexes, remove the keywords |
|
127
|
|
|
foreach ($fields as &$field) $field = str_ireplace([' desc', ' asc'], '', $field); |
|
128
|
|
|
sort($fields); |
|
129
|
|
|
$fields = array_map('strtolower', $fields); |
|
130
|
|
|
$fields = array_map('trim', $fields); |
|
131
|
|
|
$keyName = implode('_', $fields); |
|
132
|
|
|
|
|
133
|
|
|
|
|
134
|
|
|
try { |
|
135
|
|
|
$this->pdo->query('CREATE INDEX IF NOT EXISTS ' . $keyName . ' ON ' . $table . ' (' . implode(', ', $fields) . ')'); |
|
136
|
|
|
} |
|
137
|
|
|
catch (\Exception $e) { |
|
|
|
|
|
|
138
|
|
|
|
|
139
|
|
|
} |
|
140
|
|
|
} |
|
141
|
|
|
|
|
142
|
|
|
public function optimiseColumns($table) { |
|
143
|
|
|
//TODO |
|
144
|
|
|
} |
|
145
|
|
|
} |
Level-2 /
Maphper
Loading history...