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