1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29: 30: 31: 32: 33: 34: 35:
36: class PHPExcel_CachedObjectStorage_SQLite3 extends PHPExcel_CachedObjectStorage_CacheBase implements PHPExcel_CachedObjectStorage_ICache {
37:
38: 39: 40: 41: 42:
43: private $_TableName = null;
44:
45: 46: 47: 48: 49:
50: private $_DBHandle = null;
51:
52: 53: 54: 55: 56:
57: private $_selectQuery;
58:
59: 60: 61: 62: 63:
64: private $_insertQuery;
65:
66: 67: 68: 69: 70:
71: private $_updateQuery;
72:
73: 74: 75: 76: 77:
78: private $_deleteQuery;
79:
80: 81: 82: 83: 84: 85: 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: }
100:
101:
102: 103: 104: 105: 106: 107: 108: 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: }
121:
122:
123: 124: 125: 126: 127: 128: 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:
144: return NULL;
145: }
146:
147:
148: $this->_currentObjectID = $pCoord;
149:
150: $this->_currentObject = unserialize($cellData['value']);
151:
152: $this->_currentObject->attach($this);
153:
154:
155: return $this->_currentObject;
156: }
157:
158:
159: 160: 161: 162: 163: 164:
165: public function isDataSet($pCoord) {
166: if ($pCoord === $this->_currentObjectID) {
167: return TRUE;
168: }
169:
170:
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: }
180:
181:
182: 183: 184: 185: 186: 187:
188: public function deleteCacheData($pCoord) {
189: if ($pCoord === $this->_currentObjectID) {
190: $this->_currentObject->detach();
191: $this->_currentObjectID = $this->_currentObject = NULL;
192: }
193:
194:
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: }
202:
203:
204: 205: 206: 207: 208: 209: 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: }
229:
230:
231: 232: 233: 234: 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: }
253:
254:
255: 256: 257: 258: 259: 260:
261: public function copyCellCollection(PHPExcel_Worksheet $parent) {
262: $this->_currentCellIsDirty;
263: $this->_storeData();
264:
265:
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:
272: $this->_TableName = $tableName;
273: }
274:
275:
276: 277: 278: 279: 280:
281: public function unsetWorksheetCells() {
282: if(!is_null($this->_currentObject)) {
283: $this->_currentObject->detach();
284: $this->_currentObject = $this->_currentObjectID = null;
285: }
286:
287: $this->_parent = null;
288:
289:
290: $this->__destruct();
291: }
292:
293:
294: 295: 296: 297: 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: }
317:
318:
319: 320: 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: }
329:
330:
331: 332: 333: 334: 335: 336:
337: public static function cacheMethodIsAvailable() {
338: if (!class_exists('SQLite3',FALSE)) {
339: return false;
340: }
341:
342: return true;
343: }
344:
345: }
346: