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