Accessing Databases with ADODB - Caching
(Page 11 of 12 )
ADODB has a built-in caching mechanism that you can invoke using a special function to run your queries. Instead of calling Execute(), use CacheExecute() for a query. The first argument to CacheExecute() is how many seconds you want the result of the query cached. The rest of the arguments are the same as Execute():
// Cache this recordset for 10 seconds
$rs = $conn->CacheExecute(10,'SELECT flavor, price,
calories FROM ice_cream');
Record sets are cached in individual files. The filename is calculated from the SQL query passed to CacheExecute() and information from the connection handle: the database type, the database name, and the database user. For the purpose of calculating a cache filename, these values are all case-sensitive. If you call CacheExecute() twice with two queries that are identical to the database but have different spacing or capitalization, the second call to CacheExecute() doesn’t retrieve the data from the cache—it sends the query to the database. The query, database type, database name, and database user must be exactly the same for arecord set to be retrieved from the cache.
By default, ADODB puts its cache files in subdirectories it creates under the /tmp directory. You can change this base cache directory by setting the value of the global variable $ADODB_CACHE_DIR to your chosen directory name. ADODB’s caching behavior requires you to consider the permissions on the cache files and directories carefully to make sure that sensitive data is not exposed. First, the user that your Web server is running as must have write permission to $ADODB CACHE_DIR. The cache subdirectories created under $ADODB_CACHE_DIR have their permissions set so that the Web server user and group can read and write to them, but other users have only execute permission. This opens two potential security holes. First, if a user can guess the name of a cache file, then they can read the data inside it. Second, if you are in a shared-hosting environment where all users’ sites are served by the same Web server process, any user can read all of the cache files. To deal with these potential security risks, set $ADODB_CACHE_DIR to a new directory that other users can’t access. Each user in a shared hosting environment should ideally have their own space in the file system that the Web server serving their site can read but other Web servers can’t.
Record sets expire from the cache when their time, as specified to CacheExecute(), expires, but you have two ways to explicitly expire a record set. The first is to call CacheExecute() with a time value of 0. This forces ADODB to send the query to the database and overwrite any possibly cached values with new results. The second way is to call the CacheFlush() method. With no arguments, CacheFlush() removes everything from the cache. If you pass an SQL query to CacheFlush(), it removes from the cache the record set that corresponds to that query.
Both CacheExecute() and CacheFlush() calculate cache filenames based on the SQL query after any placeholders have been replaced. Both the query that contains placeholders and the placeholder array must be identical for two queries to have the same cache file:
// Get inexpensive ice cream
$rs = $conn->CacheExecute(10, 'SELECT flavor FROM ice_cream
WHERE price < ?', array(4));
// Purge the query from the cache
$conn->CacheFlush('SELECT flavor FROM ice_cream
WHERE price < ?',array(4));
 This is from Essential PHP Tools, by David Sklar (Apress, ISBN 1590592808). Check it out at your favorite bookstore today. Buy this book now. |
Next: Exporting Data >>
More Database Articles
More By Apress Publishing
/ 37 
