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