[Summary view] [Print] [Text view]

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