[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3  @version   v5.20.3  01-Jan-2016
   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.sourceforge.net
  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          $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 213          $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 214          if ($secs2cache) {
 215              $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 216          } else {
 217              $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 218          }
 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) {
 245                      return false;
 246                  }
 247              }
 248              $this->Execute("update $seq set id=id+1 where id=$num");
 249  
 250              if ($this->affected_rows() > 0) {
 251                  $num += 1;
 252                  $this->genID = $num;
 253                  return $num;
 254              }
 255          }
 256          if ($fn = $this->raiseErrorFn) {
 257              $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 258          }
 259          return false;
 260      }
 261  
 262  	function CreateSequence($seqname='adodbseq',$start=1)
 263      {
 264          if (empty($this->_genSeqSQL)) {
 265              return false;
 266          }
 267          $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 268          if (!$ok) {
 269              return false;
 270          }
 271          $start -= 1;
 272          return $this->Execute("insert into $seqname values($start)");
 273      }
 274  
 275      var $_dropSeqSQL = 'drop table %s';
 276  	function DropSequence($seqname = 'adodbseq')
 277      {
 278          if (empty($this->_dropSeqSQL)) {
 279              return false;
 280          }
 281          return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 282      }
 283  
 284      // returns true or false
 285  	function _close()
 286      {
 287          return $this->_connectionID->close();
 288      }
 289  
 290  	function MetaIndexes($table, $primary = FALSE, $owner = false)
 291      {
 292          $false = false;
 293          // save old fetch mode
 294          global $ADODB_FETCH_MODE;
 295          $save = $ADODB_FETCH_MODE;
 296          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 297          if ($this->fetchMode !== FALSE) {
 298              $savem = $this->SetFetchMode(FALSE);
 299          }
 300          $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
 301          $rs = $this->Execute($SQL);
 302          if (!is_object($rs)) {
 303              if (isset($savem)) {
 304                  $this->SetFetchMode($savem);
 305              }
 306              $ADODB_FETCH_MODE = $save;
 307              return $false;
 308          }
 309  
 310          $indexes = array ();
 311          while ($row = $rs->FetchRow()) {
 312              if ($primary && preg_match("/primary/i",$row[1]) == 0) {
 313                  continue;
 314              }
 315              if (!isset($indexes[$row[0]])) {
 316                  $indexes[$row[0]] = array(
 317                      'unique' => preg_match("/unique/i",$row[1]),
 318                      'columns' => array()
 319                  );
 320              }
 321              /**
 322               * There must be a more elegant way of doing this,
 323               * the index elements appear in the SQL statement
 324               * in cols[1] between parentheses
 325               * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 326               */
 327              $cols = explode("(",$row[1]);
 328              $cols = explode(")",$cols[1]);
 329              array_pop($cols);
 330              $indexes[$row[0]]['columns'] = $cols;
 331          }
 332          if (isset($savem)) {
 333              $this->SetFetchMode($savem);
 334              $ADODB_FETCH_MODE = $save;
 335          }
 336          return $indexes;
 337      }
 338  
 339  }
 340  
 341  /*--------------------------------------------------------------------------------------
 342          Class Name: Recordset
 343  --------------------------------------------------------------------------------------*/
 344  
 345  class ADORecordset_sqlite3 extends ADORecordSet {
 346  
 347      var $databaseType = "sqlite3";
 348      var $bind = false;
 349  
 350  	function __construct($queryID,$mode=false)
 351      {
 352  
 353          if ($mode === false) {
 354              global $ADODB_FETCH_MODE;
 355              $mode = $ADODB_FETCH_MODE;
 356          }
 357          switch($mode) {
 358              case ADODB_FETCH_NUM:
 359                  $this->fetchMode = SQLITE3_NUM;
 360                  break;
 361              case ADODB_FETCH_ASSOC:
 362                  $this->fetchMode = SQLITE3_ASSOC;
 363                  break;
 364              default:
 365                  $this->fetchMode = SQLITE3_BOTH;
 366                  break;
 367          }
 368          $this->adodbFetchMode = $mode;
 369  
 370          $this->_queryID = $queryID;
 371  
 372          $this->_inited = true;
 373          $this->fields = array();
 374          if ($queryID) {
 375              $this->_currentRow = 0;
 376              $this->EOF = !$this->_fetch();
 377              @$this->_initrs();
 378          } else {
 379              $this->_numOfRows = 0;
 380              $this->_numOfFields = 0;
 381              $this->EOF = true;
 382          }
 383  
 384          return $this->_queryID;
 385      }
 386  
 387  
 388  	function FetchField($fieldOffset = -1)
 389      {
 390          $fld = new ADOFieldObject;
 391          $fld->name = $this->_queryID->columnName($fieldOffset);
 392          $fld->type = 'VARCHAR';
 393          $fld->max_length = -1;
 394          return $fld;
 395      }
 396  
 397  	function _initrs()
 398      {
 399          $this->_numOfFields = $this->_queryID->numColumns();
 400  
 401      }
 402  
 403  	function Fields($colname)
 404      {
 405          if ($this->fetchMode != SQLITE3_NUM) {
 406              return $this->fields[$colname];
 407          }
 408          if (!$this->bind) {
 409              $this->bind = array();
 410              for ($i=0; $i < $this->_numOfFields; $i++) {
 411                  $o = $this->FetchField($i);
 412                  $this->bind[strtoupper($o->name)] = $i;
 413              }
 414          }
 415  
 416          return $this->fields[$this->bind[strtoupper($colname)]];
 417      }
 418  
 419  	function _seek($row)
 420      {
 421          // sqlite3 does not implement seek
 422          if ($this->debug) {
 423              ADOConnection::outp("SQLite3 does not implement seek");
 424          }
 425          return false;
 426      }
 427  
 428  	function _fetch($ignore_fields=false)
 429      {
 430          $this->fields = $this->_queryID->fetchArray($this->fetchMode);
 431          return !empty($this->fields);
 432      }
 433  
 434  	function _close()
 435      {
 436      }
 437  
 438  }