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    1.8.0, 2014-03-02
 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: }
307: