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
> @version v5.21.0 2021-02-27
@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/
> Latest version is available at https://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. */ // 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; >
}
> /* > * In order to map the values correctly, we must ensure the proper return $rez; > * casing for certain fields } > * Y must be UC, because y is a 2 digit year > * d must be LC, because D is 3 char day function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0) > * A must be UC because a is non-portable am { > * Q must be UC because q means nothing $nrows = (int) $nrows; > */ $offset = (int) $offset; > $fromChars = array('y','D','a','q'); $offsetStr = ($offset >= 0) ? " OFFSET $offset" : ''; > $toChars = array('Y','d','A','Q'); $limitStr = ($nrows >= 0) ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : ''); > $fmt = str_replace($fromChars,$toChars,$fmt); if ($secs2cache) { >
<
} else { $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;
} }