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_SQLite 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: 58:
59: protected function _storeData() {
60: if ($this->_currentCellIsDirty && !empty($this->_currentObjectID)) {
61: $this->_currentObject->detach();
62:
63: if (!$this->_DBHandle->queryExec("INSERT OR REPLACE INTO kvp_".$this->_TableName." VALUES('".$this->_currentObjectID."','".sqlite_escape_string(serialize($this->_currentObject))."')"))
64: throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
65: $this->_currentCellIsDirty = false;
66: }
67: $this->_currentObjectID = $this->_currentObject = null;
68: }
69:
70:
71: 72: 73: 74: 75: 76: 77: 78:
79: public function addCacheData($pCoord, PHPExcel_Cell $cell) {
80: if (($pCoord !== $this->_currentObjectID) && ($this->_currentObjectID !== null)) {
81: $this->_storeData();
82: }
83:
84: $this->_currentObjectID = $pCoord;
85: $this->_currentObject = $cell;
86: $this->_currentCellIsDirty = true;
87:
88: return $cell;
89: }
90:
91:
92: 93: 94: 95: 96: 97: 98:
99: public function getCacheData($pCoord) {
100: if ($pCoord === $this->_currentObjectID) {
101: return $this->_currentObject;
102: }
103: $this->_storeData();
104:
105: $query = "SELECT value FROM kvp_".$this->_TableName." WHERE id='".$pCoord."'";
106: $cellResultSet = $this->_DBHandle->query($query,SQLITE_ASSOC);
107: if ($cellResultSet === false) {
108: throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
109: } elseif ($cellResultSet->numRows() == 0) {
110:
111: return null;
112: }
113:
114:
115: $this->_currentObjectID = $pCoord;
116:
117: $cellResult = $cellResultSet->fetchSingle();
118: $this->_currentObject = unserialize($cellResult);
119:
120: $this->_currentObject->attach($this);
121:
122:
123: return $this->_currentObject;
124: }
125:
126:
127: 128: 129: 130: 131: 132:
133: public function isDataSet($pCoord) {
134: if ($pCoord === $this->_currentObjectID) {
135: return true;
136: }
137:
138:
139: $query = "SELECT id FROM kvp_".$this->_TableName." WHERE id='".$pCoord."'";
140: $cellResultSet = $this->_DBHandle->query($query,SQLITE_ASSOC);
141: if ($cellResultSet === false) {
142: throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
143: } elseif ($cellResultSet->numRows() == 0) {
144:
145: return false;
146: }
147: return true;
148: }
149:
150:
151: 152: 153: 154: 155: 156:
157: public function deleteCacheData($pCoord) {
158: if ($pCoord === $this->_currentObjectID) {
159: $this->_currentObject->detach();
160: $this->_currentObjectID = $this->_currentObject = null;
161: }
162:
163:
164: $query = "DELETE FROM kvp_".$this->_TableName." WHERE id='".$pCoord."'";
165: if (!$this->_DBHandle->queryExec($query))
166: throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
167:
168: $this->_currentCellIsDirty = false;
169: }
170:
171:
172: 173: 174: 175: 176: 177: 178:
179: public function moveCell($fromAddress, $toAddress) {
180: if ($fromAddress === $this->_currentObjectID) {
181: $this->_currentObjectID = $toAddress;
182: }
183:
184: $query = "DELETE FROM kvp_".$this->_TableName." WHERE id='".$toAddress."'";
185: $result = $this->_DBHandle->exec($query);
186: if ($result === false)
187: throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
188:
189: $query = "UPDATE kvp_".$this->_TableName." SET id='".$toAddress."' WHERE id='".$fromAddress."'";
190: $result = $this->_DBHandle->exec($query);
191: if ($result === false)
192: throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
193:
194: return TRUE;
195: }
196:
197:
198: 199: 200: 201: 202:
203: public function getCellList() {
204: if ($this->_currentObjectID !== null) {
205: $this->_storeData();
206: }
207:
208: $query = "SELECT id FROM kvp_".$this->_TableName;
209: $cellIdsResult = $this->_DBHandle->unbufferedQuery($query,SQLITE_ASSOC);
210: if ($cellIdsResult === false)
211: throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
212:
213: $cellKeys = array();
214: foreach($cellIdsResult as $row) {
215: $cellKeys[] = $row['id'];
216: }
217:
218: return $cellKeys;
219: }
220:
221:
222: 223: 224: 225: 226: 227:
228: public function copyCellCollection(PHPExcel_Worksheet $parent) {
229: $this->_currentCellIsDirty;
230: $this->_storeData();
231:
232:
233: $tableName = str_replace('.','_',$this->_getUniqueID());
234: if (!$this->_DBHandle->queryExec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
235: AS SELECT * FROM kvp_'.$this->_TableName))
236: throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
237:
238:
239: $this->_TableName = $tableName;
240: }
241:
242:
243: 244: 245: 246: 247:
248: public function unsetWorksheetCells() {
249: if(!is_null($this->_currentObject)) {
250: $this->_currentObject->detach();
251: $this->_currentObject = $this->_currentObjectID = null;
252: }
253:
254: $this->_parent = null;
255:
256:
257: $this->__destruct();
258: }
259:
260:
261: 262: 263: 264: 265:
266: public function __construct(PHPExcel_Worksheet $parent) {
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: if (!$this->_DBHandle->queryExec('CREATE TABLE kvp_'.$this->_TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)'))
276: throw new PHPExcel_Exception(sqlite_error_string($this->_DBHandle->lastError()));
277: }
278: }
279:
280:
281: 282: 283:
284: public function __destruct() {
285: if (!is_null($this->_DBHandle)) {
286: $this->_DBHandle->queryExec('DROP TABLE kvp_'.$this->_TableName);
287: }
288: $this->_DBHandle = null;
289: }
290:
291:
292: 293: 294: 295: 296: 297:
298: public static function cacheMethodIsAvailable() {
299: if (!function_exists('sqlite_open')) {
300: return false;
301: }
302:
303: return true;
304: }
305:
306: }
307: