Skip to content

Improved Query Caching

Jump to bottom Edit New page
World Wide Web Server edited this page Jul 4, 2012 · 30 revisions

[size=6][b][color=red][Edit: This article is still work in progress! No actual code publicly available yet, sorry.][/color][/b][/size]

The way CodeIgniter does Query Caching (namely Controller-based caching) [b]works fine with small and decentralized pages[/b] where all controllers are pretty much independent. But as soon as you have a [b]model that's shared[/b] by a handful of controllers, you [b]end up with a big mess[/b].

Just take a model for generating the data for a tag cloud that's displayed on every page. You would end up with dozens of duplicates and handling those caches would suck as hell.

[url=http://codeigniter.com/forums/viewthread/78146/]See this topic for discussion[/url]

I got pretty sick of this and thus I of re-wrote pretty much CI's entire caching storage mechanisms. Using my code CI now supports [b]several[/b] different ways to cache database queries.

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

[size=4][color=red][b]Note: [/b][/color][/size]

  1. Functions ending with [b]"cache_single(…)"[/b] have parameters and run [b]$this->db->cache_off()[/b] automatically once a query has been sent. [i](This is due to some technical limitations and might get fixed in future.)[/i]

  2. Functions ending with [b]"cache_on(…)"[/b] have no parameters (they gather all necessary data themselves). They need to be turned of manually by running [b]$this->db->cache_off()[/b]. [i](They work just the way like CI's [b]"$this->db->cache_on()"[/b] used to)[/i]

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

[size=6]1. [b]Controller[/b][/size]

[color=orange][b]Purpose:[/b][/color] For controllers-centric and fairly [b]simple projects[/b] with mostly independent controllers. [color=green][i]This is just what you know from CI 1.6.x.[/i][/color]

[color=orange][b]Where to use it:[/b][/color] In [b]Controllers[/b].

[color=orange][b]File Storage:[/b][/color] [color=blue]application / cache_database / [b]controller/ + controller + function + md5hash[/b][/color] [i](Folder is created automatically)[/i]

[color=orange][b]Sample Code:[/b][/color]

[size=4][b]Activating Controller Caches:[/b][/size] [code]<?php

//Create cache for current controller function: $this->db->cache_on(); //alias to controller_cache_on() //or better: $this->db->controller_cache_on();

?>[/code]

[size=4][b]Clearing Controller Caches:[/b][/size] [code]<?php

//Delete cache of current controller function: $this->db->cache_delete(); //alias to delete_controller_cache() $this->db->controller_cache_delete();

//Delete cache of custom defined controller $this->db->controller_cache_delete($model_name);

//Delete cache of custom defined controller function: $this->db->controller_cache_delete($model_name, $function_name);

?>[/code]

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

[size=6]2. [b]Model[/b][/size]

[color=orange][b]Purpose:[/b][/color] For model-centric and [b]semi-complex projects[/b] with multiple controllers sharing the same model.

[color=orange][b]Where to use it:[/b][/color] In [b]Models[/b].

[color=orange][b]File Storage:[/b][/color] [color=blue]application / cache_database / [b] model/ + model + function + md5hash[/b][/color] [i](Folder is created automatically)[/i]

[color=orange][b]Sample Code:[/b][/color]

[size=4][b]Activating Model Caches:[/b][/size] [code]<?php

//Create cache for current model: $this->db->model_cache_single(CLASS);

//Create cache for current model function: $this->db->model_cache_single(CLASS,FUNCTION);

//Create cache for custom model: $this->db->model_cache_single($model_name);

//Create cache for custom defined model function: $this->db->model_cache_single($model_name, $function_name);

?>[/code]

[size=4][b]Clearing Model Caches:[/b][/size] [code]<?php

//Delete cache of current model $this->db->model_cache_delete(CLASS);

//Delete cache of current model function: $this->db->model_cache_delete(CLASS,FUNCTION);

//Delete cache of custom defined model: $this->db->model_cache_delete($model_name);

//Delete cache of custom defined model function: $this->db->model_cache_delete($model_name, $function_name);

?>[/code]

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

[size=6]3. [b]Database Table[/b][/size]

[color=orange][b]Purpose:[/b][/color] For all of us who want the [b]best possible performance[/b] and [b]most advanced caching management[/b].

[color=orange][b]Where to use it:[/b][/color] Wherever you need to.

[color=orange][b]File Storage:[/b][/color] [color=blue]application / cache_database / [b]table/ + table1 + table2 + table3 + md5hash[/b][/color] [i](Folder is created automatically)[/i]

[color=red][b]Important:[/b][/color] [b]Active Record Queries support automatic table detection[/b] while [b]custom queries require tablenames to be provided manually[/b]. (This might get improved, though)

[color=orange][b]Sample Code:[/b][/color]

[size=4][b]Activating Table Caches:[/b][/size] [code]<?php

//Create cache for queries handling with one single table: $this->db->table_cache_single($table_name);

//Create cache for queries handling with multiple tables: $this->db->table_cache_single(array($table_name_1, $table_name_2));

?>[/code]

[size=4][b]Clearing Table Caches:[/b][/size] [code]<?php

//Delete all caches containing data from a particular table: $this->db->table_cache_delete($table_name);

?>[/code]

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

[size=6]4. [b]Key-Value[/b][/size]

[color=orange][b]Purpose:[/b][/color] For a [b]lightweight[/b] but [b]useful[/b] caching option.

[color=orange][b]Where to use it:[/b][/color] Wherever you need to.

[color=orange][b]File Storage:[/b][/color] [color=blue]application / cache_database / [b]key / + key + md5hash[/b][/color] [i](Folder is created automatically)[/i]

[color=orange][b]Sample Code:[/b][/color]

[size=4][b]Activating Key Caches:[/b][/size] [code]<?php

//Create cache for a particular key: $this->db->key_cache_single($key_name);

?>[/code]

[size=4][b]Clearing Key Caches:[/b][/size] [code]<?php

//Delete all caches associated with a particular key: $this->db->key_cache_delete($key_name);

?>[/code]

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

[size=4][b]Turn Caching off:[/b][/size] [code]<?php

$this->db->cache_off();

?>[/code]

[size=4][b]Delete cache files of a all caching mode:[/b][/size] [code]<?php

$this->db->cache_delete_all();

?>[/code]

[size=4][b]Delete cache files of a particular caching mode:[/b][/size] [code]<?php

$this->db->cache_delete_all($mode); //$mode can either be "controller", "model", "table" or "key". ?>[/code]

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

[size=4][b]These are the functions I either added or modified in DB_driver.php:[/b][/size]

[code]function CI_DB_driver($params) function controller_cache_on() function controller_cache_single($controller_name = NULL, $controller_function = NULL) function model_cache_single($model_name = NULL, $model_function = NULL) function table_cache_single($table_names = NULL) function ar_table_cache_on($table_names = array()) function key_cache_on($key_name = NULL) function controller_cache_delete($controller_name = '', $controller_function = '') function model_cache_delete($model_name = '', $model_function = '') function table_cache_delete($table_name = '') function key_cache_delete($key_name = NULL) function cache_on() //Deprecated function cache_off() function cache_delete($segment_one = '', $segment_two = '') //Deprecated function cache_delete_all()[/code]

[size=4][b]And these are the functions I either added or modified in DB_cache.php:[/b][/size]

[code]function read($sql) function write($sql, $object) function _get_tables_from_ar() function delete_cache_files($mode = NULL, $criteria = NULL, $cache_dir = NULL, &$cache_files = array()) function delete($segment_one = '', $segment_two = '') function delete_all($mode = '')[/code]

[b]# # # # # # # # # # # # # # # # # # # # # # # # # # # # # #[/b]

Now for the actual code:

[size=4][b]DB_cache.php:[/b][/size] [code]<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /**

// ------------------------------------------------------------------------

/**

  • Database Cache Class

  • @category Database

  • @author ExpressionEngine Dev Team

  • @link http://codeigniter.com/user_guide/database/ */ class CI_DB_Cache {

    var $CI;

    /**

    • Constructor
    • Grabs the CI super object instance so we can access it.

    */
    function CI_DB_Cache() { // Assign the main CI object to $this->CI // and load the file helper since we use it a lot $this->CI =& get_instance(); $this->CI->load->helper('file');
    }

    // --------------------------------------------------------------------

    /**

    • Set Cache Directory Path

    • @access public

      • @param string the path to the cache directory

    • @return bool */
      function check_path($path = '') { if ($path == '') { if ($this->CI->db->cachedir == '') { return $this->CI->db->cache_off(); }

       $path = $this->CI->db->cachedir;

      }

      // Add a trailing slash to the path if needed $path = preg_replace("/(.+?)/*$/", "\1/", $path);

      if ( ! is_dir($path) OR ! is_really_writable($path)) { // If the path is wrong we'll turn off caching return $this->CI->db->cache_off(); }

      $this->CI->db->cachedir = $path; return TRUE; }

    // --------------------------------------------------------------------

    /**

    • Retrieve a cached query

    • The URI being requested will become the name of the cache sub-folder.

    • An MD5 hash of the SQL statement will become the cache file name

    • @access public

    • @return string */ function read($sql) { $cache_mode = $this->CI->db->cache_mode; $cache_id = $this->CI->db->cache_ids->$cache_mode;

      if ($cache_mode == 'ar_table') { $this->_get_tables_from_ar(); }

      $cache_mode = ($cache_mode == 'ar_table') ? 'table' : $cache_mode;

      $dir_path = $this->CI->db->cachedir.'/'.$cache_mode.'/'; $file_name = $cache_id.md5($sql);

      if ( ! @is_dir($dir_path)) { return FALSE; }

      if (FALSE === ($cachedata = read_file($dir_path.$file_name))) {
      return FALSE; }

      return unserialize($cachedata);
      }

    // --------------------------------------------------------------------

    /**

    • Write a query to a cache file

    • @access public

    • @return bool */ function write($sql, $object) { $cache_mode = $this->CI->db->cache_mode; $cache_id = $this->CI->db->cache_ids->$cache_mode;

      if ($cache_mode == 'ar_table') { $this->_get_tables_from_ar(); }

      $cache_mode = ($cache_mode == 'ar_table') ? 'table' : $cache_mode;

      $dir_path = $this->CI->db->cachedir.'/'.$cache_mode.'/'; $file_name = $cache_id.md5($sql);

      if ( ! @is_dir($dir_path)) { if ( ! @mkdir($dir_path, 0777)) { return FALSE; }

       @chmod($dir_path, 0777);

      }

      if (write_file($dir_path.$file_name, serialize($object)) === FALSE) { return FALSE; }

      @chmod($dir_path.$file_name, 0777);

      return TRUE; }

    // --------------------------------------------------------------------

    /**

    • Detect Tables from Active Record

    • @access public

    • @return bool */ function _get_tables_from_ar() { if ($this->CI->db->cache_mode == 'table' && !empty($this->CI->db->ar_from)) { $ar_tables = array();

       foreach ($this->CI->db->ar_from as $table)
 {
     $ar_tables[] = substr($table,1,strlen($table)-2);
 }
 $this->CI->db->cache_ids->table = '+'.implode('+',$ar_tables).'+';

      } return TRUE; }

    // --------------------------------------------------------------------

    /**

    • Find cache files matching the criteria within a particular directory

    • @access public

    • @param array the names that were used for caching (required)

    • @param string the directory to be scanned (required)

    • @param array array of already found cache files (optional)

    • @return bool */ function delete_cache_files($mode = NULL, $criteria = NULL, $cache_dir = NULL, &$cache_files = array()) {

      if ( ! @is_dir($cache_dir)) { return FALSE; } if ($handle = opendir($cache_dir)) { while (false !== ($file = readdir($handle))) { //Ignore items starting with '.' if (strncasecmp($file,'.',1)) { //If it's a file and matches the criteria, then add it to the array if (is_file($cache_dir.$file)) {
      //Tables do not need to be found at offset 0 if ($mode == 'table') { if (strpos($file,$criteria) !== FALSE) { $cache_files[] = $cache_dir.$file; } } else //Everything else does need to be found at offset 0 { if (strpos($file,$criteria) === 0) { $cache_files[] = $cache_dir.$file; } } } else if (is_dir($cache_dir.$file)) { //Scan subdir for cache files $this->delete_cache_files($mode, $criteria, $cache_dir.$file.'/', $cache_files); } } } closedir($handle); }

      foreach ($cache_files as $cache_file) { if (FALSE === unlink($cache_file)) { return FALSE; } }

      return $cache_files; }

    // --------------------------------------------------------------------

    /**

    • Delete cache files within a particular directory
    • @access public
    • @return bool */ function delete($segment_one = '', $segment_two = '') {
      $this->CI->db->delete_controller_cache($segment_one = '', $segment_two = ''); }

    // --------------------------------------------------------------------

    /**

    • Delete all existing cache files
    • @access public
    • @return bool */ function delete_all($mode = '') { $mode = (empty($mode)) ? '' : $mode.'/'; delete_files($this->CI->db->cachedir.$mode, TRUE); }

}

?>[/code]

[size=4][b]DB_driver.php:[/b][/size] [code][/code]

Add a custom footer
Clone this wiki locally