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

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

308 lines 10.1 KB

Raw Download Zip

1	<?php
2
3	/**
4	* PHPExcel_CachedObjectStorage_SQLite
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_SQLite 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	* Store cell data in cache for the current cell object if it's "dirty",
46	* and the 'nullify' the current cell object
47	*
48	* @return void
49	* @throws PHPExcel_Exception
50	*/
51	protected function storeData()
52	{
53	if ($this->currentCellIsDirty && !empty($this->currentObjectID)) {
54	$this->currentObject->detach();
55
56	if (!$this->DBHandle->queryExec("INSERT OR REPLACE INTO kvp_".$this->TableName." VALUES('".$this->currentObjectID."','".sqlite_escape_string(serialize($this->currentObject))."')")) {
57	throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
58	}
59	$this->currentCellIsDirty = false;
60	}
61	$this->currentObjectID = $this->currentObject = null;
62	}
63
64	/**
65	* Add or Update a cell in cache identified by coordinate address
66	*
67	* @param string $pCoord Coordinate address of the cell to update
68	* @param PHPExcel_Cell $cell Cell to update
69	* @return PHPExcel_Cell
70	* @throws PHPExcel_Exception
71	*/
72	public function addCacheData($pCoord, PHPExcel_Cell $cell)
73	{
74	if (($pCoord !== $this->currentObjectID) && ($this->currentObjectID !== null)) {
75	$this->storeData();
76	}
77
78	$this->currentObjectID = $pCoord;
79	$this->currentObject = $cell;
80	$this->currentCellIsDirty = true;
81
82	return $cell;
83	}
84
85	/**
86	* Get cell at a specific coordinate
87	*
88	* @param string $pCoord Coordinate of the cell
89	* @throws PHPExcel_Exception
90	* @return PHPExcel_Cell Cell that was found, or null if not found
91	*/
92	public function getCacheData($pCoord)
93	{
94	if ($pCoord === $this->currentObjectID) {
95	return $this->currentObject;
96	}
97	$this->storeData();
98
99	$query = "SELECT value FROM kvp_".$this->TableName." WHERE id='".$pCoord."'";
100	$cellResultSet = $this->DBHandle->query($query, SQLITE_ASSOC);
101	if ($cellResultSet === false) {
102	throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
103	} elseif ($cellResultSet->numRows() == 0) {
104	// Return null if requested entry doesn't exist in cache
105	return null;
106	}
107
108	// Set current entry to the requested entry
109	$this->currentObjectID = $pCoord;
110
111	$cellResult = $cellResultSet->fetchSingle();
112	$this->currentObject = unserialize($cellResult);
113	// Re-attach this as the cell's parent
114	$this->currentObject->attach($this);
115
116	// Return requested entry
117	return $this->currentObject;
118	}
119
120	/**
121	* Is a value set for an indexed cell?
122	*
123	* @param string $pCoord Coordinate address of the cell to check
124	* @return boolean
125	*/
126	public function isDataSet($pCoord)
127	{
128	if ($pCoord === $this->currentObjectID) {
129	return true;
130	}
131
132	// Check if the requested entry exists in the cache
133	$query = "SELECT id FROM kvp_".$this->TableName." WHERE id='".$pCoord."'";
134	$cellResultSet = $this->DBHandle->query($query, SQLITE_ASSOC);
135	if ($cellResultSet === false) {
136	throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
137	} elseif ($cellResultSet->numRows() == 0) {
138	// Return null if requested entry doesn't exist in cache
139	return false;
140	}
141	return true;
142	}
143
144	/**
145	* Delete a cell in cache identified by coordinate address
146	*
147	* @param string $pCoord Coordinate address of the cell to delete
148	* @throws PHPExcel_Exception
149	*/
150	public function deleteCacheData($pCoord)
151	{
152	if ($pCoord === $this->currentObjectID) {
153	$this->currentObject->detach();
154	$this->currentObjectID = $this->currentObject = null;
155	}
156
157	// Check if the requested entry exists in the cache
158	$query = "DELETE FROM kvp_".$this->TableName." WHERE id='".$pCoord."'";
159	if (!$this->DBHandle->queryExec($query)) {
160	throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
161	}
162
163	$this->currentCellIsDirty = false;
164	}
165
166	/**
167	* Move a cell object from one address to another
168	*
169	* @param string $fromAddress Current address of the cell to move
170	* @param string $toAddress Destination address of the cell to move
171	* @return boolean
172	*/
173	public function moveCell($fromAddress, $toAddress)
174	{
175	if ($fromAddress === $this->currentObjectID) {
176	$this->currentObjectID = $toAddress;
177	}
178
179	$query = "DELETE FROM kvp_".$this->TableName." WHERE id='".$toAddress."'";
180	$result = $this->DBHandle->exec($query);
181	if ($result === false) {
182	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
183	}
184
185	$query = "UPDATE kvp_".$this->TableName." SET id='".$toAddress."' WHERE id='".$fromAddress."'";
186	$result = $this->DBHandle->exec($query);
187	if ($result === false) {
188	throw new PHPExcel_Exception($this->DBHandle->lastErrorMsg());
189	}
190
191	return true;
192	}
193
194	/**
195	* Get a list of all cell addresses currently held in cache
196	*
197	* @return string[]
198	*/
199	public function getCellList()
200	{
201	if ($this->currentObjectID !== null) {
202	$this->storeData();
203	}
204
205	$query = "SELECT id FROM kvp_".$this->TableName;
206	$cellIdsResult = $this->DBHandle->unbufferedQuery($query, SQLITE_ASSOC);
207	if ($cellIdsResult === false) {
208	throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
209	}
210
211	$cellKeys = array();
212	foreach ($cellIdsResult as $row) {
213	$cellKeys[] = $row['id'];
214	}
215
216	return $cellKeys;
217	}
218
219	/**
220	* Clone the cell collection
221	*
222	* @param PHPExcel_Worksheet $parent The new worksheet
223	* @return void
224	*/
225	public function copyCellCollection(PHPExcel_Worksheet $parent)
226	{
227	$this->currentCellIsDirty;
228	$this->storeData();
229
230	// Get a new id for the new table name
231	$tableName = str_replace('.', '_', $this->getUniqueID());
232	if (!$this->DBHandle->queryExec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
233	AS SELECT * FROM kvp_'.$this->TableName)
234	) {
235	throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
236	}
237
238	// Copy the existing cell cache file
239	$this->TableName = $tableName;
240	}
241
242	/**
243	* Clear the cell collection and disconnect from our parent
244	*
245	* @return void
246	*/
247	public function unsetWorksheetCells()
248	{
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	}
259
260	/**
261	* Initialise this new cell collection
262	*
263	* @param PHPExcel_Worksheet $parent The worksheet for this cell collection
264	*/
265	public function __construct(PHPExcel_Worksheet $parent)
266	{
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	}
276	if (!$this->DBHandle->queryExec('CREATE TABLE kvp_'.$this->TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)')) {
277	throw new PHPExcel_Exception(sqlite_error_string($this->DBHandle->lastError()));
278	}
279	}
280	}
281
282	/**
283	* Destroy this cell collection
284	*/
285	public function __destruct()
286	{
287	if (!is_null($this->DBHandle)) {
288	$this->DBHandle->queryExec('DROP TABLE kvp_'.$this->TableName);
289	}
290	$this->DBHandle = null;
291	}
292
293	/**
294	* Identify whether the caching method is currently available
295	* Some methods are dependent on the availability of certain extensions being enabled in the PHP build
296	*
297	* @return boolean
298	*/
299	public static function cacheMethodIsAvailable()
300	{
301	if (!function_exists('sqlite_open')) {
302	return false;
303	}
304
305	return true;
306	}
307	}
308