1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database\Drivers;
9:
10: use Nette;
11:
12:
13: 14: 15: 16: 17:
18: class SqliteDriver extends Nette\Object implements Nette\Database\ISupplementalDriver
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: $sql .= ' LIMIT ' . (int) $limit . ($offset > 0 ? ' OFFSET ' . (int) $offset : '');
119: }
120: }
121:
122:
123: 124: 125:
126: public function normalizeRow($row)
127: {
128: foreach ($row as $key => $value) {
129: unset($row[$key]);
130: if ($key[0] === '[' || $key[0] === '"') {
131: $key = substr($key, 1, -1);
132: }
133: $row[$key] = $value;
134: }
135: return $row;
136: }
137:
138:
139:
140:
141:
142: 143: 144:
145: public function getTables()
146: {
147: $tables = array();
148: foreach ($this->connection->query("
149: SELECT name, type = 'view' as view FROM sqlite_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
150: UNION ALL
151: SELECT name, type = 'view' as view FROM sqlite_temp_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
152: ORDER BY name
153: ") as $row) {
154: $tables[] = array(
155: 'name' => $row->name,
156: 'view' => (bool) $row->view,
157: );
158: }
159:
160: return $tables;
161: }
162:
163:
164: 165: 166:
167: public function getColumns($table)
168: {
169: $meta = $this->connection->query("
170: SELECT sql FROM sqlite_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
171: UNION ALL
172: SELECT sql FROM sqlite_temp_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
173: ")->fetch();
174:
175: $columns = array();
176: foreach ($this->connection->query("PRAGMA table_info({$this->delimite($table)})") as $row) {
177: $column = $row['name'];
178: $pattern = "/(\"$column\"|\[$column\]|$column)\\s+[^,]+\\s+PRIMARY\\s+KEY\\s+AUTOINCREMENT/Ui";
179: $type = explode('(', $row['type']);
180: $columns[] = array(
181: 'name' => $column,
182: 'table' => $table,
183: 'nativetype' => strtoupper($type[0]),
184: 'size' => isset($type[1]) ? (int) $type[1] : NULL,
185: 'unsigned' => FALSE,
186: 'nullable' => $row['notnull'] == '0',
187: 'default' => $row['dflt_value'],
188: 'autoincrement' => (bool) preg_match($pattern, $meta['sql']),
189: 'primary' => $row['pk'] > 0,
190: 'vendor' => (array) $row,
191: );
192: }
193: return $columns;
194: }
195:
196:
197: 198: 199:
200: public function getIndexes($table)
201: {
202: $indexes = array();
203: foreach ($this->connection->query("PRAGMA index_list({$this->delimite($table)})") as $row) {
204: $indexes[$row['name']]['name'] = $row['name'];
205: $indexes[$row['name']]['unique'] = (bool) $row['unique'];
206: $indexes[$row['name']]['primary'] = FALSE;
207: }
208:
209: foreach ($indexes as $index => $values) {
210: $res = $this->connection->query("PRAGMA index_info({$this->delimite($index)})");
211: while ($row = $res->fetch(TRUE)) {
212: $indexes[$index]['columns'][$row['seqno']] = $row['name'];
213: }
214: }
215:
216: $columns = $this->getColumns($table);
217: foreach ($indexes as $index => $values) {
218: $column = $indexes[$index]['columns'][0];
219: foreach ($columns as $info) {
220: if ($column == $info['name']) {
221: $indexes[$index]['primary'] = (bool) $info['primary'];
222: break;
223: }
224: }
225: }
226: if (!$indexes) {
227: foreach ($columns as $column) {
228: if ($column['vendor']['pk']) {
229: $indexes[] = array(
230: 'name' => 'ROWID',
231: 'unique' => TRUE,
232: 'primary' => TRUE,
233: 'columns' => array($column['name']),
234: );
235: break;
236: }
237: }
238: }
239:
240: return array_values($indexes);
241: }
242:
243:
244: 245: 246:
247: public function getForeignKeys($table)
248: {
249: $keys = array();
250: foreach ($this->connection->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
251: $keys[$row['id']]['name'] = $row['id'];
252: $keys[$row['id']]['local'] = $row['from'];
253: $keys[$row['id']]['table'] = $row['table'];
254: $keys[$row['id']]['foreign'] = $row['to'];
255: $keys[$row['id']]['onDelete'] = $row['on_delete'];
256: $keys[$row['id']]['onUpdate'] = $row['on_update'];
257:
258: if ($keys[$row['id']]['foreign'][0] == NULL) {
259: $keys[$row['id']]['foreign'] = NULL;
260: }
261: }
262: return array_values($keys);
263: }
264:
265:
266: 267: 268:
269: public function getColumnTypes(\PDOStatement $statement)
270: {
271: $types = array();
272: $count = $statement->columnCount();
273: for ($col = 0; $col < $count; $col++) {
274: $meta = $statement->getColumnMeta($col);
275: if (isset($meta['sqlite:decl_type'])) {
276: if ($meta['sqlite:decl_type'] === 'DATE') {
277: $types[$meta['name']] = Nette\Database\IStructure::FIELD_UNIX_TIMESTAMP;
278: } else {
279: $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['sqlite:decl_type']);
280: }
281: } elseif (isset($meta['native_type'])) {
282: $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['native_type']);
283: }
284: }
285: return $types;
286: }
287:
288:
289: 290: 291: 292:
293: public function isSupported($item)
294: {
295: return $item === self::SUPPORT_MULTI_INSERT_AS_SELECT || $item === self::SUPPORT_SUBSELECT || $item === self::SUPPORT_MULTI_COLUMN_AS_OR_COND;
296: }
297:
298: }
299: