CakePHP
  • Documentation
    • Book
    • API
    • Videos
    • Reporting Security Issues
    • Privacy Policy
    • Logos & Trademarks
  • Business Solutions
  • Swag
  • Road Trip
  • Team
  • Community
    • Community
    • Get Involved
    • Issues (GitHub)
    • Bakery
    • Featured Resources
    • Training
    • Meetups
    • My CakePHP
    • CakeFest
    • Newsletter
    • Linkedin
    • YouTube
    • Facebook
    • Twitter
    • Mastodon
    • Help & Support
    • Forum
    • Stack Overflow
    • Slack
    • Paid Support
CakePHP

C CakePHP 2.8 API

  • Overview
  • Tree
  • Deprecated
  • Version:
    • 2.8
      • 4.2
      • 4.1
      • 4.0
      • 3.9
      • 3.8
      • 3.7
      • 3.6
      • 3.5
      • 3.4
      • 3.3
      • 3.2
      • 3.1
      • 3.0
      • 2.10
      • 2.9
      • 2.8
      • 2.7
      • 2.6
      • 2.5
      • 2.4
      • 2.3
      • 2.2
      • 2.1
      • 2.0
      • 1.3
      • 1.2

Packages

  • Cake
    • Cache
      • Engine
    • Configure
    • Console
      • Command
        • Task
    • Controller
      • Component
        • Acl
        • Auth
    • Core
    • Error
    • Event
    • I18n
    • Log
      • Engine
    • Model
      • Behavior
      • Datasource
        • Database
        • Session
      • Validator
    • Network
      • Email
      • Http
    • Routing
      • Filter
      • Route
    • TestSuite
      • Coverage
      • Fixture
      • Reporter
    • Utility
    • View
      • Helper
  • None

