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