projects.mako.cc - scuttle/blob - includes/db/sqlite.php

   1 <?php
   2 /**
   3 *
   4 * @package dbal_sqlite
   5 * @version $Id: sqlite.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","sqlite");
  18
  19 /**
  20 * @package dbal_sqlite
  21 * Sqlite Database Abstraction Layer
  22 */
  23 class sql_db
  24 {
  25         var $db_connect_id;
  26         var $query_result;
  27         var $return_on_error = false;
  28         var $transaction = false;
  29         var $sql_report = '';
  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, $persistency = false)
  35         {
  36                 $this->persistency = $persistency;
  37                 $this->user = $sqluser;
  38                 $this->server = $sqlserver . (($port) ? ':' . $port : '');
  39                 $this->dbname = $database;
  40
  41                 $this->db_connect_id = ($this->persistency) ? @sqlite_popen($this->server, 0, $error) : @sqlite_open($this->server, 0, $error);
  42
  43                 return ($this->db_connect_id) ? true : $error;
  44         }
  45
  46         // Other base methods
  47         function sql_close()
  48         {
  49                 if (!$this->db_connect_id)
  50                 {
  51                         return false;
  52                 }
  53
  54                 return @sqlite_close($this->db_connect_id);
  55         }
  56
  57         function sql_return_on_error($fail = false)
  58         {
  59                 $this->return_on_error = $fail;
  60         }
  61
  62         function sql_num_queries()
  63         {
  64                 return $this->num_queries;
  65         }
  66
  67         function sql_transaction($status = 'begin')
  68         {
  69                 switch ($status)
  70                 {
  71                         case 'begin':
  72                                 $this->transaction = true;
  73                                 $result = @sqlite_query('BEGIN', $this->db_connect_id);
  74                                 break;
  75
  76                         case 'commit':
  77                                 $this->transaction = false;
  78                                 $result = @sqlite_query('COMMIT', $this->db_connect_id);
  79                                 break;
  80
  81                         case 'rollback':
  82                                 $this->transaction = false;
  83                                 $result = @sqlite_query('ROLLBACK', $this->db_connect_id);
  84                                 break;
  85
  86                         default:
  87                                 $result = true;
  88                 }
  89
  90                 return $result;
  91         }
  92
  93         // Base query method
  94         function sql_query($query = '', $expire_time = 0)
  95         {
  96                 if ($query != '')
  97                 {
  98                         global $cache;
  99
 100                         $query = preg_replace('#FROM \((.*?)\)(,|[\n\t ]+?WHERE) #s', 'FROM \1\2 ', $query);
 101
 102                         if (!$expire_time || !$cache->sql_load($query, $expire_time))
 103                         {
 104                                 if ($expire_time)
 105                                 {
 106                                         $cache_result = true;
 107                                 }
 108
 109                                 $this->query_result = false;
 110                                 $this->num_queries++;
 111
 112                                 if (!empty($_GET['explain']))
 113                                 {
 114                                         global $starttime;
 115
 116                                         $curtime = explode(' ', microtime());
 117                                         $curtime = $curtime[0] + $curtime[1] - $starttime;
 118                                 }
 119
 120                                 if (!($this->query_result = @sqlite_query($query, $this->db_connect_id)))
 121                                 {
 122                                         $this->sql_error($query);
 123                                 }
 124
 125                                 if (!empty($_GET['explain']))
 126                                 {
 127                                         $endtime = explode(' ', microtime());
 128                                         $endtime = $endtime[0] + $endtime[1] - $starttime;
 129
 130                                         $this->sql_report .= "<pre>Query:\t" . htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n\t", $query)) . "\n\n";
 131
 132                                         if ($this->query_result)
 133                                         {
 134                                                 $this->sql_report .= "Time before:  $curtime\nTime after:   $endtime\nElapsed time: <b>" . ($endtime - $curtime) . "</b>\n</pre>";
 135                                         }
 136                                         else
 137                                         {
 138                                                 $error = $this->sql_error();
 139                                                 $this->sql_report .= '<b>FAILED</b> - SQLite ' . $error['code'] . ': ' . htmlspecialchars($error['message']) . '<br><br><pre>';
 140                                         }
 141
 142                                         $this->sql_time += $endtime - $curtime;
 143
 144                                         if (preg_match('#^SELECT#', $query))
 145                                         {
 146                                                 $html_table = FALSE;
 147                                                 if ($result = @sqlite_query("EXPLAIN $query", $this->db_connect_id))
 148                                                 {
 149                                                         while ($row = @sqlite_fetch_array($result, @sqlite_ASSOC))
 150                                                         {
 151                                                                 if (!$html_table && sizeof($row))
 152                                                                 {
 153                                                                         $html_table = TRUE;
 154                                                                         $this->sql_report .= "<table width=100% border=1 cellpadding=2 cellspacing=1>\n";
 155                                                                         $this->sql_report .= "<tr>\n<td><b>" . implode("</b></td>\n<td><b>", array_keys($row)) . "</b></td>\n</tr>\n";
 156                                                                 }
 157                                                                 $this->sql_report .= "<tr>\n<td>" . implode("&nbsp;</td>\n<td>", array_values($row)) . "&nbsp;</td>\n</tr>\n";
 158                                                         }
 159                                                 }
 160
 161                                                 if ($html_table)
 162                                                 {
 163                                                         $this->sql_report .= '</table><br>';
 164                                                 }
 165                                         }
 166
 167                                         $this->sql_report .= "<hr>\n";
 168                                 }
 169
 170                                 if (preg_match('#^SELECT#', $query))
 171                                 {
 172                                         $this->open_queries[] = $this->query_result;
 173                                 }
 174                         }
 175
 176                         if (!empty($cache_result))
 177                         {
 178                                 $cache->sql_save($query, $this->query_result);
 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, $expire_time = 0)
 190         {
 191                 if ($query != '')
 192                 {
 193                         $this->query_result = false;
 194
 195                         $query .= ' LIMIT ' . ((!empty($offset)) ? $total . ' OFFSET ' . $offset : $total);
 196
 197                         return $this->sql_query($query, $expire_time);
 198                 }
 199                 else
 200                 {
 201                         return false;
 202                 }
 203         }
 204
 205         // Idea for this from Ikonboard
 206         function sql_build_array($query, $assoc_ary = false)
 207         {
 208                 if (!is_array($assoc_ary))
 209                 {
 210                         return false;
 211                 }
 212
 213                 $fields = array();
 214                 $values = array();
 215                 if ($query == 'INSERT')
 216                 {
 217                         foreach ($assoc_ary as $key => $var)
 218                         {
 219                                 $fields[] = $key;
 220
 221                                 if (is_null($var))
 222                                 {
 223                                         $values[] = 'NULL';
 224                                 }
 225                                 elseif (is_string($var))
 226                                 {
 227                                         $values[] = "'" . $this->sql_escape($var) . "'";
 228                                 }
 229                                 else
 230                                 {
 231                                         $values[] = (is_bool($var)) ? intval($var) : $var;
 232                                 }
 233                         }
 234
 235                         $query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
 236                 }
 237                 else if ($query == 'UPDATE')
 238                 {
 239                         $values = array();
 240                         foreach ($assoc_ary as $key => $var)
 241                         {
 242                                 if (is_null($var))
 243                                 {
 244                                         $values[] = "$key = NULL";
 245                                 }
 246                                 elseif (is_string($var))
 247                                 {
 248                                         $values[] = "$key = '" . $this->sql_escape($var) . "'";
 249                                 }
 250                                 else
 251                                 {
 252                                         $values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
 253                                 }
 254                         }
 255                         $query = implode(', ', $values);
 256                 }
 257
 258                 return $query;
 259         }
 260
 261         // Other query methods
 262         //
 263         // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ...
 264         //         don't want this here by a middle Milestone
 265         function sql_numrows($query_id = false)
 266         {
 267                 if (!$query_id)
 268                 {
 269                         $query_id = $this->query_result;
 270                 }
 271
 272                 return ($query_id) ? @sqlite_num_rows($query_id) : false;
 273         }
 274
 275         function sql_affectedrows()
 276         {
 277                 return ($this->db_connect_id) ? @sqlite_changes($this->db_connect_id) : false;
 278         }
 279
 280         function sql_fetchrow($query_id = 0)
 281         {
 282                 global $cache;
 283
 284                 if (!$query_id)
 285                 {
 286                         $query_id = $this->query_result;
 287                 }
 288
 289                 if ($cache->sql_exists($query_id))
 290                 {
 291                         return $cache->sql_fetchrow($query_id);
 292                 }
 293
 294                 return ($query_id) ? @sqlite_fetch_array($query_id, @sqlite_ASSOC) : false;
 295         }
 296
 297         function sql_fetchrowset($query_id = 0)
 298         {
 299                 if (!$query_id)
 300                 {
 301                         $query_id = $this->query_result;
 302                 }
 303
 304                 if ($query_id)
 305                 {
 306                         unset($this->rowset[$query_id]);
 307                         unset($this->row[$query_id]);
 308                         while ($this->rowset[$query_id] = @sqlite_fetch_array($query_id, @sqlite_ASSOC))
 309                         {
 310                                 $result[] = $this->rowset[$query_id];
 311                         }
 312                         return $result;
 313                 }
 314                 else
 315                 {
 316                         return false;
 317                 }
 318         }
 319
 320         function sql_fetchfield($field, $rownum = -1, $query_id = 0)
 321         {
 322                 if (!$query_id)
 323                 {
 324                         $query_id = $this->query_result;
 325                 }
 326
 327                 if ($query_id)
 328                 {
 329                         return ($rownum > -1) ? ((@sqlite_seek($query_id, $rownum)) ? @sqlite_column($query_id, $field) : false) : @sqlite_column($query_id, $field);
 330                 }
 331         }
 332
 333         function sql_rowseek($rownum, $query_id = 0)
 334         {
 335                 if (!$query_id)
 336                 {
 337                         $query_id = $this->query_result;
 338                 }
 339
 340                 return ($query_id) ? @sqlite_seek($query_id, $rownum) : false;
 341         }
 342
 343         function sql_nextid()
 344         {
 345                 return ($this->db_connect_id) ? @sqlite_last_insert_rowid($this->db_connect_id) : false;
 346         }
 347
 348         function sql_freeresult($query_id = false)
 349         {
 350                 return true;
 351         }
 352
 353         function sql_escape($msg)
 354         {
 355                 return @sqlite_escape_string(stripslashes($msg));
 356         }
 357
 358         function sql_error($sql = '')
 359         {
 360                 if (!$this->return_on_error)
 361                 {
 362                         $this_page = (!empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF'];
 363                         $this_page .= '&' . ((!empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : $_ENV['QUERY_STRING']);
 364
 365                         $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @sqlite_error_string(@sqlite_last_error($this->db_connect_id)) . '<br /><br /><u>CALLING PAGE</u><br /><br />'  . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />';
 366
 367                         if ($this->transaction)
 368                         {
 369                                 $this->sql_transaction('rollback');
 370                         }
 371
 372                         trigger_error($message, E_USER_ERROR);
 373                 }
 374
 375                 $result = array(
 376                         'message'       => @sqlite_error_string(@sqlite_last_error($this->db_connect_id)),
 377                         'code'          => @sqlite_last_error($this->db_connect_id)
 378                 );
 379
 380                 return $result;
 381         }
 382
 383 } // class sql_db
 384
 385 } // if ... define
 386
 387 ?>