[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_SQLite3

  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_SQLite3 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       * Prepared statement for a SQLite3 select query

  54       *

  55       * @var SQLite3Stmt

  56       */
  57      private $_selectQuery;
  58  
  59      /**

  60       * Prepared statement for a SQLite3 insert query

  61       *

  62       * @var SQLite3Stmt

  63       */
  64      private $_insertQuery;
  65  
  66      /**

  67       * Prepared statement for a SQLite3 update query

  68       *

  69       * @var SQLite3Stmt

  70       */
  71      private $_updateQuery;
  72  
  73      /**

  74       * Prepared statement for a SQLite3 delete query

  75       *

  76       * @var SQLite3Stmt

  77       */
  78      private $_deleteQuery;
  79  
  80      /**

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

  82       *     and the 'nullify' the current cell object

  83       *

  84       * @return    void

  85       * @throws    PHPExcel_Exception

  86       */
  87  	protected function _storeData() {
  88          if ($this->_currentCellIsDirty && !empty($this->_currentObjectID)) {
  89              $this->_currentObject->detach();
  90  
  91              $this->_insertQuery->bindValue('id',$this->_currentObjectID,SQLITE3_TEXT);
  92              $this->_insertQuery->bindValue('data',serialize($this->_currentObject),SQLITE3_BLOB);
  93              $result = $this->_insertQuery->execute();
  94              if ($result === false)
  95                  throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  96              $this->_currentCellIsDirty = false;
  97          }
  98          $this->_currentObjectID = $this->_currentObject = null;
  99      }    //    function _storeData()

 100  
 101  
 102      /**

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

 104       *

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

 106       * @param    PHPExcel_Cell    $cell        Cell to update

 107       * @return    void

 108       * @throws    PHPExcel_Exception

 109       */
 110  	public function addCacheData($pCoord, PHPExcel_Cell $cell) {
 111          if (($pCoord !== $this->_currentObjectID) && ($this->_currentObjectID !== null)) {
 112              $this->_storeData();
 113          }
 114  
 115          $this->_currentObjectID = $pCoord;
 116          $this->_currentObject = $cell;
 117          $this->_currentCellIsDirty = true;
 118  
 119          return $cell;
 120      }    //    function addCacheData()

 121  
 122  
 123      /**

 124       * Get cell at a specific coordinate

 125       *

 126       * @param     string             $pCoord        Coordinate of the cell

 127       * @throws     PHPExcel_Exception

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

 129       */
 130  	public function getCacheData($pCoord) {
 131          if ($pCoord === $this->_currentObjectID) {
 132              return $this->_currentObject;
 133          }
 134          $this->_storeData();
 135  
 136          $this->_selectQuery->bindValue('id',$pCoord,SQLITE3_TEXT);
 137          $cellResult = $this->_selectQuery->execute();
 138          if ($cellResult === FALSE) {
 139              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 140          }
 141          $cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
 142          if ($cellData === FALSE) {
 143              //    Return null if requested entry doesn't exist in cache

 144              return NULL;
 145          }
 146  
 147          //    Set current entry to the requested entry

 148          $this->_currentObjectID = $pCoord;
 149  
 150          $this->_currentObject = unserialize($cellData['value']);
 151          //    Re-attach this as the cell's parent

 152          $this->_currentObject->attach($this);
 153  
 154          //    Return requested entry

 155          return $this->_currentObject;
 156      }    //    function getCacheData()

 157  
 158  
 159      /**

 160       *    Is a value set for an indexed cell?

 161       *

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

 163       * @return    boolean

 164       */
 165  	public function isDataSet($pCoord) {
 166          if ($pCoord === $this->_currentObjectID) {
 167              return TRUE;
 168          }
 169  
 170          //    Check if the requested entry exists in the cache

 171          $this->_selectQuery->bindValue('id',$pCoord,SQLITE3_TEXT);
 172          $cellResult = $this->_selectQuery->execute();
 173          if ($cellResult === FALSE) {
 174              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 175          }
 176          $cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
 177  
 178          return ($cellData === FALSE) ? FALSE : TRUE;
 179      }    //    function isDataSet()

 180  
 181  
 182      /**

 183       *    Delete a cell in cache identified by coordinate address

 184       *

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

 186       * @throws    PHPExcel_Exception

 187       */
 188  	public function deleteCacheData($pCoord) {
 189          if ($pCoord === $this->_currentObjectID) {
 190              $this->_currentObject->detach();
 191              $this->_currentObjectID = $this->_currentObject = NULL;
 192          }
 193  
 194          //    Check if the requested entry exists in the cache

 195          $this->_deleteQuery->bindValue('id',$pCoord,SQLITE3_TEXT);
 196          $result = $this->_deleteQuery->execute();
 197          if ($result === FALSE)
 198              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 199  
 200          $this->_currentCellIsDirty = FALSE;
 201      }    //    function deleteCacheData()

 202  
 203  
 204      /**

 205       * Move a cell object from one address to another

 206       *

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

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

 209       * @return    boolean

 210       */
 211  	public function moveCell($fromAddress, $toAddress) {
 212          if ($fromAddress === $this->_currentObjectID) {
 213              $this->_currentObjectID = $toAddress;
 214          }
 215  
 216          $this->_deleteQuery->bindValue('id',$toAddress,SQLITE3_TEXT);
 217          $result = $this->_deleteQuery->execute();
 218          if ($result === false)
 219              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 220  
 221          $this->_updateQuery->bindValue('toid',$toAddress,SQLITE3_TEXT);
 222          $this->_updateQuery->bindValue('fromid',$fromAddress,SQLITE3_TEXT);
 223          $result = $this->_updateQuery->execute();
 224          if ($result === false)
 225              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 226  
 227          return TRUE;
 228      }    //    function moveCell()

 229  
 230  
 231      /**

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

 233       *

 234       * @return    array of string

 235       */
 236  	public function getCellList() {
 237          if ($this->_currentObjectID !== null) {
 238              $this->_storeData();
 239          }
 240  
 241          $query = "SELECT id FROM kvp_".$this->_TableName;
 242          $cellIdsResult = $this->_DBHandle->query($query);
 243          if ($cellIdsResult === false)
 244              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 245  
 246          $cellKeys = array();
 247          while ($row = $cellIdsResult->fetchArray(SQLITE3_ASSOC)) {
 248              $cellKeys[] = $row['id'];
 249          }
 250  
 251          return $cellKeys;
 252      }    //    function getCellList()

 253  
 254  
 255      /**

 256       * Clone the cell collection

 257       *

 258       * @param    PHPExcel_Worksheet    $parent        The new worksheet

 259       * @return    void

 260       */
 261  	public function copyCellCollection(PHPExcel_Worksheet $parent) {
 262          $this->_currentCellIsDirty;
 263          $this->_storeData();
 264  
 265          //    Get a new id for the new table name

 266          $tableName = str_replace('.','_',$this->_getUniqueID());
 267          if (!$this->_DBHandle->exec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
 268                                                 AS SELECT * FROM kvp_'.$this->_TableName))
 269              throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 270  
 271          //    Copy the existing cell cache file

 272          $this->_TableName = $tableName;
 273      }    //    function copyCellCollection()

 274  
 275  
 276      /**

 277       * Clear the cell collection and disconnect from our parent

 278       *

 279       * @return    void

 280       */
 281  	public function unsetWorksheetCells() {
 282          if(!is_null($this->_currentObject)) {
 283              $this->_currentObject->detach();
 284              $this->_currentObject = $this->_currentObjectID = null;
 285          }
 286          //    detach ourself from the worksheet, so that it can then delete this object successfully

 287          $this->_parent = null;
 288  
 289          //    Close down the temporary cache file

 290          $this->__destruct();
 291      }    //    function unsetWorksheetCells()

 292  
 293  
 294      /**

 295       * Initialise this new cell collection

 296       *

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

 298       */
 299  	public function __construct(PHPExcel_Worksheet $parent) {
 300          parent::__construct($parent);
 301          if (is_null($this->_DBHandle)) {
 302              $this->_TableName = str_replace('.','_',$this->_getUniqueID());
 303              $_DBName = ':memory:';
 304  
 305              $this->_DBHandle = new SQLite3($_DBName);
 306              if ($this->_DBHandle === false)
 307                  throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 308              if (!$this->_DBHandle->exec('CREATE TABLE kvp_'.$this->_TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)'))
 309                  throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
 310          }
 311  
 312          $this->_selectQuery = $this->_DBHandle->prepare("SELECT value FROM kvp_".$this->_TableName." WHERE id = :id");
 313          $this->_insertQuery = $this->_DBHandle->prepare("INSERT OR REPLACE INTO kvp_".$this->_TableName." VALUES(:id,:data)");
 314          $this->_updateQuery = $this->_DBHandle->prepare("UPDATE kvp_".$this->_TableName." SET id=:toId WHERE id=:fromId");
 315          $this->_deleteQuery = $this->_DBHandle->prepare("DELETE FROM kvp_".$this->_TableName." WHERE id = :id");
 316      }    //    function __construct()

 317  
 318  
 319      /**

 320       * Destroy this cell collection

 321       */
 322  	public function __destruct() {
 323          if (!is_null($this->_DBHandle)) {
 324              $this->_DBHandle->exec('DROP TABLE kvp_'.$this->_TableName);
 325              $this->_DBHandle->close();
 326          }
 327          $this->_DBHandle = null;
 328      }    //    function __destruct()

 329  
 330  
 331      /**

 332       * Identify whether the caching method is currently available

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

 334       *

 335       * @return    boolean

 336       */
 337  	public static function cacheMethodIsAvailable() {
 338          if (!class_exists('SQLite3',FALSE)) {
 339              return false;
 340          }
 341  
 342          return true;
 343      }
 344  
 345  }