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