00001 <?php
00002 /* SVN FILE: $Id: dbo__mysql_8php-source.html 675 2008-12-26 00:27:14Z gwoo $ */
00003 /**
00004  * MySQL layer for DBO
00005  *
00006  * Long description for file
00007  *
00008  * PHP versions 4 and 5
00009  *
00010  * CakePHP(tm) :  Rapid Development Framework <http://www.cakephp.org/>
00011  * Copyright 2005-2008, Cake Software Foundation, Inc.
00012  *                              1785 E. Sahara Avenue, Suite 490-204
00013  *                              Las Vegas, Nevada 89104
00014  *
00015  * Licensed under The MIT License
00016  * Redistributions of files must retain the above copyright notice.
00017  *
00018  * @filesource
00019  * @copyright       Copyright 2005-2008, Cake Software Foundation, Inc.
00020  * @link                http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project
00021  * @package         cake
00022  * @subpackage      cake.cake.libs.model.dbo
00023  * @since           CakePHP(tm) v 0.10.5.1790
00024  * @version         $Revision: 675 $
00025  * @modifiedby      $LastChangedBy: gwoo $
00026  * @lastmodified    $Date: 2008-12-25 16:27:14 -0800 (Thu, 25 Dec 2008) $
00027  * @license         http://www.opensource.org/licenses/mit-license.php The MIT License
00028  */
00029 
00030 /**
00031  * Short description for class.
00032  *
00033  * Long description for class
00034  *
00035  * @package     cake
00036  * @subpackage  cake.cake.libs.model.dbo
00037  */
00038 class DboMysql extends DboSource {
00039 /**
00040  * Enter description here...
00041  *
00042  * @var unknown_type
00043  */
00044     var $description = "MySQL DBO Driver";
00045 /**
00046  * Enter description here...
00047  *
00048  * @var unknown_type
00049  */
00050     var $startQuote = "`";
00051 /**
00052  * Enter description here...
00053  *
00054  * @var unknown_type
00055  */
00056     var $endQuote = "`";
00057 /**
00058  * Base configuration settings for MySQL driver
00059  *
00060  * @var array
00061  */
00062     var $_baseConfig = array(
00063         'persistent' => true,
00064         'host' => 'localhost',
00065         'login' => 'root',
00066         'password' => '',
00067         'database' => 'cake',
00068         'port' => '3306',
00069         'connect' => 'mysql_pconnect'
00070     );
00071 /**
00072  * MySQL column definition
00073  *
00074  * @var array
00075  */
00076     var $columns = array(
00077         'primary_key' => array('name' => 'int(11) DEFAULT NULL auto_increment'),
00078         'string' => array('name' => 'varchar', 'limit' => '255'),
00079         'text' => array('name' => 'text'),
00080         'integer' => array('name' => 'int', 'limit' => '11', 'formatter' => 'intval'),
00081         'float' => array('name' => 'float', 'formatter' => 'floatval'),
00082         'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
00083         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
00084         'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
00085         'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
00086         'binary' => array('name' => 'blob'),
00087         'boolean' => array('name' => 'tinyint', 'limit' => '1')
00088     );
00089 /**
00090  * Connects to the database using options in the given configuration array.
00091  *
00092  * @return boolean True if the database could be connected, else false
00093  */
00094     function connect() {
00095         $config = $this->config;
00096         $connect = $config['connect'];
00097         $this->connected = false;
00098 
00099         if (!$config['persistent'] || $config['connect'] === 'mysql_connect') {
00100             $this->connection = mysql_connect($config['host'] . ':' . $config['port'], $config['login'], $config['password'], true);
00101         } else {
00102             $this->connection = $connect($config['host'] . ':' . $config['port'], $config['login'], $config['password']);
00103         }
00104 
00105         if (mysql_select_db($config['database'], $this->connection)) {
00106             $this->connected = true;
00107         }
00108 
00109         if (isset($config['encoding']) && !empty($config['encoding'])) {
00110             $this->setEncoding($config['encoding']);
00111         }
00112 
00113         return $this->connected;
00114     }
00115 /**
00116  * Disconnects from database.
00117  *
00118  * @return boolean True if the database could be disconnected, else false
00119  */
00120     function disconnect() {
00121         @mysql_free_result($this->results);
00122         $this->connected = !@mysql_close($this->connection);
00123         return !$this->connected;
00124     }
00125 /**
00126  * Executes given SQL statement.
00127  *
00128  * @param string $sql SQL statement
00129  * @return resource Result resource identifier
00130  * @access protected
00131  */
00132     function _execute($sql) {
00133         return mysql_query($sql, $this->connection);
00134     }
00135 /**
00136  * Returns an array of sources (tables) in the database.
00137  *
00138  * @return array Array of tablenames in the database
00139  */
00140     function listSources() {
00141         $cache = parent::listSources();
00142         if ($cache != null) {
00143             return $cache;
00144         }
00145         $result = $this->_execute('SHOW TABLES FROM ' . $this->name($this->config['database']) . ';');
00146 
00147         if (!$result) {
00148             return array();
00149         }
00150 
00151         $tables = array();
00152 
00153         while ($line = mysql_fetch_array($result)) {
00154             $tables[] = $line[0];
00155         }
00156         parent::listSources($tables);
00157         return $tables;
00158     }
00159 /**
00160  * Returns an array of the fields in given table name.
00161  *
00162  * @param string $tableName Name of database table to inspect
00163  * @return array Fields in table. Keys are name and type
00164  */
00165     function describe(&$model) {
00166         $cache = parent::describe($model);
00167         if ($cache != null) {
00168             return $cache;
00169         }
00170         $fields = false;
00171         $cols = $this->query('DESCRIBE ' . $this->fullTableName($model));
00172 
00173         foreach ($cols as $column) {
00174             $colKey = array_keys($column);
00175             if (isset($column[$colKey[0]]) && !isset($column[0])) {
00176                 $column[0] = $column[$colKey[0]];
00177             }
00178             if (isset($column[0])) {
00179                 $fields[$column[0]['Field']] = array(
00180                     'type'      => $this->column($column[0]['Type']),
00181                     'null'      => ($column[0]['Null'] == 'YES' ? true : false),
00182                     'default'   => $column[0]['Default'],
00183                     'length'    => $this->length($column[0]['Type']),
00184                 );
00185                 if(!empty($column[0]['Key']) && isset($this->index[$column[0]['Key']])) {
00186                     $fields[$column[0]['Field']]['key'] = $this->index[$column[0]['Key']];
00187                 }
00188                 if(!empty($column[0]['Extra'])) {
00189                     $fields[$column[0]['Field']]['extra'] = $column[0]['Extra'];
00190                 }
00191             }
00192         }
00193         $this->__cacheDescription($this->fullTableName($model, false), $fields);
00194         return $fields;
00195     }
00196 /**
00197  * Returns a quoted and escaped string of $data for use in an SQL statement.
00198  *
00199  * @param string $data String to be prepared for use in an SQL statement
00200  * @param string $column The column into which this data will be inserted
00201  * @param boolean $safe Whether or not numeric data should be handled automagically if no column data is provided
00202  * @return string Quoted and escaped data
00203  */
00204     function value($data, $column = null, $safe = false) {
00205         $parent = parent::value($data, $column, $safe);
00206 
00207         if ($parent != null) {
00208             return $parent;
00209         } elseif ($data === null || (is_array($data) && empty($data))) {
00210             return 'NULL';
00211         } elseif ($data === '' && $column !== 'integer' && $column !== 'float' && $column !== 'boolean') {
00212             return  "''";
00213         }
00214         if (empty($column)) {
00215             $column = $this->introspectType($data);
00216         }
00217 
00218         switch ($column) {
00219             case 'boolean':
00220                 return $this->boolean((bool)$data);
00221             break;
00222             case 'integer':
00223             case 'float':
00224                 if ($data === '') {
00225                     return 'NULL';
00226                 }
00227                 if ((is_int($data) || is_float($data) || $data === '0') || (
00228                     is_numeric($data) && strpos($data, ',') === false &&
00229                     $data[0] != '0' && strpos($data, 'e') === false)) {
00230                         return $data;
00231                     }
00232             default:
00233                 $data = "'" . mysql_real_escape_string($data, $this->connection) . "'";
00234             break;
00235         }
00236         return $data;
00237     }
00238 /**
00239  * Begin a transaction
00240  *
00241  * @param unknown_type $model
00242  * @return boolean True on success, false on fail
00243  * (i.e. if the database/model does not support transactions).
00244  */
00245     function begin(&$model) {
00246         if (parent::begin($model)) {
00247             if ($this->execute('START TRANSACTION')) {
00248                 $this->_transactionStarted = true;
00249                 return true;
00250             }
00251         }
00252         return false;
00253     }
00254 /**
00255  * Commit a transaction
00256  *
00257  * @param unknown_type $model
00258  * @return boolean True on success, false on fail
00259  * (i.e. if the database/model does not support transactions,
00260  * or a transaction has not started).
00261  */
00262     function commit(&$model) {
00263         if (parent::commit($model)) {
00264             $this->_transactionStarted = false;
00265             return $this->execute('COMMIT');
00266         }
00267         return false;
00268     }
00269 /**
00270  * Rollback a transaction
00271  *
00272  * @param unknown_type $model
00273  * @return boolean True on success, false on fail
00274  * (i.e. if the database/model does not support transactions,
00275  * or a transaction has not started).
00276  */
00277     function rollback(&$model) {
00278         if (parent::rollback($model)) {
00279             return $this->execute('ROLLBACK');
00280         }
00281         return false;
00282     }
00283 /**
00284  * Returns a formatted error message from previous database operation.
00285  *
00286  * @return string Error message with error number
00287  */
00288     function lastError() {
00289         if (mysql_errno($this->connection)) {
00290             return mysql_errno($this->connection).': '.mysql_error($this->connection);
00291         }
00292         return null;
00293     }
00294 /**
00295  * Returns number of affected rows in previous database operation. If no previous operation exists,
00296  * this returns false.
00297  *
00298  * @return int Number of affected rows
00299  */
00300     function lastAffected() {
00301         if ($this->_result) {
00302             return mysql_affected_rows($this->connection);
00303         }
00304         return null;
00305     }
00306 /**
00307  * Returns number of rows in previous resultset. If no previous resultset exists,
00308  * this returns false.
00309  *
00310  * @return int Number of rows in resultset
00311  */
00312     function lastNumRows() {
00313         if ($this->_result and is_resource($this->_result)) {
00314             return @mysql_num_rows($this->_result);
00315         }
00316         return null;
00317     }
00318 /**
00319  * Returns the ID generated from the previous INSERT operation.
00320  *
00321  * @param unknown_type $source
00322  * @return in
00323  */
00324     function lastInsertId($source = null) {
00325         $id = $this->fetchRow('SELECT LAST_INSERT_ID() AS insertID', false);
00326         if ($id !== false && !empty($id) && !empty($id[0]) && isset($id[0]['insertID'])) {
00327             return $id[0]['insertID'];
00328         }
00329 
00330         return null;
00331     }
00332 /**
00333  * Converts database-layer column types to basic types
00334  *
00335  * @param string $real Real database-layer column type (i.e. "varchar(255)")
00336  * @return string Abstract column type (i.e. "string")
00337  */
00338     function column($real) {
00339         if (is_array($real)) {
00340             $col = $real['name'];
00341             if (isset($real['limit'])) {
00342                 $col .= '('.$real['limit'].')';
00343             }
00344             return $col;
00345         }
00346 
00347         $col = r(')', '', $real);
00348         $limit = $this->length($real);
00349         @list($col,$vals) = explode('(', $col);
00350 
00351         if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
00352             return $col;
00353         }
00354         if ($col == 'tinyint' && $limit == 1) {
00355             return 'boolean';
00356         }
00357         if (strpos($col, 'int') !== false) {
00358             return 'integer';
00359         }
00360         if (strpos($col, 'char') !== false || $col == 'tinytext') {
00361             return 'string';
00362         }
00363         if (strpos($col, 'text') !== false) {
00364             return 'text';
00365         }
00366         if (strpos($col, 'blob') !== false) {
00367             return 'binary';
00368         }
00369         if (in_array($col, array('float', 'double', 'decimal'))) {
00370             return 'float';
00371         }
00372         if (strpos($col, 'enum') !== false) {
00373             return "enum($vals)";
00374         }
00375         if ($col == 'boolean') {
00376             return $col;
00377         }
00378         return 'text';
00379     }
00380 /**
00381  * Gets the length of a database-native column description, or null if no length
00382  *
00383  * @param string $real Real database-layer column type (i.e. "varchar(255)")
00384  * @return int An integer representing the length of the column
00385  */
00386     function length($real) {
00387         $col = r(array(')', 'unsigned'), '', $real);
00388         $limit = null;
00389 
00390         if (strpos($col, '(') !== false) {
00391             list($col, $limit) = explode('(', $col);
00392         }
00393 
00394         if ($limit != null) {
00395             return intval($limit);
00396         }
00397         return null;
00398     }
00399 /**
00400  * Enter description here...
00401  *
00402  * @param unknown_type $results
00403  */
00404     function resultSet(&$results) {
00405         $this->results =& $results;
00406         $this->map = array();
00407         $num_fields = mysql_num_fields($results);
00408         $index = 0;
00409         $j = 0;
00410 
00411         while ($j < $num_fields) {
00412 
00413             $column = mysql_fetch_field($results,$j);
00414             if (!empty($column->table)) {
00415                 $this->map[$index++] = array($column->table, $column->name);
00416             } else {
00417                 $this->map[$index++] = array(0, $column->name);
00418             }
00419             $j++;
00420         }
00421     }
00422 /**
00423  * Fetches the next row from the current result set
00424  *
00425  * @return unknown
00426  */
00427     function fetchResult() {
00428         if ($row = mysql_fetch_row($this->results)) {
00429             $resultRow = array();
00430             $i = 0;
00431             foreach ($row as $index => $field) {
00432                 list($table, $column) = $this->map[$index];
00433                 $resultRow[$table][$column] = $row[$index];
00434                 $i++;
00435             }
00436             return $resultRow;
00437         } else {
00438             return false;
00439         }
00440     }
00441 /**
00442  * Sets the database encoding
00443  *
00444  * @param string $enc Database encoding
00445  * @return void
00446  */
00447     function setEncoding($enc) {
00448         return $this->_execute('SET NAMES ' . $enc) != false;
00449     }
00450 /**
00451  * Gets the database encoding
00452  *
00453  * @return string The database encoding
00454  */
00455     function getEncoding() {
00456         return mysql_client_encoding($this->connection);
00457     }
00458 /**
00459  * Returns an array of the indexes in given table name.
00460  *
00461  * @param string $model Name of model to inspect
00462  * @return array Fields in table. Keys are column and unique
00463  */
00464     function index($model) {
00465         $index = array();
00466         $table = $this->fullTableName($model, false);
00467         if($table) {
00468             $indexes = $this->query('SHOW INDEX FROM ' . $table);
00469             $keys = Set::extract($indexes, '{n}.STATISTICS');
00470             foreach ($keys as $i => $key) {
00471                 if(!isset($index[$key['Key_name']])) {
00472                     $index[$key['Key_name']]['column'] = $key['Column_name'];
00473                     $index[$key['Key_name']]['unique'] = ife($key['Non_unique'] == 0, 1, 0);
00474                 } else {
00475                     if(!is_array($index[$key['Key_name']]['column'])) {
00476                         $col[] = $index[$key['Key_name']]['column'];
00477                     }
00478                     $col[] = $key['Column_name'];
00479                     $index[$key['Key_name']]['column'] = $col;
00480                 }
00481             }
00482         }
00483         return $index;
00484     }
00485 /**
00486  * Generate a MySQL schema for the given Schema object
00487  *
00488  * @param object $schema An instance of a subclass of CakeSchema
00489  * @param string $table Optional.  If specified only the table name given will be generated.
00490  *                      Otherwise, all tables defined in the schema are generated.
00491  * @return string
00492  */
00493     function createSchema($schema, $table = null) {
00494         if (!is_a($schema, 'CakeSchema')) {
00495             trigger_error(__('Invalid schema object', true), E_USER_WARNING);
00496             return null;
00497         }
00498         $out = '';
00499         foreach ($schema->tables as $curTable => $columns) {
00500             if (!$table || $table == $curTable) {
00501                 $out .= 'CREATE TABLE ' . $this->fullTableName($curTable) . " (\n";
00502                 $cols = $colList = $index = array();
00503                 $primary = null;
00504                 foreach ($columns as $name => $col) {
00505                     if (is_string($col)) {
00506                         $col = array('type' => $col);
00507                     }
00508                     if (isset($col['key']) && $col['key'] == 'primary') {
00509                         $primary = $name;
00510                     }
00511                     if($name !== 'indexes') {
00512                         $col['name'] = $name;
00513                         if(!isset($col['type'])) {
00514                             $col['type'] = 'string';
00515                         }
00516                         $cols[] = $this->buildColumn($col);
00517                     } else {
00518                         $index[] =  $this->buildIndex($col);
00519                     }
00520 
00521                 }
00522                 if(empty($index) && !empty($primary)) {
00523                     $col = array('PRIMARY' => array('column'=> $primary, 'unique' => 1));
00524                     $index[] = $this->buildIndex($col);
00525                 }
00526                 $out .= "\t" . join(",\n\t", $cols) . ",\n\t". join(",\n\t", $index) . "\n);\n\n";
00527             }
00528         }
00529         return $out;
00530     }
00531 /**
00532  * Generate a MySQL Alter Table syntax for the given Schema comparison
00533  *
00534  * @param unknown_type $schema
00535  * @return unknown
00536  */
00537     function alterSchema($compare, $table = null) {
00538         if(!is_array($compare)) {
00539             return false;
00540         }
00541         $out = '';
00542         $colList = array();
00543         foreach($compare as $curTable => $types) {
00544             if (!$table || $table == $curTable) {
00545                 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
00546                 foreach($types as $type => $column) {
00547                     switch($type) {
00548                         case 'add':
00549                             foreach($column as $field => $col) {
00550                                 $col['name'] = $field;
00551                                 $alter = 'ADD '.$this->buildColumn($col);
00552                                 if(isset($col['after'])) {
00553                                     $alter .= ' AFTER '. $this->name($col['after']);
00554                                 }
00555                                 $colList[] = $alter;
00556                             }
00557                         break;
00558                         case 'drop':
00559                             foreach($column as $field => $col) {
00560                                 $col['name'] = $field;
00561                                 $colList[] = 'DROP '.$this->name($field);
00562                             }
00563                         break;
00564                         case 'change':
00565                             foreach($column as $field => $col) {
00566                                 if(!isset($col['name'])) {
00567                                     $col['name'] = $field;
00568                                 }
00569                                 $colList[] = 'CHANGE '. $this->name($field).' '.$this->buildColumn($col);
00570                             }
00571                         break;
00572                     }
00573                 }
00574                 $out .= "\t" . join(",\n\t", $colList) . ";\n\n";
00575             }
00576         }
00577         return $out;
00578     }
00579 /**
00580  * Generate a MySQL Drop table for the given Schema object
00581  *
00582  * @param object $schema An instance of a subclass of CakeSchema
00583  * @param string $table Optional.  If specified only the table name given will be generated.
00584  *                      Otherwise, all tables defined in the schema are generated.
00585  * @return string
00586  */
00587     function dropSchema($schema, $table = null) {
00588         if (!is_a($schema, 'CakeSchema')) {
00589             trigger_error(__('Invalid schema object', true), E_USER_WARNING);
00590             return null;
00591         }
00592         $out = '';
00593         foreach ($schema->tables as $curTable => $columns) {
00594             if (!$table || $table == $curTable) {
00595                 $out .= 'DROP TABLE IF EXISTS ' . $this->fullTableName($curTable) . ";\n";
00596             }
00597         }
00598         return $out;
00599     }
00600 /**
00601  * Generate a MySQL-native column schema string
00602  *
00603  * @param array $column An array structured like the following: array('name'=>'value', 'type'=>'value'[, options]),
00604  *                      where options can be 'default', 'length', or 'key'.
00605  * @return string
00606  */
00607     function buildColumn($column) {
00608         $name = $type = null;
00609         $column = am(array('null' => true), $column);
00610         extract($column);
00611 
00612         if (empty($name) || empty($type)) {
00613             trigger_error('Column name or type not defined in schema', E_USER_WARNING);
00614             return null;
00615         }
00616 
00617         if (!isset($this->columns[$type])) {
00618             trigger_error("Column type {$type} does not exist", E_USER_WARNING);
00619             return null;
00620         }
00621 
00622         $real = $this->columns[$type];
00623         $out = $this->name($name) . ' ' . $real['name'];
00624 
00625         if (isset($real['limit']) || isset($real['length']) || isset($column['limit']) || isset($column['length'])) {
00626             if (isset($column['length'])) {
00627                 $length = $column['length'];
00628             } elseif (isset($column['limit'])) {
00629                 $length = $column['limit'];
00630             } elseif (isset($real['length'])) {
00631                 $length = $real['length'];
00632             } else {
00633                 $length = $real['limit'];
00634             }
00635             $out .= '(' . $length . ')';
00636         }
00637         if (isset($column['key']) && $column['key'] == 'primary' && (isset($column['extra']) && $column['extra'] == 'auto_increment')) {
00638             $out .= ' NOT NULL AUTO_INCREMENT';
00639         } elseif (isset($column['key']) && $column['key'] == 'primary') {
00640             $out .= ' NOT NULL';
00641         } elseif (isset($column['default']) && isset($column['null']) && $column['null'] == false) {
00642             $out .= ' DEFAULT ' . $this->value($column['default'], $type) . ' NOT NULL';
00643         } elseif (isset($column['default'])) {
00644             $out .= ' DEFAULT ' . $this->value($column['default'], $type);
00645         } elseif (isset($column['null']) && $column['null'] == true) {
00646             $out .= ' DEFAULT NULL';
00647         } elseif (isset($column['null']) && $column['null'] == false) {
00648             $out .= ' NOT NULL';
00649         }
00650 
00651         return $out;
00652     }
00653 /**
00654  * Format indexes for create table
00655  *
00656  * @param array $indexes
00657  * @return string
00658  */
00659     function buildIndex($indexes) {
00660         $join = array();
00661         foreach ($indexes as $name => $value) {
00662             $out = '';
00663             if ($name == 'PRIMARY') {
00664                 $out .= 'PRIMARY ';
00665                 $name = null;
00666             } else {
00667                 if (!empty($value['unique'])) {
00668                     $out .= 'UNIQUE ';
00669                 }
00670             }
00671             if (is_array($value['column'])) {
00672                 $out .= 'KEY '. $name .' (' . join(', ', array_map(array(&$this, 'name'), $value['column'])) . ')';
00673             } else {
00674                 $out .= 'KEY '. $name .' (' . $this->name($value['column']) . ')';
00675             }
00676             $join[] = $out;
00677         }
00678         return join(",\n\t", $join);
00679     }
00680 }
00681 ?>