Project Scope: Migrate custom database to WordPress
Together, we’ve carried out more than 100 site migrations. Everything from WordPress to WordPress, to Multisite to Multisite, and even Multisite to single-site.. These migrations were all trumped by our client Shift.ms’ project.
They presented us with a taxing problem. First of all, they had a custom CMS that needed to be migrated to WordPress. They had a new WordPress/BuddyPress installation that they wanted the site migrated to. All good, you might think, but the new WordPress installation had data that the client wanted to keep, and, as you might have guessed, the database schema and infrastructure were completely different to WordPress.
Then the client told us that they had previously hired two WordPress experts. They’d taken one look at the project and run for the hills.
We decided that we couldn’t turn it down.
We scratched our heads and came up with a list of issues:
- Migrating all content from custom database including structure conversion. The database structure for the old site didn’t match up with the WordPress database structure. We knew that we’d have to map out the fields from the old database to match the corresponding fields in WordPress
- Re-index IDs of users, posts, and comments. WordPress has a relational database structure, so posting authors and commenting authors are based on user_id fields in the users table. The users, posts and comments from the old site needed new ids as they would already be taken by content in the new database.
- Migrate avatar custom built system to BuddyPress avatar system. The old database structure stored avatars in the user table under a column image. BuddyPress uses a folder structure that is located in uploads/avatar/*USER_ID*/USER_IMAGE-bpfull.jpg and uploads/avatar/*USER_ID*/USER_IMAGE-bpthumb.jpg.
- Fix username issues with special characters. The user_login column in the users table from the old database had a special character in the column that needed to be removed. They would also need to be removed in order for pretty URL’s to work properly.
- Organizing custom functionality. The old database had custom functionality like “happenings”, “videos”, “tips”, and “nervecentre”. We needed to figure out a plan to get these working in WordPress.
We did our best to identify all of the problems we thought we’d run into. We wanted the project to run as amoothly as possible.
Our advice to anyone that is taking on a custom database structure to WordPress migration: plan, plan, plan.
Before we even jumped into the first line of code we were talking solutions.
Mapping the Database Structure
We started by mapping out what we could of the old database structure. We went through each column to see where it would best fit in the WordPress database structure. Once it was mapped out we started writing the SQL queries and tested them on a development server. Once we knew that the query operated correctly, we saved it in a master query file.
Re-indexing the IDs
This was a tedious process of creating a new column in the tables called old_id. This old_id column stored the original id for reference points. This allowed us to have the new id that was created during import of the record while still keeping a reference to the old one to let us re-index all relations to that record.
For example, user A has an ID of 3 but the old id was 2081. The id 2081 is now stored in the old_id column. This lets us find any comments or posts that relate to the 2081 user and then change the id to the new id of 3. We had queries that would find the matches and then correct the id.
Migrating Avatars from the Old System
This was a challenge but very rewarding because of the great BuddyPress know-how we got from it. The old database stored the avatar in the database while BuddyPress stores the file in the file system under the id of the user. We had to do some custom scripting to get this to work. We used the mkdir() function to loop through the database of users that previously had images and create a directory named after their id. After we got the folder structure in place, we had to move the images from the old server and place them in a temporary holding area on our server. Once the files were moved, we could use the copy() php function to the find the image in the temporary file holding area, use the timthumb.php script to re-size the image, then move the resized image to the proper id folder and append the -bpfull or -bpthumb to the name of the file. This worked beautifully and was a huge time saver!
Usernames with Custom Character
Usernames from the old database had special characters which we couldn’t use in WordPress.We did a replace statement to replace the special characters. This is where we thought we had to stop with the special characters but later found out that BuddyPress uses the nicename column for URL creation. When we left special character there, profiles wouldn’t load. We had to run the same replace statement on the nicename column and this fixed the problem.
Organizing Custom Functionality
Custom functionality wasn’t too difficult. We needed to give these records custom post types that could then be pulled into templates we created to display the data. Pretty straightforward!
What We Learned
This project was a very humbling experience. Just when you think you know it all about WordPress, a project falls into your lap to show you how much there is to know.
Key points that we learned through this project:
- Overall, we have experience and the know-how to migrate custom database structure into the WordPress environment.
- Planning is essential to migration projects but even more so when it comes to custom database migrations to WordPress.
- Take time to re-work the database schema before touching the code. It’s kind of like creating a mockup before developing a website.
- How to migrate avatars from a database driven system to a file structure based system.
Migrating a custom database to WordPress can be a difficult process. We ran into a lot of problems, but we learned a lot too. Hopefully this case study has given you some useful tidbits if you’re planning to do one yourself. If you need any help, or what us to do it for you, get in touch!