1<?php
2# $Id: postgres.inc.php 1178 2006-05-01 13:53:40Z garvinhicking $
3# Copyright (c) 2003-2005, Jannis Hermanns (on behalf the Serendipity Developer Team)
4# All rights reserved.  See LICENSE file for licensing details
5
6/**
7 * Tells the DB Layer to start a DB transaction.
8 *
9 * @access public
10 */
11function serendipity_db_begin_transaction(){
12    global $serendipity;
13    $serendipity['dbConn']->beginTransaction();
14}
15
16/**
17 * Tells the DB Layer to end a DB transaction.
18 *
19 * @access public
20 * @param  boolean  If true, perform the query. If false, rollback.
21 */
22function serendipity_db_end_transaction($commit){
23    global $serendipity;
24    if ($commit){
25        $serendipity['dbConn']->commit();
26    }else{
27        $serendipity['dbConn']->rollback();
28    }
29}
30
31/**
32 * Assemble and return SQL condition for a "IN (...)" clause
33 *
34 * @access public
35 * @param  string   table column name
36 * @param  array    referenced array of values to search for in the "IN (...)" clause
37 * @param  string   condition of how to associate the different input values of the $search_ids parameter
38 * @return string   resulting SQL string
39 */
40function serendipity_db_in_sql($col, &$search_ids, $type = ' OR ') {
41    return $col . " IN (" . implode(', ', $search_ids) . ")";
42}
43
44/**
45 * Connect to the configured Database
46 *
47 * @access public
48 * @return  resource   connection handle
49 */
50function serendipity_db_connect() {
51    global $serendipity;
52
53    $host = $port = '';
54    if (strlen($serendipity['dbHost'])) {
55        if (false !== strstr($serendipity['dbHost'], ':')) {
56            $tmp = explode(':', $serendipity['dbHost']);
57            $host = "host={$tmp[0]};";
58            $port = "port={$tmp[1]};";
59        } else {
60            $host = "host={$serendipity['dbHost']};";
61        }
62    }
63
64    $serendipity['dbConn'] = new PDO(
65                               sprintf(
66                                 'pgsql:%sdbname=%s',
67                                 "$host$port",
68                                 $serendipity['dbName']
69                               ),
70                               $serendipity['dbUser'],
71                               $serendipity['dbPass']
72                             );
73
74    return $serendipity['dbConn'];
75}
76
77function serendipity_db_reconnect() {
78}
79
80/**
81 * Returns a escaped string, so that it can be safely included in a SQL string encapsulated within quotes, without allowing SQL injection.
82 *
83 * @access  public
84 * @param   string   input string
85 * @return  string   output string
86 */
87function serendipity_db_escape_string($string) {
88    global $serendipity;
89    return substr($serendipity['dbConn']->quote($string), 1, -1);
90}
91
92/**
93 * Returns the option to a LIMIT SQL statement, because it varies across DB systems
94 *
95 * @access public
96 * @param  int      Number of the first row to return data from
97 * @param  int      Number of rows to return
98 * @return string   SQL string to pass to a LIMIT statement
99 */
100function serendipity_db_limit($start, $offset) {
101    return $offset . ', ' . $start;
102}
103
104/**
105 * Return a LIMIT SQL option to the DB Layer as a full LIMIT statement
106 *
107 * @access public
108 * @param   SQL string of a LIMIT option
109 * @return  SQL string containing a full LIMIT statement
110 */
111function serendipity_db_limit_sql($limitstring) {
112    $limit_split = explode(',', $limitstring);
113    if (count($limit_split) > 1) {
114        $limit = ' LIMIT ' . $limit_split[0] . ' OFFSET ' . $limit_split[1];
115    } else {
116        $limit = ' LIMIT ' . $limit_split[0];
117    }
118    return $limit;
119}
120
121/**
122 * Returns the number of affected rows of a SQL query
123 *
124 * @access public
125 * @return int      Number of affected rows
126 */
127function serendipity_db_affected_rows() {
128    global $serendipity;
129    return $serendipity['dbSth']->rowCount();
130}
131
132/**
133 * Returns the number of updated rows in a SQL query
134 *
135 * @access public
136 * @return int  Number of updated rows
137 */
138function serendipity_db_updated_rows() {
139    global $serendipity;
140    // it is unknown whether pg_affected_rows returns number of rows
141    //  UPDATED or MATCHED on an UPDATE statement.
142    return $serendipity['dbSth']->rowCount();
143}
144
145/**
146 * Returns the number of matched rows in a SQL query
147 *
148 * @access public
149 * @return int  Number of matched rows
150 */
151function serendipity_db_matched_rows() {
152    global $serendipity;
153    // it is unknown whether pg_affected_rows returns number of rows
154    //  UPDATED or MATCHED on an UPDATE statement.
155    return $serendipity['dbSth']->rowCount();
156}
157
158/**
159 * Returns the latest INSERT_ID of an SQL INSERT INTO command, for auto-increment columns
160 *
161 * @access public
162 * @param  string   Name of the table to get a INSERT ID for
163 * @param  string   Name of the column to get a INSERT ID for
164 * @return int      Value of the auto-increment column
165 */
166function serendipity_db_insert_id($table = '', $id = '') {
167    global $serendipity;
168    if (empty($table) || empty($id)) {
169        // BC - will/should never be called with empty parameters!
170        return $serendipity['dbConn']->lastInsertId();
171    } else {
172        $query = "SELECT currval('{$serendipity['dbPrefix']}{$table}_{$id}_seq'::text) AS {$id}";
173        $res = $serendipity['dbConn']->prepare($query);
174        $res->execute();
175        foreach($res->fetchAll(PDO::FETCH_ASSOC) AS $row) {
176            return $row[$id];
177        }
178        return $serendipity['dbConn']->lastInsertId();
179    }
180}
181
182/**
183 * Perform a DB Layer SQL query.
184 *
185 * This function returns values dependin on the input parameters and the result of the query.
186 * It can return:
187 *   false or a string if there was an error (depends on $expectError),
188 *   true if the query succeeded but did not generate any rows
189 *   array of field values if it returned a single row and $single is true
190 *   array of array of field values if it returned row(s) [stacked array]
191 *
192 * @access public
193 * @param   string      SQL query to execute
194 * @param   boolean     Toggle whether the expected result is a single row (TRUE) or multiple rows (FALSE). This affects whether the returned array is 1 or 2 dimensional!
195 * @param   string      Result type of the array indexing. Can be one of "assoc" (associative), "num" (numerical), "both" (numerical and associative, default)
196 * @param   boolean     If true, errors will be reported. If false, errors will be ignored.
197 * @param   string      A possible array key name, so that you can control the multi-dimensional mapping of an array by the key column
198 * @param   string      A possible array field name, so that you can control the multi-dimensional mapping of an array by the key column and the field value.
199 * @param   boolean     If true, the executed SQL error is known to fail, and should be disregarded (errors can be ignroed on DUPLICATE INDEX queries and the likes)
200 * @return  mixed       Returns the result of the SQL query, depending on the input parameters
201 */
202function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = false, $assocKey = false, $assocVal = false, $expectError = false) {
203    global $serendipity;
204    $type_map = array(
205                         'assoc' => PDO::FETCH_ASSOC,
206                         'num'   => PDO::FETCH_NUM,
207                         'both'  => PDO::FETCH_BOTH,
208                         'true'  => true,
209                         'false' => false
210    );
211
212    if (!$expectError && ($reportErr || !$serendipity['production'])) {
213        $serendipity['dbSth'] = $serendipity['dbConn']->prepare($sql);
214    } else {
215        $serendipity['dbSth'] = $serendipity['dbConn']->prepare($sql);
216    }
217
218    if (!$serendipity['dbSth']) {
219        if (!$expectError && !$serendipity['production']) {
220            print "<span class='msg_error'>Error in $sql</span>";
221            print $serendipity['dbConn']->errorInfo() . "<BR/>\n";
222            if (function_exists('debug_backtrace')) {
223                highlight_string(var_export(debug_backtrace(), 1));
224            }
225            print "<pre>$sql</pre>\n";
226        }
227        return $type_map['false'];
228    }
229
230    $serendipity['dbSth']->execute();
231
232    if ($serendipity['dbSth'] === true) {
233        return $type_map['true'];
234    }
235
236    $result_type = $type_map[$result_type];
237
238    $n = 0;
239
240    $rows = array();
241    foreach($serendipity['dbSth']->fetchAll($result_type) AS $row) {
242        if (!empty($assocKey)) {
243            // You can fetch a key-associated array via the two function parameters assocKey and assocVal
244            if (empty($assocVal)) {
245                $rows[$row[$assocKey]] = $row;
246            } else {
247                $rows[$row[$assocKey]] = $row[$assocVal];
248            }
249        } else {
250            $rows[] = $row;
251        }
252    }
253    if(count($rows) == 0) {
254        if ($single) {
255            return $type_map['false'];
256        }
257        return $type_map['true'];
258    }
259    if(count($rows) == 1 && $single) {
260        return $rows[0];
261    }
262    return $rows;
263}
264
265/**
266 * Prepares a Serendipty query input to fully valid SQL. Replaces certain "template" variables.
267 *
268 * @access public
269 * @param  string   SQL query with template variables to convert
270 * @return resource    SQL resource handle of the executed query
271 */
272function serendipity_db_schema_import($query) {
273    static $search  = array('{AUTOINCREMENT}', '{PRIMARY}', '{UNSIGNED}',
274        '{FULLTEXT}', '{BOOLEAN}', 'int(1)', 'int(10)', 'int(11)', 'int(4)', '{UTF_8}', '{TEXT}');
275    static $replace = array('SERIAL', 'primary key', '', '', 'BOOLEAN NOT NULL', 'int2',
276        'int4', 'int4', 'int4', '', 'text');
277
278    if (stristr($query, '{FULLTEXT_MYSQL}')) {
279        return true;
280    }
281
282    $query = trim(str_replace($search, $replace, $query));
283    if ($query[0] == '@') {
284        // Errors are expected to happen (like duplicate index creation)
285        return serendipity_db_query(substr($query, 1), false, 'both', false, false, false, true);
286    } else {
287        return serendipity_db_query($query);
288    }
289}
290
291/**
292 * Try to connect to the configured Database (during installation)
293 *
294 * @access public
295 * @param  array     input configuration array, holding the connection info
296 * @param  array     referenced array which holds the errors that might be encountered
297 * @return boolean   return true on success, false on error
298 */
299function serendipity_db_probe($hash, &$errs) {
300    global $serendipity;
301
302    if(!in_array('pgsql', PDO::getAvailableDrivers())) {
303        $errs[] = 'PDO_PGSQL driver not avialable';
304        return false;
305    }
306
307    $serendipity['dbConn'] = new PDO(
308                               sprintf(
309                                 'pgsql:%sdbname=%s',
310                                 strlen($hash['dbHost']) ? ('host=' . $hash['dbHost'] . ';') : '',
311                                 $hash['dbName']
312                               ),
313                               $hash['dbUser'],
314                               $hash['dbPass']
315                             );
316
317    if (!$serendipity['dbConn']) {
318        $errs[] = 'Could not connect to database; check your settings.';
319        return false;
320    }
321
322    return true;
323}
324
325/**
326 * Returns the SQL code used for concatenating strings
327 *
328 * @access public
329 * @param  string   Input string/column to concatenate
330 * @return string   SQL parameter
331 */
332function serendipity_db_concat($string) {
333    return '(' . str_replace(', ', '||', $string) . ')';
334}
335
336/* vim: set sts=4 ts=4 expandtab : */
337