]> projects.mako.cc - scuttle/blob - includes/db/postgres.php
updated scuttle to add archive.org wayback links
[scuttle] / includes / db / postgres.php
1 <?php
2 /** 
3 *
4 * @package dbal_postgres
5 * @version $Id: postgres.php,v 1.2 2005/06/10 08:52:03 devalley Exp $
6 * @copyright (c) 2005 phpBB Group 
7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License 
8 *
9 */
10
11 /**
12 * @ignore
13 */
14 if (!defined('SQL_LAYER'))
15 {
16
17 define('SQL_LAYER', 'postgresql');
18
19 /**
20 * @package dbal_postgres
21 * PostgreSQL Database Abstraction Layer
22 * Minimum Requirement is Version 7.3+
23 */
24 class sql_db
25 {
26         var $db_connect_id;
27         var $query_result;
28         var $return_on_error = false;
29         var $transaction = false;
30         var $sql_time = 0;
31         var $num_queries = 0;
32         var $open_queries = array();
33
34         function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
35         {
36                 $this->connect_string = '';
37
38                 if ($sqluser)
39                 {
40                         $this->connect_string .= "user=$sqluser ";
41                 }
42
43                 if ($sqlpassword)
44                 {
45                         $this->connect_string .= "password=$sqlpassword ";
46                 }
47
48                 if ($sqlserver)
49                 {
50                         if (ereg(":", $sqlserver))
51                         {
52                                 list($sqlserver, $sqlport) = split(":", $sqlserver);
53                                 $this->connect_string .= "host=$sqlserver port=$sqlport ";
54                         }
55                         else
56                         {
57                                 if ($sqlserver != "localhost")
58                                 {
59                                         $this->connect_string .= "host=$sqlserver ";
60                                 }
61                         
62                                 if ($port)
63                                 {
64                                         $this->connect_string .= "port=$port ";
65                                 }
66                         }
67                 }
68
69                 if ($database)
70                 {
71                         $this->dbname = $database;
72                         $this->connect_string .= "dbname=$database";
73                 }
74
75                 $this->persistency = $persistency;
76
77                 $this->db_connect_id = ($this->persistency) ? @pg_pconnect($this->connect_string) : @pg_connect($this->connect_string);
78
79                 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
80         }
81
82         //
83         // Other base methods
84         //
85         function sql_close()
86         {
87                 if (!$this->db_connect_id)
88                 {
89                         return false;
90                 }
91
92                 if ($this->transaction)
93                 {
94                         @pg_exec($this->db_connect_id, 'COMMIT');
95                 }
96
97                 return @pg_close($this->db_connect_id);
98         }
99
100         function sql_return_on_error($fail = false)
101         {
102                 $this->return_on_error = $fail;
103         }
104
105         function sql_num_queries()
106         {
107                 return $this->num_queries;
108         }
109
110         function sql_transaction($status = 'begin')
111         {
112                 switch ($status)
113                 {
114                         case 'begin':
115                                 $result = @pg_exec($this->db_connect_id, 'BEGIN');
116                                 $this->transaction = true;
117                                 break;
118
119                         case 'commit':
120                                 $result = @pg_exec($this->db_connect_id, 'COMMIT');
121                                 $this->transaction = false;
122
123                                 if (!$result)
124                                 {
125                                         @pg_exec($this->db_connect_id, 'ROLLBACK');
126                                 }
127                                 break;
128
129                         case 'rollback':
130                                 $result = @pg_exec($this->db_connect_id, 'ROLLBACK');
131                                 $this->transaction = false;
132                                 break;
133
134                         default:
135                                 $result = true;
136                 }
137
138                 return $result;
139         }
140
141         // Base query method
142         function sql_query($query = '', $cache_ttl = 0)
143         {
144                 if ($query != '')
145                 {
146                         global $cache;
147
148                         // EXPLAIN only in extra debug mode
149                         if (defined('DEBUG_EXTRA'))
150                         {
151                                 $this->sql_report('start', $query);
152                         }
153
154                         $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
155                         
156                         if (!$this->query_result)
157                         {
158                                 $this->num_queries++;
159                                 $this->last_query_text = $query;
160
161                                 if (($this->query_result = @pg_exec($this->db_connect_id, $query)) === false)
162                                 {
163                                         $this->sql_error($query);
164                                 }
165
166                                 if (defined('DEBUG_EXTRA'))
167                                 {
168                                         $this->sql_report('stop', $query);
169                                 }
170
171                                 if ($cache_ttl && method_exists($cache, 'sql_save'))
172                                 {
173                                         $cache->sql_save($query, $this->query_result, $cache_ttl);
174                                 }
175                         }
176                         else if (defined('DEBUG_EXTRA'))
177                         {
178                                 $this->sql_report('fromcache', $query);
179                         }
180                 }
181                 else
182                 {
183                         return false;
184                 }
185
186                 return ($this->query_result) ? $this->query_result : false;
187         }
188
189         function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) 
190         { 
191                 if ($query != '') 
192                 {
193                         $this->query_result = false; 
194
195                         // if $total is set to 0 we do not want to limit the number of rows
196                         if ($total == 0)
197                         {
198                                 $total = -1;
199                         }
200
201                         $query .= "\n LIMIT $total OFFSET $offset";
202
203                         return $this->sql_query($query, $cache_ttl); 
204                 } 
205                 else 
206                 { 
207                         return false; 
208                 } 
209         }
210
211         // Idea for this from Ikonboard
212         function sql_build_array($query, $assoc_ary = false)
213         {
214                 if (!is_array($assoc_ary))
215                 {
216                         return false;
217                 }
218
219                 $fields = array();
220                 $values = array();
221                 if ($query == 'INSERT')
222                 {
223                         foreach ($assoc_ary as $key => $var)
224                         {
225                                 $fields[] = $key;
226
227                                 if (is_null($var))
228                                 {
229                                         $values[] = 'NULL';
230                                 }
231                                 elseif (is_string($var))
232                                 {
233                                         $values[] = "'" . $this->sql_escape($var) . "'";
234                                 }
235                                 else
236                                 {
237                                         $values[] = (is_bool($var)) ? intval($var) : $var;
238                                 }
239                         }
240
241                         $query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
242                 }
243                 else if ($query == 'UPDATE' || $query == 'SELECT')
244                 {
245                         $values = array();
246                         foreach ($assoc_ary as $key => $var)
247                         {
248                                 if (is_null($var))
249                                 {
250                                         $values[] = "$key = NULL";
251                                 }
252                                 elseif (is_string($var))
253                                 {
254                                         $values[] = "$key = '" . $this->sql_escape($var) . "'";
255                                 }
256                                 else
257                                 {
258                                         $values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
259                                 }
260                         }
261                         $query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values);
262                 }
263
264                 return $query;
265         }
266
267         // Other query methods
268         //
269         // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ...
270         //         don't want this here by a middle Milestone
271         function sql_numrows($query_id = false)
272         {
273                 if (!$query_id)
274                 {
275                         $query_id = $this->query_result;
276                 }
277
278                 return ($query_id) ? @pg_numrows($query_id) : false;
279         }
280
281         function sql_affectedrows($query_id = false)
282         {
283                 if (!$query_id)
284                 {
285                         $query_id = $this->query_result;
286                 }
287
288                 return ($query_id) ? @pg_cmdtuples($query_id) : false;
289         }
290
291         function sql_fetchrow($query_id = false)
292         {
293                 global $cache;
294
295                 if (!$query_id)
296                 {
297                         $query_id = $this->query_result;
298                 }
299
300                 if (!isset($this->rownum[$query_id]))
301                 {
302                         $this->rownum[$query_id] = 0;
303                 }
304
305                 if (isset($cache->sql_rowset[$query_id]))
306                 {
307                         return $cache->sql_fetchrow($query_id);
308                 }
309
310                 $result = @pg_fetch_array($query_id, NULL, PGSQL_ASSOC);
311                 
312                 if ($result)
313                 {
314                         $this->rownum[$query_id]++;
315                 }
316
317                 return $result;
318         }
319
320         function sql_fetchrowset($query_id = false)
321         {
322                 if (!$query_id)
323                 {
324                         $query_id = $this->query_result;
325                 }
326
327                 $result = array();
328
329                 if ($query_id)
330                 {
331                         unset($this->rowset[$query_id]);
332                         unset($this->row[$query_id]);
333
334                         $result = array();
335                         while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id))
336                         {
337                                 $result[] = $this->rowset[$query_id];
338                         }
339                         return $result;
340                 }
341
342                 return false;
343         }
344
345         function sql_fetchfield($field, $rownum = -1, $query_id = false)
346         {
347                 if (!$query_id)
348                 {
349                         $query_id = $this->query_result;
350                 }
351
352                 if ($query_id)
353                 {
354                         if ($rownum > -1)
355                         {
356                                 if (@function_exists('pg_result_seek'))
357                                 {
358                                         @pg_result_seek($query_id, $rownum);
359                                         $row = @pg_fetch_assoc($query_id);
360                                         $result = isset($row[$field]) ? $row[$field] : false;
361                                 }
362                                 else
363                                 {
364                                         $this->sql_rowseek($offset, $query_id);
365                                         $row = $this->sql_fetchrow($query_id);
366                                         $result = isset($row[$field]) ? $row[$field] : false;
367                                 }
368                         }
369                         else
370                         {
371                                 if (empty($this->row[$query_id]) && empty($this->rowset[$query_id]))
372                                 {
373                                         if ($this->sql_fetchrow($query_id))
374                                         {
375                                                 $result = $this->row[$query_id][$field];
376                                         }
377                                 }
378                                 else
379                                 {
380                                         if ($this->rowset[$query_id])
381                                         {
382                                                 $result = $this->rowset[$query_id][$field];
383                                         }
384                                         elseif ($this->row[$query_id])
385                                         {
386                                                 $result = $this->row[$query_id][$field];
387                                         }
388                                 }
389                         }
390                         return $result;
391                 }
392                 return false;
393         }
394
395         function sql_rowseek($offset, $query_id = false)
396         {
397                 if (!$query_id)
398                 {
399                         $query_id = $this->query_result;
400                 }
401
402                 if ($query_id)
403                 {
404                         if ($offset > -1)
405                         {
406                                 if (@function_exists('pg_result_seek'))
407                                 {
408                                         @pg_result_seek($query_id, $rownum);
409                                 }
410                                 else
411                                 {
412                                         for ($i = $this->rownum[$query_id]; $i < $offset; $i++)
413                                         {
414                                                 $this->sql_fetchrow($query_id);
415                                         }
416                                 }
417                                 return true;
418                         }
419                         else
420                         {
421                                 return false;
422                         }
423                 }
424
425                 return false;
426         }
427
428         function sql_nextid()
429         {
430                 $query_id = $this->query_result;
431
432                 if ($query_id && $this->last_query_text != '')
433                 {
434                         if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
435                         {
436                                 $query = "SELECT currval('" . $tablename[1] . "_id_seq') AS last_value";
437                                 $temp_q_id =  @pg_exec($this->db_connect_id, $query);
438                                 if (!$temp_q_id)
439                                 {
440                                         return false;
441                                 }
442
443                                 $temp_result = @pg_fetch_array($temp_q_id, NULL, PGSQL_ASSOC);
444
445                                 return ($temp_result) ? $temp_result['last_value'] : false;
446                         }
447                 }
448
449                 return false;
450         }
451
452         function sql_freeresult($query_id = false)
453         {
454                 if (!$query_id)
455                 {
456                         $query_id = $this->query_result;
457                 }
458
459                 return (is_resource($query_id)) ? @pg_freeresult($query_id) : false;
460         }
461
462         function sql_escape($msg)
463         {
464                 return str_replace("'", "''", str_replace('\\', '\\\\', $msg));
465         }
466
467         function sql_error($sql = '')
468         {
469                 if (!$this->return_on_error)
470                 {
471                         $this_page = (isset($_SERVER['PHP_SELF']) && !empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF'];
472                         $this_page .= '&' . ((isset($_SERVER['QUERY_STRING']) && !empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : (isset($_ENV['QUERY_STRING']) ? $_ENV['QUERY_STRING'] : ''));
473
474                         $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @pg_errormessage() . '<br /><br /><u>CALLING PAGE</u><br /><br />'  . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />';
475
476                         if ($this->transaction)
477                         {
478                                 $this->sql_transaction('rollback');
479                         }
480                         
481                         trigger_error($message, E_USER_ERROR);
482                 }
483
484                 $result = array(
485                         'message'       => @pg_errormessage(),
486                         'code'          => ''
487                 );
488
489                 return $result;
490         }
491
492         function sql_report($mode, $query = '')
493         {
494                 if (empty($_GET['explain']))
495                 {
496                         return;
497                 }
498
499                 global $cache, $starttime, $phpbb_root_path;
500                 static $curtime, $query_hold, $html_hold;
501                 static $sql_report = '';
502                 static $cache_num_queries = 0;
503
504                 if (!$query && !empty($query_hold))
505                 {
506                         $query = $query_hold;
507                 }
508
509                 switch ($mode)
510                 {
511                         case 'display':
512                                 if (!empty($cache))
513                                 {
514                                         $cache->unload();
515                                 }
516                                 $this->sql_close();
517
518                                 $mtime = explode(' ', microtime());
519                                 $totaltime = $mtime[0] + $mtime[1] - $starttime;
520
521                                 echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8869-1"><meta http-equiv="Content-Style-Type" content="text/css"><link rel="stylesheet" href="' . $phpbb_root_path . 'adm/subSilver.css" type="text/css"><style type="text/css">' . "\n";
522                                 echo 'th { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic3.gif\') }' . "\n";
523                                 echo 'td.cat    { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic1.gif\') }' . "\n";
524                                 echo '</style><title>' . $msg_title . '</title></head><body>';
525                                 echo '<table width="100%" cellspacing="0" cellpadding="0" border="0"><tr><td><a href="' . htmlspecialchars(preg_replace('/&explain=([^&]*)/', '', $_SERVER['REQUEST_URI'])) . '"><img src="' . $phpbb_root_path . 'adm/images/header_left.jpg" width="200" height="60" alt="phpBB Logo" title="phpBB Logo" border="0"/></a></td><td width="100%" background="' . $phpbb_root_path . 'adm/images/header_bg.jpg" height="60" align="right" nowrap="nowrap"><span class="maintitle">SQL Report</span> &nbsp; &nbsp; &nbsp;</td></tr></table><br clear="all"/><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td height="40" align="center" valign="middle"><b>Page generated in ' . round($totaltime, 4) . " seconds with {$this->num_queries} queries" . (($cache_num_queries) ? " + $cache_num_queries " . (($cache_num_queries == 1) ? 'query' : 'queries') . ' returning data from cache' : '') . '</b></td></tr><tr><td align="center" nowrap="nowrap">Time spent on MySQL queries: <b>' . round($this->sql_time, 5) . 's</b> | Time spent on PHP: <b>' . round($totaltime - $this->sql_time, 5) . 's</b></td></tr></table><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td>';
526                                 echo $sql_report;
527                                 echo '</td></tr></table><br /></body></html>';
528                                 exit;
529                                 break;
530
531                         case 'start':
532                                 $query_hold = $query;
533                                 $html_hold = '';
534
535                                 $curtime = explode(' ', microtime());
536                                 $curtime = $curtime[0] + $curtime[1];
537                                 break;
538
539                         case 'fromcache':
540                                 $endtime = explode(' ', microtime());
541                                 $endtime = $endtime[0] + $endtime[1];
542
543                                 $result = @pg_exec($this->db_connect_id, $query);
544                                 while ($void = @pg_fetch_array($result, NULL, PGSQL_ASSOC))
545                                 {
546                                         // Take the time spent on parsing rows into account
547                                 }
548                                 $splittime = explode(' ', microtime());
549                                 $splittime = $splittime[0] + $splittime[1];
550
551                                 $time_cache = $endtime - $curtime;
552                                 $time_db = $splittime - $endtime;
553                                 $color = ($time_db > $time_cache) ? 'green' : 'red';
554
555                                 $sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query results obtained from the cache</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table><p align="center">';
556
557                                 $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed [cache]: <b style="color: ' . $color . '">' . sprintf('%.5f', ($time_cache)) . 's</b> | Elapsed [db]: <b>' . sprintf('%.5f', $time_db) . 's</b></p>';
558
559                                 // Pad the start time to not interfere with page timing
560                                 $starttime += $time_db;
561
562                                 @pg_freeresult($result);
563                                 $cache_num_queries++;
564                                 break;
565
566                         case 'stop':
567                                 $endtime = explode(' ', microtime());
568                                 $endtime = $endtime[0] + $endtime[1];
569
570                                 $sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query #' . $this->num_queries . '</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table> ' . $html_hold . '<p align="center">';
571
572                                 if ($this->query_result)
573                                 {
574                                         if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query))
575                                         {
576                                                 $sql_report .= "Affected rows: <b>" . $this->sql_affectedrows($this->query_result) . '</b> | ';
577                                         }
578                                         $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $curtime) . 's</b>';
579                                 }
580                                 else
581                                 {
582                                         $error = $this->sql_error();
583                                         $sql_report .= '<b style="color: red">FAILED</b> - ' . SQL_LAYER . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']);
584                                 }
585
586                                 $sql_report .= '</p>';
587
588                                 $this->sql_time += $endtime - $curtime;
589                                 break;
590                 }
591         }
592
593 } // class ... db_sql
594
595 } // if ... defined
596
597 ?>

Benjamin Mako Hill || Want to submit a patch?