Improved Query Caching

[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=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=6][b]How to actually use this stuff:[/b][/size]

Well, say you want to cache this query:[code]$query = $this->db->get('table_name');[/code]

You could now either cache it connected to a controller function (URI) (if the query was run from a controller, which you shouldn't have in first place), like:[code]$this->db->controller_cache_on(); $query = $this->db->get('table_name'); $this->db->cache_off();[/code]

Or connected to a model function (if the query was run from a model), like:[code]$this->db->model_cache_single(CLASS , FUNCTION); $query = $this->db->get('table_name');[/code]

Or connected to a table (this one uses Active Record), like:[code]$this->db->ar_table_cache_on(); $query = $this->db->get('table_name'); $this->db->cache_off();[/code]

Or connected to a table (this time with a custom query), like:[code]$this->db->table_cache_single('my_table'); $query = $this->db->query('SELECT name FROM my_table LIMIT 1');[/code]

And last connected to a key (just like storing it in an associative array), like:[code]$this->db->key_cache_single('my_key'); $query = $this->db->query('SELECT name FROM my_table LIMIT 1');[/code]

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

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]
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=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_single($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'); /**

CodeIgniter
An open source application development framework for PHP 4.3.2 or newer
@package CodeIgniter
@author ExpressionEngine Dev Team
@license http://codeigniter.com/user_guide/license.html
@link http://codeigniter.com
@since Version 1.0
@filesource */

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

/**

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]<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); /**

CodeIgniter
An open source application development framework for PHP 4.3.2 or newer
@package CodeIgniter
@author ExpressionEngine Dev Team
@license http://codeigniter.com/user_guide/license.html
@link http://codeigniter.com
@since Version 1.0
@filesource */

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

/**

Database Driver Class
This is the platform-independent base DB implementation class.
This class will not be called directly. Rather, the adapter
class for the specific database will extend and instantiate it.
@package CodeIgniter
@subpackage Drivers
@category Database
@author ExpressionEngine Dev Team
@link http://codeigniter.com/user_guide/database/ */ class CI_DB_driver {

var $username; var $password; var $hostname; var $database; var $dbdriver = 'mysql'; var $dbprefix = ''; var $autoinit = TRUE; // Whether to automatically initialize the DB var $swap_pre = ''; var $port = ''; var $pconnect = FALSE; var $conn_id = FALSE; var $result_id = FALSE; var $db_debug = FALSE; var $benchmark = 0; var $query_count = 0; var $bind_marker = '?'; var $save_queries = TRUE; var $queries = array(); var $query_times = array(); var $data_cache = array(); var $trans_enabled = TRUE; var $_trans_depth = 0; var $_trans_status = TRUE; // Used with transactions to determine if a rollback should occur var $cache_on = FALSE; var $cache_mode = NULL; var $cache_single = NULL; var $cache_ids = array(); var $cachedir = ''; var $cache_autodel = FALSE; var $CACHE; // The cache class object

var $CI;

// These are use with Oracle var $stmt_id; var $curs_id; var $limit_used;

/**
- Constructor. Accepts one parameter containing the database
- connection settings.
- @param array */ function CI_DB_driver($params) { if (is_array($params)) { foreach ($params as $key => $val) { $this->$key = $val; } }
  
  $this->CI =& get_instance();
  
  /*$this->cache_ids = (object) array( 'controller' => (object) array('controller' => NULL, 'function' => NULL), 'model' => (object) array('model' => NULL, 'function' => NULL), 'table' => (object) array('tables' => NULL), 'key' => (object) array('key' => NULL), ); */ $this->cache_ids = (object) array( 'controller' => NULL, 'model' => NULL, 'table' => NULL, 'ar_table' => NULL, 'key' => NULL);
  
  log_message('debug', 'Database Driver Class Initialized'); }
// --------------------------------------------------------------------

/**
- Initialize Database Settings
- @access private Called by the constructor
- @param mixed
- @return void */ function initialize($create_db = FALSE) { // If an existing DB connection resource is supplied // there is no need to connect and select the database if (is_resource($this->conn_id)) { return TRUE; }
  
  // Connect to the database $this->conn_id = ($this->pconnect == FALSE) ? $this->db_connect() : $this->db_pconnect();
  
  // No connection? Throw an error if ( ! $this->conn_id) { log_message('error', 'Unable to connect to the database');
```
 if ($this->db_debug)
 {
     $this->display_error('db_unable_to_connect');
 }
 return FALSE;
```
  }
  
  // Select the database if ($this->database != '') { if ( ! $this->db_select()) { // Should we attempt to create the database? if ($create_db == TRUE) { // Load the DB utility class $CI =& get_instance(); $CI->load->dbutil();
```
         // Create the DB
         if ( ! $CI->dbutil->create_database($this->database))
         {
             log_message('error', 'Unable to create database: '.$this->database);
 
             if ($this->db_debug)
             {
                 $this->display_error('db_unable_to_create', $this->database);
             }
             return FALSE;        
         }
         else
         {
             // In the event the DB was created we need to select it
             if ($this->db_select())
             {
                 if (! $this->db_set_charset($this->char_set, $this->dbcollat))
                 {
                     log_message('error', 'Unable to set database connection charset: '.$this->char_set);

                     if ($this->db_debug)
                     {
                         $this->display_error('db_unable_to_set_charset', $this->char_set);
                     }

                     return FALSE;
                 }
         
                 return TRUE;
             }
         }
     }

     log_message('error', 'Unable to select database: '.$this->database);

     if ($this->db_debug)
     {
         $this->display_error('db_unable_to_select', $this->database);
     }
     return FALSE;
 }

 if (! $this->db_set_charset($this->char_set, $this->dbcollat))
 {
     log_message('error', 'Unable to set database connection charset: '.$this->char_set);

     if ($this->db_debug)
     {
         $this->display_error('db_unable_to_set_charset', $this->char_set);
     }

     return FALSE;
 }
```
  }
  
  return TRUE; }
