C:/xampp/htdocs/moodle/lib/dml/sqlite3_pdo_moodle_database.php

Go to the documentation of this file.

<?php

// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.


defined('MOODLE_INTERNAL') || die();

require_once($CFG->libdir.'/dml/pdo_moodle_database.php');

class sqlite3_pdo_moodle_database extends pdo_moodle_database {
    protected $database_file_extension = '.sq3.php';
    public function driver_installed() {
        if (!extension_loaded('pdo_sqlite') || !extension_loaded('pdo')){
            return get_string('sqliteextensionisnotpresentinphp', 'install');
        }
        return true;
    }

    public function get_dbfamily() {
        return 'sqlite';
    }

    protected function get_dbtype() {
        return 'sqlite3';
    }

    protected function configure_dbconnection() {
        // try to protect database file against web access;
        // this is required in case that the moodledata folder is web accessible and
        // .htaccess is not in place; requires that the database file extension is php
        $this->pdb->exec('CREATE TABLE IF NOT EXISTS "<?php die?>" (id int)');
        $this->pdb->exec('PRAGMA synchronous=OFF');
        $this->pdb->exec('PRAGMA short_column_names=1');
        $this->pdb->exec('PRAGMA encoding="UTF-8"');
        $this->pdb->exec('PRAGMA case_sensitive_like=0');
        $this->pdb->exec('PRAGMA locking_mode=NORMAL');
    }

