<?php

// SQL query caching system

    //////////////////////////////////////////////////////////////////////////
   // WARNING: This is code is still alpha code because I need someone who //
  // will test this for me. If you thing you are qualified for this       //
 // please contact me on my blog: http://blog.mxchange.org               //
//////////////////////////////////////////////////////////////////////////

class wpdb_cache extends wpdb {
	//***** Local variables *****
	// The relative path for all cache files with trailing slash
	var $cache_dir = "wp-content/cache/sql/";

	// Number of rows while the query
	var $query_nums = 0;

	// All cached tables
	var $hashList = array();

	// Cached data
	var $cachedData = array();

	// Current extracted tables from query string
	var $currTables = array();

	// Tables which needs flushing to disc
	var $waitingTables = array();

	// Number of rows returned from parent query() function
	var $currNums = array();

	// Counter for all cache hits
	var $cache_hits = 0;

	// Shall we compress and encode the cache files? (may slow-down your blog)
	var $compression = true; // false - plain cache files, bigger files!

	// Used for debugging
	var $currQuery = null;

	// Shall we use ZLIB? (on/off/auto=default)
	// (set this only to on if you have PHP configured with zlib support!)
	var $useZlib = 'auto';
	// Important note: If you change this e.g. from 'on' to 'off' then please
	// clear your sql-cache directory or else you will see lots of errors

	// Here is the usage of all directories
	//   arrays  - For caching simple arrays (indexed and asosiative; incl.
	//             multi-dimension arrays)
	//   objects - For caching whole objects (e.g. from mysql_fetch_object())
	//   single  - For caching single data (one column or result of COUNT/SUM())

	// All cacheable statements
	var $cacheableStatements = array("SELECT", "SHOW", "DESC");

	// Allocation table for serialized data
	var $allocTable = array();

	// Cache initialization part
	function wpdb_cache ($dbuser, $dbpassword, $dbname, $dbhost, $compress) {
		// Set the compression
		$this->setCompression($compress);

		// Make the cache dir absolute
		$this->cache_dir = ABSPATH . $this->cache_dir;

		// Check if the main directory does exist
		if (!is_dir($this->cache_dir)) {
			// Try to create it
			@mkdir($this->cache_dir);
			@chmod($this->cache_dir, 0777);

			// Re-check it
			if (!is_dir($this->cache_dir)) {
				// Please create it on your own
				$this->cache_die("Cannot create SQL cache directory (default: wp-content/cache/sql/)! Please create and try to CHMOD it to 0755 or 0775.");
			} else {
				// Create the missing .htaccess file
				$this->create_htaccess();
			}
		} elseif (!file_exists($this->cache_dir . ".htaccess")) {
			// Create missing .htaccess file
			$this->create_htaccess();
		}

		// Detect ZLIB support?
		$this->detectZlibSupport();

		// Load a (maybe) existing hash list
		$this->loadHashList();

		// Initialize the database layer
		parent::wpdb($dbuser, $dbpassword, $dbname, $dbhost);
		// When you remove the line above the database will no longer be accessible
		// and the sky turns pitch dark! ;-)
	}

	// private: create .htaccess files for securing the directories against direct calls
	function create_htaccess () {
		$fp = @fopen($this->cache_dir . ".htaccess", 'w') or $this->cache_die(sprintf("SQL-CACHE: Cannot create .htaccess file in %s.", $this->cache_dir));
		fwrite($fp, "Deny from all\n");
		fclose($fp);
	}

	// Get result
	function get_results ($query = NULL, $output = OBJECT, $dontCache = false) {
		if ($query) {
			if ($dontCache) {
				return parent::get_results($query, $output);
			} else {
				$this->query($query);
			}
		}
		
		if ($output == OBJECT) {
			// Return object
			return $this->cachedData[$this->currQueryHash];
		} elseif ($output == ARRAY_A || $output == ARRAY_N) {
			// Non-object
			if (!empty($this->cachedData[$this->currQueryHash])) {
				$i = 0;
				foreach($this->cachedData[$this->currQueryHash] as $row) {
					$new_array[$i] = (array) $row;
					if ($output == ARRAY_N) {
						$new_array[$i] = array_values($new_array[$i]);
					}
					$i++;
				}
				return $new_array;
			} else {
				// Empty object
				return null;
			}
		}
	}

	// get_row
	function get_row ($query = null, $output = OBJECT, $y = 0) {
		if ($query != null) {
			$this->query($query);
		}

		if (!isset($this->cachedData[$this->currQueryHash][$y])) {
			return null;
		}

		if ($output == OBJECT) {
			return $this->cachedData[$this->currQueryHash][$y] ? $this->cachedData[$this->currQueryHash][$y] : null;
		} elseif ($output == ARRAY_A) {
			return $this->cachedData[$this->currQueryHash][$y] ? get_object_vars($this->cachedData[$this->currQueryHash][$y]) : null;
		} elseif ($output == ARRAY_N) {
			return $this->cachedData[$this->currQueryHash][$y] ? array_values(get_object_vars($this->cachedData[$this->currQueryHash][$y])) : null;
		} else {
			define('DEBUG_SQL_CACHE', true);
			define('DONT_FLUSH_SQL', true);
			$this->print_error(" \$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
		}
	}

	// get_var
	function get_var ($query=null, $x = 0, $y = 0) {
		if ($query)
			$this->query($query);

		// Extract var out of cached results based x,y vals
		if (!empty($this->cachedData[$this->currQueryHash][$y])) {
			$values = array_values(get_object_vars($this->cachedData[$this->currQueryHash][$y]));
		}

		// If there is a value return it else return null
		return (isset($values[$x]) && $values[$x]!=='') ? $values[$x] : null;
	}

	// Get column
	function get_col ($query = null , $x = 0) {
		if ($query) {
			//* DEBUG: */ echo "QUERY=".$query."<br/>";
			$this->query($query);
			//* DEBUG: */ echo $this->currQueryHash." (".count($this->cachedData[$this->currQueryHash]).")<br/>";
		}

		// Extract the column values
		for ($i=0; $i < count($this->cachedData[$this->currQueryHash]); $i++) {
			$new_array[$i] = $this->get_var(null, $x, $i);
		}
		return $new_array;
	}

	// Query the database or cache file
	// returns number of entries
	function query ($query, $dontCache = false) {
		global $table_prefix;

		// Don't cache this query? (useful for SELECT queries with timemarks)
		if ($dontCache) return parent::query($query);

		// Initialize counter
		$this->query_nums = 0;

		// For debugging I used this
		$this->currQuery = $query;

		// Convert query string to upper-case
		$test = trim(strtoupper($query)); $flushCache = false;

		// Create MD5 checksum of current query + WP_SECRET (we will later use this)
		$this->currQueryHash = md5(WP_SECRET.":".$test);

		// Extract first element
		$needle = explode(" ", $test);
		$needle = $needle[0];

		// Do we have a cacheable command where we can take the cache?
		if (in_array($needle, $this->cacheableStatements)) {
			// Extract all tables from the string. The ?: is for "non-capturing subpatterns"
			// Thanx to Hinrich Sager <hinrich [DOT] sager [AT] web [DOT] de> from the PHPUG-Hamburg
			// to help me here. :-)
			preg_match_all(sprintf('/(?:FROM|JOIN|DESC|,)[\s](%s[\d\w]+)/i', $table_prefix), $query, $this->currTables);

			// Take the 2nd array, so now we have all tables together :-)
			$this->currTables = $this->currTables[1];

			// We need $this->hashList for deleting cache files when UPDATE, INSERT et cetera is queried
			if (count($this->currTables) == 0) {
				// Always read from database if no table was found... :-)
				$flushCache = true;
			} else {
				// Search for the current tables in hash list
				foreach ($this->currTables as $table) {
					if (!isset($this->hashList[$table])) {
						// The table was not found in the hash list so we add it + the query
						$this->hashList[$table] = array($this->currQueryHash);

						// Not yet cached!
						$flushCache = true;
					} elseif (!in_array($this->currQueryHash, $this->hashList[$table])) {
						// The table was been added to the hash list before but not this query string
						$this->hashList[$table][] = $this->currQueryHash;

						// Not yet cached!
						$flushCache = true;
					} else {
						// Count the cache hit
						$this->cache_hits++;
					}
				}
			}

			// Do we need to create the cache file?
			if ($flushCache) {
				// Write logfile
				/*
				$fp = @fopen(ABSPATH."wp-content/cache/db-cache.log", 'a') or $this->cache_die("Cannot write logfile!");
				fwrite($fp, $query."\n");
				fclose($fp);
				*/

				// So let's read data from database and put it in memory
				$this->currNums[$this->currQueryHash] = parent::query($query);
				if ($this->currNums[$this->currQueryHash] > 0) {
					// Read all data from the last_result array in wpdb
					for ($idx = 0; $idx < $this->currNums[$this->currQueryHash]; $idx++) {
						$this->cachedData[$this->currQueryHash][$idx] = parent::getLastResultElement($idx);
					}

					// Remeber this table for flushing
					$this->waitingTables[] = $this->currQueryHash;
				}
			} else {
				// Read from cache
				$content = $this->readCacheFile($this->currQueryHash);
				// Decompress it
				if (!empty($content)) {
					$content = $this->decode($content);
					// Split it up
					if (strstr($content, "---") !== false) {
						$array = explode("---", $content);
						$this->currNums[$this->currQueryHash] = $array[0];
						$this->cachedData[$this->currQueryHash] = unserialize($array[1]);

						// Transfer data to wpdb
						//* DEBUG: */ echo "CACHED=".$this->currQueryHash."<br/>";
						//* DEBUG: */ echo "<pre>".$array[1]."</pre>";
						parent::setLastResultArray($this->cachedData[$this->currQueryHash]);
					} else {
						// Cache is invalid!
						$this->cache_die(sprintf("SQL-CACHER BUG: Cache with query %s is invalid!", $query));
						//$this->waitingTables[] = $this->currQueryHash;
					}
				}

				// Number of rows
				$this->query_nums = $this->currNums[$this->currQueryHash];
			}
		} else {
			// Check for tables
			preg_match_all(sprintf('/[\s](%s[\d\w]+)/i', $table_prefix), $query, $this->currTables);

			// Take the 2nd array, so now we have all tables together :-)
			$this->currTables = $this->currTables[1];

			// Check for existing cahes by analyzing the query string
			$this->removeAssignedCacheFiles();

			// Run the SQL command (don't comment this out, or the roof will fall on your head! ;-))
			// Nope, this would be the ultimate spam-killer when there will be the problem that *no*
			// comment/post/link/whatever get inserted... Sorry, dude but spam-fighting is not so simple...
			$this->query_nums = parent::query($query);
		}

		// Return number of rows
		return $this->query_nums;
	}

	// Remove all assigned tables
	function removeAssignedCacheFiles () {
		foreach ($this->currTables as $table) {
			if (is_array($this->hashList[$table])) {
				foreach ($this->hashList[$table] as $hash) {
					// Remove from many arrays
					unset($this->currNums[$hash]);
					unset($this->cachedData[$hash]);

					// Delete cache file itself
					$this->removeCacheFile($table);
				}
			}

			// Remove the final hashList entry
			unset($this->hashList[$table]);
		}

		// Remove $this->currTables as well
		$this->currTables = array();
	}

	// Remove the cache file from filesystem
	function removeCacheFile ($name) {
		$filename = sprintf("%s%s.cache", $this->cache_dir, $name);
		if (file_exists($filename) && is_writeable($filename)) {
			// Remove it
			@unlink($filename);

			// Has it been removed?
			if (file_exists($filename)) {
				// No! Die here
				$this->cache_die(sprintf("SQL-CACHER BUG: Cannot remove cache file %s from filesystem!", $name));
			}
		}
	}

	// Shuts the caching system down
	function close () {
		// Shall we output debugging info? (useful for development)
		if (defined('DEBUG_SQL_CACHE')) {
			// Output cache debug informations
			$this->cache_debug_output();
		}

		// Flush all newly read tables
		$this->flushWaitingTables();

		// Flush the hashlist to disc
		$this->flushHashList();

		// Close the database connection
		parent::close();
	}

	// Flush all waiting tables to disc
	function flushWaitingTables () {
		if (count($this->waitingTables) > 0) {
			// Begin with flush procecure
			foreach ($this->waitingTables as $idx=>$table) {
				// Serialze the cached data
				$data = serialize($this->cachedData[$table]);

				if ($data != "N;") {
					// Serialize all output (--- is the default seperator)
					$content = $this->compileData($table, $data);

					// Compress/encode it
					$content = $this->encode($content);

					// Now we hash the serialized object/array for looking it up in the allocation table
					$hash = md5(WP_SECRET.":".$content);

					if ($this->lookupDataInAllocTable($hash, $table) == "new") {
						// Write it to disc
						$this->writeCacheFile($table, $content);
					} else {
						// Do not flush the same data twice...
						$this->destroyCachedData($idx, $table);
					}

					// Add it to the allocation table
					$this->addDataAllocTable($hash, $table);
				} else {
					// Remove handler from queue
					$this->destroyCachedData($idx, $table);
				}
			}
		}
	}

	// The central method which binds the SQL hash with the serialized data
	function compileData ($table, $data) {
		return $this->currNums[$table] . "---" . $data;
	}

	// Adds an allocation between the table and the hashed serialization output
	function addDataAllocTable ($hash, $table) {
		// Do we have created the table?
		if ($this->lookupDataInAllocTable($hash, $table) == "hash") {
			// Is the table already allocated with a hash?
			if (!in_array($table, $this->allocTable[$hash])) {
				$this->allocTable[$hash][] = $table;
			}
		} else {
			// Create new entry
			$this->allocTable[$hash] = array($table);
		}
	}

	// Search for the hashed data in the allocation table
	function lookupDataInAllocTable ($hash, $table) {
		// Simple, he? ;-)
		if (isset($this->allocTable[$hash])) {
			if (in_array($table, $this->allocTable[$hash])) {
				// The table is already hashed
				return "already";
			} else {
				// Only the hash was found
				return "hash";
			}
		}

		// Completly new hash and table
		return "new";
	}

	// Destroys cached data
	function destroyCachedData ($idx, $table) {
		unset($this->waitingTables[$idx]);
		unset($this->currNums[$table]);
		unset($this->cachedData[$table]);
	}

	// Encodes and compresses the hashlist
	function flushHashList () {
		// Serialize and compress/encode it
		$output = $this->encode(serialize($this->hashList));

		// Write it to disc
		$this->writeCachefile("hashlist", $output);
	}

	// Loads a cached hashList from disc
	function loadHashList () {
		$content = $this->readCacheFile("hashlist");
		if (!empty($content)) {
			$content = unserialize($this->decode($content));
			$this->hashList = $content;
		}
	}

	// Write a cache file to disc, you shall better
	// serialize $content before sending it to this method
	// (private)
	function writeCacheFile ($name, $content) {
		// Shall not be defined in productive area
		if (defined('DONT_FLUSH_SQL')) return;

		$filename = sprintf("%s%s.cache", $this->cache_dir, $name);
		if ($this->useZlib == 'on') {
			// Use the gzopen, bla functions
			// You may need to experiment with the compression level (the number behind "w")
			// to find the best value for your PHP/OS combination.
			$fp = @gzopen($filename, 'w4') or $this->cache_die(sprintf("Cannot write %s.cache!", $name));
			@gzputs($fp, $content);
			@gzclose($fp);
		} else {
			// No ZLIB compression
			if (function_exists('file_put_contents')) {
				// One function
				@file_put_contents($filename, $content) or $this->cache_die(sprintf("Cannot write %s.cache!", $name));
			} else {
				// Fall-back method
				$fp = @fopen($filename, 'w') or $this->cache_die(sprintf("Cannot write %s.cache!", $name));
				@fwrite($fp, sprintf("%s\n", $content));
				@fclose($fp);
			}
		}
			
	}

	// Read a cache file from disc
	// (private)
	function readCacheFile ($name) {
		$content = "";
		$filename = sprintf("%s%s.cache", $this->cache_dir, $name);
		if (file_exists($filename) && is_readable($filename)) {
			// Use gzopen?
			if ($this->useZlib == 'on') {
				// Read compressed file
				$fp = @gzopen($filename, 'r') or $this->cache_die(sprintf("Cannot read %s.cache!", $name));
				while (!gzeof($fp)) {
					$content .= gzgets($fp, 4096);
				}
				gzclose($fp);
			} else {
				// Use maybe uncompresed files
				$content = implode("", file($filename));
			}
		}
		return $content;
	}

	// Encode method
	function encode ($str) {
		if ($this->compress && $this->useZlib == 'off') {
			return base64_encode(gzcompress($str));
		} else {
			return $str;
		}
	}

	// Decode method
	function decode ($str) {
		if ($this->compress && $this->useZlib == 'off') {
			return gzuncompress(base64_decode($str));
		} else {
			return $str;
		}
	}

	// Setter to turn compression on/off
	function setCompression ($status) {
		$this->compress = $status;
	}

	// Debug informations of the cache
	function cache_debug_output () {
		echo "Cache hits: <strong>".$this->cache_hits."</strong><br />
Database queries: <strong>".parent::getNumQueries()."</strong><br />
<br />
Cached queries:
<pre>".print_r($this->hashList, true)."</pre>
Cached table data
<pre>".print_r($this->cachedData, true)."</pre>
Cached number of rows:
<pre>".print_r($this->currNums, true)."</pre>";
	}

	// Wrapper for wp_die()
	function cache_die ($msg) {
		printf("<h4>%s</h4>\n", $msg);
	}

	// Detects ZLIB support
	function detectZlibSupport () {
		// Do only detect when in auto-detection mode
		if ($this->useZlib == 'auto') {
			// Automatic detection can slowdown your blog
			// So when you know that your PHP has ZLIB enabled
			// change the setting in header to 'on'
			if (function_exists('gzopen')) {
				$this->useZlib = 'on';
				$this->compression = false;
			} else {
				$this->useZlib = 'off';
			}
		}
	}
}
//
?>