Wedge
Public area => Features => The Pub => Features: Forward thinking => Topic started by: Nao on May 6th, 2011, 05:10 PM
-
Feature: MySQL only!
Developer: Arantor
Target: modders, themers, admins
Status: 100% (complete.)
Comment:
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.
-
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.
-
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 at the beginning of query lines has always been preposterous to me. It doesn't help anyone. At all.
-
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.
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.
-
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...
-
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.
http://uk3.php.net/manual/en/faq.databases.php#faq.databases.mysql.php5 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.
-
Okay... Technicalities came to our help for once ;)
-
I have used MySQLi_* before, the biggest advantage over MySQL_* is prepared statement
$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.
$prepare->execute();
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.
$prepare = $pdo->prepare('INSERT INTO people (name, address, postcode) VALUES (:name, :address, :postcode)');
$param = array(
':name' => $name,
':address' => $address,
':postcode' => $postcode
);
$prepare->execute($param);
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'.
-
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));[/tt]
That makes injections completely impossible if they're properly written, of course.
-
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.
-
(Pete is the MySQL pro here. Just pretend you understood and nod with a smile. :ph34r:)
-
I understood :thanks: :lol:
-
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).
-
Except that you still have the issue of platform specific queries, because PDO doesn't rewrite queries.
-
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?
-
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.
-
Just to be clear -- we are not going to offer support for anything else than MySQL, and we never will. At least I won't, and I think Pete won't...
-
Except that you still have the issue of platform specific queries, because PDO doesn't rewrite queries.
My intention was only to post the main different between MySQL and MySQLi, as Nao and Arantor never used MySQLi.
-
mysqli can be used OOP which can be real nice ;)
well... I don't know that law, yet. looks like I have to ask google
-
Arantor, you've seen my posts about prepared statements on SMF as well as many of my other SQL related posts. One of the big things with having a query name is that you can then do caching or break it down even more. You can use the name as a hook. There's a bunch of things you can do.
-
Yes, I saw. I'm not entirely sure what the benefits would be of using prepared statements vs what we're already doing (and how that's only going to change going forward anyway), and looking at the documentation it seems that we'd have to use the SQL syntax rather than the more elegant method (or converting everything to use MySQLi) which really seems to eradicate any benefits we could get from it.
Most hosts still provide ext/mysql and that's fine for now. Changing to async queries and the OOP style doesn't really achieve all that much so we don't really stand to gain much from changing to MySQLi; in any case the biggest DB overhead is one that MySQLi can't optimise for and that schema changes need to come first.
-
That's one of the benefits of the abstraction layer though: you can support MySQLi and MySQL and the title of this topic still holds true ;)
-
Except that we removed half of the abstraction layer because it added complexity and a series of kludgy workarounds for things.
-
Just to be clear -- we are not going to offer support for anything else than MySQL, and we never will. At least I won't, and I think Pete won't...
http://www.percona.com/software/percona-server/faq/
I haven't had a chance to dig into it too much personally yet, but the online export features could be interesting from a backup perspective...
-
Never heard of it personally.
-
Not to "poke the bear", but have you considered/played with Percona at all?
http://www.percona.com/software/percona-server/faq/
I haven't had a chance to dig into it too much personally yet, but the online export features could be interesting from a backup perspective...
-
I actually had an interview with them, and as for their MySQL knowledge, half of the Percona core team actually wrote the best (IMHO) book on High Performance MySQL.
It should be pretty much a drop in replacement for MySQL.
-
Percona is nice (its close enough to a drop in replacement for MySQL for most things) but I've never tried to use it in a production environment. In theory, its a performance optimised version of MySQL. However, I've never been close enough to maxing the capabilities of MySQL to decide to replace it. (e.g. My solution to bottlenecks has always been to modify the web application or acquiring more server resources for the application)
P.S.
I wish we could report posts (see the post above with airport parking services SEO linking. Probably a bot :P)
-
Sine changing from MySQL to Percona we recognize a huge performance benefit. Fully compatible to MySQL but much faster and really stable.
-
P.S.
I wish we could report posts (see the post above with airport parking services SEO linking. Probably a bot :P)
I do read all of the posts on these boards so don't worry, I eventually see and delete these posts & accounts (I might miss posts if they're edited later though...)
Also, I'm a bit concerned that we're getting our first spammer since I enabled the Arantor Captcha and a custom question. Both of these together should have stopped them all...
-
Experience tells me that SEO services spam is actually human driven, and no amount of CAPTCHAs or questions can defeat that.
-
And they get paid what for every link...? Ten cents? Pathetic...
-
It might even be more like a dollar per 50 or something ridiculous. Wage equality is a long way away.
-
Poor people.
Hey Pete, I think there are a couple of topics waiting for your feedback if you have two minutes ;)
-
I have quite a few topics unread right now :/ Had to go back to bed, feeling so ill this morning.
-
Take your time then.
Health is more important. Says Nao who's killing himself on the job :lol:
-
Then perhaps you should take a break too :P
-
I... I really need to have Wedge ready and out by the end of the year. It's becoming an obsession to me.
I don't know what I'll do AFTER that (I'm certainly not going to provide support for it, but I'll do my best to keep developing & debugging it), but I know I want phase one to be over...
-
It might be unrelated...but funnily in OS X Lion Server, they dropped MySQL support...
http://www.theregister.co.uk/2011/08/02/apple_dumps_mysql_from_mac_os_x_server/
-
Interesting.
But Mysql is here to stay. If not, then it's forks will.
We write for real use. If the paradigm shifts to pgsql or whatever, then Wedge will offer such versions.
Right now we just don't think any server with pgsql installed would not already have Mysql installed as well. Focusing on one db system only allows us to save precious time for other features and for speed improvements.
-
Apple Server is not our typical deployment scenario anyway.