· 9 years ago · Jul 04, 2017, 10:44 PM
1| GNU General Public License for more details. |
2| |
3| You should have received a copy of the GNU General Public License |
4| along with this program; if not, write to the Free Software |
5| Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
6+---------------------------------------------------------------------------+
7$Id: pgsql.php 62345 2010-09-14 21:16:38Z chris.nutting $
8*/
9
10/**
11 * The pgsql data access layer code the delivery engine.
12 *
13 * @package OpenXDal
14 * @subpackage Delivery
15 * @author Chris Nutting <chris.nutting@openx.org>
16 * @author Andrew Hill <andrew.hill@openx.org>
17 * @author Matteo Beccati <matteo.beccati@openx.org>
18 */
19
20/**
21 * The function to open a database connection, or return the resource if already open
22 *
23 * @param string $database The name of the database config to use
24 * (Must match the database section name in the conf file)
25 * @return resource|false The PgSQL database resource
26 * or false on failure
27 */
28function OA_Dal_Delivery_connect($database = 'database') {
29 // If a connection already exists, then return that
30 if ($database == 'database' && isset($GLOBALS['_MAX']['ADMIN_DB_LINK']) && is_resource($GLOBALS['_MAX']['ADMIN_DB_LINK'])) {
31 return $GLOBALS['_MAX']['ADMIN_DB_LINK'];
32 } elseif ($database == 'rawDatabase' && isset($GLOBALS['_MAX']['RAW_DB_LINK']) && is_resource($GLOBALS['_MAX']['RAW_DB_LINK'])) {
33 return $GLOBALS['_MAX']['RAW_DB_LINK'];
34 }
35 // No connection exists, so create one
36 $conf = $GLOBALS['_MAX']['CONF'];
37 if (!empty($conf[$database])) {
38 $dbConf = $conf[$database];
39 } else {
40 $dbConf = $conf['database'];
41 }
42 $dbParams = array();
43
44 if ($dbConf['protocol'] == 'unix')
45 {
46 $dbConf['host'] = $dbConf['socket'];
47 }
48 else
49 {
50 $dbConf['port'] = (isset($dbConf['port']) ? $dbConf['port'] : 5432);
51 }
52 $dbParams[] = empty($dbConf['port'] ) ? '' : 'port='.$dbConf['port'];
53 $dbParams[] = empty($dbConf['host'] ) ? '' : 'host='.$dbConf['host'];
54 $dbParams[] = empty($dbConf['username'] ) ? '' : 'user='.$dbConf['username'];
55 $dbParams[] = empty($dbConf['password'] ) ? '' : 'password='.$dbConf['password'];
56 $dbParams[] = empty($dbConf['name'] ) ? '' : 'dbname='.$dbConf['name'];
57 if ($dbConf['persistent']) {
58 $dbLink = @pg_pconnect(join(' ', $dbParams));
59 } else {
60 $dbLink = @pg_connect(join(' ', $dbParams));
61 }
62 if ($dbLink && !empty($conf['databasePgsql']['schema'])) {
63 @pg_query($dbLink, "SET search_path='{$conf['databasePgsql']['schema']}'");
64 }
65 if ($dbLink && !empty($conf['databaseCharset']['checkComplete']) && !empty($conf['databaseCharset']['clientCharset'])) {
66 @pg_client_encoding($dbLink, $conf['databaseCharset']['clientCharset']);
67 }
68 if (!$dbLink) {
69 OX_Delivery_logMessage('DB connection error: ' . pg_last_error(), 4);
70 }
71
72 if ($dbLink)
73 @pg_query('SET standard_conforming_strings = off; SET escape_string_warning = off;');
74
75 return $dbLink;
76}
77
78/**
79 * The function to pass a query to a database link
80 *
81 * @param string $query The SQL query to execute
82 * @param string $database The database to use for this query
83 * (Must match the database section name in the conf file)
84 * @return resource|false The PgSQL resource if the query suceeded
85 * or false on failure
86 */
87function OA_Dal_Delivery_query($query, $database = 'database') {
88 // Connect to the database if necessary
89 $dbName = ($database == 'rawDatabase') ? 'RAW_DB_LINK' : 'ADMIN_DB_LINK';
90
91 if (empty($GLOBALS['_MAX'][$dbName])) {
92 $GLOBALS['_MAX'][$dbName] = OA_Dal_Delivery_connect($database);
93 }
94 if (is_resource($GLOBALS['_MAX'][$dbName])) {
95 $result = @pg_query($GLOBALS['_MAX'][$dbName], $query);
96 if (!$result) {
97 OX_Delivery_logMessage('DB query error: ' . pg_last_error(), 4);
98 OX_Delivery_logMessage(' - failing query: ' . $query, 5);
99 }
100 return $result;
101 } else {
102 return false;
103 }
104}
105
106/**
107 * The function to fetch a result from a database resource
108 *
109 * @param resource The PgSQL resource
110 * @return array
111 */
112function OA_Dal_Delivery_fetchAssoc($resource) {
113 return pg_fetch_assoc($resource);
114}
115
116/**
117 * The function to retrieve the last-insert-id from the database
118 *
119 * @param string $database The name of the database config to use
120 * (Must match the database section name in the conf file)
121 * @param string $table The name of the table we need to get the ID from
122 * @param string $column The name of the column we need to get the ID from
123 * @return int|false The last insert ID (zero if last query didn't generate an ID)
124 * or false on failure
125 */
126function OA_Dal_Delivery_insertId($database = 'database', $table, $column)
127{
128 $dbName = ($database == 'rawDatabase') ? 'RAW_DB_LINK' : 'ADMIN_DB_LINK';
129 if (!isset($GLOBALS['_MAX'][$dbName]) || !(is_resource($GLOBALS['_MAX'][$dbName]))) {
130 return false;
131 }
132 $seqName = substr($column, 0, 29).'_seq';
133 $seqName = substr($table, 0, 62 - strlen($seqName)).'_'.$seqName;
134 $query = "SELECT currval('\"".$seqName."\"')";
135 return pg_fetch_result(pg_query($query), 0, 0);
136}
137
138function OA_Dal_Delivery_numRows($result)
139{
140 return pg_num_rows($result);
141}
142
143function OA_Dal_Delivery_result($result, $row_number, $field_name)
144{
145 return pg_result($result, $row_number, $field_name);
146}
147
148function OX_escapeString($string)
149{
150 return pg_escape_string($string);
151}
152
153function OX_escapeIdentifier($string)
154{
155 return '"'.$string.'"';
156}
157
158function OX_Dal_Delivery_regex($column, $regexp)
159{
160 return $column." ~* '".$regexp."'";
161}
162
163function OX_bucket_updateTable($tableName, $aQuery, $increment = true, $counter = 'count')
164{
165 $prefix = $GLOBALS['_MAX']['CONF']['table']['prefix'];
166 $query = OX_bucket_prepareUpdateQuery($prefix . $tableName, $aQuery, $increment, $counter);
167
168 $result = OA_Dal_Delivery_query(
169 $query,
170 'rawDatabase'
171 );
172 return $result;
173}
174
175function OX_bucket_prepareUpdateQuery($tableName, $aQuery, $increment = true, $counter = 'count')
176{
177 $args = implode(',', OX_bucket_quoteArgs($aQuery));
178 $query = "SELECT bucket_update_{$tableName}({$args},1)";
179 return $query;
180}
181
182function OX_bucket_quoteArgs($aArgs)
183{
184 $array = $aArgs;
185 foreach ($array as &$value) {
186 if (!is_integer($value)) {
187 $value = "'" . $value . "'";
188 }
189 }
190 return $array;
191}
192
193function OA_Dal_Delivery_getKeywordCondition($operator, $keyword)
194{
195 $p1 = "(' ' || d.keyword || ' ')";
196 $p2 = "ILIKE '% $keyword %'";
197
198 if ($operator == 'OR') {
199 return "OR {$p1} {$p2} ";
200 } elseif ($operator == 'AND') {
201 return "AND {$p1} {$p2} ";
202 } else {
203 return "AND {$p1} NOT {$p2} ";
204 }
205}
206
207?>