Bastiaans Blog

Today I rewrote the previous mysql - memcache classes, mentioned in my previous post.
I wrote a clas with static methods (why should you need an object at all?) with logic. supporting an easy way store items in a PHP application. A second DB class extends this one, for an easy way of caching complicated queries.

/** @author Bastiaan Welmers
  *
  * 2009-01-13
  *
  * Holds memcache instance for caching stuff
  */

class Welmers_Memcache
{
	/**
	 * memcached ID for the ID list
	 */

	const IDLIST = '__idlist_RANDOMCHARS';

	/**
	 * Holds the memcache instance
	 */

	private static $_memcache;

	/**
	 * Holds the application key generated bij init()
	 * @see init()
	 */

	private static $_appKey;

	/**
	 * TTL of the objects in memcached in seconds
	 *
	 */

	public static $timeout = 1800;

	/**
	 * Get list of IDs
	 *
	 * @return array with queries as keys and unix timestamps as value
	 */

	public static function getIdList()
	{
		return self::get(self::IDLIST);
	}

	/**
	 * Get the queries that are cached in this object
	 * from memcache
	 *
	 * @param string unique ID to identify object
	 * @return mixed|bool object in cache, or false on error
	 */

	public static function get($id)
	{
		if (!self::$_memcache instanceof Memcache)
			self::init();

		return self::$_memcache->get(self::$_appKey . $id);

	}

	/**
	 * Store object into memcache
	 *
	 * @param string unique ID to identify object
	 * @param mixed object to store
	 * @return bool true on succes, false on failure
	 */

	public static function set($id, $value, $timeout = null)
	{
		if ($timeout == null)
			$timeout = self::$timeout;

		if (!self::$_memcache instanceof Memcache)
			self::init();

		if ($id != self::IDLIST)
		{ // save ID
			$idList = self::get(self::IDLIST);

			if (!is_array($idList))
				$idList = array();

			$idList[$id] = time() + $timeout;

			self::set(self::IDLIST, $idList, $timeout + 2);
		}

		return self::$_memcache->set(self::$_appKey . $id, $value, 0, $timeout);
	}

	/**
	 * expires (deletes) items from memcache
	 *
	 * @param string unique ID to identify object, ommit to flush entire cache (removes every stored object)
	 */

	public static function expire($id = null)
	{

		if (!self::$_memcache instanceof Memcache)
			self::init();

		$idList = self::get(self::IDLIST);

		if (!is_array($idList))
			$idList = array();

		if ($id == null)
		{ // remove all IDS
			foreach($idList as $expireId => $timeout)
			{
				self::$_memcache->delete($expireId);
			}
			$idList = array();
			self::set(self::IDLIST, $idList, self::$timeout);
		} else { //specific item
			if (isset($idList[$id]))
			{
				self::$_memcache->delete(self::$_appKey . $id);
				unset($idList[$id]);
				self::set(self::IDLIST, $idList, self::$timeout);
			}
		}
	}

	/**
	 * Initiates memcache.
	 *
	 * @param int $timeout TTL of every stored object, if ommitted, self::$timeout will be used, 1800 seconds.
	 */

	public static function init($timeout = null)
	{
		if ($timeout != null)
			self::$timeout = $timeout;

		self::$_memcache = new Memcache();
		self::$_memcache->connect('localhost', 11211) or die ("Could not connect to memcache");
		self::$_appKey = md5(__FILE__);
	}
}

Example for using this class:

function get_difficult_var()
{
      if (!$difficult_var = Welmers_Memcache::get('difficult_var'))
      {
          // difficult logic to create difficult var
          Welmers_Memcache::set('difficult_var', $difficult_var, 3600 /*timeout*/);
      }
      return $difficult_var
}

To remove this var, use

Welmers_Memcache::expire('difficult_var')

To expire everything inserted in this application:

Welmers_Memcache::expire()

The next class is useable for complicated MySQL queries, and replaces mysql_query.

/** @author Bastiaan Welmers
  *
  * 2009-01-13
  *
  * Cache mysql queries
  *
  */

class Welmers_Memcache_Db extends Welmers_Memcache
{
	/**
	 * IDPREFIX - salt prefix for memcached ID for queries
	 * should be random value
	 */

