1 | <?php |
---|
2 | |
---|
3 | class DBCachedQuery |
---|
4 | { |
---|
5 | function DBCachedQuery() |
---|
6 | { |
---|
7 | $this->executed_ = false; |
---|
8 | $this->_cache = array(); |
---|
9 | $this->_cached = false; |
---|
10 | |
---|
11 | // this is the minimum runtime a query has to run to be |
---|
12 | // eligible for caching in seconds |
---|
13 | $this->_minruntime = 0.1; |
---|
14 | |
---|
15 | // maximum size of a cached result set (512kB) |
---|
16 | $this->_maxcachesize = 524288; |
---|
17 | $this->d = true; |
---|
18 | } |
---|
19 | |
---|
20 | function checkCache() |
---|
21 | { |
---|
22 | // only cache selects |
---|
23 | // we don't use select ... into so there is no problem |
---|
24 | $this->_sql = str_replace(array("\r\n", "\n"), ' ', $this->_sql); |
---|
25 | if (strtolower(substr($this->_sql, 0, 6)) != 'select' && strtolower(substr($this->_sql, 0, 4)) != 'show') |
---|
26 | { |
---|
27 | // this is no select, update the table |
---|
28 | $this->markAffectedTables(); |
---|
29 | return false; |
---|
30 | } |
---|
31 | |
---|
32 | if (file_exists(KB_CACHEDIR.'/qcache_qry_'.$this->_hash)) |
---|
33 | { |
---|
34 | $this->_mtime = filemtime(KB_CACHEDIR.'/qcache_qry_'.$this->_hash); |
---|
35 | if ($this->isCacheValid()) |
---|
36 | { |
---|
37 | return true; |
---|
38 | } |
---|
39 | } |
---|
40 | |
---|
41 | return false; |
---|
42 | } |
---|
43 | |
---|
44 | function parseSQL() |
---|
45 | { |
---|
46 | // gets all involved tables for a select statement |
---|
47 | $text = strtolower($this->_sql).' '; |
---|
48 | |
---|
49 | // we try to get the text from 'from' to 'where' because all involved |
---|
50 | // tables are declared in that part |
---|
51 | $from = strpos($text, 'from')+5; |
---|
52 | if (!$to = strpos($text, 'where')) |
---|
53 | { |
---|
54 | $to = strlen($text); |
---|
55 | } |
---|
56 | $parse = trim(substr($text, $from, $to-$from)); |
---|
57 | |
---|
58 | $tables = array(); |
---|
59 | if (strpos($parse, ',') !== false) |
---|
60 | { |
---|
61 | // , is a synonym for join so we'll replace them |
---|
62 | $parse = str_replace(',', ' join ', $parse); |
---|
63 | } |
---|
64 | |
---|
65 | $parse = 'join '.$parse; |
---|
66 | if (strpos($parse, 'join')) |
---|
67 | { |
---|
68 | // if this query is a join we parse it with regexp to get all tables |
---|
69 | preg_match_all('/join (.*?) /', $parse, $match); |
---|
70 | $tables = $match[1]; |
---|
71 | } |
---|
72 | else |
---|
73 | { |
---|
74 | // no join so it is hopefully a simple table select |
---|
75 | $tables[] = $parse; |
---|
76 | } |
---|
77 | |
---|
78 | $this->_usedtables = $tables; |
---|
79 | } |
---|
80 | |
---|
81 | function isCacheValid() |
---|
82 | { |
---|
83 | // check if cachefiles are stil valid |
---|
84 | |
---|
85 | // first, we need to get all involved tables |
---|
86 | $this->parseSQL(); |
---|
87 | |
---|
88 | foreach ($this->_usedtables as $table) |
---|
89 | { |
---|
90 | $file = KB_CACHEDIR.'/qcache_tbl_'.trim($table); |
---|
91 | if (file_exists($file)) |
---|
92 | { |
---|
93 | // if one of the tables is outdated, the query is outdated |
---|
94 | if ($this->_mtime < filemtime($file)) |
---|
95 | { |
---|
96 | return false; |
---|
97 | } |
---|
98 | } |
---|
99 | } |
---|
100 | return true; |
---|
101 | } |
---|
102 | |
---|
103 | function markAffectedTables() |
---|
104 | { |
---|
105 | // this function invalidates cache files for touched tables |
---|
106 | $text = trim(strtolower($this->_sql)); |
---|
107 | $text = str_replace(array('ignore','`', "\r\n", "\n"), '', $text); |
---|
108 | $ta = preg_split('/ /', $text, 0, PREG_SPLIT_NO_EMPTY); |
---|
109 | |
---|
110 | // check for sql keywords and get the table from the appropriate position |
---|
111 | $tables = array(); |
---|
112 | if ($ta[0] == 'update') |
---|
113 | { |
---|
114 | $tables[] = $ta[1]; |
---|
115 | } |
---|
116 | elseif ($ta[0] == 'insert') |
---|
117 | { |
---|
118 | $tables[] = $ta[2]; |
---|
119 | } |
---|
120 | elseif ($ta[0] == 'replace') |
---|
121 | { |
---|
122 | $tables[] = $ta[2]; |
---|
123 | } |
---|
124 | elseif ($ta[0] == 'delete') |
---|
125 | { |
---|
126 | $tables[] = $ta[2]; |
---|
127 | } |
---|
128 | elseif ($ta[0] == 'alter') |
---|
129 | { |
---|
130 | return false; |
---|
131 | } |
---|
132 | elseif ($ta[0] == 'create') |
---|
133 | { |
---|
134 | return false; |
---|
135 | } |
---|
136 | else |
---|
137 | { |
---|
138 | var_dump($ta); |
---|
139 | trigger_error('No suitable handler for query found.',E_USER_WARNING); |
---|
140 | return false; |
---|
141 | } |
---|
142 | |
---|
143 | foreach ($tables as $table) |
---|
144 | { |
---|
145 | $file = KB_CACHEDIR.'/qcache_tbl_'.$table; |
---|
146 | touch($file); |
---|
147 | } |
---|
148 | // refresh php's filestatcache so we dont get wrong timestamps on changed files |
---|
149 | clearstatcache(); |
---|
150 | } |
---|
151 | |
---|
152 | function genCache() |
---|
153 | { |
---|
154 | // this function fetches all rows and writes the data into a textfile |
---|
155 | |
---|
156 | // don't attemp to cache updates! |
---|
157 | if (strtolower(substr($this->_sql, 0, 6)) != 'select' && strtolower(substr($this->_sql, 0, 4)) != 'show') |
---|
158 | { |
---|
159 | return false; |
---|
160 | } |
---|
161 | |
---|
162 | $bsize = 0; |
---|
163 | while ($row = $this->getRow()) |
---|
164 | { |
---|
165 | $this->_cache[] = $row; |
---|
166 | |
---|
167 | // if the bytesize of the table exceeds the limit we'll abort |
---|
168 | // the cache generation and leave this query unbuffered |
---|
169 | $bsize += join('', $row); |
---|
170 | if ($bsize > $this->_maxcachesize) |
---|
171 | { |
---|
172 | $this->_cache[] = array(); |
---|
173 | $this->_cached = false; |
---|
174 | $this->rewind(); |
---|
175 | return false; |
---|
176 | } |
---|
177 | } |
---|
178 | |
---|
179 | // write data into textfile |
---|
180 | file_put_contents(KB_CACHEDIR.'/qcache_qry_'.$this->_hash, serialize($this->_cache)); |
---|
181 | |
---|
182 | $this->_cached = true; |
---|
183 | $this->_currrow = 0; |
---|
184 | $this->executed_ = true; |
---|
185 | } |
---|
186 | |
---|
187 | function loadCache() |
---|
188 | { |
---|
189 | // loads the cachefile into the memory |
---|
190 | $this->_cache = unserialize(file_get_contents(KB_CACHEDIR.'/qcache_qry_'.$this->_hash)); |
---|
191 | |
---|
192 | $this->_cached = true; |
---|
193 | $this->_currrow = 0; |
---|
194 | $this->executed_ = true; |
---|
195 | } |
---|
196 | |
---|
197 | function execute($sql) |
---|
198 | { |
---|
199 | $this->_sql = trim($sql); |
---|
200 | $this->_hash = md5($this->_sql); |
---|
201 | $this->_cache = array(); |
---|
202 | $this->_cached = false; |
---|
203 | |
---|
204 | if ($this->checkCache()) |
---|
205 | { |
---|
206 | $this->loadCache(); |
---|
207 | $this->queryCachedCount(true); |
---|
208 | return true; |
---|
209 | } |
---|
210 | |
---|
211 | // we got no or no valid cache so open the connection and run the query |
---|
212 | $this->dbconn_ = new DBConnection; |
---|
213 | |
---|
214 | $t1 = strtok(microtime(), ' ') + strtok(''); |
---|
215 | |
---|
216 | $this->resid_ = mysql_query($sql, $this->dbconn_->id()); |
---|
217 | |
---|
218 | if ($this->resid_ == false) |
---|
219 | { |
---|
220 | if (DB_HALTONERROR === true) |
---|
221 | { |
---|
222 | echo "Database error: ".mysql_error($this->dbconn_->id())."<br/>"; |
---|
223 | echo "SQL: ".$this->_sql."<br/>"; |
---|
224 | exit; |
---|
225 | } |
---|
226 | else |
---|
227 | { |
---|
228 | return false; |
---|
229 | } |
---|
230 | } |
---|
231 | |
---|
232 | $this->exectime_ = strtok(microtime(), ' ') + strtok('') - $t1; |
---|
233 | $this->executed_ = true; |
---|
234 | |
---|
235 | if (KB_PROFILE == 2) |
---|
236 | { |
---|
237 | file_put_contents('/tmp/profile.lst', $sql."\nExecution time: ".$this->exectime_."\n", FILE_APPEND); |
---|
238 | } |
---|
239 | |
---|
240 | // if the query was too slow we'll fetch all rows and run it cached |
---|
241 | if ($this->exectime_ > $this->_minruntime) |
---|
242 | { |
---|
243 | $this->genCache(); |
---|
244 | } |
---|
245 | |
---|
246 | $this->queryCount(true); |
---|
247 | return true; |
---|
248 | } |
---|
249 | |
---|
250 | function queryCount($increase = false) |
---|
251 | { |
---|
252 | static $count; |
---|
253 | |
---|
254 | if ($increase) |
---|
255 | { |
---|
256 | $count++; |
---|
257 | } |
---|
258 | |
---|
259 | return $count; |
---|
260 | } |
---|
261 | |
---|
262 | function queryCachedCount($increase = false) |
---|
263 | { |
---|
264 | static $count; |
---|
265 | |
---|
266 | if ($increase) |
---|
267 | { |
---|
268 | $count++; |
---|
269 | } |
---|
270 | |
---|
271 | return $count; |
---|
272 | } |
---|
273 | |
---|
274 | function recordCount() |
---|
275 | { |
---|
276 | if ($this->_cached) |
---|
277 | { |
---|
278 | return count($this->_cache); |
---|
279 | } |
---|
280 | return mysql_num_rows($this->resid_); |
---|
281 | } |
---|
282 | |
---|
283 | function getRow() |
---|
284 | { |
---|
285 | if ($this->_cached) |
---|
286 | { |
---|
287 | if (!isset($this->_cache[$this->_currrow])) |
---|
288 | { |
---|
289 | return false; |
---|
290 | } |
---|
291 | // return the current row and increase the pointer by one |
---|
292 | return $this->_cache[$this->_currrow++]; |
---|
293 | } |
---|
294 | if (is_resource($this->resid_)) |
---|
295 | { |
---|
296 | return mysql_fetch_assoc($this->resid_); |
---|
297 | } |
---|
298 | return false; |
---|
299 | } |
---|
300 | |
---|
301 | function rewind() |
---|
302 | { |
---|
303 | if ($this->_cached) |
---|
304 | { |
---|
305 | $this->_currrow = 0; |
---|
306 | } |
---|
307 | @mysql_data_seek($this->resid_, 0); |
---|
308 | } |
---|
309 | |
---|
310 | function getInsertID() |
---|
311 | { |
---|
312 | return mysql_insert_id(); |
---|
313 | } |
---|
314 | |
---|
315 | function execTime() |
---|
316 | { |
---|
317 | return $this->exectime_; |
---|
318 | } |
---|
319 | |
---|
320 | function executed() |
---|
321 | { |
---|
322 | return $this->executed_; |
---|
323 | } |
---|
324 | |
---|
325 | function getErrorMsg() |
---|
326 | { |
---|
327 | $msg = $this->sql_."<br>"; |
---|
328 | $msg .= "Query failed. ".mysql_error($this->dbconn_->id()); |
---|
329 | |
---|
330 | return $msg; |
---|
331 | } |
---|
332 | } |
---|
333 | ?> |
---|