[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3  V5.19  23-Apr-2014  (c) 2000-2014 John Lim (jlim#natsoft.com). 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
  19  if (!defined('ADODB_DIR')) die();
  20  
  21  // class ADODB_sqlite extends ADOConnection { **change
  22  class ADODB_sqlite3 extends ADOConnection {
  23      //var $databaseType = "sqlite"; **change
  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 ADODB_sqlite3() **change
  37  	function ADODB_sqlite3()
  38      {
  39      }
  40  
  41  /*
  42    function __get($name)
  43    {
  44        switch($name) {
  45      case 'sysDate': return "'".date($this->fmtDate)."'";
  46      case 'sysTimeStamp' : return "'".date($this->sysTimeStamp)."'";
  47      }
  48    }*/
  49  
  50  	function ServerInfo()
  51      {
  52          $arr['version'] = $this->_connectionID->version(); //**tochange
  53          $arr['description'] = 'SQLite 3'; //**tochange
  54          //$arr['encoding'] = sqlite_libencoding();//**tochange
  55          return $arr;
  56      }
  57  
  58      function BeginTrans()
  59      {
  60           if ($this->transOff) return true;
  61           $ret = $this->Execute("BEGIN TRANSACTION");
  62           $this->transCnt += 1;
  63           return true;
  64      }
  65  
  66      function CommitTrans($ok=true)
  67      {
  68          if ($this->transOff) return true;
  69          if (!$ok) return $this->RollbackTrans();
  70          $ret = $this->Execute("COMMIT");
  71          if ($this->transCnt>0)$this->transCnt -= 1;
  72          return !empty($ret);
  73      }
  74  
  75      function RollbackTrans()
  76      {
  77          if ($this->transOff) return true;
  78          $ret = $this->Execute("ROLLBACK");
  79          if ($this->transCnt>0)$this->transCnt -= 1;
  80          return !empty($ret);
  81      }
  82  
  83      // mark newnham
  84      function MetaColumns($table, $normalize=true)
  85      {
  86        global $ADODB_FETCH_MODE;
  87        $false = false;
  88        $save = $ADODB_FETCH_MODE;
  89        $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
  90        if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
  91        $rs = $this->Execute("PRAGMA table_info('$table')");
  92        if (isset($savem)) $this->SetFetchMode($savem);
  93        if (!$rs) {
  94          $ADODB_FETCH_MODE = $save;
  95          return $false;
  96        }
  97        $arr = array();
  98        while ($r = $rs->FetchRow()) {
  99  
 100          $type = explode('(',$r['type']);
 101          $size = '';
 102          if (sizeof($type)==2)
 103          $size = trim($type[1],')');
 104          $fn = strtoupper($r['name']);
 105          $fld = new ADOFieldObject;
 106          $fld->name = $r['name'];
 107          $fld->type = $type[0];
 108          $fld->max_length = $size;
 109          $fld->not_null = $r['notnull'];
 110          $fld->default_value = $r['dflt_value'];
 111          $fld->scale = 0;
 112          if (isset($r['pk']) && $r['pk']) $fld->primary_key=1;
 113          if ($save == ADODB_FETCH_NUM) $arr[] = $fld;
 114          else $arr[strtoupper($fld->name)] = $fld;
 115        }
 116        $rs->Close();
 117        $ADODB_FETCH_MODE = $save;
 118        return $arr;
 119      }
 120  
 121      function _init($parentDriver)
 122      {
 123  
 124          $parentDriver->hasTransactions = false;
 125          $parentDriver->hasInsertID = true;
 126      }
 127  
 128      function _insertid()
 129      {
 130          //return sqlite_last_insert_rowid($this->_connectionID)->; //**change
 131          return $this->_connectionID->lastInsertRowID();
 132      }
 133  
 134      function _affectedrows()
 135      {
 136          return $this->_connectionID->changes();
 137         //return sqlite3_changes($this->_connectionID); //**tochange
 138      }
 139  
 140      function ErrorMsg()
 141       {
 142          if ($this->_logsql) return $this->_errorMsg;
 143  
 144          return ($this->_errorNo) ? $this->ErrorNo() : ''; //**tochange?
 145      }
 146  
 147      function ErrorNo()
 148      {
 149          return $this->_connectionID->lastErrorCode(); //**tochange??
 150      }
 151  
 152      function SQLDate($fmt, $col=false)
 153      {
 154          $fmt = $this->qstr($fmt);
 155          return ($col) ? "adodb_date2($fmt,$col)" : "adodb_date($fmt)";
 156      }
 157  
 158  
 159      function _createFunctions()
 160      {
 161          //@sqlite3_create_function($this->_connectionID, 'adodb_date', 'adodb_date', 1); *change
 162          $this->_connectionID->createFunction('adodb_date', 'adodb_date', 1);
 163  
 164          //@sqlite3_create_function($this->_connectionID, 'adodb_date2', 'adodb_date2', 2);**change
 165          $this->_connectionID->createFunction('adodb_date2', 'adodb_date2', 2);
 166      }
 167  
 168  
 169      // returns true or false
 170      function _connect($argHostname, $argUsername, $argPassword, $argDatabasename) //**tochange: all the function need to be changed, just hacks for the moment
 171      {
 172          if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 173          $this->_connectionID = new SQLite3($argDatabasename);
 174          $this->_createFunctions();
 175  
 176          return true; // hack
 177          /*
 178          if (!function_exists('sqlite_open')) return null;
 179          if (empty($argHostname) && $argDatabasename) $argHostname = $argDatabasename;
 180  
 181          $this->_connectionID = sqlite_open($argHostname);
 182          if ($this->_connectionID === false) return false;
 183          $this->_createFunctions();
 184          return true;
 185          */
 186      }
 187  
 188      // returns true or false
 189  	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename) //**tochange
 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);//**change
 204          $rez = $this->_connectionID->query($sql);
 205          if (!$rez) {
 206              //$this->_errorNo = sqlite3_last_error($this->_connectionID);**change
 207              $this->_connectionID->lastErrorCode();
 208          }
 209  
 210          return $rez;
 211      }
 212  
 213      function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 214      {
 215          $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
 216          $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : ($offset >= 0 ? ' LIMIT 999999999' : '');
 217            if ($secs2cache)
 218                 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 219            else
 220                 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 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) return false;
 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)) return false;
 265          $ok = $this->Execute(sprintf($this->_genSeqSQL,$seqname));
 266          if (!$ok) return false;
 267          $start -= 1;
 268          return $this->Execute("insert into $seqname values($start)");
 269      }
 270  
 271      var $_dropSeqSQL = 'drop table %s';
 272  	function DropSequence($seqname)
 273      {
 274          if (empty($this->_dropSeqSQL)) return false;
 275          return $this->Execute(sprintf($this->_dropSeqSQL,$seqname));
 276      }
 277  
 278      // returns true or false
 279  	function _close()
 280      {
 281          //return @sqlite3_close($this->_connectionID);**change
 282          return $this->_connectionID->close();
 283      }
 284  
 285  	function MetaIndexes($table, $primary = FALSE, $owner=false, $owner = false)
 286      {
 287          $false = false;
 288          // save old fetch mode
 289          global $ADODB_FETCH_MODE;
 290          $save = $ADODB_FETCH_MODE;
 291          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 292          if ($this->fetchMode !== FALSE) {
 293                 $savem = $this->SetFetchMode(FALSE);
 294          }
 295          $SQL=sprintf("SELECT name,sql FROM sqlite_master WHERE type='index' AND tbl_name='%s'", strtolower($table));
 296          $rs = $this->Execute($SQL);
 297          if (!is_object($rs)) {
 298              if (isset($savem))
 299                  $this->SetFetchMode($savem);
 300              $ADODB_FETCH_MODE = $save;
 301              return $false;
 302          }
 303  
 304          $indexes = array ();
 305          while ($row = $rs->FetchRow()) {
 306              if ($primary && preg_match("/primary/i",$row[1]) == 0) continue;
 307              if (!isset($indexes[$row[0]])) {
 308  
 309              $indexes[$row[0]] = array(
 310                     'unique' => preg_match("/unique/i",$row[1]),
 311                     'columns' => array());
 312              }
 313              /**
 314                * There must be a more elegant way of doing this,
 315                * the index elements appear in the SQL statement
 316                * in cols[1] between parentheses
 317                * e.g CREATE UNIQUE INDEX ware_0 ON warehouse (org,warehouse)
 318                */
 319              $cols = explode("(",$row[1]);
 320              $cols = explode(")",$cols[1]);
 321              array_pop($cols);
 322              $indexes[$row[0]]['columns'] = $cols;
 323          }
 324          if (isset($savem)) {
 325              $this->SetFetchMode($savem);
 326              $ADODB_FETCH_MODE = $save;
 327          }
 328          return $indexes;
 329      }
 330  
 331  }
 332  
 333  /*--------------------------------------------------------------------------------------
 334           Class Name: Recordset
 335  --------------------------------------------------------------------------------------*/
 336  
 337  //class ADORecordset_sqlite extends ADORecordSet {**change
 338  class ADORecordset_sqlite3 extends ADORecordSet {
 339  
 340      //var $databaseType = "sqlite";**change
 341      var $databaseType = "sqlite3";
 342      var $bind = false;
 343  
 344      //function ADORecordset_sqlite($queryID,$mode=false)**change
 345  	function ADORecordset_sqlite3($queryID,$mode=false)
 346      {
 347  
 348          if ($mode === false) {
 349              global $ADODB_FETCH_MODE;
 350              $mode = $ADODB_FETCH_MODE;
 351          }
 352          switch($mode) {
 353          //case ADODB_FETCH_NUM: $this->fetchMode = SQLITE_NUM; break;**change
 354          case ADODB_FETCH_NUM: $this->fetchMode = SQLITE3_NUM; break;
 355          //case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE_ASSOC; break;**change
 356          case ADODB_FETCH_ASSOC: $this->fetchMode = SQLITE3_ASSOC; break;
 357          //default: $this->fetchMode = SQLITE_BOTH; break;**change
 358          default: $this->fetchMode = SQLITE3_BOTH; break;
 359          }
 360          $this->adodbFetchMode = $mode;
 361  
 362          $this->_queryID = $queryID;
 363  
 364          $this->_inited = true;
 365          $this->fields = array();
 366          if ($queryID) {
 367              $this->_currentRow = 0;
 368              $this->EOF = !$this->_fetch();
 369              @$this->_initrs();
 370          } else {
 371              $this->_numOfRows = 0;
 372              $this->_numOfFields = 0;
 373              $this->EOF = true;
 374          }
 375  
 376          return $this->_queryID;
 377      }
 378  
 379  
 380  	function FetchField($fieldOffset = -1)
 381      {
 382          $fld = new ADOFieldObject;
 383          //$fld->name = sqlite3_field_name($this->_queryID, $fieldOffset);**change
 384          $fld->name->columnName($this->_queryID, $fieldOffset);
 385          $fld->type = 'VARCHAR';
 386          $fld->max_length = -1;
 387          return $fld;
 388      }
 389  
 390     function _initrs()
 391     {
 392          //$this->_numOfRows = @sqlite_num_rows($this->_queryID); **tochange but sqlite3 doesn't implement this!
 393          $this->_numOfRows = 1;
 394          //$this->_numOfFields = @sqlite3_num_fields($this->_queryID);**change
 395          $this->_numOfFields = $this->_queryID->numColumns();
 396  
 397     }
 398  
 399  	function Fields($colname)
 400      {
 401          //if ($this->fetchMode != SQLITE_NUM) return $this->fields[$colname];**change
 402          if ($this->fetchMode != SQLITE3_NUM) return $this->fields[$colname];
 403          if (!$this->bind) {
 404              $this->bind = array();
 405              for ($i=0; $i < $this->_numOfFields; $i++) {
 406                  $o = $this->FetchField($i);
 407                  $this->bind[strtoupper($o->name)] = $i;
 408              }
 409          }
 410  
 411           return $this->fields[$this->bind[strtoupper($colname)]];
 412      }
 413  
 414     function _seek($row)
 415     {
 416             return sqlite3_seek($this->_queryID, $row);//**tochange but sqlite3 seems not to implement seek!
 417     }
 418  
 419      function _fetch($ignore_fields=false)
 420      {
 421          //$this->fields = @sqlite3_fetch_array($this->_queryID,$this->fetchMode);**change
 422          $this->fields = $this->_queryID->fetchArray($this->fetchMode);
 423          return !empty($this->fields);
 424      }
 425  
 426      function _close()
 427      {
 428      }
 429  
 430  }