lib/dml/sqlite3_pdo_moodle_database.php

   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 }