Dev Blog: Interaction, Admins and Vehicle Skins

[Doctor Internet]

So, my specialist topic, database design and other stuff.

Current State of the Databases
So, starting where we were when I started, the databases were not in a good shape. Tables used a mix of latin1, utf8mb3 and utf8mb4 encodings, and one table still used the MyISAM engine. This sounds confusing, so lets break it down.

Character encodings are how letters are stored on a hard disk, as a series of "on" or "off" bits:

latin1 is the oldest, storing each character as 8 bits. Using this, we can store regular English characters, numbers, and some symbols, so we can store most of Danish, Irish, Italian and many other European languages.

utf8mb3 is newer, following the Unicode Standard, from 8 bits per letter, up to 24 bits per letter. This means we can store other languages, such as Greek, Hebrew and Indonesian. However, this means that some things like Emoji, Symbols and some other languages are not supported. This encoding is also deprecated, meaning that it may be removed in the future.

Finally, utf8mb4 stores letters from 8 bits to 32 bits, and supports all of the Unicode standard.

So, why do we need Emoji and Ancient Greek Numbers in our DB?
Without their support, someone who put one in their Steam name could break lots of things, such as not having their data saved, or logs which included them failing to save. An admin who put an emoji in a ban reason could have the ban fail to save.

Overall, lots of places we can fail.

Next, what is a storage engine? Again, like an encoding, it's how the database stores its data on disk. MyISAM is old, and like utf8mb3, might be removed with any upgrade; also lacking features we use, such foreign keys, and is slower than its replacement, InnoDB.


Not great, I'm sure we'll agree.
Combined onto this however, is the data that's stored.
For example, did you know that blacklists have two places where the admin's name can be stored?
Or that both clans and organisations exist in the database.

Migrations
So, how do we sort this out, while also making sure every database we have is in sync with the data it stores, and the formats it uses?

Before now, we would copy the structure of the database, and send that file to anyone who needed to make a new database, but those often got out of sync.

To rectify this, we created our migrations system. Loosely based on Laravels's migrations, these are files which run once, in a defined order, and run changes on the database. This means that instead of having to write code to ensure that the database is up to date, a developer only needs to write a new file and all the backend is handled for them.

For example, when @Arny was updating the chatbox, he had to increase the maximum size of a logging column. Instead of having to write all the code to manage that, it was a <20 line file (which could have been smaller too).

Code:

cityrp.migrations.register({
up = function(done)
mysql:RawQuery([[
ALTER TABLE
logs
    MODIFY
value1 VARCHAR(4096) NULL;
]], done)
end,
down = function(done)
mysql:RawQuery([[
ALTER TABLE
logs
    MODIFY
value1 VARCHAR(255) NULL;
]], done)
end
})

Cleanup
With that out of the way, what are we going to do? Aside from changing from deprecated engines and encodings, we are also doing a number of things to cleanup the data we store and making it easier to write code for.

For the main data table, we'll be changing how rows are keyed, for example. Currently, players are stored by their key field, which is calculated from their SteamID, but we also store both the SteamID and CommunityID, and require them to be unique (so a single SteamID can only have a single row). However, this also breaks bots, as their SteamID is "BOT", but their CommunityID is different for each bot.

Overall, we're storing 3 unique IDs, all of which are based on each-other and can be converted, but the one we're using right now breaks bots. Fixing this, we'll use the CommunityID as the key, store the SteamID while it's used in other tables, and drop the key column.

With that one change, we reduce our data storage by 1/3rd for those columns, and fix the bots issue, which makes testing easier.

Along with that, there's a number of columns which aren't used anymore. Players can have access and flags, which is how the old admin and donator systems worked, but haven't been used in years. Players used to be able to type in their own clans (mine was a mix of 3 clans), but since the clan system got added, that hasn't been used. Loading screen music state used to be stored, but since loading screen music crashes people's audio, it was removed, but the flag never was.

Conclusion
I know this isn't the most typically interesting topic, but it is a topic I love to bore people with, as by looking at what hasn't changed, you can really look back at the history of the code changes over time.