<?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;


	}

}