Importing member fields into members.data?

Nao

  • Dadman with a boy
  • Posts: 16,013
Importing member fields into members.data?
« on February 16th, 2014, 01:11 AM »
This is mainly for @TE...

I'm looking into moving some {db_prefix}members columns into {db_prefix}members.data, where they'll be serialized and stored with other variables.

A few reasons for that...
- Well, there are 60 or 70 columns in that table... Not exactly EASY to browser through.
- I think I could save about a dozen (or at least half a dozen) columns by moving them to data.
- Basically, anything that (1) doesn't have an associated index, (2) doesn't need to be updated for anyone but the current user.

For now, I've moved 'mod_prefs' to data['modset'], and 'secret_question/secret_answer' to an array called secret_qa.
What I've been doing is: enter this into my new Upgrade script (not yet committed), where Wedge will get the existing entries, then convert them to data variables, and then remove the columns when it's done.

I guess it works fine if you're on a current install of Wedge: the upgrade script will pick it up, and then upgrade your table automatically.
But what if you're installing a new version..? The upgrade script won't run, because the database is in the new format, and it doesn't need to run. So... What to do?

(a) Leave the database with all these extra fields, because it's only annoying when browsing through phpMyAdmin, but 10 less fields won't make a big difference in either convenience or performance. (I'm not saying it won't; I'm giving you an opportunity to say whether you think it will.)

(b) Leave the database as is, at Wedge install time. Also set the database version to zero, basically... And let Wedge process it. But this means the import process needs to be done immediately after installing. Ouch... (Because, otherwise, Wedge will then start the upgrade process, and then delete the columns after it's gotten zero results on secret_* etc.)

(c) Add some code in the import script to make it possible to easily import an member field into the members.data field.

What do you think, guys..? (Especially Thorsten!)
Re: Importing member fields into members.data?
« Reply #1, on February 17th, 2014, 04:17 PM »
Okay, because waiting was unbearable, I went for (c) by myself...
I wrote a <code> script (which happens after <presql> and before <query>, just what I needed) that imports elements from the table and then moves them manually to data. I removed 'data' from the query section, of course.

I'll now be working on importing as many elements as possible, i.e. anything that's not used in an index or modified on the fly for other members...
Posted: February 17th, 2014, 11:22 AM

There's just one thing I don't know... If I have 10.000 members or something, how exactly am I supposed to split this query..?!
Or do I just rely on the fact that most of the members won't use the features I'm moving to data anyway..? (And thus, I can just make a query that skips members who use neither secret_question, nor message_labels, not mod_prefs, etc...)
Posted: February 17th, 2014, 11:29 AM

I really could use your help, @TE!

TE

  • Posts: 286
Re: Importing member fields into members.data?
« Reply #2, on February 17th, 2014, 05:27 PM »
Sorry, I'm currently busy (As always)..

<query> is designed to automatically LIMIT the sql query, default is IIRC 500 at a time. <Presql> is used to prepare tables for import, e.g. a TRUNCATE.
<preparsecode> is used to modify the collected data from Query, and put the modified data in a field
example from the WBB importer, the body field will  cleaned via the wbb_replace_bbc(); function.
Code: [Select]
<preparsecode>
$row['body'] = wbb_replace_bbc($row['body']);
</preparsecode>
<query>
SELECT
pmID AS id_pm, userID AS id_member_from, '0' AS deleted_by_sender,
time AS msgtime, username from_name, SUBSTRING(subject, 1, 255) AS subject,
SUBSTRING(message, 1, 65534) AS body
FROM {$from_prefix}{$wcf_prefix}pm;
</query>

If you need to collect data from one table and put it in another table, you might check the part below
Code: [Select]
<title>Importing poll choices</title>
in the MyBB importer.
Thorsten "TE" Eurich - Former SMF Developer & Converters Guru

Nao

  • Dadman with a boy
  • Posts: 16,013
Re: Importing member fields into members.data?
« Reply #3, on February 17th, 2014, 06:23 PM »
Oh... Good, then! I'd seen this preparsecode thing, but couldn't make sense out of it. Looks like it's just what I needed, thanks!
I'll get to work now...
Posted: February 17th, 2014, 06:18 PM

So, would this work..?

Code: [Select]
<preparsecode>
// We're going to import some of SMF's member settings into the serialized data array.
$data = array(
'message_labels' => $row['message_labels'],
'modset' => $row['mod_prefs'],
);
if (!empty($row['secret_question']))
$data['secret'] => array(
$row['secret_question'], $row['secret_answer']
);

$row['data'] = serialize($data);
</preparsecode>

I just wrote that, of course, haven't tested anything. Hopefully, the importer will catch that $row['data'] assignment and replace the existing value..?

TE

  • Posts: 286
Re: Importing member fields into members.data?
« Reply #4, on February 17th, 2014, 08:15 PM »
yep, looking good in general but you should use an unset at the end, unset the $data + all those rows that exist in the source system (SMF) but not in Wedge.
Code: [Select]
unset($data, $row['message_labels'], $row['mod_prefs']...)

Nao

  • Dadman with a boy
  • Posts: 16,013
Re: Importing member fields into members.data?
« Reply #5, on February 18th, 2014, 12:10 PM »
Good suggestion, thanks ;)

I'm fighting hard on this feature, but I have no idea if I'll end up doing it... :^^;:
For instance, secret_question and secret_answer indeed have no associated index, so turning them into sub-variables isn't a problem, but they still required that I add over a dozen lines of code just for them in Profile-Modify.php, because the original code is dependent upon the fact that only member fields can be modified through it... Ah, well...