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

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

347 lines 11.0 KB

Raw Download Zip

1	<?php
2
3	/**
4	* PHPExcel_CachedObjectStorage_SQLite3
5	*
6	* Copyright (c) 2006 - 2015 PHPExcel
7	*
8	* This library is free software; you can redistribute it and/or
9	* modify it under the terms of the GNU Lesser General Public
10	* License as published by the Free Software Foundation; either
11	* version 2.1 of the License, or (at your option) any later version.
12	*
13	* This library is distributed in the hope that it will be useful,
14	* but WITHOUT ANY WARRANTY; without even the implied warranty of
15	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16	* Lesser General Public License for more details.
17	*
18	* You should have received a copy of the GNU Lesser General Public
19	* License along with this library; if not, write to the Free Software
20	* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
21	*
22	* @category PHPExcel
23	* @package PHPExcel_CachedObjectStorage
24	* @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
25	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
26	* @version ##VERSION##, ##DATE##
27	*/
28	class PHPExcel_CachedObjectStorage_SQLite3 extends PHPExcel_CachedObjectStorage_CacheBase implements PHPExcel_CachedObjectStorage_ICache
29	{
30	/**
31	* Database table name
32	*
33	* @var string
34	*/
35	private $TableName = null;
36
37	/**
38	* Database handle
39	*
40	* @var resource
41	*/
42	private $DBHandle = null;
43
44	/**
45	* Prepared statement for a SQLite3 select query
46	*
47	* @var SQLite3Stmt
48	*/
49	private $selectQuery;
50
51	/**
52	* Prepared statement for a SQLite3 insert query
53	*
54	* @var SQLite3Stmt
55	*/
56	private $insertQuery;
57
58	/**
59	* Prepared statement for a SQLite3 update query
60	*
61	* @var SQLite3Stmt
62	*/
63	private $updateQuery;
64
65	/**
66	* Prepared statement for a SQLite3 delete query
67	*
68	* @var SQLite3Stmt
69	*/
70	private $deleteQuery;
71
72	/**
73	* Store cell data in cache for the current cell object if it's "dirty",
74	* and the 'nullify' the current cell object
75	*
76	* @return void
77	* @throws PHPExcel_Exception
78	*/
79	protected function storeData()
80	{
81	if ($this->currentCellIsDirty && !empty($this->currentObjectID)) {
82	$this->currentObject->detach();
83
84	$this->insertQuery->bindValue('id', $this->currentObjectID, SQLITE3_TEXT);
85	$this->insertQuery->bindValue('data', serialize($this->currentObject), SQLITE3_BLOB);
86	$result = $this->insertQuery->execute();
87	if ($result === false) {
88	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
89	}
90	$this->currentCellIsDirty = false;
91	}
92	$this->currentObjectID = $this->currentObject = null;
93	}
94
95	/**
96	* Add or Update a cell in cache identified by coordinate address
97	*
98	* @param string $pCoord Coordinate address of the cell to update
99	* @param PHPExcel_Cell $cell Cell to update
100	* @return PHPExcel_Cell
101	* @throws PHPExcel_Exception
102	*/
103	public function addCacheData($pCoord, PHPExcel_Cell $cell)
104	{
105	if (($pCoord !== $this->currentObjectID) && ($this->currentObjectID !== null)) {
106	$this->storeData();
107	}
108
109	$this->currentObjectID = $pCoord;
110	$this->currentObject = $cell;
111	$this->currentCellIsDirty = true;
112
113	return $cell;
114	}
115
116	/**
117	* Get cell at a specific coordinate
118	*
119	* @param string $pCoord Coordinate of the cell
120	* @throws PHPExcel_Exception
121	* @return PHPExcel_Cell Cell that was found, or null if not found
122	*/
123	public function getCacheData($pCoord)
124	{
125	if ($pCoord === $this->currentObjectID) {
126	return $this->currentObject;
127	}
128	$this->storeData();
129
130	$this->selectQuery->bindValue('id', $pCoord, SQLITE3_TEXT);
131	$cellResult = $this->selectQuery->execute();
132	if ($cellResult === false) {
133	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
134	}
135	$cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
136	if ($cellData === false) {
137	// Return null if requested entry doesn't exist in cache
138	return null;
139	}
140
141	// Set current entry to the requested entry
142	$this->currentObjectID = $pCoord;
143
144	$this->currentObject = unserialize($cellData['value']);
145	// Re-attach this as the cell's parent
146	$this->currentObject->attach($this);
147
148	// Return requested entry
149	return $this->currentObject;
150	}
151
152	/**
153	* Is a value set for an indexed cell?
154	*
155	* @param string $pCoord Coordinate address of the cell to check
156	* @return boolean
157	*/
158	public function isDataSet($pCoord)
159	{
160	if ($pCoord === $this->currentObjectID) {
161	return true;
162	}
163
164	// Check if the requested entry exists in the cache
165	$this->selectQuery->bindValue('id', $pCoord, SQLITE3_TEXT);
166	$cellResult = $this->selectQuery->execute();
167	if ($cellResult === false) {
168	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
169	}
170	$cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
171
172	return ($cellData === false) ? false : true;
173	}
174
175	/**
176	* Delete a cell in cache identified by coordinate address
177	*
178	* @param string $pCoord Coordinate address of the cell to delete
179	* @throws PHPExcel_Exception
180	*/
181	public function deleteCacheData($pCoord)
182	{
183	if ($pCoord === $this->currentObjectID) {
184	$this->currentObject->detach();
185	$this->currentObjectID = $this->currentObject = null;
186	}
187
188	// Check if the requested entry exists in the cache
189	$this->deleteQuery->bindValue('id', $pCoord, SQLITE3_TEXT);
190	$result = $this->deleteQuery->execute();
191	if ($result === false) {
192	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
193	}
194
195	$this->currentCellIsDirty = false;
196	}
197
198	/**
199	* Move a cell object from one address to another
200	*
201	* @param string $fromAddress Current address of the cell to move
202	* @param string $toAddress Destination address of the cell to move
203	* @return boolean
204	*/
205	public function moveCell($fromAddress, $toAddress)
206	{
207	if ($fromAddress === $this->currentObjectID) {
208	$this->currentObjectID = $toAddress;
209	}
210
211	$this->deleteQuery->bindValue('id', $toAddress, SQLITE3_TEXT);
212	$result = $this->deleteQuery->execute();
213	if ($result === false) {
214	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
215	}
216
217	$this->updateQuery->bindValue('toid', $toAddress, SQLITE3_TEXT);
218	$this->updateQuery->bindValue('fromid', $fromAddress, SQLITE3_TEXT);
219	$result = $this->updateQuery->execute();
220	if ($result === false) {
221	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
222	}
223
224	return true;
225	}
226
227	/**
228	* Get a list of all cell addresses currently held in cache
229	*
230	* @return string[]
231	*/
232	public function getCellList()
233	{
234	if ($this->currentObjectID !== null) {
235	$this->storeData();
236	}
237
238	$query = "SELECT id FROM kvp_".$this->TableName;
239	$cellIdsResult = $this->DBHandle->query($query);
240	if ($cellIdsResult === false) {
241	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
242	}
243
244	$cellKeys = array();
245	while ($row = $cellIdsResult->fetchArray(SQLITE3_ASSOC)) {
246	$cellKeys[] = $row['id'];
247	}
248
249	return $cellKeys;
250	}
251
252	/**
253	* Clone the cell collection
254	*
255	* @param PHPExcel_Worksheet $parent The new worksheet
256	* @return void
257	*/
258	public function copyCellCollection(PHPExcel_Worksheet $parent)
259	{
260	$this->currentCellIsDirty;
261	$this->storeData();
262
263	// Get a new id for the new table name
264	$tableName = str_replace('.', '_', $this->getUniqueID());
265	if (!$this->DBHandle->exec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
266	AS SELECT * FROM kvp_'.$this->TableName)
267	) {
268	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
269	}
270
271	// Copy the existing cell cache file
272	$this->TableName = $tableName;
273	}
274
275	/**
276	* Clear the cell collection and disconnect from our parent
277	*
278	* @return void
279	*/
280	public function unsetWorksheetCells()
281	{
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	}
292
293	/**
294	* Initialise this new cell collection
295	*
296	* @param PHPExcel_Worksheet $parent The worksheet for this cell collection
297	*/
298	public function __construct(PHPExcel_Worksheet $parent)
299	{
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	}
309	if (!$this->DBHandle->exec('CREATE TABLE kvp_'.$this->TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)')) {
310	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
311	}
312	}
313
314	$this->selectQuery = $this->DBHandle->prepare("SELECT value FROM kvp_".$this->TableName." WHERE id = :id");
315	$this->insertQuery = $this->DBHandle->prepare("INSERT OR REPLACE INTO kvp_".$this->TableName." VALUES(:id,:data)");
316	$this->updateQuery = $this->DBHandle->prepare("UPDATE kvp_".$this->TableName." SET id=:toId WHERE id=:fromId");
317	$this->deleteQuery = $this->DBHandle->prepare("DELETE FROM kvp_".$this->TableName." WHERE id = :id");
318	}
319
320	/**
321	* Destroy this cell collection
322	*/
323	public function __destruct()
324	{
325	if (!is_null($this->DBHandle)) {
326	$this->DBHandle->exec('DROP TABLE kvp_'.$this->TableName);
327	$this->DBHandle->close();
328	}
329	$this->DBHandle = null;
330	}
331
332	/**
333	* Identify whether the caching method is currently available
334	* Some methods are dependent on the availability of certain extensions being enabled in the PHP build
335	*
336	* @return boolean
337	*/
338	public static function cacheMethodIsAvailable()
339	{
340	if (!class_exists('SQLite3', false)) {
341	return false;
342	}
343
344	return true;
345	}
346	}
347