[Summary view] [Print] [Text view]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Experimental pdo database class.
  19   *
  20   * @package    core_dml
  21   * @copyright  2008 Andrei Bautu
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  
  25  defined('MOODLE_INTERNAL') || die();
  26  
  27  require_once (__DIR__.'/pdo_moodle_database.php');
  28  
  29  /**
  30   * Experimental pdo database class
  31   *
  32   * @package    core_dml
  33   * @copyright  2008 Andrei Bautu
  34   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  35   */
  36  class sqlite3_pdo_moodle_database extends pdo_moodle_database {
  37      protected $database_file_extension = '.sq3.php';
  38      /**
  39       * Detects if all needed PHP stuff installed.
  40       * Note: can be used before connect()
  41       * @return mixed true if ok, string if something
  42       */
  43      public function driver_installed() {
  44          if (!extension_loaded('pdo_sqlite') || !extension_loaded('pdo')){
  45              return get_string('sqliteextensionisnotpresentinphp', 'install');
  46          }
  47          return true;
  48      }
  49  
  50      /**
  51       * Returns database family type - describes SQL dialect
  52       * Note: can be used before connect()
  53       * @return string db family name (mysql, postgres, mssql, oracle, etc.)
  54       */
  55      public function get_dbfamily() {
  56          return 'sqlite';
  57      }
  58  
  59      /**
  60       * Returns more specific database driver type
  61       * Note: can be used before connect()
  62       * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
  63       */
  64      protected function get_dbtype() {
  65          return 'sqlite3';
  66      }
  67  
  68      protected function configure_dbconnection() {
  69          // try to protect database file against web access;
  70          // this is required in case that the moodledata folder is web accessible and
  71          // .htaccess is not in place; requires that the database file extension is php
  72          $this->pdb->exec('CREATE TABLE IF NOT EXISTS "<?php die?>" (id int)');
  73          $this->pdb->exec('PRAGMA synchronous=OFF');
  74          $this->pdb->exec('PRAGMA short_column_names=1');
  75          $this->pdb->exec('PRAGMA encoding="UTF-8"');
  76          $this->pdb->exec('PRAGMA case_sensitive_like=0');
  77          $this->pdb->exec('PRAGMA locking_mode=NORMAL');
  78      }
  79  
  80      /**
  81       * Attempt to create the database
  82       * @param string $dbhost
  83       * @param string $dbuser
  84       * @param string $dbpass
  85       * @param string $dbname
  86       *
  87       * @return bool success
  88       */
  89      public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
  90          global $CFG;
  91  
  92          $this->dbhost = $dbhost;
  93          $this->dbuser = $dbuser;
  94          $this->dbpass = $dbpass;
  95          $this->dbname = $dbname;
  96          $filepath = $this->get_dbfilepath();
  97          $dirpath = dirname($filepath);
  98          @mkdir($dirpath, $CFG->directorypermissions, true);
  99          return touch($filepath);
 100      }
 101  
 102      /**
 103       * Returns the driver-dependent DSN for PDO based on members stored by connect.
 104       * Must be called after connect (or after $dbname, $dbhost, etc. members have been set).
 105       * @return string driver-dependent DSN
 106       */
 107      protected function get_dsn() {
 108          return 'sqlite:'.$this->get_dbfilepath();
 109      }
 110  
 111      /**
 112       * Returns the file path for the database file, computed from dbname and/or dboptions.
 113       * If dboptions['file'] is set, then it is used (use :memory: for in memory database);
 114       * else if dboptions['path'] is set, then the file will be <dboptions path>/<dbname>.sq3.php;
 115       * else if dbhost is set and not localhost, then the file will be <dbhost>/<dbname>.sq3.php;
 116       * else the file will be <moodle data path>/<dbname>.sq3.php
 117       * @return string file path to the SQLite database;
 118       */
 119      public function get_dbfilepath() {
 120          global $CFG;
 121          if (!empty($this->dboptions['file'])) {
 122              return $this->dboptions['file'];
 123          }
 124          if ($this->dbhost && $this->dbhost != 'localhost') {
 125              $path = $this->dbhost;
 126          } else {
 127              $path = $CFG->dataroot;
 128          }
 129          $path = rtrim($path, '\\/').'/';
 130          if (!empty($this->dbuser)) {
 131              $path .= $this->dbuser.'_';
 132          }
 133          $path .= $this->dbname.'_'.md5($this->dbpass).$this->database_file_extension;
 134          return $path;
 135      }
 136  
 137      /**
 138       * Return tables in database WITHOUT current prefix.
 139       * @param bool $usecache if true, returns list of cached tables.
 140       * @return array of table names in lowercase and without prefix
 141       */
 142      public function get_tables($usecache=true) {
 143          $tables = array();
 144  
 145          $sql = 'SELECT name FROM sqlite_master WHERE type="table" UNION ALL SELECT name FROM sqlite_temp_master WHERE type="table" ORDER BY name';
 146          if ($this->debug) {
 147              $this->debug_query($sql);
 148          }
 149          $rstables = $this->pdb->query($sql);
 150          foreach ($rstables as $table) {
 151              $table = $table['name'];
 152              $table = strtolower($table);
 153              if ($this->prefix !== false && $this->prefix !== '') {
 154                  if (strpos($table, $this->prefix) !== 0) {
 155                      continue;
 156                  }
 157                  $table = substr($table, strlen($this->prefix));
 158              }
 159              $tables[$table] = $table;
 160          }
 161          return $tables;
 162      }
 163  
 164      /**
 165       * Return table indexes - everything lowercased
 166       * @param string $table The table we want to get indexes from.
 167       * @return array of arrays
 168       */
 169      public function get_indexes($table) {
 170          $indexes = array();
 171          $sql = 'PRAGMA index_list('.$this->prefix.$table.')';
 172          if ($this->debug) {
 173              $this->debug_query($sql);
 174          }
 175          $rsindexes = $this->pdb->query($sql);
 176          foreach($rsindexes as $index) {
 177              $unique = (boolean)$index['unique'];
 178              $index = $index['name'];
 179              $sql = 'PRAGMA index_info("'.$index.'")';
 180              if ($this->debug) {
 181                  $this->debug_query($sql);
 182              }
 183              $rscolumns = $this->pdb->query($sql);
 184              $columns = array();
 185              foreach($rscolumns as $row) {
 186                  $columns[] = strtolower($row['name']);
 187              }
 188              $index = strtolower($index);
 189              $indexes[$index]['unique'] = $unique;
 190              $indexes[$index]['columns'] = $columns;
 191          }
 192          return $indexes;
 193      }
 194  
 195      /**
 196       * Returns detailed information about columns in table. This information is cached internally.
 197       * @param string $table name
 198       * @param bool $usecache
 199       * @return array array of database_column_info objects indexed with column names
 200       */
 201      public function get_columns($table, $usecache=true) {
 202  
 203          if ($usecache) {
 204              if ($this->temptables->is_temptable($table)) {
 205                  if ($data = $this->get_temp_tables_cache()->get($table)) {
 206                      return $data;
 207                  }
 208              } else {
 209                  if ($data = $this->get_metacache()->get($table)) {
 210                      return $data;
 211                  }
 212              }
 213          }
 214  
 215          $structure = array();
 216  
 217          // get table's CREATE TABLE command (we'll need it for autoincrement fields)
 218          $sql = 'SELECT sql FROM sqlite_master WHERE type="table" AND tbl_name="'.$this->prefix.$table.'"';
 219          if ($this->debug) {
 220              $this->debug_query($sql);
 221          }
 222          $createsql = $this->pdb->query($sql)->fetch();
 223          if (!$createsql) {
 224              return false;
 225          }
 226          $createsql = $createsql['sql'];
 227  
 228          $sql = 'PRAGMA table_info("'. $this->prefix.$table.'")';
 229          if ($this->debug) {
 230              $this->debug_query($sql);
 231          }
 232          $rscolumns = $this->pdb->query($sql);
 233          foreach ($rscolumns as $row) {
 234              $columninfo = array(
 235                  'name' => strtolower($row['name']), // colum names must be lowercase
 236                  'not_null' =>(boolean)$row['notnull'],
 237                  'primary_key' => (boolean)$row['pk'],
 238                  'has_default' => !is_null($row['dflt_value']),
 239                  'default_value' => $row['dflt_value'],
 240                  'auto_increment' => false,
 241                  'binary' => false,
 242                  //'unsigned' => false,
 243              );
 244              $type = explode('(', $row['type']);
 245              $columninfo['type'] = strtolower($type[0]);
 246              if (count($type) > 1) {
 247                  $size = explode(',', trim($type[1], ')'));
 248                  $columninfo['max_length'] = $size[0];
 249                  if (count($size) > 1) {
 250                      $columninfo['scale'] = $size[1];
 251                  }
 252              }
 253              // SQLite does not have a fixed set of datatypes (ie. it accepts any string as
 254              // datatype in the CREATE TABLE command. We try to guess which type is used here
 255              switch(substr($columninfo['type'], 0, 3)) {
 256                  case 'int': // int integer
 257                      if ($columninfo['primary_key'] && preg_match('/'.$columninfo['name'].'\W+integer\W+primary\W+key\W+autoincrement/im', $createsql)) {
 258                          $columninfo['meta_type'] = 'R';
 259                          $columninfo['auto_increment'] = true;
 260                      } else {
 261                          $columninfo['meta_type'] = 'I';
 262                      }
 263                      break;
 264                  case 'num': // number numeric
 265                  case 'rea': // real
 266                  case 'dou': // double
 267                  case 'flo': // float
 268                      $columninfo['meta_type'] = 'N';
 269                      break;
 270                  case 'var': // varchar
 271                  case 'cha': // char
 272                      $columninfo['meta_type'] = 'C';
 273                      break;
 274                  case 'enu': // enums
 275                      $columninfo['meta_type'] = 'C';
 276                      break;
 277                  case 'tex': // text
 278                  case 'clo': // clob
 279                      $columninfo['meta_type'] = 'X';
 280                      break;
 281                  case 'blo': // blob
 282                  case 'non': // none
 283                      $columninfo['meta_type'] = 'B';
 284                      $columninfo['binary'] = true;
 285                      break;
 286                  case 'boo': // boolean
 287                  case 'bit': // bit
 288                  case 'log': // logical
 289                      $columninfo['meta_type'] = 'L';
 290                      $columninfo['max_length'] = 1;
 291                      break;
 292                  case 'tim': // timestamp
 293                      $columninfo['meta_type'] = 'T';
 294                      break;
 295                  case 'dat': // date datetime
 296                      $columninfo['meta_type'] = 'D';
 297                      break;
 298              }
 299              if ($columninfo['has_default'] && ($columninfo['meta_type'] == 'X' || $columninfo['meta_type']== 'C')) {
 300                  // trim extra quotes from text default values
 301                  $columninfo['default_value'] = substr($columninfo['default_value'], 1, -1);
 302              }
 303              $structure[$columninfo['name']] = new database_column_info($columninfo);
 304          }
 305  
 306          if ($usecache) {
 307              if ($this->temptables->is_temptable($table)) {
 308                  $this->get_temp_tables_cache()->set($table, $structure);
 309              } else {
 310                  $this->get_metacache()->set($table, $structure);
 311              }
 312          }
 313  
 314          return $structure;
 315      }
 316  
 317      /**
 318       * Normalise values based in RDBMS dependencies (booleans, LOBs...)
 319       *
 320       * @param database_column_info $column column metadata corresponding with the value we are going to normalise
 321       * @param mixed $value value we are going to normalise
 322       * @return mixed the normalised value
 323       */
 324      protected function normalise_value($column, $value) {
 325          return $value;
 326      }
 327  
 328      /**
 329       * Returns the sql statement with clauses to append used to limit a recordset range.
 330       * @param string $sql the SQL statement to limit.
 331       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 332       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 333       * @return string the SQL statement with limiting clauses
 334       */
 335      protected function get_limit_clauses($sql, $limitfrom=0, $limitnum=0) {
 336          if ($limitnum) {
 337              $sql .= ' LIMIT '.$limitnum;
 338              if ($limitfrom) {
 339                  $sql .= ' OFFSET '.$limitfrom;
 340              }
 341          }
 342          return $sql;
 343      }
 344  
 345      /**
 346       * Delete the records from a table where all the given conditions met.
 347       * If conditions not specified, table is truncated.
 348       *
 349       * @param string $table the table to delete from.
 350       * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
 351       * @return returns success.
 352       */
 353      public function delete_records($table, array $conditions=null) {
 354          if (is_null($conditions)) {
 355              return $this->execute("DELETE FROM {{$table}}");
 356          }
 357          list($select, $params) = $this->where_clause($table, $conditions);
 358          return $this->delete_records_select($table, $select, $params);
 359      }
 360  
 361      /**
 362       * Returns the proper SQL to do CONCAT between the elements passed
 363       * Can take many parameters
 364       *
 365       * @param string $element
 366       * @return string
 367       */
 368      public function sql_concat() {
 369          $elements = func_get_args();
 370          return implode('||', $elements);
 371      }
 372  
 373      /**
 374       * Returns the proper SQL to do CONCAT between the elements passed
 375       * with a given separator
 376       *
 377       * @param string $separator
 378       * @param array  $elements
 379       * @return string
 380       */
 381      public function sql_concat_join($separator="' '", $elements=array()) {
 382          // Intersperse $elements in the array.
 383          // Add items to the array on the fly, walking it
 384          // _backwards_ splicing the elements in. The loop definition
 385          // should skip first and last positions.
 386          for ($n=count($elements)-1; $n > 0; $n--) {
 387              array_splice($elements, $n, 0, $separator);
 388          }
 389          return implode('||', $elements);
 390      }
 391  
 392      /**
 393       * Returns the SQL text to be used in order to perform one bitwise XOR operation
 394       * between 2 integers.
 395       *
 396       * @param integer int1 first integer in the operation
 397       * @param integer int2 second integer in the operation
 398       * @return string the piece of SQL code to be used in your statement.
 399       */
 400      public function sql_bitxor($int1, $int2) {
 401          return '( ~' . $this->sql_bitand($int1, $int2) . ' & ' . $this->sql_bitor($int1, $int2) . ')';
 402      }
 403  }