// --------------------------------------------------------------------

/**
- The name of the platform in use (mysql, mssql, etc...)
- @access public
- @return string */ function platform() { return $this->dbdriver; }
// --------------------------------------------------------------------

/**
- Database Version Number. Returns a string containing the
- version of the database being used
- @access public
- @return string */ function version() { if (FALSE === ($sql = $this->_version())) { if ($this->db_debug) { return $this->display_error('db_unsupported_function'); } return FALSE; }
  
  if ($this->dbdriver == 'oci8') { return $sql; }
  
  $query = $this->query($sql); return $query->row('ver'); }
// --------------------------------------------------------------------

/**
- Execute the query
- Accepts an SQL string as input and returns a result object upon
- successful execution of a "read" type query. Returns boolean TRUE
- upon successful execution of a "write" type query. Returns boolean
- FALSE upon failure, and if the $db_debug variable is set to TRUE
- will raise an error.
- @access public
- @param string An SQL query string
- @param array An array of binding data
- @return mixed */ function query($sql, $binds = FALSE, $return_object = TRUE) { if ($sql == '') { if ($this->db_debug) { log_message('error', 'Invalid query: '.$sql); return $this->display_error('db_invalid_query'); } return FALSE; }
  
  // Verify table prefix and replace if necessary if ( ($this->dbprefix != '' AND $this->swap_pre != '') AND ($this->dbprefix != $this->swap_pre) ) {
  $sql = preg_replace("/(\W)".$this->swap_pre."(\S+?)/", "\1".$this->dbprefix."\2", $sql); }
  
  // Is query caching enabled? If the query is a "read type" // we will load the caching class and return the previously // cached query if it exists if ($this->cache_on == TRUE AND stristr($sql, 'SELECT')) { if ($this->_cache_init()) { $this->load_rdriver(); if (FALSE !== ($cache = $this->CACHE->read($sql))) { if ($this->cache_single === TRUE) { $this->cache_off(); } return $cache; } } }
  
  // Compile binds if needed if ($binds !== FALSE) { $sql = $this->compile_binds($sql, $binds); }
  
  // Save the query for debugging if ($this->save_queries == TRUE) { $this->queries[] = $sql; }
  
  // Start the Query Timer $time_start = list($sm, $ss) = explode(' ', microtime());
  
  // Run the Query if (FALSE === ($this->result_id = $this->simple_query($sql))) { // This will trigger a rollback if transactions are being used $this->_trans_status = FALSE;
