lib/dml/sqlite3_pdo_moodle_database.php

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