Migrating OSQA xml backup to new Q2A installation

Question

Hi Q2A community,

I have a backup of an OSQA site that I used to run back in 2011. The backup is in XML format with a structure of files, e.g:

• [xsd/]
• badges.xml
• CloseReasons.xml
• Comments2Votes.xml
• FlatPages.xml
• Messages.xml
• MessageTypes.xml
• ModeratorMessages.xml
• ...
• VoteTypes.xml

I'm planning to write some code that will loop through the Posts.xml file and restore all the data there, inserting each post as a single-line entry into qa_posts, with structure something along the line of:

(X, Q, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 1, NULL, 2090122648, NULL, NULL, 0, 0, NULL, NULL, 1, NULL, [IPADDRESS], NULL, NULL, 0, 0, 0, [IPADDRESS], 1, 21553800000, 0, NULL, [TODAYSDATE], NULL, NULL, [TITLE], [CONTENT], [Tags seperated by commas])

The userID will be a generic administrator user with a name such as 'Community' or 'Robot' or something like that.

I could probably extend the script to also allow for users, but since it's been about a year since the site was live, I doubt many (any?) of the users will magically jump on the site and try to login again.

Before I delve into this and start writing code, has anyone already achieved what I'm about to attempt? Any pointers? Am I taking the right approach? I'd rather re-use some other code if someone has already done this succesfully.

Cheers.

Answer 1

Solved this.

Script is available

Here's the script I wrote in case anyone else is trying to do this:

https://github.com/jamesspittal/osqa-q2a

Here's how you'd use it:

1. Ensure that Q2A is setup already with a 'standard default' configuration, i.e: you've already setup a super user account and logged in succesfully but don't have any posts yet.
2. Move all your OSQA *.xml files into a directory, e.g: ~/import-osqa/
3. Copy the script into ~/import-osqa/import-osqa.php
4. Edit the script to uncomment the 'print $sql' statements (to ensure that all SQL insert statements are printed to STDOUT)
5. Run `php import-osqa.php > import_sql_users_and_posts.sql`
6. Run `mysql --force -u DB_USERNAME -p DATABASE_NAME < import_sql_users_and_posts.sql`
7. Warning: I've included --force in the previous step because my script as it currently stands seem to throw some primary key errors. It's not a perfect solution, but it works.
8. View your Q2A site, all the users and questions should now appear in the home page. If not, something went wrong. If yes, you're good to go.
9. Login as the admin account and run all the database repair tools, recount posts, etc.
10. Done!

Hope that helps.

Comments

I tried that dropbox link but it didn't work. Can you please post alternate URL? I need that migration script.

---

@thameema: I've edited/updated the answer to include the link to my github project for the script: https://github.com/jamesspittal/osqa-q2a - hope that helps! Good luck.

---

I've tried this, but apparently I'm missing something. I don't get a "posts.xml" when exporting from OSQA, I get "Nodes.xml" which fails terribly. I'm trying this on local installs with throwaway data, so that I can get a working method for importing. I'm a moderator on jailbreakqa.com, arguably the busiest site running OSQA, and we're tired of its limitations. The site owner (saurik, a name you'll recognize if you're into jailbreaking iPhones) doesn't have the time to put into it, so I'm trying to stumble my way into a solution. ;P

---

Strange. I don't recall a "Nodes.xml" when backing up all of my OSQA data, but it was a long time ago. Can you share what the output of Nodes.xml looks like? Perhaps e-mail might be better if you don't want to share your database publicly.

---

Also, when I wrote this script, I think the Q2A database may have looked different to how it looks now - so https://github.com/jamesspittal/osqa-q2a probably needs to be updated/modified.

---

Nodes.xml looks like this: https://ghostbin.com/paste/kry32

Thanks for looking at this, really appreciate it. :)

---

Thanks for sharing that. Had a look, it looks similar to `Posts.xml` but definitely different. Roughly - you'd need to: Loop through each 'Node', grab <id>, check <type> to see whether it is `question` or `answer` and copy across body, title, date, etc - and then translate that into the new format into `qa_posts`. I'd help but don't have any spare time at the moment, unfortunately.

---

Thanks. I'm just wondering why I get a different xml file than you do. Did you use the admin panel's export function? I'm looking through the different versions of exporter.py, hoping that I could simplify the process by using a different OSQA build, but I'm not seeing anything that outputs posts.xml or any of the other xml files you listed in your original post.

---

It's a good question - not totally sure. It would have been a couple years back (I'd estimate ~2011) when I did the export, so maybe the exporter.py in OSQA changed around that time?