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:
19: class SqlsrvDriver extends Nette\Object implements Nette\Database\ISupplementalDriver
20: {
21:
22: private $connection;
23:
24:
25: public function __construct(Nette\Database\Connection $connection, array $options)
26: {
27: $this->connection = $connection;
28: }
29:
30:
31: public function convertException(\PDOException $e)
32: {
33: return Nette\Database\DriverException::from($e);
34: }
35:
36:
37:
38:
39:
40: 41: 42:
43: public function delimite($name)
44: {
45:
46: return '[' . str_replace(']', ']]', $name) . ']';
47: }
48:
49:
50: 51: 52:
53: public function formatBool($value)
54: {
55: return $value ? '1' : '0';
56: }
57:
58:
59: 60: 61:
62: public function formatDateTime( $value)
63: {
64:
65: return $value->format("'Y-m-d H:i:s'");
66: }
67:
68:
69: 70: 71:
72: public function formatDateInterval(\DateInterval $value)
73: {
74: throw new Nette\NotSupportedException;
75: }
76:
77:
78: 79: 80:
81: public function formatLike($value, $pos)
82: {
83:
84: $value = strtr($value, array("'" => "''", '%' => '[%]', '_' => '[_]', '[' => '[[]'));
85: return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
86: }
87:
88:
89: 90: 91:
92: public function applyLimit(& $sql, $limit, $offset)
93: {
94: if ($limit >= 0) {
95: $sql = preg_replace('#^\s*(SELECT|UPDATE|DELETE)#i', '$0 TOP ' . (int) $limit, $sql, 1, $count);
96: if (!$count) {
97: throw new Nette\InvalidArgumentException('SQL query must begin with SELECT, UPDATE or DELETE command.');
98: }
99: }
100:
101: if ($offset > 0) {
102: throw new Nette\NotSupportedException('Offset is not supported by this database.');
103: }
104: }
105:
106:
107: 108: 109:
110: public function normalizeRow($row)
111: {
112: return $row;
113: }
114:
115:
116:
117:
118:
119: 120: 121:
122: public function getTables()
123: {
124: $tables = array();
125: foreach ($this->connection->query("
126: SELECT
127: name,
128: CASE type
129: WHEN 'U' THEN 0
130: WHEN 'V' THEN 1
131: END AS [view]
132: FROM
133: sys.objects
134: WHERE
135: type IN ('U', 'V')
136: ") as $row) {
137: $tables[] = array(
138: 'name' => $row->name,
139: 'view' => (bool) $row->view,
140: );
141: }
142:
143: return $tables;
144: }
145:
146:
147: 148: 149:
150: public function getColumns($table)
151: {
152: $columns = array();
153: foreach ($this->connection->query("
154: SELECT
155: c.name AS name,
156: o.name AS [table],
157: UPPER(t.name) AS nativetype,
158: NULL AS size,
159: 0 AS unsigned,
160: c.is_nullable AS nullable,
161: OBJECT_DEFINITION(c.default_object_id) AS [default],
162: c.is_identity AS autoincrement,
163: CASE WHEN i.index_id IS NULL
164: THEN 0
165: ELSE 1
166: END AS [primary]
167: FROM
168: sys.columns c
169: JOIN sys.objects o ON c.object_id = o.object_id
170: LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
171: LEFT JOIN sys.key_constraints k ON o.object_id = k.parent_object_id AND k.type = 'PK'
172: LEFT JOIN sys.index_columns i ON k.parent_object_id = i.object_id AND i.index_id = k.unique_index_id AND i.column_id = c.column_id
173: WHERE
174: o.type IN ('U', 'V')
175: AND o.name = {$this->connection->quote($table)}
176: ") as $row) {
177: $row = (array) $row;
178: $row['vendor'] = $row;
179: $row['unsigned'] = (bool) $row['unsigned'];
180: $row['nullable'] = (bool) $row['nullable'];
181: $row['autoincrement'] = (bool) $row['autoincrement'];
182: $row['primary'] = (bool) $row['primary'];
183:
184: $columns[] = $row;
185: }
186:
187: return $columns;
188: }
189:
190:
191: 192: 193:
194: public function getIndexes($table)
195: {
196: $indexes = array();
197: foreach ($this->connection->query("
198: SELECT
199: i.name AS name,
200: CASE WHEN i.is_unique = 1 OR i.is_unique_constraint = 1
201: THEN 1
202: ELSE 0
203: END AS [unique],
204: i.is_primary_key AS [primary],
205: c.name AS [column]
206: FROM
207: sys.indexes i
208: JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
209: JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
210: JOIN sys.tables t ON i.object_id = t.object_id
211: WHERE
212: t.name = {$this->connection->quote($table)}
213: ORDER BY
214: i.index_id,
215: ic.index_column_id
216: ") as $row) {
217: $indexes[$row->name]['name'] = $row->name;
218: $indexes[$row->name]['unique'] = (bool) $row->unique;
219: $indexes[$row->name]['primary'] = (bool) $row->primary;
220: $indexes[$row->name]['columns'][] = $row->column;
221: }
222:
223: return array_values($indexes);
224: }
225:
226:
227: 228: 229:
230: public function getForeignKeys($table)
231: {
232:
233: $keys = array();
234: foreach ($this->connection->query("
235: SELECT
236: fk.name AS name,
237: cl.name AS local,
238: tf.name AS [table],
239: cf.name AS [column]
240: FROM
241: sys.foreign_keys fk
242: JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
243: JOIN sys.tables tl ON fkc.parent_object_id = tl.object_id
244: JOIN sys.columns cl ON fkc.parent_object_id = cl.object_id AND fkc.parent_column_id = cl.column_id
245: JOIN sys.tables tf ON fkc.referenced_object_id = tf.object_id
246: JOIN sys.columns cf ON fkc.referenced_object_id = cf.object_id AND fkc.referenced_column_id = cf.column_id
247: WHERE
248: tl.name = {$this->connection->quote($table)}
249: ") as $row) {
250: $keys[$row->name] = (array) $row;
251: }
252:
253: return array_values($keys);
254: }
255:
256:
257: 258: 259:
260: public function getColumnTypes(\PDOStatement $statement)
261: {
262: $types = array();
263: $count = $statement->columnCount();
264: for ($col = 0; $col < $count; $col++) {
265: $meta = $statement->getColumnMeta($col);
266: if (isset($meta['sqlsrv:decl_type']) && $meta['sqlsrv:decl_type'] !== 'timestamp') {
267: $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['sqlsrv:decl_type']);
268: } elseif (isset($meta['native_type'])) {
269: $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['native_type']);
270: }
271: }
272: return $types;
273: }
274:
275:
276: 277: 278: 279:
280: public function isSupported($item)
281: {
282: return $item === self::SUPPORT_SUBSELECT;
283: }
284:
285: }
286: