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