Simple Machines Blogs > Developers' Blog

Database implementation in SMF 2.0

(1/4) > >>

Grudge:
There have been a few posts here and there about the way SMF 2.0 addresses database abstraction and I thought that people might appreciate a blog on the topic from a developer to get an insight into the reasons behind our implementation and the benefits we see to the approach.

Firstly, let's talk about $smfFunc - the SMF 2.0 version of $func - name changed to allow for better integration with other applications. Since 1.1 RC2 (I think) this has been used for string functions to allow efficient implementation of unicode support. $smfFunc is an array of function names, so, for example $smfFunc['strpos'] could be assigned to the name 'strpos' or 'uc_strpos'. Whenever $smfFunc['strpos'] is called the function whose name it's values corresponds is actually called. Take a made up example.


--- Code: ---function cheap()
{
  return 2;
}

function expensive()
{
  return 200;
}

$smfFunc['price'] = $_POST['name'] == 'grudge' ? 'cheap' : 'expensive';

--- End code ---

In the above piece of code if the script was submitted with the name of 'grudge' $smfFunc['price'] would equal 'cheap' - otherwise it is 'expensive'. This variable can then be used like in the below:

--- Code: ---echo $smfFunc['price']();

--- End code ---

Which would execute the function 'cheap' or 'expensive' dependant on whether the name was grudge - hence displaying either 2 or 200 in the browser.

The above code shows how we can dynamically assign a function to a variable. Note that the function never exists in the variable - only the name of the function. This means that when you access $smfFunc['price']() PHP calls the cheap or expensive function direct - there is no overhead at all. You could do the above set of functionality like below:


--- Code: ---function cheap()
{
  return 2;
}

function expensive()
{
  return 200;
}

function price()
{
  if ($_POST['name'] == 'grudge')
    return cheap();
  else
  return expensive();
}

--- End code ---

This would work exactly the same used with:

--- Code: ---echo price();

--- End code ---

However, note that now when you call the price function PHP actually goes through another function to get there. The value returned from cheap/expensive has to pass into the price function then back out again. For a simple return value like an integer this overhead is relatively minimal but now imagine doing this with mysql_fetch_assoc - which is the function used to fetch data from the database. This function can (And on an average page load will) return, say, 25kB of data in an array. In the example above all this data needs to transition through a "pass through" function - whereas the first example (Using $smfFunc) effectively allows PHP to access the function natively.

So - this explains what we are doing with SMF 2.0. In the database loading function you'll find a line effectively doing this:

--- Code: ---$smfFunc['db_fetch_assoc'] = 'mysql_fetch_assoc';

--- End code ---

And when we call $smfFunc['db_fetch_assoc'] all PHP needs to do is a quick lookup on the value of $smfFunc['db_fetch_assoc'] and then call mysql_fetch_assoc. If you looked in the database loading function for the postgreSQL database the line would say:

--- Code: ---$smfFunc['db_fetch_assoc'] = 'postg_fetch_assoc';

--- End code ---

and so forth for sqlite. As in the first example we could have done:

--- Code: ---function db_fetch_assoc($handle)
{
  return mysql_fetch_assoc($handle);
}

--- End code ---

But this has the memory overhead. Hence, and for consistantency, all database functions in SMF 2.0 now follow this $smfFunc model. There are other things we've done in 2.0 which I won't go into here but are quite cool. In particular there is now a whole set of functions for creating, altering and droping tables. This is to ensure mod authors can write there mods *once* and SMF will automatically sort out the database changes so they work regardless of which database the user is using (i.e. the mod will work on MySQL, PostgreSQL and SQLite without any extra effort from the author).

The other thing we've done with 2.0 is try to make the SQL much more standard to enable abstraction to work correctly. So, for example, all cross joins such as:
SELECT *
FROM (smf_topics AS t, smf_messages AS m)
WHERE m.id_msg = t.id_first_msg

Won't work on PostgreSQL and many other schemas as it's not standard. Instead SMF 2.0 now uses inner joins for all queries. These are just as quick but much more standard and IMHO easier to read - so the above function now looks like:
SELECT *
FROM smf_topics AS t
  INNER JOIN smf_messages AS m ON (m.id_msg = t.id_first_msg)

There have been many more changes than the above but we hope they are for the benefit of users and that mod authors will be quick to adapt to the new way of working with the database. Happy to answer any comments you might have.

Grudge

MPT.:
Nice! It would be useful..

Dannii:
What is cool (though I don't know if it's possible in PHP) are to have functions that rewrite themselves after their first use. The first time the function is run it checks to see which version should be used, and then rewrites itself to use it, meaning that the check is only ever run once, although if the function is never run, it's never actually checked. That's probably not useful in this situation as the settings files mean we know for sure whether the forum is using MySQL or PostgreSQL or something else, but it's very useful in javascript when you really can assume nothing about what the user's browser can do ;)

SleePy:
http://us3.php.net/manual/en/function.rename-function.php
http://us3.php.net/manual/en/function.override-function.php

The thing is, it only works for built-in functions.

winrules:
And only if you have a certain extension installed ;)

Navigation

[0] Message Index

[#] Next page