Elgg  Version 3.0
Database.php
Go to the documentation of this file.
1 <?php
2 
3 namespace Elgg;
4 
13 
21 class Database {
22  use Profilable;
23  use Loggable;
24 
25  const DELAYED_QUERY = 'q';
26  const DELAYED_TYPE = 't';
27  const DELAYED_HANDLER = 'h';
28  const DELAYED_PARAMS = 'p';
29 
33  private $table_prefix;
34 
38  private $connections = [];
39 
43  private $query_count = 0;
44 
50  protected $query_cache;
51 
59  protected $delayed_queries = [];
60 
64  private $config;
65 
72  public function __construct(DbConfig $config, QueryCache $query_cache) {
73  $this->query_cache = $query_cache;
74 
75  $this->resetConnections($config);
76  }
77 
85  public function resetConnections(DbConfig $config) {
86  $this->connections = [];
87  $this->config = $config;
88  $this->table_prefix = $config->getTablePrefix();
89  $this->query_cache->enable();
90  $this->query_cache->clear();
91 
92  }
93 
102  public function getConnection($type) {
103  if (isset($this->connections[$type])) {
104  return $this->connections[$type];
105  } else if (isset($this->connections['readwrite'])) {
106  return $this->connections['readwrite'];
107  } else {
108  $this->setupConnections();
109  return $this->getConnection($type);
110  }
111  }
112 
122  public function setupConnections() {
123  if ($this->config->isDatabaseSplit()) {
124  $this->connect('read');
125  $this->connect('write');
126  } else {
127  $this->connect('readwrite');
128  }
129  }
130 
141  public function connect($type = "readwrite") {
142  $conf = $this->config->getConnectionConfig($type);
143 
144  $params = [
145  'dbname' => $conf['database'],
146  'user' => $conf['user'],
147  'password' => $conf['password'],
148  'host' => $conf['host'],
149  'charset' => $conf['encoding'],
150  'driver' => 'pdo_mysql',
151  ];
152 
153  try {
154  $this->connections[$type] = DriverManager::getConnection($params);
155  $this->connections[$type]->setFetchMode(\PDO::FETCH_OBJ);
156 
157  // https://github.com/Elgg/Elgg/issues/8121
158  $sub_query = "SELECT REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', '')";
159  $this->connections[$type]->exec("SET SESSION sql_mode=($sub_query);");
160  } catch (\Exception $e) {
161  // http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html
162  $this->log(LogLevel::ERROR, $e);
163 
164  if ($e->getCode() == 1102 || $e->getCode() == 1049) {
165  $msg = "Elgg couldn't select the database '{$conf['database']}'. "
166  . "Please check that the database is created and you have access to it.";
167  } else {
168  $msg = "Elgg couldn't connect to the database using the given credentials. Check the settings file.";
169  }
170  throw new \DatabaseException($msg);
171  }
172  }
173 
191  public function getData($query, $callback = null, array $params = []) {
192  return $this->getResults($query, $callback, false, $params);
193  }
194 
209  public function getDataRow($query, $callback = null, array $params = []) {
210  return $this->getResults($query, $callback, true, $params);
211  }
212 
225  public function insertData($query, array $params = []) {
226 
227  if ($query instanceof QueryBuilder) {
228  $params = $query->getParameters();
229  $query = $query->getSQL();
230  }
231 
232  if ($this->logger) {
233  $this->logger->info("DB insert query $query (params: " . print_r($params, true) . ")");
234  }
235 
236  $connection = $this->getConnection('write');
237 
238  $this->query_cache->clear();
239 
240  $this->executeQuery($query, $connection, $params);
241  return (int) $connection->lastInsertId();
242  }
243 
258  public function updateData($query, $get_num_rows = false, array $params = []) {
259 
260  if ($query instanceof QueryBuilder) {
261  $params = $query->getParameters();
262  $query = $query->getSQL();
263  }
264 
265  if ($this->logger) {
266  $this->logger->info("DB update query $query (params: " . print_r($params, true) . ")");
267  }
268 
269  $this->query_cache->clear();
270 
271  $stmt = $this->executeQuery($query, $this->getConnection('write'), $params);
272  if ($get_num_rows) {
273  return $stmt->rowCount();
274  } else {
275  return true;
276  }
277  }
278 
290  public function deleteData($query, array $params = []) {
291 
292  if ($query instanceof QueryBuilder) {
293  $params = $query->getParameters();
294  $query = $query->getSQL();
295  }
296 
297  if ($this->logger) {
298  $this->logger->info("DB delete query $query (params: " . print_r($params, true) . ")");
299  }
300 
301  $connection = $this->getConnection('write');
302 
303  $this->query_cache->clear();
304 
305  $stmt = $this->executeQuery("$query", $connection, $params);
306  return (int) $stmt->rowCount();
307  }
308 
320  protected function fingerprintCallback($callback) {
321  if (is_string($callback)) {
322  return $callback;
323  }
324  if (is_object($callback)) {
325  return spl_object_hash($callback) . "::__invoke";
326  }
327  if (is_array($callback)) {
328  if (is_string($callback[0])) {
329  return "{$callback[0]}::{$callback[1]}";
330  }
331  return spl_object_hash($callback[0]) . "::{$callback[1]}";
332  }
333  // this should not happen
334  return "";
335  }
336 
350  protected function getResults($query, $callback = null, $single = false, array $params = []) {
351 
352  if ($query instanceof QueryBuilder) {
353  $params = $query->getParameters();
354  $sql = $query->getSQL();
355  } else {
356  $sql = $query;
357  }
358 
359  // Since we want to cache results of running the callback, we need to
360  // namespace the query with the callback and single result request.
361  // https://github.com/elgg/elgg/issues/4049
362  $extras = (int) $single . '|';
363  if ($callback) {
364  if (!is_callable($callback)) {
365  throw new \RuntimeException('$callback must be a callable function. Given '
366  . _elgg_services()->handlers->describeCallable($callback));
367  }
368  $extras .= $this->fingerprintCallback($callback);
369  }
370 
371  $hash = $this->query_cache->getHash($sql, $params, $extras);
372 
373  $cached_results = $this->query_cache->get($hash);
374  if (isset($cached_results)) {
375  return $cached_results;
376  }
377 
378  if ($this->logger) {
379  $this->logger->info("DB select query $sql (params: " . print_r($params, true) . ")");
380  }
381 
382  $return = [];
383 
384  if ($query instanceof QueryBuilder) {
385  $stmt = $this->executeQuery($query, $query->getConnection());
386  } else {
387  $stmt = $this->executeQuery($query, $this->getConnection('read'), $params);
388  }
389 
390  while ($row = $stmt->fetch()) {
391  if ($callback) {
392  $row = call_user_func($callback, $row);
393  }
394 
395  if ($single) {
396  $return = $row;
397  break;
398  } else {
399  $return[] = $row;
400  }
401  }
402 
403  // Cache result
404  $this->query_cache->set($hash, $return);
405 
406  return $return;
407  }
408 
422  protected function executeQuery($query, Connection $connection, array $params = []) {
423  if ($query == null) {
424  throw new \DatabaseException("Query cannot be null");
425  }
426 
427  $sql = $query;
428  if ($query instanceof QueryBuilder) {
429  $params = $query->getParameters();
430  $sql = $query->getSQL();
431  }
432 
433  try {
434  $value = $this->trackQuery($sql, $params, function() use ($query, $params, $connection, $sql) {
435  if ($query instanceof \Elgg\Database\QueryBuilder) {
436  return $query->execute(false);
437  } elseif ($query instanceof QueryBuilder) {
438  return $query->execute();
439  } elseif (!empty($params)) {
440  return $connection->executeQuery($sql, $params);
441  } else {
442  // faster
443  return $connection->query($sql);
444  }
445  });
446  } catch (\Exception $e) {
447  $ex = new \DatabaseException($e->getMessage());
448  $ex->setParameters($params);
449  $ex->setQuery($sql);
450 
451  throw $ex;
452  }
453 
454  return $value;
455  }
456 
466  public function trackQuery($query, array $params, callable $callback) {
467 
468  $sql = $query;
469  if ($query instanceof QueryBuilder) {
470  $params = $query->getParameters();
471  $sql = $query->getSQL();
472  }
473 
474  $this->query_count++;
475 
476  $timer_key = false;
477  if ($this->timer) {
478  $timer_key = preg_replace('~\\s+~', ' ', trim($sql . '|' . serialize($params)));
479  $this->timer->begin(['SQL', $timer_key]);
480  }
481 
482  $stop_timer = function() use ($timer_key) {
483  if ($timer_key) {
484  $this->timer->end(['SQL', $timer_key]);
485  }
486  };
487 
488  try {
489  $result = $callback();
490  } catch (\Exception $e) {
491  $stop_timer();
492 
493  throw $e;
494  }
495 
496  $stop_timer();
497 
498  return $result;
499  }
500 
525  public function runSqlScript($scriptlocation) {
526  $script = file_get_contents($scriptlocation);
527  if ($script) {
528  $errors = [];
529 
530  // Remove MySQL '-- ' and '# ' style comments
531  $script = preg_replace('/^(?:--|#) .*$/m', '', $script);
532 
533  // Statements must end with ; and a newline
534  $sql_statements = preg_split('/;[\n\r]+/', "$script\n");
535 
536  foreach ($sql_statements as $statement) {
537  $statement = trim($statement);
538  $statement = str_replace("prefix_", $this->table_prefix, $statement);
539  if (!empty($statement)) {
540  try {
541  $this->updateData($statement);
542  } catch (\DatabaseException $e) {
543  $errors[] = $e->getMessage();
544  }
545  }
546  }
547  if (!empty($errors)) {
548  $errortxt = "";
549  foreach ($errors as $error) {
550  $errortxt .= " {$error};";
551  }
552 
553  $msg = "There were a number of issues: " . $errortxt;
554  throw new \DatabaseException($msg);
555  }
556  } else {
557  $msg = "Elgg couldn't find the requested database script at " . $scriptlocation . ".";
558  throw new \DatabaseException($msg);
559  }
560  }
561 
575  public function registerDelayedQuery($query, $type, $callback = null, array $params = []) {
576  if ($type != 'read' && $type != 'write') {
577  return false;
578  }
579 
580  $this->delayed_queries[] = [
581  self::DELAYED_QUERY => $query,
582  self::DELAYED_TYPE => $type,
583  self::DELAYED_HANDLER => $callback,
584  self::DELAYED_PARAMS => $params,
585  ];
586 
587  return true;
588  }
589 
597  public function executeDelayedQueries() {
598 
599  foreach ($this->delayed_queries as $set) {
600  $query = $set[self::DELAYED_QUERY];
601  $type = $set[self::DELAYED_TYPE];
602  $handler = $set[self::DELAYED_HANDLER];
603  $params = $set[self::DELAYED_PARAMS];
604 
605  try {
606  $stmt = $this->executeQuery($query, $this->getConnection($type), $params);
607 
608  if (is_callable($handler)) {
609  call_user_func($handler, $stmt);
610  }
611  } catch (\Exception $e) {
612  if ($this->logger) {
613  // Suppress all exceptions since page already sent to requestor
614  $this->logger->error($e);
615  }
616  }
617  }
618 
619  $this->delayed_queries = [];
620  }
621 
629  public function enableQueryCache() {
630  $this->query_cache->enable();
631  }
632 
641  public function disableQueryCache() {
642  $this->query_cache->disable();
643  }
644 
650  public function getQueryCount() {
651  return $this->query_count;
652  }
653 
662  public function sanitizeInt($value, $signed = true) {
663  $value = (int) $value;
664 
665  if ($signed === false) {
666  if ($value < 0) {
667  $value = 0;
668  }
669  }
670 
671  return $value;
672  }
673 
682  public function sanitizeString($value) {
683  if (is_array($value)) {
684  throw new \DatabaseException(__METHOD__ . '() and serialize_string() cannot accept arrays.');
685  }
686  $quoted = $this->getConnection('read')->quote($value);
687  if ($quoted[0] !== "'" || substr($quoted, -1) !== "'") {
688  throw new \DatabaseException("PDO::quote did not return surrounding single quotes.");
689  }
690  return substr($quoted, 1, -1);
691  }
692 
700  public function getServerVersion($type) {
701  $driver = $this->getConnection($type)->getWrappedConnection();
702  if ($driver instanceof ServerInfoAwareConnection) {
703  return $driver->getServerVersion();
704  }
705 
706  return null;
707  }
708 
715  public function __get($name) {
716  if ($name === 'prefix') {
717  return $this->table_prefix;
718  }
719 
720  throw new \RuntimeException("Cannot read property '$name'");
721  }
722 
730  public function __set($name, $value) {
731  throw new \RuntimeException("Cannot write property '$name'");
732  }
733 }
updateData($query, $get_num_rows=false, array $params=[])
Update the database.
Definition: Database.php:258
$query
Definition: groups.php:8
if(!$user||!$user->canDelete()) $name
Definition: delete.php:22
getQueryCount()
Get the number of queries made to the database.
Definition: Database.php:650
log($msg, $level=LogLevel::NOTICE)
Log a message.
Definition: Seeding.php:762
$params
Saves global plugin settings.
Definition: save.php:13
WARNING: API IN FLUX.
Definition: DbConfig.php:13
$errors
Definition: pending.php:46
__construct(DbConfig $config, QueryCache $query_cache)
Constructor.
Definition: Database.php:72
The Elgg database.
Definition: Database.php:21
trait Loggable
Enables adding a logger.
Definition: Loggable.php:12
$type
Definition: delete.php:21
executeQuery($query, Connection $connection, array $params=[])
Execute a query.
Definition: Database.php:422
trait Profilable
Make an object accept a timer.
Definition: Profilable.php:10
resetConnections(DbConfig $config)
Reset the connections with new credentials.
Definition: Database.php:85
$config
Advanced site settings, debugging section.
Definition: debugging.php:6
sanitizeInt($value, $signed=true)
Sanitizes an integer value for use in a query.
Definition: Database.php:662
$error
Bad request error.
Definition: 400.php:6
getData($query, $callback=null, array $params=[])
Retrieve rows from the database.
Definition: Database.php:191
Configuration exception.
__set($name, $value)
Handle magic property writes.
Definition: Database.php:730
sanitizeString($value)
Sanitizes a string for use in a query.
Definition: Database.php:682
__get($name)
Handle magic property reads.
Definition: Database.php:715
getTablePrefix()
Get the database table prefix.
Definition: DbConfig.php:65
Volatile cache for select queries.
Definition: QueryCache.php:17
runSqlScript($scriptlocation)
Runs a full database script from disk.
Definition: Database.php:525
if($item instanceof\ElggEntity) elseif($item instanceof\ElggRiverItem) elseif(is_callable([$item, 'getType']))
Definition: item.php:39
registerDelayedQuery($query, $type, $callback=null, array $params=[])
Queue a query for execution upon shutdown.
Definition: Database.php:575
fingerprintCallback($callback)
Get a string that uniquely identifies a callback during the current request.
Definition: Database.php:320
getResults($query, $callback=null, $single=false, array $params=[])
Handles queries that return results, running the results through a an optional callback function...
Definition: Database.php:350
connect($type="readwrite")
Establish a connection to the database server.
Definition: Database.php:141
$value
Definition: debugging.php:7
deleteData($query, array $params=[])
Delete data from the database.
Definition: Database.php:290
getConnection($type)
Gets (if required, also creates) a DB connection.
Definition: Database.php:102
disableQueryCache()
Disable the query cache.
Definition: Database.php:641
enableQueryCache()
Enable the query cache.
Definition: Database.php:629
getDataRow($query, $callback=null, array $params=[])
Retrieve a single row from the database.
Definition: Database.php:209
_elgg_services()
Get the global service provider.
Definition: elgglib.php:1292
elgg ajax ERROR
Definition: ajax.js:33
getServerVersion($type)
Get the server version number.
Definition: Database.php:700
$handler
Definition: add.php:7
executeDelayedQueries()
Trigger all queries that were registered as "delayed" queries.
Definition: Database.php:597
setupConnections()
Establish database connections.
Definition: Database.php:122
insertData($query, array $params=[])
Insert a row into the database.
Definition: Database.php:225
trackQuery($query, array $params, callable $callback)
Tracks the query count and timers for a given query.
Definition: Database.php:466