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:  * @author     David Grudl
 17:  * @author     Miloslav Hůla
 18:  */
 19: class SqlsrvDriver extends Nette\Object implements Nette\Database\ISupplementalDriver
 20: {
 21:     /** @var Nette\Database\Connection */
 22:     private $connection;
 23: 
 24: 
 25:     public function __construct(Nette\Database\Connection $connection, array $options)
 26:     {
 27:         $this->connection = $connection;
 28:     }
 29: 
 30: 
 31:     /********************* SQL ****************d*g**/
 32: 
 33: 
 34:     /**
 35:      * Delimites identifier for use in a SQL statement.
 36:      */
 37:     public function delimite($name)
 38:     {
 39:         /** @see https://msdn.microsoft.com/en-us/library/ms176027.aspx */
 40:         return '[' . str_replace(']', ']]', $name) . ']';
 41:     }
 42: 
 43: 
 44:     /**
 45:      * Formats boolean for use in a SQL statement.
 46:      */
 47:     public function formatBool($value)
 48:     {
 49:         return $value ? '1' : '0';
 50:     }
 51: 
 52: 
 53:     /**
 54:      * Formats date-time for use in a SQL statement.
 55:      */
 56:     public function formatDateTime(/*\DateTimeInterface*/ $value)
 57:     {
 58:         /** @see https://msdn.microsoft.com/en-us/library/ms187819.aspx */
 59:         return $value->format("'Y-m-d H:i:s'");
 60:     }
 61: 
 62: 
 63:     /**
 64:      * Encodes string for use in a LIKE statement.
 65:      */
 66:     public function formatLike($value, $pos)
 67:     {
 68:         /** @see https://msdn.microsoft.com/en-us/library/ms179859.aspx */
 69:         $value = strtr($value, array("'" => "''", '%' => '[%]', '_' => '[_]', '[' => '[[]'));
 70:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
 71:     }
 72: 
 73: 
 74:     /**
 75:      * Injects LIMIT/OFFSET to the SQL query.
 76:      */
 77:     public function applyLimit(& $sql, $limit, $offset)
 78:     {
 79:         if ($limit >= 0) {
 80:             $sql = preg_replace('#^\s*(SELECT|UPDATE|DELETE)#i', '$0 TOP ' . (int) $limit, $sql, 1, $count);
 81:             if (!$count) {
 82:                 throw new Nette\InvalidArgumentException('SQL query must begin with SELECT, UPDATE or DELETE command.');
 83:             }
 84:         }
 85: 
 86:         if ($offset > 0) {
 87:             throw new Nette\NotSupportedException('Offset is not supported by this database.');
 88:         }
 89:     }
 90: 
 91: 
 92:     /**
 93:      * Normalizes result row.
 94:      */
 95:     public function normalizeRow($row)
 96:     {
 97:         return $row;
 98:     }
 99: 
100: 
101:     /********************* reflection ****************d*g**/
102: 
103: 
104:     /**
105:      * Returns list of tables.
106:      */
107:     public function getTables()
108:     {
109:         $tables = array();
110:         foreach ($this->connection->query("
111:             SELECT
112:                 name,
113:                 CASE type
114:                     WHEN 'U' THEN 0
115:                     WHEN 'V' THEN 1
116:                 END AS [view]
117:             FROM
118:                 sys.objects
119:             WHERE
120:                 type IN ('U', 'V')
121:         ") as $row) {
122:             $tables[] = array(
123:                 'name' => $row->name,
124:                 'view' => (bool) $row->view,
125:             );
126:         }
127: 
128:         return $tables;
129:     }
130: 
131: 
132:     /**
133:      * Returns metadata for all columns in a table.
134:      */
135:     public function getColumns($table)
136:     {
137:         $columns = array();
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:     /**
177:      * Returns metadata for all indexes in a table.
178:      */
179:     public function getIndexes($table)
180:     {
181:         $indexes = array();
182:         foreach ($this->connection->query("
183:             SELECT
184:                 i.name AS name,
185:                 CASE WHEN i.is_unique = 1 OR i.is_unique_constraint = 1
186:                     THEN 1
187:                     ELSE 0
188:                 END AS [unique],
189:                 i.is_primary_key AS [primary],
190:                 c.name AS [column]
191:             FROM
192:                 sys.indexes i
193:                 JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
194:                 JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
195:                 JOIN sys.tables t ON i.object_id = t.object_id
196:             WHERE
197:                 t.name = {$this->connection->quote($table)}
198:             ORDER BY
199:                 i.index_id,
200:                 ic.index_column_id
201:         ") as $row) {
202:             $indexes[$row->name]['name'] = $row->name;
203:             $indexes[$row->name]['unique'] = (bool) $row->unique;
204:             $indexes[$row->name]['primary'] = (bool) $row->primary;
205:             $indexes[$row->name]['columns'][] = $row->column;
206:         }
207: 
208:         return array_values($indexes);
209:     }
210: 
211: 
212:     /**
213:      * Returns metadata for all foreign keys in a table.
214:      */
215:     public function getForeignKeys($table)
216:     {
217:         // Does't work with multicolumn foreign keys
218:         $keys = array();
219:         foreach ($this->connection->query("
220:             SELECT
221:                 fk.name AS name,
222:                 cl.name AS local,
223:                 tf.name AS [table],
224:                 cf.name AS [column]
225:             FROM
226:                 sys.foreign_keys fk
227:                 JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
228:                 JOIN sys.tables tl ON fkc.parent_object_id = tl.object_id
229:                 JOIN sys.columns cl ON fkc.parent_object_id = cl.object_id AND fkc.parent_column_id = cl.column_id
230:                 JOIN sys.tables tf ON fkc.referenced_object_id = tf.object_id
231:                 JOIN sys.columns cf ON fkc.referenced_object_id = cf.object_id AND fkc.referenced_column_id = cf.column_id
232:             WHERE
233:                 tl.name = {$this->connection->quote($table)}
234:         ") as $row) {
235:             $keys[$row->name] = (array) $row;
236:         }
237: 
238:         return array_values($keys);
239:     }
240: 
241: 
242:     /**
243:      * Returns associative array of detected types (IReflection::FIELD_*) in result set.
244:      */
245:     public function getColumnTypes(\PDOStatement $statement)
246:     {
247:         $types = array();
248:         $count = $statement->columnCount();
249:         for ($col = 0; $col < $count; $col++) {
250:             $meta = $statement->getColumnMeta($col);
251:             if (isset($meta['sqlsrv:decl_type']) && $meta['sqlsrv:decl_type'] !== 'timestamp') { // timestamp does not mean time in sqlsrv
252:                 $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['sqlsrv:decl_type']);
253:             } elseif (isset($meta['native_type'])) {
254:                 $types[$meta['name']] = Nette\Database\Helpers::detectType($meta['native_type']);
255:             }
256:         }
257:         return $types;
258:     }
259: 
260: 
261:     /**
262:      * @return bool
263:      */
264:     public function isSupported($item)
265:     {
266:         return $item === self::SUPPORT_SUBSELECT;
267:     }
268: 
269: }
270: