PHP Manual

Procedural user-defined storage handler

The query cache plugin supports the use of user-defined storage handler. User-defined storage handler can use arbitrarily complex invalidation algorithms and support arbitrary storage media.

All user-defined storage handlers have to provide a certain interface. The functions of the user-defined storage handler will be called by the core of the cache plugin. The necessary interface consists of seven public functions. Both procedural and object oriented user-defined storage handler must implement the same set of functions.

Please check the example for details.

<?php
/* Enable default caching of all statements */
ini_set("mysqlnd_qc.cache_by_default"1);

/* Procedural user defined storage handler functions */

$__cache = array();

function 
get_hash($host_info$port$user$db$query) {
  global 
$__cache;
  
printf("\t%s(%d)\n"__FUNCTION__func_num_args());

  return 
md5(sprintf("%s%s%s%s%s"$host_info$port$user$db$query));
}

function 
find_query_in_cache($key) {
  global 
$__cache;
  
printf("\t%s(%d)\n"__FUNCTION__func_num_args());

  if (isset(
$__cache[$key])) {
    
$tmp $__cache[$key];
    if (
$tmp["valid_until"] < time()) {
      unset(
$__cache[$key]);
      
$ret NULL;
    } else {
      
$ret $__cache[$key]["data"];
    }
  } else {
    
$ret NULL;
  }

  return 
$ret;
}

function 
return_to_cache($key) {
  
/*
     Called on cache hit after cached data has been processed,
     may be used for reference counting
  */
  
printf("\t%s(%d)\n"__FUNCTION__func_num_args());
}

function 
add_query_to_cache_if_not_exists($key$data$ttl$run_time$store_time$row_count) {
  global 
$__cache;
  
printf("\t%s(%d)\n"__FUNCTION__func_num_args());

  
$__cache[$key] = array(
    
"data"               => $data,
    
"row_count"          => $row_count,
    
"valid_until"        => time() + $ttl,
    
"hits"               => 0,
    
"run_time"           => $run_time,
    
"store_time"         => $store_time,
    
"cached_run_times"   => array(),
    
"cached_store_times" => array(),
  );

  return 
TRUE;
}

function 
query_is_select($query) {
  
printf("\t%s('%s'): "__FUNCTION__$query);

  
$ret FALSE;
  if (
stristr($query"SELECT") !== FALSE) {
    
/* cache for 5 seconds */
    
$ret 5;
  }

  
printf("%s\n", (FALSE === $ret) ? "FALSE" $ret);
  return 
$ret;
}

function 
update_query_run_time_stats($key$run_time$store_time) {
  global 
$__cache;
  
printf("\t%s(%d)\n"__FUNCTION__func_num_args());

  if (isset(
$__cache[$key])) {
    
$__cache[$key]['hits']++;
    
$__cache[$key]["cached_run_times"][] = $run_time;
    
$__cache[$key]["cached_store_times"][] = $store_time;
  }
}

function 
get_stats($key NULL) {
  global 
$__cache;
  
printf("\t%s(%d)\n"__FUNCTION__func_num_args());

  if (
$key && isset($__cache[$key])) {
    
$stats $__cache[$key];
  } else {
    
$stats = array();
    foreach (
$__cache as $key => $details) {
      
$stats[$key] = array(
        
'hits'              => $details['hits'],
        
'bytes'             => strlen($details['data']),
        
'uncached_run_time' => $details['run_time'],
        
'cached_run_time'   => (count($details['cached_run_times']))
                                  ? 
array_sum($details['cached_run_times']) / count($details['cached_run_times'])
                                  : 
0,
      );
    }
  }

  return 
$stats;
}

function 
clear_cache() {
  global 
$__cache;
  
printf("\t%s(%d)\n"__FUNCTION__func_num_args());

  
$__cache = array();
  return 
TRUE;
}

/* Install procedural user-defined storage handler */
if (!mysqlnd_qc_set_user_handlers("get_hash""find_query_in_cache",
      
"return_to_cache""add_query_to_cache_if_not_exists",
      
"query_is_select""update_query_run_time_stats",
       
"get_stats""clear_cache")) {
  
printf("Failed to install user-defined storage handler\n");
}


/* Connect, create and populate test table */
$mysqli = new mysqli("host""user""password""schema""port""socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");

printf("\nCache put/cache miss\n");

$res $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();

/* Delete record to verify we get our data from the cache */
$mysqli->query("DELETE FROM test WHERE id = 1");

printf("\nCache hit\n");

$res $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();

printf("\nDisplay cache statistics\n");
var_dump(mysqlnd_qc_get_cache_info());

printf("\nFlushing cache, cache put/cache miss");
var_dump(mysqlnd_qc_clear_cache());

$res $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
?>

上の例の出力は以下となります。

        query_is_select('DROP TABLE IF EXISTS test'): FALSE
        query_is_select('CREATE TABLE test(id INT)'): FALSE
        query_is_select('INSERT INTO test(id) VALUES (1), (2)'): FALSE

Cache put/cache miss
        query_is_select('SELECT id FROM test WHERE id = 1'): 5
        get_hash(5)
        find_query_in_cache(1)
        add_query_to_cache_if_not_exists(6)
array(1) {
  ["id"]=>
  string(1) "1"
}
        query_is_select('DELETE FROM test WHERE id = 1'): FALSE

Cache hit
        query_is_select('SELECT id FROM test WHERE id = 1'): 5
        get_hash(5)
        find_query_in_cache(1)
        return_to_cache(1)
        update_query_run_time_stats(3)
array(1) {
  ["id"]=>
  string(1) "1"
}

Display cache statistics
        get_stats(0)
array(4) {
  ["num_entries"]=>
  int(1)
  ["handler"]=>
  string(4) "user"
  ["handler_version"]=>
  string(5) "1.0.0"
  ["data"]=>
  array(1) {
    ["18683c177dc89bb352b29965d112fdaa"]=>
    array(4) {
      ["hits"]=>
      int(1)
      ["bytes"]=>
      int(71)
      ["uncached_run_time"]=>
      int(398)
      ["cached_run_time"]=>
      int(4)
    }
  }
}

Flushing cache, cache put/cache miss    clear_cache(0)
bool(true)
        query_is_select('SELECT id FROM test WHERE id = 1'): 5
        get_hash(5)
        find_query_in_cache(1)
        add_query_to_cache_if_not_exists(6)
NULL


PHP Manual