1<?php 2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */ 3require_once('./Services/Database/classes/PDO/Manager/class.ilDBPdoManagerPostgres.php'); 4require_once('class.ilDBPdo.php'); 5require_once('./Services/Database/classes/PDO/FieldDefinition/class.ilDBPdoPostgresFieldDefinition.php'); 6require_once('./Services/Database/classes/PDO/Reverse/class.ilDBPdoReversePostgres.php'); 7 8/** 9 * Class ilDBPdoPostgreSQL 10 * 11 * @author Fabian Schmid <fs@studer-raimann.ch> 12 */ 13class ilDBPdoPostgreSQL extends ilDBPdo implements ilDBInterface 14{ 15 const POSTGRE_STD_PORT = 5432; 16 /** 17 * @var int 18 */ 19 protected $port = self::POSTGRE_STD_PORT; 20 /** 21 * @var string 22 */ 23 protected $storage_engine = null; 24 /** 25 * @var ilDBPdoManagerPostgres 26 */ 27 protected $manager; 28 29 30 public function generateDSN() 31 { 32 if (!$this->getPort()) { 33 $this->setPort(self::POSTGRE_STD_PORT); 34 } 35 $this->dsn = 'pgsql:host=' . $this->getHost() . ';port=' . $this->getPort() . ';dbname=' . $this->getDbname() . ';user=' 36 . $this->getUsername() . ';password=' . $this->getPassword() . ''; 37 } 38 39 40 /** 41 * @param bool $return_false_for_error 42 * @return bool 43 * @throws \Exception 44 */ 45 public function connect($return_false_for_error = false) 46 { 47 $this->generateDSN(); 48 try { 49 $this->pdo = new PDO($this->getDSN(), $this->getUsername(), $this->getPassword(), $this->getAttributes()); 50 $this->initHelpers(); 51 } catch (Exception $e) { 52 $this->error_code = $e->getCode(); 53 if ($return_false_for_error) { 54 return false; 55 } 56 throw $e; 57 } 58 59 return ($this->pdo->errorCode() == PDO::ERR_NONE); 60 } 61 62 63 protected function getAdditionalAttributes() 64 { 65 return array( 66 PDO::ATTR_EMULATE_PREPARES => true, 67 PDO::ATTR_STRINGIFY_FETCHES => true, 68 ); 69 } 70 71 72 public function initHelpers() 73 { 74 $this->manager = new ilDBPdoManagerPostgres($this->pdo, $this); 75 $this->reverse = new ilDBPdoReversePostgres($this->pdo, $this); 76 $this->field_definition = new ilDBPdoPostgresFieldDefinition($this); 77 } 78 79 80 /** 81 * Primary key identifier 82 */ 83 public function getPrimaryKeyIdentifier() 84 { 85 return "pk"; 86 } 87 88 89 /** 90 * @return bool 91 */ 92 public function supportsFulltext() 93 { 94 return false; 95 } 96 97 98 /** 99 * @return bool 100 */ 101 public function supportsTransactions() 102 { 103 return true; 104 } 105 106 107 /** 108 * @param $a_table 109 * @param $a_constraint 110 * @return string 111 */ 112 public function constraintName($a_table, $a_constraint) 113 { 114 $a_constraint = str_replace($a_table . '_', '', $a_constraint); 115 116 return $a_table . '_' . $a_constraint; 117 } 118 119 120 /** 121 * @param $index_name_base 122 * @return string 123 */ 124 public function getIndexName($index_name_base) 125 { 126 return parent::getIndexName($index_name_base); // TODO: Change the autogenerated stub 127 } 128 129 130 /** 131 * @param $a_table 132 * @param $a_pk_columns 133 * @param $a_other_columns 134 * @return bool 135 * @throws \ilDatabaseException 136 */ 137 public function replace($a_table, $a_pk_columns, $a_other_columns) 138 { 139 $a_columns = array_merge($a_pk_columns, $a_other_columns); 140 $fields = array(); 141 $field_values = array(); 142 $placeholders = array(); 143 $types = array(); 144 $values = array(); 145 $lobs = false; 146 $lob = array(); 147 $val_field = array(); 148 $a = array(); 149 $b = array(); 150 foreach ($a_columns as $k => $col) { 151 if ($col[0] == 'clob' or $col[0] == 'blob') { 152 $val_field[] = $this->quote($col[1], 'text') . " " . $k; 153 } else { 154 $val_field[] = $this->quote($col[1], $col[0]) . " " . $k; 155 } 156 $fields[] = $k; 157 $placeholders[] = "%s"; 158 $placeholders2[] = ":$k"; 159 $types[] = $col[0]; 160 $values[] = $col[1]; 161 $field_values[$k] = $col[1]; 162 if ($col[0] == "blob" || $col[0] == "clob") { 163 $lobs = true; 164 $lob[$k] = $k; 165 } 166 $a[] = "a." . $k; 167 $b[] = "b." . $k; 168 } 169 $abpk = array(); 170 $aboc = array(); 171 $delwhere = array(); 172 foreach ($a_pk_columns as $k => $col) { 173 $abpk[] = "a." . $k . " = b." . $k; 174 $delwhere[] = $k . " = " . $this->quote($col[1], $col[0]); 175 } 176 foreach ($a_other_columns as $k => $col) { 177 $aboc[] = "a." . $k . " = b." . $k; 178 } 179 // if ($lobs) // lobs -> use prepare execute (autoexecute broken in PEAR 2.4.1) 180 // { 181 $this->manipulate("DELETE FROM " . $a_table . " WHERE " . implode(" AND ", $delwhere)); 182 $this->insert($a_table, $a_columns); 183 184 return true; 185 } 186 187 188 /** 189 * @param array $a_tables 190 * @deprecated Use ilAtomQuery instead 191 * @return bool 192 */ 193 public function lockTables($a_tables) 194 { 195 $locks = array(); 196 197 $counter = 0; 198 foreach ($a_tables as $table) { 199 if (!isset($table['sequence']) && $table['sequence']) { 200 $lock = 'LOCK TABLE ' . $table['name']; 201 202 switch ($table['type']) { 203 case ilDBConstants::LOCK_READ: 204 $lock .= ' IN SHARE MODE '; 205 break; 206 207 case ilDBConstants::LOCK_WRITE: 208 $lock .= ' IN EXCLUSIVE MODE '; 209 break; 210 } 211 212 $locks[] = $lock; 213 } 214 } 215 216 // @TODO use and store a unique identifier to allow nested lock/unlocks 217 $this->beginTransaction(); 218 foreach ($locks as $lock) { 219 $this->query($lock); 220 } 221 222 return true; 223 } 224 225 226 /** 227 * @throws \ilDatabaseException 228 * @deprecated Use ilAtomQuery instead 229 */ 230 public function unlockTables() 231 { 232 $this->commit(); 233 } 234 235 236 public function getStorageEngine() 237 { 238 return null; 239 } 240 241 242 public function setStorageEngine($storage_engine) 243 { 244 return false; 245 } 246 247 // 248 // 249 // 250 251 /** 252 * @param string $table_name 253 * @return mixed 254 * @throws \ilDatabaseException 255 */ 256 public function nextId($table_name) 257 { 258 $sequence_name = $table_name . '_seq'; 259 $query = "SELECT NEXTVAL('$sequence_name')"; 260 $result = $this->query($query, 'integer'); 261 $data = $result->fetchObject(); 262 263 return $data->nextval; 264 } 265 266 267 /** 268 * @param $table_name 269 * @param bool $error_if_not_existing 270 * @return int 271 */ 272 public function dropTable($table_name, $error_if_not_existing = false) 273 { 274 try { 275 $this->pdo->exec("DROP TABLE $table_name"); 276 } catch (PDOException $PDOException) { 277 if ($error_if_not_existing) { 278 throw $PDOException; 279 } 280 281 return false; 282 } 283 284 return true; 285 } 286 287 288 /** 289 * @param $identifier 290 * @param bool $check_option 291 * @return mixed 292 */ 293 public function quoteIdentifier($identifier, $check_option = false) 294 { 295 return '"' . $identifier . '"'; 296 } 297 298 299 /** 300 * @param string $table_name 301 * @return bool 302 */ 303 public function tableExists($table_name) 304 { 305 $tables = $this->listTables(); 306 307 if (is_array($tables)) { 308 if (in_array($table_name, $tables)) { 309 return true; 310 } 311 } 312 313 return false; 314 } 315 316 317 /** 318 * @param $query 319 * @return string 320 */ 321 protected function appendLimit($query) 322 { 323 if ($this->limit !== null && $this->offset !== null) { 324 $query .= ' LIMIT ' . (int) $this->limit . ' OFFSET ' . (int) $this->offset; 325 $this->limit = null; 326 $this->offset = null; 327 328 return $query; 329 } 330 331 return $query; 332 } 333 334 335 /** 336 * @param $table_name string 337 * @param $column_name string 338 * 339 * @return bool 340 */ 341 public function tableColumnExists($table_name, $column_name) 342 { 343 return in_array($column_name, $this->manager->listTableFields($table_name)); 344 } 345 346 347 /** 348 * @param $a_name 349 * @param $a_new_name 350 * @return bool 351 * @throws \ilDatabaseException 352 */ 353 public function renameTable($a_name, $a_new_name) 354 { 355 // check table name 356 try { 357 $this->checkTableName($a_new_name); 358 } catch (ilDatabaseException $e) { 359 return true; 360 } 361 362 if ($this->tableExists($a_new_name)) { 363 return true; 364 } 365 try { 366 $this->manager->alterTable($a_name, array( "name" => $a_new_name ), false); 367 } catch (Exception $e) { 368 return true; 369 } 370 371 return true; 372 } 373 374 375 /** 376 * @param $table_name 377 * @param int $start 378 * @return bool 379 */ 380 public function createSequence($table_name, $start = 1) 381 { 382 if (in_array($table_name, $this->manager->listSequences())) { 383 return true; 384 } 385 try { 386 parent::createSequence($table_name, $start); // TODO: Change the autogenerated stub 387 } catch (Exception $e) { 388 return true; 389 } 390 } 391 392 393 /** 394 * @param $table_name 395 * @param $fields 396 * @param bool $drop_table 397 * @param bool $ignore_erros 398 * @return bool|mixed 399 * @throws \ilDatabaseException 400 */ 401 public function createTable($table_name, $fields, $drop_table = false, $ignore_erros = false) 402 { 403 if ($this->tableExists($table_name)) { 404 return true; 405 } 406 try { 407 return parent::createTable($table_name, $fields, $drop_table, $ignore_erros); // TODO: Change the autogenerated stub 408 } catch (Exception $e) { 409 return true; 410 } 411 } 412 413 414 /** 415 * @param string $table_name 416 * @param array $primary_keys 417 * @return bool 418 */ 419 public function addPrimaryKey($table_name, $primary_keys) 420 { 421 require_once('./Services/Database/classes/class.ilDBAnalyzer.php'); 422 $ilDBAnalyzer = new ilDBAnalyzer($this); 423 if ($ilDBAnalyzer->getPrimaryKeyInformation($table_name)) { 424 return true; 425 } 426 try { 427 return parent::addPrimaryKey($table_name, $primary_keys); // TODO: Change the autogenerated stub 428 } catch (Exception $e) { 429 return true; 430 } 431 } 432 433 434 public function addIndex($table_name, $fields, $index_name = '', $fulltext = false) 435 { 436 $indices = $this->manager->listTableIndexes($table_name); 437 if (in_array($this->constraintName($table_name, $index_name), $indices)) { 438 return true; 439 } 440 try { 441 return parent::addIndex($table_name, $fields, $index_name, $fulltext); // TODO: Change the autogenerated stub 442 } catch (Exception $e) { 443 return true; 444 } 445 } 446 447 448 public function addUniqueConstraint($table, $fields, $name = "con") 449 { 450 try { 451 return parent::addUniqueConstraint($table, $fields, $name); // TODO: Change the autogenerated stub 452 } catch (Exception $e) { 453 return true; 454 } 455 } 456 457 /** 458 * @param $table_name 459 * @return bool 460 */ 461 public function dropPrimaryKey($table_name) 462 { 463 return $this->manager->dropConstraint($table_name, "pk", true); 464 } 465} 466