1
<?php
2
/**
3
* PostgreSQL Data Access Object
4
*
5
* @author Zhou Yuan <yuanzhou19@gmail.com>
6
* @link http://www.infopotato.com/
7
* @copyright Copyright © 2009-2011 Zhou Yuan
8
* @license http://www.opensource.org/licenses/mit-license.php MIT Licence
9
*/
10
class PostgreSQL_DAO extends Base_DAO {
11
/**
12
* Database connection handler
13
*
14
* @var resource
15
*/
16
public $dbh;
17
18
/**
19
* Constructor
20
*
21
* Allow the user to perform a connect at the same time as initialising the this class
22
*/
23
public function __construct(array $config = array()) {
24
// If there is no existing database connection then try to connect
25
if ( ! $this->dbh) {
26
if ( ! $this->dbh = pg_connect("host=$config['host'] port=$config['port'] user=$config['user'] password=$config['pass'] dbname=$config['name']", TRUE)) {
27
halt('An Error Was Encountered', 'Could not connect: '.pg_last_error($this->dbh), 'sys_error');
28
}
29
30
// Specify the client encoding per connection
31
$collation_query = "SET NAMES '{$config['charset']}'";
32
$this->exec_query($collation_query);
33
}
34
}
35
36
/**
37
* Escapes special characters in a string for use in an SQL statement,
38
* taking into account the current charset of the connection
39
*/
40
public function escape($string) {
41
// Only quote and escape string
42
// is_string() will take '' will as string
43
if (is_string($string)) {
44
$string = isset($this->dbh) ? pg_escape_string($this->dbh, $string) : addslashes($string);
45
}
46
return $string;
47
}
48
49
/**
50
* USAGE: prepare( string $query [, array $params ] )
51
* The following directives can be used in the query format string:
52
* %d (decimal number)
53
* %s (string)
54
*
55
* Both %d and %s are to be left unquoted in the query string and they need an argument passed for them.
56
*/
57
public function prepare($query, array $params = NULL) {
58
if (count($params) > 0) {
59
foreach ($params as $k => $v) {
60
// Only quote and escape string
61
if (is_string($v)) {
62
$params[$k] = isset($this->dbh) ? "'".pg_escape_string($this->dbh, $v)."'" : "'".addslashes($v)."'";
63
}
64
}
65
// vsprintf - replacing all %d and %s to parameters
66
$query = vsprintf($query, $params);
67
}
68
return $query;
69
}
70
71
/**
72
* Perform a unique query (multiple queries are not supported) and try to determine result value
73
*
74
* @return int Number of rows affected/selected
75
*/
76
public function exec_query($query) {
77
// Initialise return
78
$return_val = 0;
79
80
// Reset stored query result
81
$this->query_result = array();
82
83
// For reg expressions
84
$query = trim($query);
85
86
// A query result resource on success or FALSE on failure.
87
$result = pg_query($this->dbh, $query);
88
89
// If there is an error then take note of it.
90
if ($err_msg = pg_last_error($this->dbh)) {
91
halt('An Error Was Encountered', $err_msg, 'sys_error');
92
}
93
94
// Query was an insert, delete, drop, update, replace, alter
95
if (preg_match("/^(insert|delete|drop|update|replace|alter)\s+/i", $query)) {
96
$rows_affected = pg_affected_rows($result);
97
98
// Take note of the last_insert_id
99
// REPLACE works exactly like INSERT, except that if an old row in the table has the same value
100
// as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
101
if (preg_match("/^(insert|replace)\s+/i", $query)) {
102
$this->last_insert_id = pg_last_oid($result);
103
}
104
// Return number fo rows affected
105
$return_val = $rows_affected;
106
} elseif (preg_match("/^(select|describe|desc|show|explain)\s+/i", $query)) {
107
// Store Query Results
108
$num_rows = 0;
109
while ($row = pg_fetch_object($result)) {
110
// Store relults as an objects within main array
111
$this->query_result[$num_rows] = $row;
112
$num_rows++;
113
}
114
115
pg_free_result($result);
116
117
// Log number of rows the query returned
118
$this->num_rows = $num_rows;
119
120
// Return number of rows selected
121
$return_val = $this->num_rows;
122
} elseif (preg_match("/^create\s+/i", $query)) {
123
// Table creation returns TRUE on success, or FALSE on error.
124
$return_val = $result;
125
}
126
127
return $return_val;
128
}
129
130
/**
131
* Begin Transaction using standard sql
132
*
133
* @access public
134
* @return bool
135
*/
136
public function trans_begin() {
137
pg_query($this->dbh, 'begin');
138
}
139
140
/**
141
* Commit Transaction using standard sql
142
*
143
* @access public
144
* @return bool
145
*/
146
public function trans_commit() {
147
pg_query($this->dbh, 'commit');
148
}
149
150
/**
151
* Rollback Transaction using standard sql
152
*
153
* @access public
154
* @return bool
155
*/
156
public function trans_rollback() {
157
pg_query($this->dbh, 'rollback');
158
}
159
160
}
161
162
// End of file: ./system/core/postgresql_dao.php
Page URI: http://www.infopotato.com/index.php/code/core/postgresql_dao/