diff options
Diffstat (limited to 'system/database/drivers/postgre')
| -rw-r--r-- | system/database/drivers/postgre/postgre_driver.php | 290 | ||||
| -rw-r--r-- | system/database/drivers/postgre/postgre_forge.php | 125 | ||||
| -rw-r--r-- | system/database/drivers/postgre/postgre_result.php | 20 | ||||
| -rw-r--r-- | system/database/drivers/postgre/postgre_utility.php | 41 |
4 files changed, 144 insertions, 332 deletions
diff --git a/system/database/drivers/postgre/postgre_driver.php b/system/database/drivers/postgre/postgre_driver.php index 3bfccad05..84bf768ee 100644 --- a/system/database/drivers/postgre/postgre_driver.php +++ b/system/database/drivers/postgre/postgre_driver.php @@ -29,7 +29,7 @@ * Postgre Database Adapter Class * * Note: _DB is an extender class that the app controller - * creates dynamically based on whether the active record + * creates dynamically based on whether the query builder * class is being used or not. * * @package CodeIgniter @@ -57,29 +57,64 @@ class CI_DB_postgre_driver extends CI_DB { protected $_random_keyword = ' RANDOM()'; // database specific random keyword /** - * Connection String + * Constructor * - * @return string + * Creates a DSN string to be used for db_connect() and db_pconnect() + * + * @return void */ - protected function _connect_string() + public function __construct($params) { - $components = array( - 'hostname' => 'host', - 'port' => 'port', - 'database' => 'dbname', - 'username' => 'user', - 'password' => 'password' - ); - - $connect_string = ""; - foreach ($components as $key => $val) + parent::__construct($params); + + if ( ! empty($this->dsn)) + { + return; + } + + $this->dsn === '' OR $this->dsn = ''; + + if (strpos($this->hostname, '/') !== FALSE) + { + // If UNIX sockets are used, we shouldn't set a port + $this->port = ''; + } + + $this->hostname === '' OR $this->dsn = 'host='.$this->hostname.' '; + + if ( ! empty($this->port) && ctype_digit($this->port)) + { + $this->dsn .= 'port='.$this->port.' '; + } + + if ($this->username !== '') { - if (isset($this->$key) && $this->$key != '') + $this->dsn .= 'user='.$this->username.' '; + + /* An empty password is valid! + * + * $db['password'] = NULL must be done in order to ignore it. + */ + $this->password === NULL OR $this->dsn .= "password='".$this->password."' "; + } + + $this->database === '' OR $this->dsn .= 'dbname='.$this->database.' '; + + /* We don't have these options as elements in our standard configuration + * array, but they might be set by parse_url() if the configuration was + * provided via string. Example: + * + * postgre://username:password@localhost:5432/database?connect_timeout=5&sslmode=1 + */ + foreach (array('connect_timeout', 'options', 'sslmode', 'service') as $key) + { + if (isset($this->$key) && is_string($this->key) && $this->key !== '') { - $connect_string .= " $val=".$this->$key; + $this->dsn .= $key."='".$this->key."' "; } } - return trim($connect_string); + + $this->dsn = rtrim($this->dsn); } // -------------------------------------------------------------------- @@ -91,7 +126,7 @@ class CI_DB_postgre_driver extends CI_DB { */ public function db_connect() { - return @pg_connect($this->_connect_string()); + return @pg_connect($this->dsn); } // -------------------------------------------------------------------- @@ -103,7 +138,7 @@ class CI_DB_postgre_driver extends CI_DB { */ public function db_pconnect() { - return @pg_pconnect($this->_connect_string()); + return @pg_pconnect($this->dsn); } // -------------------------------------------------------------------- @@ -127,23 +162,10 @@ class CI_DB_postgre_driver extends CI_DB { // -------------------------------------------------------------------- /** - * Select the database - * - * @return resource - */ - public function db_select() - { - // Not needed for Postgre so we'll return TRUE - return TRUE; - } - - // -------------------------------------------------------------------- - - /** * Set client character set * - * @param string - * @return bool + * @param string + * @return bool */ protected function _db_set_charset($charset) { @@ -197,6 +219,7 @@ class CI_DB_postgre_driver extends CI_DB { /** * Begin Transaction * + * @param bool * @return bool */ public function trans_begin($test_mode = FALSE) @@ -212,7 +235,7 @@ class CI_DB_postgre_driver extends CI_DB { // even if the queries produce a successful result. $this->_trans_failure = ($test_mode === TRUE); - return @pg_query($this->conn_id, 'BEGIN'); + return (bool) @pg_query($this->conn_id, 'BEGIN'); } // -------------------------------------------------------------------- @@ -230,7 +253,7 @@ class CI_DB_postgre_driver extends CI_DB { return TRUE; } - return @pg_query($this->conn_id, 'COMMIT'); + return (bool) @pg_query($this->conn_id, 'COMMIT'); } // -------------------------------------------------------------------- @@ -248,7 +271,7 @@ class CI_DB_postgre_driver extends CI_DB { return TRUE; } - return @pg_query($this->conn_id, 'ROLLBACK'); + return (bool) @pg_query($this->conn_id, 'ROLLBACK'); } // -------------------------------------------------------------------- @@ -306,34 +329,41 @@ class CI_DB_postgre_driver extends CI_DB { */ public function insert_id() { - $v = $this->version(); + $v = pg_version($this->conn_id); + $v = isset($v['server']) ? $v['server'] : 0; // 'server' key is only available since PosgreSQL 7.4 - $table = func_num_args() > 0 ? func_get_arg(0) : NULL; - $column = func_num_args() > 1 ? func_get_arg(1) : NULL; + $table = (func_num_args() > 0) ? func_get_arg(0) : NULL; + $column = (func_num_args() > 1) ? func_get_arg(1) : NULL; if ($table == NULL && $v >= '8.1') { - $sql='SELECT LASTVAL() as ins_id'; - } - elseif ($table != NULL && $column != NULL && $v >= '8.0') - { - $sql = sprintf("SELECT pg_get_serial_sequence('%s','%s') as seq", $table, $column); - $query = $this->query($sql); - $row = $query->row(); - $sql = sprintf("SELECT CURRVAL('%s') as ins_id", $row->seq); + $sql = 'SELECT LASTVAL() AS ins_id'; } elseif ($table != NULL) { - // seq_name passed in table parameter - $sql = sprintf("SELECT CURRVAL('%s') as ins_id", $table); + if ($column != NULL && $v >= '8.0') + { + $sql = 'SELECT pg_get_serial_sequence(\''.$table."', '".$column."') AS seq"; + $query = $this->query($sql); + $query = $query->row(); + $seq = $query->seq; + } + else + { + // seq_name passed in table parameter + $seq = $table; + } + + $sql = 'SELECT CURRVAL(\''.$seq."') AS ins_id"; } else { return pg_last_oid($this->result_id); } + $query = $this->query($sql); - $row = $query->row(); - return $row->ins_id; + $query = $query->row(); + return (int) $query->ins_id; } // -------------------------------------------------------------------- @@ -360,9 +390,9 @@ class CI_DB_postgre_driver extends CI_DB { return 0; } - $row = $query->row(); + $query = $query->row(); $this->_reset_select(); - return (int) $row->numrows; + return (int) $query->numrows; } // -------------------------------------------------------------------- @@ -379,9 +409,9 @@ class CI_DB_postgre_driver extends CI_DB { { $sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"; - if ($prefix_limit !== FALSE AND $this->dbprefix != '') + if ($prefix_limit !== FALSE && $this->dbprefix != '') { - $sql .= " AND table_name LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr); + return $sql." AND table_name LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr); } return $sql; @@ -399,7 +429,7 @@ class CI_DB_postgre_driver extends CI_DB { */ protected function _list_columns($table = '') { - return "SELECT column_name FROM information_schema.columns WHERE table_name ='".$table."'"; + return "SELECT column_name FROM information_schema.columns WHERE table_name = '".$table."'"; } // -------------------------------------------------------------------- @@ -410,11 +440,11 @@ class CI_DB_postgre_driver extends CI_DB { * Generates a platform-specific query so that the column data can be retrieved * * @param string the table name - * @return object + * @return string */ protected function _field_data($table) { - return "SELECT * FROM ".$table." LIMIT 1"; + return 'SELECT * FROM '.$table.' LIMIT 1'; } // -------------------------------------------------------------------- @@ -435,47 +465,6 @@ class CI_DB_postgre_driver extends CI_DB { // -------------------------------------------------------------------- /** - * Escape the SQL Identifiers - * - * This function escapes column and table names - * - * @param string - * @return string - */ - public function _escape_identifiers($item) - { - if ($this->_escape_char == '') - { - return $item; - } - - foreach ($this->_reserved_identifiers as $id) - { - if (strpos($item, '.'.$id) !== FALSE) - { - $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item); - - // remove duplicates if the user already included the escape - return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); - } - } - - if (strpos($item, '.') !== FALSE) - { - $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; - } - else - { - $str = $this->_escape_char.$item.$this->_escape_char; - } - - // remove duplicates if the user already included the escape - return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); - } - - // -------------------------------------------------------------------- - - /** * From Tables * * This function implicitly groups FROM tables so there is no confusion @@ -497,40 +486,6 @@ class CI_DB_postgre_driver extends CI_DB { // -------------------------------------------------------------------- /** - * Insert statement - * - * Generates a platform-specific insert string from the supplied data - * - * @param string the table name - * @param array the insert keys - * @param array the insert values - * @return string - */ - protected function _insert($table, $keys, $values) - { - return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; - } - - // -------------------------------------------------------------------- - - /** - * Insert_batch statement - * - * Generates a platform-specific insert string from the supplied data - * - * @param string the table name - * @param array the insert keys - * @param array the insert values - * @return string - */ - protected function _insert_batch($table, $keys, $values) - { - return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES ".implode(', ', $values); - } - - // -------------------------------------------------------------------- - - /** * Update statement * * Generates a platform-specific update string from the supplied data @@ -538,39 +493,26 @@ class CI_DB_postgre_driver extends CI_DB { * @param string the table name * @param array the update data * @param array the where clause - * @param array the orderby clause - * @param array the limit clause + * @param array the orderby clause (ignored) + * @param array the limit clause (ignored) + * @param array the like clause * @return string */ - protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE) + protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array()) { foreach ($values as $key => $val) { - $valstr[] = $key." = ".$val; + $valstr[] = $key.' = '.$val; } - $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); - - $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; - - return $sql; - } + $where = empty($where) ? '' : ' WHERE '.implode(' ', $where); - // -------------------------------------------------------------------- + if ( ! empty($like)) + { + $where .= ($where === '' ? ' WHERE ' : ' AND ').implode(' ', $like); + } - /** - * Truncate statement - * - * Generates a platform-specific truncate string from the supplied data - * If the database does not support the truncate() command - * This function maps to "DELETE FROM table" - * - * @param string the table name - * @return string - */ - protected function _truncate($table) - { - return "TRUNCATE ".$table; + return 'UPDATE '.$table.' SET '.implode(', ', $valstr).$where; } // -------------------------------------------------------------------- @@ -582,29 +524,22 @@ class CI_DB_postgre_driver extends CI_DB { * * @param string the table name * @param array the where clause - * @param string the limit clause + * @param array the like clause + * @param string the limit clause (ignored) * @return string */ protected function _delete($table, $where = array(), $like = array(), $limit = FALSE) { - $conditions = ''; + $conditions = array(); - if (count($where) > 0 OR count($like) > 0) - { - $conditions = "\nWHERE "; - $conditions .= implode("\n", $this->ar_where); - - if (count($where) > 0 && count($like) > 0) - { - $conditions .= " AND "; - } - $conditions .= implode("\n", $like); - } + empty($where) OR $conditions[] = implode(' ', $where); + empty($like) OR $conditions[] = implode(' ', $like); - return "DELETE FROM ".$table.$conditions; + return 'DELETE FROM '.$table.(count($conditions) > 0 ? ' WHERE '.implode(' AND ', $conditions) : ''); } // -------------------------------------------------------------------- + /** * Limit string * @@ -617,14 +552,7 @@ class CI_DB_postgre_driver extends CI_DB { */ protected function _limit($sql, $limit, $offset) { - $sql .= "LIMIT ".$limit; - - if ($offset > 0) - { - $sql .= " OFFSET ".$offset; - } - - return $sql; + return $sql.' LIMIT '.$limit.($offset == 0 ? '' : ' OFFSET '.$offset); } // -------------------------------------------------------------------- @@ -643,4 +571,4 @@ class CI_DB_postgre_driver extends CI_DB { } /* End of file postgre_driver.php */ -/* Location: ./system/database/drivers/postgre/postgre_driver.php */
\ No newline at end of file +/* Location: ./system/database/drivers/postgre/postgre_driver.php */ diff --git a/system/database/drivers/postgre/postgre_forge.php b/system/database/drivers/postgre/postgre_forge.php index a72449820..94c97af50 100644 --- a/system/database/drivers/postgre/postgre_forge.php +++ b/system/database/drivers/postgre/postgre_forge.php @@ -34,31 +34,7 @@ */ class CI_DB_postgre_forge extends CI_DB_forge { - /** - * Create database - * - * @param string the database name - * @return bool - */ - public function _create_database($name) - { - return "CREATE DATABASE ".$name; - } - - // -------------------------------------------------------------------- - - /** - * Drop database - * - * @param string the database name - * @return bool - */ - public function _drop_database($name) - { - return "DROP DATABASE ".$name; - } - - // -------------------------------------------------------------------- + protected $_drop_table = 'DROP TABLE IF EXISTS %s CASCADE'; /** * Process Fields @@ -66,7 +42,7 @@ class CI_DB_postgre_forge extends CI_DB_forge { * @param mixed the fields * @return string */ - protected function _process_fields($fields, $primary_keys=array()) + protected function _process_fields($fields, $primary_keys = array()) { $sql = ''; $current_field_count = 0; @@ -78,15 +54,14 @@ class CI_DB_postgre_forge extends CI_DB_forge { // entered the field information, so we'll simply add it to the list if (is_numeric($field)) { - $sql .= "\n\t$attributes"; + $sql .= "\n\t".$attributes; } else { - $attributes = array_change_key_case($attributes, CASE_UPPER); - $sql .= "\n\t".$this->db->protect_identifiers($field); - $is_unsigned = (array_key_exists('UNSIGNED', $attributes) && $attributes['UNSIGNED'] === TRUE); + $attributes = array_change_key_case($attributes, CASE_UPPER); + $is_unsigned = ( ! empty($attributes['UNSIGNED']) && $attributes['UNSIGNED'] === TRUE); // Convert datatypes to be PostgreSQL-compatible switch (strtoupper($attributes['TYPE'])) @@ -118,44 +93,24 @@ class CI_DB_postgre_forge extends CI_DB_forge { case 'BLOB': $attributes['TYPE'] = 'BYTEA'; break; + default: + break; } // If this is an auto-incrementing primary key, use the serial data type instead - if (in_array($field, $primary_keys) && array_key_exists('AUTO_INCREMENT', $attributes) - && $attributes['AUTO_INCREMENT'] === TRUE) - { - $sql .= ' SERIAL'; - } - else - { - $sql .= ' '.$attributes['TYPE']; - } + $sql .= (in_array($field, $primary_keys) && ! empty($attributes['AUTO_INCREMENT']) && $attributes['AUTO_INCREMENT'] === TRUE) + ? ' SERIAL' : ' '.$attributes['TYPE']; // Modified to prevent constraints with integer data types - if (array_key_exists('CONSTRAINT', $attributes) && strpos($attributes['TYPE'], 'INT') === false) + if ( ! empty($attributes['CONSTRAINT']) && strpos($attributes['TYPE'], 'INT') === FALSE) { $sql .= '('.$attributes['CONSTRAINT'].')'; } - if (array_key_exists('DEFAULT', $attributes)) - { - $sql .= ' DEFAULT \''.$attributes['DEFAULT'].'\''; - } - - if (array_key_exists('NULL', $attributes) && $attributes['NULL'] === TRUE) - { - $sql .= ' NULL'; - } - else - { - $sql .= ' NOT NULL'; - } - - // Added new attribute to create unqite fields. Also works with MySQL - if (array_key_exists('UNIQUE', $attributes) && $attributes['UNIQUE'] === TRUE) - { - $sql .= ' UNIQUE'; - } + $sql .= (isset($attributes['DEFAULT']) ? " DEFAULT '".$attributes['DEFAULT']."'" : '') + .(( ! empty($attributes['NULL']) && $attributes['NULL'] === TRUE) ? ' NULL' : ' NOT NULL') + // Added new attribute to create unqite fields. Also works with MySQL + .(( ! empty($attributes['UNIQUE']) && $attributes['UNIQUE'] === TRUE) ? ' UNIQUE' : ''); } // don't add a comma on the end of the last field @@ -180,7 +135,7 @@ class CI_DB_postgre_forge extends CI_DB_forge { * @param bool should 'IF NOT EXISTS' be added to the SQL * @return bool */ - public function _create_table($table, $fields, $primary_keys, $keys, $if_not_exists) + protected function _create_table($table, $fields, $primary_keys, $keys, $if_not_exists) { $sql = 'CREATE TABLE '; @@ -193,8 +148,7 @@ class CI_DB_postgre_forge extends CI_DB_forge { } } - $sql .= $this->db->_escape_identifiers($table)." ("; - $sql .= $this->_process_fields($fields, $primary_keys); + $sql .= $this->db->escape_identifiers($table).' ('.$this->_process_fields($fields, $primary_keys); if (count($primary_keys) > 0) { @@ -204,7 +158,7 @@ class CI_DB_postgre_forge extends CI_DB_forge { $primary_keys[$index] = $this->db->protect_identifiers($key); } - $sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")"; + $sql .= ",\n\tPRIMARY KEY (".implode(', ', $primary_keys).')'; } $sql .= "\n);"; @@ -224,7 +178,7 @@ class CI_DB_postgre_forge extends CI_DB_forge { foreach ($key as $field) { - $sql .= "CREATE INDEX " . $table . "_" . str_replace(array('"', "'"), '', $field) . "_index ON $table ($field); "; + $sql .= 'CREATE INDEX '.$table.'_'.str_replace(array('"', "'"), '', $field).'_index ON '.$table.' ('.$field.'); '; } } } @@ -235,18 +189,6 @@ class CI_DB_postgre_forge extends CI_DB_forge { // -------------------------------------------------------------------- /** - * Drop Table - * - * @return string - */ - public function _drop_table($table) - { - return "DROP TABLE IF EXISTS ".$this->db->_escape_identifiers($table)." CASCADE"; - } - - // -------------------------------------------------------------------- - - /** * Alter table query * * Generates a platform-specific query so that a table can be altered @@ -257,45 +199,24 @@ class CI_DB_postgre_forge extends CI_DB_forge { * @param string the table name * @param string the column definition * @param string the default value - * @param boolean should 'NOT NULL' be added + * @param bool should 'NOT NULL' be added * @param string the field after which we should add the new field * @return string */ - public function _alter_table($alter_type, $table, $fields, $after_field = '') + protected function _alter_table($alter_type, $table, $fields, $after_field = '') { $sql = 'ALTER TABLE '.$this->db->protect_identifiers($table).' '.$alter_type.' '; // DROP has everything it needs now. - if ($alter_type == 'DROP') + if ($alter_type === 'DROP') { return $sql.$this->db->protect_identifiers($fields); } - $sql .= $this->_process_fields($fields); - - if ($after_field != '') - { - return $sql.' AFTER '.$this->db->protect_identifiers($after_field); - } - - return $sql; + return $sql.$this->_process_fields($fields) + .($after_field != '' ? ' AFTER '.$this->db->protect_identifiers($after_field) : ''); } - // -------------------------------------------------------------------- - - /** - * Rename a table - * - * Generates a platform-specific query so that a table can be renamed - * - * @param string the old table name - * @param string the new table name - * @return string - */ - public function _rename_table($table_name, $new_table_name) - { - return 'ALTER TABLE '.$this->db->protect_identifiers($table_name).' RENAME TO '.$this->db->protect_identifiers($new_table_name); - } } /* End of file postgre_forge.php */ diff --git a/system/database/drivers/postgre/postgre_result.php b/system/database/drivers/postgre/postgre_result.php index 8b22564b3..f913bc9eb 100644 --- a/system/database/drivers/postgre/postgre_result.php +++ b/system/database/drivers/postgre/postgre_result.php @@ -70,7 +70,7 @@ class CI_DB_postgre_result extends CI_DB_result { public function list_fields() { $field_names = array(); - for ($i = 0; $i < $this->num_fields(); $i++) + for ($i = 0, $c = $this->num_fields(); $i < $c; $i++) { $field_names[] = pg_field_name($this->result_id, $i); } @@ -90,16 +90,14 @@ class CI_DB_postgre_result extends CI_DB_result { public function field_data() { $retval = array(); - for ($i = 0; $i < $this->num_fields(); $i++) + for ($i = 0, $c = $this->num_fields(); $i < $c; $i++) { - $F = new stdClass(); - $F->name = pg_field_name($this->result_id, $i); - $F->type = pg_field_type($this->result_id, $i); - $F->max_length = pg_field_size($this->result_id, $i); - $F->primary_key = 0; - $F->default = ''; - - $retval[] = $F; + $retval[$i] = new stdClass(); + $retval[$i]->name = pg_field_name($this->result_id, $i); + $retval[$i]->type = pg_field_type($this->result_id, $i); + $retval[$i]->max_length = pg_field_size($this->result_id, $i); + $retval[$i]->primary_key = 0; + $retval[$i]->default = ''; } return $retval; @@ -130,7 +128,7 @@ class CI_DB_postgre_result extends CI_DB_result { * this internally before fetching results to make sure the * result set starts at zero * - * @return array + * @return bool */ protected function _data_seek($n = 0) { diff --git a/system/database/drivers/postgre/postgre_utility.php b/system/database/drivers/postgre/postgre_utility.php index c6b71b4d9..c95e6df0c 100644 --- a/system/database/drivers/postgre/postgre_utility.php +++ b/system/database/drivers/postgre/postgre_utility.php @@ -34,43 +34,8 @@ */ class CI_DB_postgre_utility extends CI_DB_utility { - /** - * List databases - * - * @return string - */ - public function _list_databases() - { - return 'SELECT datname FROM pg_database'; - } - - // -------------------------------------------------------------------- - - /** - * Optimize table query - * - * @param string the table name - * @return string - */ - public function _optimize_table($table) - { - return 'REINDEX TABLE '.$this->db->protect_identifiers($table); - } - - // -------------------------------------------------------------------- - - /** - * Repair table query - * - * @param string the table name - * @return bool - */ - public function _repair_table($table) - { - return FALSE; - } - - // -------------------------------------------------------------------- + protected $_list_databases = 'SELECT datname FROM pg_database'; + protected $_optimize_table = 'REINDEX TABLE %s'; /** * Postgre Export @@ -78,7 +43,7 @@ class CI_DB_postgre_utility extends CI_DB_utility { * @param array Preferences * @return mixed */ - function _backup($params = array()) + protected function _backup($params = array()) { // Currently unsupported return $this->db->display_error('db_unsuported_feature'); |