Simple Machines Blogs > Developers' Blog

Database implementation in SMF 2.0

<< < (2/4) > >>

Nao 尚:
Hello,


--- Quote from: Grudge on November 02, 2007, 09:22:31 AM ---Since 1.1 RC2 (I think) this has been used for string functions to allow efficient implementation of unicode support.
--- End quote ---
Yes, and it's a nice implementation. Too bad it's undocumented. People just aren't aware that $func/$smfFunc versions of the same functions are UTF8-compatible. (I don't use UTF8 in my code yet -- still too buggy in SMF, so...)


--- Quote ---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.
--- End quote ---
On average, a SMF page has about 15 queries I'd say... A bit more for the Display.php pages. Doesn't take a lot of memory. 25KB of data? Where? I'm pretty sure it's sci-fi at this point :) From using the db debug info variable, and checking out the viewquery page, I'd say the whole page of queries (all in all) is 10KB at the most. Hardly 25KB per query. I don't think the overhead is significant... And maybe it could be avoided by using variable referencing (&) in the db_query() dummy function? I'm not too proficient with parameter referencing so correct me if I'm wrong.
$smfFunc could still be used for performance-critical operations like maintenance features.

One of the other problems with the new syntax for db_query is the empty parameter at the beginning of the parameter list. Why wasn't it just put at the end, after the __LINE__ and __FILE__ parameters?
Something like $dummy = '' would allow for no changes. I noticed a similar problematic pattern in an SSI function where an "exclude boards" parameter is now available, but not at the end. Interestingly (for me at least), I had already modified my 1.1.3 code to include such a feature, but I did it differently -- I checked the include board list, and if a board had a negative value, then it would be added to the exclude board list. This allows for no changes at all in the SSI function structure, while retaining the "exclude boards" feature.


--- Quote ---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.
--- End quote ---
Ah, too bad... I love this syntax.


--- Quote ---Instead SMF 2.0 now uses inner joins for all queries. These are just as quick
--- End quote ---
Are you 100% sure they're just as quick? Did you benchmark them?

What is the purpose of the "INNER JOIN" member, as opposed to the more common (in SMF) "LEFT JOIN"? Is "INNER JOIN" supposed to mean that there's always an equivalent in the joined table and that there should be no overhead as opposed to a LEFT JOIN call?

EDIT--I just checked, and INNER JOIN seems to do the same job as a "FROM table1, table2" indeed. It only returns the common entries. And it's more readable I have to agree, although the query is a bit longer. Still, I don't know if it's exactly the same performance as the double FROM.


--- Quote ---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.
--- End quote ---
Well I'm not a mod author but I did convert a lot of big mods (Pretty URLs, Googlebot & Spiders and Ultimate Profile come to mind), and $smfFunc was sometimes annoying to implement (not because of the multiple search & replace, but because I had to make sure I added global $smfFunc where needed), but not as annoying as the completely new admin area (menu stuff). Since this is completely undocumented, I had to do a lot of trial & error and it probably took more than half the time I needed to convert the mods.

So, as a conclusion, I'd say it would be nice to explain somewhere the steps to apply when converting a mod that has entries in the admin area :) (Not that I need that now, since I've converted all the mods I needed, but I'm pretty sure most mod authors will be glad to avoid the whole trial & error stuff I had to go through!)

Aaron:
It's a bit logical these changes haven't been documented yet, don't you think?  As SMF 2.0 is still in an early beta stage, certain things are still likely to change and thus extensively documenting probably hasn't got a high priority.

Then again, I'm not a dev, so I can't tell for sure. ;)

Nao 尚:

--- Quote from: Aäron on November 08, 2007, 05:36:08 AM ---It's a bit logical these changes haven't been documented yet, don't you think?  As SMF 2.0 is still in an early beta stage, certain things are still likely to change and thus extensively documenting probably hasn't got a high priority.
--- End quote ---
I'm not asking for a full-featured document, but a topic somewhere would be nice, with explanations on the differences to take into account when upgrading ;)
There's already such a topic BTW, but it doesn't mention the admin panel changes.

Grudge:
The thing is that $smfFunc is, in the most part, globalised everywhere in 2.0 as nearly every function uses the database so I don't think it's particularly problematic.

The reason why '' is at the start of every query is that *eventually* every query will have a unique string there - we just have only added the names to those functions which we needed to alter for PostgreSQL and SQLite - it's not optional.

The comment on SSI is fair if that is the case - we shouldn't be changing the position of previous parameters in SSI IMHO.

As for db_fetch_assoc etc. That memory overhead is *very* significant as board sizes scale up to the size of, for example, the community here. I really don't see that using $smfFunc['db_query'] is really any different to smf_db_query in terms of typing. Also if you need to globalise $smfFunc then you'd need to globalize $db_prefix anyway so it's not like it's really extra work. Of course you can simply use smf_db_query if desired but I don't think it's as elegant so it certainly won't be used like that in the core.

INNER JOIN and cross joins should be comparable speed wise. We saw no different in speed at all (Positive or negative) on MySQL.

As for documentation - you are right that it could be better. I did however think that the admin menu stuff is documented in the Subs-Menu.php file?

Nao 尚:

--- Quote from: Grudge on November 08, 2007, 08:12:47 AM ---The thing is that $smfFunc is, in the most part, globalised everywhere in 2.0 as nearly every function uses the database so I don't think it's particularly problematic.
--- End quote ---
I did get SMF to crash several times because the mod conversions I was doing didn't add $smfFunc to the globals.
For instance, mod authors may add db queries to functions that usually don't call the database. So they have to add these global declarations manually.


--- Quote ---The reason why '' is at the start of every query is that *eventually* every query will have a unique string there - we just have only added the names to those functions which we needed to alter for PostgreSQL and SQLite - it's not optional.
--- End quote ---
You mean we'll have to change the mods again?
And what's going to be the purpose of this mysterious string, then?


--- Quote ---The comment on SSI is fair if that is the case - we shouldn't be changing the position of previous parameters in SSI IMHO.
--- End quote ---
Thanks.
Maybe you could consider doing it the way I've been doing it, then? It's only a matter of a few minor rewrites. It's been working great on my boards really. You just go through the board list, if a negative value is found, turn it to positive and add it to the exclude list, then unset the current value, and continue to the next iteration.


--- Quote ---As for db_fetch_assoc etc. That memory overhead is *very* significant as board sizes scale up to the size of, for example, the community here.
--- End quote ---
I'm already hearing Eldacar say, "when you've got a huge board, you usually don't worry about these little things. You need larger servers anyway." :P

That's the kind of thing he tells me  :P Because I'm one of those who complain that SMF is sometimes... a little bit on the overgrown side. I wouldn't like to see it become another bloated phpBB. $smfFunc is precisely one of these places where I think SMF might be getting a little too big. I mean, so many changes, just to add PostgreSQL support (among others)... I don't know of anyone who uses this. It's a bit frustrating.

Still, the other new features in SMF2 keep me from going back to v1.1 ;)


--- Quote ---I really don't see that using $smfFunc['db_query'] is really any different to smf_db_query in terms of typing.
--- End quote ---
I don't know, maybe you don't have to press the AltGr key to enter [ and ], and often end up with "[{...@]" because you released the AltGr key too late ;)
db_query('...') doesn't require any shift/alt/alt+gr combination.


--- Quote ---Also if you need to globalise $smfFunc then you'd need to globalize $db_prefix anyway so it's not like it's really extra work.
--- End quote ---
Yes you have a point.


--- Quote ---Of course you can simply use smf_db_query if desired but I don't think it's as elegant so it certainly won't be used like that in the core.
--- End quote ---
I gave up long ago on convincing the team to use an alias function ;) Although the idea's still playing in my head... It won't shut up but I'm doing my best not to let my fingers type it :P


--- Quote ---INNER JOIN and cross joins should be comparable speed wise. We saw no different in speed at all (Positive or negative) on MySQL.
--- End quote ---
I hope you're right! :)


--- Quote ---As for documentation - you are right that it could be better. I did however think that the admin menu stuff is documented in the Subs-Menu.php file?
--- End quote ---
Yes it is, but what's not documented, is how to convert a mod's admin area to SMF2. Unfortunately it can't be automated.

Navigation

[0] Message Index

[#] Next page

[*] Previous page