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

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?