1: <?php
  2: /**
  3:  * MS SQL Server layer for DBO
  4:  *
  5:  * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
  6:  * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
  7:  *
  8:  * Licensed under The MIT License
  9:  * For full copyright and license information, please see the LICENSE.txt
 10:  * Redistributions of files must retain the above copyright notice.
 11:  *
 12:  * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 13:  * @link          https://cakephp.org CakePHP(tm) Project
 14:  * @package       Cake.Model.Datasource.Database
 15:  * @since         CakePHP(tm) v 0.10.5.1790
 16:  * @license       https://opensource.org/licenses/mit-license.php MIT License
 17:  */
 18: 
 19: App::uses('DboSource', 'Model/Datasource');
 20: 
 21: /**
 22:  * Dbo layer for Microsoft's official SQLServer driver
 23:  *
 24:  * A Dbo layer for MS SQL Server 2005 and higher. Requires the
 25:  * `pdo_sqlsrv` extension to be enabled.
 26:  *
 27:  * @link http://www.php.net/manual/en/ref.pdo-sqlsrv.php
 28:  *
 29:  * @package       Cake.Model.Datasource.Database
 30:  */
 31: class Sqlserver extends DboSource {
 32: 
 33: /**
 34:  * Driver description
 35:  *
 36:  * @var string
 37:  */
 38:     public $description = "SQL Server DBO Driver";
 39: 
 40: /**
 41:  * Starting quote character for quoted identifiers
 42:  *
 43:  * @var string
 44:  */
 45:     public $startQuote = "[";
 46: 
 47: /**
 48:  * Ending quote character for quoted identifiers
 49:  *
 50:  * @var string
 51:  */
 52:     public $endQuote = "]";
 53: 
 54: /**
 55:  * Creates a map between field aliases and numeric indexes. Workaround for the
 56:  * SQL Server driver's 30-character column name limitation.
 57:  *
 58:  * @var array
 59:  */
 60:     protected $_fieldMappings = array();
 61: 
 62: /**
 63:  * Storing the last affected value
 64:  *
 65:  * @var mixed
 66:  */
 67:     protected $_lastAffected = false;
 68: 
 69: /**
 70:  * Base configuration settings for MS SQL driver
 71:  *
 72:  * @var array
 73:  */
 74:     protected $_baseConfig = array(
 75:         'host' => 'localhost\SQLEXPRESS',
 76:         'login' => '',
 77:         'password' => '',
 78:         'database' => 'cake',
 79:         'schema' => '',
 80:         'flags' => array()
 81:     );
 82: 
 83: /**
 84:  * MS SQL column definition
 85:  *
 86:  * @var array
 87:  * @link https://msdn.microsoft.com/en-us/library/ms187752.aspx SQL Server Data Types
 88:  */
 89:     public $columns = array(
 90:         'primary_key' => array('name' => 'IDENTITY (1, 1) NOT NULL'),
 91:         'string' => array('name' => 'nvarchar', 'limit' => '255'),
 92:         'text' => array('name' => 'nvarchar', 'limit' => 'MAX'),
 93:         'integer' => array('name' => 'int', 'formatter' => 'intval'),
 94:         'smallinteger' => array('name' => 'smallint', 'formatter' => 'intval'),
 95:         'tinyinteger' => array('name' => 'tinyint', 'formatter' => 'intval'),
 96:         'biginteger' => array('name' => 'bigint'),
 97:         'numeric' => array('name' => 'decimal', 'formatter' => 'floatval'),
 98:         'decimal' => array('name' => 'decimal', 'formatter' => 'floatval'),
 99:         'float' => array('name' => 'float', 'formatter' => 'floatval'),
100:         'real' => array('name' => 'float', 'formatter' => 'floatval'),
101:         'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
102:         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
103:         'time' => array('name' => 'datetime', 'format' => 'H:i:s', 'formatter' => 'date'),
104:         'date' => array('name' => 'datetime', 'format' => 'Y-m-d', 'formatter' => 'date'),
105:         'binary' => array('name' => 'varbinary'),
106:         'boolean' => array('name' => 'bit')
107:     );
108: 
109: /**
110:  * Magic column name used to provide pagination support for SQLServer 2008
111:  * which lacks proper limit/offset support.
112:  *
113:  * @var string
114:  */
115:     const ROW_COUNTER = '_cake_page_rownum_';
116: 
117: /**
118:  * Connects to the database using options in the given configuration array.
119:  *
120:  * Please note that the PDO::ATTR_PERSISTENT attribute is not supported by
121:  * the SQL Server PHP PDO drivers.  As a result you cannot use the
122:  * persistent config option when connecting to a SQL Server  (for more
123:  * information see: https://github.com/Microsoft/msphpsql/issues/65).
124:  *
125:  * @return bool True if the database could be connected, else false
126:  * @throws InvalidArgumentException if an unsupported setting is in the database config
127:  * @throws MissingConnectionException
128:  */
129:     public function connect() {
130:         $config = $this->config;
131:         $this->connected = false;
132: 
133:         if (isset($config['persistent']) && $config['persistent']) {
134:             throw new InvalidArgumentException('Config setting "persistent" cannot be set to true, as the Sqlserver PDO driver does not support PDO::ATTR_PERSISTENT');
135:         }
136: 
137:         $flags = $config['flags'] + array(
138:             PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
139:         );
140: 
141:         if (!empty($config['encoding'])) {
142:             $flags[PDO::SQLSRV_ATTR_ENCODING] = $config['encoding'];
143:         }
144: 
145:         try {
146:             $this->_connection = new PDO(
147:                 "sqlsrv:server={$config['host']};Database={$config['database']}",
148:                 $config['login'],
149:                 $config['password'],
150:                 $flags
151:             );
152:             $this->connected = true;
153:             if (!empty($config['settings'])) {
154:                 foreach ($config['settings'] as $key => $value) {
155:                     $this->_execute("SET $key $value");
156:                 }
157:             }
158:         } catch (PDOException $e) {
159:             throw new MissingConnectionException(array(
160:                 'class' => get_class($this),
161:                 'message' => $e->getMessage()
162:             ));
163:         }
164: 
165:         return $this->connected;
166:     }
167: 
168: /**
169:  * Check that PDO SQL Server is installed/loaded
170:  *
171:  * @return bool
172:  */
173:     public function enabled() {
174:         return in_array('sqlsrv', PDO::getAvailableDrivers());
175:     }
176: 
177: /**
178:  * Returns an array of sources (tables) in the database.
179:  *
180:  * @param mixed $data The names
181:  * @return array Array of table names in the database
182:  */
183:     public function listSources($data = null) {
184:         $cache = parent::listSources();
185:         if ($cache !== null) {
186:             return $cache;
187:         }
188:         $result = $this->_execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
189: 
190:         if (!$result) {
191:             $result->closeCursor();
192:             return array();
193:         }
194:         $tables = array();
195: 
196:         while ($line = $result->fetch(PDO::FETCH_NUM)) {
197:             $tables[] = $line[0];
198:         }
199: 
200:         $result->closeCursor();
201:         parent::listSources($tables);
202:         return $tables;
203:     }
204: 
205: /**
206:  * Returns an array of the fields in given table name.
207:  *
208:  * @param Model|string $model Model object to describe, or a string table name.
209:  * @return array Fields in table. Keys are name and type
210:  * @throws CakeException
211:  */
212:     public function describe($model) {
213:         $table = $this->fullTableName($model, false, false);
214:         $fulltable = $this->fullTableName($model, false, true);
215: 
216:         $cache = parent::describe($fulltable);
217:         if ($cache) {
218:             return $cache;
219:         }
220: 
221:         $fields = array();
222:         $schema = is_object($model) ? $model->schemaName : false;
223: 
224:         $cols = $this->_execute(
225:             "SELECT
226:                 COLUMN_NAME as Field,
227:                 DATA_TYPE as Type,
228:                 COL_LENGTH('" . ($schema ? $fulltable : $table) . "', COLUMN_NAME) as Length,
229:                 IS_NULLABLE As [Null],
230:                 COLUMN_DEFAULT as [Default],
231:                 COLUMNPROPERTY(OBJECT_ID('" . ($schema ? $fulltable : $table) . "'), COLUMN_NAME, 'IsIdentity') as [Key],
232:                 NUMERIC_SCALE as Size
233:             FROM INFORMATION_SCHEMA.COLUMNS
234:             WHERE TABLE_NAME = '" . $table . "'" . ($schema ? " AND TABLE_SCHEMA = '" . $schema . "'" : '')
235:         );
236: 
237:         if (!$cols) {
238:             throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table));
239:         }
240: 
241:         while ($column = $cols->fetch(PDO::FETCH_OBJ)) {
242:             $field = $column->Field;
243:             $fields[$field] = array(
244:                 'type' => $this->column($column),
245:                 'null' => ($column->Null === 'YES' ? true : false),
246:                 'default' => $column->Default,
247:                 'length' => $this->length($column),
248:                 'key' => ($column->Key == '1') ? 'primary' : false
249:             );
250: 
251:             if ($fields[$field]['default'] === 'null') {
252:                 $fields[$field]['default'] = null;
253:             }
254:             if ($fields[$field]['default'] !== null) {
255:                 $fields[$field]['default'] = preg_replace(
256:                     "/^[(]{1,2}'?([^')]*)?'?[)]{1,2}$/",
257:                     "$1",
258:                     $fields[$field]['default']
259:                 );
260:                 $this->value($fields[$field]['default'], $fields[$field]['type']);
261:             }
262: 
263:             if ($fields[$field]['key'] !== false && $fields[$field]['type'] === 'integer') {
264:                 $fields[$field]['length'] = 11;
265:             } elseif ($fields[$field]['key'] === false) {
266:                 unset($fields[$field]['key']);
267:             }
268:             if (in_array($fields[$field]['type'], array('date', 'time', 'datetime', 'timestamp'))) {
269:                 $fields[$field]['length'] = null;
270:             }
271:             if ($fields[$field]['type'] === 'float' && !empty($column->Size)) {
272:                 $fields[$field]['length'] = $fields[$field]['length'] . ',' . $column->Size;
273:             }
274:         }
275:         $this->_cacheDescription($table, $fields);
276:         $cols->closeCursor();
277:         return $fields;
278:     }
279: 
280: /**
281:  * Generates the fields list of an SQL query.
282:  *
283:  * @param Model $model The model to get fields for.
284:  * @param string $alias Alias table name
285:  * @param array $fields The fields so far.
286:  * @param bool $quote Whether or not to quote identfiers.
287:  * @return array
288:  */
289:     public function fields(Model $model, $alias = null, $fields = array(), $quote = true) {
290:         if (empty($alias)) {
291:             $alias = $model->alias;
292:         }
293:         $fields = parent::fields($model, $alias, $fields, false);
294:         $count = count($fields);
295: 
296:         if ($count >= 1 && strpos($fields[0], 'COUNT(*)') === false) {
297:             $result = array();
298:             for ($i = 0; $i < $count; $i++) {
299:                 $prepend = '';
300: 
301:                 if (strpos($fields[$i], 'DISTINCT') !== false && strpos($fields[$i], 'COUNT') === false) {
302:                     $prepend = 'DISTINCT ';
303:                     $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i]));
304:                 }
305:                 if (strpos($fields[$i], 'COUNT(DISTINCT') !== false) {
306:                     $prepend = 'COUNT(DISTINCT ';
307:                     $fields[$i] = trim(str_replace('COUNT(DISTINCT', '', $this->_quoteFields($fields[$i])));
308:                 }
309: 
310:                 if (!preg_match('/\s+AS\s+/i', $fields[$i])) {
311:                     if (substr($fields[$i], -1) === '*') {
312:                         if (strpos($fields[$i], '.') !== false && $fields[$i] != $alias . '.*') {
313:                             $build = explode('.', $fields[$i]);
314:                             $AssociatedModel = $model->{$build[0]};
315:                         } else {
316:                             $AssociatedModel = $model;
317:                         }
318: 
319:                         $_fields = $this->fields($AssociatedModel, $AssociatedModel->alias, array_keys($AssociatedModel->schema()));
320:                         $result = array_merge($result, $_fields);
321:                         continue;
322:                     }
323: 
324:                     if (strpos($fields[$i], '.') === false) {
325:                         $this->_fieldMappings[$alias . '__' . $fields[$i]] = $alias . '.' . $fields[$i];
326:                         $fieldName = $this->name($alias . '.' . $fields[$i]);
327:                         $fieldAlias = $this->name($alias . '__' . $fields[$i]);
328:                     } else {
329:                         $build = explode('.', $fields[$i]);
330:                         $build[0] = trim($build[0], '[]');
331:                         $build[1] = trim($build[1], '[]');
332:                         $name = $build[0] . '.' . $build[1];
333:                         $alias = $build[0] . '__' . $build[1];
334: 
335:                         $this->_fieldMappings[$alias] = $name;
336:                         $fieldName = $this->name($name);
337:                         $fieldAlias = $this->name($alias);
338:                     }
339:                     if ($model->getColumnType($fields[$i]) === 'datetime') {
340:                         $fieldName = "CONVERT(VARCHAR(20), {$fieldName}, 20)";
341:                     }
342:                     $fields[$i] = "{$fieldName} AS {$fieldAlias}";
343:                 }
344:                 $result[] = $prepend . $fields[$i];
345:             }
346:             return $result;
347:         }
348:         return $fields;
349:     }
350: 
351: /**
352:  * Generates and executes an SQL INSERT statement for given model, fields, and values.
353:  * Removes Identity (primary key) column from update data before returning to parent, if
354:  * value is empty.
355:  *
356:  * @param Model $model The model to insert into.
357:  * @param array $fields The fields to set.
358:  * @param array $values The values to set.
359:  * @return array
360:  */
361:     public function create(Model $model, $fields = null, $values = null) {
362:         if (!empty($values)) {
363:             $fields = array_combine($fields, $values);
364:         }
365:         $primaryKey = $this->_getPrimaryKey($model);
366: 
367:         if (array_key_exists($primaryKey, $fields)) {
368:             if (empty($fields[$primaryKey])) {
369:                 unset($fields[$primaryKey]);
370:             } else {
371:                 $this->_execute('SET IDENTITY_INSERT ' . $this->fullTableName($model) . ' ON');
372:             }
373:         }
374:         $result = parent::create($model, array_keys($fields), array_values($fields));
375:         if (array_key_exists($primaryKey, $fields) && !empty($fields[$primaryKey])) {
376:             $this->_execute('SET IDENTITY_INSERT ' . $this->fullTableName($model) . ' OFF');
377:         }
378:         return $result;
379:     }
380: 
381: /**
382:  * Generates and executes an SQL UPDATE statement for given model, fields, and values.
383:  * Removes Identity (primary key) column from update data before returning to parent.
384:  *
385:  * @param Model $model The model to update.
386:  * @param array $fields The fields to set.
387:  * @param array $values The values to set.
388:  * @param mixed $conditions The conditions to use.
389:  * @return array
390:  */
391:     public function update(Model $model, $fields = array(), $values = null, $conditions = null) {
392:         if (!empty($values)) {
393:             $fields = array_combine($fields, $values);
394:         }
395:         if (isset($fields[$model->primaryKey])) {
396:             unset($fields[$model->primaryKey]);
397:         }
398:         if (empty($fields)) {
399:             return true;
400:         }
401:         return parent::update($model, array_keys($fields), array_values($fields), $conditions);
402:     }
403: 
404: /**
405:  * Returns a limit statement in the correct format for the particular database.
406:  *
407:  * @param int $limit Limit of results returned
408:  * @param int $offset Offset from which to start results
409:  * @return string SQL limit/offset statement
410:  */
411:     public function limit($limit, $offset = null) {
412:         if ($limit) {
413:             $rt = '';
414:             if (!strpos(strtolower($limit), 'top') || strpos(strtolower($limit), 'top') === 0) {
415:                 $rt = ' TOP';
416:             }
417:             $rt .= sprintf(' %u', $limit);
418:             if ((is_int($offset) || ctype_digit($offset)) && $offset > 0) {
419:                 $rt = sprintf(' OFFSET %u ROWS FETCH FIRST %u ROWS ONLY', $offset, $limit);
420:             }
421:             return $rt;
422:         }
423:         return null;
424:     }
425: 
426: /**
427:  * Converts database-layer column types to basic types
428:  *
429:  * @param mixed $real Either the string value of the fields type.
430:  *    or the Result object from Sqlserver::describe()
431:  * @return string Abstract column type (i.e. "string")
432:  */
433:     public function column($real) {
434:         $limit = null;
435:         $col = $real;
436:         if (is_object($real) && isset($real->Field)) {
437:             $limit = $real->Length;
438:             $col = $real->Type;
439:         }
440: 
441:         if ($col === 'datetime2') {
442:             return 'datetime';
443:         }
444:         if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
445:             return $col;
446:         }
447:         if ($col === 'bit') {
448:             return 'boolean';
449:         }
450:         if (strpos($col, 'bigint') !== false) {
451:             return 'biginteger';
452:         }
453:         if (strpos($col, 'smallint') !== false) {
454:             return 'smallinteger';
455:         }
456:         if (strpos($col, 'tinyint') !== false) {
457:             return 'tinyinteger';
458:         }
459:         if (strpos($col, 'int') !== false) {
460:             return 'integer';
461:         }
462:         if (strpos($col, 'char') !== false && $limit == -1) {
463:             return 'text';
464:         }
465:         if (strpos($col, 'char') !== false) {
466:             return 'string';
467:         }
468:         if (strpos($col, 'text') !== false) {
469:             return 'text';
470:         }
471:         if (strpos($col, 'binary') !== false || $col === 'image') {
472:             return 'binary';
473:         }
474:         if (in_array($col, array('float', 'real'))) {
475:             return 'float';
476:         }
477:         if (in_array($col, array('decimal', 'numeric'))) {
478:             return 'decimal';
479:         }
480:         return 'text';
481:     }
482: 
483: /**
484:  * Handle SQLServer specific length properties.
485:  * SQLServer handles text types as nvarchar/varchar with a length of -1.
486:  *
487:  * @param mixed $length Either the length as a string, or a Column descriptor object.
488:  * @return mixed null|integer with length of column.
489:  */
490:     public function length($length) {
491:         if (is_object($length) && isset($length->Length)) {
492:             if ($length->Length == -1 && strpos($length->Type, 'char') !== false) {
493:                 return null;
494:             }
495:             if (in_array($length->Type, array('nchar', 'nvarchar'))) {
496:                 return floor($length->Length / 2);
497:             }
498:             if ($length->Type === 'text') {
499:                 return null;
500:             }
501:             return $length->Length;
502:         }
503:         return parent::length($length);
504:     }
505: 
506: /**
507:  * Builds a map of the columns contained in a result
508:  *
509:  * @param PDOStatement $results The result to modify.
510:  * @return void
511:  */
512:     public function resultSet($results) {
513:         $this->map = array();
514:         $numFields = $results->columnCount();
515:         $index = 0;
516: 
517:         while ($numFields-- > 0) {
518:             $column = $results->getColumnMeta($index);
519:             $name = $column['name'];
520: 
521:             if (strpos($name, '__')) {
522:                 if (isset($this->_fieldMappings[$name]) && strpos($this->_fieldMappings[$name], '.')) {
523:                     $map = explode('.', $this->_fieldMappings[$name]);
524:                 } elseif (isset($this->_fieldMappings[$name])) {
525:                     $map = array(0, $this->_fieldMappings[$name]);
526:                 } else {
527:                     $map = array(0, $name);
528:                 }
529:             } else {
530:                 $map = array(0, $name);
531:             }
532:             $map[] = ($column['sqlsrv:decl_type'] === 'bit') ? 'boolean' : $column['native_type'];
533:             $this->map[$index++] = $map;
534:         }
535:     }
536: 
537: /**
538:  * Builds final SQL statement
539:  *
540:  * @param string $type Query type
541:  * @param array $data Query data
542:  * @return string
543:  */
544:     public function renderStatement($type, $data) {
545:         switch (strtolower($type)) {
546:             case 'select':
547:                 extract($data);
548:                 $fields = trim($fields);
549: 
550:                 $having = !empty($having) ? " $having" : '';
551:                 $lock = !empty($lock) ? " $lock" : '';
552: 
553:                 if (strpos($limit, 'TOP') !== false && strpos($fields, 'DISTINCT ') === 0) {
554:                     $limit = 'DISTINCT ' . trim($limit);
555:                     $fields = substr($fields, 9);
556:                 }
557: 
558:                 // hack order as SQLServer requires an order if there is a limit.
559:                 if ($limit && !$order) {
560:                     $order = 'ORDER BY (SELECT NULL)';
561:                 }
562: 
563:                 // For older versions use the subquery version of pagination.
564:                 if (version_compare($this->getVersion(), '11', '<') && preg_match('/FETCH\sFIRST\s+([0-9]+)/i', $limit, $offset)) {
565:                     preg_match('/OFFSET\s*(\d+)\s*.*?(\d+)\s*ROWS/', $limit, $limitOffset);
566: 
567:                     $limit = 'TOP ' . (int)$limitOffset[2];
568:                     $page = (int)($limitOffset[1] / $limitOffset[2]);
569:                     $offset = (int)($limitOffset[2] * $page);
570: 
571:                     $rowCounter = static::ROW_COUNTER;
572:                     $sql = "SELECT {$limit} * FROM (
573:                             SELECT {$fields}, ROW_NUMBER() OVER ({$order}) AS {$rowCounter}
574:                             FROM {$table} {$alias}{$lock} {$joins} {$conditions} {$group}{$having}
575:                         ) AS _cake_paging_
576:                         WHERE _cake_paging_.{$rowCounter} > {$offset}
577:                         ORDER BY _cake_paging_.{$rowCounter}
578:                     ";
579:                     return trim($sql);
580:                 }
581:                 if (strpos($limit, 'FETCH') !== false) {
582:                     return trim("SELECT {$fields} FROM {$table} {$alias}{$lock} {$joins} {$conditions} {$group}{$having} {$order} {$limit}");
583:                 }
584:                 return trim("SELECT {$limit} {$fields} FROM {$table} {$alias}{$lock} {$joins} {$conditions} {$group}{$having} {$order}");
585:             case "schema":
586:                 extract($data);
587: 
588:                 foreach ($indexes as $i => $index) {
589:                     if (preg_match('/PRIMARY KEY/', $index)) {
590:                         unset($indexes[$i]);
591:                         break;
592:                     }
593:                 }
594: 
595:                 foreach (array('columns', 'indexes') as $var) {
596:                     if (is_array(${$var})) {
597:                         ${$var} = "\t" . implode(",\n\t", array_filter(${$var}));
598:                     }
599:                 }
600:                 return trim("CREATE TABLE {$table} (\n{$columns});\n{$indexes}");
601:             default:
602:                 return parent::renderStatement($type, $data);
603:         }
604:     }
605: 
606: /**
607:  * Returns a quoted and escaped string of $data for use in an SQL statement.
608:  *
609:  * @param string $data String to be prepared for use in an SQL statement
610:  * @param string $column The column into which this data will be inserted
611:  * @param bool $null Column allows NULL values
612:  * @return string Quoted and escaped data
613:  */
614:     public function value($data, $column = null, $null = true) {
615:         if ($data === null || is_array($data) || is_object($data)) {
616:             return parent::value($data, $column, $null);
617:         }
618:         if (in_array($data, array('{$__cakeID__$}', '{$__cakeForeignKey__$}'), true)) {
619:             return $data;
620:         }
621: 
622:         if (empty($column)) {
623:             $column = $this->introspectType($data);
624:         }
625: 
626:         switch ($column) {
627:             case 'string':
628:             case 'text':
629:                 return 'N' . $this->_connection->quote($data, PDO::PARAM_STR);
630:             default:
631:                 return parent::value($data, $column, $null);
632:         }
633:     }
634: 
635: /**
636:  * Returns an array of all result rows for a given SQL query.
637:  * Returns false if no rows matched.
638:  *
639:  * @param Model $model The model to read from
640:  * @param array $queryData The query data
641:  * @param int $recursive How many layers to go.
642:  * @return array|false Array of resultset rows, or false if no rows matched
643:  */
644:     public function read(Model $model, $queryData = array(), $recursive = null) {
645:         $results = parent::read($model, $queryData, $recursive);
646:         $this->_fieldMappings = array();
647:         return $results;
648:     }
649: 
650: /**
651:  * Fetches the next row from the current result set.
652:  * Eats the magic ROW_COUNTER variable.
653:  *
654:  * @return mixed
655:  */
656:     public function fetchResult() {
657:         if ($row = $this->_result->fetch(PDO::FETCH_NUM)) {
658:             $resultRow = array();
659:             foreach ($this->map as $col => $meta) {
660:                 list($table, $column, $type) = $meta;
661:                 if ($table === 0 && $column === static::ROW_COUNTER) {
662:                     continue;
663:                 }
664:                 $resultRow[$table][$column] = $row[$col];
665:                 if ($type === 'boolean' && $row[$col] !== null) {
666:                     $resultRow[$table][$column] = $this->boolean($resultRow[$table][$column]);
667:                 }
668:             }
669:             return $resultRow;
670:         }
671:         $this->_result->closeCursor();
672:         return false;
673:     }
674: 
675: /**
676:  * Inserts multiple values into a table
677:  *
678:  * @param string $table The table to insert into.
679:  * @param string $fields The fields to set.
680:  * @param array $values The values to set.
681:  * @return void
682:  */
683:     public function insertMulti($table, $fields, $values) {
684:         $primaryKey = $this->_getPrimaryKey($table);
685:         $hasPrimaryKey = $primaryKey && (
686:             (is_array($fields) && in_array($primaryKey, $fields)
687:             || (is_string($fields) && strpos($fields, $this->startQuote . $primaryKey . $this->endQuote) !== false))
688:         );
689: 
690:         if ($hasPrimaryKey) {
691:             $this->_execute('SET IDENTITY_INSERT ' . $this->fullTableName($table) . ' ON');
692:         }
693: 
694:         parent::insertMulti($table, $fields, $values);
695: 
696:         if ($hasPrimaryKey) {
697:             $this->_execute('SET IDENTITY_INSERT ' . $this->fullTableName($table) . ' OFF');
698:         }
699:     }
700: 
701: /**
702:  * Generate a database-native column schema string
703:  *
704:  * @param array $column An array structured like the
705:  *   following: array('name'=>'value', 'type'=>'value'[, options]),
706:  *   where options can be 'default', 'length', or 'key'.
707:  * @return string
708:  */
709:     public function buildColumn($column) {
710:         $result = parent::buildColumn($column);
711:         $result = preg_replace('/(bigint|int|integer)\([0-9]+\)/i', '$1', $result);
712:         $result = preg_replace('/(bit)\([0-9]+\)/i', '$1', $result);
713:         if (strpos($result, 'DEFAULT NULL') !== false) {
714:             if (isset($column['default']) && $column['default'] === '') {
715:                 $result = str_replace('DEFAULT NULL', "DEFAULT ''", $result);
716:             } else {
717:                 $result = str_replace('DEFAULT NULL', 'NULL', $result);
718:             }
719:         } elseif (array_keys($column) === array('type', 'name')) {
720:             $result .= ' NULL';
721:         } elseif (strpos($result, "DEFAULT N'")) {
722:             $result = str_replace("DEFAULT N'", "DEFAULT '", $result);
723:         }
724:         return $result;
725:     }
726: 
727: /**
728:  * Format indexes for create table
729:  *
730:  * @param array $indexes The indexes to build
731:  * @param string $table The table to make indexes for.
732:  * @return string
733:  */
734:     public function buildIndex($indexes, $table = null) {
735:         $join = array();
736: 
737:         foreach ($indexes as $name => $value) {
738:             if ($name === 'PRIMARY') {
739:                 $join[] = 'PRIMARY KEY (' . $this->name($value['column']) . ')';
740:             } elseif (isset($value['unique']) && $value['unique']) {
741:                 $out = "ALTER TABLE {$table} ADD CONSTRAINT {$name} UNIQUE";
742: 
743:                 if (is_array($value['column'])) {
744:                     $value['column'] = implode(', ', array_map(array(&$this, 'name'), $value['column']));
745:                 } else {
746:                     $value['column'] = $this->name($value['column']);
747:                 }
748:                 $out .= "({$value['column']});";
749:                 $join[] = $out;
750:             }
751:         }
752:         return $join;
753:     }
754: 
755: /**
756:  * Makes sure it will return the primary key
757:  *
758:  * @param Model|string $model Model instance of table name
759:  * @return string
760:  */
761:     protected function _getPrimaryKey($model) {
762:         $schema = $this->describe($model);
763:         foreach ($schema as $field => $props) {
764:             if (isset($props['key']) && $props['key'] === 'primary') {
765:                 return $field;
766:             }
767:         }
768:         return null;
769:     }
770: 
771: /**
772:  * Returns number of affected rows in previous database operation. If no previous operation exists,
773:  * this returns false.
774:  *
775:  * @param mixed $source Unused
776:  * @return int Number of affected rows
777:  */
778:     public function lastAffected($source = null) {
779:         $affected = parent::lastAffected();
780:         if ($affected === null && $this->_lastAffected !== false) {
781:             return $this->_lastAffected;
782:         }
783:         return $affected;
784:     }
785: 
786: /**
787:  * Executes given SQL statement.
788:  *
789:  * @param string $sql SQL statement
790:  * @param array $params list of params to be bound to query (supported only in select)
791:  * @param array $prepareOptions Options to be used in the prepare statement
792:  * @return mixed PDOStatement if query executes with no problem, true as the result of a successful, false on error
793:  * query returning no rows, such as a CREATE statement, false otherwise
794:  * @throws PDOException
795:  */
796:     protected function _execute($sql, $params = array(), $prepareOptions = array()) {
797:         $this->_lastAffected = false;
798:         $sql = trim($sql);
799:         if (strncasecmp($sql, 'SELECT', 6) === 0 || preg_match('/^EXEC(?:UTE)?\s/mi', $sql) > 0) {
800:             $prepareOptions += array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL);
801:             return parent::_execute($sql, $params, $prepareOptions);
802:         }
803:         try {
804:             $this->_lastAffected = $this->_connection->exec($sql);
805:             if ($this->_lastAffected === false) {
806:                 $this->_results = null;
807:                 $error = $this->_connection->errorInfo();
808:                 $this->error = $error[2];
809:                 return false;
810:             }
811:             return true;
812:         } catch (PDOException $e) {
813:             if (isset($query->queryString)) {
814:                 $e->queryString = $query->queryString;
815:             } else {
816:                 $e->queryString = $sql;
817:             }
818:             throw $e;
819:         }
820:     }
821: 
822: /**
823:  * Generate a "drop table" statement for the given table
824:  *
825:  * @param type $table Name of the table to drop
826:  * @return string Drop table SQL statement
827:  */
828:     protected function _dropTable($table) {
829:         return "IF OBJECT_ID('" . $this->fullTableName($table, false) . "', 'U') IS NOT NULL DROP TABLE " . $this->fullTableName($table) . ";";
830:     }
831: 
832: /**
833:  * Gets the schema name
834:  *
835:  * @return string The schema name
836:  */
837:     public function getSchemaName() {
838:         return $this->config['schema'];
839:     }
840: 
841: /**
842:  * Returns a locking hint for the given mode.
843:  *
844:  * Currently, this method only returns WITH (UPDLOCK) when the mode is set to true.
845:  *
846:  * @param mixed $mode Lock mode
847:  * @return string|null WITH (UPDLOCK) clause or null
848:  */
849:     public function getLockingHint($mode) {
850:         if ($mode !== true) {
851:             return null;
852:         }
853:         return ' WITH (UPDLOCK)';
854:     }
855: }
856: