MySQL only!


  • Dadman with a boy
  • Posts: 16,072
MySQL only!
« on May 6th, 2011, 05:10 PM »
Feature: MySQL only!
Developer: Arantor
Target: modders, themers, admins
Status: 100% (complete.)

PostgreSQL and SQlite are great database systems. But they just aren't used that much, and they add more complexity to database development in SMF. Wedge continues abstracting database queries, but doesn't attempt to convert them to another SQL language.
From our experience, users with PGSQL or SQlite installed on their server always have MySQL installed as well. If you really don't, Wedge just isn't for you, but we won't go back to applying patches for systems that no one really ever uses. It makes development tedious, longer, and probably insecure.

Re: MySQL only!
« Reply #1, on May 6th, 2011, 11:17 PM »
After all I think SQLite isn't made for forums. It works well with the most sites, but forums are clearly the wrong scope, except then you are setting up a testboard.

Re: MySQL only!
« Reply #2, on May 7th, 2011, 12:05 AM »
I guess it's mostly a matter of saying, "look, we support everything and everyone". Or perhaps the dev in charge of SQlite integration was himself playing with SQlite at the time. Or something. It just doesn't add up...
Plus, the simple fact that SMF requires an *empty* parameter[1] at the beginning of query lines has always been preposterous to me. It doesn't help anyone. At all.
 1. Used only in a few internal queries...

Re: MySQL only!
« Reply #3, on May 7th, 2011, 12:22 AM »
OK, let's back up before any FUD starts flying.

Cast your mind back to late 2005 when SMF 2.0 started development. PHP 5 was still relatively new, and due to PHP's licence change, classic client MySQL (the mysql_* functions) was no longer compliant. That's why the MySQLi interface even exists, in fact.

Alongside this, PHP 5 was bringing SQLite with it, enabled by default, so in theory any host with PHP 5 would have SQLite support enabled. And with MySQL being tenuously supported it seemed reasonably logical.

There's the reason for it.[1]

The empty parameter is not always empty. Yes, it's only used in a few queries but they're ones where MySQL's syntax isn't compatible with either PostgreSQL and/or SQLite, and there was no need to slow down *all* queries for the sake of the minority. For example, there's a RAND() reference in there; except on one or other, it's RANDOM() not RAND(). Since it's only used in one place, there's the logic of using specialist replacements.

Honestly, if they were going for proper abstraction, the best place to start would have been upending the schema and removing FIND_IN_SET which is the single biggest barrier to PostgreSQL use, not to mention shooting performance in the foot on anything that isn't MySQL.

It claims to support these other platforms, but it only really gives the impression of doing so; you'd have to up performance by an order of magnitude on the backend to see an improvement in the application as a whole because of the fact that FIND_IN_SET (which is so heavily used in MySQL/SMF) is not available in PostgreSQL and is implemented as a user defined function. Guess how much that hurts performance.
 1. I'm not defending it, because it's not defensible. But there it is.

Re: MySQL only!
« Reply #4, on May 7th, 2011, 12:32 AM »
Yeah, SMF's implementation of a user-defined find_in_set was pretty funny...

Had forgotten about the Sqlite stuff. How exactly did the PHP/MySQL duo manage to survive the PHP license change then? I don't remember ever using mysqli_* mysqlelf...

Re: MySQL only!
« Reply #5, on May 7th, 2011, 01:00 AM »
No, I haven't used MySQLi either, nor the newer mysqlnd (native driver).

What happened was, prior to 5.0, it was bundled and thus part of the source. In the case of 5.0+, it wasn't bundled, nor built by default but because it's then a dynamic linked library it's sort of on a technicality - it's then calling it through a defined interface rather than direct linking the code. has more on it no longer being bundled - specifically, the PHP licence isn't strictly BSD/Apache licensed, but for the general use it makes no real difference.

As of 5.3.0, it's been replaced by the native driver (mysqlnd) and as such there is no physical requirement at all, so that's the future-proofed answer.

Re: MySQL only!
« Reply #6, on May 7th, 2011, 07:47 AM »
Okay... Technicalities came to our help for once ;)

Re: MySQL only!
« Reply #7, on May 7th, 2011, 01:26 PM »Last edited on May 7th, 2011, 01:32 PM by CJ Jackson
I have used MySQLi_* before, the biggest advantage over MySQL_* is prepared statement

Code: [Select]
$prepare = $mysqli->prepare('INSERT INTO people (name, address, postcode) VALUES (?, ?, ?)'); // '?' specifies param
$prepare->bind_param('sss', $name, $address, $postcode); // The first param has to be string, which specifies the data type, the letter 's' is obvious.

The great thing about prepared statement is that you don't need to sensitize the data input that done automatically.  PDO_MYSQL has better support for prepared statement.

Code: [Select]
$prepare = $pdo->prepare('INSERT INTO people (name, address, postcode) VALUES (:name, :address, :postcode)');
$param = array(
':name' => $name,
':address' => $address,
':postcode' => $postcode

With PDO you can use named parameters and you don't need to specifies data types that done automatically.  Prepared statement is the way forward.  I like to see someone try to pull off a SQL injection with prepared statement.

I would stick with the MySQL software, mainly because of dialect different in SQL in other SQL software.  I used Oracle at Uni, it wouldn't let me insert multiple rows via 'INSERT' statement only one row is allowed per 'INSERT'.

Re: MySQL only!
« Reply #8, on May 7th, 2011, 01:49 PM »
Prepared statements are interesting, but they're already available in Wedge/SMF2:

wesql::query('SELECT id FROM table WHERE name IN {array_string:names}', array('names' => (array) $names));

That makes injections completely impossible if they're properly written, of course.

Re: MySQL only!
« Reply #9, on May 7th, 2011, 01:54 PM »
Plus SMF/Wedge's version doesn't require two separate dependencies as it only uses the stock mysql client, as opposed to needing the PDO core plus the PDO-MySQL specific library.

Re: MySQL only!
« Reply #10, on May 7th, 2011, 01:59 PM »
(Pete is the MySQL pro here. Just pretend you understood and nod with a smile. :ph34r:)

Re: MySQL only!
« Reply #11, on May 7th, 2011, 02:27 PM »
I understood   :thanks:  :lol:

Re: MySQL only!
« Reply #12, on May 7th, 2011, 10:15 PM »
Well as I remember it right SQLite (2) is a core feature since some time in php and SQLite3 is a core feature since 5.3. So you can use both. If you implement SQLite then you should stick with SQLite3 as SQLite(2) is just to slow for it.

To the PDO-Topic:
With the PDO interface it shouldn't be that hard to allow additional databases. You have here and there some differences but that's nothing that couldn't be fixed (or something like that).

Re: MySQL only!
« Reply #13, on May 7th, 2011, 10:27 PM »
Except that you still have the issue of platform specific queries, because PDO doesn't rewrite queries.

Re: MySQL only!
« Reply #14, on May 7th, 2011, 11:39 PM »
I might not get the point but AFAIK real prepared statements as CJ Jackson posted em are not only there to avoid sql injection but to improve performance.
is that what is used or are there just checks for the data to avoid injections?

Re: MySQL only!
« Reply #15, on May 7th, 2011, 11:50 PM »
Real prepared statements have a truly marginal performance improvement because the parser doesn't have to parse the query. For us, Amdahl's Law applies.