A JISC-funded Managing Research Data project

Posts tagged SQL

Initially, the Orbital project has been developed around MongoDB. CodeIgniter uses SQL as its built-in sessions storing database but MongoDB has been used for everything else. In terms of the creation and modification of projects, this has been no problem in Mongo. However, when a project is deleted it also needs to delete any permissions to the project and any related files, which is where Mongo gets messy. Each collection needs to be individually accessed and the items relating to the project found and deleted, so no collections of data are related in any way, unlike in SQL.

A decision has been made to switch from MongoDB to SQL for the handling of project details, permissions and related files as the relational database functionality it provides would mean a more structured set of data which would be much easier to work with. This decision has been made because although Mongo works perfectly well and arguably enables much more functionality to be deployed on the tables later on, the programming would be increased and become less structured as each table would need checking for items related to projects. In SQL, this would no longer be a problem. As SQL is relational, if a project is deleted, any related items such as permissions and files are guaranteed to be deleted. The permissions a user has for each project as well as the files associated with a project also need to be linked to the project collection/database.

Mongo will still be used across the rest of the project for the actual research data sets, simply not the metadata.

As Orbital has a modular development, the switch to SQL requires little code to be changed. This is because only the models in Orbital Core connect to the database. Therefore, only these files will require changes to interface with SQL.

One of the cool things about Orbital from my point of view is that I’m not just responsible for putting together a bit of software that runs on a web server, but also for designing the reference platform which you run those bits of software on.

At this point I could digress into discussing exactly what boxes we’re running Orbital on top of, but that doesn’t really matter. What is more interesting is how the various servers click together into building the complete Orbital platform, and how those servers can help us scale and provide a resilient service.

You’re probably used to thinking of most web applications like this:

A 'traditional' server model

It’s simple. You install what you need to run your application on a server, hook it up to the internet, and off you go. Everything is contained on a single box which gives you epic simplicity benefits and is often a lot more cost efficient, but you lose scalability. If one day your application has a traffic spike your Serv-O-Matic 100 may not be able to cope. The solution is to make your server bigger!

Throw more power at it!

This is all well and good, until you start to factor in resiliency as well. Your Serv-O-Matic 500 may be sporting 16 processor cores and 96GB of RAM, but it’s only doing it’s job until the OS decides it’s going to fall over, or your network card gives up, or somebody knocks the Big Red Switch.