|
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
|
|
|
public function query(\Maphper\Lib\Query $query) { |
|
17
|
|
|
$queryId = md5($query->getSql()); |
|
18
|
|
View Code Duplication |
if (isset($this->queryCache[$queryId])) $stmt = $this->queryCache[$queryId]; |
|
|
|
|
|
|
19
|
|
|
else { |
|
20
|
|
|
$stmt = $this->pdo->prepare($query->getSql(), [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY]); |
|
21
|
|
|
if ($stmt) $this->queryCache[$queryId] = $stmt; |
|
22
|
|
|
} |
|
23
|
|
|
|
|
24
|
|
|
$args = $query->getArgs(); |
|
25
|
|
View Code Duplication |
foreach ($args as &$arg) if ($arg instanceof \DateTime) { |
|
|
|
|
|
|
26
|
|
|
if ($arg->format('H:i:s') == '00:00:00') $arg = $arg->format('Y-m-d'); |
|
27
|
|
|
else $arg = $arg->format('Y-m-d H:i:s'); |
|
28
|
|
|
} |
|
29
|
|
|
|
|
30
|
|
|
if ($stmt !== false) { |
|
31
|
|
|
try { |
|
32
|
|
|
if (count($args) > 0) $res = $stmt->execute($args); |
|
|
|
|
|
|
33
|
|
|
else $res = $stmt->execute(); |
|
34
|
|
|
if ($stmt->errorCode() !== '00000') throw new \Exception('Invalid query'); |
|
35
|
|
|
if (substr($query->getSql(), 0, 6) === 'SELECT') return $stmt->fetchAll(\PDO::FETCH_OBJ); |
|
36
|
|
|
else return $stmt; |
|
37
|
|
|
} |
|
38
|
|
|
catch (\Exception $e) { |
|
39
|
|
|
//SQLite causes an error if when the DB schema changes, rebuild $stmt and try again. |
|
40
|
|
|
if ($stmt->errorInfo()[2] == 'database schema has changed') { |
|
41
|
|
|
unset($this->queryCache[$queryId]); |
|
42
|
|
|
return $this->query($query); |
|
43
|
|
|
} |
|
44
|
|
|
else return $stmt; |
|
45
|
|
|
} |
|
46
|
|
|
} |
|
47
|
|
|
//Handle SQLite when PDO_ERRMODE is set to SILENT |
|
48
|
|
|
else { |
|
49
|
|
|
throw new \Exception('Invalid query'); |
|
50
|
|
|
} |
|
51
|
|
|
} |
|
52
|
|
|
|
|
53
|
|
|
public function lastInsertId() { |
|
54
|
|
|
return $this->pdo->lastInsertId(); |
|
55
|
|
|
} |
|
56
|
|
|
|
|
57
|
|
|
private function getType($val) { |
|
58
|
|
|
if ($val instanceof \DateTime) return 'DATETIME'; |
|
59
|
|
View Code Duplication |
else if (is_int($val)) return 'INTEGER'; |
|
|
|
|
|
|
60
|
|
|
else if (is_double($val)) return 'DECIMAL(9,' . strlen($val) - strrpos($val, '.') - 1 . ')'; |
|
61
|
|
|
else if (is_string($val) && strlen($val) < 256) return 'VARCHAR(255)'; |
|
62
|
|
|
else if (is_string($val) && strlen($val) > 256) return 'LONGBLOG'; |
|
63
|
|
|
else return 'VARCHAR(255)'; |
|
64
|
|
|
} |
|
65
|
|
|
|
|
66
|
|
|
private function tableExists($name) { |
|
67
|
|
|
$result = $this->pdo->query('SELECT name FROM sqlite_master WHERE type="table" and name="'. $name.'"'); |
|
68
|
|
|
return count($result->fetchAll()) == 1; |
|
69
|
|
|
} |
|
70
|
|
|
|
|
71
|
|
|
private function getColumns($table) { |
|
72
|
|
|
$result = $this->pdo->query('PRAGMA table_info(' . $table . ');')->fetchAll(\PDO::FETCH_OBJ); |
|
73
|
|
|
$return = []; |
|
74
|
|
|
foreach ($result as $row) { |
|
75
|
|
|
$return[] = $row->name; |
|
76
|
|
|
} |
|
77
|
|
|
return $return; |
|
78
|
|
|
} |
|
79
|
|
|
|
|
80
|
|
|
//Alter the database so that it can store $data |
|
81
|
|
|
public function alterDatabase($table, array $primaryKey, $data) { |
|
82
|
|
|
//Unset query cache, otherwise it causes: |
|
83
|
|
|
// SQLSTATE[HY000]: General error: 17 database schema has changed |
|
84
|
|
|
$this->queryCache = []; |
|
85
|
|
|
|
|
86
|
|
|
$affix = '_'.substr(md5($table), 0, 6); |
|
87
|
|
|
$this->createTable($table . $affix, $primaryKey, $data); |
|
88
|
|
|
$fields = []; |
|
89
|
|
|
foreach ($data as $key => $value) { $fields[] = $key; } |
|
90
|
|
|
try { |
|
91
|
|
|
if ($this->tableExists($table)) { |
|
92
|
|
|
$columns = implode(', ', $this->getColumns($table)); |
|
93
|
|
|
|
|
94
|
|
|
$this->pdo->query('INSERT INTO ' . $this->quote($table . $affix) . '(' . $columns . ') SELECT ' . $columns . ' FROM ' . $this->quote($table)); |
|
95
|
|
|
$this->pdo->query('DROP TABLE IF EXISTS ' . $table ); |
|
96
|
|
|
} |
|
97
|
|
|
} |
|
98
|
|
|
catch (\PDOException $e) { |
|
99
|
|
|
// No data to copy |
|
100
|
|
|
echo $e->getMessage(); |
|
101
|
|
|
} |
|
102
|
|
|
|
|
103
|
|
|
$this->pdo->query('DROP TABLE IF EXISTS ' . $table ); |
|
104
|
|
|
$this->pdo->query('ALTER TABLE ' . $table . $affix. ' RENAME TO '. $table ); |
|
105
|
|
|
|
|
106
|
|
|
} |
|
107
|
|
|
|
|
108
|
|
|
public function createTable($table, array $primaryKey, $data) { |
|
109
|
|
|
$parts = []; |
|
110
|
|
View Code Duplication |
foreach ($primaryKey as $key) { |
|
|
|
|
|
|
111
|
|
|
$pk = $data->$key; |
|
112
|
|
|
if ($pk == null) $parts[] = $key . ' INTEGER'; |
|
113
|
|
|
else $parts[] = $key . ' ' . $this->getType($pk) . ' NOT NULL'; |
|
114
|
|
|
} |
|
115
|
|
|
|
|
116
|
|
|
$pkField = implode(', ', $parts) . ', PRIMARY KEY(' . implode(', ', $primaryKey) . ')'; |
|
117
|
|
|
|
|
118
|
|
|
$this->pdo->query('DROP TABLE IF EXISTS ' . $table ); |
|
119
|
|
|
$this->pdo->query('CREATE TABLE ' . $table . ' (' . $pkField . ')'); |
|
120
|
|
|
|
|
121
|
|
|
foreach ($data as $key => $value) { |
|
122
|
|
|
if (is_array($value) || (is_object($value) && !($value instanceof \DateTime))) continue; |
|
123
|
|
|
if (in_array($key, $primaryKey)) continue; |
|
124
|
|
|
|
|
125
|
|
|
$type = $this->getType($value); |
|
126
|
|
|
|
|
127
|
|
|
$this->pdo->query('ALTER TABLE ' . $table . ' ADD ' . $this->quote($key) . ' ' . $type); |
|
128
|
|
|
} |
|
129
|
|
|
} |
|
130
|
|
|
|
|
131
|
|
|
|
|
132
|
|
|
public function addIndex($table, array $fields) { |
|
133
|
|
|
if (empty($fields)) return false; |
|
134
|
|
|
|
|
135
|
|
|
//SQLite doesn't support ASC/DESC indexes, remove the keywords |
|
136
|
|
|
foreach ($fields as &$field) $field = str_ireplace([' desc', ' asc'], '', $field); |
|
137
|
|
|
sort($fields); |
|
138
|
|
|
$fields = array_map('strtolower', $fields); |
|
139
|
|
|
$fields = array_map('trim', $fields); |
|
140
|
|
|
$keyName = implode('_', $fields); |
|
141
|
|
|
|
|
142
|
|
|
|
|
143
|
|
|
try { |
|
144
|
|
|
$this->pdo->query('CREATE INDEX IF NOT EXISTS ' . $keyName . ' ON ' . $table . ' (' . implode(', ', $fields) . ')'); |
|
145
|
|
|
} |
|
146
|
|
|
catch (\Exception $e) { |
|
|
|
|
|
|
147
|
|
|
|
|
148
|
|
|
} |
|
149
|
|
|
} |
|
150
|
|
|
|
|
151
|
|
|
public function optimiseColumns($table) { |
|
152
|
|
|
//TODO |
|
153
|
|
|
} |
|
154
|
|
|
} |
Level-2 /
Maphper
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.