00001 <?php
00002 /* SVN FILE: $Id: dbo__postgres_8php-source.html 675 2008-12-26 00:27:14Z gwoo $ */
00003 
00004 /**
00005  * PostgreSQL layer for DBO.
00006  *
00007  * Long description for file
00008  *
00009  * PHP versions 4 and 5
00010  *
00011  * CakePHP(tm) :  Rapid Development Framework <http://www.cakephp.org/>
00012  * Copyright 2005-2008, Cake Software Foundation, Inc.
00013  *                              1785 E. Sahara Avenue, Suite 490-204
00014  *                              Las Vegas, Nevada 89104
00015  *
00016  * Licensed under The MIT License
00017  * Redistributions of files must retain the above copyright notice.
00018  *
00019  * @filesource
00020  * @copyright       Copyright 2005-2008, Cake Software Foundation, Inc.
00021  * @link                http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project
00022  * @package         cake
00023  * @subpackage      cake.cake.libs.model.dbo
00024  * @since           CakePHP(tm) v 0.9.1.114
00025  * @version         $Revision: 675 $
00026  * @modifiedby      $LastChangedBy: gwoo $
00027  * @lastmodified    $Date: 2008-12-25 16:27:14 -0800 (Thu, 25 Dec 2008) $
00028  * @license         http://www.opensource.org/licenses/mit-license.php The MIT License
00029  */
00030 
00031 /**
00032  * PostgreSQL layer for DBO.
00033  *
00034  * Long description for class
00035  *
00036  * @package     cake
00037  * @subpackage  cake.cake.libs.model.dbo
00038  */
00039 class DboPostgres extends DboSource {
00040 
00041     var $description = "PostgreSQL DBO Driver";
00042 
00043     var $_baseConfig = array(
00044         'connect'   => 'pg_pconnect',
00045         'persistent' => true,
00046         'host' => 'localhost',
00047         'login' => 'root',
00048         'password' => '',
00049         'database' => 'cake',
00050         'schema' => 'public',
00051         'port' => 5432,
00052         'encoding' => ''
00053     );
00054 
00055     var $columns = array(
00056         'primary_key' => array('name' => 'serial NOT NULL'),
00057         'string' => array('name'  => 'varchar', 'limit' => '255'),
00058         'text' => array('name' => 'text'),
00059         'integer' => array('name' => 'integer', 'formatter' => 'intval'),
00060         'float' => array('name' => 'float', 'formatter' => 'floatval'),
00061         'datetime' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
00062         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
00063         'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
00064         'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
00065         'binary' => array('name' => 'bytea'),
00066         'boolean' => array('name' => 'boolean'),
00067         'number' => array('name' => 'numeric'),
00068         'inet' => array('name'  => 'inet')
00069     );
00070 
00071     var $startQuote = '"';
00072 
00073     var $endQuote = '"';
00074 /**
00075  * Contains mappings of custom auto-increment sequences, if a table uses a sequence name
00076  * other than what is dictated by convention.
00077  *
00078  * @var array
00079  */
00080     var $_sequenceMap = array();
00081 /**
00082  * Connects to the database using options in the given configuration array.
00083  *
00084  * @return True if successfully connected.
00085  */
00086     function connect() {
00087 
00088         $config = $this->config;
00089         $connect = $config['connect'];
00090         $this->connection = $connect("host='{$config['host']}' port='{$config['port']}' dbname='{$config['database']}' user='{$config['login']}' password='{$config['password']}'");
00091 
00092         if ($this->connection) {
00093             $this->connected = true;
00094             $this->_execute("SET search_path TO " . $config['schema']);
00095         } else {
00096             $this->connected = false;
00097         }
00098         if (!empty($config['encoding'])) {
00099             $this->setEncoding($config['encoding']);
00100         }
00101 
00102         return $this->connected;
00103     }
00104 
00105 /**
00106  * Disconnects from database.
00107  *
00108  * @return boolean True if the database could be disconnected, else false
00109  */
00110     function disconnect() {
00111         @pg_free_result($this->results);
00112         $this->connected = !@pg_close($this->connection);
00113         return !$this->connected;
00114     }
00115 
00116 /**
00117  * Executes given SQL statement.
00118  *
00119  * @param string $sql SQL statement
00120  * @return resource Result resource identifier
00121  */
00122     function _execute($sql) {
00123         return pg_query($this->connection, $sql);
00124     }
00125 /**
00126  * Returns an array of tables in the database. If there are no tables, an error is raised and the application exits.
00127  *
00128  * @return array Array of tablenames in the database
00129  */
00130     function listSources() {
00131         $cache = parent::listSources();
00132 
00133         if ($cache != null) {
00134             return $cache;
00135         }
00136 
00137         $schema = $this->config['schema'];
00138         $sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE table_schema = '{$schema}';";
00139         $result = $this->fetchAll($sql);
00140 
00141         if (!$result) {
00142             return array();
00143         } else {
00144             $tables = array();
00145 
00146             foreach ($result as $item) {
00147                 $tables[] = $item[0]['name'];
00148             }
00149 
00150             parent::listSources($tables);
00151             return $tables;
00152         }
00153     }
00154 
00155 /**
00156  * Returns an array of the fields in given table name.
00157  *
00158  * @param string $tableName Name of database table to inspect
00159  * @return array Fields in table. Keys are name and type
00160  */
00161     function &describe(&$model) {
00162         if (isset($model->sequence)) {
00163             $this->_sequenceMap[$this->fullTableName($model, false)] = $model->sequence;
00164         }
00165 
00166         $cache = parent::describe($model);
00167         if ($cache != null) {
00168             return $cache;
00169         }
00170 
00171         $fields = false;
00172         $cols = $this->fetchAll("SELECT DISTINCT column_name AS name, data_type AS type, is_nullable AS null, column_default AS default, ordinal_position AS position, character_maximum_length AS char_length, character_octet_length AS oct_length FROM information_schema.columns WHERE table_name =" . $this->value($model->tablePrefix . $model->table) . " ORDER BY position");
00173 
00174         foreach ($cols as $column) {
00175             $colKey = array_keys($column);
00176 
00177             if (isset($column[$colKey[0]]) && !isset($column[0])) {
00178                 $column[0] = $column[$colKey[0]];
00179             }
00180 
00181             if (isset($column[0])) {
00182                 $c = $column[0];
00183                 if (strpos($c['default'], 'nextval(') === 0) {
00184                     $c['default'] = null;
00185                 }
00186                 if (!empty($c['char_length'])) {
00187                     $length = intval($c['char_length']);
00188                 } elseif (!empty($c['oct_length'])) {
00189                     $length = intval($c['oct_length']);
00190                 } else {
00191                     $length = $this->length($c['type']);
00192                 }
00193                 $fields[$c['name']] = array(
00194                     'type'    => $this->column($c['type']),
00195                     'null'    => ($c['null'] == 'NO' ? false : true),
00196                     'default' => $c['default'],
00197                     'length'  => $length
00198                 );
00199             }
00200         }
00201         $this->__cacheDescription($model->tablePrefix . $model->table, $fields);
00202         return $fields;
00203     }
00204 /**
00205  * Returns a quoted and escaped string of $data for use in an SQL statement.
00206  *
00207  * @param string $data String to be prepared for use in an SQL statement
00208  * @param string $column The column into which this data will be inserted
00209  * @return string Quoted and escaped
00210  * @todo Add logic that formats/escapes data based on column type
00211  */
00212     function value($data, $column = null) {
00213 
00214         $parent = parent::value($data, $column);
00215         if ($parent != null) {
00216             return $parent;
00217         }
00218 
00219         if ($data === null) {
00220             return 'NULL';
00221         }
00222 
00223         switch($column) {
00224             case 'inet':
00225                 if (!strlen($data)) {
00226                     return 'DEFAULT';
00227                 } else {
00228                     $data = pg_escape_string($data);
00229                 }
00230             break;
00231             case 'integer':
00232                 if ($data === '') {
00233                     return 'DEFAULT';
00234                 } else {
00235                     $data = pg_escape_string($data);
00236                 }
00237             break;
00238             case 'binary':
00239                 $data = pg_escape_bytea($data);
00240 
00241             break;
00242             case 'boolean':
00243             default:
00244                 if ($data === true) {
00245                     return 'TRUE';
00246                 } elseif ($data === false) {
00247                     return 'FALSE';
00248                 }
00249                 $data = pg_escape_string($data);
00250             break;
00251         }
00252         return "'" . $data . "'";
00253     }
00254 
00255 /**
00256  * Begin a transaction
00257  *
00258  * @param unknown_type $model
00259  * @return boolean True on success, false on fail
00260  * (i.e. if the database/model does not support transactions).
00261  */
00262     function begin(&$model) {
00263         if (parent::begin($model)) {
00264             if ($this->execute('BEGIN')) {
00265                 $this->_transactionStarted = true;
00266                 return true;
00267             }
00268         }
00269         return false;
00270     }
00271 
00272 /**
00273  * Commit a transaction
00274  *
00275  * @param unknown_type $model
00276  * @return boolean True on success, false on fail
00277  * (i.e. if the database/model does not support transactions,
00278  * or a transaction has not started).
00279  */
00280     function commit(&$model) {
00281         if (parent::commit($model)) {
00282             $this->_transactionStarted = false;
00283             return $this->execute('COMMIT');
00284         }
00285         return false;
00286     }
00287 
00288 /**
00289  * Rollback a transaction
00290  *
00291  * @param unknown_type $model
00292  * @return boolean True on success, false on fail
00293  * (i.e. if the database/model does not support transactions,
00294  * or a transaction has not started).
00295  */
00296     function rollback(&$model) {
00297         if (parent::rollback($model)) {
00298             return $this->execute('ROLLBACK');
00299         }
00300         return false;
00301     }
00302 
00303 /**
00304  * Returns a formatted error message from previous database operation.
00305  *
00306  * @return string Error message
00307  */
00308     function lastError() {
00309         $last_error = pg_last_error($this->connection);
00310         if ($last_error) {
00311             return $last_error;
00312         }
00313         return null;
00314     }
00315 
00316 /**
00317  * Returns number of affected rows in previous database operation. If no previous operation exists, this returns false.
00318  *
00319  * @return int Number of affected rows
00320  */
00321     function lastAffected() {
00322         if ($this->_result) {
00323             $return = pg_affected_rows($this->_result);
00324             return $return;
00325         }
00326         return false;
00327     }
00328 /**
00329  * Returns number of rows in previous resultset. If no previous resultset exists,
00330  * this returns false.
00331  *
00332  * @return int Number of rows in resultset
00333  */
00334     function lastNumRows() {
00335         if ($this->_result) {
00336             $return = pg_num_rows($this->_result);
00337             return $return;
00338         }
00339         return false;
00340     }
00341 /**
00342  * Returns the ID generated from the previous INSERT operation.
00343  *
00344  * @param string $source Name of the database table
00345  * @param string $field Name of the ID database field. Defaults to "id"
00346  * @return int
00347  */
00348     function lastInsertId($source, $field = 'id') {
00349         foreach ($this->__descriptions[$source] as $name => $sourceinfo) {
00350             if (strcasecmp($name, $field) == 0) {
00351                 break;
00352             }
00353         }
00354 
00355         if (isset($this->_sequenceMap[$source])) {
00356             $seq = $this->_sequenceMap[$source];
00357         } elseif (preg_match('/^nextval\(\'(\w+)\'/', $sourceinfo['default'], $matches)) {
00358             $seq = $matches[1];
00359         } else {
00360             $seq = "{$source}_{$field}_seq";
00361         }
00362 
00363         $res = $this->rawQuery("SELECT last_value AS max FROM \"{$seq}\"");
00364         $data = $this->fetchRow($res);
00365         return $data[0]['max'];
00366     }
00367 /**
00368  * Generates the fields list of an SQL query.
00369  *
00370  * @param Model $model
00371  * @param string $alias Alias tablename
00372  * @param mixed $fields
00373  * @return array
00374  */
00375     function fields(&$model, $alias = null, $fields = array(), $quote = true) {
00376         if (empty($alias)) {
00377             $alias = $model->name;
00378         }
00379         $fields = parent::fields($model, $alias, $fields, false);
00380 
00381         if (!$quote) {
00382             return $fields;
00383         }
00384         $count = count($fields);
00385 
00386         if ($count >= 1 && $fields[0] != '*' && strpos($fields[0], 'COUNT(*)') === false) {
00387             for ($i = 0; $i < $count; $i++) {
00388                 if (!preg_match('/^.+\\(.*\\)/', $fields[$i]) && !preg_match('/\s+AS\s+/', $fields[$i])) {
00389                     $prepend = '';
00390                     if (strpos($fields[$i], 'DISTINCT') !== false) {
00391                         $prepend = 'DISTINCT ';
00392                         $fields[$i] = trim(r('DISTINCT', '', $fields[$i]));
00393                     }
00394 
00395                     $dot = strrpos($fields[$i], '.');
00396                     if ($dot === false) {
00397                         $fields[$i] = $prepend . $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fields[$i]);
00398                     } else {
00399                         $build = explode('.', $fields[$i]);
00400                         $fields[$i] = $prepend . $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $build[1]);
00401                     }
00402                 }
00403             }
00404         }
00405         return $fields;
00406     }
00407 /**
00408  * Returns a limit statement in the correct format for the particular database.
00409  *
00410  * @param int $limit Limit of results returned
00411  * @param int $offset Offset from which to start results
00412  * @return string SQL limit/offset statement
00413  */
00414     function limit($limit, $offset = null) {
00415         if ($limit) {
00416             $rt = '';
00417             if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
00418                 $rt = ' LIMIT';
00419             }
00420 
00421             $rt .= ' ' . $limit;
00422             if ($offset) {
00423                 $rt .= ' OFFSET ' . $offset;
00424             }
00425 
00426             return $rt;
00427         }
00428         return null;
00429     }
00430 /**
00431  * Converts database-layer column types to basic types
00432  *
00433  * @param string $real Real database-layer column type (i.e. "varchar(255)")
00434  * @return string Abstract column type (i.e. "string")
00435  */
00436     function column($real) {
00437         if (is_array($real)) {
00438             $col = $real['name'];
00439             if (isset($real['limit'])) {
00440                 $col .= '(' . $real['limit'] . ')';
00441             }
00442             return $col;
00443         }
00444 
00445         $col = r(')', '', $real);
00446         $limit = null;
00447         @list($col, $limit) = explode('(', $col);
00448 
00449         if (in_array($col, array('date', 'time'))) {
00450             return $col;
00451         }
00452         if (strpos($col, 'timestamp') !== false) {
00453             return 'datetime';
00454         }
00455         if ($col == 'inet') {
00456             return('inet');
00457         }
00458         if ($col == 'boolean') {
00459             return 'boolean';
00460         }
00461         if (strpos($col, 'int') !== false && $col != 'interval') {
00462             return 'integer';
00463         }
00464         if (strpos($col, 'char') !== false) {
00465             return 'string';
00466         }
00467         if (strpos($col, 'text') !== false) {
00468             return 'text';
00469         }
00470         if (strpos($col, 'bytea') !== false) {
00471             return 'binary';
00472         }
00473         if (in_array($col, array('float', 'float4', 'float8', 'double', 'double precision', 'decimal', 'real', 'numeric'))) {
00474             return 'float';
00475         }
00476         return 'text';
00477     }
00478 /**
00479  * Gets the length of a database-native column description, or null if no length
00480  *
00481  * @param string $real Real database-layer column type (i.e. "varchar(255)")
00482  * @return int An integer representing the length of the column
00483  */
00484     function length($real) {
00485         $col = r(array(')', 'unsigned'), '', $real);
00486         $limit = null;
00487 
00488         if (strpos($col, '(') !== false) {
00489             list($col, $limit) = explode('(', $col);
00490         }
00491 
00492         if ($limit != null) {
00493             return intval($limit);
00494         }
00495         return null;
00496     }
00497 /**
00498  * Enter description here...
00499  *
00500  * @param unknown_type $results
00501  */
00502     function resultSet(&$results) {
00503         $this->results =& $results;
00504         $this->map = array();
00505         $num_fields = pg_num_fields($results);
00506         $index = 0;
00507         $j = 0;
00508 
00509         while ($j < $num_fields) {
00510             $columnName = pg_field_name($results, $j);
00511 
00512             if (strpos($columnName, '__')) {
00513                 $parts = explode('__', $columnName);
00514                 $this->map[$index++] = array($parts[0], $parts[1]);
00515             } else {
00516                 $this->map[$index++] = array(0, $columnName);
00517             }
00518             $j++;
00519         }
00520     }
00521 /**
00522  * Fetches the next row from the current result set
00523  *
00524  * @return unknown
00525  */
00526     function fetchResult() {
00527         if ($row = pg_fetch_row($this->results)) {
00528             $resultRow = array();
00529             $i = 0;
00530 
00531             foreach ($row as $index => $field) {
00532                 list($table, $column) = $this->map[$index];
00533                 $resultRow[$table][$column] = $row[$index];
00534                 $i++;
00535             }
00536             return $resultRow;
00537         } else {
00538             return false;
00539         }
00540     }
00541 /**
00542  * Translates between PHP boolean values and PostgreSQL boolean values
00543  *
00544  * @param mixed $data Value to be translated
00545  * @param boolean $quote    True to quote value, false otherwise
00546  * @return mixed Converted boolean value
00547  */
00548     function boolean($data, $quote = true) {
00549         $result = null;
00550 
00551         if ($data === true || $data === false) {
00552             $result = $data;
00553         } elseif (is_string($data) && !is_numeric($data)) {
00554             if (strpos(low($data), 't') !== false) {
00555                 $result = true;
00556             } else {
00557                 $result = false;
00558             }
00559         } else {
00560             $result = (bool)$data;
00561         }
00562         return $result;
00563     }
00564 /**
00565  * Sets the database encoding
00566  *
00567  * @param mixed $enc Database encoding
00568  * @return boolean True on success, false on failure
00569  */
00570     function setEncoding($enc) {
00571         return pg_set_client_encoding($this->connection, $enc) == 0;
00572     }
00573 /**
00574  * Gets the database encoding
00575  *
00576  * @return string The database encoding
00577  */
00578     function getEncoding() {
00579         return pg_client_encoding($this->connection);
00580     }
00581 /**
00582  * Inserts multiple values into a join table
00583  *
00584  * @param string $table
00585  * @param string $fields
00586  * @param array $values
00587  */
00588     function insertMulti($table, $fields, $values) {
00589         $count = count($values);
00590         for ($x = 0; $x < $count; $x++) {
00591             $this->query("INSERT INTO {$table} ({$fields}) VALUES {$values[$x]}");
00592         }
00593     }
00594 }
00595 ?>