[DISC] phpBB2 Native SQL Caching (1.0.0)

A place for MOD Authors to post and receive feedback on MODs still in development. No MODs within this forum should be used within a live environment! No new topics are allowed in this forum.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

IMPORTANT: MOD Development Forum rules

On February 1, 2009 this forum will be set to read only as part of retiring of phpBB2.
I'm Only 16
I've Been Banned!
Posts: 15
Joined: Thu Jun 29, 2006 5:19 pm

[DISC] phpBB2 Native SQL Caching (1.0.0)

Post by 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)
Last edited by I'm Only 16 on Fri Jun 30, 2006 6:26 pm, edited 3 times in total.
I'm Only 16
I've Been Banned!
Posts: 15
Joined: Thu Jun 29, 2006 5:19 pm

Post by I'm Only 16 »

- Edit: Config caching is now bundled.
Last edited by I'm Only 16 on Fri Jun 30, 2006 6:25 pm, edited 2 times in total.
User avatar
Ramon Fincken
Registered User
Posts: 4835
Joined: Thu Oct 14, 2004 1:04 am
Location: NL, The Netherlands Amsterdam area @GMT +1
Contact:

Post by Ramon Fincken »

interesting...

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

did you write this on your own?

Rfn
Dutch quality fully managed WordPress hosting - ManagedWPHosting.nl

Before changing a file, some code or installing a MOD >> Make a backup first!

Do you like my mods? paypal me $1 :) forumsoftware[AT}creativepulses[DOT}nl [/size]
PhpBBantispam.com || Instant find your mod here
I'm Only 16
I've Been Banned!
Posts: 15
Joined: Thu Jun 29, 2006 5:19 pm

Post by I'm Only 16 »

Yep :)
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by 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 blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
I'm Only 16
I've Been Banned!
Posts: 15
Joined: Thu Jun 29, 2006 5:19 pm

Post by I'm Only 16 »

It would prob also be logical to cache the auth table and the moderators/categories on the index.
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by 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 blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
I'm Only 16
I've Been Banned!
Posts: 15
Joined: Thu Jun 29, 2006 5:19 pm

Post by 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.
Last edited by I'm Only 16 on Fri Jun 30, 2006 6:26 pm, edited 1 time in total.
User avatar
Ramon Fincken
Registered User
Posts: 4835
Joined: Thu Oct 14, 2004 1:04 am
Location: NL, The Netherlands Amsterdam area @GMT +1
Contact:

Post by Ramon Fincken »

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


Rfn
Dutch quality fully managed WordPress hosting - ManagedWPHosting.nl

Before changing a file, some code or installing a MOD >> Make a backup first!

Do you like my mods? paypal me $1 :) forumsoftware[AT}creativepulses[DOT}nl [/size]
PhpBBantispam.com || Instant find your mod here
User avatar
Marnboorapa
Registered User
Posts: 84
Joined: Sat Jan 08, 2005 3:19 pm
Location: Thailand
Contact:

Post by Marnboorapa »

It's very interesting, but link now died.
A.I. BOT
Registered User
Posts: 1848
Joined: Thu Apr 17, 2003 11:43 pm
Location: Newfoundland, Canada
Contact:

Post by 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
Registered User
Posts: 565
Joined: Wed May 14, 2003 9:14 am
Location: Find-Ip-Address.org
Contact:

Post by 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.
Geolocation of any IP address including detection of hostname,
browser, country and country code with ip address range web tool.

IP Address Locator | Email Tracking | IP Address | Check Email
User avatar
Ramon Fincken
Registered User
Posts: 4835
Joined: Thu Oct 14, 2004 1:04 am
Location: NL, The Netherlands Amsterdam area @GMT +1
Contact:

Post by 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
Dutch quality fully managed WordPress hosting - ManagedWPHosting.nl

Before changing a file, some code or installing a MOD >> Make a backup first!

Do you like my mods? paypal me $1 :) forumsoftware[AT}creativepulses[DOT}nl [/size]
PhpBBantispam.com || Instant find your mod here
User avatar
NGLeader
Registered User
Posts: 88
Joined: Fri Mar 14, 2003 12:04 am
Location: New York, N.Y.
Contact:

Post by 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
I've Been Banned!
Posts: 15
Joined: Thu Jun 29, 2006 5:19 pm

Post by I'm Only 16 »

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

Return to “[2.0.x] MODs in Development”