Database Changes in SMF 2.0 - Part 2

[Grudge]

Yes, the way for retrieving data is the same - only the way for doing a query is different. The other main change is that db_insert_id needs to be passed the table name and primary column to allow it to work on PostgreSQL, i.e:

Code Select Expand


$topicID = $smfFunc['db_insert_id']('{db_prefix}topics', 'id_topic');


[Dannii]

Okay thanks.
Can I ask what we should use instead of mysql_num_rows()?

[Thantos]

$smfFunc['db_num_rows']()

For the most part you can take any mysql_* or db_* function and it'll follow the $smfFunc['db_*'] format.  I can't think of any exceptions off the top of my head.

[Sarge]

Great post! Very well written and relatively easy to understand.

Mod authors and people who plan to integrate SMF 2.0 via SSI etc. should definitely check this out when SMF 2.0 comes out.

[Nao 尚]

Single quotes are not allowed in queries....... Okay... But are double quotes allowed?
I've converted some stuff to the latest version and it uses a lot of queries with strings, so I just left them as they were, just replacing single quotes with double quotes, and double quotes with single quotes.

Something like,

Code Select Expand

$smfFunc['db_query']('', 'SELECT something FROM {db_prefix}hello WHERE stuff="' . $yo . '"');

I think it does work, though...

[Thantos]

Well you can simply change it to

Code Select Expand

$smfFunc['db_query']('', 'SELECT something FROM {db_prefix}hello WHERE stuff={string:yo}', array('yo' => $yo)); and then not have to worry about it.

[Grudge]

Not sure what the results of that would be Nao. MySQL normally expects strings to be enclosed in single quotes (In terms of mysql_real_escape_string etc) so you may end up getting odd results. Personally I'd just do as Thantos suggests...

[Daniel15]

Looking good, I'm gonna have to update my mods soon
As for $smfFunc['db_insert'], how long has that been there for? I never noticed it

[Aaron]

As for $smfFunc['db_insert'], how long has that been there for? I never noticed it

Since SMF 2.0 Alpha, if I recall correctly. It's been there for quite a while now, in any case.

[JayBachatero]

That has been there since the early days of 2.0.  Back when the abstraction stuff started.

[Daniel15]

Oh... My bad
I've been using SMF 2.0 since the Alpha, but never went through the abstraction code in great detail...

[Nao 尚]

Not sure what the results of that would be Nao. MySQL normally expects strings to be enclosed in single quotes (In terms of mysql_real_escape_string etc) so you may end up getting odd results.

Hmm that's curious indeed. Never had any problems since last week at least... (When the new format was introduced.)
And I do check most of my pages regularly.
I'll try to change these to the new format anyway, progressively at least.
I will also have to make sure my strings are not escaped before they're passed to db_query... Or maybe I should just use mysql_query for these, I don't know.

[codenaught]

I haven't personally yet did any code with the new query style yet, but from the looks of it it looks pretty nice and clean in my opinion. I love that it makes it more likely to be secure (lowers chances of an oversight). I've always been concerned myself about my mods having some hole in them.

[Nao 尚]

Yeah... It's great, indeed. Takes a while to get used to, but it really makes the programmer feel better after a while
And when you need speed, you can use the "security_disable" flag, or just rely on mysql_query (if you're using MySQL )... This is really good policy.

Apart from that $smfFunc['db_query']('you_bet') and then $smcFunc['db_query']('my_ass'), of course..........

[ByLegenS]

thanks.

[JayBachatero]

Earl > Bertie

Umm I just noticed the phpBB's mascot is named Bertie.  http://www.phpbb.com/shop/

[Thantos]

See again, more proof that Earl > Bertie

[Ben_S]

Converted my custom stuff a few days back, the one that got me was {$db_prefix} to {db_prefix}, looked for ages and couldn't see where my error was coming from. As with most things leave it for an hour or so then come back and you spot it straight away.

Now it's just a shame I can't do the actual upgrade for a little while due to the amount of traffic I'm getting at the mo.

[Grudge]

Ben,

I have to say it's going to be a bit scary when you upgrade your site to SMF 2.0. I think it will be the first proper comparison of 2.0 vs 1.1 performance as your site probably runs up to 99% capacity so if SMF 2.0 is only 5% slower than 1.1 it's really going to show.

Will certainly be interesting

[Sverre]

Where's your confidence, man?!?

What would you do if it turns out there is a (significant) performance regression between 1.1x and 2.0?