One of the things that you may have noticed recently on the Elite: Dangerous posts on this site is the little statistics panels at the bottom of posts – this post is about those:
They are generated by a custom extension to the WordPress theme that I’m currently using, backed by a MySQL database. The database schema I’m using can be found here for those interested.
Trip list
The basic idea is a special page template which is used for Elite: Dangerous Exploration Trips, which pulls information primarially from the trip table, joining as needed to waypoint and system to pull out names:
This query, roughly speaking, takes all the trips, joins them to the arrival waypoints (wto.special = 'a'), all the via waypoints (v.special = 'v'), and all the system names for display. It’s worth me noting here that the dates are stored as the real date and time, not the in-game date and time. The two only differ by the year, so it’s easy to calculate the in-game time by adding 1286 years to the current date. This is done at the database level, so there’s less calculation required in PHP.
WordPress Templates
There’s also a custom category and single post template, which do nothing other than redirect to the content-elite and content-single-elite templates. Both of these do nothing additional to the default except call the elitestats template.
The elitestats template is where the magic starts appearing, and reveals how the entire system hangs on custom fields. Pretty much the entire content of the elitestats template is this:
As you can see, here we divert to either the elitetrip template or the elitesession template.
Trip posts
Trip posts are standard blog posts, but using a custom template to add generated tables of data at the bottom.
Waypoint data
Waypoints are retrieved by the following SQL, which is fairly complex.
This statement is comprised of three distinct SELECT statements, unioned together to form a single resultset. As we very specifically want the rows from different unions to be sorted next to each other, and in a specific order, we augment the resultset with an ordergroup column, which is different to each union. We expect the first two SELECTs to return a single row, so we don’t care about the order of the results within those groups, but we do care about the order in the third group, hence a second ordergroup column. The first SELECT retrieves the departure point, and the second retrieves the arrival point. Both are very similar, connecting trip to system to pull out the system name. The third select pulls out all of the waypoints and vias, ordered by the sequence in the database, displaying the waypoint numbers and the name of the commander who plotted the waypoint. The penultimate line shows a coalesce – this works because standard waypoints have a null in the special field, and vias have a ‘v’. By coalescing null to ‘v’, then checking equality to ‘v’, we catch all of the waypoints and vias, but not any other special types.
Incident data
Retrieving incidents is also fairly complex:
Firstly, we retrieve all of the incident types where they are either technical or non-technical. We left-join on the total of these incidents for this trip, and the number of incidents for this session, and hide any hidden incident types unless there’s at least one of those reported.
And that’s about it for the trip page, save for some basic SQL for incident totals, and retrieving the current location, etc.
Session posts
The basic session data is generated by this SQL query:
A quick rundown of the query is a basic select from the session table, filtered where the session ID is a passed in value, limiting the result to one row. This gives us the instantaneous values of the number of jumps made, and the current level of space madness. It also joins onto system to get the current system details, and onto waypoint and system to get the next waypoint details. Both name and coordinates are retrieved for both of these.
We also left-join on incidents, summing all incidents where the incident is non-technical (aka my fault, not my PC or network connection’s fault) by joining onto incidenttype, and grouped by session, then filtered to the correct session. There’s an optimisation to be made here, but currently there’s not that much data in the database for this to have any performance impact.
Finally, we also join onto trip to pull the jump range my ship has for this trip. This was added as I sold my Hutton mugs and refitted my ship slightly while I was at Jaques, so my jump range actually increased by over a light year.
Next, we pull the waypoint data:
This is again reasonably straight-forward (he says spotting the four joins…); we pull all the waypoints where the waypoint is either an arrival waypoint or a via waypoint, filtered to the current session (sessions and waypoints are both linked to trips, so we use the joins via trip to apply the filtering). Linking to session also gives us access to the next waypoint, allowing us to filter out any waypoints which we’ve already passed by checking the sequence numbers. Finally, we tack on system to the waypoints to pull out the name and coordinates, which is what we actually care about.
We use this data to calculate the distance remaining, simply by using Pythagoras’ Theorem:
d = \sqrt{\Delta x^2 + \Delta y^2 + \Delta z^2}… and with the distance calculated, we can get the best case jump count, by dividing distance by jump range:
j = \lceil \frac{d}{r} \rceilFinally, we pull the session incidents by incident type and the number of incidents, for both technical and non-technical incidents.
Then, we simply display all of this in a nice simple table. And that’s it!
Other stuff
There’s also a hidden web interface to allow me to manage the data stored in this secondary database, which is a very basic CRUD interface. This is slowing improving as I spend more and more time on it, including the most recent addition, which is the addition of coordinate retrieval using the EDSM API.
I made the decision of implementing it as a an expansion of the theme rather than a plugin so it would be more straight-forward to maintain, and as I know the abilities of theme customisation for custom post types – I already have a custom theme with numerous modifications here and there. It made sense to just extend it, as it’s probably just me using it – even though the code is on GitHub, I don’t expect anyone else to have any use for the code I’ve written here, it’s very specific to my own itch.
If anyone does want to use it, they’re welcome to it, but it will need at least some modification for your own purposes.