Skip to main content ↑ ↓
Topic: Custom Profile Fields (Read 7055 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Custom Profile Fields

I was toying a bit with this function to add in a new option and had a general question to ponder.

First the preliminarily .... I'm not the sharpest tool in the drawer when it comes to db stuff  ;)

Today those variable/value fields are all stored in the themes table under theme 1 so they load up as expected etc.   I was thinking those may be better in their own table with the same basic structure as themes (first thought)  ... id_member/variable/value cols, so many rows ... this just works with the existing querys plus it seems better than adding in a new col every time a new option is added.  So thats kind of question 1 ... should it move and if so structure?

Question 2 really is more if the above approach is right, I'd like to avoid additional querys since we still have to get data out of themes, and now a new table as well.   Seems like given the theme table structure and assuming a similar one for custom profile, I'd use a union on the two tables ... but we don't support union (don't know why) and I don't know if sqlite and postgre do (just have not checked) .... there is a full join option but that could be massive I think.

Of course there is then some ugliness during an upgrade to move existing data.

Anyone else have thoughts on this?  Maybe its just fine the way it is  :-\

Re: Custom Profile Fields

Reply #1 –

Question 1 makes sense. However question 2 touches on why it's important and why it's a problem.

The performance issue. A user's own profile fields are implicitly loaded with their theme options. Custom fields, not so much. But you don't necessarily incur extra queries because I don't know if you load other users' themes-table values in loadMemberData any more or not. ISTR this could happen in SMF. So then it's just about changing one query for another.

So, then: do you need to get anything out of themes for other users or is it just in profile view where you implicitly need both sets of data? If it's only in profile, only get them all in profile, and get only custom fields elsewhere.

As far as unions go, that's largely a legacy thing. Partly that SMF was built around MySQL 3.23 originally and UNION didn't appear until MySQL 4.0. Enforcing no-union was just a bonus. Secondarily, remember SMF 1.x didn't have a query layer, which means bad SQL injections were a risk - and union queries were a typical additional risk, since like subqueries they were ripe for abuse. These two issues are much less of a problem in 2.0, of course.

There is a side issue that the query viewer doesn't show you a link for the EXPLAIN of unioned queries.

Re: Custom Profile Fields

Reply #2 –

Thanks for tacking this. I think they'd better moved anyway, if only to clear up some of the unnormalized (and ugly) nature of the current structure.
On union, the above is pretty much how it is afaik: rather a "since we don't use union lets also secure against it", than anything else.
The best moment for testing your PR is right after you merge it. Can't miss with that one.

Re: Custom Profile Fields

Reply #3 –

thanks for the information and idea .... I'll dig in to this a bit deeper and see what I can come up with ....

Re: Custom Profile Fields

Reply #4 –

While looking at moving this, I did not get far before I had my first huh?  So this is a bit of a repeat from IRC but posted here since not everyone sees that, here it goes!

in loadMemberData(), There is this query https://gist.github.com/Spuds/5326635

Was this intended to only load in the custom profile fields from the theme table or was there other data it was also trying to get? Without the theme id in the query some of the variables/value pairs will be duplicated so the query just seems odd at first glance, plus all of the theme specific things are already in $settings.  

The query results are added in to $user_profile[$user][some variable] ... that's later placed as $profile = user_profile[$user] and then put in to context in loadMemberContext()

It was pointed out that that query dates back to 1.0 .... so it was not for custom profile fields back then ... and the theme table structure was the same back then as well.  I looked at what base data was installed in the themes table back 1.0 but did not immediately find anything that would be outside of $settings

So I'm still trying to understand what data was being loaded and why like that so if anyone has some insight on this ...

Re: Custom Profile Fields

Reply #5 –

QuoteWas this intended to only load in the custom profile fields from the theme table or was there other data it was also trying to get? Without the theme id in the query some of the variables/value pairs will be duplicated so the query just seems odd at first glance, plus all of the theme specific things are already in $settings.  

It's getting everything. Whether this is desirable or intentional is another matter entirely. I seem to remember that it is done that way because being more selective in the query would actually be slower than not doing so.

The themes table contains user preferences as well as actual theme settings, so if you're doing the profile load you do need to get everything so that you can populate things like look/layout for other users (if you're an admin and can view others' profiles)

Re: Custom Profile Fields

Reply #6 –

Maybe things have possibility changed over time or even over the last few weeks, or I'm just making things up since Dr. Who is on tonight and I want to stop looking :P 

I'm going to keep digging but right now the only use of $profile['options'] I can find is for the custom profile fields, the rest of the information that is loaded in to $user_profile['id_member']['options'] just does not appear to be used. 

That query is run for the display function but only the custom profile fields are needed AFAIK ...

For the profile section, there is a loadThemeOptions($memID) which loads all the items for a given $memID so the admin can see a given users settings, it does not appear to use the $user_profile items but I need to keep checking.

I can see how it could be used, but I'm just failing to see it being used in my tracing through the functions. 

Re: Custom Profile Fields

Reply #7 –

QuoteMaybe things have possibility changed over time or even over the last few weeks, or I'm just making things up since Dr. Who is on tonight and I want to stop looking

Some of us have already seen said episode :P

Quote$user_profile['id_member']['options'] just does not appear to be used. 

Not even under $context['member']['options'] in the profile area? loadMemberData is also used in the profile area with the 'profile' parameter.

Re: Custom Profile Fields

Reply #8 –

QuoteSome of us have already seen said episode :P
Fun episode ... those first trip new companion style ones.  I'm sure there will be more exploring the impossible girl as the season goes on .... covered the leaf but not the missing dates.  All of the religious lines/views were quite fun as well.

I read that this will be Matt's last season as well ... or that he has not signed up past the Xmas episode, don't know if thats true or not.

QuoteNot even under $context['member']['options'] in the profile area? loadMemberData is also used in the profile area with the 'profile' parameter.
I'll have to check again ... for some reason I though those were being loaded in to context (other than custom profile stuff) with another query ... but its like tracking a chameleon :P

Re: Custom Profile Fields

Reply #9 –

Season 7 won't be his last season, there's a much, much bigger event to come - since season 8 should be showing later this year... just in time for the 50th anniversary in November.

And yes, it's like tracking a chameleon but far from the worst of them. You wait until you decide to overhaul how post moderation, warnings and bans work. It's not pretty. Playing with it just somehow feels so fragile and like I really shouldn't be disturbing eldritch demons...