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