[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4   * PHPExcel_CachedObjectStorage_SQLite
   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_SQLite 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       * Store cell data in cache for the current cell object if it's "dirty",
  46       *     and the 'nullify' the current cell object
  47       *
  48       * @return    void
  49       * @throws    PHPExcel_Exception
  50       */
  51      protected function storeData()
  52      {
  53          if ($this->currentCellIsDirty && !empty($this->currentObjectID)) {
  54              $this->currentObject->detach();
  55  
  56              if (!$this->DBHandle->queryExec("INSERT OR REPLACE INTO kvp_".$this->TableName." VALUES('".$this->currentObjectID."','".sqlite_escape_string(serialize($this->currentObject))."')")) {
  57                  throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
  58              }
  59              $this->currentCellIsDirty = false;
  60          }
  61          $this->currentObjectID = $this->currentObject = null;
  62      }
  63  
  64      /**
  65       * Add or Update a cell in cache identified by coordinate address
  66       *
  67       * @param    string            $pCoord        Coordinate address of the cell to update
  68       * @param    PHPExcel_Cell    $cell        Cell to update
  69       * @return    PHPExcel_Cell
  70       * @throws    PHPExcel_Exception
  71       */
  72      public function addCacheData($pCoord, PHPExcel_Cell $cell)
  73      {
  74          if (($pCoord !== $this->currentObjectID) && ($this->currentObjectID !== null)) {
  75              $this->storeData();
  76          }
  77  
  78          $this->currentObjectID = $pCoord;
  79          $this->currentObject = $cell;
  80          $this->currentCellIsDirty = true;
  81  
  82          return $cell;
  83      }
  84  
  85      /**
  86       * Get cell at a specific coordinate
  87       *
  88       * @param     string             $pCoord        Coordinate of the cell
  89       * @throws     PHPExcel_Exception
  90       * @return     PHPExcel_Cell     Cell that was found, or null if not found
  91       */
  92      public function getCacheData($pCoord)
  93      {
  94          if ($pCoord === $this->currentObjectID) {
  95              return $this->currentObject;
  96          }
  97          $this->storeData();
  98  
  99          $query = "SELECT value FROM kvp_".$this->TableName." WHERE id='".$pCoord."'";
 100          $cellResultSet = $this->DBHandle->query($query, SQLITE_ASSOC);
 101          if ($cellResultSet === false) {
 102              throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
 103          } elseif ($cellResultSet->numRows() == 0) {
 104              //    Return null if requested entry doesn't exist in cache
 105              return null;
 106          }
 107  
 108          //    Set current entry to the requested entry
 109          $this->currentObjectID = $pCoord;
 110  
 111          $cellResult = $cellResultSet->fetchSingle();
 112          $this->currentObject = unserialize($cellResult);
 113          //    Re-attach this as the cell's parent
 114          $this->currentObject->attach($this);
 115  
 116          //    Return requested entry
 117          return $this->currentObject;
 118      }
 119  
 120      /**
 121       * Is a value set for an indexed cell?
 122       *
 123       * @param    string        $pCoord        Coordinate address of the cell to check
 124       * @return    boolean
 125       */
 126      public function isDataSet($pCoord)
 127      {
 128          if ($pCoord === $this->currentObjectID) {
 129              return true;
 130          }
 131  
 132          //    Check if the requested entry exists in the cache
 133          $query = "SELECT id FROM kvp_".$this->TableName." WHERE id='".$pCoord."'";
 134          $cellResultSet = $this->DBHandle->query($query, SQLITE_ASSOC);
 135          if ($cellResultSet === false) {
 136              throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
 137          } elseif ($cellResultSet->numRows() == 0) {
 138              //    Return null if requested entry doesn't exist in cache
 139              return false;
 140          }
 141          return true;
 142      }
 143  
 144      /**
 145       * Delete a cell in cache identified by coordinate address
 146       *
 147       * @param    string            $pCoord        Coordinate address of the cell to delete
 148       * @throws    PHPExcel_Exception
 149       */
 150      public function deleteCacheData($pCoord)
 151      {
 152          if ($pCoord === $this->currentObjectID) {
 153              $this->currentObject->detach();
 154              $this->currentObjectID = $this->currentObject = null;
 155          }
 156  
 157          //    Check if the requested entry exists in the cache
 158          $query = "DELETE FROM kvp_".$this->TableName." WHERE id='".$pCoord."'";
 159          if (!$this->DBHandle->queryExec($query)) {
 160              throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
 161          }
 162  
 163          $this->currentCellIsDirty = false;
 164      }
 165  
 166      /**
 167       * Move a cell object from one address to another
 168       *
 169       * @param    string        $fromAddress    Current address of the cell to move
 170       * @param    string        $toAddress        Destination address of the cell to move
 171       * @return    boolean
 172       */
 173      public function moveCell($fromAddress, $toAddress)
 174      {
 175          if ($fromAddress === $this->currentObjectID) {
 176              $this->currentObjectID = $toAddress;
 177          }
 178  
 179          $query = "DELETE FROM kvp_".$this->TableName." WHERE id='".$toAddress."'";
 180          $result = $this->DBHandle->exec($query);
 181          if ($result === false) {
 182              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 183          }
 184  
 185          $query = "UPDATE kvp_".$this->TableName." SET id='".$toAddress."' WHERE id='".$fromAddress."'";
 186          $result = $this->DBHandle->exec($query);
 187          if ($result === false) {
 188              throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
 189          }
 190  
 191          return true;
 192      }
 193  
 194      /**
 195       * Get a list of all cell addresses currently held in cache
 196       *
 197       * @return    string[]
 198       */
 199      public function getCellList()
 200      {
 201          if ($this->currentObjectID !== null) {
 202              $this->storeData();
 203          }
 204  
 205          $query = "SELECT id FROM kvp_".$this->TableName;
 206          $cellIdsResult = $this->DBHandle->unbufferedQuery($query, SQLITE_ASSOC);
 207          if ($cellIdsResult === false) {
 208              throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
 209          }
 210  
 211          $cellKeys = array();
 212          foreach ($cellIdsResult as $row) {
 213              $cellKeys[] = $row['id'];
 214          }
 215  
 216          return $cellKeys;
 217      }
 218  
 219      /**
 220       * Clone the cell collection
 221       *
 222       * @param    PHPExcel_Worksheet    $parent        The new worksheet
 223       * @return    void
 224       */
 225      public function copyCellCollection(PHPExcel_Worksheet $parent)
 226      {
 227          $this->currentCellIsDirty;
 228          $this->storeData();
 229  
 230          //    Get a new id for the new table name
 231          $tableName = str_replace('.', '_', $this->getUniqueID());
 232          if (!$this->DBHandle->queryExec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
 233              AS SELECT * FROM kvp_'.$this->TableName)
 234          ) {
 235              throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
 236          }
 237  
 238          //    Copy the existing cell cache file
 239          $this->TableName = $tableName;
 240      }
 241  
 242      /**
 243       * Clear the cell collection and disconnect from our parent
 244       *
 245       * @return    void
 246       */
 247      public function unsetWorksheetCells()
 248      {
 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      }
 259  
 260      /**
 261       * Initialise this new cell collection
 262       *
 263       * @param    PHPExcel_Worksheet    $parent        The worksheet for this cell collection
 264       */
 265      public function __construct(PHPExcel_Worksheet $parent)
 266      {
 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              }
 276              if (!$this->DBHandle->queryExec('CREATE TABLE kvp_'.$this->TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)')) {
 277                  throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
 278              }
 279          }
 280      }
 281  
 282      /**
 283       * Destroy this cell collection
 284       */
 285      public function __destruct()
 286      {
 287          if (!is_null($this->DBHandle)) {
 288              $this->DBHandle->queryExec('DROP TABLE kvp_'.$this->TableName);
 289          }
 290          $this->DBHandle = null;
 291      }
 292  
 293      /**
 294       * Identify whether the caching method is currently available
 295       * Some methods are dependent on the availability of certain extensions being enabled in the PHP build
 296       *
 297       * @return    boolean
 298       */
 299      public static function cacheMethodIsAvailable()
 300      {
 301          if (!function_exists('sqlite_open')) {
 302              return false;
 303          }
 304  
 305          return true;
 306      }
 307  }