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