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