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.10 API

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