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