3 V4.22 15 Apr 2004 (c) 2000-2004 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.
8 Latest version is available at http://php.weblogs.com/
10 SQLite info: http://www.hwaci.com/sw/sqlite/
14 1. Place this in adodb/drivers
15 2. Rename the file, remove the .txt prefix.
18 class ADODB_sqlite extends ADOConnection {
19 var $databaseType = "sqlite";
20 var $replaceQuote = "''"; // string to use to replace quotes
21 var $concat_operator='||';
24 var $hasInsertID = true; /// supports autoincrement ID?
25 var $hasAffectedRows = true; /// supports affected rows for update/delete?
26 var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
27 var $sysDate = "adodb_date('Y-m-d')";
28 var $sysTimeStamp = "adodb_date('Y-m-d H:i:s')";
29 var $fmtTimeStamp = "'Y-m-d H:i:s'";
31 function ADODB_sqlite()
39 case 'sysDate': return "'".date($this->fmtDate)."'";
40 case 'sysTimeStamp' : return "'".date($this->sysTimeStamp)."'";
46 $arr['version'] = sqlite_libversion();
47 $arr['description'] = 'SQLite ';
48 $arr['encoding'] = sqlite_libencoding();
54 if ($this->transOff) return true;
55 $ret = $this->Execute("BEGIN TRANSACTION");
60 function CommitTrans($ok=true)
62 if ($this->transOff) return true;
63 if (!$ok) return $this->RollbackTrans();
64 $ret = $this->Execute("COMMIT");
65 if ($this->transCnt>0)$this->transCnt -= 1;
69 function RollbackTrans()
71 if ($this->transOff) return true;
72 $ret = $this->Execute("ROLLBACK");
73 if ($this->transCnt>0)$this->transCnt -= 1;
79 return sqlite_last_insert_rowid($this->_connectionID);
82 function _affectedrows()
84 return sqlite_changes($this->_connectionID);
89 if ($this->_logsql) return $this->_errorMsg;
90 return ($this->_errorNo) ? sqlite_error_string($this->_errorNo) : '';
95 return $this->_errorNo;
98 function SQLDate($fmt, $col=false)
100 $fmt = $this->qstr($fmt);
101 return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
104 function &MetaColumns($tab)
106 global $ADODB_FETCH_MODE;
108 $rs = $this->Execute("select * from $tab limit 1");
109 if (!$rs) return false;
111 for ($i=0,$max=$rs->_numOfFields; $i < $max; $i++) {
112 $fld =& $rs->FetchField($i);
113 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] =& $fld;
114 else $arr[strtoupper($fld->name)] =& $fld;
120 function _createFunctions()
122 @sqlite_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1);
123 @sqlite_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);
126 // returns true or false
127 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
129 if (!function_exists('sqlite_open')) return false;
131 $this->_connectionID = sqlite_open($argDatabasename);
132 if ($this->_connectionID === false) return false;
133 $this->_createFunctions();
137 // returns true or false
138 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
140 if (!function_exists('sqlite_popen')) return false;
142 $this->_connectionID = sqlite_popen($argDatabasename);
143 if ($this->_connectionID === false) return false;
144 $this->_createFunctions();
148 // returns query ID if successful, otherwise false
149 function _query($sql,$inputarr=false)
151 $rez = sqlite_query($sql,$this->_connectionID);
153 $this->_errorNo = sqlite_last_error($this->_connectionID);
159 function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
161 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
162 $limitStr = ($nrows >= 0) ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
164 $rs =& $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
166 $rs =& $this->Execute($sql."$limitStr$offsetStr",$inputarr);
172 This algorithm is not very efficient, but works even if table locking
175 Will return false if unable to generate an ID after $MAXLOOPS attempts.
177 var $_genSeqSQL = "create table %s (id integer)";
179 function GenID($seq='adodbseq',$start=1)
181 // if you have to modify the parameter below, your database is overloaded,
182 // or you need to implement generation of id's yourself!
185 while (--$MAXLOOPS>=0) {
186 $num = $this->GetOne("select id from $seq");
187 if ($num === false) {
188 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
191 $ok = $this->Execute("insert into $seq values($start)");
192 if (!$ok) return false;
194 $this->Execute("update $seq set id=id+1 where id=$num");
196 if ($this->affected_rows() > 0) {
202 if ($fn = $this->raiseErrorFn) {
203 $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
208 function CreateSequence($seqname='adodbseq',$start=1)
210 if (empty($this->_genSeqSQL)) return false;
211 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
212 if (!$ok) return false;
214 return $this->Execute("insert into $seqname values($start)");
217 var $_dropSeqSQL = 'drop table %s';
218 function DropSequence($seqname)
220 if (empty($this->_dropSeqSQL)) return false;
221 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
224 // returns true or false
227 return @sqlite_close($this->_connectionID);
233 /*--------------------------------------------------------------------------------------
234 Class Name: Recordset
235 --------------------------------------------------------------------------------------*/
237 class ADORecordset_sqlite extends ADORecordSet {
239 var $databaseType = "sqlite";
242 function ADORecordset_sqlite($queryID,$mode=false)
245 if ($mode === false) {
246 global $ADODB_FETCH_MODE;
247 $mode = $ADODB_FETCH_MODE;
250 case ADODB_FETCH_NUM: $this->fetchMode = SQLITE_NUM; break;
251 case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE_ASSOC; break;
252 default: $this->fetchMode = SQLITE_BOTH; break;
255 $this->_queryID = $queryID;
257 $this->_inited = true;
258 $this->fields = array();
260 $this->_currentRow = 0;
261 $this->EOF = !$this->_fetch();
264 $this->_numOfRows = 0;
265 $this->_numOfFields = 0;
269 return $this->_queryID;
273 function &FetchField($fieldOffset = -1)
275 $fld = new ADOFieldObject;
276 $fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
277 $fld->type = 'VARCHAR';
278 $fld->max_length = -1;
284 $this->_numOfRows = @sqlite_num_rows($this->_queryID);
285 $this->_numOfFields = @sqlite_num_fields($this->_queryID);
288 function Fields($colname)
290 if ($this->fetchMode != SQLITE_NUM) return $this->fields[$colname];
292 $this->bind = array();
293 for ($i=0; $i < $this->_numOfFields; $i++) {
294 $o = $this->FetchField($i);
295 $this->bind[strtoupper($o->name)] = $i;
299 return $this->fields[$this->bind[strtoupper($colname)]];
304 return sqlite_seek($this->_queryID, $row);
307 function _fetch($ignore_fields=false)
309 $this->fields = @sqlite_fetch_array($this->_queryID,$this->fetchMode);
310 return !empty($this->fields);