```
 if ($this->db_debug)
 {
     log_message('error', 'Query error: '.$this->_error_message());
     return $this->display_error(
                             array(
                                     'Error Number: '.$this->_error_number(),
                                     $this->_error_message(),
                                     $sql
                                 )
                             );
 }

 return FALSE;
```
  }
  
  // Stop and aggregate the query time results $time_end = list($em, $es) = explode(' ', microtime()); $this->benchmark += ($em + $es) - ($sm + $ss);
  
  if ($this->save_queries == TRUE) { $this->query_times[] = ($em + $es) - ($sm + $ss); }
  
  // Increment the query counter $this->query_count++;
  
  // Was the query a "write" type? // If so we'll simply return true if ($this->is_write_type($sql) === TRUE) { // If caching is enabled we'll auto-cleanup any // existing files related to this particular URI if ($this->cache_on == TRUE AND $this->cache_autodel == TRUE AND $this->_cache_init()) { $this->CACHE->delete(); }
```
 return TRUE;
```
  }
  
  // Return TRUE if we don't need to create a result object // Currently only the Oracle driver uses this when stored // procedures are used if ($return_object !== TRUE) { return TRUE; }
  
  // Load and instantiate the result driver
  
  $driver = $this->load_rdriver(); $RES = new $driver(); $RES->conn_id = $this->conn_id; $RES->result_id = $this->result_id; $RES->num_rows = $RES->num_rows();
  
  if ($this->dbdriver == 'oci8') { $RES->stmt_id = $this->stmt_id; $RES->curs_id = NULL; $RES->limit_used = $this->limit_used; }
  
  // Is query caching enabled? If so, we'll serialize the // result object and save it to a cache file. if ($this->cache_on == TRUE AND $this->_cache_init()) { // We'll create a new instance of the result object // only without the platform specific driver since // we can't use it with cached data (the query result // resource ID won't be any good once we've cached the // result object, so we'll have to compile the data // and save it) $CR = new CI_DB_result(); $CR->num_rows = $RES->num_rows(); $CR->result_object = $RES->result_object(); $CR->result_array = $RES->result_array();
```
 // Reset these since cached objects can not utilize resource IDs.
 $CR->conn_id        = NULL;
 $CR->result_id        = NULL;

 $this->CACHE->write($sql, $CR);
 
 if ($this->cache_single === TRUE)
 {
     $this->cache_off();
 }
```
  }
  
  return $RES; }
// --------------------------------------------------------------------

/**
- Load the result drivers
- @access public
- @return string the name of the result class */ function load_rdriver() { $driver = 'CI_DB_'.$this->dbdriver.'_result';
  
  if ( ! class_exists($driver)) { include_once(BASEPATH.'database/DB_result'.EXT); include_once(BASEPATH.'database/drivers/'.$this->dbdriver.'/'.$this->dbdriver.'_result'.EXT); }
  
  return $driver; }
// --------------------------------------------------------------------

/**
- Simple Query
- This is a simplified version of the query() function. Internally
- we only use it when running transaction commands since they do
- not require all the features of the main query() function.
- @access public
- @param string the sql query
- @return mixed */ function simple_query($sql) { if ( ! $this->conn_id) { $this->initialize(); }
  
  return $this->_execute($sql); }
// --------------------------------------------------------------------

/**
- Disable Transactions
- This permits transactions to be disabled at run-time.
- @access public
- @return void */ function trans_off() { $this->trans_enabled = FALSE; }
// --------------------------------------------------------------------

