Path: A new stack of PHP, Yii and Oracle

Path has been through a variety of environments over its two-and-a-bit-year life. When it was a student project, it started off on servers run by EUSA. It then moved to IS’s webhosting environment. Now it’s on a proper supported LAMP infrastructure, with three environments and the sort of features you might have come to expect. This is fantastic news for Path’s future, but it hasn’t been the steadiest journey.

Adding the M to MVC

Whilst moving between environments, we’ve also changed our stack several times. The constant factor has been that we’ve used PHP as a programming language, but near everything else has changed since July 2012. On EUSA, we used a homebrewed framework which was akin to MVC, but interacting much more directly with SQL rather than using a “model” tier (the entire database interaction was handled in around 650 lines). This worked fine at the time but wasn’t maintainable, and certainly wasn’t up to the standards held by the University.

So, when Path started to become a part of IS’s portfolio we rewrote it with Yii, a framework already used by a few applications including the Undergraduate prospectus. We also used the templating engine Twig to make our views neater. All in all, we were happy with the way we transferred to Yii. The code was neater, more maintainable and generally of a higher standard. It was also here that we moved to IS’s webhosting, and even got a test environment for the first time.

From MySQL to Our SQL

In 2013, we started the “Path Enhancements” project (SCE003, project code fans) which aimed primarily to:

• Move Path onto the corporate infrastructure.
• Create an automated feed to update Path’s core data from central sources.
• Expand Path to a wider audience, introducing it to more schools.

A key part of this was switching from the previous MySQL database to a new Oracle database, which was a big job but seemed a logical decision since Oracle was the standard database elsewhere in the University and would allow us to use database links to pull data out of other services for our update process.

This gave us the following stack to develop with:

We were now working with familiar technology, and some combinations that had come up before. The Yii framework had been used a few times with PHP in the University, and we’d used the PHP/Oracle combination in the Course Timetable Browser. What we weren’t prepared for, however, was using Yii with Oracle.

Whilst Yii technically supports Oracle databases, it doesn’t work as smoothly as you might hope. In particular for Path, it doesn’t work as smoothly as it does with MySQL. Here are some of the issues we came across and how we handled them:

Yii effectively forces lower-case column names by wrapping each in quotation marks, but Oracle expects column names in upper-case

We created a new OciSchema extension object that only wraps columns that aren’t reserved words (e.g. “public”, “session”, “year”) and, whenever wrapping, forces them into upper-case.

ActiveRecord is case-sensitive, but a lot of our code referred to lower-case properties (particularly in Twig)

We created a new ActiveRecord extension object that looks for both the requested property and its upper-case counterpart whenever getting or setting a value.

PHP raises notices whenever it references fields which are null in Oracle

For every nullable field, we had to add a get* method to the relevant model, which attempted to return the value. It still output nulls as expected, but PHP didn’t cause a fuss because of how Yii trusts get* methods more completely.

There were large performance issues, particularly around LOBs. Some of these are commonly seen in a PHP and Oracle combination, but some were more related to Yii’s specifics.

We stopped the application from reading LOB objects until they were needed. Technically, this involved using OCI-Lob’s read method rather than using the OCI_RETURN_LOBS of the oci_fetch* functions.

We also added plenty of caching around the site to lighten the load on the database, and rewrote some queries to stop using ActiveRecord so we had more control over the SQL being executed.

Whilst it’s easy to go back and list what we did now, these issues took a lot of debugging, and a lot of experimenting with solutions. In particular, the performance issues were very hard to narrow down and even harder to find suitable solutions for. It involved a lot of digging into Yii’s framework, and breaking down its complex chains of functions to find the bottleneck.

Ultimately, I think we learned from this project that PHP and Oracle don’t always play nice together, particularly with Yii, and that future developments should strongly consider this before getting too involved. By the time we realised the scale of the problems, we were too far down the rabbit-hole to consider alternatives and so had to find application-level solutions. In the future, more research needs to be done on what causes the sort of performance issues we saw and how they should be resolved. Greater consideration should be made too to investigate whether other PHP frameworks have these same issues, or could be used instead. Down the line, Yii2 may work better with Oracle too.

Despite all the hard work, it is great to now have Path on a proper robust infrastructure. It has been handling load suitably and is already receiving thousands of pageviews a day. More schools and more students are able to access their course information online and make wiser choices in their degrees. All in all, the project has been a success and achieved its goals, and we’ve learnt a lot on the way.