[WIP]SQLite3 Cache System

[nend]

Requirements

• PHP with SQLite3

Incompatible With

• Mods that save caches in SMF's ob callback function. (PHP Bug #62000)

Information
Will this is a work in progress and is the first time ever I touched SQLite. Hopefully didn't miss up too bad on the code, going to have the SQLite experts shout, "No, that isn't the way" or "Your crazy". However the system looks promising after a few test runs with my code, which like I said may not be perfect.

One problem that I have with the code, since I am no SQLite expert, I can't get vacuum to work properly so the file isn't able to shrink. 

What I am trying to do though is find a alternative to the file caching mechanism in SMF which is currently bugged by illegal file name characters and file name limit exceeding. This system in some runs I seems equal to file caching, in some instances better and in some a little worse. So my conclusion is that it has about the same performance as file caching minus the invalid/max character limits.

Installation
First off I am going to show what I did, then I expect everyone's input. Lets make this a community project because I am not making a mod for it but I am allowing anyone to distribute my findings as they see fit, including making mods, lol.

First off edit your sources.

Load.php
Find

Code Select Expand

// Zend Platform/ZPS/etc.
elseif (function_exists('output_cache_put'))
output_cache_put($key, $value);
elseif (function_exists('xcache_set') && ini_get('xcache.var_size') > 0)
{
if ($value === null)
xcache_unset($key);
else
xcache_set($key, $value, $ttl);
}
Add After

Code Select Expand

// SI Cache
elseif (function_exists('sicache_put'))
{
sicache_put($key, $value, $ttl);
}

Find

Code Select Expand

// Zend's pricey stuff.
elseif (function_exists('output_cache_get'))
$value = output_cache_get($key, $ttl);
elseif (function_exists('xcache_get') && ini_get('xcache.var_size') > 0)
$value = xcache_get($key);
Add After

Code Select Expand

elseif (function_exists('sicache_get'))
$value = sicache_get($key);

Then in the main index.php
Find

Code Select Expand

// And important includes.
require_once($sourcedir . '/QueryString.php');
require_once($sourcedir . '/Subs.php');
require_once($sourcedir . '/Errors.php');
require_once($sourcedir . '/Load.php');
require_once($sourcedir . '/Security.php');
Add After

Code Select Expand

require_once($sourcedir . '/sicache.php');

Then create a file name it "sicache.php" put the contents that I am going to post below and upload it to your Sources.

Code Select Expand


<?php

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB, $sicache_trans;

$sicacheDB = new SQLite3('./cache/cache');
@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int);');
if (!isset($sicache_trans))
$sicache_trans = '';
register_shutdown_function('sicache_trans');
}

function sicache_get($key) {

global $sicacheDB, $sicacheData;

if(!isset($key))
return;
if(!isset($sicacheDB))
sicache_ini();
$query = @$sicacheDB->querySingle('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' LIMIT 1', true);
if ($query != false) {
return $query['value'];
}
}

function sicache_put($key, $value, $ttl = 120) {

global $sicacheDB, $sicache_trans;

if(!isset($key) || !isset($value))
return;
if (!isset($sicacheDB))
sicache_ini();
if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.sqlite_escape_string(time() + $ttl).');';
}
}

function sicache_trans() {

global $sicacheDB, $sicache_trans;

ob_end_flush(); // We need to be the last on the block, flush and let SMF do its ob callback stuff.
@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.time().';'.$sicache_trans.'COMMIT;VACUUM;');
}
?>

To activate just turn on caching. Please test levels 1 to 3.

The cache file will be saved to your cache folder.

Benchmarks

Carrying out 100 iterations

Short String
File Cache Put: 0.91465306282 seconds
File Cache Get: 0.727028846741 seconds
File Cache Del: 0.538260936737 seconds
SQLite3 Cache Put: 0.169445037842 seconds
SQLite3 Cache Get: 0.505801916122 seconds
SQLite3 Cache Del: 0.000108003616333 seconds

Medium String
File Cache Put: 0.744477033615 seconds
File Cache Get: 0.524791002274 seconds
File Cache Del: 0.123141050339 seconds
SQLite3 Cache Put: 0.000550985336304 seconds
SQLite3 Cache Get: 0.401298999786 seconds
SQLite3 Cache Del: 0.000107049942017 seconds

Long String
File Cache Put: 0.72679400444 seconds
File Cache Get: 0.562448978424 seconds
File Cache Del: 0.173590898514 seconds
SQLite3 Cache Put: 0.00059700012207 seconds
SQLite3 Cache Get: 0.556365013123 seconds
SQLite3 Cache Del: 0.0001060962677 seconds

As you can see Put & Del are faster than Get. This is because Put and Del can be put into transactions which are allot faster than single queries. There are penalties though, especially on database connection, but these don't seem as bad as file cache for some reason.

Also I had to do 100 request because some results for SQLite where coming up with 0 second results or the E-5 ending.

*updated benchmarks.

[Robert.]

Nice! I've never really understood how cache really works, but your code looks promising

[vbgamer45]

Nice! I've never really understood how cache really works, but your code looks promising

The goal is to reduce sql queries which take more time and if the data does not change often it is better to cache it in memory/files/or in this case sql lite.

[nend]

Looking at it though, I would guess it would all have to depend on your setup. SQLite queries on my host are like 10x faster than MySql queries.

I have been trying to comprehend the documentation, my code has changed quite a bit because of it.

From what I gather if a SQLite DB is hit hard enough it basically becomes a memory driven DB until the queries calm down again. The documentation says the recommended cache is around 2mb. So if your DB is running at less then possibly the DB will stick in memory until things calm back down again. My DB I have been playing around with has been under 200kb's. This is however based on my comprehension of the documentation, we need a SQLite expert here to confirm this. 

There is also a option for a memory database through SQLite, but that would not work in this case, it is destroyed when the script disconnects.

[nend]

Updated to SQLite 3 branch, old code uses SQLite2

sicache.php

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB;

$sicacheDB = new SQLite3('./cache/cache');
if (@$sicacheDB->exec('DELETE FROM cache WHERE ttl < '.time()) != 1)
$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text, value text, ttl int);');
}


function sicache_get($key) {

global $sicacheDB;

if(!isset($sicacheDB))
sicache_ini();

$query = @$sicacheDB->query('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' LIMIT 1');
if ($query != false) {
$data = $query->fetchArray();
return $data['value'];
}
}

function sicache_put($key, $value, $ttl) {

global $sicacheDB;

if(!isset($sicacheDB))
sicache_ini();

if ($value === null) {
@$sicacheDB->exec('DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\'');
} else {
@$sicacheDB->exec('INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.sqlite_escape_string(time() + $ttl).')');
}
}
 
?>

[nend]

Updated to use transactions for all the put operations. Get operations are sort of tricky, don't think there is a transaction method. Transactions in SQLite are faster than doing the queries one at a time.

New code, I need to quit playing with this for a while, lol.
sicache.php

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB;

$sicacheDB = new SQLite3('./cache/cache');
if (@$sicacheDB->exec('DELETE FROM cache WHERE ttl < '.time()) != 1)
$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text, value text, ttl int);');

// Register transaction shutdown function to run on exit.
register_shutdown_function('sicache_trans');
}


function sicache_get($key) {

global $sicacheDB;

if(!isset($sicacheDB))
sicache_ini();

$query = @$sicacheDB->query('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' LIMIT 1');
if ($query != false) {
$data = $query->fetchArray();
return $data['value'];
}
}

function sicache_put($key, $value, $ttl) {

global $sicacheDB, $sicache_trans;

if (!isset($sicacheDB))
sicache_ini();

if (!isset($sicache_trans))
$sicache_trans = '';

if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.sqlite_escape_string(time() + $ttl).');';
}
}

function sicache_trans() { // Transactions are faster with SQLite, we can't do it for read but at least for write and delete. :D

global $sicacheDB, $sicache_trans;

$sicacheDB->exec('BEGIN;'.$sicache_trans.'COMMIT;');
}
?>

[nend]

Ok, a question here to all SMF Guru's.

So I registered a shutdown function to run all db inserts and deletes in one big transaction.

My question is there any other registered shutdown functions in SMF?

I need to know this so I can know if flushing the buffer is a good idea in my shutdown function. The problem I can foresee if another shutdown function that got registered later than mine, needs access to the buffer and I already flushed it.

Flushing the buffer though IMHO would make it feel like the script speeded up even though it hasn't. Basically outputing the contents while the script is still doing its inserts and deletes in the SQLite DB. So if there is nothing to follow that would ouput to the buffer, then outputting the buffer and then processing sounds like a good idea.

[nend]

Ok figured it out, SMF doesn't have any shutdown functions, however it has a ob callback function in QueryString.php called ob_sessrewrite().

Well found a bug, it doesn't seem to be related to SMF or the script. It looks to be a PHP bug. You can not access a SQLite3 database in a ob callback function. You can access a MySql database or write/read to a file but not use a SQLite3 database in the function.

If you try to access a SQLite3 database in the callback function PHP will return no error, not even in the error logs. The script will actually output nothing and the SQLite3 database can not be accessed.

Simple demonstration of the bug.

Code Select Expand

<?php
ob_start('callbackfunc');
echo 'test test test test test test test test test ';
function callbackfunc($buffer) {
$DB = new SQLite3('db');
$DB->exec('CREATE TABLE test (one int, two text);');
$DB->exec('INSERT INTO test VALUES (10, \'some text\')');
return $buffer;
}
?>
You can move the SQLite3 database queries outside of the callback function and all will work ok, but inside it will not.

This doesn't only serve a problem to my work in progress here, it also serves as a problem to SMF when using SQLite3 and a query is made from within the callback by say a mod. There are only a few mods that do this, most of them modify the output buffer like Pretty URLs and I am sure Simple SEF does it the same way.

These results where generated on a server running PHP 5.3 with SQLite3. Maybe they don't apply to earlier versions of PHP or other versions of SQLite. I however doubt the problem relies in SQLite but mainly with PHP 5.3 and/or possibly others and how they access SQLite.

[nend]

SMF's ob callback was getting called after the shutdown function. Fixed by flushing of the buffer and turning off the output buffer, thus forcing SMF to do its end processing before the shutdown function executes the rest of its task. The script should take the same amount of time to run but may notice a speed increase because the buffer should be sent before the big insert and delete transaction.

Now using a more proper query for the get function. We are using querySingle instead of query then fetchArray, basically reduced down two lines to one.

Moved all deletes of expired ttls to the shutdown function.

Also the vacuum is fixed, which means we finally have a size management system. No longer databases growing to a gigantic size.

New code.

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB, $sicache_trans;

$sicacheDB = new SQLite3('./cache/cache');
@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int);');
if (!isset($sicache_trans))
$sicache_trans = '';
register_shutdown_function('sicache_trans');
}

function sicache_get($key) {

global $sicacheDB, $sicacheData;

if(!isset($key))
return;
if(!isset($sicacheDB))
sicache_ini();
$query = @$sicacheDB->querySingle('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' LIMIT 1', true);
if ($query != false) {
return $query['value'];
}
}

function sicache_put($key, $value, $ttl = 120) {

global $sicacheDB, $sicache_trans;

if(!isset($key) || !isset($value))
return;
if (!isset($sicacheDB))
sicache_ini();
if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.sqlite_escape_string(time() + $ttl).');';
}
}

function sicache_trans() {

global $sicacheDB, $sicache_trans;

ob_end_flush(); // We need to be the last on the block, flush and let SMF do its ob callback stuff.
@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.time().';'.$sicache_trans.'COMMIT;VACUUM;');
}
?>

[nend]

Hmm, no comments. Well for the sake of keeping my code changes organized here is another code change I wiped up real quick. It is sort of a workaround for PHP bug 62000. I don't like it because it isn't really optimal way, but there isn't any other choice then to load any caches that where set in the ob callback before it loads and prevent ob call back from accessing the database until its finished. 

Turn off you cache to delete the file, apply the new code to sicache.php then turn on your cache to your desired level. Have to do this step because we have a new table for the ob cache.

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB, $sicache_trans;

$sicacheDB = new SQLite3('./cache/cache');
@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);');
if (!isset($sicache_trans))
$sicache_trans = '';
register_shutdown_function('sicache_trans');
}

