| 1 | <?php |
| 2 | /* |
| 3 | * $Id: Pgsql.php 3967 2008-03-11 02:51:33Z jwage $ |
| 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.phpdoctrine.org>. |
| 20 | */ |
| 21 | Doctrine::autoload('Doctrine_Export'); |
| 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.phpdoctrine.org |
| 31 | * @since 1.0 |
| 32 | * @version $Revision: 3967 $ |
| 33 | */ |
| 34 | class 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['feferred'])) { |
| 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 ' . $name . ' ' . $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 ' . $name . ' ' . $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['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->datatype->getTypeDeclaration($field['definition']); |
| 156 | $sql[] = 'ALTER TABLE ' . $name . ' ' . $query; |
| 157 | } |
| 158 | if (array_key_exists('default', $field)) { |
| 159 | $query = 'ALTER ' . $fieldName . ' SET DEFAULT ' . $this->conn->quote($field['definition']['default'], $field['definition']['type']); |
| 160 | $sql[] = 'ALTER TABLE ' . $name . ' ' . $query; |
| 161 | } |
| 162 | if ( ! empty($field['notnull'])) { |
| 163 | $query = 'ALTER ' . $fieldName . ' ' . ($field['definition']['notnull'] ? 'SET' : 'DROP') . ' NOT NULL'; |
| 164 | $sql[] = 'ALTER TABLE ' . $name . ' ' . $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 ' . $name . ' 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 ' . $name . ' 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 $this->conn->exec('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 | $sql[] = $query; |
| 345 | |
| 346 | if (isset($options['indexes']) && ! empty($options['indexes'])) { |
| 347 | foreach($options['indexes'] as $index => $definition) { |
| 348 | $sql[] = $this->createIndexSql($name, $index, $definition); |
| 349 | } |
| 350 | } |
| 351 | |
| 352 | if (isset($options['foreignKeys'])) { |
| 353 | |
| 354 | foreach ((array) $options['foreignKeys'] as $k => $definition) { |
| 355 | if (is_array($definition)) { |
| 356 | $sql[] = $this->createForeignKeySql($name, $definition); |
| 357 | } |
| 358 | } |
| 359 | } |
| 360 | |
| 361 | return $sql; |
| 362 | } |
| 363 | |
| 364 | } |