1<?php
2/*
3@version   v5.21.0  2021-02-27
4@copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
5@copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
6  Released under both BSD license and Lesser GPL library license.
7  Whenever there is any discrepancy between the two licenses,
8  the BSD license will take precedence.
9
10  Latest version is available at https://adodb.org/
11
12  SQLite info: http://www.hwaci.com/sw/sqlite/
13
14  Install Instructions:
15  ====================
16  1. Place this in adodb/drivers
17  2. Rename the file, remove the .txt prefix.
18*/
19
20// security - hide paths
21if (!defined('ADODB_DIR')) die();
22
23class ADODB_sqlite extends ADOConnection {
24	var $databaseType = "sqlite";
25	var $dataProvider = "sqlite";
26	var $replaceQuote = "''"; // string to use to replace quotes
27	var $concat_operator='||';
28	var $_errorNo = 0;
29	var $hasLimit = true;
30	var $hasInsertID = true; 		/// supports autoincrement ID?
31	var $hasAffectedRows = true; 	/// supports affected rows for update/delete?
32	var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
33	var $sysDate = "adodb_date('Y-m-d')";
34	var $sysTimeStamp = "adodb_date('Y-m-d H:i:s')";
35	var $fmtTimeStamp = "'Y-m-d H:i:s'";
36
37	function ServerInfo()
38	{
39		$arr['version'] = sqlite_libversion();
40		$arr['description'] = 'SQLite ';
41		$arr['encoding'] = sqlite_libencoding();
42		return $arr;
43	}
44
45	function BeginTrans()
46	{
47		if ($this->transOff) {
48			return true;
49		}
50		$ret = $this->Execute("BEGIN TRANSACTION");
51		$this->transCnt += 1;
52		return true;
53	}
54
55	function CommitTrans($ok=true)
56	{
57		if ($this->transOff) {
58			return true;
59		}
60		if (!$ok) {
61			return $this->RollbackTrans();
62		}
63		$ret = $this->Execute("COMMIT");
64		if ($this->transCnt > 0) {
65			$this->transCnt -= 1;
66		}
67		return !empty($ret);
68	}
69
70	function RollbackTrans()
71	{
72		if ($this->transOff) {
73			return true;
74		}
75		$ret = $this->Execute("ROLLBACK");
76		if ($this->transCnt > 0) {
77			$this->transCnt -= 1;
78		}
79		return !empty($ret);
80	}
81
82	// mark newnham
83	function MetaColumns($table, $normalize=true)
84	{
85		global $ADODB_FETCH_MODE;
86		$false = false;
87		$save = $ADODB_FETCH_MODE;
88		$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
89		if ($this->fetchMode !== false) {
90			$savem = $this->SetFetchMode(false);
91		}
92		$rs = $this->Execute("PRAGMA table_info('$table')");
93		if (isset($savem)) {
94			$this->SetFetchMode($savem);
95		}
96		if (!$rs) {
97			$ADODB_FETCH_MODE = $save;
98			return $false;
99		}
100		$arr = array();
101		while ($r = $rs->FetchRow()) {
102			$type = explode('(',$r['type']);
103			$size = '';
104			if (sizeof($type)==2) {
105				$size = trim($type[1],')');
106			}
107			$fn = strtoupper($r['name']);
108			$fld = new ADOFieldObject;
109			$fld->name = $r['name'];
110			$fld->type = $type[0];
111			$fld->max_length = $size;
112			$fld->not_null = $r['notnull'];
113			$fld->default_value = $r['dflt_value'];
114			$fld->scale = 0;
115			if (isset($r['pk']) && $r['pk']) {
116				$fld->primary_key=1;
117			}
118			if ($save == ADODB_FETCH_NUM) {
119				$arr[] = $fld;
120			} else {
121				$arr[strtoupper($fld->name)] = $fld;
122			}
123		}
124		$rs->Close();
125		$ADODB_FETCH_MODE = $save;
126		return $arr;
127	}
128
129	function _init($parentDriver)
130	{
131		$parentDriver->hasTransactions = false;
132		$parentDriver->hasInsertID = true;
133	}
134
135	function _insertid()
136	{
137		return sqlite_last_insert_rowid($this->_connectionID);
138	}
139
140	function _affectedrows()
141	{
142		return sqlite_changes($this->_connectionID);
143	}
144
145	function ErrorMsg()
146 	{
147		if ($this->_logsql) {
148			return $this->_errorMsg;
149		}
150		return ($this->_errorNo) ? sqlite_error_string($this->_errorNo) : '';
151	}
152
153	function ErrorNo()
154	{
155		return $this->_errorNo;
156	}
157
158	function SQLDate($fmt, $col=false)
159	{
160		$fmt = $this->qstr($fmt);
161		return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
162	}
163
164
165	function _createFunctions()
166	{
167		@sqlite_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1);
168		@sqlite_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);
169	}
170
171
172	// returns true or false
173	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
174	{
175		if (!function_exists('sqlite_open')) {
176			return null;
177		}
178		if (empty($argHostname) && $argDatabasename) {
179			$argHostname = $argDatabasename;
180		}
181
182		$this->_connectionID = sqlite_open($argHostname);
183		if ($this->_connectionID === false) {
184			return false;
185		}
186		$this->_createFunctions();
187		return true;
188	}
189
190	// returns true or false
191	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
192	{
193		if (!function_exists('sqlite_open')) {
194			return null;
195		}
196		if (empty($argHostname) && $argDatabasename) {
197			$argHostname = $argDatabasename;
198		}
199
200		$this->_connectionID = sqlite_popen($argHostname);
201		if ($this->_connectionID === false) {
202			return false;
203		}
204		$this->_createFunctions();
205		return true;
206	}
207
208	// returns query ID if successful, otherwise false
209	function _query($sql,$inputarr=false)
210	{
211		$rez = sqlite_query($sql,$this->_connectionID);
212		if (!$rez) {
213			$this->_errorNo = sqlite_last_error($this->_connectionID);
214		}
215		// If no data was returned, we don't need to create a real recordset
216		// Note: this code is untested, as I don't have a sqlite2 setup available
217		elseif (sqlite_num_fields($rez) == 0) {
218			$rez = true;
219		}
220
221		return $rez;
222	}
223
224	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
225	{
226		$nrows = (int) $nrows;
227		$offset = (int) $offset;
228		$offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
229		$limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
230		if ($secs2cache) {
231			$rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
232		} else {
233			$rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
234		}
235
236		return $rs;
237	}
238
239	/*
240		This algorithm is not very efficient, but works even if table locking
241		is not available.
242
243		Will return false if unable to generate an ID after $MAXLOOPS attempts.
244	*/
245	var $_genSeqSQL = "create table %s (id integer)";
246
247	function GenID($seq='adodbseq',$start=1)
248	{
249		// if you have to modify the parameter below, your database is overloaded,
250		// or you need to implement generation of id's yourself!
251		$MAXLOOPS = 100;
252		//$this->debug=1;
253		while (--$MAXLOOPS>=0) {
254			@($num = $this->GetOne("select id from $seq"));
255			if ($num === false) {
256				$this->Execute(sprintf($this->_genSeqSQL ,$seq));
257				$start -= 1;
258				$num = '0';
259				$ok = $this->Execute("insert into $seq values($start)");
260				if (!$ok) {
261					return false;
262				}
263			}
264			$this->Execute("update $seq set id=id+1 where id=$num");
265
266			if ($this->affected_rows() > 0) {
267				$num += 1;
268				$this->genID = $num;
269				return $num;
270			}
271		}
272		if ($fn = $this->raiseErrorFn) {
273			$fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
274		}
275		return false;
276	}
277
278	function CreateSequence($seqname='adodbseq',$start=1)
279	{
280		if (empty($this->_genSeqSQL)) {
281			return false;
282		}
283		$ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
284		if (!$ok) {
285			return false;
286		}
287		$start -= 1;
288		return $this->Execute("insert into $seqname values($start)");
289	}
290
291	var $_dropSeqSQL = 'drop table %s';
292	function DropSequence($seqname = 'adodbseq')
293	{
294		if (empty($this->_dropSeqSQL)) {
295			return false;
296		}
297		return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
298	}
299
300	// returns true or false
301	function _close()
302	{
303		return @sqlite_close($this->_connectionID);
304	}
305
306	function MetaIndexes($table, $primary = FALSE, $owner = false)
307	{
308		$false = false;
309		// save old fetch mode
310		global $ADODB_FETCH_MODE;
311		$save = $ADODB_FETCH_MODE;
312		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
313		if ($this->fetchMode !== FALSE) {
314			$savem = $this->SetFetchMode(FALSE);
315		}
316		$SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
317		$rs = $this->Execute($SQL);
318		if (!is_object($rs)) {
319			if (isset($savem)) {
320				$this->SetFetchMode($savem);
321			}
322			$ADODB_FETCH_MODE = $save;
323			return $false;
324		}
325
326		$indexes = array ();
327		while ($row = $rs->FetchRow()) {
328			if ($primary && preg_match("/primary/i",$row[1]) == 0) {
329				continue;
330			}
331			if (!isset($indexes[$row[0]])) {
332				$indexes[$row[0]] = array(
333					'unique' => preg_match("/unique/i",$row[1]),
334					'columns' => array()
335				);
336			}
337			/**
338			 * There must be a more elegant way of doing this,
339			 * the index elements appear in the SQL statement
340			 * in cols[1] between parentheses
341			 * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
342			 */
343			$cols = explode("(",$row[1]);
344			$cols = explode(")",$cols[1]);
345			array_pop($cols);
346			$indexes[$row[0]]['columns'] = $cols;
347		}
348		if (isset($savem)) {
349			$this->SetFetchMode($savem);
350			$ADODB_FETCH_MODE = $save;
351		}
352		return $indexes;
353	}
354
355	/**
356	* Returns the maximum size of a MetaType C field. Because of the
357	* database design, sqlite places no limits on the size of data inserted
358	*
359	* @return int
360	*/
361	function charMax()
362	{
363		return ADODB_STRINGMAX_NOLIMIT;
364	}
365
366	/**
367	* Returns the maximum size of a MetaType X field. Because of the
368	* database design, sqlite places no limits on the size of data inserted
369	*
370	* @return int
371	*/
372	function textMax()
373	{
374		return ADODB_STRINGMAX_NOLIMIT;
375	}
376
377	/*
378	 * Converts a date to a month only field and pads it to 2 characters
379	 *
380	 * @param 	str		$fld	The name of the field to process
381	 * @return	str				The SQL Statement
382	 */
383	function month($fld)
384	{
385		$x = "strftime('%m',$fld)";
386
387		return $x;
388	}
389
390	/*
391	 * Converts a date to a day only field and pads it to 2 characters
392	 *
393	 * @param 	str		$fld	The name of the field to process
394	 * @return	str				The SQL Statement
395	 */
396	function day($fld) {
397		$x = "strftime('%d',$fld)";
398		return $x;
399	}
400
401	/*
402	 * Converts a date to a year only field
403	 *
404	 * @param 	str		$fld	The name of the field to process
405	 * @return	str				The SQL Statement
406	 */
407	function year($fld) {
408		$x = "strftime('%Y',$fld)";
409
410		return $x;
411	}
412}
413
414/*--------------------------------------------------------------------------------------
415		Class Name: Recordset
416--------------------------------------------------------------------------------------*/
417
418class ADORecordset_sqlite extends ADORecordSet {
419
420	var $databaseType = "sqlite";
421	var $bind = false;
422
423	function __construct($queryID,$mode=false)
424	{
425
426		if ($mode === false) {
427			global $ADODB_FETCH_MODE;
428			$mode = $ADODB_FETCH_MODE;
429		}
430		switch($mode) {
431			case ADODB_FETCH_NUM:
432				$this->fetchMode = SQLITE_NUM;
433				break;
434			case ADODB_FETCH_ASSOC:
435				$this->fetchMode = SQLITE_ASSOC;
436				break;
437			default:
438				$this->fetchMode = SQLITE_BOTH;
439				break;
440		}
441		$this->adodbFetchMode = $mode;
442
443		$this->_queryID = $queryID;
444
445		$this->_inited = true;
446		$this->fields = array();
447		if ($queryID) {
448			$this->_currentRow = 0;
449			$this->EOF = !$this->_fetch();
450			@$this->_initrs();
451		} else {
452			$this->_numOfRows = 0;
453			$this->_numOfFields = 0;
454			$this->EOF = true;
455		}
456
457		return $this->_queryID;
458	}
459
460
461	function FetchField($fieldOffset = -1)
462	{
463		$fld = new ADOFieldObject;
464		$fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
465		$fld->type = 'VARCHAR';
466		$fld->max_length = -1;
467		return $fld;
468	}
469
470	function _initrs()
471	{
472		$this->_numOfRows = @sqlite_num_rows($this->_queryID);
473		$this->_numOfFields = @sqlite_num_fields($this->_queryID);
474	}
475
476	function Fields($colname)
477	{
478		if ($this->fetchMode != SQLITE_NUM) {
479			return $this->fields[$colname];
480		}
481		if (!$this->bind) {
482			$this->bind = array();
483			for ($i=0; $i < $this->_numOfFields; $i++) {
484				$o = $this->FetchField($i);
485				$this->bind[strtoupper($o->name)] = $i;
486			}
487		}
488
489		return $this->fields[$this->bind[strtoupper($colname)]];
490	}
491
492	function _seek($row)
493	{
494		return sqlite_seek($this->_queryID, $row);
495	}
496
497	function _fetch($ignore_fields=false)
498	{
499		$this->fields = @sqlite_fetch_array($this->_queryID,$this->fetchMode);
500		return !empty($this->fields);
501	}
502
503	function _close()
504	{
505	}
506
507}
508