function sicache_get($key) {

global $siOBcache;

if(!isset($key))
return;
if(isset($siOBcache[$key]))
return $siOBcache[$key];
if(isset($siOBcache))
return;

global $sicacheDB, $sicacheData;

if(!isset($sicacheDB))
sicache_ini();

$query = @$sicacheDB->querySingle('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' LIMIT 1', true);
if ($query != false) {
return $query['value'];
}
}

function sicache_put($key, $value, $ttl = 120) {

global $siOBcache, $sicache_trans;

if(!isset($key) || !isset($value))
return;

if (isset($siOBcache)) {
if ($value === null) {
$sicache_trans.= 'DELETE FROM obcache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO obcache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.sqlite_escape_string(time() + $ttl).');';
}
return;
 }

if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.sqlite_escape_string(time() + $ttl).');';
}
}

function sicache_trans() {

global $siOBcache, $sicacheDB, $sicache_trans;

//PHP Bug #62000 workaround, we need to get the whole ob cache.
//Basically a sloppy blind query, but what else can you do?
$query = @$sicacheDB->query('SELECT * FROM obcache');
if ($query != false) {
$siOBcache = array();
while ($data = $query->fetchArray()) {
$siOBcache[$data['key']] = $data['value'];
}
}
ob_end_flush();
if (!isset($sicacheDB))
sicache_ini();
@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.time().';DELETE FROM obcache WHERE ttl < '.time().';'.$sicache_trans.'COMMIT;VACUUM;');
}
?>

[emanuele]

Nice work!

I was testing it in SMF, what I noticed is that the:

Code Select Expand

@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);');
slows down the cache a lot:
Cache hits: 5: 0.05661s for 14216 bytes
while removing it:
Cache hits: 5: 0.00092s for 14216 bytes
that's on board index, and a couple of refreshes.
For reference this is with the file cache:
Cache hits: 5: 0.00088s for 14216 bytes
Of course there is *a lot* of variability, but the first is definitely higher that the other two.

Something like that should help:

Code Select Expand

static $fileexists;

if (!isset($fileexists))
$fileexists = file_exists('./cache/cache');

$sicacheDB = new SQLite3('./cache/cache');
if (!$fileexists)
@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);');


Additionally, for security it would be better if the name of the database is random (you can save it in Settings.php).

[nend]

On previous testing I was getting the same results, because I was manually calling the setup function.

I wonder why that query is so slow. 

The only thing though is one time with SQLite I got DB corruption, The DB will exist just with 0 bytes and the slow query sets up the tables on corruption. There is a better way to do this though, allot faster instead of running the query all the time and suppressing the error like in my code. You have demonstrated one way, just need to check for 0 byte databases, the sign of corruption.

Allot of the performance of SQLite really depends on the machine and its settings. The file cache is the same way, different machine settings can lead to different performance outputs. I like your results, basically the same performance as the file cache, 0.00004s difference. There is one bonus to this system, freedom from the file limitations of the file system like file names and characters. However the server must have SQLite3 in order to use it.

[emanuele]

On previous testing I was getting the same results, because I was manually calling the setup function.

I wonder why that query is so slow. 

"PRAGMA auto_vacuum = 2; "
Seems to be the main problem, removing it it becomes as faster as without the entire query.

The only thing though is one time with SQLite I got DB corruption, The DB will exist just with 0 bytes and the slow query sets up the tables on corruption. There is a better way to do this though, allot faster instead of running the query all the time and suppressing the error like in my code. You have demonstrated one way, just need to check for 0 byte databases, the sign of corruption.

If you have to check it every once in a while, you can use the same "trick" SMF uses to check if the cache directory is writable (check in Subs.php, search for $securityFiles and you will find the place).
Maybe you can do the same with the PRAGMA and the VACUUM.

Allot of the performance of SQLite really depends on the machine and its settings. The file cache is the same way, different machine settings can lead to different performance outputs. I like your results, basically the same performance as the file cache, 0.00004s difference. There is one bonus to this system, freedom from the file limitations of the file system like file names and characters. However the server must have SQLite3 in order to use it.

That difference is not significant (the result should be something like +/- 20%), they are basically the same and that is good!

[nend]

Thanks for the input, will have to give a few of those suggestions a try.

Did a few optimizations, really just taking off feathers of weight off the system. Maybe a bad expression there but the optimizations I done aren't going to show unless the cache gets around 200 request and still not even a second. I don't think there is much more I can do on this end to give it any extra power. The system is at full speed, not going to get any faster I think.