Classes

  • Mysql
  • Postgres
  • Sqlite
  • Sqlserver
  1: <?php
  2: /**
  3:  * MySQL 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:  * MySQL DBO driver object
 23:  *
 24:  * Provides connection and SQL generation for MySQL RDMS
 25:  *
 26:  * @package       Cake.Model.Datasource.Database
 27:  */
 28: class Mysql extends DboSource {
 29: 
 30: /**
 31:  * Datasource description
 32:  *
 33:  * @var string
 34:  */
 35:     public $description = "MySQL DBO Driver";
 36: 
 37: /**
 38:  * Base configuration settings for MySQL driver
 39:  *
 40:  * @var array
 41:  */
 42:     protected $_baseConfig = array(
 43:         'persistent' => true,
 44:         'host' => 'localhost',
 45:         'login' => 'root',
 46:         'password' => '',
 47:         'database' => 'cake',
 48:         'port' => '3306',
 49:         'flags' => array()
 50:     );
 51: 
 52: /**
 53:  * Reference to the PDO object connection
 54:  *
 55:  * @var PDO
 56:  */
 57:     protected $_connection = null;
 58: 
 59: /**
 60:  * Start quote
 61:  *
 62:  * @var string
 63:  */
 64:     public $startQuote = "`";
 65: 
 66: /**
 67:  * End quote
 68:  *
 69:  * @var string
 70:  */
 71:     public $endQuote = "`";
 72: 
 73: /**
 74:  * use alias for update and delete. Set to true if version >= 4.1
 75:  *
 76:  * @var bool
 77:  */
 78:     protected $_useAlias = true;
 79: 
 80: /**
 81:  * List of engine specific additional field parameters used on table creating
 82:  *
 83:  * @var array
 84:  */
 85:     public $fieldParameters = array(
 86:         'charset' => array('value' => 'CHARACTER SET', 'quote' => false, 'join' => ' ', 'column' => false, 'position' => 'beforeDefault'),
 87:         'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => ' ', 'column' => 'Collation', 'position' => 'beforeDefault'),
 88:         'comment' => array('value' => 'COMMENT', 'quote' => true, 'join' => ' ', 'column' => 'Comment', 'position' => 'afterDefault'),
 89:         'unsigned' => array(
 90:             'value' => 'UNSIGNED', 'quote' => false, 'join' => ' ', 'column' => false, 'position' => 'beforeDefault',
 91:             'noVal' => true,
 92:             'options' => array(true),
 93:             'types' => array('integer', 'float', 'decimal', 'biginteger')
 94:         )
 95:     );
 96: 
 97: /**
 98:  * List of table engine specific parameters used on table creating
 99:  *
100:  * @var array
101:  */
102:     public $tableParameters = array(
103:         'charset' => array('value' => 'DEFAULT CHARSET', 'quote' => false, 'join' => '=', 'column' => 'charset'),
104:         'collate' => array('value' => 'COLLATE', 'quote' => false, 'join' => '=', 'column' => 'Collation'),
105:         'engine' => array('value' => 'ENGINE', 'quote' => false, 'join' => '=', 'column' => 'Engine'),
106:         'comment' => array('value' => 'COMMENT', 'quote' => true, 'join' => '=', 'column' => 'Comment'),
107:     );
108: 
109: /**
110:  * MySQL column definition
111:  *
112:  * @var array
113:  */
114:     public $columns = array(
115:         'primary_key' => array('name' => 'NOT NULL AUTO_INCREMENT'),
116:         'string' => array('name' => 'varchar', 'limit' => '255'),
117:         'text' => array('name' => 'text'),
118:         'biginteger' => array('name' => 'bigint', 'limit' => '20'),
119:         'integer' => array('name' => 'int', 'limit' => '11', 'formatter' => 'intval'),
120:         'float' => array('name' => 'float', 'formatter' => 'floatval'),
121:         'decimal' => array('name' => 'decimal', 'formatter' => 'floatval'),
122:         'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
123:         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
124:         'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
125:         'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
126:         'binary' => array('name' => 'blob'),
127:         'boolean' => array('name' => 'tinyint', 'limit' => '1')
128:     );
129: 
130: /**
131:  * Mapping of collation names to character set names
132:  *
133:  * @var array
134:  */
135:     protected $_charsets = array();
136: 
137: /**
138:  * Connects to the database using options in the given configuration array.
139:  *
140:  * MySQL supports a few additional options that other drivers do not:
141:  *
142:  * - `unix_socket` Set to the path of the MySQL sock file. Can be used in place
143:  *   of host + port.
144:  * - `ssl_key` SSL key file for connecting via SSL. Must be combined with `ssl_cert`.
145:  * - `ssl_cert` The SSL certificate to use when connecting via SSL. Must be
146:  *   combined with `ssl_key`.
147:  * - `ssl_ca` The certificate authority for SSL connections.
148:  *
149:  * @return bool True if the database could be connected, else false
150:  * @throws MissingConnectionException
151:  */
152:     public function connect() {
153:         $config = $this->config;
154:         $this->connected = false;
155: 
156:         $flags = $config['flags'] + array(
157:             PDO::ATTR_PERSISTENT => $config['persistent'],
158:             PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
159:             PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
160:         );
161: 
162:         if (!empty($config['encoding'])) {
163:             $flags[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['encoding'];
164:         }
165:         if (!empty($config['ssl_key']) && !empty($config['ssl_cert'])) {
166:             $flags[PDO::MYSQL_ATTR_SSL_KEY] = $config['ssl_key'];
167:             $flags[PDO::MYSQL_ATTR_SSL_CERT] = $config['ssl_cert'];
168:         }
169:         if (!empty($config['ssl_ca'])) {
170:             $flags[PDO::MYSQL_ATTR_SSL_CA] = $config['ssl_ca'];
171:         }
172:         if (empty($config['unix_socket'])) {
173:             $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['database']}";
174:         } else {
175:             $dsn = "mysql:unix_socket={$config['unix_socket']};dbname={$config['database']}";
176:         }
177: 
178:         try {
179:             $this->_connection = new PDO(
180:                 $dsn,
181:                 $config['login'],
182:                 $config['password'],
183:                 $flags
184:             );
185:             $this->connected = true;
186:             if (!empty($config['settings'])) {
187:                 foreach ($config['settings'] as $key => $value) {
188:                     $this->_execute("SET $key=$value");
189:                 }
190:             }
191:         } catch (PDOException $e) {
192:             throw new MissingConnectionException(array(
193:                 'class' => get_class($this),
194:                 'message' => $e->getMessage()
195:             ));
196:         }
197: 
198:         $this->_charsets = array();
199:         $this->_useAlias = (bool)version_compare($this->getVersion(), "4.1", ">=");
200: 
201:         return $this->connected;
202:     }
203: 
204: /**
205:  * Check whether the MySQL extension is installed/loaded
206:  *
207:  * @return bool
208:  */
209:     public function enabled() {
210:         return in_array('mysql', PDO::getAvailableDrivers());
211:     }
212: 
213: /**
214:  * Returns an array of sources (tables) in the database.
215:  *
216:  * @param mixed $data List of tables.
217:  * @return array Array of table names in the database
218:  */
219:     public function listSources($data = null) {
220:         $cache = parent::listSources();
221:         if ($cache) {
222:             return $cache;
223:         }
224:         $result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database']));
225: 
226:         if (!$result) {
227:             $result->closeCursor();
228:             return array();
229:         }
230:         $tables = array();
231: 
232:         while ($line = $result->fetch(PDO::FETCH_NUM)) {
233:             $tables[] = $line[0];
234:         }
235: 
236:         $result->closeCursor();
237:         parent::listSources($tables);
238:         return $tables;
239:     }
240: 
241: /**
242:  * Builds a map of the columns contained in a result
243:  *
244:  * @param PDOStatement $results The results to format.
245:  * @return void
246:  */
247:     public function resultSet($results) {
248:         $this->map = array();
249:         $numFields = $results->columnCount();
250:         $index = 0;
251: 
252:         while ($numFields-- > 0) {
253:             $column = $results->getColumnMeta($index);
254:             if ($column['len'] === 1 && (empty($column['native_type']) || $column['native_type'] === 'TINY')) {
255:                 $type = 'boolean';
256:             } else {
257:                 $type = empty($column['native_type']) ? 'string' : $column['native_type'];
258:             }
259:             if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false) {
260:                 $this->map[$index++] = array($column['table'], $column['name'], $type);
261:             } else {
262:                 $this->map[$index++] = array(0, $column['name'], $type);
263:             }
264:         }
265:     }
266: 
267: /**
268:  * Fetches the next row from the current result set
269:  *
270:  * @return mixed array with results fetched and mapped to column names or false if there is no results left to fetch
271:  */
272:     public function fetchResult() {
273:         if ($row = $this->_result->fetch(PDO::FETCH_NUM)) {
274:             $resultRow = array();
275:             foreach ($this->map as $col => $meta) {
276:                 list($table, $column, $type) = $meta;
277:                 $resultRow[$table][$column] = $row[$col];
278:                 if ($type === 'boolean' && $row[$col] !== null) {
279:                     $resultRow[$table][$column] = $this->boolean($resultRow[$table][$column]);
280:                 }
281:             }
282:             return $resultRow;
283:         }
284:         $this->_result->closeCursor();
285:         return false;
286:     }
287: 
288: /**
289:  * Gets the database encoding
290:  *
291:  * @return string The database encoding
292:  */
293:     public function getEncoding() {
294:         return $this->_execute('SHOW VARIABLES LIKE ?', array('character_set_client'))->fetchObject()->Value;
295:     }
296: 
297: /**
298:  * Query charset by collation
299:  *
300:  * @param string $name Collation name
301:  * @return string Character set name
302:  */
303:     public function getCharsetName($name) {
304:         if ((bool)version_compare($this->getVersion(), "5", "<")) {
305:             return false;
306:         }
307:         if (isset($this->_charsets[$name])) {
308:             return $this->_charsets[$name];
309:         }
310:         $r = $this->_execute(
311:             'SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME = ?',
312:             array($name)
313:         );
314:         $cols = $r->fetch(PDO::FETCH_ASSOC);
315: 
316:         if (isset($cols['CHARACTER_SET_NAME'])) {
317:             $this->_charsets[$name] = $cols['CHARACTER_SET_NAME'];
318:         } else {
319:             $this->_charsets[$name] = false;
320:         }
321:         return $this->_charsets[$name];
322:     }
323: 
324: /**
325:  * Returns an array of the fields in given table name.
326:  *
327:  * @param Model|string $model Name of database table to inspect or model instance
328:  * @return array Fields in table. Keys are name and type
329:  * @throws CakeException
330:  */
331:     public function describe($model) {
332:         $key = $this->fullTableName($model, false);
333:         $cache = parent::describe($key);
334:         if ($cache) {
335:             return $cache;
336:         }
337:         $table = $this->fullTableName($model);
338: 
339:         $fields = false;
340:         $cols = $this->_execute('SHOW FULL COLUMNS FROM ' . $table);
341:         if (!$cols) {
342:             throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table));
343:         }
344: 
345:         while ($column = $cols->fetch(PDO::FETCH_OBJ)) {
346:             $fields[$column->Field] = array(
347:                 'type' => $this->column($column->Type),
348:                 'null' => ($column->Null === 'YES' ? true : false),
349:                 'default' => $column->Default,
350:                 'length' => $this->length($column->Type)
351:             );
352:             if (in_array($fields[$column->Field]['type'], $this->fieldParameters['unsigned']['types'], true)) {
353:                 $fields[$column->Field]['unsigned'] = $this->_unsigned($column->Type);
354:             }
355:             if (in_array($fields[$column->Field]['type'], array('timestamp', 'datetime')) && strtoupper($column->Default) === 'CURRENT_TIMESTAMP') {
356:                 $fields[$column->Field]['default'] = null;
357:             }
358:             if (!empty($column->Key) && isset($this->index[$column->Key])) {
359:                 $fields[$column->Field]['key'] = $this->index[$column->Key];
360:             }
361:             foreach ($this->fieldParameters as $name => $value) {
362:                 if (!empty($column->{$value['column']})) {
363:                     $fields[$column->Field][$name] = $column->{$value['column']};
364:                 }
365:             }
366:             if (isset($fields[$column->Field]['collate'])) {
367:                 $charset = $this->getCharsetName($fields[$column->Field]['collate']);
368:                 if ($charset) {
369:                     $fields[$column->Field]['charset'] = $charset;
370:                 }
371:             }
372:         }
373:         $this->_cacheDescription($key, $fields);
374:         $cols->closeCursor();
375:         return $fields;
376:     }
377: 
378: /**
379:  * Generates and executes an SQL UPDATE statement for given model, fields, and values.
380:  *
381:  * @param Model $model The model to update.
382:  * @param array $fields The fields to update.
383:  * @param array $values The values to set.
384:  * @param mixed $conditions The conditions to use.
385:  * @return array
386:  */
387:     public function update(Model $model, $fields = array(), $values = null, $conditions = null) {
388:         if (!$this->_useAlias) {
389:             return parent::update($model, $fields, $values, $conditions);
390:         }
391: 
392:         if (!$values) {
393:             $combined = $fields;
394:         } else {
395:             $combined = array_combine($fields, $values);
396:         }
397: 
398:         $alias = $joins = false;
399:         $fields = $this->_prepareUpdateFields($model, $combined, empty($conditions), !empty($conditions));
400:         $fields = implode(', ', $fields);
401:         $table = $this->fullTableName($model);
402: 
403:         if (!empty($conditions)) {
404:             $alias = $this->name($model->alias);
405:             if ($model->name === $model->alias) {
406:                 $joins = implode(' ', $this->_getJoins($model));
407:             }
408:         }
409:         $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);
410: 
411:         if ($conditions === false) {
412:             return false;
413:         }
414: 
415:         if (!$this->execute($this->renderStatement('update', compact('table', 'alias', 'joins', 'fields', 'conditions')))) {
416:             $model->onError();
417:             return false;
418:         }
419:         return true;
420:     }
421: 
422: /**
423:  * Generates and executes an SQL DELETE statement for given id/conditions on given model.
424:  *
425:  * @param Model $model The model to delete from.
426:  * @param mixed $conditions The conditions to use.
427:  * @return bool Success
428:  */
429:     public function delete(Model $model, $conditions = null) {
430:         if (!$this->_useAlias) {
431:             return parent::delete($model, $conditions);
432:         }
433:         $alias = $this->name($model->alias);
434:         $table = $this->fullTableName($model);
435:         $joins = implode(' ', $this->_getJoins($model));
436: 
437:         if (empty($conditions)) {
438:             $alias = $joins = false;
439:         }
440:         $complexConditions = $this->_deleteNeedsComplexConditions($model, $conditions);
441:         if (!$complexConditions) {
442:             $joins = false;
443:         }
444: 
445:         $conditions = $this->conditions($this->defaultConditions($model, $conditions, $alias), true, true, $model);
446:         if ($conditions === false) {
447:             return false;
448:         }
449:         if ($this->execute($this->renderStatement('delete', compact('alias', 'table', 'joins', 'conditions'))) === false) {
450:             $model->onError();
451:             return false;
452:         }
453:         return true;
454:     }
455: 
456: /**
457:  * Checks whether complex conditions are needed for a delete with the given conditions.
458:  *
459:  * @param Model $model The model to delete from.
460:  * @param mixed $conditions The conditions to use.
461:  * @return bool Whether or not complex conditions are needed
462:  */
463:     protected function _deleteNeedsComplexConditions(Model $model, $conditions) {
464:         $fields = array_keys($this->describe($model));
465:         foreach ((array)$conditions as $key => $value) {
466:             if (in_array(strtolower(trim($key)), $this->_sqlBoolOps, true)) {
467:                 if ($this->_deleteNeedsComplexConditions($model, $value)) {
468:                     return true;
469:                 }
470:             } elseif (strpos($key, $model->alias) === false && !in_array($key, $fields, true)) {
471:                 return true;
472:             }
473:         }
474:         return false;
475:     }
476: 
477: /**
478:  * Sets the database encoding
479:  *
480:  * @param string $enc Database encoding
481:  * @return bool
482:  */
483:     public function setEncoding($enc) {
484:         return $this->_execute('SET NAMES ' . $enc) !== false;
485:     }
486: 
487: /**
488:  * Returns an array of the indexes in given datasource name.
489:  *
490:  * @param string $model Name of model to inspect
491:  * @return array Fields in table. Keys are column and unique
492:  */
493:     public function index($model) {
494:         $index = array();
495:         $table = $this->fullTableName($model);
496:         $old = version_compare($this->getVersion(), '4.1', '<=');
497:         if ($table) {
498:             $indexes = $this->_execute('SHOW INDEX FROM ' . $table);
499:             // @codingStandardsIgnoreStart
500:             // MySQL columns don't match the cakephp conventions.
501:             while ($idx = $indexes->fetch(PDO::FETCH_OBJ)) {
502:                 if ($old) {
503:                     $idx = (object)current((array)$idx);
504:                 }
505:                 if (!isset($index[$idx->Key_name]['column'])) {
506:                     $col = array();
507:                     $index[$idx->Key_name]['column'] = $idx->Column_name;
508: 
509:                     if ($idx->Index_type === 'FULLTEXT') {
510:                         $index[$idx->Key_name]['type'] = strtolower($idx->Index_type);
511:                     } else {
512:                         $index[$idx->Key_name]['unique'] = (int)($idx->Non_unique == 0);
513:                     }
514:                 } else {
515:                     if (!empty($index[$idx->Key_name]['column']) && !is_array($index[$idx->Key_name]['column'])) {
516:                         $col[] = $index[$idx->Key_name]['column'];
517:                     }
518:                     $col[] = $idx->Column_name;
519:                     $index[$idx->Key_name]['column'] = $col;
520:                 }
521:                 if (!empty($idx->Sub_part)) {
522:                     if (!isset($index[$idx->Key_name]['length'])) {
523:                         $index[$idx->Key_name]['length'] = array();
524:                     }
525:                     $index[$idx->Key_name]['length'][$idx->Column_name] = $idx->Sub_part;
526:                 }
527:             }
528:             // @codingStandardsIgnoreEnd
529:             $indexes->closeCursor();
530:         }
531:         return $index;
532:     }
533: 
534: /**
535:  * Generate a MySQL Alter Table syntax for the given Schema comparison
536:  *
537:  * @param array $compare Result of a CakeSchema::compare()
538:  * @param string $table The table name.
539:  * @return array Array of alter statements to make.
540:  */
541:     public function alterSchema($compare, $table = null) {
542:         if (!is_array($compare)) {
543:             return false;
544:         }
545:         $out = '';
546:         $colList = array();
547:         foreach ($compare as $curTable => $types) {
548:             $indexes = $tableParameters = $colList = array();
549:             if (!$table || $table === $curTable) {
550:                 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
551:                 foreach ($types as $type => $column) {
552:                     if (isset($column['indexes'])) {
553:                         $indexes[$type] = $column['indexes'];
554:                         unset($column['indexes']);
555:                     }
556:                     if (isset($column['tableParameters'])) {
557:                         $tableParameters[$type] = $column['tableParameters'];
558:                         unset($column['tableParameters']);
559:                     }
560:                     switch ($type) {
561:                         case 'add':
562:                             foreach ($column as $field => $col) {
563:                                 $col['name'] = $field;
564:                                 $alter = 'ADD ' . $this->buildColumn($col);
565:                                 if (isset($col['after'])) {
566:                                     $alter .= ' AFTER ' . $this->name($col['after']);
567:                                 }
568:                                 $colList[] = $alter;
569:                             }
570:                             break;
571:                         case 'drop':
572:                             foreach ($column as $field => $col) {
573:                                 $col['name'] = $field;
574:                                 $colList[] = 'DROP ' . $this->name($field);
575:                             }
576:                             break;
577:                         case 'change':
578:                             foreach ($column as $field => $col) {
579:                                 if (!isset($col['name'])) {
580:                                     $col['name'] = $field;
581:                                 }
582:                                 $alter = 'CHANGE ' . $this->name($field) . ' ' . $this->buildColumn($col);
583:                                 if (isset($col['after'])) {
584:                                     $alter .= ' AFTER ' . $this->name($col['after']);
585:                                 }
586:                                 $colList[] = $alter;
587:                             }
588:                             break;
589:                     }
590:                 }
591:                 $colList = array_merge($colList, $this->_alterIndexes($curTable, $indexes));
592:                 $colList = array_merge($colList, $this->_alterTableParameters($curTable, $tableParameters));
593:                 $out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
594:             }
595:         }
596:         return $out;
597:     }
598: 
599: /**
600:  * Generate a "drop table" statement for the given table
601:  *
602:  * @param type $table Name of the table to drop
603:  * @return string Drop table SQL statement
604:  */
605:     protected function _dropTable($table) {
606:         return 'DROP TABLE IF EXISTS ' . $this->fullTableName($table) . ";";
607:     }
608: 
609: /**
610:  * Generate MySQL table parameter alteration statements for a table.
611:  *
612:  * @param string $table Table to alter parameters for.
613:  * @param array $parameters Parameters to add & drop.
614:  * @return array Array of table property alteration statements.
615:  */
616:     protected function _alterTableParameters($table, $parameters) {
617:         if (isset($parameters['change'])) {
618:             return $this->buildTableParameters($parameters['change']);
619:         }
620:         return array();
621:     }
622: 
623: /**
624:  * Format indexes for create table
625:  *
626:  * @param array $indexes An array of indexes to generate SQL from
627:  * @param string $table Optional table name, not used
628:  * @return array An array of SQL statements for indexes
629:  * @see DboSource::buildIndex()
630:  */
631:     public function buildIndex($indexes, $table = null) {
632:         $join = array();
633:         foreach ($indexes as $name => $value) {
634:             $out = '';
635:             if ($name === 'PRIMARY') {
636:                 $out .= 'PRIMARY ';
637:                 $name = null;
638:             } else {
639:                 if (!empty($value['unique'])) {
640:                     $out .= 'UNIQUE ';
641:                 }
642:                 $name = $this->startQuote . $name . $this->endQuote;
643:             }
644:             if (isset($value['type']) && strtolower($value['type']) === 'fulltext') {
645:                 $out .= 'FULLTEXT ';
646:             }
647:             $out .= 'KEY ' . $name . ' (';
648: 
649:             if (is_array($value['column'])) {
650:                 if (isset($value['length'])) {
651:                     $vals = array();
652:                     foreach ($value['column'] as $column) {
653:                         $name = $this->name($column);
654:                         if (isset($value['length'])) {
655:                             $name .= $this->_buildIndexSubPart($value['length'], $column);
656:                         }
657:                         $vals[] = $name;
658:                     }
659:                     $out .= implode(', ', $vals);
660:                 } else {
661:                     $out .= implode(', ', array_map(array(&$this, 'name'), $value['column']));
662:                 }
663:             } else {
664:                 $out .= $this->name($value['column']);
665:                 if (isset($value['length'])) {
666:                     $out .= $this->_buildIndexSubPart($value['length'], $value['column']);
667:                 }
668:             }
669:             $out .= ')';
670:             $join[] = $out;
671:         }
672:         return $join;
673:     }
674: 
675: /**
676:  * Generate MySQL index alteration statements for a table.
677:  *
678:  * @param string $table Table to alter indexes for
679:  * @param array $indexes Indexes to add and drop
680:  * @return array Index alteration statements
681:  */
682:     protected function _alterIndexes($table, $indexes) {
683:         $alter = array();
684:         if (isset($indexes['drop'])) {
685:             foreach ($indexes['drop'] as $name => $value) {
686:                 $out = 'DROP ';
687:                 if ($name === 'PRIMARY') {
688:                     $out .= 'PRIMARY KEY';
689:                 } else {
690:                     $out .= 'KEY ' . $this->startQuote . $name . $this->endQuote;
691:                 }
692:                 $alter[] = $out;
693:             }
694:         }
695:         if (isset($indexes['add'])) {
696:             $add = $this->buildIndex($indexes['add']);
697:             foreach ($add as $index) {
698:                 $alter[] = 'ADD ' . $index;
699:             }
700:         }
701:         return $alter;
702:     }
703: 
704: /**
705:  * Format length for text indexes
706:  *
707:  * @param array $lengths An array of lengths for a single index
708:  * @param string $column The column for which to generate the index length
709:  * @return string Formatted length part of an index field
710:  */
711:     protected function _buildIndexSubPart($lengths, $column) {
712:         if ($lengths === null) {
713:             return '';
714:         }
715:         if (!isset($lengths[$column])) {
716:             return '';
717:         }
718:         return '(' . $lengths[$column] . ')';
719:     }
720: 
721: /**
722:  * Returns a detailed array of sources (tables) in the database.
723:  *
724:  * @param string $name Table name to get parameters
725:  * @return array Array of table names in the database
726:  */
727:     public function listDetailedSources($name = null) {
728:         $condition = '';
729:         if (is_string($name)) {
730:             $condition = ' WHERE name = ' . $this->value($name);
731:         }
732:         $result = $this->_connection->query('SHOW TABLE STATUS ' . $condition, PDO::FETCH_ASSOC);
733: 
734:         if (!$result) {
735:             $result->closeCursor();
736:             return array();
737:         }
738:         $tables = array();
739:         foreach ($result as $row) {
740:             $tables[$row['Name']] = (array)$row;
741:             unset($tables[$row['Name']]['queryString']);
742:             if (!empty($row['Collation'])) {
743:                 $charset = $this->getCharsetName($row['Collation']);
744:                 if ($charset) {
745:                     $tables[$row['Name']]['charset'] = $charset;
746:                 }
747:             }
748:         }
749:         $result->closeCursor();
750:         if (is_string($name) && isset($tables[$name])) {
751:             return $tables[$name];
752:         }
753:         return $tables;
754:     }
755: 
756: /**
757:  * Converts database-layer column types to basic types
758:  *
759:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
760:  * @return string Abstract column type (i.e. "string")
761:  */
762:     public function column($real) {
763:         if (is_array($real)) {
764:             $col = $real['name'];
765:             if (isset($real['limit'])) {
766:                 $col .= '(' . $real['limit'] . ')';
767:             }
768:             return $col;
769:         }
770: 
771:         $col = str_replace(')', '', $real);
772:         $limit = $this->length($real);
773:         if (strpos($col, '(') !== false) {
774:             list($col, $vals) = explode('(', $col);
775:         }
776: 
777:         if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
778:             return $col;
779:         }
780:         if (($col === 'tinyint' && $limit === 1) || $col === 'boolean') {
781:             return 'boolean';
782:         }
783:         if (strpos($col, 'bigint') !== false || $col === 'bigint') {
784:             return 'biginteger';
785:         }
786:         if (strpos($col, 'int') !== false) {
787:             return 'integer';
788:         }
789:         if (strpos($col, 'char') !== false || $col === 'tinytext') {
790:             return 'string';
791:         }
792:         if (strpos($col, 'text') !== false) {
793:             return 'text';
794:         }
795:         if (strpos($col, 'blob') !== false || $col === 'binary') {
796:             return 'binary';
797:         }
798:         if (strpos($col, 'float') !== false || strpos($col, 'double') !== false) {
799:             return 'float';
800:         }
801:         if (strpos($col, 'decimal') !== false || strpos($col, 'numeric') !== false) {
802:             return 'decimal';
803:         }
804:         if (strpos($col, 'enum') !== false) {
805:             return "enum($vals)";
806:         }
807:         if (strpos($col, 'set') !== false) {
808:             return "set($vals)";
809:         }
810:         return 'text';
811:     }
812: 
813: /**
814:  * {@inheritDoc}
815:  */
816:     public function value($data, $column = null, $null = true) {
817:         $value = parent::value($data, $column, $null);
818:         if (is_numeric($value) && substr($column, 0, 3) === 'set') {
819:             return $this->_connection->quote($value);
820:         }
821:         return $value;
822:     }
823: 
824: /**
825:  * Gets the schema name
826:  *
827:  * @return string The schema name
828:  */
829:     public function getSchemaName() {
830:         return $this->config['database'];
831:     }
832: 
833: /**
834:  * Check if the server support nested transactions
835:  *
836:  * @return bool
837:  */
838:     public function nestedTransactionSupported() {
839:         return $this->useNestedTransactions && version_compare($this->getVersion(), '4.1', '>=');
840:     }
841: 
842: /**
843:  * Check if column type is unsigned
844:  *
845:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
846:  * @return bool True if column is unsigned, false otherwise
847:  */
848:     protected function _unsigned($real) {
849:         return strpos(strtolower($real), 'unsigned') !== false;
850:     }
851: 
852: /**
853:  * Inserts multiple values into a table. Uses a single query in order to insert
854:  * multiple rows.
855:  *
856:  * @param string $table The table being inserted into.
857:  * @param array $fields The array of field/column names being inserted.
858:  * @param array $values The array of values to insert. The values should
859:  *   be an array of rows. Each row should have values keyed by the column name.
860:  *   Each row must have the values in the same order as $fields.
861:  * @return bool
862:  */
863:     public function insertMulti($table, $fields, $values) {
864:         $table = $this->fullTableName($table);
865:         $holder = implode(', ', array_fill(0, count($fields), '?'));
866:         $fields = implode(', ', array_map(array($this, 'name'), $fields));
867:         $pdoMap = array(
868:             'integer' => PDO::PARAM_INT,
869:             'float' => PDO::PARAM_STR,
870:             'boolean' => PDO::PARAM_BOOL,
871:             'string' => PDO::PARAM_STR,
872:             'text' => PDO::PARAM_STR
873:         );
874:         $columnMap = array();
875:         $rowHolder = "({$holder})";
876:         $sql = "INSERT INTO {$table} ({$fields}) VALUES ";
877:         $countRows = count($values);
878:         for ($i = 0; $i < $countRows; $i++) {
879:             if ($i !== 0) {
880:                 $sql .= ',';
881:             }
882:             $sql .= " $rowHolder";
883:         }
884:         $statement = $this->_connection->prepare($sql);
885:         foreach ($values[key($values)] as $key => $val) {
886:             $type = $this->introspectType($val);
887:             $columnMap[$key] = $pdoMap[$type];
888:         }
889:         $valuesList = array();
890:         $i = 1;
891:         foreach ($values as $value) {
892:             foreach ($value as $col => $val) {
893:                 $valuesList[] = $val;
894:                 $statement->bindValue($i, $val, $columnMap[$col]);
895:                 $i++;
896:             }
897:         }
898:         $result = $statement->execute();
899:         $statement->closeCursor();
900:         if ($this->fullDebug) {
901:             $this->logQuery($sql, $valuesList);
902:         }
903:         return $result;
904:     }
905: }
906: 
OpenHub
Rackspace
Rackspace
  • Business Solutions
  • Showcase
  • Documentation
  • Book
  • API
  • Videos
  • Reporting Security Issues
  • Privacy Policy
  • Logos & Trademarks
  • Community
  • Get Involved
  • Issues (GitHub)
  • Bakery
  • Featured Resources
  • Training
  • Meetups
  • My CakePHP
  • CakeFest
  • Newsletter
  • Linkedin
  • YouTube
  • Facebook
  • Twitter
  • Mastodon
  • Help & Support
  • Forum
  • Stack Overflow
  • Slack
  • Paid Support

Generated using CakePHP API Docs