« Stuff The Internet Says On Scalability For February 5th, 2016 | Main | Sponsored Post: Netflix, Macmillan Learning, Aerospike, TrueSight Pulse, LaunchDarkly, Robinhood, StatusPage.io, Redis Labs, InMemory.Net, VividCortex, MemSQL, Scalyr, AiScaler, AppDynamics, ManageEngine, Site24x7 »
Wednesday
Feb032016

A Case Study: WordPress Migration for Shift.ms

The case study presented involves a migration from custom database to WordPress. The company with the task is Valet and it has a vast portfolio of previously done jobs that included shifts from database to WordPress, multisite-to-multisite, and multisite to single site among others. The client is Shift.ms.

Problem

The client, Shift.ms, presented a taxing problem to the team. Shift.ms had a custom database that they needed migrated to WordPress. They had installed a WordPress/BuddyPress and wanted their data moved into this new installation. All this may seem rather simple. However, there was one problem; the client had some data in the newly installed WordPress that they intended to keep.

Challenges

The main problem was that the schema for the database and that of WordPress are very different in infrastructure. The following issues arose in an effort to deal with the problem:

  • The option to migrate the content as a whole from the custom database and include the structure conversion. The structure for the WordPress database did not match up with the structure for the database in the old site. The option was to map out fields found in the old database in order to match it to the fields found in the matching new WordPress installation.
  • To re-index the IDs of the users, comments and posts. WordPress normally contains a relational database structure. Therefore, posting commenting authors and posting authors are normally based on the user’s ID fields found in the user’s table. Users, posts, as well as comments in the old site would require a set of new IDs for they would already be found in the new database. 
  • Migrate the avatar built-in system and change it to the BuddyPress avatar system. The team found that the previous database structure used to store avatars in a user table under the column image. However, BuddyPress makes use of a folder structure located in the uploads/avatar/*USER_ID*/USER_IMAGE-bpthumb.jpg. and uploads/avatar/*USER_ID*/USER_IMAGE-BPfull.jpg
  • The need to fix any username issues using special characters. The user login column for the old database user table had been input with a special character found in the column that required removal. Additionally, the character needed removal so that the URL’s would function properly.
  • Organize custom functionality. In the old database, there was custom functionality for videos, happenings, nerve centre and tips. Therefore, the team needed to find out how to get this functionality to work with WordPress.

The team did their best to find all the problems that the migration would bring. This would enable the project to run as smoothly as possible. The idea that the team used when planning a database to WordPress migration was to plan and plan some more. 

Solutions

Mapping Out the Database Structure

The initial plan the team had was to map out what they could do with the previous database structure. The team considered each column in the old database to find out how well it would fit into the structure of the new WordPress database. Once the structure was mapped out, the team began to write the SQL queries and subsequently tested them on the server under development. Once the team found that the query operated in the right way, they saved the query in a master query file.

Re-indexing user IDs

The process of re-indexing was tedious as it involved creation of a new column that would take the place of the tables listed as old-ID. The old ID column was stored in the original ID for the purposes of reference. The process allowed the team to have that new ID that had been created during the import of the records and still keep a reference of the old ID and allow them to re-index all relations to the old record.

For instance, if user A has the ID number 3 but the old ID used to be 2018, the old ID2018 is stored in the old ID column. This allowed the team to find any comments or posts that would be related to the old ID 2018. Once the posts and comments are found, they could then be changed to the new ID number 3. There were queries the team created that would help find matches to the correct ID and in the process migrate the content.

Migrating Avatar from old system

This was a challenge for the team. However, the team got some good experience on the BuddyPress avatar system. For the old system, the avatar is stored in the database. However, for BuddyPress, the file is stored in a file system located in the ID of the user. The team did some custom scripting to help get this work done. The team made use of the mkdir() function to help loop through the database of users who had previously saved images in the old server so that they may store them in a holding area in the team’s server. It was necessary for the database support services.

Once the files were moved, the copy() php function was used to find the image in the temporary holding area and using the timthumb.php script to resize the image, they could then move the image that was resized to the correct IDfolder and then append the –bpfull or –bpthumb onto the file name. This procedure worked successfully and helped save time.

Usernames and custom characters

The usernames found in the old database contained special characters that could not be used in WordPress. There was need to replace the special characters with a statement. That was where the team decided to stop with special characters and use BuddyPress, which uses a nice name column specifically for the URL creation. If the team left the special characters there, the profiles would have failed to load. There was need to replace the nice name column to fix the problem.

How the Team Organized the Custom Functionality

Custom functionality was easy for the team. The procedure was straightforward as all they had to do was give records custom post types and create templates that the data could be displayed in.

Lessons

  • Planning is very important to the success of migration projects even regarding database to WordPress migration
  • It takes some time to rework a database schema prior to touching the code. It is similar to creation of a mock up prior to website development

·         The option to migrate the content as a whole from the custom database and include the structure conversion. The structure for the WordPress database did not match up with the structure for the database in the old site. The option was to map out fields found in the old database in order to match it to the fields found in the matching new WordPress installation. 

Reader Comments (2)

What happened to this site? I thought it was all about high scalability?

February 3, 2016 | Unregistered CommenterFoobar

I understand where you are coming from. The larger point of the site is about building stuff so I think this post qualifies. And if people make the effort to contribute a post I go out of my way to publish it if I can.

February 3, 2016 | Registered CommenterTodd Hoff

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>