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