source: pro-violet-viettel/www/deploy/20150304/application/libraries/PHPExcel/CachedObjectStorage/SQLite3.php

Last change on this file was 780, checked in by dungnv, 10 years ago
File size: 10.4 KB
RevLine 
[780]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    1.8.0, 2014-03-02
26 */
27
28
29/**
30 * PHPExcel_CachedObjectStorage_SQLite3
31 *
32 * @category   PHPExcel
33 * @package    PHPExcel_CachedObjectStorage
34 * @copyright  Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
35 */
36class PHPExcel_CachedObjectStorage_SQLite3 extends PHPExcel_CachedObjectStorage_CacheBase implements PHPExcel_CachedObjectStorage_ICache {
37
38        /**
39         * Database table name
40         *
41         * @var string
42         */
43        private $_TableName = null;
44
45        /**
46         * Database handle
47         *
48         * @var resource
49         */
50        private $_DBHandle = null;
51
52        /**
53         * Prepared statement for a SQLite3 select query
54         *
55         * @var SQLite3Stmt
56         */
57        private $_selectQuery;
58
59        /**
60         * Prepared statement for a SQLite3 insert query
61         *
62         * @var SQLite3Stmt
63         */
64        private $_insertQuery;
65
66        /**
67         * Prepared statement for a SQLite3 update query
68         *
69         * @var SQLite3Stmt
70         */
71        private $_updateQuery;
72
73        /**
74         * Prepared statement for a SQLite3 delete query
75         *
76         * @var SQLite3Stmt
77         */
78        private $_deleteQuery;
79
80    /**
81     * Store cell data in cache for the current cell object if it's "dirty",
82     *     and the 'nullify' the current cell object
83     *
84         * @return      void
85     * @throws  PHPExcel_Exception
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        }       //      function _storeData()
100
101
102    /**
103     * Add or Update a cell in cache identified by coordinate address
104     *
105     * @param   string                  $pCoord         Coordinate address of the cell to update
106     * @param   PHPExcel_Cell   $cell           Cell to update
107         * @return      void
108     * @throws  PHPExcel_Exception
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        }       //      function addCacheData()
121
122
123    /**
124     * Get cell at a specific coordinate
125     *
126     * @param   string                  $pCoord         Coordinate of the cell
127     * @throws  PHPExcel_Exception
128     * @return  PHPExcel_Cell   Cell that was found, or null if not found
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                        //      Return null if requested entry doesn't exist in cache
144                        return NULL;
145                }
146
147                //      Set current entry to the requested entry
148                $this->_currentObjectID = $pCoord;
149
150                $this->_currentObject = unserialize($cellData['value']);
151        //    Re-attach this as the cell's parent
152        $this->_currentObject->attach($this);
153
154                //      Return requested entry
155                return $this->_currentObject;
156        }       //      function getCacheData()
157
158
159        /**
160         *      Is a value set for an indexed cell?
161         *
162         * @param       string          $pCoord         Coordinate address of the cell to check
163         * @return      boolean
164         */
165        public function isDataSet($pCoord) {
166                if ($pCoord === $this->_currentObjectID) {
167                        return TRUE;
168                }
169
170                //      Check if the requested entry exists in the cache
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        }       //      function isDataSet()
180
181
182    /**
183     *  Delete a cell in cache identified by coordinate address
184     *
185     * @param   string                  $pCoord         Coordinate address of the cell to delete
186     * @throws  PHPExcel_Exception
187     */
188        public function deleteCacheData($pCoord) {
189                if ($pCoord === $this->_currentObjectID) {
190                        $this->_currentObject->detach();
191                        $this->_currentObjectID = $this->_currentObject = NULL;
192                }
193
194                //      Check if the requested entry exists in the cache
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        }       //      function deleteCacheData()
202
203
204        /**
205         * Move a cell object from one address to another
206         *
207         * @param       string          $fromAddress    Current address of the cell to move
208         * @param       string          $toAddress              Destination address of the cell to move
209         * @return      boolean
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        }       //      function moveCell()
229
230
231        /**
232         * Get a list of all cell addresses currently held in cache
233         *
234         * @return      array of string
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        }       //      function getCellList()
253
254
255        /**
256         * Clone the cell collection
257         *
258         * @param       PHPExcel_Worksheet      $parent         The new worksheet
259         * @return      void
260         */
261        public function copyCellCollection(PHPExcel_Worksheet $parent) {
262                $this->_currentCellIsDirty;
263        $this->_storeData();
264
265                //      Get a new id for the new table name
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                //      Copy the existing cell cache file
272                $this->_TableName = $tableName;
273        }       //      function copyCellCollection()
274
275
276        /**
277         * Clear the cell collection and disconnect from our parent
278         *
279         * @return      void
280         */
281        public function unsetWorksheetCells() {
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        }       //      function unsetWorksheetCells()
292
293
294        /**
295         * Initialise this new cell collection
296         *
297         * @param       PHPExcel_Worksheet      $parent         The worksheet for this cell collection
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        }       //      function __construct()
317
318
319        /**
320         * Destroy this cell collection
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        }       //      function __destruct()
329
330
331        /**
332         * Identify whether the caching method is currently available
333         * Some methods are dependent on the availability of certain extensions being enabled in the PHP build
334         *
335         * @return      boolean
336         */
337        public static function cacheMethodIsAvailable() {
338                if (!class_exists('SQLite3',FALSE)) {
339                        return false;
340                }
341
342                return true;
343        }
344
345}
Note: See TracBrowser for help on using the repository browser.