| 53 | | return new Pgsql_Result(pg_query($this->link, $sql), $this->link, $this->db_config['object'], $sql); |
| 54 | | } |
| 55 | | |
| 56 | | public function delete($table, $where) |
| 57 | | { |
| 58 | | return 'DELETE FROM '.$this->escape_table($table).' WHERE '.implode(' ', $where); |
| 59 | | } |
| 60 | | |
| 61 | | public function update($table, $values, $where) |
| 62 | | { |
| 63 | | foreach($values as $key => $val) |
| 64 | | { |
| 65 | | $valstr[] = $this->escape_column($key)." = ".$val; |
| 66 | | } |
| 67 | | return 'UPDATE '.$this->escape_table($table).' SET '.implode(', ', $valstr).' WHERE '.implode(' AND ',$this->where($where, NULL, 'AND', 0, TRUE)); |
| | 53 | return new Pgsql_Result(pg_query($sql, $this->link), $this->link, $this->db_config['object'], $sql); |
| 77 | | return str_replace('.', '`.`', $table); |
| 78 | | } |
| 79 | | |
| 80 | | public function escape_column($column) |
| 81 | | { |
| 82 | | return '\''.$column.'\''; |
| 83 | | } |
| 84 | | |
| 85 | | public function where($key, $value, $type, $num_wheres, $quote) |
| 86 | | { |
| 87 | | if ( ! is_array($key)) |
| 88 | | { |
| 89 | | $key = array($key => $value); |
| 90 | | } |
| 91 | | |
| 92 | | $wheres = array(); |
| 93 | | $count = 1; |
| 94 | | foreach ($key as $k => $v) |
| 95 | | { |
| 96 | | |
| 97 | | $prefix = (($num_wheres > 0) OR ($count++ > 1)) ? $type : ''; |
| 98 | | |
| 99 | | if ($quote === -1) |
| 100 | | { |
| 101 | | $v = ''; |
| 102 | | } |
| 103 | | else |
| 104 | | { |
| 105 | | if ($v === NULL) |
| 106 | | { |
| 107 | | if ( ! $this->has_operator($k)) |
| 108 | | { |
| 109 | | $k .= ' IS'; |
| 110 | | } |
| 111 | | |
| 112 | | $v = ' NULL'; |
| 113 | | } |
| 114 | | elseif (is_bool($v)) |
| 115 | | { |
| 116 | | if ( ! $this->has_operator($k)) |
| 117 | | { |
| 118 | | $k .= ' ='; |
| 119 | | } |
| 120 | | |
| 121 | | $v = ($v == TRUE) ? ' 1' : ' 0'; |
| 122 | | } |
| 123 | | else |
| 124 | | { |
| 125 | | if ( ! $this->has_operator($k)) |
| 126 | | { |
| 127 | | $k .= ' ='; |
| 128 | | } |
| 129 | | |
| 130 | | $v = ' '.(($quote == TRUE) ? $this->escape($v) : $v); |
| 131 | | } |
| 132 | | } |
| 133 | | $wheres[] = $prefix.$k.$v; |
| 134 | | } |
| 135 | | return $wheres; |
| 136 | | } |
| 137 | | |
| 138 | | public function like($field, $match = '', $type = 'AND ', $num_likes) |
| 139 | | { |
| 140 | | if ( ! is_array($field)) |
| 141 | | { |
| 142 | | $field = array($field => $match); |
| 143 | | } |
| 144 | | |
| 145 | | $likes = array(); |
| 146 | | foreach ($field as $k => $v) |
| 147 | | { |
| 148 | | $prefix = (count($num_likes) == 0) ? '' : $type; |
| 149 | | |
| 150 | | $v = (substr($v, 0, 1) == '%' OR substr($v, (strlen($v)-1), 1) == '%') ? $this->escape_str($v) : '%'.$this->escape_str($v).'%'; |
| 151 | | |
| 152 | | $likes[] = $prefix." ".$k." LIKE '".$v . "'"; |
| 153 | | } |
| 154 | | return $likes; |
| 155 | | } |
| 156 | | |
| 157 | | public function insert($table, $keys, $values) |
| 158 | | { |
| 159 | | return 'INSERT INTO '.$this->escape_table($table).' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')'; |
| | 63 | return '\''.str_replace('.', '\'.\'', $table).'\''; |
| | 64 | } |
| | 65 | |
| | 66 | public function escape_column($column) |
| | 67 | { |
| | 68 | if (strtolower($column) == 'count(*)' OR $column == '*') |
| | 69 | return $column; |
| | 70 | |
| | 71 | // This matches any modifiers we support to SELECT. |
| | 72 | if ( ! preg_match('/\b(?:rand|all|distinct(?:row)?|high_priority|sql_(?:small_result|b(?:ig_result|uffer_result)|no_cache|ca(?:che|lc_found_rows)))\s/i', $column)) |
| | 73 | { |
| | 74 | if (stripos($column, ' AS ') !== FALSE) |
| | 75 | { |
| | 76 | // Force 'AS' to uppercase |
| | 77 | $column = str_ireplace(' AS ', ' AS ', $column); |
| | 78 | |
| | 79 | // Runs escape_column on both sides of an AS statement |
| | 80 | $column = array_map(array($this, __FUNCTION__), explode(' AS ', $column)); |
| | 81 | |
| | 82 | // Re-create the AS statement |
| | 83 | return implode(' AS ', $column); |
| | 84 | } |
| | 85 | |
| | 86 | return preg_replace('/[^.*]+/', '\'$0\'', $column); |
| | 87 | } |
| | 88 | |
| | 89 | $parts = explode(' ', $column); |
| | 90 | $column = ''; |
| | 91 | |
| | 92 | for ($i = 0, $c = count($parts); $i < $c; $i++) |
| | 93 | { |
| | 94 | // The column is always last |
| | 95 | if ($i == ($c - 1)) |
| | 96 | { |
| | 97 | $column .= preg_replace('/[^.*]+/', '\'$0\'', $parts[$i]); |
| | 98 | } |
| | 99 | else // otherwise, it's a modifier |
| | 100 | { |
| | 101 | $column .= $parts[$i].' '; |
| | 102 | } |
| | 103 | } |
| | 104 | return $column; |
| | 105 | } |
| | 106 | |
| | 107 | public function regex($field, $match = '', $type = 'AND ', $num_regexs) |
| | 108 | { |
| | 109 | $prefix = ($num_regexs == 0) ? '' : $type; |
| | 110 | |
| | 111 | return $prefix.' '.$this->escape_column($field).' REGEXP \''.$this->escape_str($match).'\''; |
| | 112 | } |
| | 113 | |
| | 114 | public function notregex($field, $match = '', $type = 'AND ', $num_regexs) |
| | 115 | { |
| | 116 | $prefix = $num_regexs == 0 ? '' : $type; |
| | 117 | |
| | 118 | return $prefix.' '.$this->escape_column($field).' NOT REGEXP \''.$this->escape_str($match) . '\''; |
| | 201 | |
| | 202 | public function list_fields($table, $query = FALSE) |
| | 203 | { |
| | 204 | static $tables; |
| | 205 | |
| | 206 | if (is_object($query)) |
| | 207 | { |
| | 208 | if (empty($tables[$table])) |
| | 209 | { |
| | 210 | $tables[$table] = array(); |
| | 211 | |
| | 212 | foreach($query as $row) |
| | 213 | { |
| | 214 | $tables[$table][] = $row->Field; |
| | 215 | } |
| | 216 | } |
| | 217 | |
| | 218 | return $tables[$table]; |
| | 219 | } |
| | 220 | |
| | 221 | // WOW...REALLY?!? |
| | 222 | // Taken from http://www.postgresql.org/docs/7.4/interactive/catalogs.html |
| | 223 | return 'SELECT |
| | 224 | -- Field |
| | 225 | pg_attribute.attname AS "Field", |
| | 226 | -- Type |
| | 227 | CASE pg_type.typname |
| | 228 | WHEN \'int2\' THEN \'smallint\' |
| | 229 | WHEN \'int4\' THEN \'int\' |
| | 230 | WHEN \'int8\' THEN \'bigint\' |
| | 231 | WHEN \'varchar\' THEN \'varchar(\' || pg_attribute.atttypmod-4 || \')\' |
| | 232 | ELSE pg_type.typname |
| | 233 | END AS "Type", |
| | 234 | -- Null |
| | 235 | CASE WHEN pg_attribute.attnotnull THEN \'\' |
| | 236 | ELSE \'YES\' |
| | 237 | END AS "Null", |
| | 238 | -- Default |
| | 239 | CASE pg_type.typname |
| | 240 | WHEN \'varchar\' THEN substring(pg_attrdef.adsrc from \'^\'(.*)\'.*$\') |
| | 241 | ELSE pg_attrdef.adsrc |
| | 242 | END AS "Default" |
| | 243 | FROM pg_class |
| | 244 | INNER JOIN pg_attribute |
| | 245 | ON (pg_class.oid=pg_attribute.attrelid) |
| | 246 | INNER JOIN pg_type |
| | 247 | ON (pg_attribute.atttypid=pg_type.oid) |
| | 248 | LEFT JOIN pg_attrdef |
| | 249 | ON (pg_class.oid=pg_attrdef.adrelid AND pg_attribute.attnum=pg_attrdef.adnum) |
| | 250 | WHERE pg_class.relname=\''.$this->escape_table($table).'\' AND pg_attribute.attnum>=1 AND NOT pg_attribute.attisdropped |
| | 251 | ORDER BY pg_attribute.attnum'; |
| | 252 | |
| | 253 | } |
| | 254 | |
| | 255 | public function field_data($table) |
| | 256 | { |
| | 257 | // TODO: This whole function needs to be debugged. |
| | 258 | if ( ! in_array($table, $this->list_tables())) |
| | 259 | return FALSE; |
| | 260 | |
| | 261 | $query = pg_query('SELECT * FROM '.$this->escape_table($table).' LIMIT 1', $this->link); |
| | 262 | $fields = pg_num_fields($query); |
| | 263 | $table = array(); |
| | 264 | |
| | 265 | for ($i=0; $i < $fields; $i++) |
| | 266 | { |
| | 267 | $table[$i]['type'] = pg_field_type($query, $i); |
| | 268 | $table[$i]['name'] = pg_field_name($query, $i); |
| | 269 | $table[$i]['len'] = pg_field_prtlen($query, $i); |
| | 270 | } |
| | 271 | |
| | 272 | return $table; |
| | 273 | } |
| | 274 | |
| 291 | | class Pgsql_Result implements Database_Result, Iterator |
| 292 | | { |
| 293 | | private $link = FALSE; |
| 294 | | private $result = FALSE; |
| 295 | | private $insert_id = NULL; |
| 296 | | private $num_rows = 0; |
| 297 | | private $rows = array(); |
| 298 | | private $object = TRUE; |
| 299 | | |
| | 286 | class Pgsql_Result implements Database_Result, ArrayAccess, Iterator, Countable { |
| | 287 | |
| | 288 | // Result resource |
| | 289 | protected $result = NULL; |
| | 290 | |
| | 291 | // Total rows and current row |
| | 292 | protected $total_rows = FALSE; |
| | 293 | protected $current_row = FALSE; |
| | 294 | |
| | 295 | // Insert id |
| | 296 | protected $insert_id = FALSE; |
| | 297 | |
| | 298 | // Data fetching types |
| | 299 | protected $fetch_type = 'pgsql_fetch_object'; |
| | 300 | protected $return_type = PGSQL_ASSOC; |
| | 301 | |
| | 302 | /** |
| | 303 | * Constructor: __construct |
| | 304 | * Sets up the class. |
| | 305 | * |
| | 306 | * Parameters: |
| | 307 | * result - result resource |
| | 308 | * link - database resource link |
| | 309 | * object - return objects or arrays |
| | 310 | * sql - sql query that was run |
| | 311 | */ |
| 307 | | $this->result = $result; |
| 308 | | $this->num_rows = pg_num_rows($this->result); |
| | 319 | $this->current_row = 0; |
| | 320 | $this->total_rows = pg_num_rows($this->result); |
| | 321 | $this->fetch_type = ($object === TRUE) ? 'pg_fetch_object' : 'pg_fetch_array'; |
| | 322 | } |
| | 323 | elseif (is_bool($result)) |
| | 324 | { |
| | 325 | if ($result == FALSE) |
| | 326 | { |
| | 327 | // SQL error |
| | 328 | throw new Kohana_Database_Exception('database.error', pg_last_error().' - '.$sql); |
| | 329 | } |
| | 330 | else |
| | 331 | { |
| | 332 | // Its an DELETE, INSERT, REPLACE, or UPDATE query |
| | 333 | $this->insert_id = $this->get_insert_id($link); |
| | 334 | $this->total_rows = pg_affected_rows($link); |
| | 335 | } |
| | 336 | } |
| | 337 | |
| | 338 | // Set result type |
| | 339 | $this->result($object); |
| | 340 | } |
| | 341 | |
| | 342 | /** |
| | 343 | * Destructor: __destruct |
| | 344 | * Magic __destruct function, frees the result. |
| | 345 | */ |
| | 346 | public function __destruct() |
| | 347 | { |
| | 348 | if (is_resource($this->result)) |
| | 349 | { |
| | 350 | pg_free_result($this->result); |
| | 351 | } |
| | 352 | } |
| | 353 | |
| | 354 | public function result($object = TRUE, $type = PGSQL_ASSOC) |
| | 355 | { |
| | 356 | $this->fetch_type = ((bool) $object) ? 'pg_fetch_object' : 'pg_fetch_array'; |
| | 357 | |
| | 358 | // This check has to be outside the previous statement, because we do not |
| | 359 | // know the state of fetch_type when $object = NULL |
| | 360 | // NOTE - The class set by $type must be defined before fetching the result, |
| | 361 | // autoloading is disabled to save a lot of stupid overhead. |
| | 362 | if ($this->fetch_type == 'pg_fetch_object') |
| | 363 | { |
| | 364 | $this->return_type = class_exists($type, FALSE) ? $type : 'stdClass'; |
| 312 | | if ($result == FALSE) |
| 313 | | { |
| 314 | | throw new Kohana_Exception('database.error', pg_last_error($this->link).' - '.$sql); |
| 315 | | } |
| 316 | | else if ($result == TRUE) // Its an DELETE, INSERT, REPLACE, or UPDATE query |
| 317 | | { |
| 318 | | //$this->insert_id = mysql_insert_id($link); |
| 319 | | $this->num_rows = pg_affected_rows($link); |
| 320 | | } |
| 321 | | } |
| 322 | | } |
| 323 | | |
| 324 | | public function result($object = TRUE, $type = PGSQL_ASSOC) |
| 325 | | { |
| 326 | | $fetch = ($object == TRUE) ? 'pg_fetch_object' : 'pg_fetch_array'; |
| 327 | | $type = ($object == TRUE) ? 'stdClass' : $type; |
| | 368 | $this->return_type = $type; |
| | 369 | } |
| | 370 | |
| | 371 | return $this; |
| | 372 | } |
| | 373 | |
| | 374 | public function result_array($object = NULL, $type = PGSQL_ASSOC) |
| | 375 | { |
| | 376 | $rows = array(); |
| | 377 | |
| | 378 | if (is_string($object)) |
| | 379 | { |
| | 380 | $fetch = $object; |
| | 381 | } |
| | 382 | elseif (is_bool($object)) |
| | 383 | { |
| | 384 | if ($object === TRUE) |
| | 385 | { |
| | 386 | $fetch = 'pg_fetch_object'; |
| | 387 | |
| | 388 | // NOTE - The class set by $type must be defined before fetching the result, |
| | 389 | // autoloading is disabled to save a lot of stupid overhead. |
| | 390 | $type = class_exists($type, FALSE) ? $type : 'stdClass'; |
| | 391 | } |
| | 392 | else |
| | 393 | { |
| | 394 | $fetch = 'pg_fetch_array'; |
| | 395 | } |
| | 396 | } |
| | 397 | else |
| | 398 | { |
| | 399 | // Use the default config values |
| | 400 | $fetch = $this->fetch_type; |
| | 401 | |
| | 402 | if ($fetch == 'pg_fetch_object') |
| | 403 | { |
| | 404 | $type = class_exists($type, FALSE) ? $type : 'stdClass'; |
| | 405 | } |
| | 406 | } |
| | 421 | public function list_fields() |
| | 422 | { |
| | 423 | throw new Kohana_Database_Exception('database.not_implimented', __FUNCTION__); |
| | 424 | } |
| | 425 | // End Interface |
| | 426 | |
| | 427 | private function get_insert_id($link) |
| | 428 | { |
| | 429 | $query = 'SELECT LASTVAL() as insert_id'; |
| | 430 | |
| | 431 | $result = pg_query($link, $query); |
| | 432 | $insert_id = pg_fetch_array($result, NULL, PGSQL_ASSOC); |
| | 433 | |
| | 434 | return $insert_id['insert_id']; |
| | 435 | } |
| | 436 | |
| | 437 | // Interface: Countable |
| | 438 | /** |
| | 439 | * Method: count |
| | 440 | * Counts the number of rows in the result set. |
| | 441 | * |
| | 442 | * Returns: |
| | 443 | * The number of rows in the result set |
| | 444 | */ |
| | 445 | public function count() |
| | 446 | { |
| | 447 | return $this->total_rows; |
| | 448 | } |
| | 449 | // End Interface |
| | 450 | |
| | 451 | // Interface: ArrayAccess |
| | 452 | /** |
| | 453 | * Method: offsetExists |
| | 454 | * Determines if the requested offset of the result set exists. |
| | 455 | * |
| | 456 | * Parameters: |
| | 457 | * offset - offset id |
| | 458 | * |
| | 459 | * Returns: |
| | 460 | * TRUE if the offset exists, FALSE otherwise |
| | 461 | */ |
| | 462 | public function offsetExists($offset) |
| | 463 | { |
| | 464 | if ($this->total_rows > 0) |
| | 465 | { |
| | 466 | $min = 0; |
| | 467 | $max = $this->total_rows - 1; |
| | 468 | |
| | 469 | return ($offset < $min OR $offset > $max) ? FALSE : TRUE; |
| | 470 | } |
| | 471 | |
| | 472 | return FALSE; |
| | 473 | } |
| | 474 | |
| | 475 | /** |
| | 476 | * Method: offsetGet |
| | 477 | * Retreives the requested query result offset. |
| | 478 | * |
| | 479 | * Parameters: |
| | 480 | * offset - offset id |
| | 481 | * |
| | 482 | * Returns: |
| | 483 | * The query row |
| | 484 | */ |
| | 485 | public function offsetGet($offset) |
| | 486 | { |
| | 487 | // Check to see if the requested offset exists. |
| | 488 | if (!$this->offsetExists($offset)) |
| | 489 | return FALSE; |
| | 490 | |
| | 491 | // Go to the offset and return the row |
| | 492 | $fetch = $this->fetch_type; |
| | 493 | return $fetch($this->result, $offset, $this->return_type); |
| | 494 | } |
| | 495 | |
| | 496 | /** |
</
