We use an open source web application called osTicket to manage our e-mails for Zenutech. Unfortunately the osTicket project is controlled by a hosting company that does not accept patches. However, Travis Cline (aka tcline) recently stepped up and started a new branch of the project called osTicket Community Edition. The project is managed using Trac which allows you to browse the Subversion repository and see recent changes to the codebase. It’s good to see this project in action.
Currently there is no database upgrade script from osTicket 1.2.5 (the version we were running) to the latest release of osTicket CE. I was able to write a short SQL script to perform the upgrade, by doing the following:
Determine the schema changes
The first thing to do is figure out what has changed about the database. In this case, we need to uncover what has changed from version 1.2.5 to CE. To do this, I downloaded the latest version of osTicket CE and installed it as a new install. Don’t overwrite your existing install. Then I logged-in to phpMyAdmin (my web-based MySQL manager) and exported the structure of my old ‘osticket’ database and the structure of the new ‘ostcv’ database as SQL scripts. Then I ran the two exported SQL scripts through CSDiff (my favorite diff program) and it showed me exactly what has changed from version 1.2.5 to CE.
Writing the SQL upgrade script
Now the easy part. Make sure you are using the old database as the base revision in your diff program. CSDiff is by far the best diff program I’ve used, and I’ve used quite a few (Visual SourceSafe, Perforce, diff command in Linux, etc). CSDiff is very good at figuring out what has changed and only highlighting those key parts. It shows you what has been added in blue and what has been removed or changed in red. In this case with osTicket, there was only one new table and a few columns in other tables showing up in blue. There was a bit of red, but not much.
So, I simply created a new SQL script called upgrade_from_1.2.5.sql, copied the SQL for the table that was showing up in blue and wrote a few ALTER TABLE foo ADD COLUMN statements for the new columns. And there it was, a simple little upgrade script that can be run against my old osTicket 1.2.5 database upgrading it to osTicket CE.
Of course, this case was really simple because there was very little red showing up in CSDiff. If there were a lot of red (tables removed, columns removed, data type changes, etc.) some data massaging may also be necessary to upgrade the database. Usually this is done with a simple server-side script (PHP, Perl, Python, etc).