	const IDPREFIX = 'aasd79tpbo1234'; 

	/**
	 * QUERIES_PREFIX - prefix for memcached ID storing cached queries
	 */

	const QUERIES_PREFIX = '__queries';

	/**
	 * Get the queries that are cached in this object
	 *
	 * @return array with queries as keys
	 */

	public static function getQueries()
	{
		return self::get(self::IDPREFIX . self::QUERIES_PREFIX);
	}

	/**
	 * Get the queries that are cached in this object
	 *
	 * @return array with queries as keys
	 */

	private static function _saveQuery($sql)
	{
		$queries = self::get(self::IDPREFIX . self::QUERIES_PREFIX);
		if (!is_array($queries))
			$queries = array();
		$queries[$sql] = true;
		self::set(self::IDPREFIX . self::QUERIES_PREFIX, $queries, self::$timeout + 2);
	}

	/**
	 * Helper function for mysql_query and delete* functions
	 *
	 * @param string $sql SQL query
	 * @return string with ID for memcached for this query
	 */

	private static function _getQueryKey($sql)
	{
		return md5(self::IDPREFIX . $sql);
	}

	/**
	 * The cached version of the plain mysql_query funtion
	 * Returns multidimensional array with al return values.
	 *
	 * @param string $sql SQL query
	 * @param resource $linkIdentifier optional link identifier of the mysql connection
	 * @return array multidimensional array with all returned records
	 */

	public static function mysql_query($sql, $linkIdentifier = false)
	{

		if (!($cache = self::get(self::_getQueryKey($sql))))
		{
			$cache = array();
			$r = ($linkIdentifier !== false) ? mysql_query($sql, $linkIdentifier) : mysql_query($sql);
			if (is_resource($r) && (($rows = mysql_num_rows($r)) != 0))
			{
				for ($i=0; $i < $rows; $i++) {
					$fields = mysql_num_fields($r);
					$row = mysql_fetch_array($r);
					for ($j = 0; $j  $true)
			{
				if (strpos($query, $searchItem) !== false)
				{
					self::expire(self::_getQueryKey($query));
					unset($queries[$query]);
				}
			}
		}

		self::set(self::IDPREFIX . self::QUERIES_PREFIX, $queries, self::$timeout + 2);

	}

	/**
	 * Deletes all queries that are cached with this application
	 * Only use this for a global flush functionality, i.e. not for
	 * every change of a particular table.
	 * (then use deleteMatching('tablename') instead)
	 *
	 */

	public function deleteAll()
	{
		$queries = self::get(self::IDPREFIX . self::QUERIES_PREFIX);
		if (!is_array($queries))
			$queries = array();

		foreach($queries as $query => $true)
		{
			self::expire(self::_getQueryKey($query));
			unset($queries[$query]);
		}

		self::set(self::IDPREFIX . self::QUERIES_PREFIX, $queries, self::$timeout + 2);

	}
}

Example of this use would be easy:

// example query, quite complicated for mysql to calculate
$query = "SELECT
   DATE_FORMAT(lb.datum, '%Y') AS year,
   DATE_FORMAT(lb.datum, '%d-%m-%Y') AS datum_formatted,
   DATE_FORMAT(lb2.datum, '%d-%m-%Y') AS datum_tot_formatted,
lp.lidnummer, lp.bedrijfsnaam, lp.voornaam, lp.tussenvoegsel, lp.achternaam
FROM leden_betaalmethodearchief lb
LEFT JOIN leden_persoonlijk lp ON lp.lidnummer = lb.lidnummer
LEFT JOIN leden_betaalmethodearchief lb2 ON
       lb.lidnummer = lb2.lidnummer AND
       lb2.datum > lb.datum AND
       lb2.betaalmethode != 'acceptgiro'
WHERE lb.betaalmethode = 'acceptgiro' ORDER BY lb.datum ASC
";
$result = Welmers_Memcache_Db::mysql_query($query);
// instead of $result = mysql_query($query)

foreach($result as $row) {
// instead of while ($row = mysql_fetch_assoc($result) {
     print $row['field'];
     // …..
}

Have a lot of fun!

Tags: memcache, mysql, php

This entry was posted on Wednesday, January 14th, 2009 at 9:30 pm and is filed under English, IT. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.