Dynamically cache requests and responses in a single mysql table

RequestCache.php

<?php

/**
 * Dynamically cache requests and responses in a single mysql table
 */
class RequestCache{
	
	// Configurable Options
	// The mysql options (except for $mysql_table) may be left blank if a $pdo
	// connection is passed to the constructor.
	private $mysql_host = 'localhost';
	private $mysql_db = 'mydatabase';
	private $mysql_user = 'myusername';
	private $mysql_pw = 'mypassword';
	private $mysql_table = 'request_cache';
	private $cache_time_days = 30;
	
	// Do not alter below this
	private $pdo;
	private $paramColumns = array();
	private $script_path;
	private $isCached = false;
	private $response = "";
	private $response_content_type;
	private $request_method;
	private $response_age = 0;
	
	/**
	 * constructor
	 * @param type $script_path
	 * @param type $pdo
	 */
	public function __construct($script_path, $pdo=false){
		$this->script_path = $script_path;
		$this->request_method = $_SERVER['REQUEST_METHOD'];
		if(!$pdo) $this->loadPDO();
		else $this->pdo = $pdo;
		$attr = $this->pdo->getAttribute(PDO::ATTR_CASE);
		$this->pdo->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
		$this->checkTable()->checkRequest();
		$this->pdo->setAttribute(PDO::ATTR_CASE, $attr);
	}
	
	/**
	 * Cache the response
	 * @param type $response
	 * @param type $contentType
	 * @return $this
	 */
	public function cacheResponse($response, $contentType=""){
		$sql = "INSERT INTO `{$this->mysql_db}`.`{$this->mysql_table}` ";
		$cols = array('script_path', 'response', 'cache_time', 'content_type', 'request_method');
		$vals = array($this->script_path, $response, time(), $contentType, $this->request_method);
		foreach($_POST as $param=>$val){
			$cols[] = "p_$param";
			$vals[] = $val;
		}
		foreach($_GET as $param=>$val){
			$cols[] = "g_$param";
			$vals[] = $val;
		}
		$sql .= "(`".implode("`, `", $cols)."`) VALUES ";
		$sql .= "(".trim(str_repeat("?, ", count($cols))," ,").")";
		$this->pdo->prepare($sql)->execute($vals);
		return $this;
	}
	
	/**
	 * Check if the request has been cached already
	 * @return $this
	 */
	private function checkRequest(){
		// Make sure a column exists for every request parameter
		$q = $this->pdo->query("SHOW COLUMNS FROM `{$this->mysql_db}`.`{$this->mysql_table}`");
		if(empty($this->paramColumns)){
			while($res = $q->fetch(PDO::FETCH_ASSOC)){
				if(in_array($res['Field'], array("id", "script_path", "response", "cache_time", "content_type", "request_method"))) continue;
				$this->paramColumns[$res['Field']] = intval(trim(substr($res['Type'], strpos($res['Type'], "(")), " ()"));
			}
		}
		$notFound = false;
		$n = 0;
		$sql = "SELECT `id`, `response`, `cache_time`, `content_type` "
			. "FROM `{$this->mysql_db}`.`{$this->mysql_table}` "
			. "WHERE script_path = :sp "
			. "AND request_method = :rm";
		$params = array(
			":sp"=>$this->script_path,
			":rm"=>$this->request_method
		);
		foreach($_POST as $param=>$val){
			$n++;
			$len = strlen($val)+1;
			if(!array_key_exists("p_".$param, $this->paramColumns)){
				$this->pdo->query("ALTER TABLE `{$this->mysql_db}`.`{$this->mysql_table}` ADD COLUMN p_$param varchar($len)");
				$this->paramColumns["p_$param"] = $len;
				$notFound = true;
			}
			if($this->paramColumns["p_$param"] < $len){
				$this->pdo->query("ALTER TABLE `{$this->mysql_db}`.`{$this->mysql_table}` MODIFY COLUMN p_$param varchar($len)");
				$this->paramColumns["p_$param"] = $len;
				$notFound = true;
			}
			$params[":p$n"] = $val;
			$sql .= " AND `p_$param` = :p$n";
		}
		foreach($_GET as $param=>$val){
			$n++;
			$len = strlen($val)+1;
			if(!array_key_exists("g_".$param, $this->paramColumns)){
				$this->pdo->query("ALTER TABLE `{$this->mysql_db}`.`{$this->mysql_table}` ADD COLUMN g_$param varchar($len)");
				$this->paramColumns["g_$param"] = $len;
				$notFound = true;
			}
			if($this->paramColumns["g_$param"] < $len){
				$this->pdo->query("ALTER TABLE `{$this->mysql_db}`.`{$this->mysql_table}` MODIFY COLUMN g_$param varchar($len)");
				$this->paramColumns["g_$param"] = $len;
				$notFound = true;
			}
			$params[":p$n"] = $val;
			$sql .= " AND `g_$param` = :p$n";
		}
		
		foreach($this->paramColumns as $param=>$len)
			if(!array_key_exists(substr($param, 2), $_POST) && !array_key_exists(substr($param, 2), $_GET))
				$sql .= " AND `$param` IS NULL";
		if(!$notFound){
			$q = $this->pdo->prepare($sql);
			$q->execute($params);
			$resp = $q->fetch(PDO::FETCH_ASSOC);
			if(!empty($resp)){
				$max_cache_age_seconds = 60*60*24*$this->cache_time_days;
				$cache_age = time() - $resp['cache_time'];
				if($max_cache_age_seconds >= $cache_age){
					$this->isCached = true;
					$this->response = $resp['response'];
					$this->response_content_type = $resp['content_type'];
					$this->response_age = $cache_age;
					$this->respond();
				}else{
					$this->pdo->query("DELETE FROM `{$this->mysql_db}`.`{$this->mysql_table}` WHERE id = {$resp['id']}");
				}
			}
		}
		return $this;
	}
	
	/**
	 * Send the response back to the client
	 */
	private function respond(){
		if(!empty($this->response_content_type))
			header("Content-Type: {$this->response_content_type}");
		header("x-cached: ".($this->isCached ? "true" : "false"));
		header("x-cache-age: {$this->response_age}");
		echo $this->response;
		exit;
	}
	
	/**
	 * Make sure the table exists
	 * @return $this
	 */
	private function checkTable(){
		$this->pdo->query("CREATE TABLE IF NOT EXISTS `{$this->mysql_db}`.`{$this->mysql_table}` (
			`id` INT NOT NULL AUTO_INCREMENT,
			`script_path` VARCHAR(2500) NULL,
			`response` BLOB NULL,
			`cache_time` INT NULL,
			`content_type` VARCHAR(100) NULL,
			`request_method` VARCHAR(5) NULL,
			PRIMARY KEY (`id`));");
		return $this;
	}
	
	/**
	 * Create a PDO connection
	 * @return $this
	 */
	private function loadPDO(){
		$this->pdo = new PDO(
			'mysql:host='.$this->mysql_host.';'.
			'dbname='.$this->mysql_db.';'.
			'charset=utf8', 
			$this->mysql_user, 
			$this->mysql_pw
		);
		return $this;
	}
	
}