[DISC] phpBB2 Native SQL Caching (1.0.0)

[I'm Only 16]

With this modification installed you will be able to cache sql queries for faster page generation times and less database usage, the following can be done with this method:

Standard Query:

Code: Select all

$db->sql_query($sql);

Cached Query:

Code: Select all

// TRUE|FALSE is the $transaction param from the standard phpBB2 DBAL.
$db->sql_query($sql, (TRUE|FALSE), 'myquery');

Clearing cache:

Code: Select all

$db->cache_destroy('myquery')/ // Single cache
$db->cache_destroy(); // All cache

I hereby give all authors permission to use this native sql caching system in their mods/projects.

MOD Download: mod_sql_caching_100.zip

Changelog:
- 0.1.0 to 1.0.0: Reviewed the code and realized that i had forgotten about the $transaction param so it has been re-inserted. (also submitted to moddb)

[I'm Only 16]

- Edit: Config caching is now bundled.

[Ramon Fincken]

interesting...

so we have to change all intensive sql queries with an extra parameter?

did you write this on your own?

Rfn

[I'm Only 16]

Yep

[drathbun]

Intensive but different queries should not be cached. From what I am guessing is happening here, this should only be used on data that does not change frequently. Like (per the example) the config table. Other candidates would be the ranks table, the smilies table, the banned user table, and the word substitutions table. I've written caching for all of these tables already for my own boards.

Also I would like to see this hooked into the admin panel, so that any change to the config table takes place immediately and does not wait for four hours. The simple fix is to (again, based on the example posted) use the cache_destroy option as the last operation within the admin_board.php code. That way the next page load will reset the cache with the newly updated config table values.

[I'm Only 16]

It would prob also be logical to cache the auth table and the moderators/categories on the index.

[drathbun]

Auth table is another good candidate, sure. I removed the moderator list on the index and on the forum display to cut out the two queries.

[I'm Only 16]

Also for authors looking to integrate without having it required this line will exec queries:

Code: Select all

// TRUE|FALSE = Standard DBAL $transaction param.
if ( ! $result = ( class_exists('sql_db_x') ) ? $db->sql_query($sql, TRUE|FALSE, 'cachename') : $db->sql_query($sql) )
{
    .. error ..
}

This will detect and use sql caching if available.

[Ramon Fincken]

the cache file has an expiration date of 4 hours from what I saw yesterday...


Rfn

[Marnboorapa]

It's very interesting, but link now died.

[A.I. BOT]

Link is back up, Im hosting the download for him ... the server was doing a 20 minute kernel update. Works good now.

Thanks.

[deny]

Interesting but it will be nice to have fully modification with how/to code for
the ranks table, the smilies table, the banned user table, the word substitutions table etc. for the people that are not sure what to change and where.
We have now only one example for config table and it looks more as mod for experienced php users.

[Ramon Fincken]

when it's MODDB I certainly have some uses for this script..

however I do not recommend 'newbies' using this mod, besides if they have a good programming knowledge..


Rfn

[NGLeader]

It would also be nice if the mod authors got together and create a "Cache Manager Mod" with ACP options:

Delete All Cache,
Delete Cache Older Than 30 Days
Delete Cache Older Than 45 Days

Features:
Template and SQL Caching

[I'm Only 16]

thats something to think of later on, as for now i just need to get it into the moddb.