Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 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 - 2013 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_SQLite3
31
 *
32
 * @category   PHPExcel
33
 * @package    PHPExcel_CachedObjectStorage
34
 * @copyright  Copyright (c) 2006 - 2013 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) {
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
}