[Summary view] [Print] [Text view]

   1  <?php
   2  /**

   3   * PHPExcel

   4   *

   5   * Copyright (c) 2006 - 2014 PHPExcel

   6   *

   7   * This library is free software; you can redistribute it and/or

   8   * modify it under the terms of the GNU Lesser General Public

   9   * License as published by the Free Software Foundation; either

  10   * version 2.1 of the License, or (at your option) any later version.

  11   *

  12   * This library is distributed in the hope that it will be useful,

  13   * but WITHOUT ANY WARRANTY; without even the implied warranty of

  14   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU

  15   * Lesser General Public License for more details.

  16   *

  17   * You should have received a copy of the GNU Lesser General Public

  18   * License along with this library; if not, write to the Free Software

  19   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

  20   *

  21   * @category   PHPExcel

  22   * @package    PHPExcel_CachedObjectStorage

  23   * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)

  24   * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL

  25   * @version    ##VERSION##, ##DATE##

  26   */
  27  
  28  
  29  /**

  30   * PHPExcel_CachedObjectStorage_SQLite

  31   *

  32   * @category   PHPExcel

  33   * @package    PHPExcel_CachedObjectStorage

  34   * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)

  35   */
  36  class PHPExcel_CachedObjectStorage_SQLite extends PHPExcel_CachedObjectStorage_CacheBase implements PHPExcel_CachedObjectStorage_ICache {
  37  
  38      /**

  39       * Database table name

  40       *

  41       * @var string

  42       */
  43      private $_TableName = null;
  44  
  45      /**

  46       * Database handle

  47       *

  48       * @var resource

  49       */
  50      private $_DBHandle = null;
  51  
  52      /**

  53       * Store cell data in cache for the current cell object if it's "dirty",

  54       *     and the 'nullify' the current cell object

  55       *

  56       * @return    void

  57       * @throws    PHPExcel_Exception

  58       */
  59  	protected function _storeData() {
  60          if ($this->_currentCellIsDirty && !empty($this->_currentObjectID)) {
  61              $this->_currentObject->detach();
  62  
  63              if (!$this->_DBHandle->queryExec("INSERT OR REPLACE INTO kvp_".$this->_TableName." VALUES('".$this->_currentObjectID."','".sqlite_escape_string(serialize($this->_currentObject))."')"))
  64                  throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
  65              $this->_currentCellIsDirty = false;
  66          }
  67          $this->_currentObjectID = $this->_currentObject = null;
  68      }    //    function _storeData()

  69  
  70  
  71      /**

  72       * Add or Update a cell in cache identified by coordinate address

  73       *

  74       * @param    string            $pCoord        Coordinate address of the cell to update

  75       * @param    PHPExcel_Cell    $cell        Cell to update

  76       * @return    void

  77       * @throws    PHPExcel_Exception

  78       */
  79  	public function addCacheData($pCoord, PHPExcel_Cell $cell) {
  80          if (($pCoord !== $this->_currentObjectID) && ($this->_currentObjectID !== null)) {
  81              $this->_storeData();
  82          }
  83  
  84          $this->_currentObjectID = $pCoord;
  85          $this->_currentObject = $cell;
  86          $this->_currentCellIsDirty = true;
  87  
  88          return $cell;
  89      }    //    function addCacheData()

  90  
  91  
  92      /**

  93       * Get cell at a specific coordinate

  94       *

  95       * @param     string             $pCoord        Coordinate of the cell

  96       * @throws     PHPExcel_Exception

  97       * @return     PHPExcel_Cell     Cell that was found, or null if not found

  98       */
  99  	public function getCacheData($pCoord) {
 100          if ($pCoord === $this->_currentObjectID) {
 101              return $this->_currentObject;
 102          }
 103          $this->_storeData();
 104  
 105          $query = "SELECT value FROM kvp_".$this->_TableName." WHERE id='".$pCoord."'";
 106          $cellResultSet = $this->_DBHandle->query($query,SQLITE_ASSOC);
 107          if ($cellResultSet === false) {
 108              throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
 109          } elseif ($cellResultSet->numRows() == 0) {
 110              //    Return null if requested entry doesn't exist in cache

 111              return null;
 112          }
 113  
 114          //    Set current entry to the requested entry

 115          $this->_currentObjectID = $pCoord;
 116  
 117          $cellResult = $cellResultSet->fetchSingle();
 118          $this->_currentObject = unserialize($cellResult);
 119          //    Re-attach this as the cell's parent

 120          $this->_currentObject->attach($this);
 121  
 122          //    Return requested entry

 123          return $this->_currentObject;
 124      }    //    function getCacheData()

 125  
 126  
 127      /**

 128       * Is a value set for an indexed cell?

 129       *

 130       * @param    string        $pCoord        Coordinate address of the cell to check

 131       * @return    boolean

 132       */
 133  	public function isDataSet($pCoord) {
 134          if ($pCoord === $this->_currentObjectID) {
 135              return true;
 136          }
 137  
 138          //    Check if the requested entry exists in the cache

 139          $query = "SELECT id FROM kvp_".$this->_TableName." WHERE id='".$pCoord."'";
 140          $cellResultSet = $this->_DBHandle->query($query,SQLITE_ASSOC);
 141          if ($cellResultSet === false) {
 142              throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
 143          } elseif ($cellResultSet->numRows() == 0) {
 144              //    Return null if requested entry doesn't exist in cache

 145              return false;
 146          }
 147          return true;
 148      }    //    function isDataSet()

 149  
 150  
 151      /**

 152       * Delete a cell in cache identified by coordinate address

 153       *

 154       * @param    string            $pCoord        Coordinate address of the cell to delete

 155       * @throws    PHPExcel_Exception

 156       */
 157  	public function deleteCacheData($pCoord) {
 158          if ($pCoord === $this->_currentObjectID) {
 159              $this->_currentObject->detach();
 160              $this->_currentObjectID = $this->_currentObject = null;
 161          }
 162  
 163          //    Check if the requested entry exists in the cache

 164          $query = "DELETE FROM kvp_".$this->_TableName." WHERE id='".$pCoord."'";
 165          if (!$this->_DBHandle->queryExec($query))
 166              throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
 167  
 168          $this->_currentCellIsDirty = false;
 169      }    //    function deleteCacheData()

 170  
 171  
 172      /**

 173       * Move a cell object from one address to another

 174       *

 175       * @param    string        $fromAddress    Current address of the cell to move

 176       * @param    string        $toAddress        Destination address of the cell to move

 177       * @return    boolean

 178       */
 179  	public function moveCell($fromAddress, $toAddress) {
 180          if ($fromAddress === $this->_currentObjectID) {
 181              $this->_currentObjectID = $toAddress;
 182          }
 183  
 184          $query = "DELETE FROM kvp_".$this->_TableName." WHERE id='".$toAddress."'";
 185          $result = $this->_DBHandle->exec($query);
 186          if ($result === false)
 187              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 188  
 189          $query = "UPDATE kvp_".$this->_TableName." SET id='".$toAddress."' WHERE id='".$fromAddress."'";
 190          $result = $this->_DBHandle->exec($query);
 191          if ($result === false)
 192              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 193  
 194          return TRUE;
 195      }    //    function moveCell()

 196  
 197  
 198      /**

 199       * Get a list of all cell addresses currently held in cache

 200       *

 201       * @return    array of string

 202       */
 203  	public function getCellList() {
 204          if ($this->_currentObjectID !== null) {
 205              $this->_storeData();
 206          }
 207  
 208          $query = "SELECT id FROM kvp_".$this->_TableName;
 209          $cellIdsResult = $this->_DBHandle->unbufferedQuery($query,SQLITE_ASSOC);
 210          if ($cellIdsResult === false)
 211              throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
 212  
 213          $cellKeys = array();
 214          foreach($cellIdsResult as $row) {
 215              $cellKeys[] = $row['id'];
 216          }
 217  
 218          return $cellKeys;
 219      }    //    function getCellList()

 220  
 221  
 222      /**

 223       * Clone the cell collection

 224       *

 225       * @param    PHPExcel_Worksheet    $parent        The new worksheet

 226       * @return    void

 227       */
 228  	public function copyCellCollection(PHPExcel_Worksheet $parent) {
 229          $this->_currentCellIsDirty;
 230          $this->_storeData();
 231  
 232          //    Get a new id for the new table name

 233          $tableName = str_replace('.','_',$this->_getUniqueID());
 234          if (!$this->_DBHandle->queryExec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
 235                                                      AS SELECT * FROM kvp_'.$this->_TableName))
 236              throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
 237  
 238          //    Copy the existing cell cache file

 239          $this->_TableName = $tableName;
 240      }    //    function copyCellCollection()

 241  
 242  
 243      /**

 244       * Clear the cell collection and disconnect from our parent

 245       *

 246       * @return    void

 247       */
 248  	public function unsetWorksheetCells() {
 249          if(!is_null($this->_currentObject)) {
 250              $this->_currentObject->detach();
 251              $this->_currentObject = $this->_currentObjectID = null;
 252          }
 253          //    detach ourself from the worksheet, so that it can then delete this object successfully

 254          $this->_parent = null;
 255  
 256          //    Close down the temporary cache file

 257          $this->__destruct();
 258      }    //    function unsetWorksheetCells()

 259  
 260  
 261      /**

 262       * Initialise this new cell collection

 263       *

 264       * @param    PHPExcel_Worksheet    $parent        The worksheet for this cell collection

 265       */
 266  	public function __construct(PHPExcel_Worksheet $parent) {
 267          parent::__construct($parent);
 268          if (is_null($this->_DBHandle)) {
 269              $this->_TableName = str_replace('.','_',$this->_getUniqueID());
 270              $_DBName = ':memory:';
 271  
 272              $this->_DBHandle = new SQLiteDatabase($_DBName);
 273              if ($this->_DBHandle === false)
 274                  throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
 275              if (!$this->_DBHandle->queryExec('CREATE TABLE kvp_'.$this->_TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)'))
 276                  throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
 277          }
 278      }    //    function __construct()

 279  
 280  
 281      /**

 282       * Destroy this cell collection

 283       */
 284  	public function __destruct() {
 285          if (!is_null($this->_DBHandle)) {
 286              $this->_DBHandle->queryExec('DROP TABLE kvp_'.$this->_TableName);
 287          }
 288          $this->_DBHandle = null;
 289      }    //    function __destruct()

 290  
 291  
 292      /**

 293       * Identify whether the caching method is currently available

 294       * Some methods are dependent on the availability of certain extensions being enabled in the PHP build

 295       *

 296       * @return    boolean

 297       */
 298  	public static function cacheMethodIsAvailable() {
 299          if (!function_exists('sqlite_open')) {
 300              return false;
 301          }
 302  
 303          return true;
 304      }
 305  
 306  }