Your browser does not seem to support CSS. If images appear below, please disregard them.
It appears that you're running an Ad-Blocker. This site is monetized by Advertising and by ">User Donations; we ask that if you find this site helpful that you whitelist us in your Ad-Blocker, or make a ">Donation to help aid in operating costs.
Previous Thread
Next Thread
Print Thread
Rate This Thread
#45751 - 05/31/08 10:02 AM PHP5 OOP - Caching out SQL query results securly  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
Sup peoples. I promised another tut on PHP5 caching and I am actually going to do it. If you are like me, you have spent hours shaving precious Milli seconds off queries trying to speed up applications. Maybe you have that one query that hits 10 tables across 3 databases. Damn shame. Meanwhile your applications gets bogged down. Your users complain the site has slowed down since the new features. You know you want to keep the application but the time is killing you.

Or maybe your hoster has said your application take up too many resources. I first looked into caching when Gizmo mentioned it to me. About a year maybe two years ago, we were yucking it up on the tele. I thought, caching... Ole red beard is drinking again. Caching is for desktops. Sigh, seems I was wrong.

While working at the Census Department I coded quite a few LARGE data apps. There are over 300,000,000 people living in the US. So just imagine database table with more rows than 300,000,000 rows. Now we would partition the tables, that helped a lot. We had one kick [censored] DBA. But in the end, I still was lacking some speed. Enter caching

If you have data you know you can count on being the same for 15 minutes or more in a high use environment... Caching is for you. When I started thinking seriously about caching I layed out some requirements.


  • The dataset being cached had to be secure (no web surfer should be able to directory surf and get them.)
  • The file names had to be unique enough That I could reasonably be sure now accidental overwriting would happen
  • The structure of the save data would have to be easy to work with. (I didn't want to write whole new code just to read and work with cache files)
  • I had to be able to code this thing one time and use it for all apps.
  • It had to document the cache files it created.
  • It had to be flexible and allow me to change a few things.
    • File extensions it uses for the cache files
    • The hashing algarythm it uses, Not all systems use SHA1
    • The time frame a cache is good for.


So with my requirements I set off to code a SQL (or array caching system.)

Sponsored Links
#45752 - 05/31/08 10:14 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
Below is what your buddy §intå× came up with. As always it is heavily documented. I will cover its uses below.

PHP Code



<?php
/**
* This file houses the cache class. It will handle the caching of data for sql queries and markup.
*
* This file exists because I belive if you do something more than once you should find a way to
* abstract it to a single call
* @package sqlCache
* @since 6/22/2007 22:10
* @author Russell Vance <[email protected]>
* @license http://www.opensource.org/licenses/lgpl-2.1.php
*/

/**
* This class will generate cache files needed to speed up processing by the other classes
*
* The methods in this class are very powerful and should cut back time spent on sql queries
* @version 1.0.1
*/
class cacheMgr{

/**
* A numeric value for the hash type you want used in the naming of your files.
* 1 = md5
* 2 = sha1
* The default is 1 md5
*
* @access public
* @var integer
*/
public $hashType;

/**
* The number of seconds untill the cache file is expired.
* 1 hrs = 3600
* 6 hrs = 21600 etc, etc etc...
*
* @access public
* @var integer
*/
public $expierTime;

/**
* A boolean value that tells the class wether or not to secure the cache file
*
* @access public
* @var boolean
*/
public $secure;

/**
* A string value that tells the the file extension you want for the cache files. Defaults to '.tmp'
*
* @access public
* @var string
*/
public $fileExt;

/**
* The full file path to your cache directory
*
* @access public
* @var string
*/
public $cachePath;

/**
* This is the class constructor. Here you can set some options for your cached files.
*
* This constructor requires 3 parameters for the class to initialise. You can see how to initialize
* the class in the example below:
* <code>
* //Example:
*
* $cache = new cacheMgr(1, 3600, true, 'php');
* $cache2 = new cacheMgr('sha1', 86400, false, 'inc');
*
* </code>
* The above code would create two new cacheMgr objects.
* The files with the first object created would be called and named with an
* md5 hash algorithm, They would expire after 1 hour and they would be made
* secure from outside viewers.
*
* The the files created from the second object would be named using the sha1 algorithm
* They would expier after 24 hrs. They would not be secured. And they would use the
* default file extension.
* @access public
* @param integer $hashType options are 1, 2 or you can use the string identifiers md5, sha1. <br />1 returns md5 hash named files, 2 returns sha1 hash named files
* @param integer $expierTime The number of seconds the cahe file is good for. Defaults to 3600.
* @param boolean $secure True now direct web access. False, allow web access to cache files.
* @param string $fileExt The cache file, file extension to use.
* @return void
*/
public function __construct($hashType = 1, $expierTime = 3600, $secure, $fileExt = 'tmp', $cachePath){
$this->hashType = $hashType;
$this->expierTime = (int)$expierTime;
$this->secure = (bool)$secure;
$this->fileExt = (string)$fileExt;
$this->cachePath = (string)$cachePath;
}

/**
* The class destructor
*
* Cleans up after the class and frees up memory. No need to call it.
* @access public
* @param void
* @return void
*/
public function __destruct(){
unset(
$this->hashType);
unset(
$this->expierTime);
unset(
$this->secure);
unset(
$this->fileExt);
unset(
$this->cachePath);
}

/**
* This method will return the md5 or sha1 hash for a pain text string.
*
* This method exists so I do not have to define both hash methods in every cacheOutxxx method.
* @access private
* @param string $plainText The plain text string to be hashed
* @return string On sucess, return the newly created hash value. On fail return false.
*/
private function getHash($plainText){
switch(
$this->hashType){
case
1 || 'md5':
default:
$newHash = md5($plainText, false);
break;
case
2 || 'sha1':
$newHash = sha1($plainText, false);
break;
}
return
$newHash;
}

/**
* This method is used to cache out an enumerated array
*
* This method will cache out an enumerated array into a php script that can be called.
* This is usually done to avoid having to do complex queries. Note this method is only
* good for single demensional arrays at this time.
* @access public
* @param array $array An enumerated array to cache out
* @param string $uniqueName A string value passed in that will not be passed in for another cache. Usualy $_GET parameters, or query keys from the sqlClass
* @return string|bool On sucess returns the file name created. On fail returns false.
*/
public function cacheOutArray($array, $uniquieId){
$uniquieId = str_replace(' ', '_', $uniquieId);
if(
is_array($array)){
$outString = "\$".$uniquieId." = '".json_encode($array)."';";
}
if(
$fileName = $this->setCacheFile($uniquieId,$outString)){
$retVal = $fileName;
}else{
$retVal = false;
}
return
$retVal;
}

/**
* This method returns data that has been cached out.
*
* This method accepts a unique identifier that would be used to name a cache file, generates
* a hash of it and check if a file by that name exists. If it does it will include the file.
* @access public
* @uses cache::cachePath Used to define the path to the cache directory
* @uses DS A Constant for DIRECTORY_SEPERATOR
* @param string $uniqueId A unique identifier used to generate a hash value
* @return boolean On sucess returns true. On fail returns false.
*/
public function getCache($fileName){
$filePath = $this->cachePath;
if(
file_exists($fileName)){
@include_once(
$filePath.$fileName);
return
true;
}else{
return
false;
}
}

/**
* This method returns an array that has been cached out.
*
* This method accepts a unique identifier that would be used to name a cache file, generates
* a hash of it and check if a file by that name exists. If it does it will include the file
* and return the array.
* @access public
* @uses cache::cachePath Used to define the path to the cache directory
* @uses DS A Constant for DIRECTORY_SEPERATOR
* @param string $uniqueId A unique identifier used to generate a hash value
* @return array|boolean Returns a stored array object on sucess. On fail returns false.
*/
public function getArrayCache($uniquieId){
$uniquieId = str_replace(' ', '_', $uniquieId);
$fileName = $this->getHash($uniquieId).'.'.$this->fileExt;
$filePath = $this->cachePath;
if(
file_exists($filePath.$fileName)){
include_once(
$filePath.$fileName);
//print_r(json_decode($getOptions, true));
$arr = json_decode( $$uniquieId, true );
return
$arr;
}else{
return
false;
}
}

/**
* This method is used to generate a docblock for the cache file
*
* This method will auto gen a doc block for the cach file with the time it was created in human readable format
* and noting the file was created by this class and not a human. This might help future proofing human error.
* @access private
* @param void
* @return string Returns a dockblock comment that will go in a head section of a cache file.
*/
private function getCacheDocBlock(){
$tmStmp = date('F d, Y - H:i:s T');
$expires = date('F d, Y - H:i:s T', mktime(date('H'), date('i'), date('s'), date('m'), date('d')+1, date('Y')));
$comment = "\n/** \n* Cache file created by cacheMgr class \n* \n* You should not edit this file unless you know what you are doing. \n* If you do edit the file and the ads system crashes, just delete \n* this file. It will be re-built.\n* @version $tmStmp \n* @expires $expires \n* @author CAMS::cacheMgr \n*/";
return
$comment;
}

/**
* This method creates a cache file
*
* Takes incoming string data and writes it out to a file. If class var $secure is true ads a constant
* check to the top of the class.
* @access private
* @uses cache::cachePath Used to define the path to the cache directory
* @uses DS A Constant for DIRECTORY_SEPERATOR
* @param string $fileName A md5 or sha1 generated hash and file extension file name.
* @param string $outString The data that is to be writen out to a file.
* @return boolean On sucess return true. On fail return false.
*/
private function setCacheFile($uniquieId, $outString){
$uniquieId = str_replace(' ', '_', $uniquieId);
$fileName = $this->getHash($uniquieId) . '.' . $this->fileExt;
$outString = $this->prepStrForOut($outString);
$cacheFile = $this->cachePath . $fileName;
if(
file_put_contents($cacheFile, $outString) === false){
return
false;
}
return
$fileName;
}

/**
* This method preps a string that will be cached out with header and footer charaters it will need
*
* If the request is to create a secured cache file, a check for a joomla defined constant will be added to
* the header.
* @access private
* @param string $outString The string that will be written out to a file
* @return string A new string with header and footer data will be returned.
*/
private function prepStrForOut($outString){
if(
$this->secure){
$outHeader = "<"."?php ".$this->getCacheDocBlock()." \ndefined( '_VALID_AMS_' ) or die( 'Restricted access' ); \n";
}else{
$outHeader = "<?php
"
.$this->getCacheDocBlock()." \n";
}
$outFooter = " \n?".">";
$outString = $outHeader . $outString . $outFooter;
return
$outString;
}
}
?>

#45753 - 05/31/08 10:40 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
Ok, to use this you have to re-think how you use MySQL. This isn't a bad thing. You probably code in a procedural style and thats fine. But you probably treat SQL statements like strings, thats not so fine. SQL is a language that can do things to your database that will make baby Jesus cry. It is time to treat SQL like what it is. An object in your code. Enter prepared statements.

http://www.php.net/manual/en/mysqli-stmt.prepare.php

Now you can use mysqli_stmt as a procedural thing but if you are changing, I suggest you use it in an OOP way. What I do now in building my apps is place all SQL into an array writen as prepared statements. When I need them I prepare them and slam the prepared statements into an array for use.

The [censored] did he call me you say.... Nah, it isn't as hard as it sounds.

Below is a short example of a function that might store an array of prepared statement ready queries.
PHP Code


function queries($key){
$queries = array(
'quer1' => 'SELECT * FROM `tableA` WHERE `id` = ?',
'quer2' => 'SELECT * FROM `tableb` WHERE `id` = ?,
'
quer3' => 'SELECT
`tableb`.`col1`,
`
tableb`.`col2`,
`
tableb`.`col3`,
`
tablea`.`col1`,
`
tablea`.`col2`
FROM
`tablea`,
`
tableb`
WHERE
`tablea`.`id` = ?
AND
`
tableb`.`id` = ?'
);
if(!array_key_exists($key, $queries)){
$retVal = false;
}else{
$retVal = $queries[$key];
}
}



Now below is how we might use this.
PHP Code



function getTableA_Data($id){
$key = 'quer1';
if(
$query = queries($key)){
//Query is now an object, it runs faster, and I can use it over and over in a loop!!!
$stmt->prepare($query);
}else{
//handle no query available here
}

//This will replace '?' with the value I set. The "i" means integer. This is secure. More so than the old way of doing things
$stmt->bind_param("i", $id);

//Execute the query
$stmt->execute();
$stmt->bind_result($col1, $col2, $id);
WHILE(
$stmt->fetch()){
$rows[] = array(
'col1' => $col1,
'col2' => $col2,
'id' => $id
);
}
//Kill our prepared statement and free system resources
$stmt->close();
return
$rows;
}


Now if I wanted a data set out of tablea in the rest of me code I just do this

PHP Code


$tableaRows
= getTableA_Data(3);


By the way I would never put all that in a function in real world code. I have centralized the handling of all prepared statements in my own wrapper of sorts. I needed to touch on this to make the caching work.

So as it stands. the queries are stored in an array in a function and are served up [censored] needed. Each query is handled by its own function. But how do we keep from executing the query every time?

Last edited by §intå×; 05/31/08 10:54 AM.
#45754 - 05/31/08 11:03 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
The answer is simple. First we instantiate our class.

PHP Code


/**
* Defaults are
* hashing MD5
* time in seconds 3600
* secure mode NULL
* cache dir NULL
*/
$cache = new cache( 2, //hash type (1) md5 (2) Sha1
3600, //Timer seconds
true, //secure mode
'php', //file extensions
'/path/to/cache/directory' //Cache dir
);


#45755 - 05/31/08 11:19 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
Then we pass it into our functions that will do our queries. THis would be a great use of the registry pattern I posted yesterday.
PHP Code


$reg
= new registry();
$cache = new cache( 2, 3600, true, 'php', '/path/to/cache/directory' );
$reg->set('cache',$cache);


function
getTableA_Data($id, $reg){
if(!
$rows = $reg->get('cache')->getArrayCache('quer1'.$id)){
if(
$query = queries('quer1')){
//Query is now an object, it runs faster, and I can use it over and over in a loop!!!
$stmt->prepare($query);
}else{
//handle no query available here
}

//This will replace '?' with the value I set. The "i" means integer. This is secure. More so than the old way of doing things
$stmt->bind_param("i", $id);

//Execute the query
$stmt->execute();

//Bind the results to variables
$stmt->bind_result($col1, $col2, $id);
WHILE(
$stmt->fetch()){
$rows[] = array(
'col1' => $col1,
'col2' => $col2,
'id' => $id
);
}
//Kill our prepared statement and free system resources
$stmt->close();
$cache->cacheOutArray($rows, 'quer1'.$id);
$rows = $cache->getArrayCache('quer1'.$id);
}
return
$rows;
}


So what just happened here? Well when we first enter the functions we call on the cache class method getArrayCache() to see if out cache file exists. If it does and the cache timer has not expired we return the results. If not we process the query and overwrite any cache file with the same name. The file names are created using the query keys and the parameter values we use in our queries. These are concatenated together and then hashed using either MD5 or SHA1.

Okay you drunks. You have code and examples. Hit me with any and all questions. If you improve it, I am interested in what you did. Please post your modified code.

Sponsored Links
#45756 - 05/31/08 11:32 AM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
Requirements of this class.



PHP Code


define
('_VALID_AMS_', 1);


You can change '_VALID_AMS_' but you need to find it in my class and change it there too. The above examples are how I use it and find it useful. Please feel free to experiment with it. The code is released under lgpl to use and learn with. It is hoped it will be useful but no guarantees. Use at your own risk. Please throughly test before using in a production environment.


Last edited by §intå×; 05/31/08 11:33 AM.
#45761 - 05/31/08 03:16 PM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]  
Joined: Feb 2002
Posts: 7,198
Gremelin Offline
Community Owner
Gremelin  Offline

Community Owner

Joined: Feb 2002
Posts: 7,198
Likes: 11
Portland, OR; USA
For some reason I still feel like i failed... well, in teaching you how to format your code... you have so many un-needed tabs... i didnt' even look for double+ spaces... bad bad bad...


Donate to UGN Security here.
UGN Security, Back of the Web, and VNC Web Services Owner
#45762 - 05/31/08 10:23 PM Re: PHP5 OOP - Caching out SQL query results securly [Re: Gremelin]  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
Much of my code was altered on posting, but it is well formatted here on my desk top. The class itself was coded a bit ago and should look fine to you. Some of my examples are just spaces and you BBS deciding how many. Can't use tab in this post box.

In my haste to get this out I forgot to post an example of what a cache file might look like.
PHP Code



<?php
/**
* Cache file created by cacheMgr class
*
* You should not edit this file unless you know what you are doing.
* If you do edit the file and the ads system crashes, just delete
* this file. It will be re-built.
* @version February 13, 2008 - 23:40:06 EST
* @expires February 14, 2008 - 23:40:06 EST
* @author CAMS::cacheMgr
*/
defined( '_VALID_AMS_' ) or die( 'Restricted access' );
$_core_GetPkgNames = '["default","tkt","dorxad"]';


From "defined( '_VALID_AMS_' ) or die( 'Restricted access' ); " up to the top, the cache script generates that. You can alter the comments in the head of the document by editiing the private method "getCacheDocBlock()" in my class.

Also if you wanted to edit the constant name inserted into the cache files edit the "prepStrForOut()" method.


getCacheDocBlock() -line # 218
prepStrForOut($outString) - line 257

Line numbers are no good if you edit the file first.

Public methods you can call in your code are
  • getArrayCache($uniquieId)
  • getCache($fileName)
  • cacheOutArray($array, $uniquieId)


I hope you finde my code decently commented and useful. But I offer no guarantees, use at your own risk.

#45763 - 05/31/08 10:40 PM Re: PHP5 OOP - Caching out SQL query results securly [Re: §intå×]  
Joined: Dec 2002
Posts: 3,255
§intå× Offline
§intå×  Offline


Joined: Dec 2002
Posts: 3,255
Likes: 3
Maryland
I do not see where I mentioned this above.

PHP Code


$cache
->cacheOutArray($rows, 'quer1'.$id);

$rows = $cache->getArrayCache('quer1'.$id);


In the first method call "$cache->cacheOutArray($rows, 'quer1'.$id)" '$rows' is the array I am caching and 'quer1'.$id is the name of the query and the value of the parameter I will use in the query. "'quer1'.$id" in my class will be processed into either an md5 or sha1. This hash is then used for a file name of the cache file. This helps ensure the file names will be unique. It will also allow for multiple parameter values to be used so that any query may be cached out.

You can have multiple cache object in your code. Maybe you want to have a cache lasting 15 minutes, a cache lasting 1 hrs, and one lasting a day.

PHP Code


//Cache file created using this object will last only 15 minutes
$cache15min = new cache( 2, 900, true, 'php', '/path/to/cache/directory' );

//Cache file created using this object will last only 1 hrs
$cache1hrs = new cache( 2, 3600, true, 'php', '/path/to/cache/directory' );

//Cache file created using this object will last only 24 hrs
$cache1Day = new cache( 2, 86400, true, 'php', '/path/to/cache/directory' );


Then just used the methods I defined with the cache object you want to use.

PHP Code


$cache15min
->cacheOutArray($membersOnline, 'memOnline'.$param.$param);

$cache1hrs->cacheOutArray($newsFeed, 'newsRssData'.$param.$param2);

$cache1day->cacheOutArray($staffArray, 'getStaffNames'.$param.$param2.$param3);

Last edited by §intå×; 05/31/08 10:48 PM.

Member Spotlight
Gremelin
Gremelin
Portland, OR; USA
Posts: 7,198
Joined: February 2002
Show All Member Profiles 
Forum Statistics
Forums45
Topics34,014
Posts69,156
Members2,164
Average Daily Posts3
Members2,164
Most Online1,567
Apr 25th, 2010
Latest Postings
Blackbeard.....
by Gremelin on 01/14/17 07:03 PM
Top Posters(All Time)
UGN Security 41,248
Gremelin 7,198
§intå× 3,255
SilentRage 1,273
Ice 1,146
pergesu 1,136
Infinite 1,041
jonconley 955
Girlie 908
unreal 860
Top Liked Users (All Time)
§intå× Likes: 3
Black Beard Likes: 1
Cold Sunn Likes: 1
Crime Likes: 1
Cyrez Likes: 1
fleshwound Likes: 1
Ghost Likes: 2
Gremelin Likes: 12
Ice Likes: 1
ninjaneo Likes: 1
Top Liked Users (30 Days)
No Data Found
Powered by UBB.threads™ PHP Forum Software 7.6.0
(Snapshot build 20170206)