I tried moving the db connection to get loaded first, thinking it might give the db more time to load. All I did was create the illusion the system was going faster, SMF still had the same page speeds. If anyone wishes to test this on any further you can call sicache_ini() after including the sicache.php file in index.php.

The first request though is always going to be the slowest, the DB is still loading, as the request go down the line they get faster and faster. Main reason is that sicache_ini() which loads the database gets called on the first request. One could call this function earlier in SMF, but it seems to have no benefits like I stated.

From what I read the "PRAGMA" has to be called before the tables get set up. If it isn't then there is a change auto_vacuum will not work. I'll research more if the PRAGMA is actually required there. Auto vacuum though is a nice feature that would keep the database size down.

Also I decided to use filesize() on sicache_ini() to check the db condition, if 0 is returned then the database has to be set up again. It seems to be working pretty good.

Well set out to try to make a cache system that may be better or worse than the file cache. Ended up with something that is sometimes better or sometimes worse. 

Here is my latest code.

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB, $sicache_trans, $cachedir, $sicache_time;

$sicacheDB = new SQLite3($cachedir.'/cache');
if (filesize($cachedir.'/cache') == 0) {
@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);');
}
if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}
register_shutdown_function('sicache_trans');
}

function sicache_get($key) {

global $siOBcache, $sicache_time, $sicache_trans;

if(!isset($key))
return;
if(isset($siOBcache[$key]))
return $siOBcache[$key];
if(isset($siOBcache))
return;

global $sicacheDB;

if(!isset($sicacheDB)) {
sicache_ini();
}

$query = @$sicacheDB->querySingle('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' AND ttl > '.$sicache_time.' LIMIT 1', true);
if ($query != false) {
return $query['value'];
}
}

function sicache_put($key, $value, $ttl = 120) {

global $siOBcache, $sicache_trans, $sicache_time;

if(!isset($key))
return;

if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}

if (isset($siOBcache)) {
if ($value === null) {
$sicache_trans.= 'DELETE FROM obcache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO obcache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
return;
 }

if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
}

function sicache_trans() {

global $siOBcache, $sicacheDB, $sicache_trans, $sicache_time;

$query = @$sicacheDB->query('SELECT * FROM obcache');
if ($query != false) {
$siOBcache = array();
while ($data = $query->fetchArray()) {
if ($data['ttl'] > $sicache_time) {
$siOBcache[$data['key']] = $data['value'];
}
}
}

ob_end_flush();

if (!isset($sicacheDB)) {
sicache_ini();
}

@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.$sicache_time.';DELETE FROM obcache WHERE ttl < '.$sicache_time.';'.$sicache_trans.'COMMIT;VACUUM;');
}
?>

[CircleDock]

I couldn't get your code to run as-is on either my local server nor on a production server and therefore had to convert it to use the PDO interface. All my changes are documented.

As you can see, I've also slightly modified the Pragmas and now the vacuuming works a treat.

Code Select Expand


<?php
//---------------------------------------------------------------
// SMF Caching using SQLite3 database.
//
// Original code by: nend
// Modified by: CircleDock
//
// Changes:
//      * Modified code for PHP5 PDO implementation of SQLite
//        especially sichache_ini()
//      * Changed "key" to "ckey" to avoid keyword collision.
//      * Removed second parameter from query statement in
//        sichache_get() -- parameter was "true" but caused
//        multiple errors.
//---------------------------------------------------------------

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB, $sicache_trans, $cachedir, $sicache_time;
    $dbName = $cachedir . '/cache.sdb';
    $connectStr = 'sqlite:' . $dbName;

// Open the connection
    $sicacheDB = new PDO($connectStr);   

// If new database, then create the two tables needed
// otherwise clean-up the database and reduce its disk size
if (filesize($dbName) == 0) {
$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (ckey TEXT UNIQUE PRIMARY KEY, value TEXT, ttl INTEGER); CREATE TABLE obcache (ckey TEXT UNIQUE PRIMARY KEY, value TEXT, ttl INTEGER);');
} else
        $sicacheDB->exec('VACUUM;');
        
if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}
register_shutdown_function('sicache_trans');
}

