MDL-20734 normalise_value() - moving from private to protected everywhere and abstracting
[moodle.git] / lib / dml / sqlite3_pdo_moodle_database.php
CommitLineData
49926145 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 moodlecore
23 * @subpackage DML
24 * @copyright 2008 Andrei Bautu
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 */
55946ffd 27
28require_once($CFG->libdir.'/dml/pdo_moodle_database.php');
29
30/**
31 * Experimental pdo database class
55946ffd 32 */
33class sqlite3_pdo_moodle_database extends pdo_moodle_database {
34 protected $database_file_extension = '.sq3.php';
35 /**
36 * Detects if all needed PHP stuff installed.
37 * Note: can be used before connect()
38 * @return mixed true if ok, string if something
39 */
40 public function driver_installed() {
fa648098 41 if (!extension_loaded('pdo_sqlite') || !extension_loaded('pdo')){
55946ffd 42 return get_string('sqliteextensionisnotpresentinphp', 'install');
fa648098 43 }
55946ffd 44 return true;
45 }
46
47 /**
48 * Returns database family type - describes SQL dialect
49 * Note: can be used before connect()
50 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
51 */
52 public function get_dbfamily() {
53 return 'sqlite';
54 }
55
56 /**
57 * Returns more specific database driver type
58 * Note: can be used before connect()
59 * @return string db type mysql, mysqli, postgres7
60 */
61 protected function get_dbtype() {
62 return 'sqlite3';
63 }
64
65 protected function configure_dbconnection() {
66 // try to protect database file agains web access;
67 // this is required in case that the moodledata folder is web accessible and
68 // .htaccess is not in place; requires that the database file extension is php
69 $this->pdb->exec('CREATE TABLE IF NOT EXISTS "<?php die?>" (id int)');
70 $this->pdb->exec('PRAGMA synchronous=OFF');
71 $this->pdb->exec('PRAGMA short_column_names=1');
72 $this->pdb->exec('PRAGMA encoding="UTF-8"');
73 $this->pdb->exec('PRAGMA case_sensitive_like=0');
74 $this->pdb->exec('PRAGMA locking_mode=NORMAL');
75 }
76
77 /**
78 * Attempt to create the database
79 * @param string $dbhost
80 * @param string $dbuser
81 * @param string $dbpass
82 * @param string $dbname
83 *
84 * @return bool success
85 */
3b093310 86 public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
55946ffd 87 $this->dbhost = $dbhost;
88 $this->dbuser = $dbuser;
89 $this->dbpass = $dbpass;
90 $this->dbname = $dbname;
91 $filepath = $this->get_dbfilepath();
92 $dirpath = dirname($filepath);
93 @mkdir($dirpath);
94 return touch($filepath);
95 }
96
97 /**
98 * Returns the driver-dependent DSN for PDO based on members stored by connect.
99 * Must be called after connect (or after $dbname, $dbhost, etc. members have been set).
100 * @return string driver-dependent DSN
101 */
eccc4a07 102 protected function get_dsn() {
a47c84c4 103 return 'sqlite:'.$this->get_dbfilepath();
55946ffd 104 }
105
106 /**
107 * Returns the file path for the database file, computed from dbname and/or dboptions.
108 * If dboptions['file'] is set, then it is used (use :memory: for in memory database);
109 * else if dboptions['path'] is set, then the file will be <dboptions path>/<dbname>.sq3.php;
110 * else if dbhost is set and not localhost, then the file will be <dbhost>/<dbname>.sq3.php;
111 * else the file will be <moodle data path>/<dbname>.sq3.php
112 * @return string file path to the SQLite database;
113 */
114 public function get_dbfilepath() {
115 global $CFG;
a47c84c4 116 if (!empty($this->dboptions['file'])) {
55946ffd 117 return $this->dboptions['file'];
118 }
a47c84c4 119 if ($this->dbhost && $this->dbhost != 'localhost') {
55946ffd 120 $path = $this->dbhost;
121 } else {
122 $path = $CFG->dataroot;
123 }
a47c84c4 124 $path = rtrim($path, '\\/').'/';
125 if (!empty($this->dbuser)) {
126 $path .= $this->dbuser.'_';
55946ffd 127 }
a47c84c4 128 $path .= $this->dbname.'_'.md5($this->dbpass).$this->database_file_extension;
55946ffd 129 return $path;
130 }
131
132 /**
133 * Return tables in database WITHOUT current prefix
134 * @return array of table names in lowercase and without prefix
135 */
117679db 136 public function get_tables($usecache=true) {
55946ffd 137 $tables = array();
b9c639d6 138
a47c84c4 139 $sql = 'SELECT name FROM sqlite_master WHERE type="table" UNION ALL SELECT name FROM sqlite_temp_master WHERE type="table" ORDER BY name';
140 if ($this->debug) {
55946ffd 141 $this->debug_query($sql);
142 }
143 $rstables = $this->pdb->query($sql);
144 foreach ($rstables as $table) {
145 $table = $table['name'];
146 $table = strtolower($table);
ba147910 147 if (empty($this->prefix) || strpos($table, $this->prefix) === 0) {
148 $table = substr($table, strlen($this->prefix));
55946ffd 149 $tables[$table] = $table;
150 }
151 }
152 return $tables;
153 }
154
155 /**
156 * Return table indexes - everything lowercased
157 * @return array of arrays
158 */
159 public function get_indexes($table) {
160 $indexes = array();
a47c84c4 161 $sql = 'PRAGMA index_list('.$this->prefix.$table.')';
162 if ($this->debug) {
55946ffd 163 $this->debug_query($sql);
164 }
165 $rsindexes = $this->pdb->query($sql);
166 foreach($rsindexes as $index) {
943de432 167 $unique = (boolean)$index['unique'];
168 $index = $index['name'];
a47c84c4 169 $sql = 'PRAGMA index_info("'.$index.'")';
170 if ($this->debug) {
55946ffd 171 $this->debug_query($sql);
172 }
173 $rscolumns = $this->pdb->query($sql);
174 $columns = array();
175 foreach($rscolumns as $row) {
176 $columns[] = strtolower($row['name']);
177 }
178 $index = strtolower($index);
943de432 179 $indexes[$index]['unique'] = $unique;
55946ffd 180 $indexes[$index]['columns'] = $columns;
181 }
182 return $indexes;
183 }
184
185 /**
186 * Returns datailed information about columns in table. This information is cached internally.
187 * @param string $table name
188 * @param bool $usecache
189 * @return array array of database_column_info objects indexed with column names
190 */
191 public function get_columns($table, $usecache=true) {
192 if ($usecache and isset($this->columns[$table])) {
193 return $this->columns[$table];
194 }
195 // get table's CREATE TABLE command (we'll need it for autoincrement fields)
a47c84c4 196 $sql = 'SELECT sql FROM sqlite_master WHERE type="table" AND tbl_name="'.$this->prefix.$table.'"';
197 if ($this->debug) {
55946ffd 198 $this->debug_query($sql);
199 }
200 $createsql = $this->pdb->query($sql)->fetch();
a47c84c4 201 if (!$createsql) {
55946ffd 202 return false;
203 }
204 $createsql = $createsql['sql'];
205
206 $columns = array();
a47c84c4 207 $sql = 'PRAGMA table_info("'. $this->prefix.$table.'")';
208 if ($this->debug) {
55946ffd 209 $this->debug_query($sql);
210 }
211 $rscolumns = $this->pdb->query($sql);
212 foreach ($rscolumns as $row) {
213 $columninfo = array(
214 'name' => strtolower($row['name']), // colum names must be lowercase
215 'not_null' =>(boolean)$row['notnull'],
216 'primary_key' => (boolean)$row['pk'],
217 'has_default' => !is_null($row['dflt_value']),
218 'default_value' => $row['dflt_value'],
943de432 219 'auto_increment' => false,
220 'binary' => false,
221 //'unsigned' => false,
55946ffd 222 );
223 $type = explode('(', $row['type']);
224 $columninfo['type'] = strtolower($type[0]);
a47c84c4 225 if (count($type) > 1) {
55946ffd 226 $size = explode(',', trim($type[1], ')'));
227 $columninfo['max_length'] = $size[0];
a47c84c4 228 if (count($size) > 1) {
55946ffd 229 $columninfo['scale'] = $size[1];
230 }
231 }
232 // SQLite does not have a fixed set of datatypes (ie. it accepts any string as
233 // datatype in the CREATE TABLE command. We try to guess which type is used here
234 switch(substr($columninfo['type'], 0, 3)) {
235 case 'int': // int integer
a47c84c4 236 if ($columninfo['primary_key'] && preg_match('/'.$columninfo['name'].'\W+integer\W+primary\W+key\W+autoincrement/im', $createsql)) {
55946ffd 237 $columninfo['meta_type'] = 'R';
943de432 238 $columninfo['auto_increment'] = true;
55946ffd 239 } else {
240 $columninfo['meta_type'] = 'I';
241 }
242 break;
243 case 'num': // number numeric
244 case 'rea': // real
245 case 'dou': // double
246 case 'flo': // float
247 $columninfo['meta_type'] = 'N';
248 break;
249 case 'var': // varchar
250 case 'cha': // char
251 $columninfo['meta_type'] = 'C';
252 break;
a47c84c4 253 case 'enu': // enums
254 if (preg_match('|'.$columninfo['name'].'\W+in\W+\(/\*liststart\*/(.*?)/\*listend\*/\)|im', $createsql, $tmp)) {
255 $tmp = explode(',', $tmp[1]);
256 foreach($tmp as $value) {
257 $columninfo['enums'][] = trim($value, '\'"');
258 }
259 unset($tmp);
260 }
261 $columninfo['meta_type'] = 'C';
262 break;
55946ffd 263 case 'tex': // text
264 case 'clo': // clob
265 $columninfo['meta_type'] = 'X';
266 break;
267 case 'blo': // blob
268 case 'non': // none
269 $columninfo['meta_type'] = 'B';
943de432 270 $columninfo['binary'] = true;
55946ffd 271 break;
272 case 'boo': // boolean
273 case 'bit': // bit
274 case 'log': // logical
275 $columninfo['meta_type'] = 'L';
943de432 276 $columninfo['max_length'] = 1;
55946ffd 277 break;
278 case 'tim': // timestamp
279 $columninfo['meta_type'] = 'T';
280 break;
281 case 'dat': // date datetime
282 $columninfo['meta_type'] = 'D';
283 break;
284 }
a47c84c4 285 if ($columninfo['has_default'] && ($columninfo['meta_type'] == 'X' || $columninfo['meta_type']== 'C')) {
286 // trim extra quotes from text default values
287 $columninfo['default_value'] = substr($columninfo['default_value'], 1, -1);
288 }
55946ffd 289 $columns[$columninfo['name']] = new database_column_info($columninfo);
290 }
291
292 $this->columns[$table] = $columns;
293 return $columns;
294 }
295
e3acc8af
EL
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
55946ffd 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) {
a47c84c4 315 if ($limitnum) {
316 $sql .= ' LIMIT '.$limitnum;
317 if ($limitfrom) {
318 $sql .= ' OFFSET '.$limitfrom;
55946ffd 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($conditions);
337 return $this->delete_records_select($table, $select, $params);
338 }
339
55946ffd 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.
a47c84c4 365 for ($n=count($elements)-1; $n > 0; $n--) {
55946ffd 366 array_splice($elements, $n, 0, $separator);
367 }
368 return implode('||', $elements);
369 }
0661894c 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 }
b9c639d6 382}