1<?php 2/* 3 * $Id: Pgsql.php 7680 2010-08-19 14:08:28Z lsmith $ 4 * 5 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 6 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 7 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 8 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 9 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 10 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 11 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 12 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 13 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 14 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 15 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 16 * 17 * This software consists of voluntary contributions made by many individuals 18 * and is licensed under the LGPL. For more information, see 19 * <http://www.doctrine-project.org>. 20 */ 21 22/** 23 * Doctrine_Export_Pgsql 24 * 25 * @package Doctrine 26 * @subpackage Export 27 * @author Konsta Vesterinen <kvesteri@cc.hut.fi> 28 * @author Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library) 29 * @license http://www.opensource.org/licenses/lgpl-license.php LGPL 30 * @link www.doctrine-project.org 31 * @since 1.0 32 * @version $Revision: 7680 $ 33 */ 34class Doctrine_Export_Pgsql extends Doctrine_Export 35{ 36 public $tmpConnectionDatabase = 'postgres'; 37 38 /** 39 * createDatabaseSql 40 * 41 * @param string $name 42 * @return void 43 */ 44 public function createDatabaseSql($name) 45 { 46 $query = 'CREATE DATABASE ' . $this->conn->quoteIdentifier($name); 47 48 return $query; 49 } 50 51 /** 52 * drop an existing database 53 * 54 * @param string $name name of the database that should be dropped 55 * @throws PDOException 56 * @access public 57 */ 58 public function dropDatabaseSql($name) 59 { 60 $query = 'DROP DATABASE ' . $this->conn->quoteIdentifier($name); 61 62 return $query; 63 } 64 65 /** 66 * getAdvancedForeignKeyOptions 67 * Return the FOREIGN KEY query section dealing with non-standard options 68 * as MATCH, INITIALLY DEFERRED, ON UPDATE, ... 69 * 70 * @param array $definition foreign key definition 71 * @return string 72 * @access protected 73 */ 74 public function getAdvancedForeignKeyOptions(array $definition) 75 { 76 $query = ''; 77 if (isset($definition['match'])) { 78 $query .= ' MATCH ' . $definition['match']; 79 } 80 if (isset($definition['onUpdate'])) { 81 $query .= ' ON UPDATE ' . $definition['onUpdate']; 82 } 83 if (isset($definition['onDelete'])) { 84 $query .= ' ON DELETE ' . $definition['onDelete']; 85 } 86 if (isset($definition['deferrable'])) { 87 $query .= ' DEFERRABLE'; 88 } else { 89 $query .= ' NOT DEFERRABLE'; 90 } 91 if (isset($definition['deferred'])) { 92 $query .= ' INITIALLY DEFERRED'; 93 } else { 94 $query .= ' INITIALLY IMMEDIATE'; 95 } 96 return $query; 97 } 98 99 /** 100 * generates the sql for altering an existing table on postgresql 101 * 102 * @param string $name name of the table that is intended to be changed. 103 * @param array $changes associative array that contains the details of each type * 104 * @param boolean $check indicates whether the function should just check if the DBMS driver 105 * can perform the requested table alterations if the value is true or 106 * actually perform them otherwise. 107 * @see Doctrine_Export::alterTable() 108 * @return array 109 */ 110 public function alterTableSql($name, array $changes, $check = false) 111 { 112 foreach ($changes as $changeName => $change) { 113 switch ($changeName) { 114 case 'add': 115 case 'remove': 116 case 'change': 117 case 'name': 118 case 'rename': 119 break; 120 default: 121 throw new Doctrine_Export_Exception('change type "' . $changeName . '\" not yet supported'); 122 } 123 } 124 125 if ($check) { 126 return true; 127 } 128 129 $sql = array(); 130 131 if (isset($changes['add']) && is_array($changes['add'])) { 132 foreach ($changes['add'] as $fieldName => $field) { 133 $query = 'ADD ' . $this->getDeclaration($fieldName, $field); 134 $sql[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($name, true) . ' ' . $query; 135 } 136 } 137 138 if (isset($changes['remove']) && is_array($changes['remove'])) { 139 foreach ($changes['remove'] as $fieldName => $field) { 140 $fieldName = $this->conn->quoteIdentifier($fieldName, true); 141 $query = 'DROP ' . $fieldName; 142 $sql[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($name, true) . ' ' . $query; 143 } 144 } 145 146 if (isset($changes['change']) && is_array($changes['change'])) { 147 foreach ($changes['change'] as $fieldName => $field) { 148 $fieldName = $this->conn->quoteIdentifier($fieldName, true); 149 if (isset($field['definition']['type'])) { 150 $serverInfo = $this->conn->getServerVersion(); 151 152 if (is_array($serverInfo) && $serverInfo['major'] < 8) { 153 throw new Doctrine_Export_Exception('changing column type for "'.$field['type'].'\" requires PostgreSQL 8.0 or above'); 154 } 155 $query = 'ALTER ' . $fieldName . ' TYPE ' . $this->conn->dataDict->getNativeDeclaration($field['definition']); 156 $sql[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($name, true) . ' ' . $query; 157 } 158 if (array_key_exists('default', $field['definition'])) { 159 $query = 'ALTER ' . $fieldName . ' SET DEFAULT ' . $this->conn->quote($field['definition']['default'], $field['definition']['type']); 160 $sql[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($name, true) . ' ' . $query; 161 } 162 if ( isset($field['definition']['notnull'])) { 163 $query = 'ALTER ' . $fieldName . ' ' . ($field['definition']['notnull'] ? 'SET' : 'DROP') . ' NOT NULL'; 164 $sql[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($name, true) . ' ' . $query; 165 } 166 } 167 } 168 169 if (isset($changes['rename']) && is_array($changes['rename'])) { 170 foreach ($changes['rename'] as $fieldName => $field) { 171 $fieldName = $this->conn->quoteIdentifier($fieldName, true); 172 $sql[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($name, true) . ' RENAME COLUMN ' . $fieldName . ' TO ' . $this->conn->quoteIdentifier($field['name'], true); 173 } 174 } 175 176 $name = $this->conn->quoteIdentifier($name, true); 177 if (isset($changes['name'])) { 178 $changeName = $this->conn->quoteIdentifier($changes['name'], true); 179 $sql[] = 'ALTER TABLE ' . $this->conn->quoteIdentifier($name, true) . ' RENAME TO ' . $changeName; 180 } 181 182 return $sql; 183 } 184 185 /** 186 * alter an existing table 187 * 188 * @param string $name name of the table that is intended to be changed. 189 * @param array $changes associative array that contains the details of each type 190 * of change that is intended to be performed. The types of 191 * changes that are currently supported are defined as follows: 192 * 193 * name 194 * 195 * New name for the table. 196 * 197 * add 198 * 199 * Associative array with the names of fields to be added as 200 * indexes of the array. The value of each entry of the array 201 * should be set to another associative array with the properties 202 * of the fields to be added. The properties of the fields should 203 * be the same as defined by the Metabase parser. 204 * 205 * 206 * remove 207 * 208 * Associative array with the names of fields to be removed as indexes 209 * of the array. Currently the values assigned to each entry are ignored. 210 * An empty array should be used for future compatibility. 211 * 212 * rename 213 * 214 * Associative array with the names of fields to be renamed as indexes 215 * of the array. The value of each entry of the array should be set to 216 * another associative array with the entry named name with the new 217 * field name and the entry named Declaration that is expected to contain 218 * the portion of the field declaration already in DBMS specific SQL code 219 * as it is used in the CREATE TABLE statement. 220 * 221 * change 222 * 223 * Associative array with the names of the fields to be changed as indexes 224 * of the array. Keep in mind that if it is intended to change either the 225 * name of a field and any other properties, the change array entries 226 * should have the new names of the fields as array indexes. 227 * 228 * The value of each entry of the array should be set to another associative 229 * array with the properties of the fields to that are meant to be changed as 230 * array entries. These entries should be assigned to the new values of the 231 * respective properties. The properties of the fields should be the same 232 * as defined by the Metabase parser. 233 * 234 * Example 235 * array( 236 * 'name' => 'userlist', 237 * 'add' => array( 238 * 'quota' => array( 239 * 'type' => 'integer', 240 * 'unsigned' => 1 241 * ) 242 * ), 243 * 'remove' => array( 244 * 'file_limit' => array(), 245 * 'time_limit' => array() 246 * ), 247 * 'change' => array( 248 * 'name' => array( 249 * 'length' => '20', 250 * 'definition' => array( 251 * 'type' => 'text', 252 * 'length' => 20, 253 * ), 254 * ) 255 * ), 256 * 'rename' => array( 257 * 'sex' => array( 258 * 'name' => 'gender', 259 * 'definition' => array( 260 * 'type' => 'text', 261 * 'length' => 1, 262 * 'default' => 'M', 263 * ), 264 * ) 265 * ) 266 * ) 267 * 268 * @param boolean $check indicates whether the function should just check if the DBMS driver 269 * can perform the requested table alterations if the value is true or 270 * actually perform them otherwise. 271 * @throws Doctrine_Connection_Exception 272 * @return boolean 273 */ 274 public function alterTable($name, array $changes, $check = false) 275 { 276 $sql = $this->alterTableSql($name, $changes, $check); 277 foreach ($sql as $query) { 278 $this->conn->exec($query); 279 } 280 return true; 281 } 282 283 /** 284 * return RDBMS specific create sequence statement 285 * 286 * @throws Doctrine_Connection_Exception if something fails at database level 287 * @param string $seqName name of the sequence to be created 288 * @param string $start start value of the sequence; default is 1 289 * @param array $options An associative array of table options: 290 * array( 291 * 'comment' => 'Foo', 292 * 'charset' => 'utf8', 293 * 'collate' => 'utf8_unicode_ci', 294 * ); 295 * @return string 296 */ 297 public function createSequenceSql($sequenceName, $start = 1, array $options = array()) 298 { 299 $sequenceName = $this->conn->quoteIdentifier($this->conn->formatter->getSequenceName($sequenceName), true); 300 return 'CREATE SEQUENCE ' . $sequenceName . ' INCREMENT 1' . 301 ($start < 1 ? ' MINVALUE ' . $start : '') . ' START ' . $start; 302 } 303 304 /** 305 * drop existing sequence 306 * 307 * @param string $sequenceName name of the sequence to be dropped 308 */ 309 public function dropSequenceSql($sequenceName) 310 { 311 $sequenceName = $this->conn->quoteIdentifier($this->conn->formatter->getSequenceName($sequenceName), true); 312 return 'DROP SEQUENCE ' . $sequenceName; 313 } 314 315 /** 316 * Creates a table. 317 * 318 * @param unknown_type $name 319 * @param array $fields 320 * @param array $options 321 * @return unknown 322 */ 323 public function createTableSql($name, array $fields, array $options = array()) 324 { 325 if ( ! $name) { 326 throw new Doctrine_Export_Exception('no valid table name specified'); 327 } 328 329 if (empty($fields)) { 330 throw new Doctrine_Export_Exception('no fields specified for table ' . $name); 331 } 332 333 $queryFields = $this->getFieldDeclarationList($fields); 334 335 336 if (isset($options['primary']) && ! empty($options['primary'])) { 337 $keyColumns = array_values($options['primary']); 338 $keyColumns = array_map(array($this->conn, 'quoteIdentifier'), $keyColumns); 339 $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')'; 340 } 341 342 $query = 'CREATE TABLE ' . $this->conn->quoteIdentifier($name, true) . ' (' . $queryFields; 343 344 if ($check = $this->getCheckDeclaration($fields)) { 345 $query .= ', ' . $check; 346 } 347 348 if (isset($options['checks']) && $check = $this->getCheckDeclaration($options['checks'])) { 349 $query .= ', ' . $check; 350 } 351 352 $query .= ')'; 353 354 $sql[] = $query; 355 356 if (isset($options['indexes']) && ! empty($options['indexes'])) { 357 foreach($options['indexes'] as $index => $definition) { 358 $sql[] = $this->createIndexSql($name, $index, $definition); 359 } 360 } 361 362 if (isset($options['foreignKeys'])) { 363 364 foreach ((array) $options['foreignKeys'] as $k => $definition) { 365 if (is_array($definition)) { 366 $sql[] = $this->createForeignKeySql($name, $definition); 367 } 368 } 369 } 370 if (isset($options['sequenceName'])) { 371 $sql[] = $this->createSequenceSql($options['sequenceName']); 372 } 373 return $sql; 374 } 375 376 /** 377 * Get the stucture of a field into an array. 378 * 379 * @param string $table name of the table on which the index is to be created 380 * @param string $name name of the index to be created 381 * @param array $definition associative array that defines properties of the index to be created. 382 * @see Doctrine_Export::createIndex() 383 * @return string 384 */ 385 public function createIndexSql($table, $name, array $definition) 386 { 387 $query = parent::createIndexSql($table, $name, $definition); 388 if (isset($definition['where'])) { 389 return $query . ' WHERE ' . $definition['where']; 390 } 391 return $query; 392 } 393}