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:
35:
36:
37:
38: 39: 40:
41: public function delimite($name)
42: {
43: return '[' . strtr($name, '[]', ' ') . ']';
44: }
45:
46:
47: 48: 49:
50: public function formatBool($value)
51: {
52: return $value ? '1' : '0';
53: }
54:
55:
56: 57: 58:
59: public function formatDateTime(\DateTime $value)
60: {
61: return $value->format($this->fmtDateTime);
62: }
63:
64:
65: 66: 67:
68: public function formatLike($value, $pos)
69: {
70: $value = addcslashes(substr($this->connection->quote($value), 1, -1), '%_\\');
71: return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'") . " ESCAPE '\\'";
72: }
73:
74:
75: 76: 77:
78: public function applyLimit(& $sql, $limit, $offset)
79: {
80: if ($limit >= 0 || $offset > 0) {
81: $sql .= ' LIMIT ' . $limit . ($offset > 0 ? ' OFFSET ' . (int) $offset : '');
82: }
83: }
84:
85:
86: 87: 88:
89: public function normalizeRow($row, $statement)
90: {
91: foreach ($row as $key => $value) {
92: unset($row[$key]);
93: if ($key[0] === '[' || $key[0] === '"') {
94: $key = substr($key, 1, -1);
95: }
96: $row[$key] = $value;
97: }
98: return $row;
99: }
100:
101:
102:
103:
104:
105: 106: 107:
108: public function getTables()
109: {
110: return $this->connection->query("
111: SELECT name, type = 'view' as view FROM sqlite_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
112: UNION ALL
113: SELECT name, type = 'view' as view FROM sqlite_temp_master WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
114: ORDER BY name
115: ")->fetchAll();
116: }
117:
118:
119: 120: 121:
122: public function getColumns($table)
123: {
124: $meta = $this->connection->query("
125: SELECT sql FROM sqlite_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
126: UNION ALL
127: SELECT sql FROM sqlite_temp_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
128: ")->fetch();
129:
130: $columns = array();
131: foreach ($this->connection->query("PRAGMA table_info({$this->delimite($table)})") as $row) {
132: $column = $row['name'];
133: $pattern = "/(\"$column\"|\[$column\]|$column)\\s+[^,]+\\s+PRIMARY\\s+KEY\\s+AUTOINCREMENT/Ui";
134: $type = explode('(', $row['type']);
135: $columns[] = array(
136: 'name' => $column,
137: 'table' => $table,
138: 'fullname' => "$table.$column",
139: 'nativetype' => strtoupper($type[0]),
140: 'size' => isset($type[1]) ? (int) $type[1] : NULL,
141: 'nullable' => $row['notnull'] == '0',
142: 'default' => $row['dflt_value'],
143: 'autoincrement' => (bool) preg_match($pattern, $meta['sql']),
144: 'primary' => $row['pk'] == '1',
145: 'vendor' => (array) $row,
146: );
147: }
148: return $columns;
149: }
150:
151:
152: 153: 154:
155: public function getIndexes($table)
156: {
157: $indexes = array();
158: foreach ($this->connection->query("PRAGMA index_list({$this->delimite($table)})") as $row) {
159: $indexes[$row['name']]['name'] = $row['name'];
160: $indexes[$row['name']]['unique'] = (bool) $row['unique'];
161: }
162:
163: foreach ($indexes as $index => $values) {
164: $res = $this->connection->query("PRAGMA index_info({$this->delimite($index)})");
165: while ($row = $res->fetch(TRUE)) {
166: $indexes[$index]['columns'][$row['seqno']] = $row['name'];
167: }
168: }
169:
170: $columns = $this->getColumns($table);
171: foreach ($indexes as $index => $values) {
172: $column = $indexes[$index]['columns'][0];
173: $primary = FALSE;
174: foreach ($columns as $info) {
175: if ($column == $info['name']) {
176: $primary = $info['primary'];
177: break;
178: }
179: }
180: $indexes[$index]['primary'] = (bool) $primary;
181: }
182: if (!$indexes) {
183: foreach ($columns as $column) {
184: if ($column['vendor']['pk']) {
185: $indexes[] = array(
186: 'name' => 'ROWID',
187: 'unique' => TRUE,
188: 'primary' => TRUE,
189: 'columns' => array($column['name']),
190: );
191: break;
192: }
193: }
194: }
195:
196: return array_values($indexes);
197: }
198:
199:
200: 201: 202:
203: public function getForeignKeys($table)
204: {
205: $keys = array();
206: foreach ($this->connection->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
207: $keys[$row['id']]['name'] = $row['id'];
208: $keys[$row['id']]['local'] = $row['from'];
209: $keys[$row['id']]['table'] = $row['table'];
210: $keys[$row['id']]['foreign'] = $row['to'];
211: $keys[$row['id']]['onDelete'] = $row['on_delete'];
212: $keys[$row['id']]['onUpdate'] = $row['on_update'];
213:
214: if ($keys[$row['id']]['foreign'][0] == NULL) {
215: $keys[$row['id']]['foreign'] = NULL;
216: }
217: }
218: return array_values($keys);
219: }
220:
221:
222: 223: 224:
225: public function isSupported($item)
226: {
227: return FALSE;
228: }
229:
230: }
231: