git.maemo.org Git - speedfreak/blob - Server/system/libraries/drivers/Database/Pgsql.php

   1 <?php defined('SYSPATH') OR die('No direct access allowed.');
   2 /**
   3  * PostgreSQL 8.1+ Database Driver
   4  *
   5  * $Id: Pgsql.php 4344 2009-05-11 16:41:39Z zombor $
   6  *
   7  * @package    Core
   8  * @author     Kohana Team
   9  * @copyright  (c) 2007-2008 Kohana Team
  10  * @license    http://kohanaphp.com/license.html
  11  */
  12 class Database_Pgsql_Driver extends Database_Driver {
  13
  14         // Database connection link
  15         protected $link;
  16         protected $db_config;
  17
  18         /**
  19          * Sets the config for the class.
  20          *
  21          * @param  array  database configuration
  22          */
  23         public function __construct($config)
  24         {
  25                 $this->db_config = $config;
  26
  27                 Kohana::log('debug', 'PgSQL Database Driver Initialized');
  28         }
  29
  30         public function connect()
  31         {
  32                 // Check if link already exists
  33                 if (is_resource($this->link))
  34                         return $this->link;
  35
  36                 // Import the connect variables
  37                 extract($this->db_config['connection']);
  38
  39                 // Persistent connections enabled?
  40                 $connect = ($this->db_config['persistent'] == TRUE) ? 'pg_pconnect' : 'pg_connect';
  41
  42                 // Build the connection info
  43                 $port = isset($port) ? 'port=\''.$port.'\'' : '';
  44                 $host = isset($host) ? 'host=\''.$host.'\' '.$port : ''; // if no host, connect with the socket
  45
  46                 $connection_string = $host.' dbname=\''.$database.'\' user=\''.$user.'\' password=\''.$pass.'\'';
  47                 // Make the connection and select the database
  48                 if ($this->link = $connect($connection_string))
  49                 {
  50                         if ($charset = $this->db_config['character_set'])
  51                         {
  52                                 echo $this->set_charset($charset);
  53                         }
  54
  55                         // Clear password after successful connect
  56                         $this->db_config['connection']['pass'] = NULL;
  57
  58                         return $this->link;
  59                 }
  60
  61                 return FALSE;
  62         }
  63
  64         public function query($sql)
  65         {
  66                 // Only cache if it's turned on, and only cache if it's not a write statement
  67                 if ($this->db_config['cache'] AND ! preg_match('#\b(?:INSERT|UPDATE|SET)\b#i', $sql))
  68                 {
  69                         $hash = $this->query_hash($sql);
  70
  71                         if ( ! isset($this->query_cache[$hash]))
  72                         {
  73                                 // Set the cached object
  74                                 $this->query_cache[$hash] = new Pgsql_Result(pg_query($this->link, $sql), $this->link, $this->db_config['object'], $sql);
  75                         }
  76                         else
  77                         {
  78                                 // Rewind cached result
  79                                 $this->query_cache[$hash]->rewind();
  80                         }
  81
  82                         return $this->query_cache[$hash];
  83                 }
  84
  85                 // Suppress warning triggered when a database error occurs (e.g., a constraint violation)
  86                 return new Pgsql_Result(@pg_query($this->link, $sql), $this->link, $this->db_config['object'], $sql);
  87         }
  88
  89         public function set_charset($charset)
  90         {
  91                 $this->query('SET client_encoding TO '.pg_escape_string($this->link, $charset));
  92         }
  93
  94         public function escape_table($table)
  95         {
  96                 if (!$this->db_config['escape'])
  97                         return $table;
  98
  99                 return '"'.str_replace('.', '"."', $table).'"';
 100         }
 101
 102         public function escape_column($column)
 103         {
 104                 if (!$this->db_config['escape'])
 105                         return $column;
 106
 107                 if ($column == '*')
 108                         return $column;
 109
 110                 // This matches any functions we support to SELECT.
 111                 if ( preg_match('/(avg|count|sum|max|min)\(\s*(.*)\s*\)(\s*as\s*(.+)?)?/i', $column, $matches))
 112                 {
 113                         if ( count($matches) == 3)
 114                         {
 115                                 return $matches[1].'('.$this->escape_column($matches[2]).')';
 116                         }
 117                         else if ( count($matches) == 5)
 118                         {
 119                                 return $matches[1].'('.$this->escape_column($matches[2]).') AS '.$this->escape_column($matches[2]);
 120                         }
 121                 }
 122
 123                 // This matches any modifiers we support to SELECT.
 124                 if ( ! preg_match('/\b(?:all|distinct)\s/i', $column))
 125                 {
 126                         if (stripos($column, ' AS ') !== FALSE)
 127                         {
 128                                 // Force 'AS' to uppercase
 129                                 $column = str_ireplace(' AS ', ' AS ', $column);
 130
 131                                 // Runs escape_column on both sides of an AS statement
 132                                 $column = array_map(array($this, __FUNCTION__), explode(' AS ', $column));
 133
 134                                 // Re-create the AS statement
 135                                 return implode(' AS ', $column);
 136                         }
 137
 138                         return preg_replace('/[^.*]+/', '"$0"', $column);
 139                 }
 140
 141                 $parts = explode(' ', $column);
 142                 $column = '';
 143
 144                 for ($i = 0, $c = count($parts); $i < $c; $i++)
 145                 {
 146                         // The column is always last
 147                         if ($i == ($c - 1))
 148                         {
 149                                 $column .= preg_replace('/[^.*]+/', '"$0"', $parts[$i]);
 150                         }
 151                         else // otherwise, it's a modifier
 152                         {
 153                                 $column .= $parts[$i].' ';
 154                         }
 155                 }
 156                 return $column;
 157         }
 158
 159         public function regex($field, $match, $type, $num_regexs)
 160         {
 161                 $prefix = ($num_regexs == 0) ? '' : $type;
 162
 163                 return $prefix.' '.$this->escape_column($field).' ~* \''.$this->escape_str($match).'\'';
 164         }
 165
 166         public function notregex($field, $match, $type, $num_regexs)
 167         {
 168                 $prefix = $num_regexs == 0 ? '' : $type;
 169
 170                 return $prefix.' '.$this->escape_column($field).' !~* \''.$this->escape_str($match) . '\'';
 171         }
 172
 173         public function limit($limit, $offset = 0)
 174         {
 175                 return 'LIMIT '.$limit.' OFFSET '.$offset;
 176         }
 177
 178         public function compile_select($database)
 179         {
 180                 $sql = ($database['distinct'] == TRUE) ? 'SELECT DISTINCT ' : 'SELECT ';
 181                 $sql .= (count($database['select']) > 0) ? implode(', ', $database['select']) : '*';
 182
 183                 if (count($database['from']) > 0)
 184                 {
 185                         $sql .= "\nFROM ";
 186                         $sql .= implode(', ', $database['from']);
 187                 }
 188
 189                 if (count($database['join']) > 0)
 190                 {
 191                         foreach($database['join'] AS $join)
 192                         {
 193                                 $sql .= "\n".$join['type'].'JOIN '.implode(', ', $join['tables']).' ON '.$join['conditions'];
 194                         }
 195                 }
 196
 197                 if (count($database['where']) > 0)
 198                 {
 199                         $sql .= "\nWHERE ";
 200                 }
 201
 202                 $sql .= implode("\n", $database['where']);
 203
 204                 if (count($database['groupby']) > 0)
 205                 {
 206                         $sql .= "\nGROUP BY ";
 207                         $sql .= implode(', ', $database['groupby']);
 208                 }
 209
 210                 if (count($database['having']) > 0)
 211                 {
 212                         $sql .= "\nHAVING ";
 213                         $sql .= implode("\n", $database['having']);
 214                 }
 215
 216                 if (count($database['orderby']) > 0)
 217                 {
 218                         $sql .= "\nORDER BY ";
 219                         $sql .= implode(', ', $database['orderby']);
 220                 }
 221
 222                 if (is_numeric($database['limit']))
 223                 {
 224                         $sql .= "\n";
 225                         $sql .= $this->limit($database['limit'], $database['offset']);
 226                 }
 227
 228                 return $sql;
 229         }
 230
 231         public function escape_str($str)
 232         {
 233                 if (!$this->db_config['escape'])
 234                         return $str;
 235
 236                 is_resource($this->link) or $this->connect();
 237
 238                 return pg_escape_string($this->link, $str);
 239         }
 240
 241         public function list_tables()
 242         {
 243                 $sql    = 'SELECT table_schema || \'.\' || table_name FROM information_schema.tables WHERE table_schema NOT IN (\'pg_catalog\', \'information_schema\')';
 244                 $result = $this->query($sql)->result(FALSE, PGSQL_ASSOC);
 245
 246                 $retval = array();
 247                 foreach ($result as $row)
 248                 {
 249                         $retval[] = current($row);
 250                 }
 251
 252                 return $retval;
 253         }
 254
 255         public function show_error()
 256         {
 257                 return pg_last_error($this->link);
 258         }
 259
 260         public function list_fields($table)
 261         {
 262                 $result = NULL;
 263
 264                 foreach ($this->field_data($table) as $row)
 265                 {
 266                         // Make an associative array
 267                         $result[$row->column_name] = $this->sql_type($row->data_type);
 268
 269                         if (!strncmp($row->column_default, 'nextval(', 8))
 270                         {
 271                                 $result[$row->column_name]['sequenced'] = TRUE;
 272                         }
 273
 274                         if ($row->is_nullable === 'YES')
 275                         {
 276                                 $result[$row->column_name]['null'] = TRUE;
 277                         }
 278                 }
 279
 280                 if (!isset($result))
 281                         throw new Kohana_Database_Exception('database.table_not_found', $table);
 282
 283                 return $result;
 284         }
 285
 286         public function field_data($table)
 287         {
 288                 // http://www.postgresql.org/docs/8.3/static/infoschema-columns.html
 289                 $result = $this->query('
 290                         SELECT column_name, column_default, is_nullable, data_type, udt_name,
 291                                 character_maximum_length, numeric_precision, numeric_precision_radix, numeric_scale
 292                         FROM information_schema.columns
 293                         WHERE table_name = \''. $this->escape_str($table) .'\'
 294                         ORDER BY ordinal_position
 295                 ');
 296
 297                 return $result->result_array(TRUE);
 298         }
 299
 300 } // End Database_Pgsql_Driver Class
 301
 302 /**
 303  * PostgreSQL Result
 304  */
 305 class Pgsql_Result extends Database_Result {
 306
 307         // Data fetching types
 308         protected $fetch_type  = 'pgsql_fetch_object';
 309         protected $return_type = PGSQL_ASSOC;
 310
 311         /**
 312          * Sets up the result variables.
 313          *
 314          * @param  resource  query result
 315          * @param  resource  database link
 316          * @param  boolean   return objects or arrays
 317          * @param  string    SQL query that was run
 318          */
 319         public function __construct($result, $link, $object = TRUE, $sql)
 320         {
 321                 $this->link = $link;
 322                 $this->result = $result;
 323
 324                 // If the query is a resource, it was a SELECT, SHOW, DESCRIBE, EXPLAIN query
 325                 if (is_resource($result))
 326                 {
 327                         // Its an DELETE, INSERT, REPLACE, or UPDATE query
 328                         if (preg_match('/^(?:delete|insert|replace|update)\b/iD', trim($sql), $matches))
 329                         {
 330                                 $this->insert_id  = (strtolower($matches[0]) == 'insert') ? $this->insert_id() : FALSE;
 331                                 $this->total_rows = pg_affected_rows($this->result);
 332                         }
 333                         else
 334                         {
 335                                 $this->current_row = 0;
 336                                 $this->total_rows  = pg_num_rows($this->result);
 337                                 $this->fetch_type = ($object === TRUE) ? 'pg_fetch_object' : 'pg_fetch_array';
 338                         }
 339                 }
 340                 else
 341                 {
 342                         throw new Kohana_Database_Exception('database.error', pg_last_error().' - '.$sql);
 343                 }
 344
 345                 // Set result type
 346                 $this->result($object);
 347
 348                 // Store the SQL
 349                 $this->sql = $sql;
 350         }
 351
 352         /**
 353          * Magic __destruct function, frees the result.
 354          */
 355         public function __destruct()
 356         {
 357                 if (is_resource($this->result))
 358                 {
 359                         pg_free_result($this->result);
 360                 }
 361         }
 362
 363         public function result($object = TRUE, $type = PGSQL_ASSOC)
 364         {
 365                 $this->fetch_type = ((bool) $object) ? 'pg_fetch_object' : 'pg_fetch_array';
 366
 367                 // This check has to be outside the previous statement, because we do not
 368                 // know the state of fetch_type when $object = NULL
 369                 // NOTE - The class set by $type must be defined before fetching the result,
 370                 // autoloading is disabled to save a lot of stupid overhead.
 371                 if ($this->fetch_type == 'pg_fetch_object')
 372                 {
 373                         $this->return_type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 374                 }
 375                 else
 376                 {
 377                         $this->return_type = $type;
 378                 }
 379
 380                 return $this;
 381         }
 382
 383         public function as_array($object = NULL, $type = PGSQL_ASSOC)
 384         {
 385                 return $this->result_array($object, $type);
 386         }
 387
 388         public function result_array($object = NULL, $type = PGSQL_ASSOC)
 389         {
 390                 $rows = array();
 391
 392                 if (is_string($object))
 393                 {
 394                         $fetch = $object;
 395                 }
 396                 elseif (is_bool($object))
 397                 {
 398                         if ($object === TRUE)
 399                         {
 400                                 $fetch = 'pg_fetch_object';
 401
 402                                 // NOTE - The class set by $type must be defined before fetching the result,
 403                                 // autoloading is disabled to save a lot of stupid overhead.
 404                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 405                         }
 406                         else
 407                         {
 408                                 $fetch = 'pg_fetch_array';
 409                         }
 410                 }
 411                 else
 412                 {
 413                         // Use the default config values
 414                         $fetch = $this->fetch_type;
 415
 416                         if ($fetch == 'pg_fetch_object')
 417                         {
 418                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 419                         }
 420                 }
 421
 422                 if ($this->total_rows)
 423                 {
 424                         pg_result_seek($this->result, 0);
 425
 426                         while ($row = $fetch($this->result, NULL, $type))
 427                         {
 428                                 $rows[] = $row;
 429                         }
 430                 }
 431
 432                 return $rows;
 433         }
 434
 435         public function insert_id()
 436         {
 437                 if ($this->insert_id === NULL)
 438                 {
 439                         $query = 'SELECT LASTVAL() AS insert_id';
 440
 441                         // Disable error reporting for this, just to silence errors on
 442                         // tables that have no serial column.
 443                         $ER = error_reporting(0);
 444
 445                         $result = pg_query($this->link, $query);
 446                         $insert_id = pg_fetch_array($result, NULL, PGSQL_ASSOC);
 447
 448                         $this->insert_id = $insert_id['insert_id'];
 449
 450                         // Reset error reporting
 451                         error_reporting($ER);
 452                 }
 453
 454                 return $this->insert_id;
 455         }
 456
 457         public function seek($offset)
 458         {
 459                 if ($this->offsetExists($offset) AND pg_result_seek($this->result, $offset))
 460                 {
 461                         // Set the current row to the offset
 462                         $this->current_row = $offset;
 463
 464                         return TRUE;
 465                 }
 466
 467                 return FALSE;
 468         }
 469
 470         public function list_fields()
 471         {
 472                 $field_names = array();
 473
 474                 $fields = pg_num_fields($this->result);
 475                 for ($i = 0; $i < $fields; ++$i)
 476                 {
 477                         $field_names[] = pg_field_name($this->result, $i);
 478                 }
 479
 480                 return $field_names;
 481         }
 482
 483         /**
 484          * ArrayAccess: offsetGet
 485          */
 486         public function offsetGet($offset)
 487         {
 488                 if ( ! $this->seek($offset))
 489                         return FALSE;
 490
 491                 // Return the row by calling the defined fetching callback
 492                 $fetch = $this->fetch_type;
 493                 return $fetch($this->result, NULL, $this->return_type);
 494         }
 495
 496 } // End Pgsql_Result Class
 497
 498 /**
 499  * PostgreSQL Prepared Statement (experimental)
 500  */
 501 class Kohana_Pgsql_Statement {
 502
 503         protected $link = NULL;
 504         protected $stmt;
 505
 506         public function __construct($sql, $link)
 507         {
 508                 $this->link = $link;
 509
 510                 $this->stmt = $this->link->prepare($sql);
 511
 512                 return $this;
 513         }
 514
 515         public function __destruct()
 516         {
 517                 $this->stmt->close();
 518         }
 519
 520         // Sets the bind parameters
 521         public function bind_params()
 522         {
 523                 $argv = func_get_args();
 524                 return $this;
 525         }
 526
 527         // sets the statement values to the bound parameters
 528         public function set_vals()
 529         {
 530                 return $this;
 531         }
 532
 533         // Runs the statement
 534         public function execute()
 535         {
 536                 return $this;
 537         }
 538 }