[Summary view]

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_sqlite3 extends ADOConnection {
  24  	 var $databaseType = "sqlite3";
  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  	 	 $version = SQLite3::version();
  43  	 	 $arr['version'] = $version['versionString'];
  44  	 	 $arr['description'] = 'SQLite 3';
  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 $this->_connectionID->lastInsertRowID();
 141  	 }
 142  
 143  	function _affectedrows()
 144  	 {
 145  	 	 return $this->_connectionID->changes();
 146  	 }
 147  
 148  	function ErrorMsg()
 149   	 {
 150  	 	 if ($this->_logsql) {
 151  	 	 	 return $this->_errorMsg;
 152  	 	 }
 153  	 	 return ($this->_errorNo) ? $this->ErrorNo() : ''; //**tochange?
 154  	 }
 155  
 156  	 function ErrorNo()
 157  	 {
 158  	 	 return $this->_connectionID->lastErrorCode(); //**tochange??
 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  	 	 $this->_connectionID->createFunction('adodb_date', 'adodb_date', 1);
 171  	 	 $this->_connectionID->createFunction('adodb_date2', 'adodb_date2', 2);
 172  	 }
 173  
 174  
 175  	 // returns true or false
 176  	 function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
 177  	 {
 178  	 	 if (empty($argHostname) && $argDatabasename) {
 179  	 	 	 $argHostname = $argDatabasename;
 180  	 	 }
 181  	 	 $this->_connectionID = new SQLite3($argHostname);
 182  	 	 $this->_createFunctions();
 183  
 184  	 	 return true;
 185  	 }
 186  
 187  	 // returns true or false
 188  	 function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
 189  	 {
 190  	 	 // There's no permanent connect in SQLite3
 191  	 	 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabasename);
 192  	 }
 193  
 194  	 // returns query ID if successful, otherwise false
 195  	 function _query($sql,$inputarr=false)
 196  	 {
 197  	 	 $rez = $this->_connectionID->query($sql);
 198  	 	 if ($rez === false) {
 199  	 	 	 $this->_errorNo = $this->_connectionID->lastErrorCode();
 200  	 	 }
 201  	 	 // If no data was returned, we don't need to create a real recordset
 202  	 	 elseif ($rez->numColumns() == 0) {
 203  	 	 	 $rez->finalize();
 204  	 	 	 $rez = true;
 205  	 	 }
 206  
 207  	 	 return $rez;
 208  	 }
 209  
 210  	 function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 211  	 {
 212  	 	 $nrows = (int) $nrows;
 213  	 	 $offset = (int) $offset;
 214  	 	 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 215  	 	 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 216  	 	 if ($secs2cache) {
 217  	 	 	 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 218  	 	 } else {
 219  	 	 	 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 220  	 	 }
 221  
 222  	 	 return $rs;
 223  	 }
 224  
 225  	 /*
 226  	 	 This algorithm is not very efficient, but works even if table locking
 227  	 	 is not available.
 228  
 229  	 	 Will return false if unable to generate an ID after $MAXLOOPS attempts.
 230  	 */
 231  	 var $_genSeqSQL = "create table %s (id integer)";
 232  
 233  	function GenID($seq='adodbseq',$start=1)
 234  	 {
 235  	 	 // if you have to modify the parameter below, your database is overloaded,
 236  	 	 // or you need to implement generation of id's yourself!
 237  	 	 $MAXLOOPS = 100;
 238  	 	 //$this->debug=1;
 239  	 	 while (--$MAXLOOPS>=0) {
 240  	 	 	 @($num = $this->GetOne("select id from $seq"));
 241  	 	 	 if ($num === false) {
 242  	 	 	 	 $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 243  	 	 	 	 $start -= 1;
 244  	 	 	 	 $num = '0';
 245  	 	 	 	 $ok = $this->Execute("insert into $seq values($start)");
 246  	 	 	 	 if (!$ok) {
 247  	 	 	 	 	 return false;
 248  	 	 	 	 }
 249  	 	 	 }
 250  	 	 	 $this->Execute("update $seq set id=id+1 where id=$num");
 251  
 252  	 	 	 if ($this->affected_rows() > 0) {
 253  	 	 	 	 $num += 1;
 254  	 	 	 	 $this->genID = $num;
 255  	 	 	 	 return $num;
 256  	 	 	 }
 257  	 	 }
 258  	 	 if ($fn = $this->raiseErrorFn) {
 259  	 	 	 $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 260  	 	 }
 261  	 	 return false;
 262  	 }
 263  
 264  	function CreateSequence($seqname='adodbseq',$start=1)
 265  	 {
 266  	 	 if (empty($this->_genSeqSQL)) {
 267  	 	 	 return false;
 268  	 	 }
 269  	 	 $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 270  	 	 if (!$ok) {
 271  	 	 	 return false;
 272  	 	 }
 273  	 	 $start -= 1;
 274  	 	 return $this->Execute("insert into $seqname values($start)");
 275  	 }
 276  
 277  	 var $_dropSeqSQL = 'drop table %s';
 278  	function DropSequence($seqname = 'adodbseq')
 279  	 {
 280  	 	 if (empty($this->_dropSeqSQL)) {
 281  	 	 	 return false;
 282  	 	 }
 283  	 	 return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 284  	 }
 285  
 286  	 // returns true or false
 287  	function _close()
 288  	 {
 289  	 	 return $this->_connectionID->close();
 290  	 }
 291  
 292  	function MetaIndexes($table, $primary = FALSE, $owner = false)
 293  	 {
 294  	 	 $false = false;
 295  	 	 // save old fetch mode
 296  	 	 global $ADODB_FETCH_MODE;
 297  	 	 $save = $ADODB_FETCH_MODE;
 298  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 299  	 	 if ($this->fetchMode !== FALSE) {
 300  	 	 	 $savem = $this->SetFetchMode(FALSE);
 301  	 	 }
 302  	 	 $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
 303  	 	 $rs = $this->Execute($SQL);
 304  	 	 if (!is_object($rs)) {
 305  	 	 	 if (isset($savem)) {
 306  	 	 	 	 $this->SetFetchMode($savem);
 307  	 	 	 }
 308  	 	 	 $ADODB_FETCH_MODE = $save;
 309  	 	 	 return $false;
 310  	 	 }
 311  
 312  	 	 $indexes = array ();
 313  	 	 while ($row = $rs->FetchRow()) {
 314  	 	 	 if ($primary && preg_match("/primary/i",$row[1]) == 0) {
 315  	 	 	 	 continue;
 316  	 	 	 }
 317  	 	 	 if (!isset($indexes[$row[0]])) {
 318  	 	 	 	 $indexes[$row[0]] = array(
 319  	 	 	 	 	 'unique' => preg_match("/unique/i",$row[1]),
 320  	 	 	 	 	 'columns' => array()
 321  	 	 	 	 );
 322  	 	 	 }
 323  	 	 	 /**
 324  	 	 	  * There must be a more elegant way of doing this,
 325  	 	 	  * the index elements appear in the SQL statement
 326  	 	 	  * in cols[1] between parentheses
 327  	 	 	  * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 328  	 	 	  */
 329  	 	 	 $cols = explode("(",$row[1]);
 330  	 	 	 $cols = explode(")",$cols[1]);
 331  	 	 	 array_pop($cols);
 332  	 	 	 $indexes[$row[0]]['columns'] = $cols;
 333  	 	 }
 334  	 	 if (isset($savem)) {
 335  	 	 	 $this->SetFetchMode($savem);
 336  	 	 	 $ADODB_FETCH_MODE = $save;
 337  	 	 }
 338  	 	 return $indexes;
 339  	 }
 340  
 341  }
 342  
 343  /*--------------------------------------------------------------------------------------
 344  	 	 Class Name: Recordset
 345  --------------------------------------------------------------------------------------*/
 346  
 347  class ADORecordset_sqlite3 extends ADORecordSet {
 348  
 349  	 var $databaseType = "sqlite3";
 350  	 var $bind = false;
 351  
 352  	function __construct($queryID,$mode=false)
 353  	 {
 354  
 355  	 	 if ($mode === false) {
 356  	 	 	 global $ADODB_FETCH_MODE;
 357  	 	 	 $mode = $ADODB_FETCH_MODE;
 358  	 	 }
 359  	 	 switch($mode) {
 360  	 	 	 case ADODB_FETCH_NUM:
 361  	 	 	 	 $this->fetchMode = SQLITE3_NUM;
 362  	 	 	 	 break;
 363  	 	 	 case ADODB_FETCH_ASSOC:
 364  	 	 	 	 $this->fetchMode = SQLITE3_ASSOC;
 365  	 	 	 	 break;
 366  	 	 	 default:
 367  	 	 	 	 $this->fetchMode = SQLITE3_BOTH;
 368  	 	 	 	 break;
 369  	 	 }
 370  	 	 $this->adodbFetchMode = $mode;
 371  
 372  	 	 $this->_queryID = $queryID;
 373  
 374  	 	 $this->_inited = true;
 375  	 	 $this->fields = array();
 376  	 	 if ($queryID) {
 377  	 	 	 $this->_currentRow = 0;
 378  	 	 	 $this->EOF = !$this->_fetch();
 379  	 	 	 @$this->_initrs();
 380  	 	 } else {
 381  	 	 	 $this->_numOfRows = 0;
 382  	 	 	 $this->_numOfFields = 0;
 383  	 	 	 $this->EOF = true;
 384  	 	 }
 385  
 386  	 	 return $this->_queryID;
 387  	 }
 388  
 389  
 390  	function FetchField($fieldOffset = -1)
 391  	 {
 392  	 	 $fld = new ADOFieldObject;
 393  	 	 $fld->name = $this->_queryID->columnName($fieldOffset);
 394  	 	 $fld->type = 'VARCHAR';
 395  	 	 $fld->max_length = -1;
 396  	 	 return $fld;
 397  	 }
 398  
 399  	function _initrs()
 400  	 {
 401  	 	 $this->_numOfFields = $this->_queryID->numColumns();
 402  
 403  	 }
 404  
 405  	function Fields($colname)
 406  	 {
 407  	 	 if ($this->fetchMode != SQLITE3_NUM) {
 408  	 	 	 return $this->fields[$colname];
 409  	 	 }
 410  	 	 if (!$this->bind) {
 411  	 	 	 $this->bind = array();
 412  	 	 	 for ($i=0; $i < $this->_numOfFields; $i++) {
 413  	 	 	 	 $o = $this->FetchField($i);
 414  	 	 	 	 $this->bind[strtoupper($o->name)] = $i;
 415  	 	 	 }
 416  	 	 }
 417  
 418  	 	 return $this->fields[$this->bind[strtoupper($colname)]];
 419  	 }
 420  
 421  	function _seek($row)
 422  	 {
 423  	 	 // sqlite3 does not implement seek
 424  	 	 if ($this->debug) {
 425  	 	 	 ADOConnection::outp("SQLite3 does not implement seek");
 426  	 	 }
 427  	 	 return false;
 428  	 }
 429  
 430  	function _fetch($ignore_fields=false)
 431  	 {
 432  	 	 $this->fields = $this->_queryID->fetchArray($this->fetchMode);
 433  	 	 return !empty($this->fields);
 434  	 }
 435  
 436  	function _close()
 437  	 {
 438  	 }
 439  
 440  }