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_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: }
346: