1
|
|
|
<?php |
2
|
|
|
/* zLibrary |
3
|
|
|
* |
4
|
|
|
* This program is free software: you can redistribute it and/or modify |
5
|
|
|
* it under the terms of the GNU Affero General Public License as published by |
6
|
|
|
* the Free Software Foundation, either version 3 of the License, or |
7
|
|
|
* (at your option) any later version. |
8
|
|
|
* |
9
|
|
|
* This program is distributed in the hope that it will be useful, |
10
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
11
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
12
|
|
|
* GNU Affero General Public License for more details. |
13
|
|
|
* |
14
|
|
|
* You should have received a copy of the GNU Affero General Public License |
15
|
|
|
* along with this program. If not, see <http://www.gnu.org/licenses/>. |
16
|
|
|
*/ |
17
|
|
|
|
18
|
|
|
class Db |
19
|
|
|
{ |
20
|
|
|
/** |
21
|
|
|
* @var int Stores the number of Query executions and inserts |
22
|
|
|
*/ |
23
|
|
|
protected static $queryCount = 0; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* Creates and returns a PDO object. |
27
|
|
|
* |
28
|
|
|
* @static |
29
|
|
|
* @return PDO |
30
|
|
|
*/ |
31
|
|
|
protected static function getPDO() |
32
|
|
|
{ |
33
|
|
|
global $dbUser, $dbPassword, $dbName, $dbHost, $dbSocket, $dbPersist, $dbEmulatePrepares, $dbUseBufferedQuery; |
34
|
|
|
|
35
|
|
|
if($dbSocket) |
36
|
|
|
$dsn = "mysql:dbname=$dbName;unix_socket=$dbSocket"; |
37
|
|
|
else |
38
|
|
|
$dsn = "mysql:dbname=$dbName;host=$dbHost"; |
39
|
|
|
|
40
|
|
|
try |
41
|
|
|
{ |
42
|
|
|
$pdo = new PDO($dsn, $dbUser, $dbPassword, array( |
43
|
|
|
PDO::ATTR_PERSISTENT => $dbPersist, // Keep the connection open, so it can be reused |
44
|
|
|
PDO::ATTR_EMULATE_PREPARES => $dbEmulatePrepares, // Use native prepares, since they and the execution plan is cached in MySQL, and thus generate faster queries, but more garbled errors if we make any. |
45
|
|
|
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => $dbUseBufferedQuery, // Used buffered queries |
46
|
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Error mode |
47
|
|
|
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = \'+00:00\'' // Default to using UTC as timezone for all queries.. Since EVE is UTC, so should we be! |
48
|
|
|
) |
49
|
|
|
); |
50
|
|
|
} |
51
|
|
|
catch (Exception $e) |
52
|
|
|
{ |
53
|
|
|
Log::log("Unable to connect to the database: " . $e->getMessage()); |
54
|
|
|
throw new Exception("Unable to connect to database!"); |
55
|
|
|
} |
56
|
|
|
|
57
|
|
|
return $pdo; |
58
|
|
|
} |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* Executes an SQL query, returns the full result |
62
|
|
|
* |
63
|
|
|
* @static |
64
|
|
|
* @param string $query The query to be executed. |
65
|
|
|
* @param array $parameters (optional) A key/value array of parameters. |
66
|
|
|
* @param int $cacheTime The time, in seconds, to cache the result of the query. Default: 30 |
67
|
|
|
* @param bool selectCheck If true, does a strict check that the query is using a select. Default: true |
68
|
|
|
* @return array Returns the full resultset as an array. |
69
|
|
|
*/ |
70
|
|
|
public static function query($query, $parameters = array(), $cacheTime = 30, $selectCheck = true) |
|
|
|
|
71
|
|
|
{ |
72
|
|
|
// Sanity check |
73
|
|
|
if(strpos($query, ";") !== false) |
74
|
|
|
throw new Exception("Semicolons are not allowed in queries. Use parameters instead."); |
75
|
|
|
|
76
|
|
|
// Cache time of 0 seconds means skip all caches. and just do the query |
77
|
|
|
$key = self::getKey($query, $parameters); |
78
|
|
|
|
79
|
|
|
// If cache time is above 0 seconds, lets try and get it from that. |
80
|
|
|
if($cacheTime > 0) |
81
|
|
|
{ |
82
|
|
|
// Try the cache system |
83
|
|
|
$result = Cache::get($key); |
84
|
|
|
if($result !== FALSE) |
85
|
|
|
return $result; |
86
|
|
|
} |
87
|
|
|
|
88
|
|
|
try |
89
|
|
|
{ |
90
|
|
|
// Start the timer |
91
|
|
|
$timer = new Timer(); |
92
|
|
|
|
93
|
|
|
// Increment the queryCounter |
94
|
|
|
self::$queryCount++; |
95
|
|
|
|
96
|
|
|
// Open the databse connection |
97
|
|
|
$pdo = self::getPDO(); |
98
|
|
|
|
99
|
|
|
// Make sure PDO is set |
100
|
|
|
if($pdo == NULL) |
101
|
|
|
return; |
102
|
|
|
|
103
|
|
|
// add page to the query |
104
|
|
|
$requestURI = isset($_SERVER["REQUEST_URI"]) ? $_SERVER["REQUEST_URI"] : ""; |
105
|
|
|
$query = $query . " /* " . $requestURI . " */"; |
106
|
|
|
// Prepare the query |
107
|
|
|
$stmt = $pdo->prepare($query); |
108
|
|
|
|
109
|
|
|
// Execute the query, with the parameters |
110
|
|
|
$stmt->execute($parameters); |
111
|
|
|
|
112
|
|
|
// Check for errors |
113
|
|
|
if($stmt->errorCode() != 0) |
114
|
|
|
self::processError($stmt, $query, $parameters); |
115
|
|
|
|
116
|
|
|
// Fetch an associative array |
117
|
|
|
$result = $stmt->fetchAll(PDO::FETCH_ASSOC); |
118
|
|
|
|
119
|
|
|
// Close the cursor |
120
|
|
|
$stmt->closeCursor(); |
121
|
|
|
|
122
|
|
|
// Stop the timer |
123
|
|
|
$duration = $timer->stop(); |
124
|
|
|
|
125
|
|
|
// If cache time is above 0 seconds, lets store it in the cache. |
126
|
|
|
if($cacheTime > 0) |
127
|
|
|
Cache::set($key, $result, min(3600, $cacheTime)); // Store it in the cache system |
128
|
|
|
|
129
|
|
|
self::log($query, $parameters, $duration); |
130
|
|
|
|
131
|
|
|
// now to return the result |
132
|
|
|
return $result; |
133
|
|
|
} |
134
|
|
|
catch (Exception $e) |
135
|
|
|
{ |
136
|
|
|
// There was some sort of nasty nasty nasty error.. |
137
|
|
|
throw $e; |
138
|
|
|
} |
139
|
|
|
} |
140
|
|
|
|
141
|
|
|
/** |
142
|
|
|
* Executes an SQL query, and returns a single row |
143
|
|
|
* |
144
|
|
|
* @static |
145
|
|
|
* @param string $query The query to be executed |
146
|
|
|
* @param array $parameters (optional) A key/value array of parameters |
147
|
|
|
* @param int $cacheTime The time, in seconds, to cache the result of the query. Default: 30 |
148
|
|
|
* @return array Returns the first row of the result set. Returns an empty array if there are no rows. |
149
|
|
|
*/ |
150
|
|
|
public static function queryRow($query, $parameters = array(), $cacheTime = 30, $selectCheck = true) |
151
|
|
|
{ |
152
|
|
|
// Get the result |
153
|
|
|
$result = self::query($query, $parameters, $cacheTime, $selectCheck); |
154
|
|
|
|
155
|
|
|
// Figure out if it has more than one result and return it |
156
|
|
|
if(sizeof($result) >= 1) |
157
|
|
|
return $result[0]; |
158
|
|
|
|
159
|
|
|
// No results at all |
160
|
|
|
return array(); |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
/** |
164
|
|
|
* Executes an SQL query, and returns a single result |
165
|
|
|
* |
166
|
|
|
* @static |
167
|
|
|
* @param string $query The query to be executed |
168
|
|
|
* @param string $field The name of the field to return |
169
|
|
|
* @param array $parameters (optional) A key/value array of parameters |
170
|
|
|
* @param int $cacheTime The time, in seconds, to cache the result of the query. Default: 30 |
171
|
|
|
* @return mixed Returns the value of $field in the first row of the resultset. Returns null if there are no results |
172
|
|
|
*/ |
173
|
|
|
public static function queryField($query, $field, $parameters = array(), $cacheTime = 30, $selectCheck = true) |
174
|
|
|
{ |
175
|
|
|
// Get the result |
176
|
|
|
$result = self::query($query, $parameters, $cacheTime, $selectCheck); |
177
|
|
|
|
178
|
|
|
// Figure out if it has no results |
179
|
|
|
if(sizeof($result) == 0) |
180
|
|
|
return null; |
181
|
|
|
|
182
|
|
|
// Bind the first result to $resultRow |
183
|
|
|
$resultRow = $result[0]; |
184
|
|
|
|
185
|
|
|
// Return the result + the field requested |
186
|
|
|
return $resultRow[$field]; |
187
|
|
|
} |
188
|
|
|
|
189
|
|
|
/** |
190
|
|
|
* Executes an SQL command and returns the number of rows affected. |
191
|
|
|
* Good for inserts, updates, deletes, etc. |
192
|
|
|
* |
193
|
|
|
* @static |
194
|
|
|
* @param string $query The query to be executed. |
195
|
|
|
* @param array $parameters (optional) A key/value array of parameters. |
196
|
|
|
* @param boolean $reportErrors Log the query and throw an exception if the query fails. Default: true |
197
|
|
|
* @return int The number of rows affected by the sql query. |
198
|
|
|
*/ |
199
|
|
|
public static function execute($query, $parameters = array(), $reportErrors = true, $returnID = false) |
200
|
|
|
{ |
201
|
|
|
// Start the timer |
202
|
|
|
$timer = new Timer(); |
203
|
|
|
|
204
|
|
|
// Increment the queryCounter |
205
|
|
|
self::$queryCount++; |
206
|
|
|
|
207
|
|
|
// Open the databse connection |
208
|
|
|
$pdo = self::getPDO(); |
209
|
|
|
|
210
|
|
|
// Begin the transaction |
211
|
|
|
$pdo->beginTransaction(); |
212
|
|
|
|
213
|
|
|
// Prepare the query |
214
|
|
|
$stmt = $pdo->prepare($query); |
215
|
|
|
|
216
|
|
|
// Execute the query, with the parameters |
217
|
|
|
$stmt->execute($parameters); |
218
|
|
|
|
219
|
|
|
// An error happened |
220
|
|
|
if($stmt->errorCode() != 0) |
221
|
|
|
{ |
222
|
|
|
// Report the error |
223
|
|
|
self::processError($stmt, $query, $parameters, $reportErrors); |
224
|
|
|
// Rollback the query |
225
|
|
|
$pdo->rollBack(); |
226
|
|
|
// Return false |
227
|
|
|
return false; |
228
|
|
|
} |
229
|
|
|
|
230
|
|
|
// return the last inserted id |
231
|
|
|
$lastInsertID = $returnID ? $pdo->lastInsertId() : 0; |
232
|
|
|
|
233
|
|
|
// No error, time to commit |
234
|
|
|
$pdo->commit(); |
235
|
|
|
|
236
|
|
|
// Stop the timer |
237
|
|
|
$duration = $timer->stop(); |
238
|
|
|
|
239
|
|
|
// Log the query |
240
|
|
|
self::log($query, $parameters, $duration); |
241
|
|
|
|
242
|
|
|
// Get the amount of rows that was altered |
243
|
|
|
$rowCount = $stmt->rowCount(); |
244
|
|
|
|
245
|
|
|
// Close the cursor |
246
|
|
|
$stmt->closeCursor(); |
247
|
|
|
|
248
|
|
|
if($returnID) |
249
|
|
|
return $lastInsertID; |
250
|
|
|
|
251
|
|
|
// Return the amount of rows that was altered |
252
|
|
|
return $rowCount; |
253
|
|
|
} |
254
|
|
|
|
255
|
|
|
/** |
256
|
|
|
* Validates a query to ensure it contains no semicolons |
257
|
|
|
* |
258
|
|
|
* @static |
259
|
|
|
* @param string $query The query to be executed. |
260
|
|
|
* @return void |
261
|
|
|
*/ |
262
|
|
|
private static function validateQuery($query) |
|
|
|
|
263
|
|
|
{ |
264
|
|
|
if(strpos($query, ";") !== false) throw new Exception("Semicolons are not allowed in queryes. Use parameters instead."); |
265
|
|
|
} |
266
|
|
|
|
267
|
|
|
/** |
268
|
|
|
* Retrieve the number of queries executed so far. |
269
|
|
|
* |
270
|
|
|
* @static |
271
|
|
|
* @return int Number of queries executed so far |
272
|
|
|
*/ |
273
|
|
|
public static function getQueryCount() |
274
|
|
|
{ |
275
|
|
|
return self::$queryCount; |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
/** |
279
|
|
|
* @static |
280
|
|
|
* @throws Exception |
281
|
|
|
* @param PDOStatement $statement |
282
|
|
|
* @param string $query |
283
|
|
|
* @param array $parameters |
284
|
|
|
* @param bool $reportErrors |
285
|
|
|
* @return void |
286
|
|
|
*/ |
287
|
|
|
public static function processError($statement, $query, $parameters = array(), $reportErrors = true) |
288
|
|
|
{ |
289
|
|
|
if ($reportErrors == false) return; |
|
|
|
|
290
|
|
|
$errorCode = $statement->errorCode(); |
291
|
|
|
$errorInfo = $statement->errorInfo(); |
292
|
|
|
self::log("$errorCode - " . $errorInfo[2] . "\n$query", $parameters, 1000); |
293
|
|
|
throw new Exception($errorInfo[0] . " - " . $errorInfo[1] . " - " . $errorInfo[2]); |
294
|
|
|
} |
295
|
|
|
|
296
|
|
|
/** |
297
|
|
|
* Logs a query, its parameters, and the amount of time it took to execute. |
298
|
|
|
* The original query is modified through simple search and replace to create |
299
|
|
|
* the query as close to the execution as PDO would have the query. This |
300
|
|
|
* logging function doesn't take any care to escape any parameters, so take |
301
|
|
|
* caution if you attempt to execute any logged queries. |
302
|
|
|
* |
303
|
|
|
* @param string $query The query. |
304
|
|
|
* @param array $parameters A key/value array of parameters |
305
|
|
|
* @param int $duration The length of time it took for the query to execute. |
306
|
|
|
* @return void |
307
|
|
|
*/ |
308
|
|
|
public static function log($query, $parameters = array(), $duration = 0) |
309
|
|
|
{ |
310
|
|
|
StatsD::increment("website_queryCount"); |
311
|
|
|
|
312
|
|
|
if ($duration < 2000) // Don't log queries taking less than 10 seconds. |
313
|
|
|
return; |
314
|
|
|
|
315
|
|
|
global $baseAddr; |
316
|
|
|
foreach ($parameters as $k => $v) { |
317
|
|
|
$query = str_replace($k, "'" . $v . "'", $query); |
318
|
|
|
} |
319
|
|
|
$uri = isset($_SERVER["REQUEST_URI"]) ? "Query page: https://$baseAddr" . $_SERVER["REQUEST_URI"] . "\n": ""; |
320
|
|
|
Log::log(($duration != 0 ? number_format($duration / 1000, 3) . "s " : "") . " Query: \n$query;\n$uri"); |
321
|
|
|
} |
322
|
|
|
|
323
|
|
|
/** |
324
|
|
|
* @static |
325
|
|
|
* @param string $query The query. |
326
|
|
|
* @param array $parameters The parameters |
327
|
|
|
* @return string The query and parameters as a hashed value. |
328
|
|
|
*/ |
329
|
|
|
public static function getKey($query, $parameters = array()) |
330
|
|
|
{ |
331
|
|
|
foreach($parameters as $key => $value) |
332
|
|
|
{ |
333
|
|
|
if(is_array($key)) |
334
|
|
|
$key = implode("", $key); |
335
|
|
|
|
336
|
|
|
if(is_array($value)) |
337
|
|
|
$value = implode("", $value); |
338
|
|
|
|
339
|
|
|
$query .= "|$key|$value"; |
340
|
|
|
} |
341
|
|
|
return "Db:" . sha1($query); |
342
|
|
|
} |
343
|
|
|
} |
344
|
|
|
|
This check looks from parameters that have been defined for a function or method, but which are not used in the method body.