Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.10.x will end 8 November 2021 (12 months).
  • Bug fixes for security issues in 3.10.x will end 9 May 2022 (18 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.
<?php
< /* < @version v5.20.16 12-Jan-2020 < @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved. < @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community < Released under both BSD license and Lesser GPL library license. < Whenever there is any discrepancy between the two licenses, < the BSD license will take precedence. < < Latest version is available at http://adodb.org/ < < SQLite info: http://www.hwaci.com/sw/sqlite/ < < Install Instructions: < ==================== < 1. Place this in adodb/drivers < 2. Rename the file, remove the .txt prefix.
> /** > * SQLite3 driver > * > * @link https://www.sqlite.org/ > * > * This file is part of ADOdb, a Database Abstraction Layer library for PHP. > * > * @package ADOdb > * @link https://adodb.org Project's web site and documentation > * @link https://github.com/ADOdb/ADOdb Source code and issue tracker > * > * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause > * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option, > * any later version. This means you can use it in proprietary products. > * See the LICENSE.md file distributed with this source code for details. > * @license BSD-3-Clause > * @license LGPL-2.1-or-later > * > * @copyright 2000-2013 John Lim > * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
*/ // security - hide paths if (!defined('ADODB_DIR')) die(); class ADODB_sqlite3 extends ADOConnection { var $databaseType = "sqlite3";
> var $dataProvider = "sqlite";
var $replaceQuote = "''"; // string to use to replace quotes var $concat_operator='||'; var $_errorNo = 0; var $hasLimit = true; var $hasInsertID = true; /// supports autoincrement ID? var $hasAffectedRows = true; /// supports affected rows for update/delete? var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"; var $sysDate = "adodb_date('Y-m-d')"; var $sysTimeStamp = "adodb_date('Y-m-d H:i:s')"; var $fmtTimeStamp = "'Y-m-d H:i:s'";
< function __construct() < { < } <
function ServerInfo() { $version = SQLite3::version(); $arr['version'] = $version['versionString']; $arr['description'] = 'SQLite 3'; return $arr; } function BeginTrans() { if ($this->transOff) { return true; } $ret = $this->Execute("BEGIN TRANSACTION"); $this->transCnt += 1; return true; } function CommitTrans($ok=true) { if ($this->transOff) { return true; } if (!$ok) { return $this->RollbackTrans(); } $ret = $this->Execute("COMMIT"); if ($this->transCnt > 0) { $this->transCnt -= 1; } return !empty($ret); } function RollbackTrans() { if ($this->transOff) { return true; } $ret = $this->Execute("ROLLBACK"); if ($this->transCnt > 0) { $this->transCnt -= 1; } return !empty($ret); }
> function metaType($t,$len=-1,$fieldobj=false) // mark newnham > { function MetaColumns($table, $normalize=true) > { > if (is_object($t)) global $ADODB_FETCH_MODE; > { $false = false; > $fieldobj = $t; $save = $ADODB_FETCH_MODE; > $t = $fieldobj->type; $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; > $len = $fieldobj->max_length; if ($this->fetchMode !== false) { > } $savem = $this->SetFetchMode(false); > } > $t = strtoupper($t); $rs = $this->Execute("PRAGMA table_info('$table')"); > if (isset($savem)) { > /* $this->SetFetchMode($savem); > * We are using the Sqlite affinity method here } > * @link https://www.sqlite.org/datatype3.html if (!$rs) { > */ $ADODB_FETCH_MODE = $save; > $affinity = array( return $false; > 'INT'=>'INTEGER', } > 'INTEGER'=>'INTEGER', $arr = array(); > 'TINYINT'=>'INTEGER', while ($r = $rs->FetchRow()) { > 'SMALLINT'=>'INTEGER', $type = explode('(',$r['type']); > 'MEDIUMINT'=>'INTEGER', $size = ''; > 'BIGINT'=>'INTEGER', if (sizeof($type)==2) { > 'UNSIGNED BIG INT'=>'INTEGER', $size = trim($type[1],')'); > 'INT2'=>'INTEGER', } > 'INT8'=>'INTEGER', $fn = strtoupper($r['name']); > $fld = new ADOFieldObject; > 'CHARACTER'=>'TEXT', $fld->name = $r['name']; > 'VARCHAR'=>'TEXT', $fld->type = $type[0]; > 'VARYING CHARACTER'=>'TEXT', $fld->max_length = $size; > 'NCHAR'=>'TEXT', $fld->not_null = $r['notnull']; > 'NATIVE CHARACTER'=>'TEXT', $fld->default_value = $r['dflt_value']; > 'NVARCHAR'=>'TEXT', $fld->scale = 0; > 'TEXT'=>'TEXT', if (isset($r['pk']) && $r['pk']) { > 'CLOB'=>'TEXT', $fld->primary_key=1; > } > 'BLOB'=>'BLOB', if ($save == ADODB_FETCH_NUM) { > $arr[] = $fld; > 'REAL'=>'REAL', } else { > 'DOUBLE'=>'REAL', $arr[strtoupper($fld->name)] = $fld; > 'DOUBLE PRECISION'=>'REAL', } > 'FLOAT'=>'REAL', } > $rs->Close(); > 'NUMERIC'=>'NUMERIC', $ADODB_FETCH_MODE = $save; > 'DECIMAL'=>'NUMERIC', return $arr; > 'BOOLEAN'=>'NUMERIC', } > 'DATE'=>'NUMERIC', > 'DATETIME'=>'NUMERIC' function _init($parentDriver) > ); { > $parentDriver->hasTransactions = false; > if (!isset($affinity[$t])) $parentDriver->hasInsertID = true; > return ADODB_DEFAULT_METATYPE; } > > $subt = $affinity[$t]; function _insertid() > /* { > * Now that we have subclassed the provided data down return $this->_connectionID->lastInsertRowID(); > * the sqlite 'affinity', we convert to ADOdb metatype } > */ > function _affectedrows() > $subclass = array('INTEGER'=>'I', { > 'TEXT'=>'X', return $this->_connectionID->changes(); > 'BLOB'=>'B', } > 'REAL'=>'N', > 'NUMERIC'=>'N'); function ErrorMsg() > { > return $subclass[$subt]; if ($this->_logsql) { > }
return $this->_errorMsg;
> function metaForeignKeys( $table, $owner = FALSE, $upper = FALSE, $associative = FALSE ) } > { return ($this->_errorNo) ? $this->ErrorNo() : ''; //**tochange? > global $ADODB_FETCH_MODE; } > if ($ADODB_FETCH_MODE == ADODB_FETCH_ASSOC > || $this->fetchMode == ADODB_FETCH_ASSOC) function ErrorNo() > $associative = true; { > return $this->_connectionID->lastErrorCode(); //**tochange?? > /* } > * Read sqlite master to find foreign keys > */ function SQLDate($fmt, $col=false) > $sql = "SELECT sql { > FROM ( $fmt = $this->qstr($fmt); > SELECT sql sql, type type, tbl_name tbl_name, name name return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)"; > FROM sqlite_master } > ) > WHERE type != 'meta' > AND sql NOTNULL function _createFunctions() > AND LOWER(name) ='" . strtolower($table) . "'"; { > $this->_connectionID->createFunction('adodb_date', 'adodb_date', 1); > $tableSql = $this->getOne($sql); $this->_connectionID->createFunction('adodb_date2', 'adodb_date2', 2); > } > $fkeyList = array(); > $ylist = preg_split("/,+/",$tableSql); > foreach ($ylist as $y) // returns true or false > { function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) > if (!preg_match('/FOREIGN/',$y)) { > continue; if (empty($argHostname) && $argDatabasename) { > $argHostname = $argDatabasename; > $matches = false; } > preg_match_all('/\((.+?)\)/i',$y,$matches); $this->_connectionID = new SQLite3($argHostname); > $tmatches = false; $this->_createFunctions(); > preg_match_all('/REFERENCES (.+?)\(/i',$y,$tmatches); > return true; > if ($associative) } > { > if (!isset($fkeyList[$tmatches[1][0]])) // returns true or false > $fkeyList[$tmatches[1][0]] = array(); function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) > $fkeyList[$tmatches[1][0]][$matches[1][0]] = $matches[1][1]; { > } // There's no permanent connect in SQLite3 > else return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename); > $fkeyList[$tmatches[1][0]][] = $matches[1][0] . '=' . $matches[1][1]; } > } > // returns query ID if successful, otherwise false > if ($associative) function _query($sql,$inputarr=false) > { { > if ($upper) $rez = $this->_connectionID->query($sql); > $fkeyList = array_change_key_case($fkeyList,CASE_UPPER); if ($rez === false) { > else $this->_errorNo = $this->_connectionID->lastErrorCode(); > $fkeyList = array_change_key_case($fkeyList,CASE_LOWER); } > } // If no data was returned, we don't need to create a real recordset > return $fkeyList; elseif ($rez->numColumns() == 0) { > } $rez->finalize(); > $rez = true; >
< function _insertid()
> protected function _insertID($table = '', $column = '')
> /* return $rez; > * In order to map the values correctly, we must ensure the proper } > * casing for certain fields > * Y must be UC, because y is a 2 digit year function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0) > * d must be LC, because D is 3 char day { > * A must be UC because a is non-portable am $nrows = (int) $nrows; > * Q must be UC because q means nothing $offset = (int) $offset; > */ $offsetStr = ($offset >= 0) ? " OFFSET $offset" : ''; > $fromChars = array('y','D','a','q'); $limitStr = ($nrows >= 0) ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : ''); > $toChars = array('Y','d','A','Q'); if ($secs2cache) { > $fmt = str_replace($fromChars,$toChars,$fmt); $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr); >
<
$rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr); } return $rs; } /* This algorithm is not very efficient, but works even if table locking is not available. Will return false if unable to generate an ID after $MAXLOOPS attempts. */ var $_genSeqSQL = "create table %s (id integer)"; function GenID($seq='adodbseq',$start=1) { // if you have to modify the parameter below, your database is overloaded, // or you need to implement generation of id's yourself! $MAXLOOPS = 100; //$this->debug=1; while (--$MAXLOOPS>=0) { @($num = $this->GetOne("select id from $seq")); if ($num === false) { $this->Execute(sprintf($this->_genSeqSQL ,$seq)); $start -= 1; $num = '0'; $ok = $this->Execute("insert into $seq values($start)"); if (!$ok) { return false; } } $this->Execute("update $seq set id=id+1 where id=$num"); if ($this->affected_rows() > 0) { $num += 1; $this->genID = $num; return $num; } } if ($fn = $this->raiseErrorFn) { $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num); } return false; } function CreateSequence($seqname='adodbseq',$start=1) { if (empty($this->_genSeqSQL)) { return false; } $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname)); if (!$ok) { return false; } $start -= 1; return $this->Execute("insert into $seqname values($start)"); } var $_dropSeqSQL = 'drop table %s'; function DropSequence($seqname = 'adodbseq') { if (empty($this->_dropSeqSQL)) { return false; } return $this->Execute(sprintf($this->_dropSeqSQL,$seqname)); } // returns true or false function _close() { return $this->_connectionID->close(); }
< function MetaIndexes($table, $primary = FALSE, $owner = false)
> function metaIndexes($table, $primary = FALSE, $owner = false)
{ $false = false; // save old fetch mode global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; if ($this->fetchMode !== FALSE) { $savem = $this->SetFetchMode(FALSE); }
< $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table)); < $rs = $this->Execute($SQL);
> > $pragmaData = array(); > > /* > * If we want the primary key, we must extract > * it from the table statement, and the pragma > */ > if ($primary) > { > $sql = sprintf('PRAGMA table_info([%s]);', > strtolower($table) > ); > $pragmaData = $this->getAll($sql); > } > > /* > * Exclude the empty entry for the primary index > */ > $sqlite = "SELECT name,sql > FROM sqlite_master > WHERE type='index' > AND sql IS NOT NULL > AND LOWER(tbl_name)='%s'"; > > $SQL = sprintf($sqlite, > strtolower($table) > ); > > $rs = $this->execute($SQL); >
if (!is_object($rs)) { if (isset($savem)) { $this->SetFetchMode($savem); } $ADODB_FETCH_MODE = $save; return $false; } $indexes = array ();
< while ($row = $rs->FetchRow()) { < if ($primary && preg_match("/primary/i",$row[1]) == 0) { < continue; < }
> > while ($row = $rs->FetchRow()) > { >
if (!isset($indexes[$row[0]])) { $indexes[$row[0]] = array( 'unique' => preg_match("/unique/i",$row[1]), 'columns' => array() ); } /**
< * There must be a more elegant way of doing this, < * the index elements appear in the SQL statement
> * The index elements appear in the SQL statement
* in cols[1] between parentheses * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse) */
< $cols = explode("(",$row[1]); < $cols = explode(")",$cols[1]); < array_pop($cols); < $indexes[$row[0]]['columns'] = $cols;
> preg_match_all('/\((.*)\)/',$row[1],$indexExpression); > $indexes[$row[0]]['columns'] = array_map('trim',explode(',',$indexExpression[1][0]));
}
>
if (isset($savem)) { $this->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; }
> return $indexes; > /* } > * If we want primary, add it here > */ } > if ($primary){ > /*-------------------------------------------------------------------------------------- > /* Class Name: Recordset > * Check the previously retrieved pragma to search --------------------------------------------------------------------------------------*/ > * with a closure > */ class ADORecordset_sqlite3 extends ADORecordSet { > > $pkIndexData = array('unique'=>1,'columns'=>array()); var $databaseType = "sqlite3"; > var $bind = false; > $pkCallBack = function ($value, $key) use (&$pkIndexData) { > function __construct($queryID,$mode=false) > /* { > * As we iterate the elements check for pk index and sort > */ if ($mode === false) { > if ($value[5] > 0) global $ADODB_FETCH_MODE; > { $mode = $ADODB_FETCH_MODE; > $pkIndexData['columns'][$value[5]] = strtolower($value[1]); } > ksort($pkIndexData['columns']); switch($mode) { > } case ADODB_FETCH_NUM: > }; $this->fetchMode = SQLITE3_NUM; > break; > array_walk($pragmaData,$pkCallBack); case ADODB_FETCH_ASSOC: > $this->fetchMode = SQLITE3_ASSOC; > /* break; > * If we found no columns, there is no default: > * primary index $this->fetchMode = SQLITE3_BOTH; > */ break; > if (count($pkIndexData['columns']) > 0) } > $indexes['PRIMARY'] = $pkIndexData; $this->adodbFetchMode = $mode; > } >
$this->_queryID = $queryID;
> } > $this->_inited = true; > /** $this->fields = array(); > * Returns the maximum size of a MetaType C field. Because of the if ($queryID) { > * database design, sqlite places no limits on the size of data inserted $this->_currentRow = 0; > * $this->EOF = !$this->_fetch(); > * @return int @$this->_initrs(); > */ } else { > function charMax() $this->_numOfRows = 0; > { $this->_numOfFields = 0; > return ADODB_STRINGMAX_NOLIMIT; $this->EOF = true; > } } > > /** return $this->_queryID; > * Returns the maximum size of a MetaType X field. Because of the } > * database design, sqlite places no limits on the size of data inserted > * > * @return int function FetchField($fieldOffset = -1) > */ { > function textMax() $fld = new ADOFieldObject; > { $fld->name = $this->_queryID->columnName($fieldOffset); > return ADODB_STRINGMAX_NOLIMIT; $fld->type = 'VARCHAR'; > } $fld->max_length = -1; > return $fld; > /** } > * Converts a date to a month only field and pads it to 2 characters > * function _initrs() > * This uses the more efficient strftime native function to process { > * $this->_numOfFields = $this->_queryID->numColumns(); > * @param str $fld The name of the field to process > * } > * @return str The SQL Statement > */ function Fields($colname) > function month($fld) { > { if ($this->fetchMode != SQLITE3_NUM) { > $x = "strftime('%m',$fld)"; return $this->fields[$colname]; > return $x; } > } if (!$this->bind) { > $this->bind = array(); > /** for ($i=0; $i < $this->_numOfFields; $i++) { > * Converts a date to a day only field and pads it to 2 characters $o = $this->FetchField($i); > * $this->bind[strtoupper($o->name)] = $i; > * This uses the more efficient strftime native function to process } > * } > * @param str $fld The name of the field to process > * return $this->fields[$this->bind[strtoupper($colname)]]; > * @return str The SQL Statement } > */ > function day($fld) { function _seek($row) > $x = "strftime('%d',$fld)"; { > return $x; // sqlite3 does not implement seek > } if ($this->debug) { > ADOConnection::outp("SQLite3 does not implement seek"); > /** } > * Converts a date to a year only field return false; > * } > * This uses the more efficient strftime native function to process > * function _fetch($ignore_fields=false) > * @param str $fld The name of the field to process { > * $this->fields = $this->_queryID->fetchArray($this->fetchMode); > * @return str The SQL Statement return !empty($this->fields); > */ } > function year($fld) > { function _close() > $x = "strftime('%Y',$fld)"; { > return $x;
} }