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