1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database\Drivers;
9:
10: use Nette;
11:
12:
13: 14: 15:
16: class SqliteDriver implements Nette\Database\ISupplementalDriver
17: {
18: use Nette\SmartObject;
19:
20:
21: private $connection;
22:
23:
24: private $fmtDateTime;
25:
26:
27: public function __construct(Nette\Database\Connection $connection, array $options)
28: {
29: $this->connection = $connection;
30: $this->fmtDateTime = isset($options['formatDateTime']) ? $options['formatDateTime'] : 'U';
31: }
32:
33:
34: public function convertException(\PDOException $e)
35: {
36: $code = isset($e->errorInfo[1]) ? $e->errorInfo[1] : null;
37: $msg = $e->getMessage();
38: if ($code !== 19) {
39: return Nette\Database\DriverException::from($e);
40:
41: } elseif (strpos($msg, 'must be unique') !== false
42: || strpos($msg, 'is not unique') !== false
43: || strpos($msg, 'UNIQUE constraint failed') !== false
44: ) {
45: return Nette\Database\UniqueConstraintViolationException::from($e);
46:
47: } elseif (strpos($msg, 'may not be null') !== false
48: || strpos($msg, 'NOT NULL constraint failed') !== false
49: ) {
50: return Nette\Database\NotNullConstraintViolationException::from($e);
51:
52: } elseif (strpos($msg, 'foreign key constraint failed') !== false
53: || strpos($msg, 'FOREIGN KEY constraint failed') !== false
54: ) {
55: return Nette\Database\ForeignKeyConstraintViolationException::from($e);
56:
57: } else {
58: return Nette\Database\ConstraintViolationException::from($e);
59: }
60: }
61:
62:
63:
64:
65:
66: 67: 68:
69: public function delimite($name)
70: {
71: return '[' . strtr($name, '[]', ' ') . ']';
72: }
73:
74:
75: 76: 77:
78: public function formatBool($value)
79: {
80: return $value ? '1' : '0';
81: }
82:
83:
84: 85: 86:
87: public function formatDateTime( $value)
88: {
89: return $value->format($this->fmtDateTime);
90: }
91:
92:
93: 94: 95:
96: public function formatDateInterval(\DateInterval $value)
97: {
98: throw new Nette\NotSupportedException;
99: }
100:
101:
102: 103: 104:
105: public function formatLike($value, $pos)
106: {
107: $value = addcslashes(substr($this->connection->quote($value), 1, -1), '%_\\');
108: return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'") . " ESCAPE '\\'";
109: }
110:
111:
112: 113: 114:
115: public function applyLimit(&$sql, $limit, $offset)
116: {
117: if ($limit < 0 || $offset < 0) {
118: throw new Nette\InvalidArgumentException('Negative offset or limit.');
119:
120: } elseif ($limit !== null || $offset) {
121: $sql .= ' LIMIT ' . ($limit === null ? '-1' : (int) $limit)
122: . ($offset ? ' OFFSET ' . (int) $offset : '');
123: }
124: }
125:
126:
127: 128: 129:
130: public function normalizeRow($row)
131: {
132: foreach ($row as $key => $value) {
133: unset($row[$key]);
134: if ($key[0] === '[' || $key[0] === '"') {
135: $key = substr($key, 1, -1);
136: }
137: $row[$key] = $value;
138: }
139: return $row;
140: }
141:
142:
143:
144:
145:
146: 147: 148:
149: public function getTables()
150: {
151: $tables = [];
152: foreach ($this->connection->query("
153: SELECT name, type = 'view' as view FROM sqlite_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
154: UNION ALL
155: SELECT name, type = 'view' as view FROM sqlite_temp_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
156: ORDER BY name
157: ") as $row) {
158: $tables[] = [
159: 'name' => $row->name,
160: 'view' => (bool) $row->view,
161: ];
162: }
163:
164: return $tables;
165: }
166:
167:
168: 169: 170:
171: public function getColumns($table)
172: {
173: $meta = $this->connection->query("
174: SELECT sql FROM sqlite_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
175: UNION ALL
176: SELECT sql FROM sqlite_temp_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
177: ")->fetch();
178:
179: $columns = [];
180: foreach ($this->connection->query("PRAGMA table_info({$this->delimite($table)})") as $row) {
181: $column = $row['name'];
182: $pattern = "/(\"$column\"|`$column`|\[$column\]|$column)\\s+[^,]+\\s+PRIMARY\\s+KEY\\s+AUTOINCREMENT/Ui";
183: $type = explode('(', $row['type']);
184: $columns[] = [
185: 'name' => $column,
186: 'table' => $table,
187: 'nativetype' => strtoupper($type[0]),
188: 'size' => isset($type[1]) ? (int) $type[1] : null,
189: 'unsigned' => false,
190: 'nullable' => $row['notnull'] == '0',
191: 'default' => $row['dflt_value'],
192: 'autoincrement' => (bool) preg_match($pattern, $meta['sql']),
193: 'primary' => $row['pk'] > 0,
194: 'vendor' => (array) $row,
195: ];
196: }
197: return $columns;
198: }
199:
200:
201: 202: 203:
204: public function getIndexes($table)
205: {
206: $indexes = [];
207: foreach ($this->connection->query("PRAGMA index_list({$this->delimite($table)})") as $row) {
208: $indexes[$row['name']]['name'] = $row['name'];
209: $indexes[$row['name']]['unique'] = (bool) $row['unique'];
210: $indexes[$row['name']]['primary'] = false;
211: }
212:
213: foreach ($indexes as $index => $values) {
214: $res = $this->connection->query("PRAGMA index_info({$this->delimite($index)})");
215: while ($row = $res->fetch()) {
216: $indexes[$index]['columns'][$row['seqno']] = $row['name'];
217: }
218: }
219:
220: $columns = $this->getColumns($table);
221: foreach ($indexes as $index => $values) {
222: $column = $indexes[$index]['columns'][0];
223: foreach ($columns as $info) {
224: if ($column == $info['name']) {
225: $indexes[$index]['primary'] = (bool) $info['primary'];
226: break;
227: }
228: }
229: }
230: if (!$indexes) {
231: foreach ($columns as $column) {
232: if ($column['vendor']['pk']) {
233: $indexes[] = [
234: 'name' => 'ROWID',
235: 'unique' => true,
236: 'primary' => true,
237: 'columns' => [$column['name']],
238: ];
239: break;
240: }
241: }
242: }
243:
244: return array_values($indexes);
245: }
246:
247:
248: 249: 250:
251: public function getForeignKeys($table)
252: {
253: $keys = [];
254: foreach ($this->connection->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
255: $keys[$row['id']]['name'] = $row['id'];
256: $keys[$row['id']]['local'] = $row['from'];
257: $keys[$row['id']]['table'] = $row['table'];
258: $keys[$row['id']]['foreign'] = $row['to'];
259: $keys[$row['id']]['onDelete'] = $row['on_delete'];
260: $keys[$row['id']]['onUpdate'] = $row['on_update'];
261:
262: if ($keys[$row['id']]['foreign'][0] == null) {
263: $keys[$row['id']]['foreign'] = null;
264: }
265: }
266: return array_values($keys);
267: }
268:
269:
270: 271: 272:
273: public function getColumnTypes(\PDOStatement $statement)
274: {
275: $types = [];
276: $count = $statement->columnCount();
277: for ($col = 0; $col < $count; $col++) {
278: $meta = $statement->getColumnMeta($col);
279: if (isset($meta['sqlite:decl_type'])) {
280: if (in_array($meta['sqlite:decl_type'], ['DATE', 'DATETIME'], true)) {
281: $types[$meta['name']] = Nette\Database\IStructure::FIELD_UNIX_TIMESTAMP;
282: } else {
283: $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['sqlite:decl_type']);
284: }
285: } elseif (isset($meta['native_type'])) {
286: $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['native_type']);
287: }
288: }
289: return $types;
290: }
291:
292:
293: 294: 295: 296:
297: public function isSupported($item)
298: {
299: return $item === self::SUPPORT_MULTI_INSERT_AS_SELECT || $item === self::SUPPORT_SUBSELECT || $item === self::SUPPORT_MULTI_COLUMN_AS_OR_COND;
300: }
301: }
302: