Created
June 8, 2017 16:54
-
-
Save Pamblam/577969b75d1cadf63e4db13603dc7c73 to your computer and use it in GitHub Desktop.
Dynamically cache requests and responses in a single mysql table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment