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