See Release Notes
Long Term Support Release
<?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 { > * Class ADODB_sqlite3 var $databaseType = "sqlite3"; > */var $replaceQuote = "''"; // string to use to replace quotes> var $dataProvider = "sqlite";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 $sysDate = "DATE('now','localtime')"; > var $sysTimeStamp = "DATETIME('now','localtime')";var $fmtTimeStamp = "'Y-m-d H:i:s'";< function __construct() < { < }> /** @var SQLite3 */ > var $_connectionID;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->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)) { > if (array_key_exists($t,$this->customActualTypes)) $this->SetFetchMode($savem); > return $this->customActualTypes[$t]; } > if (!$rs) { > /* $ADODB_FETCH_MODE = $save; > * We are using the Sqlite affinity method here return $false; > * @link https://www.sqlite.org/datatype3.html } > */ $arr = array(); > $affinity = array( while ($r = $rs->FetchRow()) { > 'INT'=>'INTEGER', $type = explode('(',$r['type']); > 'INTEGER'=>'INTEGER', $size = ''; > 'TINYINT'=>'INTEGER', if (sizeof($type)==2) { > 'SMALLINT'=>'INTEGER', $size = trim($type[1],')'); > 'MEDIUMINT'=>'INTEGER', } > 'BIGINT'=>'INTEGER', $fn = strtoupper($r['name']); > 'UNSIGNED BIG INT'=>'INTEGER', $fld = new ADOFieldObject; > 'INT2'=>'INTEGER', $fld->name = $r['name']; > 'INT8'=>'INTEGER', $fld->type = $type[0]; > $fld->max_length = $size; > 'CHARACTER'=>'TEXT', $fld->not_null = $r['notnull']; > 'VARCHAR'=>'TEXT', $fld->default_value = $r['dflt_value']; > 'VARYING CHARACTER'=>'TEXT', $fld->scale = 0; > 'NCHAR'=>'TEXT', if (isset($r['pk']) && $r['pk']) { > 'NATIVE CHARACTER'=>'TEXT', $fld->primary_key=1; > 'NVARCHAR'=>'TEXT', } > 'TEXT'=>'TEXT', if ($save == ADODB_FETCH_NUM) { > 'CLOB'=>'TEXT', $arr[] = $fld; > } else { > 'BLOB'=>'BLOB', $arr[strtoupper($fld->name)] = $fld; > } > 'REAL'=>'REAL', } > 'DOUBLE'=>'REAL', $rs->Close(); > 'DOUBLE PRECISION'=>'REAL', $ADODB_FETCH_MODE = $save; > 'FLOAT'=>'REAL', return $arr; > } > 'NUMERIC'=>'NUMERIC', > 'DECIMAL'=>'NUMERIC', function _init($parentDriver) > 'BOOLEAN'=>'NUMERIC', { > 'DATE'=>'NUMERIC', $parentDriver->hasTransactions = false; > 'DATETIME'=>'NUMERIC' $parentDriver->hasInsertID = true; > ); } > > if (!isset($affinity[$t])) function _insertid() > return ADODB_DEFAULT_METATYPE; { > return $this->_connectionID->lastInsertRowID(); > $subt = $affinity[$t]; } > /* > * Now that we have subclassed the provided data down function _affectedrows() > * the sqlite 'affinity', we convert to ADOdb metatype { > */ return $this->_connectionID->changes(); > } > $subclass = array('INTEGER'=>'I', > 'TEXT'=>'X', function ErrorMsg() > 'BLOB'=>'B', { > 'REAL'=>'N', if ($this->_logsql) { > 'NUMERIC'=>'N'); return $this->_errorMsg; > } > return $subclass[$subt]; return ($this->_errorNo) ? $this->ErrorNo() : ''; //**tochange? > }}> public function metaForeignKeys($table, $owner = '', $upper = false, $associative = false) > { function ErrorNo() > global $ADODB_FETCH_MODE; { > if ($ADODB_FETCH_MODE == ADODB_FETCH_ASSOC return $this->_connectionID->lastErrorCode(); //**tochange?? > || $this->fetchMode == ADODB_FETCH_ASSOC) } > $associative = true; > function SQLDate($fmt, $col=false) > /* { > * Read sqlite master to find foreign keys $fmt = $this->qstr($fmt); > */ return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)"; > $sql = "SELECT sql } > FROM ( > SELECT sql sql, type type, tbl_name tbl_name, name name > FROM sqlite_master function _createFunctions() > ) { > WHERE type != 'meta' $this->_connectionID->createFunction('adodb_date', 'adodb_date', 1); > AND sql NOTNULL $this->_connectionID->createFunction('adodb_date2', 'adodb_date2', 2); > AND LOWER(name) ='" . strtolower($table) . "'"; } > > $tableSql = $this->getOne($sql); > // returns true or false > $fkeyList = array(); function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) > $ylist = preg_split("/,+/",$tableSql); { > foreach ($ylist as $y) if (empty($argHostname) && $argDatabasename) { > { $argHostname = $argDatabasename; > if (!preg_match('/FOREIGN/',$y)) } > continue; $this->_connectionID = new SQLite3($argHostname); > $this->_createFunctions(); > $matches = false; > preg_match_all('/\((.+?)\)/i',$y,$matches); return true; > $tmatches = false; } > preg_match_all('/REFERENCES (.+?)\(/i',$y,$tmatches); > // returns true or false > if ($associative) function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) > { { > if (!isset($fkeyList[$tmatches[1][0]])) // There's no permanent connect in SQLite3 > $fkeyList[$tmatches[1][0]] = array(); return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename); > $fkeyList[$tmatches[1][0]][$matches[1][0]] = $matches[1][1]; } > } > else // returns query ID if successful, otherwise false > $fkeyList[$tmatches[1][0]][] = $matches[1][0] . '=' . $matches[1][1]; function _query($sql,$inputarr=false) > } { > $rez = $this->_connectionID->query($sql); > if ($associative) if ($rez === false) { > { $this->_errorNo = $this->_connectionID->lastErrorCode(); > if ($upper) } > $fkeyList = array_change_key_case($fkeyList,CASE_UPPER); // If no data was returned, we don't need to create a real recordset > else elseif ($rez->numColumns() == 0) { > $fkeyList = array_change_key_case($fkeyList,CASE_LOWER); $rez->finalize(); > } $rez = true; > return $fkeyList; } > } > return $rez; >< function _insertid()> protected function _insertID($table = '', $column = '')> /* function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0) > * In order to map the values correctly, we must ensure the proper { > * casing for certain fields $nrows = (int) $nrows; > * Y must be UC, because y is a 2 digit year $offset = (int) $offset; > * d must be LC, because D is 3 char day $offsetStr = ($offset >= 0) ? " OFFSET $offset" : ''; > * A must be UC because a is non-portable am $limitStr = ($nrows >= 0) ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : ''); > * Q must be UC because q means nothing if ($secs2cache) { > */ $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr); > $fromChars = array('y','D','a','q'); } else { > $toChars = array('Y','d','A','Q'); $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr); > $fmt = str_replace($fromChars,$toChars,$fmt); } ><< < // returns true or false> /** @noinspection PhpUnusedParameterInspection */< // returns true or false/* 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)> function createSequence($seqname='adodbseq', $startID=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)");> $startID -= 1; > return $this->Execute("insert into $seqname values($startID)");} 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;> /** > * Returns the maximum size of a MetaType C field. Because of the $this->_inited = true; > * database design, sqlite places no limits on the size of data inserted $this->fields = array(); > * if ($queryID) { > * @return int $this->_currentRow = 0; > */ $this->EOF = !$this->_fetch(); > function charMax() @$this->_initrs(); > { } else { > return ADODB_STRINGMAX_NOLIMIT; $this->_numOfRows = 0; > } $this->_numOfFields = 0; > $this->EOF = true; > /** } > * 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 $this->_queryID; > * } > * @return int > */ > function textMax() function FetchField($fieldOffset = -1) > { { > return ADODB_STRINGMAX_NOLIMIT; $fld = new ADOFieldObject; > } $fld->name = $this->_queryID->columnName($fieldOffset); > $fld->type = 'VARCHAR'; > /** $fld->max_length = -1; > * Converts a date to a month only field and pads it to 2 characters return $fld; > * } > * This uses the more efficient strftime native function to process > * function _initrs() > * @param string $fld The name of the field to process { > * $this->_numOfFields = $this->_queryID->numColumns(); > * @return string The SQL Statement > */ } > function month($fld) > { function Fields($colname) > return "strftime('%m',$fld)"; { > } if ($this->fetchMode != SQLITE3_NUM) { > return $this->fields[$colname]; > /** } > * Converts a date to a day only field and pads it to 2 characters if (!$this->bind) { > * $this->bind = array(); > * This uses the more efficient strftime native function to process for ($i=0; $i < $this->_numOfFields; $i++) { > * $o = $this->FetchField($i); > * @param string $fld The name of the field to process $this->bind[strtoupper($o->name)] = $i; > * } > * @return string The SQL Statement } > */ > function day($fld) { return $this->fields[$this->bind[strtoupper($colname)]]; > return "strftime('%d',$fld)"; } > } > function _seek($row) > /** { > * Converts a date to a year only field // sqlite3 does not implement seek > * if ($this->debug) { > * This uses the more efficient strftime native function to process ADOConnection::outp("SQLite3 does not implement seek"); > * } > * @param string $fld The name of the field to process return false; > * } > * @return string The SQL Statement > */ function _fetch($ignore_fields=false) > function year($fld) { > { $this->fields = $this->_queryID->fetchArray($this->fetchMode); > return "strftime('%Y',$fld)"; return !empty($this->fields); > } } > > /** function _close() > * SQLite update for blob { > * } > * SQLite must be a fully prepared statement (all variables must be bound), > * so $where can either be an array (array params) or a string that we will } > * do our best to unpack and turn into a prepared statement. > * > * @param string $table > * @param string $column > * @param string $val Blob value to set > * @param mixed $where An array of parameters (key => value pairs), > * or a string (where clause). > * @param string $blobtype ignored > * > * @return bool success > */ > function updateBlob($table, $column, $val, $where, $blobtype = 'BLOB') > { > if (is_array($where)) { > // We were passed a set of key=>value pairs > $params = $where; > } else { > // Given a where clause string, we have to disassemble the > // statements into keys and values > $params = array(); > $temp = preg_split('/(where|and)/i', $where); > $where = array_filter($temp); > > foreach ($where as $wValue) { > $wTemp = preg_split('/[= \']+/', $wValue); > $wTemp = array_filter($wTemp); > $wTemp = array_values($wTemp); > $params[$wTemp[0]] = $wTemp[1]; > } > } > > $paramWhere = array(); > foreach ($params as $bindKey => $bindValue) { > $paramWhere[] = $bindKey . '=?'; > } > > $sql = "UPDATE $table SET $column=? WHERE " > . implode(' AND ', $paramWhere); > > // Prepare the statement > $stmt = $this->_connectionID->prepare($sql); > > // Set the first bind value equal to value we want to update > if (!$stmt->bindValue(1, $val, SQLITE3_BLOB)) { > return false; > } > > // Build as many keys as available > $bindIndex = 2; > foreach ($params as $bindValue) { > if (is_integer($bindValue) || is_bool($bindValue) || is_float($bindValue)) { > $type = SQLITE3_NUM; > } elseif (is_object($bindValue)) { > // Assume a blob, this should never appear in > // the binding for a where statement anyway > $type = SQLITE3_BLOB; > } else { > $type = SQLITE3_TEXT; > } > > if (!$stmt->bindValue($bindIndex, $bindValue, $type)) { > return false; > } > > $bindIndex++; > } > > // Now execute the update. NB this is SQLite execute, not ADOdb > $ok = $stmt->execute(); > return is_object($ok); > } > > /** > * SQLite update for blob from a file > * > * @param string $table > * @param string $column > * @param string $path Filename containing blob data > * @param mixed $where {@see updateBlob()} > * @param string $blobtype ignored > * > * @return bool success > */ > function updateBlobFile($table, $column, $path, $where, $blobtype = 'BLOB') > { > if (!file_exists($path)) { > return false; > } > > // Read file information > $fileContents = file_get_contents($path); > if ($fileContents === false) > // Distinguish between an empty file and failure > return false; > > return $this->updateBlob($table, $column, $fileContents, $where, $blobtype); > } >> /** @var SQLite3Result */ > var $_queryID; > > /** @noinspection PhpMissingParentConstructorInspection */<