visualizer / vendor / phpoffice / phpexcel / Classes / PHPExcel / CachedObjectStorage / SQLite3.php

SQLite3.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.0.3, at vendor/phpoffice/phpexcel/Classes/PHPExcel/CachedObjectStorage/SQLite3.php

346 lines 10.4 KB

Raw Download Zip

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