[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /**

   4   * ADOdb Lite is a PHP class to encapsulate multiple database APIs and is compatible with 

   5   * a subset of the ADODB Command Syntax. 

   6   * Currently supports Frontbase, MaxDB, miniSQL, MSSQL, MSSQL Pro, MySQLi, MySQLt, MySQL, PostgresSQL,

   7   * PostgresSQL64, PostgresSQL7, SqLite and Sybase.

   8   * 

   9   */
  10  
  11  class sqlite_driver_ADOConnection extends ADOConnection
  12  {
  13      var $sysDate = "date('Y-m-d')";
  14      var $sysTimeStamp = "date('Y-m-d H:i:s')";
  15  
  16  	function sqlite_driver_ADOConnection()
  17      {
  18          $this->dbtype = 'sqlitepo';
  19      }
  20  
  21      /**

  22       * Connection to database server and selected database

  23       * 

  24       * @access private 

  25       */
  26  
  27  	function _connect($host = "", $username = "", $password = "", $database = "", $persistent, $forcenew)
  28      {
  29          if (!function_exists('sqlite_open')) return false;
  30  
  31          $this->host = $host;
  32          $this->username = $username;
  33          $this->password = $password;
  34          $this->database = ($database == "") ? $host : $database;
  35          $this->persistent = $persistent;
  36          $this->forcenewconnection = $forcenew;
  37  
  38          if($this->persistent == 1)
  39          {
  40              $this->connectionId = @sqlite_popen( $this->database );
  41          }
  42          else
  43          {
  44              $this->connectionId = @sqlite_open( $this->database );
  45          }
  46  
  47          if ($this->connectionId === false)
  48          {
  49              if ($fn = $this->raiseErrorFn) 
  50                  $fn($this->dbtype, 'CONNECT', $this->ErrorNo(), $this->ErrorMsg(), $this->host, $this->database, $this);
  51              return false;
  52          }
  53  
  54          return true;
  55      } 
  56  
  57      /**

  58       * Choose a database to connect.

  59       *

  60       * @param dbname     is the name of the database to select

  61       * @return         true or false

  62       * @access public

  63       */
  64  
  65  	function SelectDB($dbname)
  66      {
  67          $this->database = $dbname;
  68  
  69          $this->connectionId = sqlite_close( $this->connectionId );
  70  
  71          if($this->persistent == 1)
  72          {
  73              $this->connectionId = @sqlite_popen( $this->database );
  74          }
  75          else
  76          {
  77              $this->connectionId = @sqlite_open( $this->database );
  78          }
  79  
  80          if ($this->connectionId === false)
  81          {
  82              if ($fn = $this->raiseErrorFn) 
  83                  $fn($this->dbtype, 'CONNECT', $this->ErrorNo(), $this->ErrorMsg(), $this->host, $this->database, $this);
  84              return false;
  85          }
  86  
  87          return true;
  88      } 
  89  
  90      /**

  91       * Return database error message

  92       * Usage: $errormessage =& $db->ErrorMsg();

  93       * 

  94       * @access public

  95       */
  96  
  97  	function ErrorMsg()
  98      {
  99          $errorno = @sqlite_last_error( $this->connectionId );
 100          $error = ($errorno) ? sqlite_error_string($errorno) : '';
 101          return $error;
 102      }
 103  
 104      /**

 105       * Return database error number

 106       * Usage: $errorbo =& $db->ErrorNo();

 107       * 

 108       * @access public

 109       */
 110  
 111  	function ErrorNo()
 112      {
 113          return @sqlite_last_error($this->connectionId);
 114      }
 115  
 116      /**

 117       * Returns # of affected rows from insert/delete/update query

 118       * 

 119       * @access public 

 120       * @return integer Affected rows

 121       */
 122  
 123  	function Affected_Rows()
 124      {
 125          return @sqlite_rows_affected( $this->connectionId );
 126      } 
 127  
 128      /**

 129       * Returns the last record id of an inserted item

 130       * Usage: $db->Insert_ID();

 131       * 

 132       * @access public 

 133       */
 134  
 135  	function Insert_ID()
 136      {
 137          return @sqlite_last_insert_rowid($this->connectionId);
 138      }
 139  
 140      /**

 141       * Correctly quotes a string so that all strings are escape coded.

 142       * An example is  $db->qstr("Haven't a clue.");

 143       * 

 144       * @param string            the string to quote

 145       * @param [magic_quotes]    if $s is GET/POST var, set to get_magic_quotes_gpc().

 146       *

 147       * @return  single-quoted string IE: 'Haven\'t a clue.'

 148       */
 149  
 150  	function qstr($string, $magic_quotes=false)
 151      {    
 152          if (!$magic_quotes) {
 153              return  "'".str_replace("'", "''", $string)."'";
 154          }
 155          $string = str_replace("\\'", "''", str_replace('\\\\', '\\', str_replace('\\"', '"', $string)));
 156          return "'" . $string . "'";
 157      }
 158  
 159  	function QMagic($string)
 160      {
 161          return $this->qstr($string, get_magic_quotes_gpc());
 162      }
 163  
 164      /**

 165       * Returns concatenated string

 166       * Usage: $db->Concat($str1,$str2);

 167       * 

 168       * @return concatenated string

 169       */
 170  	function Concat()
 171      {
 172          $arr = func_get_args();
 173          return implode("||", $arr);
 174      }
 175  
 176  	function IfNull( $field, $ifNull ) 
 177      {
 178          return " CASE WHEN $field is null THEN $ifNull ELSE $field END ";
 179      }
 180  
 181      /**

 182       * Closes database connection

 183       * Usage: $db->close();

 184       * 

 185       * @access public 

 186       */
 187  
 188  	function Close()
 189      {
 190          @sqlite_close( $this->connectionId );
 191          $this->connectionId = false;
 192      }
 193  
 194       /**

 195       * Returns All Records in an array

 196       *

 197       * Usage: $db->GetAll($sql);

 198       * @access public 

 199       */
 200  
 201      function &GetAll($sql, $inputarr = false)
 202      {
 203          $data =& $this->GetArray($sql, $inputarr);
 204          return $data;
 205      }
 206  
 207       /**

 208       * Returns All Records in an array

 209       *

 210       * Usage: $db->GetArray($sql);

 211       * @access public 

 212       */
 213  
 214      function &GetArray($sql, $inputarr = false)
 215      {
 216          $data = false;
 217          $result =& $this->Execute($sql, $inputarr);
 218          if ($result)
 219          {
 220              $data =& $result->GetArray();
 221              $result->Close();
 222          }
 223          return $data;
 224      }
 225  
 226      /**

 227       * Executes SQL query and instantiates resultset methods

 228       * 

 229       * @access private 

 230       * @return mixed Resultset methods

 231       */
 232  
 233      function &do_query( $sql, $offset, $nrows, $inputarr=false )
 234      {
 235          global $ADODB_FETCH_MODE;
 236  
 237          $false = false;
 238  
 239          $offsetStr = '';
 240          $limitStr = '';
 241          if ($offset != -1 || $nrows != -1)
 242          {
 243              $offsetStr = ($offset>=0) ? " OFFSET " . $offset . "," : '';
 244              $limitStr  = ($nrows >= 0)  ? " LIMIT ". $nrows : ($offset >= 0 ? ' LIMIT 999999999' : '');
 245          }
 246  
 247          if ($inputarr && is_array($inputarr)) {
 248              $sqlarr = explode('?', $sql);
 249              if (!is_array(reset($inputarr))) $inputarr = array($inputarr);
 250              foreach($inputarr as $arr) {
 251                  $sql = ''; $i = 0;
 252                  foreach($arr as $v) {
 253                      $sql .= $sqlarr[$i];
 254                      switch(gettype($v)){
 255                          case 'string':
 256                              $sql .= $this->qstr($v);
 257                              break;
 258                          case 'double':
 259                              $sql .= str_replace(',', '.', $v);
 260                              break;
 261                          case 'boolean':
 262                              $sql .= $v ? 1 : 0;
 263                              break;
 264                          default:
 265                              if ($v === null)
 266                                  $sql .= 'NULL';
 267                              else $sql .= $v;
 268                      }
 269                      $i += 1;
 270                  }
 271                  $sql .= $sqlarr[$i];
 272                  if ($i+1 != sizeof($sqlarr))    
 273                      return $false;
 274                  $this->sql = $sql . "$limitStr$offsetStr";
 275                  $time_start = array_sum(explode(' ', microtime()));
 276                  $this->query_count++;
 277                  $resultId = @sqlite_query( $this->sql, $this->connectionId );
 278                  $this->query_time_total += (array_sum(explode(' ', microtime())) - $time_start);
 279                  if($this->debug)
 280                  {
 281                      $this->outp($sql . "$limitStr$offsetStr");
 282                  }
 283                  if ($resultId === false) { // error handling if query fails
 284                      if ($fn = $this->raiseErrorFn)
 285                          $fn($this->dbtype, 'EXECUTE', $this->ErrorNo(), $this->ErrorMsg(), $this->sql, $inputarr, $this);
 286                      return $false;
 287                  } 
 288              }
 289          }
 290          else
 291          {
 292                  $this->sql = $sql . "$limitStr$offsetStr";
 293                  $time_start = array_sum(explode(' ', microtime()));
 294                  $this->query_count++;
 295                  $resultId = @sqlite_query( $this->sql, $this->connectionId );
 296                  $this->query_time_total += (array_sum(explode(' ', microtime())) - $time_start);
 297                  if($this->debug)
 298                  {
 299                      $this->outp($sql . "$limitStr$offsetStr");
 300                  }
 301          }
 302  
 303          if ($resultId === false) { // error handling if query fails
 304              if ($fn = $this->raiseErrorFn)
 305                  $fn($this->dbtype, 'EXECUTE', $this->ErrorNo(), $this->ErrorMsg(), $this->sql, $inputarr, $this);
 306              return $false;
 307          } 
 308  
 309          if ($resultId === true) { // return simplified recordset for inserts/updates/deletes with lower overhead
 310              $rs =& new ADORecordSet_empty();
 311              return $rs;
 312          }
 313  
 314          $resultset_name = $this->last_module_name . "_ResultSet";
 315          $recordset = new $resultset_name( $resultId, $this->connectionId );
 316  
 317          $recordset->_currentRow = 0;
 318  
 319          switch ($ADODB_FETCH_MODE)
 320          {
 321              case ADODB_FETCH_NUM: $recordset->fetchMode = SQLITE_NUM; break;
 322              case ADODB_FETCH_ASSOC:$recordset->fetchMode = SQLITE_ASSOC; break;
 323              default:
 324              case ADODB_FETCH_DEFAULT:
 325              case ADODB_FETCH_BOTH:$recordset->fetchMode = SQLITE_BOTH; break;
 326          }
 327  
 328          $recordset->_numOfRows = @sqlite_num_rows( $resultId );
 329          if( $recordset->_numOfRows == 0)
 330          {
 331              $recordset->EOF = true;
 332          }
 333          $recordset->_numOfFields = @sqlite_num_fields( $resultId );
 334          $recordset->_fetch();
 335  
 336          return $recordset;
 337      } 
 338  } 
 339  
 340  class sqlite_driver_ResultSet
 341  {
 342      var $connectionId;
 343      var $fields;
 344      var $resultId;
 345      var $_currentRow = 0;
 346      var $_numOfRows = -1;
 347      var $_numOfFields = -1;
 348      var $fetchMode;
 349      var $EOF;
 350  
 351      /**

 352       * sqliteResultSet Constructor

 353       * 

 354       * @access private 

 355       * @param string $record 

 356       * @param string $resultId 

 357       */
 358  
 359  	function sqlite_driver_ResultSet( $resultId, $connectionId )
 360      {
 361          $this->fields = array();
 362          $this->connectionId = $connectionId;
 363          $this->record = array();
 364          $this->resultId = $resultId;
 365          $this->EOF = false;
 366      } 
 367  
 368      /**

 369       * Frees resultset

 370       * 

 371       * @access public 

 372       */
 373  
 374  	function close()
 375      {
 376          @sqlite_free_result( $this->resultId );
 377          $this->fields = array();
 378          $this->resultId = false;
 379      } 
 380  
 381      /**

 382       * Returns field name from select query

 383       * 

 384       * @access public 

 385       * @param string $field

 386       * @return string Field name

 387       */
 388  
 389  	function fields( $field )
 390      {
 391          return $this->fields[$field];
 392      } 
 393  
 394      /**

 395       * Returns numrows from select query

 396       * 

 397       * @access public 

 398       * @return integer Numrows

 399       */
 400  
 401  	function RecordCount()
 402      {
 403          return $this->_numOfRows;
 404      } 
 405  
 406      /**

 407       * Returns num of fields from select query

 408       * 

 409       * @access public 

 410       * @return integer numfields

 411       */
 412  
 413  	function FieldCount()
 414      {
 415          return $this->_numOfFields;
 416      } 
 417  
 418      /**

 419       * Returns next record

 420       * 

 421       * @access public 

 422       */
 423  
 424  	function MoveNext()
 425      {
 426          if (@$this->fields = sqlite_fetch_array($this->resultId,$this->fetchMode)) {
 427              $this->_currentRow += 1;
 428              return true;
 429          }
 430          if (!$this->EOF) {
 431              $this->_currentRow += 1;
 432              $this->EOF = true;
 433          }
 434          return false;
 435      } 
 436  
 437      /**

 438       * Move to the first row in the recordset. Many databases do NOT support this.

 439       *

 440       * @return true or false

 441       */
 442  
 443  	function MoveFirst() 
 444      {
 445          if ($this->_currentRow == 0) return true;
 446          return $this->Move(0);            
 447      }            
 448  
 449      /**

 450       * Returns the Last Record

 451       * 

 452       * @access public 

 453       */
 454  
 455  	function MoveLast()
 456      {
 457          if ($this->EOF) return false;
 458          return $this->Move($this->_numOfRows - 1);
 459      } 
 460  
 461      /**

 462       * Random access to a specific row in the recordset. Some databases do not support

 463       * access to previous rows in the databases (no scrolling backwards).

 464       *

 465       * @param rowNumber is the row to move to (0-based)

 466       *

 467       * @return true if there still rows available, or false if there are no more rows (EOF).

 468       */
 469  
 470  	function Move($rowNumber = 0) 
 471      {
 472          if ($rowNumber == $this->_currentRow) return true;
 473          $this->EOF = false;
 474             if ($this->_numOfRows > 0){
 475              if ($rowNumber >= $this->_numOfRows - 1){
 476                  $rowNumber = $this->_numOfRows - 1;
 477              }
 478            }
 479  
 480          if ($this->_seek($rowNumber)) {
 481              $this->_currentRow = $rowNumber;
 482              if ($this->_fetch()) {
 483                  return true;
 484              }
 485              $this->fields = false;    
 486          }
 487          $this->EOF = true;
 488          return false;
 489      }
 490  
 491      /**

 492       * Perform Seek to specific row

 493       * 

 494       * @access private 

 495       */
 496  
 497  	function _seek($row)
 498      {
 499          if ($this->_numOfRows == 0) return false;
 500          return @sqlite_seek($this->resultId,$row);
 501      }
 502  
 503      /**

 504       * Fills field array with first database element when query initially executed

 505       * 

 506       * @access private 

 507       */
 508  
 509  	function _fetch()
 510      {
 511          $this->fields = @sqlite_fetch_array($this->resultId,$this->fetchMode);
 512          return is_array($this->fields);
 513      }
 514  
 515      /**

 516       * Check to see if last record reached

 517       * 

 518       * @access public 

 519       */
 520  
 521  	function EOF()
 522      {
 523          if( $this->_currentRow < $this->_numOfRows)
 524          {
 525              return false;
 526          }
 527          else
 528          {
 529              $this->EOF = true;
 530              return true;
 531          }
 532      } 
 533  
 534      /**

 535       * Returns All Records in an array

 536       * 

 537       * @access public 

 538       * @param [nRows]  is the number of rows to return. -1 means every row.

 539       */
 540  
 541      function &GetArray($nRows = -1)
 542      {
 543          $results = array();
 544          $cnt = 0;
 545          while (!$this->EOF && $nRows != $cnt) {
 546              $results[] = $this->fields;
 547              $this->MoveNext();
 548              $cnt++;
 549          }
 550          return $results;
 551      } 
 552  
 553      function &GetRows($nRows = -1) 
 554      {
 555          $arr =& $this->GetArray($nRows);
 556          return $arr;
 557      }
 558  
 559      function &GetAll($nRows = -1)
 560      {
 561          $arr =& $this->GetArray($nRows);
 562          return $arr;
 563      }
 564  
 565      /**

 566      * Fetch field information for a table. 

 567      *

 568      * @return object containing the name, type and max_length

 569      */
 570  	function FetchField($fieldOffset = -1) 
 571      {
 572          $fieldObject = new ADOFieldObject;
 573          $fieldObject->name = sqlite_field_name($this->resultId, $fieldOffset);
 574          $fieldObject->type = 'VARCHAR';
 575          $fieldObject->max_length = -1;
 576          return $fieldObject;
 577      }
 578  }
 579  ?>