vendor/phpoffice/phpexcel/Classes/PHPExcel/CachedObjectStorage/SQLite.php

   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      PHPExcel_Cell
  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      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 }