/**
- Start Transaction
- @access public
- @return void */ function trans_start($test_mode = FALSE) { if ( ! $this->trans_enabled) { return FALSE; }
  
  // When transactions are nested we only begin/commit/rollback the outermost ones if ($this->_trans_depth > 0) { $this->_trans_depth += 1; return; }
  
  $this->trans_begin($test_mode); }
// --------------------------------------------------------------------

/**
- Complete Transaction
- @access public
- @return bool */ function trans_complete() { if ( ! $this->trans_enabled) { return FALSE; }
  
  // When transactions are nested we only begin/commit/rollback the outermost ones if ($this->_trans_depth > 1) { $this->_trans_depth -= 1; return TRUE; }
  
  // The query() function will set this flag to TRUE in the event that a query failed if ($this->_trans_status === FALSE) { $this->trans_rollback();
```
 if ($this->db_debug)
 {
     return $this->display_error('db_transaction_failure');
 }
 return FALSE;    
```
  }
  
  $this->trans_commit(); return TRUE; }
// --------------------------------------------------------------------

/**
- Lets you retrieve the transaction flag to determine if it has failed
- @access public
- @return bool */ function trans_status() { return $this->_trans_status; }
// --------------------------------------------------------------------

/**
- Compile Bindings
- @access public
- @param string the sql statement
- @param array an array of bind data
- @return string */ function compile_binds($sql, $binds) { if (strpos($sql, $this->bind_marker) === FALSE) { return $sql; }
  
  if ( ! is_array($binds)) { $binds = array($binds); }
  
  // Get the sql segments around the bind markers $segments = explode($this->bind_marker, $sql);
  
  // The count of bind should be 1 less then the count of segments // If there are more bind arguments trim it down if (count($binds) >= count($segments)) { $binds = array_slice($binds, 0, count($segments)-1); }
  
  // Construct the binded query $result = $segments[0]; $i = 0; foreach ($binds as $bind) { $result .= $this->escape($bind); $result .= $segments[++$i]; }
  
  return $result; }
// --------------------------------------------------------------------

/**
- Determines if a query is a "write" type.
- @access public
- @param string An SQL query string
- @return boolean / function is_write_type($sql) { if ( ! preg_match('/^\s"?(INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK)\s+/i', $sql)) { return FALSE; } return TRUE; }
// --------------------------------------------------------------------

/**
- Calculate the aggregate query elapsed time
- @access public
- @param integer The number of decimal places
- @return integer */ function elapsed_time($decimals = 6) { return number_format($this->benchmark, $decimals); }
// --------------------------------------------------------------------

/**
- Returns the total number of queries
- @access public
- @return integer */ function total_queries() { return $this->query_count; }
// --------------------------------------------------------------------

/**
- Returns the last query that was executed
- @access public
- @return void */ function last_query() { return end($this->queries); }
// --------------------------------------------------------------------

/**
- Protect Identifiers
- This function adds backticks if appropriate based on db type
- @access private
- @param mixed the item to escape
- @param boolean only affect the first word
- @return mixed the item with backticks */ function protect_identifiers($item, $first_word_only = FALSE) { return $this->_protect_identifiers($item, $first_word_only); }
// --------------------------------------------------------------------

/**
- "Smart" Escape String
- Escapes data based on type
- Sets boolean and null types
- @access public
- @param string
- @return integer */ function escape($str) { switch (gettype($str)) { case 'string' : $str = "'".$this->escape_str($str)."'"; break; case 'boolean' : $str = ($str === FALSE) ? 0 : 1; break; default : $str = ($str === NULL) ? 'NULL' : $str; break; }
  
  return $str; }
// --------------------------------------------------------------------

/**
- Primary
- Retrieves the primary key. It assumes that the row in the first
- position is the primary key
- @access public
- @param string the table name
- @return string */ function primary($table = '') { $fields = $this->list_fields($table);
  
  if ( ! is_array($fields)) { return FALSE; }
  
  return current($fields); }