    public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
        $this->dbhost = $dbhost;
        $this->dbuser = $dbuser;
        $this->dbpass = $dbpass;
        $this->dbname = $dbname;
        $filepath = $this->get_dbfilepath();
        $dirpath = dirname($filepath);
        @mkdir($dirpath);
        return touch($filepath);
    }

    protected function get_dsn() {
        return 'sqlite:'.$this->get_dbfilepath();
    }

    public function get_dbfilepath() {
        global $CFG;
        if (!empty($this->dboptions['file'])) {
            return $this->dboptions['file'];
        }
        if ($this->dbhost && $this->dbhost != 'localhost') {
            $path = $this->dbhost;
        } else {
            $path = $CFG->dataroot;
        }
        $path = rtrim($path, '\\/').'/';
        if (!empty($this->dbuser)) {
            $path .= $this->dbuser.'_';
        }
        $path .= $this->dbname.'_'.md5($this->dbpass).$this->database_file_extension;
        return $path;
    }

    public function get_tables($usecache=true) {
        $tables = array();

        $sql = 'SELECT name FROM sqlite_master WHERE type="table" UNION ALL SELECT name FROM sqlite_temp_master WHERE type="table" ORDER BY name';
        if ($this->debug) {
            $this->debug_query($sql);
        }
        $rstables = $this->pdb->query($sql);
        foreach ($rstables as $table) {
            $table = $table['name'];
            $table = strtolower($table);
            if (empty($this->prefix) || strpos($table, $this->prefix) === 0) {
                $table = substr($table, strlen($this->prefix));
                $tables[$table] = $table;
            }
        }
        return $tables;
    }

    public function get_indexes($table) {
        $indexes = array();
        $sql = 'PRAGMA index_list('.$this->prefix.$table.')';
        if ($this->debug) {
            $this->debug_query($sql);
        }
        $rsindexes = $this->pdb->query($sql);
        foreach($rsindexes as $index) {
            $unique = (boolean)$index['unique'];
            $index = $index['name'];
            $sql = 'PRAGMA index_info("'.$index.'")';
            if ($this->debug) {
                $this->debug_query($sql);
            }
            $rscolumns = $this->pdb->query($sql);
            $columns = array();
            foreach($rscolumns as $row) {
                $columns[] = strtolower($row['name']);
            }
            $index = strtolower($index);
            $indexes[$index]['unique'] = $unique;
            $indexes[$index]['columns'] = $columns;
        }
        return $indexes;
    }

    public function get_columns($table, $usecache=true) {
        if ($usecache and isset($this->columns[$table])) {
            return $this->columns[$table];
        }
        // get table's CREATE TABLE command (we'll need it for autoincrement fields)
        $sql = 'SELECT sql FROM sqlite_master WHERE type="table" AND tbl_name="'.$this->prefix.$table.'"';
        if ($this->debug) {
            $this->debug_query($sql);
        }
        $createsql = $this->pdb->query($sql)->fetch();
        if (!$createsql) {
            return false;
        }
        $createsql = $createsql['sql'];

        $columns = array();
        $sql = 'PRAGMA table_info("'. $this->prefix.$table.'")';
        if ($this->debug) {
            $this->debug_query($sql);
        }
        $rscolumns = $this->pdb->query($sql);
        foreach ($rscolumns as $row) {
            $columninfo = array(
                'name' => strtolower($row['name']), // colum names must be lowercase
                'not_null' =>(boolean)$row['notnull'],
                'primary_key' => (boolean)$row['pk'],
                'has_default' => !is_null($row['dflt_value']),
                'default_value' => $row['dflt_value'],
                'auto_increment' => false,
                'binary' => false,
                //'unsigned' => false,
            );
            $type = explode('(', $row['type']);
            $columninfo['type'] = strtolower($type[0]);
            if (count($type) > 1) {
                $size = explode(',', trim($type[1], ')'));
                $columninfo['max_length'] = $size[0];
                if (count($size) > 1) {
                    $columninfo['scale'] = $size[1];
                }
            }
            // SQLite does not have a fixed set of datatypes (ie. it accepts any string as
            // datatype in the CREATE TABLE command. We try to guess which type is used here
            switch(substr($columninfo['type'], 0, 3)) {
                case 'int': // int integer
                    if ($columninfo['primary_key'] && preg_match('/'.$columninfo['name'].'\W+integer\W+primary\W+key\W+autoincrement/im', $createsql)) {
                        $columninfo['meta_type'] = 'R';
                        $columninfo['auto_increment'] = true;
                    } else {
                        $columninfo['meta_type'] = 'I';
                    }
                    break;
                case 'num': // number numeric
                case 'rea': // real
                case 'dou': // double
                case 'flo': // float
                    $columninfo['meta_type'] = 'N';
                    break;
                case 'var': // varchar
                case 'cha': // char
                    $columninfo['meta_type'] = 'C';
                    break;
                case 'enu': // enums
                    if (preg_match('|'.$columninfo['name'].'\W+in\W+\(/\*liststart\*/(.*?)/\*listend\*/\)|im', $createsql, $tmp)) {
                        $tmp = explode(',', $tmp[1]);
                        foreach($tmp as $value) {
                            $columninfo['enums'][] = trim($value, '\'"');
                        }
                        unset($tmp);
                    }
                    $columninfo['meta_type'] = 'C';
                    break;
                case 'tex': // text
                case 'clo': // clob
                    $columninfo['meta_type'] = 'X';
                    break;
                case 'blo': // blob
                case 'non': // none
                    $columninfo['meta_type'] = 'B';
                    $columninfo['binary'] = true;
                    break;
                case 'boo': // boolean
                case 'bit': // bit
                case 'log': // logical
                    $columninfo['meta_type'] = 'L';
                    $columninfo['max_length'] = 1;
                    break;
                case 'tim': // timestamp
                    $columninfo['meta_type'] = 'T';
                    break;
                case 'dat': // date datetime
                    $columninfo['meta_type'] = 'D';
                    break;
            }
            if ($columninfo['has_default'] && ($columninfo['meta_type'] == 'X' || $columninfo['meta_type']== 'C')) {
                // trim extra quotes from text default values
                $columninfo['default_value'] = substr($columninfo['default_value'], 1, -1);
            }
            $columns[$columninfo['name']] = new database_column_info($columninfo);
        }

        $this->columns[$table] = $columns;
        return $columns;
    }

    protected function normalise_value($column, $value) {
        return $value;
    }

    protected function get_limit_clauses($sql, $limitfrom=0, $limitnum=0) {
        if ($limitnum) {
            $sql .= ' LIMIT '.$limitnum;
            if ($limitfrom) {
                $sql .= ' OFFSET '.$limitfrom;
            }
        }
        return $sql;
    }

    public function delete_records($table, array $conditions=null) {
        if (is_null($conditions)) {
            return $this->execute("DELETE FROM {{$table}}");
        }
        list($select, $params) = $this->where_clause($table, $conditions);
        return $this->delete_records_select($table, $select, $params);
    }

    public function sql_concat() {
        $elements = func_get_args();
        return implode('||', $elements);
    }

    public function sql_concat_join($separator="' '", $elements=array()) {
        // Intersperse $elements in the array.
        // Add items to the array on the fly, walking it
        // _backwards_ splicing the elements in. The loop definition
        // should skip first and last positions.
        for ($n=count($elements)-1; $n > 0; $n--) {
            array_splice($elements, $n, 0, $separator);
        }
        return implode('||', $elements);
    }

    public function sql_bitxor($int1, $int2) {
        return '( ~' . $this->sql_bitand($int1, $int2) . ' & ' . $this->sql_bitor($int1, $int2) . ')';
    }
}