[ Index ]

PHP Cross Reference of MyBB

title

Body

[close]

/inc/ -> db_sqlite.php (source)

   1  <?php
   2  /**
   3   * MyBB 1.6
   4   * Copyright 2010 MyBB Group, All Rights Reserved
   5   *
   6   * Website: http://www.mybboard.com
   7   * License: http://www.mybboard.com/license.php
   8   *
   9   * $Id$
  10   */
  11  
  12  class DB_SQLite
  13  {
  14      /**
  15       * The title of this layer.
  16       *
  17       * @var string
  18       */
  19      public $title = "SQLite 3";
  20      
  21      /**
  22       * The short title of this layer.
  23       *
  24       * @var string
  25       */
  26      public $short_title = "SQLite";
  27      
  28      /**
  29       * The type of db software being used.
  30       *
  31       * @var string
  32       */
  33      public $type;
  34  
  35      /**
  36       * A count of the number of queries.
  37       *
  38       * @var int
  39       */
  40      public $query_count = 0;
  41  
  42      /**
  43       * A list of the performed queries.
  44       *
  45       * @var array
  46       */
  47      public $querylist = array();
  48  
  49      /**
  50       * 1 if error reporting enabled, 0 if disabled.
  51       *
  52       * @var boolean
  53       */
  54      public $error_reporting = 1;
  55  
  56      /**
  57       * The database connection resource.
  58       *
  59       * @var resource
  60       */
  61      public $link;
  62  
  63      /**
  64       * Explanation of a query.
  65       *
  66       * @var string
  67       */
  68      public $explain;
  69  
  70      /**
  71       * The current version of SQLite.
  72       *
  73       * @var string
  74       */
  75      public $version;
  76  
  77      /**
  78       * The current table type in use (myisam/innodb)
  79       *
  80       * @var string
  81       */
  82      public $table_type = "myisam";
  83      
  84      /**
  85       * The table prefix used for simple select, update, insert and delete queries
  86       *
  87       * @var string
  88       */
  89      public $table_prefix;
  90      
  91      /**
  92       * The extension used to run the SQL database
  93       *
  94       * @var string
  95       */
  96      public $engine = "pdo";
  97      
  98      /**
  99       * Weather or not this engine can use the search functionality
 100       *
 101       * @var boolean
 102       */
 103      public $can_search = true;
 104  
 105      /**
 106       * The database encoding currently in use (if supported)
 107       *
 108       * @var string
 109       */
 110      public $db_encoding = "";
 111  
 112      /**
 113       * The time spent performing queries
 114       *
 115       * @var float
 116       */
 117      public $query_time = 0;
 118  
 119      /**
 120       * Connect to the database server.
 121       *
 122       * @param array Array of DBMS connection details.
 123       * @return resource The DB connection resource. Returns false on failure.
 124       */
 125  	function connect($config)
 126      {
 127          $this->get_execution_time();
 128          
 129          require_once  MYBB_ROOT."inc/db_pdo.php";
 130          
 131          $this->db = new dbpdoEngine("sqlite:{$config['database']}");
 132  
 133          $query_time = $this->get_execution_time();
 134  
 135          $this->query_time += $query_time;
 136  
 137          $this->connections[] = "[WRITE] {$config['database']} (Connected in ".number_format($query_time, 0)."s)";
 138          
 139          if($this->db)
 140          {
 141              $this->query('PRAGMA short_column_names = 1');
 142              return true;
 143          }
 144          else
 145          {
 146              return false;
 147          }
 148      }
 149  
 150      /**
 151       * Query the database.
 152       *
 153       * @param string The query SQL.
 154       * @param boolean 1 if hide errors, 0 if not.
 155       * @return resource The query data.
 156       */
 157  	function query($string, $hide_errors=0)
 158      {
 159          global $pagestarttime, $db, $mybb;
 160          
 161          $this->get_execution_time();
 162  
 163          if(strtolower(substr(ltrim($string), 0, 5)) == 'alter')
 164          {
 165              $string = preg_replace("#\sAFTER\s([a-z_]+?)(;*?)$#i", "", $string);
 166              
 167              $queryparts = preg_split("/[\s]+/", $string, 4, PREG_SPLIT_NO_EMPTY);
 168              $tablename = $queryparts[2];
 169              $alterdefs = $queryparts[3];
 170              if(strtolower($queryparts[1]) != 'table' || $queryparts[2] == '')
 171              {
 172                  $this->error_msg = "near \"{$queryparts[0]}\": syntax error";
 173              }
 174              else
 175              {                
 176                  // SQLITE 3 supports ADD Alter statements
 177                  if(strtolower(substr(ltrim($string), 0, 3)) == 'add')
 178                  {
 179                      $query = $this->db->query($string);
 180                  }
 181                  else
 182                  {
 183                      $query = $this->alter_table_parse($tablename, $alterdefs, $string);
 184                  }
 185              }
 186          }
 187            else
 188            {
 189              try
 190              {
 191                  $query = $this->db->query($string);
 192              }
 193              catch(PDOException $exception)
 194              {
 195                  $error = array(
 196                      "message" => $exception->getMessage(),
 197                      "code" => $exception->getCode()
 198                  );
 199  
 200                  $this->error($error['message'], $error['code']);
 201              }
 202          }
 203          
 204          if($this->error_number($query) > 0 && !$hide_errors)
 205          {
 206              $this->error($string, $query);
 207              exit;
 208          }
 209          
 210          $query_time = $this->get_execution_time();
 211          $this->query_time += $query_time;
 212          $this->query_count++;
 213          
 214          if($mybb->debug_mode)
 215          {
 216              $this->explain_query($string, $query_time);
 217          }
 218          return $query;
 219      }
 220  
 221      /**
 222       * Explain a query on the database.
 223       *
 224       * @param string The query SQL.
 225       * @param string The time it took to perform the query.
 226       */
 227  	function explain_query($string, $qtime)
 228      {
 229          if(preg_match("#^\s*select#i", $string))
 230          {
 231              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 232                  "<tr>\n".
 233                  "<td colspan=\"8\" style=\"background-color: #ccc;\"><strong>#".$this->query_count." - Select Query</strong></td>\n".
 234                  "</tr>\n".
 235                  "<tr>\n".
 236                  "<td colspan=\"8\" style=\"background-color: #fefefe;\"><span style=\"font-family: Courier; font-size: 14px;\">".htmlspecialchars_uni($string)."</span></td>\n".
 237                  "</tr>\n".
 238                  "<tr>\n".
 239                  "<td colspan=\"8\" style=\"background-color: #fff;\">Query Time: ".$qtime."</td>\n".
 240                  "</tr>\n".
 241                  "</table>\n".
 242                  "<br />\n";
 243          }
 244          else
 245          {
 246              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 247                  "<tr>\n".
 248                  "<td style=\"background-color: #ccc;\"><strong>#".$this->query_count." - Write Query</strong></td>\n".
 249                  "</tr>\n".
 250                  "<tr style=\"background-color: #fefefe;\">\n".
 251                  "<td><span style=\"font-family: Courier; font-size: 14px;\">".htmlspecialchars_uni($string)."</span></td>\n".
 252                  "</tr>\n".
 253                  "<tr>\n".
 254                  "<td bgcolor=\"#ffffff\">Query Time: ".$qtime."</td>\n".
 255                  "</tr>\n".
 256                  "</table>\n".
 257                  "<br />\n";
 258          }
 259  
 260          $this->querylist[$this->query_count]['query'] = $string;
 261          $this->querylist[$this->query_count]['time'] = $qtime;
 262      }
 263      
 264      /**
 265       * Execute a write query on the database
 266       *
 267       * @param string The query SQL.
 268       * @param boolean 1 if hide errors, 0 if not.
 269       * @return resource The query data.
 270       */
 271  	function write_query($query, $hide_errors=0)
 272      {
 273          return $this->query($query, $hide_errors);
 274      }
 275  
 276      /**
 277       * Return a result array for a query.
 278       *
 279       * @param resource The result data.
 280       * @param constant The type of array to return.
 281       * @return array The array of results.
 282       */
 283  	function fetch_array($query)
 284      {
 285          $array = $this->db->fetch_array($query);
 286          return $array;
 287      }
 288  
 289      /**
 290       * Return a specific field from a query.
 291       *
 292       * @param resource The query ID.
 293       * @param string The name of the field to return.
 294       * @param int The number of the row to fetch it from.
 295       */
 296  	function fetch_field($query, $field, $row=false)
 297      {
 298          if($row !== false)
 299          {
 300              $this->data_seek($query, $row);
 301          }
 302          $array = $this->fetch_array($query);
 303          return $array[$field];
 304      }
 305  
 306      /**
 307       * Moves internal row pointer to the next row
 308       *
 309       * @param resource The query ID.
 310       * @param int The pointer to move the row to.
 311       */
 312  	function data_seek($query, $row)
 313      {
 314          return $this->db->seek($query, $row);
 315      }
 316  
 317      /**
 318       * Return the number of rows resulting from a query.
 319       *
 320       * @param resource The query data.
 321       * @return int The number of rows in the result.
 322       */
 323  	function num_rows($query)
 324      {
 325          return $this->db->num_rows($query);
 326      }
 327  
 328      /**
 329       * Return the last id number of inserted data.
 330       *
 331       * @return int The id number.
 332       */
 333  	function insert_id($name="")
 334      {
 335          return $this->db->insert_id($name);
 336      }
 337  
 338      /**
 339       * Close the connection with the DBMS.
 340       *
 341       */
 342  	function close()
 343      {
 344          return;
 345      }
 346  
 347      /**
 348       * Return an error number.
 349       *
 350       * @return int The error number of the current error.
 351       */
 352  	function error_number($query="")
 353      {
 354          if(!$query)
 355          {
 356              $query = $this->db->last_query;
 357          }
 358          
 359          $this->error_number = $this->db->error_number($query);
 360          
 361          return $this->error_number;
 362      }
 363  
 364      /**
 365       * Return an error string.
 366       *
 367       * @return string The explanation for the current error.
 368       */
 369  	function error_string($query="")
 370      {
 371          if($this->error_number != "")
 372          {
 373              if(!$query)
 374              {
 375                  $query = $this->db->last_query;
 376              }
 377              
 378              $error_string = $this->db->error_string($query);
 379              $this->error_number = "";
 380          
 381              return $error_string;
 382          }
 383      }
 384  
 385      /**
 386       * Output a database error.
 387       *
 388       * @param string The string to present as an error.
 389       */
 390  	function error($string="", $query="", $error="", $error_no="")
 391      {
 392          $this->db->roll_back();
 393  
 394          if($this->error_reporting)
 395          {
 396              if(!$query)
 397              {
 398                  $query = $this->db->last_query;
 399              }
 400  
 401              if($error_no == "")
 402              {
 403                  $error_no = $this->error_number($query);
 404              }
 405              
 406              if($error == "")
 407              {
 408                  $error = $this->error_string($query);
 409              }
 410  
 411              if(class_exists("errorHandler"))
 412              {
 413                  global $error_handler;
 414                  
 415                  if(!is_object($error_handler))
 416                  {
 417                      require_once  MYBB_ROOT."inc/class_error.php";
 418                      $error_handler = new errorHandler();
 419                  }
 420                  
 421                  $error = array(
 422                      "error_no" => $error_no,
 423                      "error" => $error,
 424                      "query" => $string
 425                  );
 426                  $error_handler->error(MYBB_SQL, $error);
 427              }
 428              else
 429              {
 430                  trigger_error("<strong>[SQL] [{$error_no}] {$error}</strong><br />{$string}", E_USER_ERROR);
 431              }
 432          }
 433      }
 434  
 435  
 436      /**
 437       * Returns the number of affected rows in a query.
 438       *
 439       * @return int The number of affected rows.
 440       */
 441  	function affected_rows($query="")
 442      {
 443          if(!$query)
 444          {
 445              $query = $this->db->last_query;
 446          }
 447          
 448          return $this->db->affected_rows($query);
 449      }
 450  
 451      /**
 452       * Return the number of fields.
 453       *
 454       * @param resource The query data.
 455       * @return int The number of fields.
 456       */
 457  	function num_fields($query)
 458      {
 459          if(!$query)
 460          {
 461              $query = $this->db->last_query;
 462          }
 463          
 464          return $this->db->num_fields($query);
 465      }
 466  
 467      /**
 468       * Lists all functions in the database.
 469       *
 470       * @param string The database name.
 471       * @param string Prefix of the table (optional)
 472       * @return array The table list.
 473       */
 474  	function list_tables($database, $prefix='')
 475      {
 476          if($prefix)
 477          {
 478              $query = $this->query("SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name LIKE '".$this->escape_string($prefix)."%'");
 479          }
 480          else
 481          {
 482              $query = $this->query("SELECT tbl_name FROM sqlite_master WHERE type = 'table'");
 483          }
 484          
 485          while($table = $this->fetch_array($query))
 486          {
 487              $tables[] = $table['tbl_name'];
 488          }
 489          return $tables;
 490      }
 491  
 492      /**
 493       * Check if a table exists in a database.
 494       *
 495       * @param string The table name.
 496       * @return boolean True when exists, false if not.
 497       */
 498  	function table_exists($table)
 499      {
 500          $query = $this->query("SELECT COUNT(name) as count FROM sqlite_master WHERE type='table' AND name='{$this->table_prefix}{$table}'");
 501          $exists = $this->fetch_field($query, "count");
 502  
 503          if($exists > 0)
 504          {
 505              return true;
 506          }
 507          else
 508          {
 509              return false;
 510          }
 511      }
 512  
 513      /**
 514       * Check if a field exists in a database.
 515       *
 516       * @param string The field name.
 517       * @param string The table name.
 518       * @return boolean True when exists, false if not.
 519       */
 520  	function field_exists($field, $table)
 521      {
 522          $query = $this->query("PRAGMA table_info('{$this->table_prefix}{$table}')");
 523          
 524          $exists = 0;
 525          
 526          while($row = $this->fetch_array($query))
 527          {
 528              if($row['name'] == $field)
 529              {
 530                  ++$exists;
 531              }
 532          }
 533          
 534          if($exists > 0)
 535          {
 536              return true;
 537          }
 538          else
 539          {
 540              return false;
 541          }
 542      }
 543  
 544      /**
 545       * Add a shutdown query.
 546       *
 547       * @param resource The query data.
 548       * @param string An optional name for the query.
 549       */
 550  	function shutdown_query($query, $name=0)
 551      {
 552          global $shutdown_queries;
 553          if($name)
 554          {
 555              $shutdown_queries[$name] = $query;
 556          }
 557          else
 558          {
 559              $shutdown_queries[] = $query;
 560          }
 561      }
 562  
 563      /**
 564       * Performs a simple select query.
 565       *
 566       * @param string The table name to be queried.
 567       * @param string Comma delimetered list of fields to be selected.
 568       * @param string SQL formatted list of conditions to be matched.
 569       * @param array List of options, order by, order direction, limit, limit start
 570       */
 571      
 572  	function simple_select($table, $fields="*", $conditions="", $options=array())
 573      {
 574          $query = "SELECT ".$fields." FROM ".$this->table_prefix.$table;
 575          
 576          if($conditions != "")
 577          {
 578              $query .= " WHERE ".$conditions;
 579          }
 580          
 581          if(isset($options['order_by']))
 582          {
 583              $query .= " ORDER BY ".$options['order_by'];
 584              
 585              if(isset($options['order_dir']))
 586              {
 587                  $query .= " ".strtoupper($options['order_dir']);
 588              }
 589          }
 590          
 591          if(isset($options['limit_start']) && isset($options['limit']))
 592          {
 593              $query .= " LIMIT ".$options['limit_start'].", ".$options['limit'];
 594          }
 595          else if(isset($options['limit']))
 596          {
 597              $query .= " LIMIT ".$options['limit'];
 598          }
 599          
 600          return $this->query($query);
 601      }
 602  
 603  
 604      /**
 605       * Build an insert query from an array.
 606       *
 607       * @param string The table name to perform the query on.
 608       * @param array An array of fields and their values.
 609       * @return int The insert ID if available
 610       */
 611  	function insert_query($table, $array)
 612      {
 613          if(!is_array($array))
 614          {
 615              return false;
 616          }
 617          $fields = implode(",", array_keys($array));
 618          $values = implode("','", $array);
 619          $this->write_query("
 620              INSERT 
 621              INTO {$this->table_prefix}{$table} (".$fields.") 
 622              VALUES ('".$values."')
 623          ");
 624          return $this->insert_id();
 625      }
 626      
 627      /**
 628       * Build one query for multiple inserts from a multidimensional array.
 629       *
 630       * @param string The table name to perform the query on.
 631       * @param array An array of inserts.
 632       * @return int The insert ID if available
 633       */
 634  	function insert_query_multiple($table, $array)
 635      {
 636          if(!is_array($array))
 637          {
 638              return false;
 639          }
 640          // Field names
 641          $fields = array_keys($array[0]);
 642          $fields = implode(",", $fields);
 643  
 644          $insert_rows = array();
 645          foreach($array as $values)
 646          {
 647              $insert_rows[] = "('".implode("','", $values)."')";
 648          }
 649          $insert_rows = implode(", ", $insert_rows);
 650  
 651          $this->write_query("
 652              INSERT 
 653              INTO {$this->table_prefix}{$table} ({$fields}) 
 654              VALUES {$insert_rows}
 655          ");
 656      }
 657  
 658      /**
 659       * Build an update query from an array.
 660       *
 661       * @param string The table name to perform the query on.
 662       * @param array An array of fields and their values.
 663       * @param string An optional where clause for the query.
 664       * @param string An optional limit clause for the query.
 665       * @param boolean An option to quote incoming values of the array.
 666       * @return resource The query data.
 667       */
 668  	function update_query($table, $array, $where="", $limit="", $no_quote=false)
 669      {
 670          if(!is_array($array))
 671          {
 672              return false;
 673          }
 674          
 675          $comma = "";
 676          $query = "";
 677          $quote = "'";
 678          
 679          if($no_quote == true)
 680          {
 681              $quote = "";
 682          }
 683          
 684          foreach($array as $field => $value)
 685          {
 686              $query .= $comma.$field."={$quote}".$value."{$quote}";
 687              $comma = ', ';
 688          }
 689          
 690          if(!empty($where))
 691          {
 692              $query .= " WHERE $where";
 693          }
 694          
 695          return $this->query("UPDATE {$this->table_prefix}$table SET $query");
 696      }
 697  
 698      /**
 699       * Build a delete query.
 700       *
 701       * @param string The table name to perform the query on.
 702       * @param string An optional where clause for the query.
 703       * @param string An optional limit clause for the query.
 704       * @return resource The query data.
 705       */
 706  	function delete_query($table, $where="", $limit="")
 707      {
 708          $query = "";
 709          if(!empty($where))
 710          {
 711              $query .= " WHERE $where";
 712          }
 713          
 714          return $this->query("DELETE FROM {$this->table_prefix}$table $query");
 715      }
 716  
 717      /**
 718       * Escape a string
 719       *
 720       * @param string The string to be escaped.
 721       * @return string The escaped string.
 722       */
 723  	function escape_string($string)
 724      {
 725          $string = $this->db->escape_string($string);
 726          return $string;
 727      }
 728      
 729      /**
 730       * Serves no purposes except compatibility
 731       *
 732       */
 733  	function free_result($query)
 734      {
 735          return;
 736      }
 737      
 738      /**
 739       * Escape a string used within a like command.
 740       *
 741       * @param string The string to be escaped.
 742       * @return string The escaped string.
 743       */
 744  	function escape_string_like($string)
 745      {
 746          return $this->escape_string(str_replace(array('%', '_') , array('\\%' , '\\_') , $string));
 747      }
 748  
 749      /**
 750       * Gets the current version of SQLLite.
 751       *
 752       * @return string Version of MySQL.
 753       */
 754  	function get_version()
 755      {
 756          if($this->version)
 757          {
 758              return $this->version;
 759          }
 760          $this->version = $this->db->get_attribute("ATTR_SERVER_VERSION");
 761          
 762          return $this->version;
 763      }
 764  
 765      /**
 766       * Optimizes a specific table.
 767       *
 768       * @param string The name of the table to be optimized.
 769       */
 770  	function optimize_table($table)
 771      {
 772          $this->query("VACUUM ".$this->table_prefix.$table."");
 773      }
 774      
 775      /**
 776       * Analyzes a specific table.
 777       *
 778       * @param string The name of the table to be analyzed.
 779       */
 780  	function analyze_table($table)
 781      {
 782          $this->query("ANALYZE ".$this->table_prefix.$table."");
 783      }
 784  
 785      /**
 786       * Show the "create table" command for a specific table.
 787       *
 788       * @param string The name of the table.
 789       * @return string The MySQL command to create the specified table.
 790       */
 791  	function show_create_table($table)
 792      {
 793          $old_tbl_prefix = $this->table_prefix;
 794          $this->set_table_prefix("");
 795          $query = $this->simple_select("sqlite_master", "sql", "type = 'table' AND name = '{$this->table_prefix}{$table}' ORDER BY type DESC, name");
 796          $this->set_table_prefix($old_tbl_prefix);
 797          
 798          return $this->fetch_field($query, 'sql');
 799      }
 800  
 801      /**
 802       * Show the "show fields from" command for a specific table.
 803       *
 804       * @param string The name of the table.
 805       * @return string Field info for that table
 806       */
 807  	function show_fields_from($table)
 808      {
 809          $old_tbl_prefix = $this->table_prefix;
 810          $this->set_table_prefix("");
 811          $query = $this->simple_select("sqlite_master", "sql", "type = 'table' AND name = '{$old_tbl_prefix}{$table}'");
 812          $this->set_table_prefix($old_tbl_prefix);
 813          $table = trim(preg_replace('#CREATE\s+TABLE\s+"?'.$this->table_prefix.$table.'"?#i', '', $this->fetch_field($query, "sql")));
 814  
 815          preg_match('#\((.*)\)#s', $table, $matches);
 816  
 817          $field_info = array();
 818          $table_cols = explode(',', trim($matches[1]));
 819          foreach($table_cols as $declaration)
 820          {
 821              $entities = preg_split('#\s+#', trim($declaration));
 822              $column_name = preg_replace('/"?([^"]+)"?/', '\1', $entities[0]);
 823  
 824              $field_info[] = array('Extra' => $entities[1], 'Field' => $column_name);
 825          }
 826          
 827          return $field_info;
 828      }
 829  
 830      /**
 831       * Returns whether or not the table contains a fulltext index.
 832       *
 833       * @param string The name of the table.
 834       * @param string Optionally specify the name of the index.
 835       * @return boolean True or false if the table has a fulltext index or not.
 836       */
 837  	function is_fulltext($table, $index="")
 838      {
 839          return false;
 840      }
 841  
 842      /**
 843       * Returns whether or not this database engine supports fulltext indexing.
 844       *
 845       * @param string The table to be checked.
 846       * @return boolean True or false if supported or not.
 847       */
 848  
 849  	function supports_fulltext($table)
 850      {
 851          return false;
 852      }
 853  
 854      /**
 855       * Returns whether or not this database engine supports boolean fulltext matching.
 856       *
 857       * @param string The table to be checked.
 858       * @return boolean True or false if supported or not.
 859       */
 860  	function supports_fulltext_boolean($table)
 861      {
 862          return false;
 863      }
 864  
 865      /**
 866       * Creates a fulltext index on the specified column in the specified table with optional index name.
 867       *
 868       * @param string The name of the table.
 869       * @param string Name of the column to be indexed.
 870       * @param string The index name, optional.
 871       */
 872  	function create_fulltext_index($table, $column, $name="")
 873      {
 874          return false;
 875      }
 876  
 877      /**
 878       * Drop an index with the specified name from the specified table
 879       *
 880       * @param string The name of the table.
 881       * @param string The name of the index.
 882       */
 883  	function drop_index($table, $name)
 884      {
 885          $this->query("ALTER TABLE {$this->table_prefix}$table DROP INDEX $name");
 886      }
 887      
 888      /**
 889       * Checks to see if an index exists on a specified table
 890       *
 891       * @param string The name of the table.
 892       * @param string The name of the index.
 893       */
 894  	function index_exists($table, $index)
 895      {
 896          return false;
 897      }
 898      
 899      /**
 900       * Drop an table with the specified table
 901       *
 902       * @param string The name of the table.
 903       * @param boolean hard drop - no checking
 904       * @param boolean use table prefix
 905       */
 906  	function drop_table($table, $hard=false, $table_prefix=true)
 907      {
 908          if($table_prefix == false)
 909          {
 910              $table_prefix = "";
 911          }
 912          else
 913          {
 914              $table_prefix = $this->table_prefix;
 915          }
 916          
 917          if($hard == false)
 918          {
 919              if($this->table_exists($table))
 920              {
 921                  $this->query('DROP TABLE '.$table_prefix.$table);
 922              }
 923          }
 924          else
 925          {
 926              $this->query('DROP TABLE '.$table_prefix.$table);
 927          }
 928      }
 929      
 930      /**
 931       * Replace contents of table with values
 932       *
 933       * @param string The table
 934       * @param array The replacements
 935       * @param mixed The default field(s)
 936       * @param boolean Whether or not to return an insert id. True by default
 937       */
 938  	function replace_query($table, $replacements=array(), $default_field="", $insert_id=true)
 939      {
 940          $columns = '';
 941          $values = '';
 942          $comma = '';
 943          foreach($replacements as $column => $value)
 944          {
 945              $columns .= $comma.$column;
 946              $values .= $comma."'".$value."'";
 947              
 948              $comma = ',';
 949          }
 950  
 951          if(empty($columns) || empty($values))
 952          {
 953               return false;
 954          }
 955          
 956          if($default_field == "")
 957          {
 958              return $this->query("REPLACE INTO {$this->table_prefix}{$table} ({$columns}) VALUES({$values})");
 959          }
 960          else
 961          {
 962              $update = false;
 963              if(is_array($default_field) && !empty($default_field))
 964              {
 965                  $search_bit = array();
 966                  foreach($default_field as $field)
 967                  {
 968                      $search_bit[] = "{$field} = '".$replacements[$field]."'";
 969                  }
 970  
 971                  $search_bit = implode(" AND ", $search_bit);
 972                  $query = $this->write_query("SELECT COUNT(".$default_field[0].") as count FROM {$this->table_prefix}{$table} WHERE {$search_bit} LIMIT 1");
 973                  if($this->fetch_field($query, "count") == 1)
 974                  {
 975                      $update = true;
 976                  }
 977              }
 978              else
 979              {
 980                  $query = $this->write_query("SELECT {$default_field} FROM {$this->table_prefix}{$table}");
 981  
 982                  while($column = $this->fetch_array($query))
 983                  {
 984                      if($column[$default_field] == $replacements[$default_field])
 985                      {                
 986                          $update = true;
 987                          break;
 988                      }
 989                  }
 990              }
 991  
 992              if($update === true)
 993              {
 994                  return $this->update_query($table, $replacements, $search_bit);
 995              }
 996              else
 997              {
 998                  return $this->insert_query($table, $replacements, $insert_id);
 999              }
1000          }
1001      }
1002      
1003      /**
1004       * Sets the table prefix used by the simple select, insert, update and delete functions
1005       *
1006       * @param string The new table prefix
1007       */
1008  	function set_table_prefix($prefix)
1009      {
1010          $this->table_prefix = $prefix;
1011      }
1012      
1013      /**
1014       * Fetched the total size of all mysql tables or a specific table
1015       *
1016       * @param string The table (optional) (ignored)
1017       * @return integer the total size of all mysql tables or a specific table
1018       */
1019  	function fetch_size($table='')
1020      {
1021          global $config, $lang;
1022          
1023          $total = @filesize($config['database']['database']);
1024          if(!$total || $table != '')
1025          {
1026              $total = $lang->na;
1027          }
1028          return $total;
1029      }
1030      
1031      /**
1032       * Perform an "Alter Table" query in SQLite < 3.2.0 - Code taken from http://code.jenseng.com/db/
1033       *
1034       * @param string The table (optional)
1035       * @return integer the total size of all mysql tables or a specific table
1036       */
1037  	function alter_table_parse($table, $alterdefs, $fullquery="")
1038      {
1039          if(!$fullquery)
1040          {
1041              $fullquery = " ... {$alterdefs}";
1042          }
1043          
1044          if(!defined("TIME_NOW"))
1045          {
1046              define("TIME_NOW", time());
1047          }
1048          
1049          if($alterdefs != '')
1050          {
1051              $result = $this->query("SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '{$table}' ORDER BY type DESC");
1052              if($this->num_rows($result) > 0)
1053              {
1054                  $row = $this->fetch_array($result); // Table sql
1055                  $tmpname = 't'.TIME_NOW;
1056                  $origsql = trim(preg_replace("/[\s]+/", " ", str_replace(",", ", ", preg_replace("/[\(]/","( ", $row['sql'], 1))));
1057                  $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'", $tmpname, $origsql, 1)), 6);
1058                  $createindexsql = array();
1059                  $i = 0;
1060                  $defs = preg_split("/[,]+/", $alterdefs, -1, PREG_SPLIT_NO_EMPTY);
1061                  $prevword = $table;
1062                  $oldcols = preg_split("/[,]+/", substr(trim($createtemptableSQL), strpos(trim($createtemptableSQL), '(')+1), -1, PREG_SPLIT_NO_EMPTY);
1063                  $newcols = array();
1064                  
1065                  for($i = 0; $i < sizeof($oldcols); $i++)
1066                  {
1067                      $colparts = preg_split("/[\s]+/", $oldcols[$i], -1, PREG_SPLIT_NO_EMPTY);
1068                      $oldcols[$i] = $colparts[0];
1069                      $newcols[$colparts[0]] = $colparts[0];
1070                  }
1071                  
1072                  $newcolumns = '';
1073                  $oldcolumns = '';
1074                  reset($newcols);
1075                  
1076                  foreach($newcols as $key => $val)
1077                  {
1078                      $newcolumns .= ($newcolumns ? ', ' : '').$val;
1079                      $oldcolumns .= ($oldcolumns ? ', ' : '').$key;
1080                  }
1081                  
1082                  $copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table;
1083                  $dropoldsql = 'DROP TABLE '.$table;
1084                  $createtesttableSQL = $createtemptableSQL;
1085                  
1086                  foreach($defs as $def)
1087                  {
1088                      $defparts = preg_split("/[\s]+/", $def, -1, PREG_SPLIT_NO_EMPTY);
1089                      $action = strtolower($defparts[0]);
1090                      
1091                      switch($action)
1092                      {
1093                          case 'change':
1094                              if(sizeof($defparts) <= 3)
1095                              {
1096                                  $this->error($alterdefs, 'near "'.$defparts[0].($defparts[1] ? ' '.$defparts[1] : '').($defparts[2] ? ' '.$defparts[2] : '').'": syntax error', E_USER_WARNING);
1097                                  return false;
1098                              }
1099                              
1100                              if($severpos = strpos($createtesttableSQL, ' '.$defparts[1].' '))
1101                              {
1102                                  if($newcols[$defparts[1]] != $defparts[1])
1103                                  {
1104                                      $this->error($alterdefs, 'unknown column "'.$defparts[1].'" in "'.$table.'"');
1105                                      return false;
1106                                  }
1107                                  
1108                                  $newcols[$defparts[1]] = $defparts[2];
1109                                  $nextcommapos = strpos($createtesttableSQL, ',', $severpos);
1110                                  $insertval = '';
1111                                  
1112                                  for($i = 2; $i < sizeof($defparts); $i++)
1113                                  {
1114                                      $insertval .= ' '.$defparts[$i];
1115                                  }
1116                                  
1117                                  if($nextcommapos)
1118                                  {
1119                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos).$insertval.substr($createtesttableSQL, $nextcommapos);
1120                                  }
1121                                  else
1122                                  {
1123                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos-(strpos($createtesttableSQL, ',') ? 0 : 1)).$insertval.')';
1124                                  }
1125                              }
1126                              else
1127                              {
1128                                  $this->error($fullquery, 'unknown column "'.$defparts[1].'" in "'.$table.'"', E_USER_WARNING);
1129                                  return false;
1130                              }
1131                              break;
1132                          case 'drop':
1133                              if(sizeof($defparts) < 2)
1134                              {
1135                                  $this->error($fullquery, 'near "'.$defparts[0].($defparts[1] ? ' '.$defparts[1] : '').'": syntax error');
1136                                  return false;
1137                              }
1138                              
1139                              if($severpos = strpos($createtesttableSQL, ' '.$defparts[1].' '))
1140                              {
1141                                  $nextcommapos = strpos($createtesttableSQL, ',', $severpos);
1142                                  
1143                                  if($nextcommapos)
1144                                  {
1145                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos).substr($createtesttableSQL, $nextcommapos + 1);
1146                                  }
1147                                  else
1148                                  {
1149                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos-(strpos($createtesttableSQL, ',') ? 0 : 1) - 1).')';
1150                                  }
1151                                  
1152                                  unset($newcols[$defparts[1]]);
1153                              }
1154                              else
1155                              {
1156                                  $this->error($fullquery, 'unknown column "'.$defparts[1].'" in "'.$table.'"');
1157                                  return false;
1158                              }
1159                              break;
1160                          default:
1161                              $this->error($fullquery, 'near "'.$prevword.'": syntax error');
1162                              return false;
1163                      }
1164                      
1165                      $prevword = $defparts[sizeof($defparts)-1];
1166                  }
1167              
1168              
1169                  // This block of code generates a test table simply to verify that the columns specifed are valid in an sql statement
1170                  // This ensures that no reserved words are used as columns, for example
1171                  $this->query($createtesttableSQL);
1172                  
1173                  $droptempsql = 'DROP TABLE '.$tmpname;
1174                  if($this->query($droptempsql, 0) === false)
1175                  {
1176                      return false;
1177                  }
1178                  // End block
1179                  
1180                  
1181                  $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'{$tmpname}'", $table, $createtesttableSQL, 1)), 17);
1182                  $newcolumns = '';
1183                  $oldcolumns = '';
1184                  reset($newcols);
1185                  
1186                  foreach($newcols as $key => $val)
1187                  {
1188                      $newcolumns .= ($newcolumns ? ', ' : '').$val;
1189                      $oldcolumns .= ($oldcolumns ? ', ' : '').$key;
1190                  }
1191                  
1192                  $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname;
1193                  
1194                  
1195                  $this->query($createtemptableSQL); // Create temp table
1196                  $this->query($copytotempsql); // Copy to table
1197                  $this->query($dropoldsql); // Drop old table
1198                  
1199                  $this->query($createnewtableSQL); // Recreate original table
1200                  $this->query($copytonewsql); // Copy back to original table
1201                  $this->query($droptempsql); // Drop temp table
1202              }
1203              else
1204              {
1205                  $this->error($fullquery, 'no such table: '.$table);
1206                  return false;
1207              }
1208              return true;
1209          }
1210      }
1211      
1212      /**
1213       * Drops a column
1214       *
1215       * @param string The table
1216       * @param string The column name
1217       */
1218  	function drop_column($table, $column)
1219      {
1220          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} DROP {$column}");
1221      }
1222      
1223      /**
1224       * Adds a column
1225       *
1226       * @param string The table
1227       * @param string The column name
1228       * @param string the new column definition
1229       */
1230  	function add_column($table, $column, $definition)
1231      {
1232          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ADD {$column} {$definition}");
1233      }
1234      
1235      /**
1236       * Modifies a column
1237       *
1238       * @param string The table
1239       * @param string The column name
1240       * @param string the new column definition
1241       */
1242  	function modify_column($table, $column, $new_definition)
1243      {
1244          // Yes, $column is repeated twice for a reason. It simulates a rename sql query, which SQLite supports.
1245          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} CHANGE {$column} {$column} {$new_definition}");
1246      }
1247      
1248      /**
1249       * Renames a column
1250       *
1251       * @param string The table
1252       * @param string The old column name
1253       * @param string the new column name
1254       * @param string the new column definition
1255       */
1256  	function rename_column($table, $old_column, $new_column, $new_definition)
1257      {
1258          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} CHANGE {$old_column} {$new_column} {$new_definition}");
1259      }
1260  
1261      /**
1262       * Fetch a list of database character sets this DBMS supports
1263       *
1264       * @return array Array of supported character sets with array key being the name, array value being display name. False if unsupported
1265       */
1266  	function fetch_db_charsets()
1267      {
1268          return false;
1269      }
1270  
1271      /**
1272       * Fetch a database collation for a particular database character set
1273       *
1274       * @param string The database character set
1275       * @return string The matching database collation, false if unsupported
1276       */
1277  	function fetch_charset_collation($charset)
1278      {
1279          return false;
1280      }
1281  
1282      /**
1283       * Fetch a character set/collation string for use with CREATE TABLE statements. Uses current DB encoding
1284       *
1285       * @return string The built string, empty if unsupported
1286       */
1287  	function build_create_table_collation()
1288      {
1289          return '';
1290      }
1291  
1292      /**
1293       * Time how long it takes for a particular piece of code to run. Place calls above & below the block of code.
1294       *
1295       * @return float The time taken
1296       */
1297  	function get_execution_time()
1298      {
1299          static $time_start;
1300  
1301          $time = microtime(true);
1302  
1303  
1304          // Just starting timer, init and return
1305          if(!$time_start)
1306          {
1307              $time_start = $time;
1308              return;
1309          }
1310          // Timer has run, return execution time
1311          else
1312          {
1313              $total = $time-$time_start;
1314              if($total < 0) $total = 0;
1315              $time_start = 0;
1316              return $total;
1317          }
1318      }
1319  }
1320  
1321  ?>


Generated: Tue Oct 8 19:19:50 2013 Cross-referenced by PHPXref 0.7.1