[Summary 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.
 197       *
 198       * @param string $table name
 199       * @return array array of database_column_info objects indexed with column names
 200       */
 201      protected function fetch_columns(string $table): array {
 202          $structure = array();
 203  
 204          // get table's CREATE TABLE command (we'll need it for autoincrement fields)
 205          $sql = 'SELECT sql FROM sqlite_master WHERE type="table" AND tbl_name="'.$this->prefix.$table.'"';
 206          if ($this->debug) {
 207              $this->debug_query($sql);
 208          }
 209          $createsql = $this->pdb->query($sql)->fetch();
 210          if (!$createsql) {
 211              return false;
 212          }
 213          $createsql = $createsql['sql'];
 214  
 215          $sql = 'PRAGMA table_info("'. $this->prefix.$table.'")';
 216          if ($this->debug) {
 217              $this->debug_query($sql);
 218          }
 219          $rscolumns = $this->pdb->query($sql);
 220          foreach ($rscolumns as $row) {
 221              $columninfo = array(
 222                  'name' => strtolower($row['name']), // colum names must be lowercase
 223                  'not_null' =>(boolean)$row['notnull'],
 224                  'primary_key' => (boolean)$row['pk'],
 225                  'has_default' => !is_null($row['dflt_value']),
 226                  'default_value' => $row['dflt_value'],
 227                  'auto_increment' => false,
 228                  'binary' => false,
 229                  //'unsigned' => false,
 230              );
 231              $type = explode('(', $row['type']);
 232              $columninfo['type'] = strtolower($type[0]);
 233              if (count($type) > 1) {
 234                  $size = explode(',', trim($type[1], ')'));
 235                  $columninfo['max_length'] = $size[0];
 236                  if (count($size) > 1) {
 237                      $columninfo['scale'] = $size[1];
 238                  }
 239              }
 240              // SQLite does not have a fixed set of datatypes (ie. it accepts any string as
 241              // datatype in the CREATE TABLE command. We try to guess which type is used here
 242              switch(substr($columninfo['type'], 0, 3)) {
 243                  case 'int': // int integer
 244                      if ($columninfo['primary_key'] && preg_match('/'.$columninfo['name'].'\W+integer\W+primary\W+key\W+autoincrement/im', $createsql)) {
 245                          $columninfo['meta_type'] = 'R';
 246                          $columninfo['auto_increment'] = true;
 247                      } else {
 248                          $columninfo['meta_type'] = 'I';
 249                      }
 250                      break;
 251                  case 'num': // number numeric
 252                  case 'rea': // real
 253                  case 'dou': // double
 254                  case 'flo': // float
 255                      $columninfo['meta_type'] = 'N';
 256                      break;
 257                  case 'var': // varchar
 258                  case 'cha': // char
 259                      $columninfo['meta_type'] = 'C';
 260                      break;
 261                  case 'enu': // enums
 262                      $columninfo['meta_type'] = 'C';
 263                      break;
 264                  case 'tex': // text
 265                  case 'clo': // clob
 266                      $columninfo['meta_type'] = 'X';
 267                      break;
 268                  case 'blo': // blob
 269                  case 'non': // none
 270                      $columninfo['meta_type'] = 'B';
 271                      $columninfo['binary'] = true;
 272                      break;
 273                  case 'boo': // boolean
 274                  case 'bit': // bit
 275                  case 'log': // logical
 276                      $columninfo['meta_type'] = 'L';
 277                      $columninfo['max_length'] = 1;
 278                      break;
 279                  case 'tim': // timestamp
 280                      $columninfo['meta_type'] = 'T';
 281                      break;
 282                  case 'dat': // date datetime
 283                      $columninfo['meta_type'] = 'D';
 284                      break;
 285              }
 286              if ($columninfo['has_default'] && ($columninfo['meta_type'] == 'X' || $columninfo['meta_type']== 'C')) {
 287                  // trim extra quotes from text default values
 288                  $columninfo['default_value'] = substr($columninfo['default_value'], 1, -1);
 289              }
 290              $structure[$columninfo['name']] = new database_column_info($columninfo);
 291          }
 292  
 293          return $structure;
 294      }
 295  
 296      /**
 297       * Normalise values based in RDBMS dependencies (booleans, LOBs...)
 298       *
 299       * @param database_column_info $column column metadata corresponding with the value we are going to normalise
 300       * @param mixed $value value we are going to normalise
 301       * @return mixed the normalised value
 302       */
 303      protected function normalise_value($column, $value) {
 304          return $value;
 305      }
 306  
 307      /**
 308       * Returns the sql statement with clauses to append used to limit a recordset range.
 309       * @param string $sql the SQL statement to limit.
 310       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 311       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 312       * @return string the SQL statement with limiting clauses
 313       */
 314      protected function get_limit_clauses($sql, $limitfrom=0, $limitnum=0) {
 315          if ($limitnum) {
 316              $sql .= ' LIMIT '.$limitnum;
 317              if ($limitfrom) {
 318                  $sql .= ' OFFSET '.$limitfrom;
 319              }
 320          }
 321          return $sql;
 322      }
 323  
 324      /**
 325       * Delete the records from a table where all the given conditions met.
 326       * If conditions not specified, table is truncated.
 327       *
 328       * @param string $table the table to delete from.
 329       * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
 330       * @return returns success.
 331       */
 332      public function delete_records($table, array $conditions=null) {
 333          if (is_null($conditions)) {
 334              return $this->execute("DELETE FROM {{$table}}");
 335          }
 336          list($select, $params) = $this->where_clause($table, $conditions);
 337          return $this->delete_records_select($table, $select, $params);
 338      }
 339  
 340      /**
 341       * Returns the proper SQL to do CONCAT between the elements passed
 342       * Can take many parameters
 343       *
 344       * @param string $element
 345       * @return string
 346       */
 347      public function sql_concat() {
 348          $elements = func_get_args();
 349          return implode('||', $elements);
 350      }
 351  
 352      /**
 353       * Returns the proper SQL to do CONCAT between the elements passed
 354       * with a given separator
 355       *
 356       * @param string $separator
 357       * @param array  $elements
 358       * @return string
 359       */
 360      public function sql_concat_join($separator="' '", $elements=array()) {
 361          // Intersperse $elements in the array.
 362          // Add items to the array on the fly, walking it
 363          // _backwards_ splicing the elements in. The loop definition
 364          // should skip first and last positions.
 365          for ($n=count($elements)-1; $n > 0; $n--) {
 366              array_splice($elements, $n, 0, $separator);
 367          }
 368          return implode('||', $elements);
 369      }
 370  
 371      /**
 372       * Returns the SQL text to be used in order to perform one bitwise XOR operation
 373       * between 2 integers.
 374       *
 375       * @param integer int1 first integer in the operation
 376       * @param integer int2 second integer in the operation
 377       * @return string the piece of SQL code to be used in your statement.
 378       */
 379      public function sql_bitxor($int1, $int2) {
 380          return '( ~' . $this->sql_bitand($int1, $int2) . ' & ' . $this->sql_bitor($int1, $int2) . ')';
 381      }
 382  }