// Function "sqlite_secape_string()" may be missing in PDO, 
// but should be in the underlying class.
if (!function_exists('sqlite_escape_string'))
{
    function sqlite_escape_string( $string ){
        return SQLite3::escapeString($string);
    }
}

function sicache_get($key) {

global $siOBcache, $sicache_time, $sicache_trans;

if(!isset($key))
return;
if(isset($siOBcache[$key]))
return $siOBcache[$key];
if(isset($siOBcache))
return;

global $sicacheDB;

if(!isset($sicacheDB)) {
sicache_ini();
}

$query = $sicacheDB->query('SELECT * FROM cache WHERE ckey = \''.sqlite_escape_string($key).'\' AND ttl > '.$sicache_time.' LIMIT 1');
if ($query != false) {
        $row = $query->fetch(PDO::FETCH_ASSOC);
return $row['value'];
}
}

function sicache_put($key, $value, $ttl = 120) {

global $siOBcache, $sicache_trans, $sicache_time;

if(!isset($key))
return;

if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}

if (isset($siOBcache)) {
if ($value === null) {
$sicache_trans.= 'DELETE FROM obcache WHERE ckey = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO obcache (ckey, value, ttl) VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
return;
 }

if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE ckey = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache (ckey, value, ttl) VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
}

function sicache_trans() {

global $siOBcache, $sicacheDB, $sicache_trans, $sicache_time;

$query = $sicacheDB->query('SELECT * FROM obcache');
if ($query != false) {
$siOBcache = array();
while ($data = $query->fetch(PDO::FETCH_ASSOC)) { // was PDO:FETCH_ASSOC
if ($data['ttl'] > $sicache_time) {
$siOBcache[$data['ckey']] = $data['value'];
}
}
}

ob_end_flush();

if (!isset($sicacheDB)) {
sicache_ini();
}

@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.$sicache_time.';DELETE FROM obcache WHERE ttl < '.$sicache_time.';'.$sicache_trans.' COMMIT;PRAGMA incremental_vacuum;');
}
?>


I've yet to benchmark it but on both the servers where it is installed, it does appear to be faster.

Mark

[Arantor]

I'd benchmark it. Anything else may actually be placebo effect.

[CircleDock]

I agree that any apparent speed increase may be subjective. However there must be some gain due to the fact that illegal file names won't be used as cache keys.

[Arantor]

That's a definite functional enhancement, sure, and even if you don't get a performance boost, it's certainly useful.

Of course, that relies on having SQLite3 available...

[CircleDock]

There is one line that's causing a problem on the production server but not on my local server. In function sicache_trans()

Code Select Expand


ob_end_flush();

It's causing errors on that server and a read of the PHP docs suggests the call may actually not be necessary.

Edit:
ob_end_flush is necessary otherwise the MySQL database isn't updated with any changes. However:

Code Select Expand


if (ob_get_level() > 0)
    ob_end_flush();

seems to fix that problem.

[nend]

@ Circledock, vacuum has been working in the recent code. On the previous code I forgot to call vacuum;. Also if you use PDO it is going to use SQLite 2, which is OK also.

As for performance gains, It is a little difficult to explain because mainly there looks to be none. The initial connection is actually slower than the file cache, which means that the first few cache calls will be slower, however anything after will be faster usually. If you benchmark it you will either get results faster, slower or about equal.

One suggestion though, I had to figure this out also. What you want in SQLite is to lock that database file the least amount time possible. Basically anything that writes to the DB file, "INSERT;DELETE;UPDATE;VACUUM" should be called in a transaction to minimize file locking.

There is one line that's causing a problem on the production server but not on my local server. In function sicache_trans()

Code Select Expand


ob_end_flush();

It's causing errors on that server and a read of the PHP docs suggests the call may actually not be necessary.

Edit:
ob_end_flush is necessary otherwise the MySQL database isn't updated with any changes. However:

Code Select Expand


if (ob_get_level() > 0)
    ob_end_flush();

seems to fix that problem.

Yeah, notice that error a while back and forgot to fix it. It is caused by the buffer being empty, doesn't happen to much but it does happen.

The code around this area is a workaround for a bug in PHP, if I don't flush the buffer and there is a function in the callback that uses the cache then SMF will crash with no output. I hate the bug and even hate my own workaround because it is a blind query. All SQLite database operations crash in the output buffer callback.