Database.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666
  1. <?php
  2. namespace Elgg;
  3. use Elgg\Database\Config;
  4. /**
  5. * An object representing a single Elgg database.
  6. *
  7. * WARNING: THIS API IS IN FLUX. PLUGIN AUTHORS SHOULD NOT USE. See lib/database.php instead.
  8. *
  9. * @access private
  10. *
  11. * @package Elgg.Core
  12. * @subpackage Database
  13. */
  14. class Database {
  15. /** @var string $tablePrefix Prefix for database tables */
  16. private $tablePrefix;
  17. /** @var resource[] $dbLinks Database connection resources */
  18. private $dbLinks = array();
  19. /** @var int $queryCount The number of queries made */
  20. private $queryCount = 0;
  21. /**
  22. * Query cache for select queries.
  23. *
  24. * Queries and their results are stored in this cache as:
  25. * <code>
  26. * $DB_QUERY_CACHE[query hash] => array(result1, result2, ... resultN)
  27. * </code>
  28. * @see \Elgg\Database::getResults() for details on the hash.
  29. *
  30. * @var \Elgg\Cache\LRUCache $queryCache The cache
  31. */
  32. private $queryCache = null;
  33. /**
  34. * @var int $queryCacheSize The number of queries to cache
  35. */
  36. private $queryCacheSize = 50;
  37. /**
  38. * Queries are saved to an array and executed using
  39. * a function registered by register_shutdown_function().
  40. *
  41. * Queries are saved as an array in the format:
  42. * <code>
  43. * $this->delayedQueries[] = array(
  44. * 'q' => string $query,
  45. * 'l' => string $query_type,
  46. * 'h' => string $handler // a callback function
  47. * );
  48. * </code>
  49. *
  50. * @var array $delayedQueries Queries to be run during shutdown
  51. */
  52. private $delayedQueries = array();
  53. /** @var bool $installed Is the database installed? */
  54. private $installed = false;
  55. /** @var \Elgg\Database\Config $config Database configuration */
  56. private $config;
  57. /** @var \Elgg\Logger $logger The logger */
  58. private $logger;
  59. /**
  60. * Constructor
  61. *
  62. * @param \Elgg\Database\Config $config Database configuration
  63. * @param \Elgg\Logger $logger The logger
  64. */
  65. public function __construct(\Elgg\Database\Config $config, \Elgg\Logger $logger) {
  66. $this->logger = $logger;
  67. $this->config = $config;
  68. $this->tablePrefix = $config->getTablePrefix();
  69. $this->enableQueryCache();
  70. }
  71. /**
  72. * Gets (if required, also creates) a database link resource.
  73. *
  74. * The database link resources are created by
  75. * {@link \Elgg\Database::setupConnections()}, which is called if no links exist.
  76. *
  77. * @param string $type The type of link we want: "read", "write" or "readwrite".
  78. *
  79. * @return resource Database link
  80. * @throws \DatabaseException
  81. * @todo make protected once we get rid of get_db_link()
  82. */
  83. public function getLink($type) {
  84. if (isset($this->dbLinks[$type])) {
  85. return $this->dbLinks[$type];
  86. } else if (isset($this->dbLinks['readwrite'])) {
  87. return $this->dbLinks['readwrite'];
  88. } else {
  89. $this->setupConnections();
  90. return $this->getLink($type);
  91. }
  92. }
  93. /**
  94. * Establish database connections
  95. *
  96. * If the configuration has been set up for multiple read/write databases, set those
  97. * links up separately; otherwise just create the one database link.
  98. *
  99. * @return void
  100. * @throws \DatabaseException
  101. */
  102. public function setupConnections() {
  103. if ($this->config->isDatabaseSplit()) {
  104. $this->establishLink('read');
  105. $this->establishLink('write');
  106. } else {
  107. $this->establishLink('readwrite');
  108. }
  109. }
  110. /**
  111. * Establish a connection to the database server
  112. *
  113. * Connect to the database server and use the Elgg database for a particular database link
  114. *
  115. * @param string $dblinkname The type of database connection. Used to identify the
  116. * resource: "read", "write", or "readwrite".
  117. *
  118. * @return void
  119. * @throws \DatabaseException
  120. */
  121. public function establishLink($dblinkname = "readwrite") {
  122. $conf = $this->config->getConnectionConfig($dblinkname);
  123. // Connect to database
  124. $this->dbLinks[$dblinkname] = mysql_connect($conf['host'], $conf['user'], $conf['password'], true);
  125. if (!$this->dbLinks[$dblinkname]) {
  126. $msg = "Elgg couldn't connect to the database using the given credentials. Check the settings file.";
  127. throw new \DatabaseException($msg);
  128. }
  129. if (!mysql_select_db($conf['database'], $this->dbLinks[$dblinkname])) {
  130. $msg = "Elgg couldn't select the database '{$conf['database']}'. Please check that the database is created and you have access to it.";
  131. throw new \DatabaseException($msg);
  132. }
  133. // Set DB for UTF8
  134. mysql_query("SET NAMES utf8", $this->dbLinks[$dblinkname]);
  135. }
  136. /**
  137. * Retrieve rows from the database.
  138. *
  139. * Queries are executed with {@link \Elgg\Database::executeQuery()} and results
  140. * are retrieved with {@link mysql_fetch_object()}. If a callback
  141. * function $callback is defined, each row will be passed as a single
  142. * argument to $callback. If no callback function is defined, the
  143. * entire result set is returned as an array.
  144. *
  145. * @param mixed $query The query being passed.
  146. * @param string $callback Optionally, the name of a function to call back to on each row
  147. *
  148. * @return array An array of database result objects or callback function results. If the query
  149. * returned nothing, an empty array.
  150. * @throws \DatabaseException
  151. */
  152. public function getData($query, $callback = '') {
  153. return $this->getResults($query, $callback, false);
  154. }
  155. /**
  156. * Retrieve a single row from the database.
  157. *
  158. * Similar to {@link \Elgg\Database::getData()} but returns only the first row
  159. * matched. If a callback function $callback is specified, the row will be passed
  160. * as the only argument to $callback.
  161. *
  162. * @param mixed $query The query to execute.
  163. * @param string $callback A callback function
  164. *
  165. * @return mixed A single database result object or the result of the callback function.
  166. * @throws \DatabaseException
  167. */
  168. public function getDataRow($query, $callback = '') {
  169. return $this->getResults($query, $callback, true);
  170. }
  171. /**
  172. * Insert a row into the database.
  173. *
  174. * @note Altering the DB invalidates all queries in the query cache.
  175. *
  176. * @param mixed $query The query to execute.
  177. *
  178. * @return int|false The database id of the inserted row if a AUTO_INCREMENT field is
  179. * defined, 0 if not, and false on failure.
  180. * @throws \DatabaseException
  181. */
  182. public function insertData($query) {
  183. $this->logger->log("DB query $query", \Elgg\Logger::INFO);
  184. $dblink = $this->getLink('write');
  185. $this->invalidateQueryCache();
  186. if ($this->executeQuery("$query", $dblink)) {
  187. return mysql_insert_id($dblink);
  188. }
  189. return false;
  190. }
  191. /**
  192. * Update the database.
  193. *
  194. * @note Altering the DB invalidates all queries in the query cache.
  195. *
  196. * @param string $query The query to run.
  197. * @param bool $getNumRows Return the number of rows affected (default: false)
  198. *
  199. * @return bool|int
  200. * @throws \DatabaseException
  201. */
  202. public function updateData($query, $getNumRows = false) {
  203. $this->logger->log("DB query $query", \Elgg\Logger::INFO);
  204. $dblink = $this->getLink('write');
  205. $this->invalidateQueryCache();
  206. if ($this->executeQuery("$query", $dblink)) {
  207. if ($getNumRows) {
  208. return mysql_affected_rows($dblink);
  209. } else {
  210. return true;
  211. }
  212. }
  213. return false;
  214. }
  215. /**
  216. * Delete data from the database
  217. *
  218. * @note Altering the DB invalidates all queries in query cache.
  219. *
  220. * @param string $query The SQL query to run
  221. *
  222. * @return int|false The number of affected rows or false on failure
  223. * @throws \DatabaseException
  224. */
  225. public function deleteData($query) {
  226. $this->logger->log("DB query $query", \Elgg\Logger::INFO);
  227. $dblink = $this->getLink('write');
  228. $this->invalidateQueryCache();
  229. if ($this->executeQuery("$query", $dblink)) {
  230. return mysql_affected_rows($dblink);
  231. }
  232. return false;
  233. }
  234. /**
  235. * Get a string that uniquely identifies a callback during the current request.
  236. *
  237. * This is used to cache queries whose results were transformed by the callback. If the callback involves
  238. * object method calls of the same class, different instances will return different values.
  239. *
  240. * @param callable $callback The callable value to fingerprint
  241. *
  242. * @return string A string that is unique for each callable passed in
  243. * @since 1.9.4
  244. * @access private
  245. * @todo Make this protected once we can setAccessible(true) via reflection
  246. */
  247. public function fingerprintCallback($callback) {
  248. if (is_string($callback)) {
  249. return $callback;
  250. }
  251. if (is_object($callback)) {
  252. return spl_object_hash($callback) . "::__invoke";
  253. }
  254. if (is_array($callback)) {
  255. if (is_string($callback[0])) {
  256. return "{$callback[0]}::{$callback[1]}";
  257. }
  258. return spl_object_hash($callback[0]) . "::{$callback[1]}";
  259. }
  260. // this should not happen
  261. return "";
  262. }
  263. /**
  264. * Handles queries that return results, running the results through a
  265. * an optional callback function. This is for R queries (from CRUD).
  266. *
  267. * @param string $query The select query to execute
  268. * @param string $callback An optional callback function to run on each row
  269. * @param bool $single Return only a single result?
  270. *
  271. * @return array An array of database result objects or callback function results. If the query
  272. * returned nothing, an empty array.
  273. * @throws \DatabaseException
  274. */
  275. protected function getResults($query, $callback = null, $single = false) {
  276. // Since we want to cache results of running the callback, we need to
  277. // need to namespace the query with the callback and single result request.
  278. // https://github.com/elgg/elgg/issues/4049
  279. $query_id = (int)$single . $query . '|';
  280. if ($callback) {
  281. $is_callable = is_callable($callback);
  282. if ($is_callable) {
  283. $query_id .= $this->fingerprintCallback($callback);
  284. } else {
  285. // TODO do something about invalid callbacks
  286. $callback = null;
  287. }
  288. } else {
  289. $is_callable = false;
  290. }
  291. // MD5 yields smaller mem usage for cache and cleaner logs
  292. $hash = md5($query_id);
  293. // Is cached?
  294. if ($this->queryCache) {
  295. if (isset($this->queryCache[$hash])) {
  296. $this->logger->log("DB query $query results returned from cache (hash: $hash)", \Elgg\Logger::INFO);
  297. return $this->queryCache[$hash];
  298. }
  299. }
  300. $dblink = $this->getLink('read');
  301. $return = array();
  302. if ($result = $this->executeQuery("$query", $dblink)) {
  303. while ($row = mysql_fetch_object($result)) {
  304. if ($is_callable) {
  305. $row = call_user_func($callback, $row);
  306. }
  307. if ($single) {
  308. $return = $row;
  309. break;
  310. } else {
  311. $return[] = $row;
  312. }
  313. }
  314. }
  315. if (empty($return)) {
  316. $this->logger->log("DB query $query returned no results.", \Elgg\Logger::INFO);
  317. }
  318. // Cache result
  319. if ($this->queryCache) {
  320. $this->queryCache[$hash] = $return;
  321. $this->logger->log("DB query $query results cached (hash: $hash)", \Elgg\Logger::INFO);
  322. }
  323. return $return;
  324. }
  325. /**
  326. * Execute a query.
  327. *
  328. * $query is executed via {@link mysql_query()}. If there is an SQL error,
  329. * a {@link DatabaseException} is thrown.
  330. *
  331. * @param string $query The query
  332. * @param resource $dblink The DB link
  333. *
  334. * @return resource|bool The result of mysql_query()
  335. * @throws \DatabaseException
  336. * @todo should this be public?
  337. */
  338. public function executeQuery($query, $dblink) {
  339. if ($query == null) {
  340. throw new \DatabaseException("Query cannot be null");
  341. }
  342. if (!is_resource($dblink)) {
  343. throw new \DatabaseException("Connection to database was lost.");
  344. }
  345. $this->queryCount++;
  346. $result = mysql_query($query, $dblink);
  347. if (mysql_errno($dblink)) {
  348. throw new \DatabaseException(mysql_error($dblink) . "\n\n QUERY: $query");
  349. }
  350. return $result;
  351. }
  352. /**
  353. * Runs a full database script from disk.
  354. *
  355. * The file specified should be a standard SQL file as created by
  356. * mysqldump or similar. Statements must be terminated with ;
  357. * and a newline character (\n or \r\n).
  358. *
  359. * The special string 'prefix_' is replaced with the database prefix
  360. * as defined in {@link $this->tablePrefix}.
  361. *
  362. * @warning Only single line comments are supported. A comment
  363. * must start with '-- ' or '# ', where the comment sign is at the
  364. * very beginning of each line.
  365. *
  366. * @warning Errors do not halt execution of the script. If a line
  367. * generates an error, the error message is saved and the
  368. * next line is executed. After the file is run, any errors
  369. * are displayed as a {@link DatabaseException}
  370. *
  371. * @param string $scriptlocation The full path to the script
  372. *
  373. * @return void
  374. * @throws \DatabaseException
  375. */
  376. public function runSqlScript($scriptlocation) {
  377. $script = file_get_contents($scriptlocation);
  378. if ($script) {
  379. $errors = array();
  380. // Remove MySQL '-- ' and '# ' style comments
  381. $script = preg_replace('/^(?:--|#) .*$/m', '', $script);
  382. // Statements must end with ; and a newline
  383. $sql_statements = preg_split('/;[\n\r]+/', "$script\n");
  384. foreach ($sql_statements as $statement) {
  385. $statement = trim($statement);
  386. $statement = str_replace("prefix_", $this->tablePrefix, $statement);
  387. if (!empty($statement)) {
  388. try {
  389. $this->updateData($statement);
  390. } catch (\DatabaseException $e) {
  391. $errors[] = $e->getMessage();
  392. }
  393. }
  394. }
  395. if (!empty($errors)) {
  396. $errortxt = "";
  397. foreach ($errors as $error) {
  398. $errortxt .= " {$error};";
  399. }
  400. $msg = "There were a number of issues: " . $errortxt;
  401. throw new \DatabaseException($msg);
  402. }
  403. } else {
  404. $msg = "Elgg couldn't find the requested database script at " . $scriptlocation . ".";
  405. throw new \DatabaseException($msg);
  406. }
  407. }
  408. /**
  409. * Queue a query for execution upon shutdown.
  410. *
  411. * You can specify a handler function if you care about the result. This function will accept
  412. * the raw result from {@link mysql_query()}.
  413. *
  414. * @param string $query The query to execute
  415. * @param string $type The query type ('read' or 'write')
  416. * @param string $handler A callback function to pass the results array to
  417. *
  418. * @return boolean Whether registering was successful.
  419. * @todo deprecate passing resource for $type as that should not be part of public API
  420. */
  421. public function registerDelayedQuery($query, $type, $handler = "") {
  422. if (!is_resource($type) && $type != 'read' && $type != 'write') {
  423. return false;
  424. }
  425. // Construct delayed query
  426. $delayed_query = array();
  427. $delayed_query['q'] = $query;
  428. $delayed_query['l'] = $type;
  429. $delayed_query['h'] = $handler;
  430. $this->delayedQueries[] = $delayed_query;
  431. return true;
  432. }
  433. /**
  434. * Trigger all queries that were registered as "delayed" queries. This is
  435. * called by the system automatically on shutdown.
  436. *
  437. * @return void
  438. * @access private
  439. * @todo make protected once this class is part of public API
  440. */
  441. public function executeDelayedQueries() {
  442. foreach ($this->delayedQueries as $query_details) {
  443. try {
  444. $link = $query_details['l'];
  445. if ($link == 'read' || $link == 'write') {
  446. $link = $this->getLink($link);
  447. } elseif (!is_resource($link)) {
  448. $msg = "Link for delayed query not valid resource or db_link type. Query: {$query_details['q']}";
  449. $this->logger->log($msg, \Elgg\Logger::WARNING);
  450. }
  451. $result = $this->executeQuery($query_details['q'], $link);
  452. if ((isset($query_details['h'])) && (is_callable($query_details['h']))) {
  453. $query_details['h']($result);
  454. }
  455. } catch (\DatabaseException $e) {
  456. // Suppress all exceptions since page already sent to requestor
  457. $this->logger->log($e, \Elgg\Logger::ERROR);
  458. }
  459. }
  460. }
  461. /**
  462. * Enable the query cache
  463. *
  464. * This does not take precedence over the \Elgg\Database\Config setting.
  465. *
  466. * @return void
  467. */
  468. public function enableQueryCache() {
  469. if ($this->config->isQueryCacheEnabled() && $this->queryCache === null) {
  470. // @todo if we keep this cache, expose the size as a config parameter
  471. $this->queryCache = new \Elgg\Cache\LRUCache($this->queryCacheSize);
  472. }
  473. }
  474. /**
  475. * Disable the query cache
  476. *
  477. * This is useful for special scripts that pull large amounts of data back
  478. * in single queries.
  479. *
  480. * @return void
  481. */
  482. public function disableQueryCache() {
  483. $this->queryCache = null;
  484. }
  485. /**
  486. * Invalidate the query cache
  487. *
  488. * @return void
  489. */
  490. protected function invalidateQueryCache() {
  491. if ($this->queryCache) {
  492. $this->queryCache->clear();
  493. $this->logger->log("Query cache invalidated", \Elgg\Logger::INFO);
  494. }
  495. }
  496. /**
  497. * Test that the Elgg database is installed
  498. *
  499. * @return void
  500. * @throws \InstallationException
  501. */
  502. public function assertInstalled() {
  503. if ($this->installed) {
  504. return;
  505. }
  506. try {
  507. $dblink = $this->getLink('read');
  508. mysql_query("SELECT value FROM {$this->tablePrefix}datalists WHERE name = 'installed'", $dblink);
  509. if (mysql_errno($dblink) > 0) {
  510. throw new \DatabaseException();
  511. }
  512. } catch (\DatabaseException $e) {
  513. throw new \InstallationException("Unable to handle this request. This site is not configured or the database is down.");
  514. }
  515. $this->installed = true;
  516. }
  517. /**
  518. * Get the number of queries made to the database
  519. *
  520. * @return int
  521. */
  522. public function getQueryCount() {
  523. return $this->queryCount;
  524. }
  525. /**
  526. * Get the prefix for Elgg's tables
  527. *
  528. * @return string
  529. */
  530. public function getTablePrefix() {
  531. return $this->tablePrefix;
  532. }
  533. /**
  534. * Sanitizes an integer value for use in a query
  535. *
  536. * @param int $value Value to sanitize
  537. * @param bool $signed Whether negative values are allowed (default: true)
  538. * @return int
  539. */
  540. public function sanitizeInt($value, $signed = true) {
  541. $value = (int) $value;
  542. if ($signed === false) {
  543. if ($value < 0) {
  544. $value = 0;
  545. }
  546. }
  547. return $value;
  548. }
  549. /**
  550. * Sanitizes a string for use in a query
  551. *
  552. * @param string $value Value to escape
  553. * @return string
  554. */
  555. public function sanitizeString($value) {
  556. // use resource if established, but don't open a connection to do it.
  557. if (isset($this->dbLinks['read'])) {
  558. return mysql_real_escape_string($value, $this->dbLinks['read']);
  559. }
  560. return mysql_real_escape_string($value);
  561. }
  562. /**
  563. * Get the server version number
  564. *
  565. * @param string $type Connection type (Config constants, e.g. Config::READ_WRITE)
  566. *
  567. * @return string
  568. */
  569. public function getServerVersion($type) {
  570. return mysql_get_server_info($this->getLink($type));
  571. }
  572. }