From 9863842d66cd8f8d95a86b8e14a134f722b9a2fd Mon Sep 17 00:00:00 2001 From: marcus Date: Thu, 6 Nov 2008 14:13:09 +0000 Subject: * Database functions optimised and centralised a bit * Caching now caches queries which return no data * Introducing execute_delayed_query family for executing queries AFTER the page has loaded. git-svn-id: https://code.elgg.org/elgg/trunk@2414 36083f99-b078-4883-b0ff-0f9b5a30f544 --- engine/lib/database.php | 219 ++++++++++++++++++++++++++++-------------------- 1 file changed, 129 insertions(+), 90 deletions(-) (limited to 'engine/lib/database.php') diff --git a/engine/lib/database.php b/engine/lib/database.php index ccf188357..3fee75917 100644 --- a/engine/lib/database.php +++ b/engine/lib/database.php @@ -14,6 +14,7 @@ $DB_PROFILE = array(); $DB_QUERY_CACHE = array(); + $DB_DELAYED_QUERIES = array(); /** * Connect to the database server and use the Elgg database for a particular database link @@ -105,6 +106,25 @@ error_log("DB Queries for this page: $dbcalls"); error_log("***************************************************"); } + } + + /** + * Execute any delayed queries. + */ + function db_delayedexecution_shutdown_hook() + { + global $DB_DELAYED_QUERIES, $CONFIG; + + foreach ($DB_DELAYED_QUERIES as $query_details) { + $result = execute_query($query_details['q'], $query_details['l']); // use one of our db functions so it is included in profiling. + + try { + if ( (isset($query_details['h'])) && (is_callable($query_details['h']))) + $query_details['h']($result); + } catch (Exception $e) { // Suppress all errors since these can't be delt with here + if (isset($CONFIG->debug) && $CONFIG->debug) error_log($e); + } + } } /** @@ -115,6 +135,7 @@ * @param mixed $object Used for nothing in this context */ function init_db($event, $object_type, $object = null) { + register_shutdown_function('db_delayedexecution_shutdown_hook'); register_shutdown_function('db_profiling_shutdown_hook'); setup_db_connections(); return true; @@ -143,12 +164,82 @@ */ function explain_query($query, $link) { - if ($result = mysql_query("explain " . $query, $link)) { + if ($result = execute_query("explain " . $query, $link)) { return mysql_fetch_object($result); } return false; - } + } + + /** + * Execute a query. + * + * @param string $query The query + * @param link $dblink the DB link + * @return Returns a the result of mysql_query + */ + function execute_query($query, $dblink) + { + global $CONFIG, $dbcalls, $DB_PROFILE, $DB_QUERY_CACHE; + + $dbcalls++; + + if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) + $DB_PROFILE[] = $query; + + $result = mysql_query($query, $dblink); + $DB_QUERY_CACHE[$query] = -1; // Set initial cache to -1 + + if (mysql_errno($dblink)) + throw new DatabaseException(mysql_error($dblink) . " QUERY: " . $query); + + return $result; + + } + + /** + * Queue a query for execution after all output has been sent to the user. + * + * You can specify a handler function if you care about the result. This function will accept + * the raw result from mysql_query(); + * + * @param string $query The query to execute + * @param resource $dblink The database link to use + * @param string $handler The handler + */ + function execute_delayed_query($query, $dblink, $handler = "") + { + global $DB_DELAYED_QUERIES; + + if (!isset($DB_DELAYED_QUERIES)) + $DB_DELAYED_QUERIES = array(); + + // Construct delayed query + $delayed_query = array(); + $delayed_query['q'] = $query; + $delayed_query['l'] = $dblink; + $delayed_query['h'] = $handler; + + $DB_DELAYED_QUERIES[] = $delayed_query; + + return true; + } + + /** + * Write wrapper for execute_delayed_query() + * + * @param string $query The query to execute + * @param string $handler The handler if you care about the result. + */ + function execute_delayed_write_query($query, $handler = "") { return execute_delayed_query($query, get_db_link('write'), $handler); } + + /** + * Read wrapper for execute_delayed_query() + * + * @param string $query The query to execute + * @param string $handler The handler if you care about the result. + */ + function execute_delayed_read_query($query, $handler = "") { return execute_delayed_query($query, get_db_link('read'), $handler); } /** * Use this function to get data from the database @@ -159,24 +250,25 @@ function get_data($query, $callback = "") { - global $CONFIG, $dbcalls, $DB_PROFILE, $DB_QUERY_CACHE; - - $dblink = get_db_link('read'); - - $resultarray = array(); + global $CONFIG, $DB_QUERY_CACHE; - if (isset($DB_QUERY_CACHE[$query])) { + // Is cached? + $cached_query = $DB_QUERY_CACHE[$query]; + if ($cached_query) { if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) error_log ("$query results returned from cache"); - return $DB_QUERY_CACHE[$query]; + + if ($cached_query === -1) + return array(); // Last time this query returned nothing, so return an empty array + + return $cached_query; } - $dbcalls++; - - if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) - $DB_PROFILE[] = $query; + $dblink = get_db_link('read'); - if ($result = mysql_query("$query", $dblink)) { + $resultarray = array(); + + if ($result = execute_query("$query", $dblink)) { while ($row = mysql_fetch_object($result)) { if (!empty($callback) && is_callable($callback)) { $row = $callback($row); @@ -185,9 +277,6 @@ } } - if (mysql_errno($dblink)) - throw new DatabaseException(mysql_error($dblink) . " QUERY: " . $query); - if (empty($resultarray)) { if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) error_log("WARNING: DB query \"$query\" returned no results."); @@ -211,22 +300,23 @@ function get_data_row($query) { - global $CONFIG, $dbcalls, $DB_PROFILE, $DB_QUERY_CACHE; - - $dblink = get_db_link('read'); + global $CONFIG, $DB_QUERY_CACHE; - if (isset($DB_QUERY_CACHE[$query])) { - if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) + // Is cached + $cached_query = $DB_QUERY_CACHE[$query]; + if ($cached_query) { + if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) error_log ("$query results returned from cache"); - return $DB_QUERY_CACHE[$query]; - } - - $dbcalls++; - - if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) - $DB_PROFILE[] = $query; + + if ($cached_query === -1) + return false; // Last time this query returned nothing, so return false + + return $cached_query; + } + + $dblink = get_db_link('read'); - if ($result = mysql_query("$query", $dblink)) { + if ($result = execute_query("$query", $dblink)) { $row = mysql_fetch_object($result); @@ -238,9 +328,6 @@ if ($row) return $row; } - if (mysql_errno($dblink)) - throw new DatabaseException(mysql_error($dblink) . " QUERY: " . $query); - if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) error_log("WARNING: DB query \"$query\" returned no results."); @@ -256,29 +343,18 @@ function insert_data($query) { - global $CONFIG, $dbcalls, $DB_PROFILE, $DB_QUERY_CACHE; + global $CONFIG, $DB_QUERY_CACHE; $dblink = get_db_link('write'); - - $dbcalls++; - - if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) - { - $DB_PROFILE[] = $query; - - //error_log("--- DB QUERY --- $query"); - } // Invalidate query cache if ($DB_QUERY_CACHE) $DB_QUERY_CACHE->clear(); if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) error_log("Query cache invalidated"); - if (mysql_query("$query", $dblink)) + if (execute_query("$query", $dblink)) return mysql_insert_id($dblink); - if (mysql_errno($dblink)) - throw new DatabaseException(mysql_error($dblink) . " QUERY: " . $query); return false; } @@ -292,30 +368,18 @@ function update_data($query) { - global $dbcalls, $CONFIG, $DB_PROFILE, $DB_QUERY_CACHE; - - $dblink = get_db_link('write'); + global $CONFIG, $DB_QUERY_CACHE; - $dbcalls++; - - if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) - { - $DB_PROFILE[] = $query; - - //error_log("--- DB QUERY --- $query"); - } + $dblink = get_db_link('write'); // Invalidate query cache if ($DB_QUERY_CACHE) $DB_QUERY_CACHE->clear(); if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) error_log("Query cache invalidated"); - if (mysql_query("$query", $dblink)) + if (execute_query("$query", $dblink)) return true; //return mysql_affected_rows(); - - if (mysql_errno($dblink)) - throw new DatabaseException(mysql_error($dblink) . " QUERY: " . $query); - + return false; } @@ -329,46 +393,21 @@ function delete_data($query) { - global $dbcalls, $CONFIG, $DB_PROFILE, $DB_QUERY_CACHE; + global $CONFIG, $DB_QUERY_CACHE; $dblink = get_db_link('write'); - - $dbcalls++; - - if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) - { - $DB_PROFILE[] = $query; - - //error_log("--- DB QUERY --- $query"); - } // Invalidate query cache if ($DB_QUERY_CACHE) $DB_QUERY_CACHE->clear(); if ((isset($CONFIG->debug)) && ($CONFIG->debug==true)) error_log("Query cache invalidated"); - if (mysql_query("$query", $dblink)) + if (execute_query("$query", $dblink)) return mysql_affected_rows(); - - if (mysql_errno($dblink)) - throw new DatabaseException(mysql_error($dblink) . " QUERY: " . $query); - + return false; } - /** - * Returns the number of rows returned by the last select statement, without the need to re-execute the query. - * - * CANDIDATE FOR DELETION? - * - * @return int The number of rows returned by the last statement - */ - function count_last_select() { - $row = get_data_row("SELECT found_rows() as count"); - if ($row) - return $row->count; - return 0; - } /** * Get the tables currently installed in the Elgg database -- cgit v1.2.3