// --------------------------------------------------------------------

/**
- Returns an array of table names
- @access public
- @return array */ function list_tables($constrain_by_prefix = FALSE) { // Is there a cached result? if (isset($this->data_cache['table_names'])) { return $this->data_cache['table_names']; }
  
  if (FALSE === ($sql = $this->_list_tables($constrain_by_prefix))) { if ($this->db_debug) { return $this->display_error('db_unsupported_function'); } return FALSE; }
  
  $retval = array(); $query = $this->query($sql);
  
  if ($query->num_rows() > 0) { foreach($query->result_array() as $row) { if (isset($row['TABLE_NAME'])) { $retval[] = $row['TABLE_NAME']; } else { $retval[] = array_shift($row); } } }
  
  $this->data_cache['table_names'] = $retval; return $this->data_cache['table_names']; }
// --------------------------------------------------------------------

/**
- Determine if a particular table exists
- @access public
- @return boolean */ function table_exists($table_name) { return ( ! in_array($this->prep_tablename($table_name), $this->list_tables())) ? FALSE : TRUE; }
// --------------------------------------------------------------------

/**
- Fetch MySQL Field Names
- @access public
- @param string the table name
- @return array */ function list_fields($table = '') { // Is there a cached result? if (isset($this->data_cache['field_names'][$table])) { return $this->data_cache['field_names'][$table]; }
  
  if ($table == '') { if ($this->db_debug) { return $this->display_error('db_field_param_missing'); } return FALSE;
  }
  
  if (FALSE === ($sql = $this->_list_columns($this->prep_tablename($table)))) { if ($this->db_debug) { return $this->display_error('db_unsupported_function'); } return FALSE; }
  
  $query = $this->query($sql);
  
  $retval = array(); foreach($query->result_array() as $row) { if (isset($row['COLUMN_NAME'])) { $retval[] = $row['COLUMN_NAME']; } else { $retval[] = current($row); } }
  
  $this->data_cache['field_names'][$table] = $retval; return $this->data_cache['field_names'][$table]; }
// --------------------------------------------------------------------

/**
- Determine if a particular field exists
- @access public
- @param string
- @param string
- @return boolean */ function field_exists($field_name, $table_name) { return ( ! in_array($field_name, $this->list_fields($table_name))) ? FALSE : TRUE; }
// --------------------------------------------------------------------

/**
- DEPRECATED - use list_fields() */ function field_names($table = '') { return $this->list_fields($table); }
// --------------------------------------------------------------------

/**
- Returns an object with field data
- @access public
- @param string the table name
- @return object */ function field_data($table = '') { if ($table == '') { if ($this->db_debug) { return $this->display_error('db_field_param_missing'); } return FALSE;
  }
  
  $query = $this->query($this->_field_data($this->prep_tablename($table))); return $query->field_data(); }
// --------------------------------------------------------------------

/**
- Generate an insert string
- @access public
- @param string the table upon which the query will be performed
- @param array an associative array data of key/values
- @return string */ function insert_string($table, $data) { $fields = array(); $values = array();
  
  foreach($data as $key => $val) { $fields[] = $key; $values[] = $this->escape($val); }
  
  return $this->_insert($this->prep_tablename($table), $fields, $values); }
// --------------------------------------------------------------------

