056955d1d30277db2fa64faf3db95aa7ea454825.svn-base 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345
  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. * PHPExcel_CachedObjectStorage_SQLite3
  29. *
  30. * @category PHPExcel
  31. * @package PHPExcel_CachedObjectStorage
  32. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  33. */
  34. class PHPExcel_CachedObjectStorage_SQLite3 extends PHPExcel_CachedObjectStorage_CacheBase implements PHPExcel_CachedObjectStorage_ICache {
  35. /**
  36. * Database table name
  37. *
  38. * @var string
  39. */
  40. private $_TableName = null;
  41. /**
  42. * Database handle
  43. *
  44. * @var resource
  45. */
  46. private $_DBHandle = null;
  47. /**
  48. * Prepared statement for a SQLite3 select query
  49. *
  50. * @var SQLite3Stmt
  51. */
  52. private $_selectQuery;
  53. /**
  54. * Prepared statement for a SQLite3 insert query
  55. *
  56. * @var SQLite3Stmt
  57. */
  58. private $_insertQuery;
  59. /**
  60. * Prepared statement for a SQLite3 update query
  61. *
  62. * @var SQLite3Stmt
  63. */
  64. private $_updateQuery;
  65. /**
  66. * Prepared statement for a SQLite3 delete query
  67. *
  68. * @var SQLite3Stmt
  69. */
  70. private $_deleteQuery;
  71. /**
  72. * Store cell data in cache for the current cell object if it's "dirty",
  73. * and the 'nullify' the current cell object
  74. *
  75. * @return void
  76. * @throws PHPExcel_Exception
  77. */
  78. protected function _storeData() {
  79. if ($this->_currentCellIsDirty && !empty($this->_currentObjectID)) {
  80. $this->_currentObject->detach();
  81. $this->_insertQuery->bindValue('id',$this->_currentObjectID,SQLITE3_TEXT);
  82. $this->_insertQuery->bindValue('data',serialize($this->_currentObject),SQLITE3_BLOB);
  83. $result = $this->_insertQuery->execute();
  84. if ($result === false)
  85. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  86. $this->_currentCellIsDirty = false;
  87. }
  88. $this->_currentObjectID = $this->_currentObject = null;
  89. } // function _storeData()
  90. /**
  91. * Add or Update a cell in cache identified by coordinate address
  92. *
  93. * @param string $pCoord Coordinate address of the cell to update
  94. * @param PHPExcel_Cell $cell Cell to update
  95. * @return PHPExcel_Cell
  96. * @throws PHPExcel_Exception
  97. */
  98. public function addCacheData($pCoord, PHPExcel_Cell $cell) {
  99. if (($pCoord !== $this->_currentObjectID) && ($this->_currentObjectID !== null)) {
  100. $this->_storeData();
  101. }
  102. $this->_currentObjectID = $pCoord;
  103. $this->_currentObject = $cell;
  104. $this->_currentCellIsDirty = true;
  105. return $cell;
  106. } // function addCacheData()
  107. /**
  108. * Get cell at a specific coordinate
  109. *
  110. * @param string $pCoord Coordinate of the cell
  111. * @throws PHPExcel_Exception
  112. * @return PHPExcel_Cell Cell that was found, or null if not found
  113. */
  114. public function getCacheData($pCoord) {
  115. if ($pCoord === $this->_currentObjectID) {
  116. return $this->_currentObject;
  117. }
  118. $this->_storeData();
  119. $this->_selectQuery->bindValue('id',$pCoord,SQLITE3_TEXT);
  120. $cellResult = $this->_selectQuery->execute();
  121. if ($cellResult === FALSE) {
  122. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  123. }
  124. $cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
  125. if ($cellData === FALSE) {
  126. // Return null if requested entry doesn't exist in cache
  127. return NULL;
  128. }
  129. // Set current entry to the requested entry
  130. $this->_currentObjectID = $pCoord;
  131. $this->_currentObject = unserialize($cellData['value']);
  132. // Re-attach this as the cell's parent
  133. $this->_currentObject->attach($this);
  134. // Return requested entry
  135. return $this->_currentObject;
  136. } // function getCacheData()
  137. /**
  138. * Is a value set for an indexed cell?
  139. *
  140. * @param string $pCoord Coordinate address of the cell to check
  141. * @return boolean
  142. */
  143. public function isDataSet($pCoord) {
  144. if ($pCoord === $this->_currentObjectID) {
  145. return TRUE;
  146. }
  147. // Check if the requested entry exists in the cache
  148. $this->_selectQuery->bindValue('id',$pCoord,SQLITE3_TEXT);
  149. $cellResult = $this->_selectQuery->execute();
  150. if ($cellResult === FALSE) {
  151. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  152. }
  153. $cellData = $cellResult->fetchArray(SQLITE3_ASSOC);
  154. return ($cellData === FALSE) ? FALSE : TRUE;
  155. } // function isDataSet()
  156. /**
  157. * Delete a cell in cache identified by coordinate address
  158. *
  159. * @param string $pCoord Coordinate address of the cell to delete
  160. * @throws PHPExcel_Exception
  161. */
  162. public function deleteCacheData($pCoord) {
  163. if ($pCoord === $this->_currentObjectID) {
  164. $this->_currentObject->detach();
  165. $this->_currentObjectID = $this->_currentObject = NULL;
  166. }
  167. // Check if the requested entry exists in the cache
  168. $this->_deleteQuery->bindValue('id',$pCoord,SQLITE3_TEXT);
  169. $result = $this->_deleteQuery->execute();
  170. if ($result === FALSE)
  171. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  172. $this->_currentCellIsDirty = FALSE;
  173. } // function deleteCacheData()
  174. /**
  175. * Move a cell object from one address to another
  176. *
  177. * @param string $fromAddress Current address of the cell to move
  178. * @param string $toAddress Destination address of the cell to move
  179. * @return boolean
  180. */
  181. public function moveCell($fromAddress, $toAddress) {
  182. if ($fromAddress === $this->_currentObjectID) {
  183. $this->_currentObjectID = $toAddress;
  184. }
  185. $this->_deleteQuery->bindValue('id',$toAddress,SQLITE3_TEXT);
  186. $result = $this->_deleteQuery->execute();
  187. if ($result === false)
  188. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  189. $this->_updateQuery->bindValue('toid',$toAddress,SQLITE3_TEXT);
  190. $this->_updateQuery->bindValue('fromid',$fromAddress,SQLITE3_TEXT);
  191. $result = $this->_updateQuery->execute();
  192. if ($result === false)
  193. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  194. return TRUE;
  195. } // function moveCell()
  196. /**
  197. * Get a list of all cell addresses currently held in cache
  198. *
  199. * @return string[]
  200. */
  201. public function getCellList() {
  202. if ($this->_currentObjectID !== null) {
  203. $this->_storeData();
  204. }
  205. $query = "SELECT id FROM kvp_".$this->_TableName;
  206. $cellIdsResult = $this->_DBHandle->query($query);
  207. if ($cellIdsResult === false)
  208. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  209. $cellKeys = array();
  210. while ($row = $cellIdsResult->fetchArray(SQLITE3_ASSOC)) {
  211. $cellKeys[] = $row['id'];
  212. }
  213. return $cellKeys;
  214. } // function getCellList()
  215. /**
  216. * Clone the cell collection
  217. *
  218. * @param PHPExcel_Worksheet $parent The new worksheet
  219. * @return void
  220. */
  221. public function copyCellCollection(PHPExcel_Worksheet $parent) {
  222. $this->_currentCellIsDirty;
  223. $this->_storeData();
  224. // Get a new id for the new table name
  225. $tableName = str_replace('.','_',$this->_getUniqueID());
  226. if (!$this->_DBHandle->exec('CREATE TABLE kvp_'.$tableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)
  227. AS SELECT * FROM kvp_'.$this->_TableName))
  228. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  229. // Copy the existing cell cache file
  230. $this->_TableName = $tableName;
  231. } // function copyCellCollection()
  232. /**
  233. * Clear the cell collection and disconnect from our parent
  234. *
  235. * @return void
  236. */
  237. public function unsetWorksheetCells() {
  238. if(!is_null($this->_currentObject)) {
  239. $this->_currentObject->detach();
  240. $this->_currentObject = $this->_currentObjectID = null;
  241. }
  242. // detach ourself from the worksheet, so that it can then delete this object successfully
  243. $this->_parent = null;
  244. // Close down the temporary cache file
  245. $this->__destruct();
  246. } // function unsetWorksheetCells()
  247. /**
  248. * Initialise this new cell collection
  249. *
  250. * @param PHPExcel_Worksheet $parent The worksheet for this cell collection
  251. */
  252. public function __construct(PHPExcel_Worksheet $parent) {
  253. parent::__construct($parent);
  254. if (is_null($this->_DBHandle)) {
  255. $this->_TableName = str_replace('.','_',$this->_getUniqueID());
  256. $_DBName = ':memory:';
  257. $this->_DBHandle = new SQLite3($_DBName);
  258. if ($this->_DBHandle === false)
  259. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  260. if (!$this->_DBHandle->exec('CREATE TABLE kvp_'.$this->_TableName.' (id VARCHAR(12) PRIMARY KEY, value BLOB)'))
  261. throw new PHPExcel_Exception($this->_DBHandle->lastErrorMsg());
  262. }
  263. $this->_selectQuery = $this->_DBHandle->prepare("SELECT value FROM kvp_".$this->_TableName." WHERE id = :id");
  264. $this->_insertQuery = $this->_DBHandle->prepare("INSERT OR REPLACE INTO kvp_".$this->_TableName." VALUES(:id,:data)");
  265. $this->_updateQuery = $this->_DBHandle->prepare("UPDATE kvp_".$this->_TableName." SET id=:toId WHERE id=:fromId");
  266. $this->_deleteQuery = $this->_DBHandle->prepare("DELETE FROM kvp_".$this->_TableName." WHERE id = :id");
  267. } // function __construct()
  268. /**
  269. * Destroy this cell collection
  270. */
  271. public function __destruct() {
  272. if (!is_null($this->_DBHandle)) {
  273. $this->_DBHandle->exec('DROP TABLE kvp_'.$this->_TableName);
  274. $this->_DBHandle->close();
  275. }
  276. $this->_DBHandle = null;
  277. } // function __destruct()
  278. /**
  279. * Identify whether the caching method is currently available
  280. * Some methods are dependent on the availability of certain extensions being enabled in the PHP build
  281. *
  282. * @return boolean
  283. */
  284. public static function cacheMethodIsAvailable() {
  285. if (!class_exists('SQLite3',FALSE)) {
  286. return false;
  287. }
  288. return true;
  289. }
  290. }