Utilizing the cache for extension-specific data

[tig_]

I have a listener that on

Code: Select all

'core.text_formatter_s9e_render_before'

needs to query the database looking for a particular entry. Specifically, it's checking for the existence of a file. I currently use `file_exists()` which is super fast. However, for various reasons, I'd like to check the database instead.

E.g. like this:

Code: Select all

            $sql = 'SELECT 1 FROM phpbb3_external_images WHERE file = "' . $file_name"';
            $result = $this->db->sql_query($sql);
            $exists = $this->db->sql_fetchrow($result) ? true : false;
            $this->db->sql_freeresult($result);

I've found this is super slow. It appears it's possible to use the built-in database cache system to cache this, but I can't figure out how to make it work.

The API document doesn't seem to provide any hints.

I'd like to do something like this (pseudo code):

Code: Select all

        $cache_key = 'file_exists_' . $file_name . '_' . $file_ext;
        $exists = $this->cache->get($cache_key);
        
        if ($exists === false) {
            $sql = 'SELECT 1 FROM phpbb3_external_images WHERE file = "' . $file_name . '" AND ext = "' . $file_ext . '"';
            $result = $this->db->sql_query($sql);
            $exists = $this->db->sql_fetchrow($result) ? true : false;
            $this->db->sql_freeresult($result);
            $this->cache->put($cache_key, $exists);
        }

        return $exists;

Is there an API in phpbb that will help with this?

[Kailey]

Example from the Trackers extension:

Code: Select all

$trackers = $this->cache->get('_trackers');

if (!$trackers)
{
	$sql = 'SELECT *
		FROM ' . $this->table_prefix . 'trackers_trackers';
	$result = $this->db->sql_query($sql);
	$trackers = [];
	while ($row = $this->db->sql_fetchrow($result))
	{
		$trackers[$row['tracker_id']] = $row;
	}
	$this->db->sql_freeresult($result);

	$this->cache->put('_trackers', $trackers);
}

[rxu]

If the point is just caching plain SQL query results, then it's a subject of simply adding cache TTL, like

Code: Select all

$cache_ttl = 3600; // Seconds

$sql = 'SELECT 1 FROM phpbb3_external_images WHERE file = "' . $file_name"';
$result = $this->db->sql_query($sql, $cache_ttl);

[Steve]

Example from the Trackers extension:

Code: Select all

$trackers = $this->cache->get('_trackers');

if (!$trackers)
{
	$sql = 'SELECT *
		FROM ' . $this->table_prefix . 'trackers_trackers';
	$result = $this->db->sql_query($sql);
	$trackers = [];
	while ($row = $this->db->sql_fetchrow($result))
	{
		$trackers[$row['tracker_id']] = $row;
	}
	$this->db->sql_freeresult($result);

	$this->cache->put('_trackers', $trackers);
}

This is my preferred method, as you can delete it when you submit changes with: $this->cache->destroy('_trackers');

[tig_]

Thanks folks.