[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_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          $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 230          $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 231          if ($secs2cache) {
 232              $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 233          } else {
 234              $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 235          }
 236  
 237          return $rs;
 238      }
 239  
 240      /*
 241          This algorithm is not very efficient, but works even if table locking
 242          is not available.
 243  
 244          Will return false if unable to generate an ID after $MAXLOOPS attempts.
 245      */
 246      var $_genSeqSQL = "create table %s (id integer)";
 247  
 248  	function GenID($seq='adodbseq',$start=1)
 249      {
 250          // if you have to modify the parameter below, your database is overloaded,
 251          // or you need to implement generation of id's yourself!
 252          $MAXLOOPS = 100;
 253          //$this->debug=1;
 254          while (--$MAXLOOPS>=0) {
 255              @($num = $this->GetOne("select id from $seq"));
 256              if ($num === false) {
 257                  $this->Execute(sprintf($this->_genSeqSQL ,$seq));
 258                  $start -= 1;
 259                  $num = '0';
 260                  $ok = $this->Execute("insert into $seq values($start)");
 261                  if (!$ok) {
 262                      return false;
 263                  }
 264              }
 265              $this->Execute("update $seq set id=id+1 where id=$num");
 266  
 267              if ($this->affected_rows() > 0) {
 268                  $num += 1;
 269                  $this->genID = $num;
 270                  return $num;
 271              }
 272          }
 273          if ($fn = $this->raiseErrorFn) {
 274              $fn($this->databaseType,'GENID',-32000,"Unable to generate unique id after $MAXLOOPS attempts",$seq,$num);
 275          }
 276          return false;
 277      }
 278  
 279  	function CreateSequence($seqname='adodbseq',$start=1)
 280      {
 281          if (empty($this->_genSeqSQL)) {
 282              return false;
 283          }
 284          $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 285          if (!$ok) {
 286              return false;
 287          }
 288          $start -= 1;
 289          return $this->Execute("insert into $seqname values($start)");
 290      }
 291  
 292      var $_dropSeqSQL = 'drop table %s';
 293  	function DropSequence($seqname = 'adodbseq')
 294      {
 295          if (empty($this->_dropSeqSQL)) {
 296              return false;
 297          }
 298          return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 299      }
 300  
 301      // returns true or false
 302  	function _close()
 303      {
 304          return @sqlite_close($this->_connectionID);
 305      }
 306  
 307  	function MetaIndexes($table, $primary = FALSE, $owner = false)
 308      {
 309          $false = 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);
 316          }
 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)) {
 320              if (isset($savem)) {
 321                  $this->SetFetchMode($savem);
 322              }
 323              $ADODB_FETCH_MODE = $save;
 324              return $false;
 325          }
 326  
 327          $indexes = array ();
 328          while ($row = $rs->FetchRow()) {
 329              if ($primary && preg_match("/primary/i",$row[1]) == 0) {
 330                  continue;
 331              }
 332              if (!isset($indexes[$row[0]])) {
 333                  $indexes[$row[0]] = array(
 334                      'unique' => preg_match("/unique/i",$row[1]),
 335                      'columns' => array()
 336                  );
 337              }
 338              /**
 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)
 343               */
 344              $cols = explode("(",$row[1]);
 345              $cols = explode(")",$cols[1]);
 346              array_pop($cols);
 347              $indexes[$row[0]]['columns'] = $cols;
 348          }
 349          if (isset($savem)) {
 350              $this->SetFetchMode($savem);
 351              $ADODB_FETCH_MODE = $save;
 352          }
 353          return $indexes;
 354      }
 355  
 356  }
 357  
 358  /*--------------------------------------------------------------------------------------
 359          Class Name: Recordset
 360  --------------------------------------------------------------------------------------*/
 361  
 362  class ADORecordset_sqlite extends ADORecordSet {
 363  
 364      var $databaseType = "sqlite";
 365      var $bind = false;
 366  
 367  	function __construct($queryID,$mode=false)
 368      {
 369  
 370          if ($mode === false) {
 371              global $ADODB_FETCH_MODE;
 372              $mode = $ADODB_FETCH_MODE;
 373          }
 374          switch($mode) {
 375              case ADODB_FETCH_NUM:
 376                  $this->fetchMode = SQLITE_NUM;
 377                  break;
 378              case ADODB_FETCH_ASSOC:
 379                  $this->fetchMode = SQLITE_ASSOC;
 380                  break;
 381              default:
 382                  $this->fetchMode = SQLITE_BOTH;
 383                  break;
 384          }
 385          $this->adodbFetchMode = $mode;
 386  
 387          $this->_queryID = $queryID;
 388  
 389          $this->_inited = true;
 390          $this->fields = array();
 391          if ($queryID) {
 392              $this->_currentRow = 0;
 393              $this->EOF = !$this->_fetch();
 394              @$this->_initrs();
 395          } else {
 396              $this->_numOfRows = 0;
 397              $this->_numOfFields = 0;
 398              $this->EOF = true;
 399          }
 400  
 401          return $this->_queryID;
 402      }
 403  
 404  
 405  	function FetchField($fieldOffset = -1)
 406      {
 407          $fld = new ADOFieldObject;
 408          $fld->name = sqlite_field_name($this->_queryID, $fieldOffset);
 409          $fld->type = 'VARCHAR';
 410          $fld->max_length = -1;
 411          return $fld;
 412      }
 413  
 414  	function _initrs()
 415      {
 416          $this->_numOfRows = @sqlite_num_rows($this->_queryID);
 417          $this->_numOfFields = @sqlite_num_fields($this->_queryID);
 418      }
 419  
 420  	function Fields($colname)
 421      {
 422          if ($this->fetchMode != SQLITE_NUM) {
 423              return $this->fields[$colname];
 424          }
 425          if (!$this->bind) {
 426              $this->bind = array();
 427              for ($i=0; $i < $this->_numOfFields; $i++) {
 428                  $o = $this->FetchField($i);
 429                  $this->bind[strtoupper($o->name)] = $i;
 430              }
 431          }
 432  
 433          return $this->fields[$this->bind[strtoupper($colname)]];
 434      }
 435  
 436  	function _seek($row)
 437      {
 438          return sqlite_seek($this->_queryID, $row);
 439      }
 440  
 441  	function _fetch($ignore_fields=false)
 442      {
 443          $this->fields = @sqlite_fetch_array($this->_queryID,$this->fetchMode);
 444          return !empty($this->fields);
 445      }
 446  
 447  	function _close()
 448      {
 449      }
 450  
 451  }