/**
- Generate an update string
- @access public
- @param string the table upon which the query will be performed
- @param array an associative array data of key/values
- @param mixed the "where" statement
- @return string */ function update_string($table, $data, $where) { if ($where == '') return false;
  
  $fields = array(); foreach($data as $key => $val) { $fields[$key] = $this->escape($val); }
  
  if ( ! is_array($where)) { $dest = array($where); } else { $dest = array(); foreach ($where as $key => $val) { $prefix = (count($dest) == 0) ? '' : ' AND ';
```
     if ($val !== '')
     {
         if ( ! $this->_has_operator($key))
         {
             $key .= ' =';
         }

         $val = ' '.$this->escape($val);
     }
         
     $dest[] = $prefix.$key.$val;
 }
```
  }
  
  return $this->_update($this->prep_tablename($table), $fields, $dest); }
// --------------------------------------------------------------------

/**
- Prep the table name - simply adds the table prefix if needed
- @access public
- @param string the table name
- @return string */ function prep_tablename($table = '') { // Do we need to add the table prefix? if ($this->dbprefix != '') { if (substr($table, 0, strlen($this->dbprefix)) != $this->dbprefix) { $table = $this->dbprefix.$table; } }
  
  return $table; }
// --------------------------------------------------------------------

/**
- Enables a native PHP function to be run, using a platform agnostic wrapper.
- @access public
- @param string the function name
- @param mixed any parameters needed by the function
- @return mixed */ function call_function($function) { $driver = ($this->dbdriver == 'postgre') ? 'pg_' : $this->dbdriver.'_';
  
  if (FALSE === strpos($driver, $function)) { $function = $driver.$function; }
  
  if ( ! function_exists($function)) { if ($this->db_debug) { return $this->display_error('db_unsupported_function'); } return FALSE;
  } else { $args = (func_num_args() > 1) ? array_splice(func_get_args(), 1) : null;
```
 return call_user_func_array($function, $args);
```
  } }
// --------------------------------------------------------------------

/**
- Set Cache Directory Path
- @access public
- @param string the path to the cache directory
- @return void */ function cache_set_path($path = '') { $this->cachedir = $path; }
// --------------------------------------------------------------------

/**
- Enable Controller-based Query Caching
- @access public
- @param string the name of the controller to be used for caching (optional)
- @param string the name of the controller function to be used for caching (optional)
- @return void */ function controller_cache_on() { $this->cache_mode = 'controller'; $this->cache_single = FALSE; $this->cache_on = TRUE;
  
  $this->cache_ids->controller = (($this->CI->uri->segment(1) == FALSE) ? '+default+' : '+'.$this->CI->uri->segment(1).'+'); $this->cache_ids->controller .= (($this->CI->uri->segment(2) == FALSE) ? 'index+' : $this->CI->uri->segment(2)).'+';
  
  return TRUE; }
// --------------------------------------------------------------------

/**
- Enable Singletime Model-based Query Caching
- @access public
- @param string the name of the model to be used for caching (required)
- @return void */ function model_cache_single($model_name = NULL, $model_function = NULL) { $model_name = strtolower($model_name); $model_function = strtolower($model_function);
  
  $this->cache_mode = 'model'; $this->cache_single = TRUE; $this->cache_on = TRUE;
  
  $this->cache_ids->model = ($model_name === NULL) ? '+model+' : '+'.strval($model_name).'+'; $this->cache_ids->model .= ($model_function === NULL) ? 'function+' : strval($model_function).'+';
  
  return TRUE; }
// --------------------------------------------------------------------

/**
- Enable Table-based Query Caching
- @access public
- @param array the names of the tables to be used for caching (required)
- @return void */ function table_cache_single($table_names = NULL) { if (is_string($table_names)) { $table_names = array($table_names); } else if (is_array($table_names)) { sort($table_names); } else { $table_names = array(); }
  
  $this->cache_mode = 'table'; $this->cache_single = TRUE; $this->cache_on = TRUE;
  
  $this->cache_ids->table = (empty($table_names)) ? '+unnamed_table+' : '+'.implode('+',$table_names).'+';
  
  return TRUE; }
// --------------------------------------------------------------------

/**
- Enable Table-based Query Caching
- @access public
- @param array the names of the tables to be used for caching (required)
- @return void */ function ar_table_cache_on($table_names = array()) { if (is_string($table_names)) { $table_names = array($table_names); } else if (is_array($table_names)) { sort($table_names); } else { $table_names = array(); }
  
  $this->cache_mode = 'ar_table'; $this->cache_single = FALSE; $this->cache_on = TRUE;
  
  $this->cache_ids->ar_table = (empty($table_names)) ? '+unnamed_table+' : '+'.implode('+',$table_names).'+';
  
  return TRUE; }
// --------------------------------------------------------------------

/**
- Enable Key-based Query Caching
- @access public
- @param array the name of the key to be used for caching (required)
- @return void */ function key_cache_single($key_name = NULL) { $this->cache_mode = 'key'; $this->cache_single = TRUE; $this->cache_on = TRUE;
  
  $this->cache_ids->key = ($key_name === NULL) ? '+unnamed_key+' : '+'.strval($key_name).'+';
  
  return TRUE; }
// --------------------------------------------------------------------

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

/**
- Delete cache files connected with a particular controller
- @access public
- @return bool */ function controller_cache_delete($controller_name = '', $controller_function = '') {
  $first_param = ''; $second_param = '';
  
  if (!empty($controller_name)) { $first_param = strval($controller_name); } else { $first_param = (($this->CI->uri->segment(1) == FALSE) ? 'default' : $this->CI->uri->segment(1)); }
  
  if (!empty($controller_name) && !empty($controller_function)) { //Controller name and function specified -> delete all files connected to controller function $second_param = strval($controller_function); } else if (empty($controller_name) && empty($controller_function))
  
  { //Controller name and function specified -> delete all files connected to controller function $second_param = ($this->CI->uri->segment(2) == FALSE) ? 'index' : $this->CI->uri->segment(2); } else if (!empty($controller_name) && empty($controller_function)) { //Only controller name specified -> delete all files connected to controller //append nothing } else if (empty($controller_name) && !empty($controller_function)) { //Only controller function specified -> Fail return FALSE; }
  
  $criteria = "+$first_param+$second_param+";
  
  $cache_dir = $this->CI->db->cachedir.'controller/';
  
  if ( ! $this->_cache_init()) { return FALSE; } $cache_files = $this->CACHE->delete_cache_files('controller', $criteria, $cache_dir); return $cache_files; }
// --------------------------------------------------------------------

/**
- Delete cache files connected with a particular model
- @access public
- @return bool */ function model_cache_delete($model_name = '', $model_function = '') {
  $model_name = strtolower($model_name); $model_function = strtolower($model_function);
  
  $first_param = ''; $second_param = '';
  
  if (!empty($model_name)) { $first_param = strval($model_name); } else { $first_param = (($this->CI->uri->segment(1) == FALSE) ? 'default' : $this->CI->uri->segment(1)); }
  
  if (!empty($model_name) && !empty($model_function)) { //Model name and function specified -> delete all files connected to model function $second_param = strval($model_function); } else if (empty($model_name) && empty($model_function)) { //Model name and function specified -> delete all files connected to model function $second_param = ($this->CI->uri->segment(2) == FALSE) ? 'index' : $this->CI->uri->segment(2); } else if (!empty($model_name) && empty($model_function)) { //Only model name specified -> delete all files connected to model //append nothing } else if (empty($model_name) && !empty($model_function)) { //Only model function specified -> Fail return FALSE; }
  
  $criteria = '+'.$first_param; $criteria .= (!empty($second_param)) ? '+'.$second_param.'+' : '+';
  
  $cache_dir = $this->CI->db->cachedir.'model/';
  
  if ( ! $this->_cache_init()) { return FALSE; } $cache_files = $this->CACHE->delete_cache_files('model', $criteria, $cache_dir); return $cache_files; }
// --------------------------------------------------------------------

/**
- Delete cache files connected with a particular database table
- @access public
- @return bool */ function table_cache_delete($table_name = '') {
  if (empty($table_name)) { return FALSE; }
  
  $criteria = "+$table_name+";
  
  $cache_dir = $this->CI->db->cachedir.'table/';
  
  if ( ! $this->_cache_init()) { return FALSE; } $cache_files = $this->CACHE->delete_cache_files('table', $criteria, $cache_dir); return $cache_files; }
// --------------------------------------------------------------------

/**
- Delete cache files connected with a particular key
- @access public
- @return bool */ function key_cache_delete($key_name = NULL) {
  if (!is_string($key_name)) { return FALSE; }
  
  $criteria = "+$key_name+";
  
  $cache_dir = $this->CI->db->cachedir.'key/';
  
  if ( ! $this->_cache_init()) { return FALSE; } $cache_files = $this->CACHE->delete_cache_files('key', $criteria, $cache_dir); return $cache_files; }
/**
- Enable Query Caching
- @access public
- @return void */ function cache_on() //Deprecated { $this->controller_cache_on(); }
// --------------------------------------------------------------------

/**
- Disable Query Caching
- @access public
- @return void */ function cache_off() { $this->cache_mode = NULL; $this->cache_single = FALSE; $this->cache_ids = (object) array( 'controller' => NULL, 'model' => NULL, 'table' => NULL, 'ar_table' => NULL, 'key' => NULL); $this->cache_on = FALSE; return FALSE; }
// --------------------------------------------------------------------

/**
- Delete the cache files associated with a particular URI (controler + function)
- @access public
- @return void */ function cache_delete($segment_one = '', $segment_two = '') //Deprecated {
  
  $this->delete_controller_cache($segment_one, $segment_two); }
// --------------------------------------------------------------------

/**
- Delete All cache files
- @access public
- @return void */ function cache_delete_all($mode = NULL) { if ( ! $this->_cache_init()) { return FALSE; }
  
  return $this->CACHE->delete_all($mode); }
// --------------------------------------------------------------------

/**
- Initialize the Cache Class
- @access private
- @return void */ function _cache_init() { if (is_object($this->CACHE) AND class_exists('CI_DB_Cache')) { return TRUE; }
  
  if ( ! @include(BASEPATH.'database/DB_cache'.EXT)) { return $this->cache_off(); }
  
  $this->CACHE = new CI_DB_Cache; return TRUE; }
// --------------------------------------------------------------------

/**
- Close DB Connection
- @access public
- @return void */ function close() { if (is_resource($this->conn_id)) { $this->_close($this->conn_id); } $this->conn_id = FALSE; }
// --------------------------------------------------------------------

/**
- Display an error message
- @access public
- @param string the error message
- @param string any "swap" values
- @param boolean whether to localize the message
- @return string sends the application/error_db.php template */ function display_error($error = '', $swap = '', $native = FALSE) { // $LANG = new CI_Lang(); $LANG = new CI_Language(); $LANG->load('db');
  
  $heading = 'Database Error';
  
  if ($native == TRUE) { $message = $error; } else { $message = ( ! is_array($error)) ? array(str_replace('%s', $swap, $LANG->line($error))) : $error; }
  
  if ( ! class_exists('CI_Exceptions')) { // include(BASEPATH.'core/Exceptions'.EXT); include(BASEPATH.'libraries/Exceptions'.EXT); }
  
  $error = new CI_Exceptions(); echo $error->show_error('An Error Was Encountered', $message, 'error_db'); exit; }
// --------------------------------------------------------------------

/**
- Generate an Insert OR Update string
- @access public
- @param string the table upon which the query will be performed
- @param array an associative array data of key/values
- @param string/array the update values in the form 'column1=value1, column2=value2, ...' or as array('column1'=>'value1', 'column2'=>'value2', ...)
- @return string */ function insert_or_update_string($table, $insert_data, $update_data) { $fields = array(); $values = array();
  
  foreach($insert_data as $key => $val) { $fields[] = $key; $values[] = $this->escape($val); }
  
  if (is_array($update_data)) { foreach($update_data as $key => $val) { $valstr[] = $key." = ".$this->escape($val); } $update_string = implode(', ', $valstr); } else if (is_string($update_data)) { $update_string = $update_data; } else { die('_insert_or_update() failed: wrong type for $update'); }
  
  return $this->_insert_or_update($this->dbprefix.$table, $fields, $values, $update_string); }

}

?>[/code]

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Improved Query Caching

Clone this wiki locally