Creating an Online Leaderboard in APEX

In my spare time I like to play around with creating video games in technologies other than Oracle (!).  I recently created a game for an online community that I participate in and I was asked whether I could create a leader board to go with it.  I had never done this before, so I had to approach it the same way as I would any development problem and figure out what tools I had available to me and whether there was anything that I could re-use to save time.

The problem

I need to allow players all over the world to play my video game and log their scores somewhere.  I also need to be able to report on these scores and make the leader board visible to anyone.

Here’s a screenshot from an example game – An Explorer skinned flappy bird rip-off:

The answer:

APEX! I use APEX every day, so it’s always the first place I go when trying to figure out a solution to a problem, and it contained all the tools I needed to create my solution.  If I could create a web service to accept a username and a score, and store this in an Oracle table, it would be easy to then create the leader board in APEX.

How to create the leader board

Step 1:

Get hold of a database.  Oracle might seem like overkill for hosting something as small as this, but the sandbox environment at apex.oracle.com is perfect for temporary solutions and investigations like this.  It also takes minutes to provision a workspace and I find it more reliable than some cheap hosting companies!

Step 2:

Create a table to hold the scores.  This should be straight forward – I went with three columns: PLAYER, SCORE and DATE_LOGGED.

Step 3:

Create a web service to insert a score.  The “RESTful Web Services” section of the APEX builder is really intuitive and allows me to create a POST web service that accepts 2 parameters – the player’s name and their recorded score.  We can then use these parameters in an insert statement to log the score in the database.

I’m not going to go into detail about how to create a web service here, but if you get stuck then check out this series of blogs from my colleague Colin.  They are based around the APEX 5.1 web services, but the concepts explained here are still the same: http://www.explorer.uk.com/web-services-part-1/

Step 4:

Create the high score screen.  Now that we have our data in an Oracle database, we can use our APEX skills to build a fancy high-score table.  Here’s a simple classic report example I made in a couple of minutes:

Step 5:

Call the web service from your video game.

There are many platforms available to create games.  I use Unity3D, which utilises C# scripts and allows me to easily call an external web service from the game code. It works in the same way as APEX, with lots of re-useable items and functionality, but with C# instead of PL/SQL.

I implemented a system where the player can enter a name in a text box each time they die, and submit the score if they like. The score and name are then passed over the web service to the Oracle database.  Due to the frustrating nature of the game, I also included a mechanism to allow the user to skip score entry and immediately try again J

Done!

And that’s it, you have now added multiplayer to your video game using an Oracle database and an APEX application.

Further Considerations

I ran into an issue when my game had been online for a few hours as one of the players decided to do some digging and found the web service call.  As I had hastily thrown together the solution, he was able to reproduce the call and alter the score.  Luckily, he told me and I was able to quickly fix the issue by encrypting the data inside my game code before sending over the web service.  Once the encrypted score and player name were on the Oracle side, I decrypted them and stored them in the database.

If you want to see this in action, here is a link to the flappy bird clone and leader board: https://csykes-dev.itch.io/explorer-bird

While this blog isn’t particularly “work-related” hopefully it shows that the skills we use every day can be used to create solutions and fix gaps in virtually any other technology.  Between low code APEX front-ends, web service integration and the Oracle Database, we can do pretty much anything.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.