Changeset 3 for branches/rsr.v5.1.dev/web/punbb/include/dblayer/pgsql.php
- Timestamp:
- Nov 14, 2011, 11:17:15 PM (12 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/rsr.v5.1.dev/web/punbb/include/dblayer/pgsql.php
r1 r3 1 1 <?php 2 /*********************************************************************** 3 4 Copyright (C) 2002-2005 Rickard Andersson (rickard@punbb.org) 5 6 This file is part of PunBB. 7 8 PunBB is free software; you can redistribute it and/or modify it 9 under the terms of the GNU General Public License as published 10 by the Free Software Foundation; either version 2 of the License, 11 or (at your option) any later version. 12 13 PunBB is distributed in the hope that it will be useful, but 14 WITHOUT ANY WARRANTY; without even the implied warranty of 15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 16 GNU General Public License for more details. 17 18 You should have received a copy of the GNU General Public License 19 along with this program; if not, write to the Free Software 20 Foundation, Inc., 59 Temple Place, Suite 330, Boston, 21 MA 02111-1307 USA 22 23 ************************************************************************/ 24 2 3 /** 4 * Copyright (C) 2008-2011 FluxBB 5 * based on code by Rickard Andersson copyright (C) 2002-2008 PunBB 6 * License: http://www.gnu.org/licenses/gpl.html GPL version 2 or higher 7 */ 25 8 26 9 // Make sure we have built in support for PostgreSQL … … 43 26 var $error_msg = 'Unknown'; 44 27 28 var $datatype_transformations = array( 29 '%^(TINY|SMALL)INT( )?(\\([0-9]+\\))?( )?(UNSIGNED)?$%i' => 'SMALLINT', 30 '%^(MEDIUM)?INT( )?(\\([0-9]+\\))?( )?(UNSIGNED)?$%i' => 'INTEGER', 31 '%^BIGINT( )?(\\([0-9]+\\))?( )?(UNSIGNED)?$%i' => 'BIGINT', 32 '%^(TINY|MEDIUM|LONG)?TEXT$%i' => 'TEXT', 33 '%^DOUBLE( )?(\\([0-9,]+\\))?( )?(UNSIGNED)?$%i' => 'DOUBLE PRECISION', 34 '%^FLOAT( )?(\\([0-9]+\\))?( )?(UNSIGNED)?$%i' => 'REAL' 35 ); 36 45 37 46 38 function DBLayer($db_host, $db_username, $db_password, $db_name, $db_prefix, $p_connect) … … 48 40 $this->prefix = $db_prefix; 49 41 50 if ($db_host != '')42 if ($db_host) 51 43 { 52 44 if (strpos($db_host, ':') !== false) … … 56 48 } 57 49 else 58 { 59 if ($db_host != 'localhost') 60 $connect_str[] = 'host='.$db_host; 61 } 50 $connect_str[] = 'host='.$db_host; 62 51 } 63 52 … … 65 54 $connect_str[] = 'dbname='.$db_name; 66 55 67 if ($db_username != '')56 if ($db_username) 68 57 $connect_str[] = 'user='.$db_username; 69 58 70 if ($db_password != '')59 if ($db_password) 71 60 $connect_str[] = 'password='.$db_password; 72 61 … … 78 67 if (!$this->link_id) 79 68 error('Unable to connect to PostgreSQL server', __FILE__, __LINE__); 80 else 81 return $this->link_id; 69 70 // Setup the client-server character set (UTF-8) 71 if (!defined('FORUM_NO_SET_NAMES')) 72 $this->set_names('utf8'); 73 74 return $this->link_id; 82 75 } 83 76 … … 105 98 106 99 107 function query($sql, $unbuffered = false) 100 function query($sql, $unbuffered = false) // $unbuffered is ignored since there is no pgsql_unbuffered_query() 108 101 { 109 102 if (strrpos($sql, 'LIMIT') !== false) 110 $sql = preg_replace(' #LIMIT ([0-9]+),([ 0-9]+)#', 'LIMIT \\2 OFFSET \\1', $sql);103 $sql = preg_replace('%LIMIT ([0-9]+),([ 0-9]+)%', 'LIMIT \\2 OFFSET \\1', $sql); 111 104 112 105 if (defined('PUN_SHOW_QUERIES')) … … 132 125 $this->saved_queries[] = array($sql, 0); 133 126 127 $this->error_no = false; 134 128 $this->error_msg = @pg_result_error($this->query_result); 135 129 … … 144 138 145 139 146 function result($query_id = 0, $row = 0 )147 { 148 return ($query_id) ? @pg_fetch_result($query_id, $row, 0) : false;140 function result($query_id = 0, $row = 0, $col = 0) 141 { 142 return ($query_id) ? @pg_fetch_result($query_id, $row, $col) : false; 149 143 } 150 144 … … 180 174 if ($query_id && $this->last_query_text[$query_id] != '') 181 175 { 182 if (preg_match(' /^INSERT INTO ([a-z0-9\_\-]+)/is', $this->last_query_text[$query_id], $table_name))176 if (preg_match('%^INSERT INTO ([a-z0-9\_\-]+)%is', $this->last_query_text[$query_id], $table_name)) 183 177 { 184 178 // Hack (don't ask) … … 218 212 function escape($str) 219 213 { 220 return pg_escape_string($str);214 return is_array($str) ? '' : pg_escape_string($str); 221 215 } 222 216 … … 225 219 { 226 220 $result['error_sql'] = @current(@end($this->saved_queries)); 227 $result['error_no'] = false; 228 /* 229 if (!empty($this->query_result)) 230 { 231 $result['error_msg'] = trim(@pg_result_error($this->query_result)); 232 if ($result['error_msg'] != '') 233 return $result; 234 } 235 236 $result['error_msg'] = (!empty($this->link_id)) ? trim(@pg_last_error($this->link_id)) : trim(@pg_last_error()); 237 */ 221 $result['error_no'] = $this->error_no; 238 222 $result['error_msg'] = $this->error_msg; 239 223 … … 262 246 return false; 263 247 } 248 249 250 function get_names() 251 { 252 $result = $this->query('SHOW client_encoding'); 253 return strtolower($this->result($result)); // MySQL returns lowercase so lets be consistent 254 } 255 256 257 function set_names($names) 258 { 259 return $this->query('SET NAMES \''.$this->escape($names).'\''); 260 } 261 262 263 function get_version() 264 { 265 $result = $this->query('SELECT VERSION()'); 266 267 return array( 268 'name' => 'PostgreSQL', 269 'version' => preg_replace('%^[^0-9]+([^\s,-]+).*$%', '\\1', $this->result($result)) 270 ); 271 } 272 273 274 function table_exists($table_name, $no_prefix = false) 275 { 276 $result = $this->query('SELECT 1 FROM pg_class WHERE relname = \''.($no_prefix ? '' : $this->prefix).$this->escape($table_name).'\''); 277 return $this->num_rows($result) > 0; 278 } 279 280 281 function field_exists($table_name, $field_name, $no_prefix = false) 282 { 283 $result = $this->query('SELECT 1 FROM pg_class c INNER JOIN pg_attribute a ON a.attrelid = c.oid WHERE c.relname = \''.($no_prefix ? '' : $this->prefix).$this->escape($table_name).'\' AND a.attname = \''.$this->escape($field_name).'\''); 284 return $this->num_rows($result) > 0; 285 } 286 287 288 function index_exists($table_name, $index_name, $no_prefix = false) 289 { 290 $result = $this->query('SELECT 1 FROM pg_index i INNER JOIN pg_class c1 ON c1.oid = i.indrelid INNER JOIN pg_class c2 ON c2.oid = i.indexrelid WHERE c1.relname = \''.($no_prefix ? '' : $this->prefix).$this->escape($table_name).'\' AND c2.relname = \''.($no_prefix ? '' : $this->prefix).$this->escape($table_name).'_'.$this->escape($index_name).'\''); 291 return $this->num_rows($result) > 0; 292 } 293 294 295 function create_table($table_name, $schema, $no_prefix = false) 296 { 297 if ($this->table_exists($table_name, $no_prefix)) 298 return true; 299 300 $query = 'CREATE TABLE '.($no_prefix ? '' : $this->prefix).$table_name." (\n"; 301 302 // Go through every schema element and add it to the query 303 foreach ($schema['FIELDS'] as $field_name => $field_data) 304 { 305 $field_data['datatype'] = preg_replace(array_keys($this->datatype_transformations), array_values($this->datatype_transformations), $field_data['datatype']); 306 307 $query .= $field_name.' '.$field_data['datatype']; 308 309 // The SERIAL datatype is a special case where we don't need to say not null 310 if (!$field_data['allow_null'] && $field_data['datatype'] != 'SERIAL') 311 $query .= ' NOT NULL'; 312 313 if (isset($field_data['default'])) 314 $query .= ' DEFAULT '.$field_data['default']; 315 316 $query .= ",\n"; 317 } 318 319 // If we have a primary key, add it 320 if (isset($schema['PRIMARY KEY'])) 321 $query .= 'PRIMARY KEY ('.implode(',', $schema['PRIMARY KEY']).'),'."\n"; 322 323 // Add unique keys 324 if (isset($schema['UNIQUE KEYS'])) 325 { 326 foreach ($schema['UNIQUE KEYS'] as $key_name => $key_fields) 327 $query .= 'UNIQUE ('.implode(',', $key_fields).'),'."\n"; 328 } 329 330 // We remove the last two characters (a newline and a comma) and add on the ending 331 $query = substr($query, 0, strlen($query) - 2)."\n".')'; 332 333 $result = $this->query($query) ? true : false; 334 335 // Add indexes 336 if (isset($schema['INDEXES'])) 337 { 338 foreach ($schema['INDEXES'] as $index_name => $index_fields) 339 $result &= $this->add_index($table_name, $index_name, $index_fields, false, $no_prefix); 340 } 341 342 return $result; 343 } 344 345 346 function drop_table($table_name, $no_prefix = false) 347 { 348 if (!$this->table_exists($table_name, $no_prefix)) 349 return true; 350 351 return $this->query('DROP TABLE '.($no_prefix ? '' : $this->prefix).$table_name) ? true : false; 352 } 353 354 355 function rename_table($old_table, $new_table, $no_prefix = false) 356 { 357 // If there new table exists and the old one doesn't, then we're happy 358 if ($this->table_exists($new_table, $no_prefix) && !$this->table_exists($old_table, $no_prefix)) 359 return true; 360 361 return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$old_table.' RENAME TO '.($no_prefix ? '' : $this->prefix).$new_table) ? true : false; 362 } 363 364 365 function add_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false) 366 { 367 if ($this->field_exists($table_name, $field_name, $no_prefix)) 368 return true; 369 370 $field_type = preg_replace(array_keys($this->datatype_transformations), array_values($this->datatype_transformations), $field_type); 371 372 $result = $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' ADD '.$field_name.' '.$field_type) ? true : false; 373 374 if ($default_value !== null) 375 { 376 if (!is_int($default_value) && !is_float($default_value)) 377 $default_value = '\''.$this->escape($default_value).'\''; 378 379 $result &= $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' ALTER '.$field_name.' SET DEFAULT '.$default_value) ? true : false; 380 $result &= $this->query('UPDATE '.($no_prefix ? '' : $this->prefix).$table_name.' SET '.$field_name.'='.$default_value) ? true : false; 381 } 382 383 if (!$allow_null) 384 $result &= $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' ALTER '.$field_name.' SET NOT NULL') ? true : false; 385 386 return $result; 387 } 388 389 390 function alter_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false) 391 { 392 if (!$this->field_exists($table_name, $field_name, $no_prefix)) 393 return true; 394 395 $field_type = preg_replace(array_keys($this->datatype_transformations), array_values($this->datatype_transformations), $field_type); 396 397 $result = $this->add_field($table_name, 'tmp_'.$field_name, $field_type, $allow_null, $default_value, $after_field, $no_prefix); 398 $result &= $this->query('UPDATE '.($no_prefix ? '' : $this->prefix).$table_name.' SET tmp_'.$field_name.' = '.$field_name) ? true : false; 399 $result &= $this->drop_field($table_name, $field_name, $no_prefix); 400 $result &= $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' RENAME COLUMN tmp_'.$field_name.' TO '.$field_name) ? true : false; 401 402 return $result; 403 } 404 405 406 function drop_field($table_name, $field_name, $no_prefix = false) 407 { 408 if (!$this->field_exists($table_name, $field_name, $no_prefix)) 409 return true; 410 411 return $this->query('ALTER TABLE '.($no_prefix ? '' : $this->prefix).$table_name.' DROP '.$field_name) ? true : false; 412 } 413 414 415 function add_index($table_name, $index_name, $index_fields, $unique = false, $no_prefix = false) 416 { 417 if ($this->index_exists($table_name, $index_name, $no_prefix)) 418 return true; 419 420 return $this->query('CREATE '.($unique ? 'UNIQUE ' : '').'INDEX '.($no_prefix ? '' : $this->prefix).$table_name.'_'.$index_name.' ON '.($no_prefix ? '' : $this->prefix).$table_name.'('.implode(',', $index_fields).')') ? true : false; 421 } 422 423 424 function drop_index($table_name, $index_name, $no_prefix = false) 425 { 426 if (!$this->index_exists($table_name, $index_name, $no_prefix)) 427 return true; 428 429 return $this->query('DROP INDEX '.($no_prefix ? '' : $this->prefix).$table_name.'_'.$index_name) ? true : false; 430 } 431 432 function truncate_table($table_name, $no_prefix = false) 433 { 434 return $this->query('DELETE FROM '.($no_prefix ? '' : $this->prefix).$table_name) ? true : false; 435 } 264 436 }
Note: See TracChangeset
for help on using the changeset viewer.
