3 @version v5.20.2 27-Dec-2015
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.
10 Latest version is available at http://adodb.sourceforge.net
12 SQLite info: http://www.hwaci.com/sw/sqlite/
16 1. Place this in adodb/drivers
17 2. Rename the file, remove the .txt prefix.
20 // security - hide paths
21 if (!defined('ADODB_DIR')) die();
23 class ADODB_sqlite
extends ADOConnection
{
24 var $databaseType = "sqlite";
25 var $replaceQuote = "''"; // string to use to replace quotes
26 var $concat_operator='||';
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'";
36 function __construct()
42 $arr['version'] = sqlite_libversion();
43 $arr['description'] = 'SQLite ';
44 $arr['encoding'] = sqlite_libencoding();
50 if ($this->transOff
) {
53 $ret = $this->Execute("BEGIN TRANSACTION");
58 function CommitTrans($ok=true)
60 if ($this->transOff
) {
64 return $this->RollbackTrans();
66 $ret = $this->Execute("COMMIT");
67 if ($this->transCnt
> 0) {
73 function RollbackTrans()
75 if ($this->transOff
) {
78 $ret = $this->Execute("ROLLBACK");
79 if ($this->transCnt
> 0) {
86 function MetaColumns($table, $normalize=true)
88 global $ADODB_FETCH_MODE;
90 $save = $ADODB_FETCH_MODE;
91 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC
;
92 if ($this->fetchMode
!== false) {
93 $savem = $this->SetFetchMode(false);
95 $rs = $this->Execute("PRAGMA table_info('$table')");
97 $this->SetFetchMode($savem);
100 $ADODB_FETCH_MODE = $save;
104 while ($r = $rs->FetchRow()) {
105 $type = explode('(',$r['type']);
107 if (sizeof($type)==2) {
108 $size = trim($type[1],')');
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'];
118 if (isset($r['pk']) && $r['pk']) {
121 if ($save == ADODB_FETCH_NUM
) {
124 $arr[strtoupper($fld->name
)] = $fld;
128 $ADODB_FETCH_MODE = $save;
132 function _init($parentDriver)
134 $parentDriver->hasTransactions
= false;
135 $parentDriver->hasInsertID
= true;
140 return sqlite_last_insert_rowid($this->_connectionID
);
143 function _affectedrows()
145 return sqlite_changes($this->_connectionID
);
150 if ($this->_logsql
) {
151 return $this->_errorMsg
;
153 return ($this->_errorNo
) ?
sqlite_error_string($this->_errorNo
) : '';
158 return $this->_errorNo
;
161 function SQLDate($fmt, $col=false)
163 $fmt = $this->qstr($fmt);
164 return ($col) ?
"adodb_date2($fmt,$col)" : "adodb_date($fmt)";
168 function _createFunctions()
170 @sqlite_create_function
($this->_connectionID
, 'adodb_date', 'adodb_date', 1);
171 @sqlite_create_function
($this->_connectionID
, 'adodb_date2', 'adodb_date2', 2);
175 // returns true or false
176 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
178 if (!function_exists('sqlite_open')) {
181 if (empty($argHostname) && $argDatabasename) {
182 $argHostname = $argDatabasename;
185 $this->_connectionID
= sqlite_open($argHostname);
186 if ($this->_connectionID
=== false) {
189 $this->_createFunctions();
193 // returns true or false
194 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
196 if (!function_exists('sqlite_open')) {
199 if (empty($argHostname) && $argDatabasename) {
200 $argHostname = $argDatabasename;
203 $this->_connectionID
= sqlite_popen($argHostname);
204 if ($this->_connectionID
=== false) {
207 $this->_createFunctions();
211 // returns query ID if successful, otherwise false
212 function _query($sql,$inputarr=false)
214 $rez = sqlite_query($sql,$this->_connectionID
);
216 $this->_errorNo
= sqlite_last_error($this->_connectionID
);
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) {
227 function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
229 $offsetStr = ($offset >= 0) ?
" OFFSET $offset" : '';
230 $limitStr = ($nrows >= 0) ?
" LIMIT $nrows" : ($offset >= 0 ?
' LIMIT 999999999' : '');
232 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
234 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
241 This algorithm is not very efficient, but works even if table locking
244 Will return false if unable to generate an ID after $MAXLOOPS attempts.
246 var $_genSeqSQL = "create table %s (id integer)";
248 function GenID($seq='adodbseq',$start=1)
250 // if you have to modify the parameter below, your database is overloaded,
251 // or you need to implement generation of id's yourself!
254 while (--$MAXLOOPS>=0) {
255 @($num = $this->GetOne("select id from $seq"));
256 if ($num === false) {
257 $this->Execute(sprintf($this->_genSeqSQL
,$seq));
260 $ok = $this->Execute("insert into $seq values($start)");
265 $this->Execute("update $seq set id=id+1 where id=$num");
267 if ($this->affected_rows() > 0) {
273 if ($fn = $this->raiseErrorFn
) {
274 $fn($this->databaseType
,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
279 function CreateSequence($seqname='adodbseq',$start=1)
281 if (empty($this->_genSeqSQL
)) {
284 $ok = $this->Execute(sprintf($this->_genSeqSQL
,$seqname));
289 return $this->Execute("insert into $seqname values($start)");
292 var $_dropSeqSQL = 'drop table %s';
293 function DropSequence($seqname = 'adodbseq')
295 if (empty($this->_dropSeqSQL
)) {
298 return $this->Execute(sprintf($this->_dropSeqSQL
,$seqname));
301 // returns true or false
304 return @sqlite_close
($this->_connectionID
);
307 function MetaIndexes($table, $primary = FALSE, $owner = false)
310 // save old fetch mode
311 global $ADODB_FETCH_MODE;
312 $save = $ADODB_FETCH_MODE;
313 $ADODB_FETCH_MODE = ADODB_FETCH_NUM
;
314 if ($this->fetchMode
!== FALSE) {
315 $savem = $this->SetFetchMode(FALSE);
317 $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
318 $rs = $this->Execute($SQL);
319 if (!is_object($rs)) {
321 $this->SetFetchMode($savem);
323 $ADODB_FETCH_MODE = $save;
328 while ($row = $rs->FetchRow()) {
329 if ($primary && preg_match("/primary/i",$row[1]) == 0) {
332 if (!isset($indexes[$row[0]])) {
333 $indexes[$row[0]] = array(
334 'unique' => preg_match("/unique/i",$row[1]),
339 * There must be a more elegant way of doing this,
340 * the index elements appear in the SQL statement
341 * in cols[1] between parentheses
342 * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
344 $cols = explode("(",$row[1]);
345 $cols = explode(")",$cols[1]);
347 $indexes[$row[0]]['columns'] = $cols;
350 $this->SetFetchMode($savem);
351 $ADODB_FETCH_MODE = $save;
358 /*--------------------------------------------------------------------------------------
359 Class Name: Recordset
360 --------------------------------------------------------------------------------------*/
362 class ADORecordset_sqlite
extends ADORecordSet
{
364 var $databaseType = "sqlite";
367 function __construct($queryID,$mode=false)
370 if ($mode === false) {
371 global $ADODB_FETCH_MODE;
372 $mode = $ADODB_FETCH_MODE;
375 case ADODB_FETCH_NUM
:
376 $this->fetchMode
= SQLITE_NUM
;
378 case ADODB_FETCH_ASSOC
:
379 $this->fetchMode
= SQLITE_ASSOC
;
382 $this->fetchMode
= SQLITE_BOTH
;
385 $this->adodbFetchMode
= $mode;
387 $this->_queryID
= $queryID;
389 $this->_inited
= true;
390 $this->fields
= array();
392 $this->_currentRow
= 0;
393 $this->EOF
= !$this->_fetch();
396 $this->_numOfRows
= 0;
397 $this->_numOfFields
= 0;
401 return $this->_queryID
;
405 function FetchField($fieldOffset = -1)
407 $fld = new ADOFieldObject
;
408 $fld->name
= sqlite_field_name($this->_queryID
, $fieldOffset);
409 $fld->type
= 'VARCHAR';
410 $fld->max_length
= -1;
416 $this->_numOfRows
= @sqlite_num_rows
($this->_queryID
);
417 $this->_numOfFields
= @sqlite_num_fields
($this->_queryID
);
420 function Fields($colname)
422 if ($this->fetchMode
!= SQLITE_NUM
) {
423 return $this->fields
[$colname];
426 $this->bind
= array();
427 for ($i=0; $i < $this->_numOfFields
; $i++
) {
428 $o = $this->FetchField($i);
429 $this->bind
[strtoupper($o->name
)] = $i;
433 return $this->fields
[$this->bind
[strtoupper($colname)]];
438 return sqlite_seek($this->_queryID
, $row);
441 function _fetch($ignore_fields=false)
443 $this->fields
= @sqlite_fetch_array
($this->_queryID
,$this->fetchMode
);
444 return !empty($this->fields
);