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?>