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
20 var $databaseType = "sqlite";
21 var $replaceQuote = "''"; // string to use to replace quotes
22 var $concat_operator = '||';
25 var $hasInsertID = true; /// supports autoincrement ID?
26 var $hasAffectedRows = true; /// supports affected rows for update/delete?
27 var $metaTablesSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
28 var $sysDate = "adodb_date('Y-m-d')";
29 var $sysTimeStamp = "adodb_date('Y-m-d H:i:s')";
30 var $fmtTimeStamp = "'Y-m-d H:i:s'";
32 function ADODB_sqlite()
40 case 'sysDate': return "'".date($this->fmtDate)."'";
41 case 'sysTimeStamp' : return "'".date($this->sysTimeStamp)."'";
47 $arr['version'] = sqlite_libversion();
48 $arr['description'] = 'SQLite ';
49 $arr['encoding'] = sqlite_libencoding();
55 if ($this->transOff) return true;
56 $ret = $this->Execute("BEGIN TRANSACTION");
61 function CommitTrans($ok = true)
63 if ($this->transOff) return true;
64 if (!$ok) return $this->RollbackTrans();
65 $ret = $this->Execute("COMMIT");
66 if ($this->transCnt > 0) $this->transCnt -= 1;
70 function RollbackTrans()
72 if ($this->transOff) return true;
73 $ret = $this->Execute("ROLLBACK");
74 if ($this->transCnt > 0) $this->transCnt -= 1;
80 return sqlite_last_insert_rowid($this->_connectionID);
83 function _affectedrows()
85 return sqlite_changes($this->_connectionID);
90 if ($this->_logsql) return $this->_errorMsg;
91 return ($this->_errorNo) ? sqlite_error_string($this->_errorNo) : '';
96 return $this->_errorNo;
99 function SQLDate($fmt, $col = false)
101 $fmt = $this->qstr($fmt);
102 return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
105 function &MetaColumns($tab)
107 global $ADODB_FETCH_MODE;
109 $rs = $this->Execute("select * from $tab limit 1");
110 if (!$rs) return false;
112 for ($i = 0, $max = $rs->_numOfFields; $i < $max; $i++) {
113 $fld =& $rs->FetchField($i);
114 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] =& $fld;
115 else $arr[strtoupper($fld->name)] =& $fld;
121 function _createFunctions()
123 @sqlite_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1);
124 @sqlite_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);
127 // returns true or false
128 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
130 if (!function_exists('sqlite_open')) return false;
132 $this->_connectionID = sqlite_open($argDatabasename);
133 if ($this->_connectionID === false) return false;
134 $this->_createFunctions();
138 // returns true or false
139 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
141 if (!function_exists('sqlite_popen')) return false;
143 $this->_connectionID = sqlite_popen($argDatabasename);
144 if ($this->_connectionID === false) return false;
145 $this->_createFunctions();
149 // returns query ID if successful, otherwise false
150 function _query($sql, $inputarr = false)
152 $rez = sqlite_query($sql, $this->_connectionID);
154 $this->_errorNo = sqlite_last_error($this->_connectionID);
160 function &SelectLimit($sql, $nrows = -1, $offset = -1, $inputarr = false, $secs2cache = 0)
162 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
163 $limitStr = ($nrows >= 0) ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
165 $rs =& $this->CacheExecute($secs2cache, $sql . "$limitStr$offsetStr", $inputarr);
167 $rs =& $this->Execute($sql . "$limitStr$offsetStr", $inputarr);
173 This algorithm is not very efficient, but works even if table locking
176 Will return false if unable to generate an ID after $MAXLOOPS attempts.
178 var $_genSeqSQL = "create table %s (id integer)";
180 function GenID($seq = 'adodbseq', $start = 1)
182 // if you have to modify the parameter below, your database is overloaded,
183 // or you need to implement generation of id's yourself!
186 while (--$MAXLOOPS >= 0) {
187 $num = $this->GetOne("select id from $seq");
188 if ($num === false) {
189 $this->Execute(sprintf($this->_genSeqSQL, $seq));
192 $ok = $this->Execute("insert into $seq values($start)");
193 if (!$ok) return false;
195 $this->Execute("update $seq set id=id+1 where id=$num");
197 if ($this->affected_rows() > 0) {
203 if ($fn = $this->raiseErrorFn) {
204 $fn($this->databaseType, 'GENID', -32000, "Unable to generate unique id after $MAXLOOPS attempts", $seq, $num);
209 function CreateSequence($seqname = 'adodbseq', $start = 1)
211 if (empty($this->_genSeqSQL)) return false;
212 $ok = $this->Execute(sprintf($this->_genSeqSQL, $seqname));
213 if (!$ok) return false;
215 return $this->Execute("insert into $seqname values($start)");
218 var $_dropSeqSQL = 'drop table %s';
220 function DropSequence($seqname)
222 if (empty($this->_dropSeqSQL)) return false;
223 return $this->Execute(sprintf($this->_dropSeqSQL, $seqname));
226 // returns true or false
229 return @sqlite_close($this->_connectionID);
235 /*--------------------------------------------------------------------------------------
236 Class Name: Recordset
237 --------------------------------------------------------------------------------------*/
239 class ADORecordset_sqlite extends ADORecordSet
242 var $databaseType = "sqlite";
245 function ADORecordset_sqlite($queryID, $mode = false)
248 if ($mode === false) {
249 global $ADODB_FETCH_MODE;
250 $mode = $ADODB_FETCH_MODE;
253 case ADODB_FETCH_NUM:
254 $this->fetchMode = SQLITE_NUM;
256 case ADODB_FETCH_ASSOC:
257 $this->fetchMode = SQLITE_ASSOC;
260 $this->fetchMode = SQLITE_BOTH;
264 $this->_queryID = $queryID;
266 $this->_inited = true;
267 $this->fields = array();
269 $this->_currentRow = 0;
270 $this->EOF = !$this->_fetch();
273 $this->_numOfRows = 0;
274 $this->_numOfFields = 0;
278 return $this->_queryID;
282 function &FetchField($fieldOffset = -1)
284 $fld = new ADOFieldObject;
285 $fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
286 $fld->type = 'VARCHAR';
287 $fld->max_length = -1;
293 $this->_numOfRows = @sqlite_num_rows($this->_queryID);
294 $this->_numOfFields = @sqlite_num_fields($this->_queryID);
297 function Fields($colname)
299 if ($this->fetchMode != SQLITE_NUM) return $this->fields[$colname];
301 $this->bind = array();
302 for ($i = 0; $i < $this->_numOfFields; $i++) {
303 $o = $this->FetchField($i);
304 $this->bind[strtoupper($o->name)] = $i;
308 return $this->fields[$this->bind[strtoupper($colname)]];
313 return sqlite_seek($this->_queryID, $row);
316 function _fetch($ignore_fields = false)
318 $this->fields = @sqlite_fetch_array($this->_queryID, $this->fetchMode);
319 return !empty($this->fields);