[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4   * PHPExcel_CachedObjectStorage_SQLite3
   5   *
   6   * Copyright (c) 2006 - 2015 PHPExcel
   7   *
   8   * This library is free software; you can redistribute it and/or
   9   * modify it under the terms of the GNU Lesser General Public
  10   * License as published by the Free Software Foundation; either
  11   * version 2.1 of the License, or (at your option) any later version.
  12   *
  13   * This library is distributed in the hope that it will be useful,
  14   * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  16   * Lesser General Public License for more details.
  17   *
  18   * You should have received a copy of the GNU Lesser General Public
  19   * License along with this library; if not, write to the Free Software
  20   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  21   *
  22   * @category   PHPExcel
  23   * @package    PHPExcel_CachedObjectStorage
  24   * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
  25   * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  26   * @version    ##VERSION##, ##DATE##
  27   */
  28  class PHPExcel_CachedObjectStorage_SQLite3 extends PHPExcel_CachedObjectStorage_CacheBase implements PHPExcel_CachedObjectStorage_ICache
  29  {
  30      /**
  31       * Database table name
  32       *
  33       * @var string
  34       */
  35      private $TableName = null;
  36  
  37      /**
  38       * Database handle
  39       *
  40       * @var resource
  41       */
  42      private $DBHandle = null;
  43  
  44      /**
  45       * Prepared statement for a SQLite3 select query
  46       *
  47       * @var SQLite3Stmt
  48       */
  49      private $selectQuery;
  50  
  51      /**
  52       * Prepared statement for a SQLite3 insert query
  53       *
  54       * @var SQLite3Stmt
  55       */
  56      private $insertQuery;
  57  
  58      /**
  59       * Prepared statement for a SQLite3 update query
  60       *
  61       * @var SQLite3Stmt
  62       */
  63      private $updateQuery;
  64  
  65      /**
  66       * Prepared statement for a SQLite3 delete query
  67       *
  68       * @var SQLite3Stmt
  69       */
  70      private $deleteQuery;
  71  
  72      /**
  73       * Store cell data in cache for the current cell object if it's "dirty",
  74       *     and the 'nullify' the current cell object
  75       *
  76       * @return    void
  77       * @throws    PHPExcel_Exception
  78       */
  79      protected function storeData()
  80      {
  81          if ($this->currentCellIsDirty && !empty($this->currentObjectID)) {
  82              $this->currentObject->detach();
  83  
  84              $this->insertQuery->bindValue('id', $this->currentObjectID, SQLITE3_TEXT);
  85              $this->insertQuery->bindValue('data', serialize($this->currentObject), SQLITE3_BLOB);
  86              $result = $this->insertQuery->execute();
  87              if ($result === false) {
  88                  throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
  89              }
  90              $this->currentCellIsDirty = false;
  91          }
  92          $this->currentObjectID = $this->currentObject = null;
  93      }
  94  
  95      /**
  96       * Add or Update a cell in cache identified by coordinate address
  97       *
  98       * @param    string            $pCoord        Coordinate address of the cell to update
  99       * @param    PHPExcel_Cell    $cell        Cell to update
 100       * @return    PHPExcel_Cell
 101       * @throws    PHPExcel_Exception
 102       */
 103      public function addCacheData($pCoord, PHPExcel_Cell $cell)
 104      {
 105          if (($pCoord !== $this->currentObjectID) && ($this->currentObjectID !== null)) {
 106              $this->storeData();
 107          }
 108  
 109          $this->currentObjectID = $pCoord;
 110          $this->currentObject = $cell;
 111          $this->currentCellIsDirty = true;
 112  
 113          return $cell;
 114      }
 115  
 116      /**
 117       * Get cell at a specific coordinate
 118       *
 119       * @param     string             $pCoord        Coordinate of the cell
 120       * @throws     PHPExcel_Exception
 121       * @return     PHPExcel_Cell     Cell that was found, or null if not found
 122       */
 123      public function getCacheData($pCoord)
 124      {
 125          if ($pCoord === $this->currentObjectID) {
 126              return $this->currentObject;
 127          }
 128          $this->storeData();
 129  
 130          $this->selectQuery->bindValue('id', $pCoord, SQLITE3_TEXT);
 131          $cellResult = $this->selectQuery->execute();
 132          if ($cellResult === false) {
 133              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 134          }
 135          $cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
 136          if ($cellData === false) {
 137              //    Return null if requested entry doesn't exist in cache
 138              return null;
 139          }
 140  
 141          //    Set current entry to the requested entry
 142          $this->currentObjectID = $pCoord;
 143  
 144          $this->currentObject = unserialize($cellData['value']);
 145          //    Re-attach this as the cell's parent
 146          $this->currentObject->attach($this);
 147  
 148          //    Return requested entry
 149          return $this->currentObject;
 150      }
 151  
 152      /**
 153       *    Is a value set for an indexed cell?
 154       *
 155       * @param    string        $pCoord        Coordinate address of the cell to check
 156       * @return    boolean
 157       */
 158      public function isDataSet($pCoord)
 159      {
 160          if ($pCoord === $this->currentObjectID) {
 161              return true;
 162          }
 163  
 164          //    Check if the requested entry exists in the cache
 165          $this->selectQuery->bindValue('id', $pCoord, SQLITE3_TEXT);
 166          $cellResult = $this->selectQuery->execute();
 167          if ($cellResult === false) {
 168              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 169          }
 170          $cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
 171  
 172          return ($cellData === false) ? false : true;
 173      }
 174  
 175      /**
 176       *    Delete a cell in cache identified by coordinate address
 177       *
 178       * @param    string            $pCoord        Coordinate address of the cell to delete
 179       * @throws    PHPExcel_Exception
 180       */
 181      public function deleteCacheData($pCoord)
 182      {
 183          if ($pCoord === $this->currentObjectID) {
 184              $this->currentObject->detach();
 185              $this->currentObjectID = $this->currentObject = null;
 186          }
 187  
 188          //    Check if the requested entry exists in the cache
 189          $this->deleteQuery->bindValue('id', $pCoord, SQLITE3_TEXT);
 190          $result = $this->deleteQuery->execute();
 191          if ($result === false) {
 192              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 193          }
 194  
 195          $this->currentCellIsDirty = false;
 196      }
 197  
 198      /**
 199       * Move a cell object from one address to another
 200       *
 201       * @param    string        $fromAddress    Current address of the cell to move
 202       * @param    string        $toAddress        Destination address of the cell to move
 203       * @return    boolean
 204       */
 205      public function moveCell($fromAddress, $toAddress)
 206      {
 207          if ($fromAddress === $this->currentObjectID) {
 208              $this->currentObjectID = $toAddress;
 209          }
 210  
 211          $this->deleteQuery->bindValue('id', $toAddress, SQLITE3_TEXT);
 212          $result = $this->deleteQuery->execute();
 213          if ($result === false) {
 214              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 215          }
 216  
 217          $this->updateQuery->bindValue('toid', $toAddress, SQLITE3_TEXT);
 218          $this->updateQuery->bindValue('fromid', $fromAddress, SQLITE3_TEXT);
 219          $result = $this->updateQuery->execute();
 220          if ($result === false) {
 221              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 222          }
 223  
 224          return true;
 225      }
 226  
 227      /**
 228       * Get a list of all cell addresses currently held in cache
 229       *
 230       * @return    string[]
 231       */
 232      public function getCellList()
 233      {
 234          if ($this->currentObjectID !== null) {
 235              $this->storeData();
 236          }
 237  
 238          $query = "SELECT id FROM kvp_".$this->TableName;
 239          $cellIdsResult = $this->DBHandle->query($query);
 240          if ($cellIdsResult === false) {
 241              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 242          }
 243  
 244          $cellKeys = array();
 245          while ($row = $cellIdsResult->fetchArray(SQLITE3_ASSOC)) {
 246              $cellKeys[] = $row['id'];
 247          }
 248  
 249          return $cellKeys;
 250      }
 251  
 252      /**
 253       * Clone the cell collection
 254       *
 255       * @param    PHPExcel_Worksheet    $parent        The new worksheet
 256       * @return    void
 257       */
 258      public function copyCellCollection(PHPExcel_Worksheet $parent)
 259      {
 260          $this->currentCellIsDirty;
 261          $this->storeData();
 262  
 263          //    Get a new id for the new table name
 264          $tableName = str_replace('.', '_', $this->getUniqueID());
 265          if (!$this->DBHandle->exec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
 266              AS SELECT * FROM kvp_'.$this->TableName)
 267          ) {
 268              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 269          }
 270  
 271          //    Copy the existing cell cache file
 272          $this->TableName = $tableName;
 273      }
 274  
 275      /**
 276       * Clear the cell collection and disconnect from our parent
 277       *
 278       * @return    void
 279       */
 280      public function unsetWorksheetCells()
 281      {
 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      }
 292  
 293      /**
 294       * Initialise this new cell collection
 295       *
 296       * @param    PHPExcel_Worksheet    $parent        The worksheet for this cell collection
 297       */
 298      public function __construct(PHPExcel_Worksheet $parent)
 299      {
 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              }
 309              if (!$this->DBHandle->exec('CREATE TABLE kvp_'.$this->TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)')) {
 310                  throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 311              }
 312          }
 313  
 314          $this->selectQuery = $this->DBHandle->prepare("SELECT value FROM kvp_".$this->TableName." WHERE id = :id");
 315          $this->insertQuery = $this->DBHandle->prepare("INSERT OR REPLACE INTO kvp_".$this->TableName." VALUES(:id,:data)");
 316          $this->updateQuery = $this->DBHandle->prepare("UPDATE kvp_".$this->TableName." SET id=:toId WHERE id=:fromId");
 317          $this->deleteQuery = $this->DBHandle->prepare("DELETE FROM kvp_".$this->TableName." WHERE id = :id");
 318      }
 319  
 320      /**
 321       * Destroy this cell collection
 322       */
 323      public function __destruct()
 324      {
 325          if (!is_null($this->DBHandle)) {
 326              $this->DBHandle->exec('DROP TABLE kvp_'.$this->TableName);
 327              $this->DBHandle->close();
 328          }
 329          $this->DBHandle = null;
 330      }
 331  
 332      /**
 333       * Identify whether the caching method is currently available
 334       * Some methods are dependent on the availability of certain extensions being enabled in the PHP build
 335       *
 336       * @return    boolean
 337       */
 338      public static function cacheMethodIsAvailable()
 339      {
 340          if (!class_exists('SQLite3', false)) {
 341              return false;
 342          }
 343  
 344          return true;
 345      }
 346  }