blob: 89c0cd0c980ba17412a0f5dc105beb41cf8fd6a1 [file] [log] [blame]
Andrey Andreev214583f2012-01-26 16:39:09 +02001<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Derek Allard2067d1a2008-11-13 22:59:24 +00002/**
3 * CodeIgniter
4 *
Greg Aker741de1c2010-11-10 14:52:57 -06005 * An open source application development framework for PHP 5.1.6 or newer
Derek Allard2067d1a2008-11-13 22:59:24 +00006 *
Derek Jonesf4a4bd82011-10-20 12:18:42 -05007 * NOTICE OF LICENSE
Andrey Andreev214583f2012-01-26 16:39:09 +02008 *
Derek Jonesf4a4bd82011-10-20 12:18:42 -05009 * Licensed under the Open Software License version 3.0
Andrey Andreev214583f2012-01-26 16:39:09 +020010 *
Derek Jonesf4a4bd82011-10-20 12:18:42 -050011 * This source file is subject to the Open Software License (OSL 3.0) that is
12 * bundled with this package in the files license.txt / license.rst. It is
13 * also available through the world wide web at this URL:
14 * http://opensource.org/licenses/OSL-3.0
15 * If you did not receive a copy of the license and are unable to obtain it
16 * through the world wide web, please send an email to
17 * licensing@ellislab.com so we can send you a copy immediately.
18 *
Derek Allard2067d1a2008-11-13 22:59:24 +000019 * @package CodeIgniter
Derek Jonesf4a4bd82011-10-20 12:18:42 -050020 * @author EllisLab Dev Team
Greg Aker0defe5d2012-01-01 18:46:41 -060021 * @copyright Copyright (c) 2008 - 2012, EllisLab, Inc. (http://ellislab.com/)
Derek Jonesf4a4bd82011-10-20 12:18:42 -050022 * @license http://opensource.org/licenses/OSL-3.0 Open Software License (OSL 3.0)
Derek Allard2067d1a2008-11-13 22:59:24 +000023 * @link http://codeigniter.com
24 * @since Version 1.0
25 * @filesource
26 */
27
Derek Allard2067d1a2008-11-13 22:59:24 +000028/**
29 * Postgre Database Adapter Class
30 *
31 * Note: _DB is an extender class that the app controller
32 * creates dynamically based on whether the active record
33 * class is being used or not.
34 *
35 * @package CodeIgniter
36 * @subpackage Drivers
37 * @category Database
Derek Jonesf4a4bd82011-10-20 12:18:42 -050038 * @author EllisLab Dev Team
Derek Allard2067d1a2008-11-13 22:59:24 +000039 * @link http://codeigniter.com/user_guide/database/
40 */
41class CI_DB_postgre_driver extends CI_DB {
42
Andrey Andreev214583f2012-01-26 16:39:09 +020043 public $dbdriver = 'postgre';
Barry Mienydd671972010-10-04 16:33:58 +020044
Andrey Andreev214583f2012-01-26 16:39:09 +020045 protected $_escape_char = '"';
Derek Allard2067d1a2008-11-13 22:59:24 +000046
Derek Jonese4ed5832009-02-20 21:44:59 +000047 // clause and character used for LIKE escape sequences
Andrey Andreev214583f2012-01-26 16:39:09 +020048 protected $_like_escape_str = ' ESCAPE \'%s\' ';
49 protected $_like_escape_chr = '!';
Derek Jonese4ed5832009-02-20 21:44:59 +000050
Derek Allard2067d1a2008-11-13 22:59:24 +000051 /**
52 * The syntax to count rows is slightly different across different
53 * database engines, so this string appears in each driver and is
54 * used for the count_all() and count_all_results() functions.
55 */
Andrey Andreev214583f2012-01-26 16:39:09 +020056 protected $_count_string = 'SELECT COUNT(*) AS ';
57 protected $_random_keyword = ' RANDOM()'; // database specific random keyword
58
Derek Allard2067d1a2008-11-13 22:59:24 +000059 /**
Andrey Andreev214583f2012-01-26 16:39:09 +020060 * Constructor
Derek Allard2067d1a2008-11-13 22:59:24 +000061 *
Andrey Andreev214583f2012-01-26 16:39:09 +020062 * Creates a DSN string to be used for db_connect() and db_pconnect()
63 *
64 * @return void
Barry Mienydd671972010-10-04 16:33:58 +020065 */
Andrey Andreev2b3edbd2012-01-27 21:02:53 +020066 public function __construct($params)
Derek Allard2067d1a2008-11-13 22:59:24 +000067 {
Andrey Andreev214583f2012-01-26 16:39:09 +020068 parent::__construct($params);
Barry Mienydd671972010-10-04 16:33:58 +020069
Andrey Andreeva64c61a2012-02-12 22:26:46 +020070 if ( ! empty($this->dsn))
Derek Allard2067d1a2008-11-13 22:59:24 +000071 {
Andrey Andreeva64c61a2012-02-12 22:26:46 +020072 return;
73 }
74
75 $this->dsn === '' OR $this->dsn = '';
76
77 if (strpos($this->hostname, '/') !== FALSE)
78 {
79 // If UNIX sockets are used, we shouldn't set a port
80 $this->port = '';
81 }
82
83 $this->hostname === '' OR $this->dsn = 'host='.$this->hostname;
84
85 if ( ! empty($this->port) && ctype_digit($this->port))
86 {
87 $this->dsn .= 'host='.$this->port.' ';
88 }
89
90 if ($this->username !== '')
91 {
92 $this->dsn .= 'username='.$this->username.' ';
93
94 /* An empty password is valid!
95 *
96 * $db['password'] = NULL must be done in order to ignore it.
97 */
98 $this->password === NULL OR $this->dsn .= "password='".$this->password."' ";
99 }
100
101 $this->database === '' OR $this->dsn .= 'dbname='.$this->database.' ';
102
103 /* We don't have these options as elements in our standard configuration
104 * array, but they might be set by parse_url() if the configuration was
105 * provided via string. Example:
106 *
107 * postgre://username:password@localhost:5432/database?connect_timeout=5&sslmode=1
108 */
109 foreach (array('connect_timeout', 'options', 'sslmode', 'service') as $key)
110 {
111 if (isset($this->$key) && is_string($this->key) && $this->key !== '')
Derek Allard2067d1a2008-11-13 22:59:24 +0000112 {
Andrey Andreeva64c61a2012-02-12 22:26:46 +0200113 $this->dsn .= $key."='".$this->key."' ";
Derek Allard2067d1a2008-11-13 22:59:24 +0000114 }
115 }
Andrey Andreev214583f2012-01-26 16:39:09 +0200116
Andrey Andreeva64c61a2012-02-12 22:26:46 +0200117 $this->dsn = rtrim($this->dsn);
Derek Allard2067d1a2008-11-13 22:59:24 +0000118 }
119
120 // --------------------------------------------------------------------
121
122 /**
123 * Non-persistent database connection
124 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000125 * @return resource
Barry Mienydd671972010-10-04 16:33:58 +0200126 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200127 public function db_connect()
Barry Mienydd671972010-10-04 16:33:58 +0200128 {
Andrey Andreeva64c61a2012-02-12 22:26:46 +0200129 $ret = @pg_connect($this->dsn);
Andrey Andreev214583f2012-01-26 16:39:09 +0200130 if (is_resource($ret) && $this->char_set != '')
131 {
132 pg_set_client_encoding($ret, $this->char_set);
133 }
134
135 return $ret;
Derek Allard2067d1a2008-11-13 22:59:24 +0000136 }
137
138 // --------------------------------------------------------------------
139
140 /**
141 * Persistent database connection
142 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000143 * @return resource
Barry Mienydd671972010-10-04 16:33:58 +0200144 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200145 public function db_pconnect()
Derek Allard2067d1a2008-11-13 22:59:24 +0000146 {
Andrey Andreeva64c61a2012-02-12 22:26:46 +0200147 $ret = @pg_pconnect($this->dsn);
Andrey Andreev214583f2012-01-26 16:39:09 +0200148 if (is_resource($ret) && $this->char_set != '')
149 {
150 pg_set_client_encoding($ret, $this->char_set);
151 }
152
153 return $ret;
Derek Allard2067d1a2008-11-13 22:59:24 +0000154 }
Barry Mienydd671972010-10-04 16:33:58 +0200155
Derek Allard2067d1a2008-11-13 22:59:24 +0000156 // --------------------------------------------------------------------
157
158 /**
Derek Jones87cbafc2009-02-27 16:29:59 +0000159 * Reconnect
160 *
161 * Keep / reestablish the db connection if no queries have been
162 * sent for a length of time exceeding the server's idle timeout
163 *
Derek Jones87cbafc2009-02-27 16:29:59 +0000164 * @return void
165 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200166 public function reconnect()
Derek Jones87cbafc2009-02-27 16:29:59 +0000167 {
168 if (pg_ping($this->conn_id) === FALSE)
169 {
170 $this->conn_id = FALSE;
171 }
172 }
173
174 // --------------------------------------------------------------------
175
176 /**
Derek Allard2067d1a2008-11-13 22:59:24 +0000177 * Select the database
178 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000179 * @return resource
Barry Mienydd671972010-10-04 16:33:58 +0200180 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200181 public function db_select()
Derek Allard2067d1a2008-11-13 22:59:24 +0000182 {
183 // Not needed for Postgre so we'll return TRUE
184 return TRUE;
185 }
186
187 // --------------------------------------------------------------------
188
189 /**
190 * Set client character set
191 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000192 * @param string
193 * @param string
Andrey Andreev214583f2012-01-26 16:39:09 +0200194 * @return bool
Derek Allard2067d1a2008-11-13 22:59:24 +0000195 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200196 public function db_set_charset($charset, $collation)
Derek Allard2067d1a2008-11-13 22:59:24 +0000197 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200198 return (pg_set_client_encoding($this->conn_id, $charset) === 0);
199 }
200
201 // --------------------------------------------------------------------
202
203 /**
204 * Database version
205 *
206 * This method overrides the one from the base class, as if PHP was
207 * compiled with PostgreSQL version >= 7.4 libraries, we have a native
208 * function to get the server's version.
209 *
210 * @return string
211 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200212 public function version()
213 {
214 $version = pg_version($this->conn_id);
215
216 /* If we don't have the server version string - fall back to
217 * the base driver class' method
218 */
219 return isset($version['server']) ? $version['server'] : parent::version();
Derek Allard2067d1a2008-11-13 22:59:24 +0000220 }
221
222 // --------------------------------------------------------------------
Barry Mienydd671972010-10-04 16:33:58 +0200223
Derek Allard2067d1a2008-11-13 22:59:24 +0000224 /**
225 * Version number query string
226 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000227 * @return string
228 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200229 protected function _version()
Derek Allard2067d1a2008-11-13 22:59:24 +0000230 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200231 return 'SELECT version() AS ver';
Derek Allard2067d1a2008-11-13 22:59:24 +0000232 }
233
234 // --------------------------------------------------------------------
235
236 /**
237 * Execute the query
238 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000239 * @param string an SQL query
240 * @return resource
Barry Mienydd671972010-10-04 16:33:58 +0200241 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200242 protected function _execute($sql)
Derek Allard2067d1a2008-11-13 22:59:24 +0000243 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200244 return @pg_query($this->conn_id, $this->_prep_query($sql));
Derek Allard2067d1a2008-11-13 22:59:24 +0000245 }
Barry Mienydd671972010-10-04 16:33:58 +0200246
Derek Allard2067d1a2008-11-13 22:59:24 +0000247 // --------------------------------------------------------------------
248
249 /**
250 * Prep the query
251 *
252 * If needed, each database adapter can prep the query string
253 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000254 * @param string an SQL query
255 * @return string
Barry Mienydd671972010-10-04 16:33:58 +0200256 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200257 protected function _prep_query($sql)
Derek Allard2067d1a2008-11-13 22:59:24 +0000258 {
259 return $sql;
260 }
261
262 // --------------------------------------------------------------------
263
264 /**
265 * Begin Transaction
266 *
Andrey Andreev214583f2012-01-26 16:39:09 +0200267 * @param bool
Barry Mienydd671972010-10-04 16:33:58 +0200268 * @return bool
269 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200270 public function trans_begin($test_mode = FALSE)
Derek Allard2067d1a2008-11-13 22:59:24 +0000271 {
Derek Allard2067d1a2008-11-13 22:59:24 +0000272 // When transactions are nested we only begin/commit/rollback the outermost ones
Andrey Andreev214583f2012-01-26 16:39:09 +0200273 if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
Derek Allard2067d1a2008-11-13 22:59:24 +0000274 {
275 return TRUE;
276 }
277
278 // Reset the transaction failure flag.
279 // If the $test_mode flag is set to TRUE transactions will be rolled back
280 // even if the queries produce a successful result.
Andrey Andreev214583f2012-01-26 16:39:09 +0200281 $this->_trans_failure = ($test_mode === TRUE);
Derek Allard2067d1a2008-11-13 22:59:24 +0000282
Andrey Andreev214583f2012-01-26 16:39:09 +0200283 return (bool) @pg_query($this->conn_id, 'BEGIN');
Derek Allard2067d1a2008-11-13 22:59:24 +0000284 }
285
286 // --------------------------------------------------------------------
287
288 /**
289 * Commit Transaction
290 *
Barry Mienydd671972010-10-04 16:33:58 +0200291 * @return bool
292 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200293 public function trans_commit()
Derek Allard2067d1a2008-11-13 22:59:24 +0000294 {
Derek Allard2067d1a2008-11-13 22:59:24 +0000295 // When transactions are nested we only begin/commit/rollback the outermost ones
Andrey Andreev214583f2012-01-26 16:39:09 +0200296 if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
Derek Allard2067d1a2008-11-13 22:59:24 +0000297 {
298 return TRUE;
299 }
300
Andrey Andreev214583f2012-01-26 16:39:09 +0200301 return (bool) @pg_query($this->conn_id, 'COMMIT');
Derek Allard2067d1a2008-11-13 22:59:24 +0000302 }
303
304 // --------------------------------------------------------------------
305
306 /**
307 * Rollback Transaction
308 *
Barry Mienydd671972010-10-04 16:33:58 +0200309 * @return bool
310 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200311 public function trans_rollback()
Derek Allard2067d1a2008-11-13 22:59:24 +0000312 {
Derek Allard2067d1a2008-11-13 22:59:24 +0000313 // When transactions are nested we only begin/commit/rollback the outermost ones
Andrey Andreev214583f2012-01-26 16:39:09 +0200314 if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
Derek Allard2067d1a2008-11-13 22:59:24 +0000315 {
316 return TRUE;
317 }
318
Andrey Andreev214583f2012-01-26 16:39:09 +0200319 return (bool) @pg_query($this->conn_id, 'ROLLBACK');
Derek Allard2067d1a2008-11-13 22:59:24 +0000320 }
321
322 // --------------------------------------------------------------------
323
324 /**
325 * Escape String
326 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000327 * @param string
Derek Jonese4ed5832009-02-20 21:44:59 +0000328 * @param bool whether or not the string will be used in a LIKE condition
Derek Allard2067d1a2008-11-13 22:59:24 +0000329 * @return string
330 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200331 public function escape_str($str, $like = FALSE)
Derek Jonese4ed5832009-02-20 21:44:59 +0000332 {
333 if (is_array($str))
334 {
Pascal Krietec3a4a8d2011-02-14 13:40:08 -0500335 foreach ($str as $key => $val)
Barry Mienydd671972010-10-04 16:33:58 +0200336 {
Derek Jonese4ed5832009-02-20 21:44:59 +0000337 $str[$key] = $this->escape_str($val, $like);
Barry Mienydd671972010-10-04 16:33:58 +0200338 }
339
340 return $str;
341 }
Derek Jonese4ed5832009-02-20 21:44:59 +0000342
343 $str = pg_escape_string($str);
Barry Mienydd671972010-10-04 16:33:58 +0200344
Derek Jonese4ed5832009-02-20 21:44:59 +0000345 // escape LIKE condition wildcards
346 if ($like === TRUE)
347 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200348 return str_replace(array('%', '_', $this->_like_escape_chr),
349 array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
350 $str);
Derek Jonese4ed5832009-02-20 21:44:59 +0000351 }
Barry Mienydd671972010-10-04 16:33:58 +0200352
Derek Jonese4ed5832009-02-20 21:44:59 +0000353 return $str;
Derek Allard2067d1a2008-11-13 22:59:24 +0000354 }
Barry Mienydd671972010-10-04 16:33:58 +0200355
Derek Allard2067d1a2008-11-13 22:59:24 +0000356 // --------------------------------------------------------------------
357
358 /**
359 * Affected Rows
360 *
Andrey Andreev214583f2012-01-26 16:39:09 +0200361 * @return int
Derek Allard2067d1a2008-11-13 22:59:24 +0000362 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200363 public function affected_rows()
Derek Allard2067d1a2008-11-13 22:59:24 +0000364 {
365 return @pg_affected_rows($this->result_id);
366 }
Barry Mienydd671972010-10-04 16:33:58 +0200367
Derek Allard2067d1a2008-11-13 22:59:24 +0000368 // --------------------------------------------------------------------
369
370 /**
371 * Insert ID
372 *
Andrey Andreev214583f2012-01-26 16:39:09 +0200373 * @return int
Derek Allard2067d1a2008-11-13 22:59:24 +0000374 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200375 public function insert_id()
Derek Allard2067d1a2008-11-13 22:59:24 +0000376 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200377 $v = pg_version($this->conn_id);
378 $v = isset($v['server']) ? $v['server'] : 0; // 'server' key is only available since PosgreSQL 7.4
Barry Mienydd671972010-10-04 16:33:58 +0200379
Andrey Andreev214583f2012-01-26 16:39:09 +0200380 $table = (func_num_args() > 0) ? func_get_arg(0) : NULL;
381 $column = (func_num_args() > 1) ? func_get_arg(1) : NULL;
Barry Mienydd671972010-10-04 16:33:58 +0200382
Pascal Kriete8761ef52011-02-14 13:13:52 -0500383 if ($table == NULL && $v >= '8.1')
Derek Allard2067d1a2008-11-13 22:59:24 +0000384 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200385 $sql = 'SELECT LASTVAL() AS ins_id';
Derek Allard2067d1a2008-11-13 22:59:24 +0000386 }
Pascal Kriete8761ef52011-02-14 13:13:52 -0500387 elseif ($table != NULL)
Derek Allard2067d1a2008-11-13 22:59:24 +0000388 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200389 if ($column != NULL && $v >= '8.0')
390 {
391 $sql = 'SELECT pg_get_serial_sequence(\''.$table."', '".$column."') AS seq";
392 $query = $this->query($sql);
393 $query = $query->row();
394 $seq = $query->seq;
395 }
396 else
397 {
398 // seq_name passed in table parameter
399 $seq = $table;
400 }
401
402 $sql = 'SELECT CURRVAL(\''.$seq."') AS ins_id";
Derek Allard2067d1a2008-11-13 22:59:24 +0000403 }
404 else
405 {
406 return pg_last_oid($this->result_id);
407 }
Andrey Andreev214583f2012-01-26 16:39:09 +0200408
Derek Allard2067d1a2008-11-13 22:59:24 +0000409 $query = $this->query($sql);
Andrey Andreev214583f2012-01-26 16:39:09 +0200410 $query = $query->row();
411 return (int) $query->ins_id;
Derek Allard2067d1a2008-11-13 22:59:24 +0000412 }
413
414 // --------------------------------------------------------------------
415
416 /**
417 * "Count All" query
418 *
419 * Generates a platform-specific query string that counts all records in
420 * the specified database
421 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000422 * @param string
423 * @return string
424 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200425 public function count_all($table = '')
Derek Allard2067d1a2008-11-13 22:59:24 +0000426 {
427 if ($table == '')
Derek Allarde37ab382009-02-03 16:13:57 +0000428 {
429 return 0;
430 }
Derek Allard2067d1a2008-11-13 22:59:24 +0000431
Andrey Andreev214583f2012-01-26 16:39:09 +0200432 $query = $this->query($this->_count_string.$this->_protect_identifiers('numrows').' FROM '.$this->_protect_identifiers($table, TRUE, NULL, FALSE));
Derek Allard2067d1a2008-11-13 22:59:24 +0000433 if ($query->num_rows() == 0)
Derek Allarde37ab382009-02-03 16:13:57 +0000434 {
435 return 0;
436 }
Derek Allard2067d1a2008-11-13 22:59:24 +0000437
Andrey Andreev214583f2012-01-26 16:39:09 +0200438 $query = $query->row();
Greg Aker90248ab2011-08-20 14:23:14 -0500439 $this->_reset_select();
Andrey Andreev214583f2012-01-26 16:39:09 +0200440 return (int) $query->numrows;
Derek Allard2067d1a2008-11-13 22:59:24 +0000441 }
442
443 // --------------------------------------------------------------------
444
445 /**
446 * Show table query
447 *
448 * Generates a platform-specific query string so that the table names can be fetched
449 *
Andrey Andreev214583f2012-01-26 16:39:09 +0200450 * @param bool
Derek Allard2067d1a2008-11-13 22:59:24 +0000451 * @return string
452 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200453 protected function _list_tables($prefix_limit = FALSE)
Barry Mienydd671972010-10-04 16:33:58 +0200454 {
455 $sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
456
Andrey Andreev214583f2012-01-26 16:39:09 +0200457 if ($prefix_limit !== FALSE && $this->dbprefix != '')
Derek Allard2067d1a2008-11-13 22:59:24 +0000458 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200459 return $sql." AND table_name LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr);
Derek Allard2067d1a2008-11-13 22:59:24 +0000460 }
Barry Mienydd671972010-10-04 16:33:58 +0200461
Derek Allard2067d1a2008-11-13 22:59:24 +0000462 return $sql;
463 }
Barry Mienydd671972010-10-04 16:33:58 +0200464
Derek Allard2067d1a2008-11-13 22:59:24 +0000465 // --------------------------------------------------------------------
466
467 /**
468 * Show column query
469 *
470 * Generates a platform-specific query string so that the column names can be fetched
471 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000472 * @param string the table name
473 * @return string
474 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200475 protected function _list_columns($table = '')
Derek Allard2067d1a2008-11-13 22:59:24 +0000476 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200477 return "SELECT column_name FROM information_schema.columns WHERE table_name = '".$table."'";
Derek Allard2067d1a2008-11-13 22:59:24 +0000478 }
479
480 // --------------------------------------------------------------------
481
482 /**
483 * Field data query
484 *
485 * Generates a platform-specific query so that the column data can be retrieved
486 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000487 * @param string the table name
Andrey Andreev214583f2012-01-26 16:39:09 +0200488 * @return string
Derek Allard2067d1a2008-11-13 22:59:24 +0000489 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200490 protected function _field_data($table)
Derek Allard2067d1a2008-11-13 22:59:24 +0000491 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200492 return 'SELECT * FROM '.$table.' LIMIT 1';
Derek Allard2067d1a2008-11-13 22:59:24 +0000493 }
494
495 // --------------------------------------------------------------------
496
497 /**
498 * The error message string
499 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000500 * @return string
501 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200502 protected function _error_message()
Derek Allard2067d1a2008-11-13 22:59:24 +0000503 {
504 return pg_last_error($this->conn_id);
505 }
Barry Mienydd671972010-10-04 16:33:58 +0200506
Derek Allard2067d1a2008-11-13 22:59:24 +0000507 // --------------------------------------------------------------------
508
509 /**
510 * The error message number
511 *
Andrey Andreev214583f2012-01-26 16:39:09 +0200512 * @return string
Derek Allard2067d1a2008-11-13 22:59:24 +0000513 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200514 protected function _error_number()
Derek Allard2067d1a2008-11-13 22:59:24 +0000515 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200516 // Not supported in Postgre
Derek Allard2067d1a2008-11-13 22:59:24 +0000517 return '';
518 }
519
520 // --------------------------------------------------------------------
521
522 /**
523 * Escape the SQL Identifiers
524 *
525 * This function escapes column and table names
526 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000527 * @param string
528 * @return string
529 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200530 public function _escape_identifiers($item)
Derek Allard2067d1a2008-11-13 22:59:24 +0000531 {
532 if ($this->_escape_char == '')
533 {
534 return $item;
535 }
536
537 foreach ($this->_reserved_identifiers as $id)
538 {
539 if (strpos($item, '.'.$id) !== FALSE)
540 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200541 $item = str_replace('.', $this->_escape_char.'.', $item);
Barry Mienydd671972010-10-04 16:33:58 +0200542
Derek Allard2067d1a2008-11-13 22:59:24 +0000543 // remove duplicates if the user already included the escape
Andrey Andreev214583f2012-01-26 16:39:09 +0200544 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $this->_escape_char.$item);
Barry Mienydd671972010-10-04 16:33:58 +0200545 }
Derek Allard2067d1a2008-11-13 22:59:24 +0000546 }
Barry Mienydd671972010-10-04 16:33:58 +0200547
Derek Allard2067d1a2008-11-13 22:59:24 +0000548 if (strpos($item, '.') !== FALSE)
549 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200550 $item = str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item);
Derek Allard2067d1a2008-11-13 22:59:24 +0000551 }
Barry Mienydd671972010-10-04 16:33:58 +0200552
Derek Allard2067d1a2008-11-13 22:59:24 +0000553 // remove duplicates if the user already included the escape
Andrey Andreev214583f2012-01-26 16:39:09 +0200554 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $this->_escape_char.$item.$this->_escape_char);
Derek Allard2067d1a2008-11-13 22:59:24 +0000555 }
Barry Mienydd671972010-10-04 16:33:58 +0200556
Derek Allard2067d1a2008-11-13 22:59:24 +0000557 // --------------------------------------------------------------------
558
559 /**
560 * From Tables
561 *
562 * This function implicitly groups FROM tables so there is no confusion
563 * about operator precedence in harmony with SQL standards
564 *
Andrey Andreev214583f2012-01-26 16:39:09 +0200565 * @param string table name
566 * @return string
Derek Allard2067d1a2008-11-13 22:59:24 +0000567 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200568 protected function _from_tables($tables)
Derek Allard2067d1a2008-11-13 22:59:24 +0000569 {
570 if ( ! is_array($tables))
571 {
572 $tables = array($tables);
573 }
Barry Mienydd671972010-10-04 16:33:58 +0200574
Derek Allard2067d1a2008-11-13 22:59:24 +0000575 return implode(', ', $tables);
576 }
577
578 // --------------------------------------------------------------------
Barry Mienydd671972010-10-04 16:33:58 +0200579
Derek Allard2067d1a2008-11-13 22:59:24 +0000580 /**
581 * Insert statement
582 *
583 * Generates a platform-specific insert string from the supplied data
584 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000585 * @param string the table name
586 * @param array the insert keys
587 * @param array the insert values
588 * @return string
589 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200590 protected function _insert($table, $keys, $values)
Barry Mienydd671972010-10-04 16:33:58 +0200591 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200592 return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')';
Derek Allard2067d1a2008-11-13 22:59:24 +0000593 }
Barry Mienydd671972010-10-04 16:33:58 +0200594
Derek Allard2067d1a2008-11-13 22:59:24 +0000595 // --------------------------------------------------------------------
596
597 /**
Greg Aker60ef4ea2011-04-27 01:45:38 -0500598 * Insert_batch statement
599 *
600 * Generates a platform-specific insert string from the supplied data
601 *
Greg Aker60ef4ea2011-04-27 01:45:38 -0500602 * @param string the table name
603 * @param array the insert keys
604 * @param array the insert values
605 * @return string
606 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200607 protected function _insert_batch($table, $keys, $values)
Greg Aker60ef4ea2011-04-27 01:45:38 -0500608 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200609 return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values);
Greg Aker60ef4ea2011-04-27 01:45:38 -0500610 }
611
612 // --------------------------------------------------------------------
613
614 /**
Derek Allard2067d1a2008-11-13 22:59:24 +0000615 * Update statement
616 *
617 * Generates a platform-specific update string from the supplied data
618 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000619 * @param string the table name
620 * @param array the update data
621 * @param array the where clause
622 * @param array the orderby clause
623 * @param array the limit clause
624 * @return string
625 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200626 protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
Derek Allard2067d1a2008-11-13 22:59:24 +0000627 {
Pascal Krietec3a4a8d2011-02-14 13:40:08 -0500628 foreach ($values as $key => $val)
Derek Allard2067d1a2008-11-13 22:59:24 +0000629 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200630 $valstr[] = $key.' = '.$val;
Derek Allard2067d1a2008-11-13 22:59:24 +0000631 }
Barry Mienydd671972010-10-04 16:33:58 +0200632
Andrey Andreev214583f2012-01-26 16:39:09 +0200633 return 'UPDATE '.$table.' SET '.implode(', ', $valstr)
634 .(($where != '' && count($where) > 0) ? ' WHERE '.implode(' ', $where) : '')
635 .(count($orderby) > 0 ? ' ORDER BY '.implode(', ', $orderby) : '')
636 .( ! $limit ? '' : ' LIMIT '.$limit);
Derek Allard2067d1a2008-11-13 22:59:24 +0000637 }
638
639 // --------------------------------------------------------------------
640
641 /**
642 * Truncate statement
643 *
644 * Generates a platform-specific truncate string from the supplied data
645 * If the database does not support the truncate() command
646 * This function maps to "DELETE FROM table"
647 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000648 * @param string the table name
649 * @return string
Barry Mienydd671972010-10-04 16:33:58 +0200650 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200651 protected function _truncate($table)
Derek Allard2067d1a2008-11-13 22:59:24 +0000652 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200653 return 'TRUNCATE '.$table;
Derek Allard2067d1a2008-11-13 22:59:24 +0000654 }
Barry Mienydd671972010-10-04 16:33:58 +0200655
Derek Allard2067d1a2008-11-13 22:59:24 +0000656 // --------------------------------------------------------------------
657
658 /**
659 * Delete statement
660 *
661 * Generates a platform-specific delete string from the supplied data
662 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000663 * @param string the table name
664 * @param array the where clause
665 * @param string the limit clause
666 * @return string
Barry Mienydd671972010-10-04 16:33:58 +0200667 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200668 protected function _delete($table, $where = array(), $like = array(), $limit = FALSE)
Derek Allard2067d1a2008-11-13 22:59:24 +0000669 {
670 $conditions = '';
671
672 if (count($where) > 0 OR count($like) > 0)
673 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200674 $conditions = "\nWHERE ".implode("\n", $this->ar_where);
Derek Allard2067d1a2008-11-13 22:59:24 +0000675
676 if (count($where) > 0 && count($like) > 0)
677 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200678 $conditions .= ' AND ';
Derek Allard2067d1a2008-11-13 22:59:24 +0000679 }
680 $conditions .= implode("\n", $like);
681 }
682
Andrey Andreev214583f2012-01-26 16:39:09 +0200683 return 'DELETE FROM '.$table.$conditions.( ! $limit ? '' : ' LIMIT '.$limit);
Derek Allard2067d1a2008-11-13 22:59:24 +0000684 }
685
686 // --------------------------------------------------------------------
687 /**
688 * Limit string
689 *
690 * Generates a platform-specific LIMIT clause
691 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000692 * @param string the sql query string
Andrey Andreev214583f2012-01-26 16:39:09 +0200693 * @param int the number of rows to limit the query to
694 * @param int the offset value
Derek Allard2067d1a2008-11-13 22:59:24 +0000695 * @return string
696 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200697 protected function _limit($sql, $limit, $offset)
Barry Mienydd671972010-10-04 16:33:58 +0200698 {
Andrey Andreev214583f2012-01-26 16:39:09 +0200699 return $sql.' LIMIT '.$limit.($offset == 0 ? '' : ' OFFSET '.$offset);
Derek Allard2067d1a2008-11-13 22:59:24 +0000700 }
701
702 // --------------------------------------------------------------------
703
704 /**
705 * Close DB Connection
706 *
Derek Allard2067d1a2008-11-13 22:59:24 +0000707 * @param resource
708 * @return void
709 */
Andrey Andreev214583f2012-01-26 16:39:09 +0200710 protected function _close($conn_id)
Derek Allard2067d1a2008-11-13 22:59:24 +0000711 {
712 @pg_close($conn_id);
713 }
714
Derek Allard2067d1a2008-11-13 22:59:24 +0000715}
716
Derek Allard2067d1a2008-11-13 22:59:24 +0000717/* End of file postgre_driver.php */
Andrey Andreev214583f2012-01-26 16:39:09 +0200718/* Location: ./system/database/drivers/postgre/postgre_driver.php */