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