If you've read the
website blog on smack talk you will know that I've just “enabled” emoji's on the website. Stupidly, I thought this would be an easy task as I figured it's just a matter of getting the database (MariaDB) to accept the code for each emoji. It kind of is that simple, but a cascading sequence of events happened that dramatically increased the amount of work needed to get there. I've already done a blog title “I knew an old woman that swallowed a fly” otherwise I'd have used that here.
OK, so what you need to do is have the database and each table allow unicode which I'm 100% sure I'd already done. It's quite possible that I did, but emoji's have become a thing since then and they require a specific unicode. In this case UTF8MB4. Not a big deal, just write a SQL script to change the database encoding then iterate through all the tables changing them.
It failed on about the third or fourth table. I've significantly cut down the number of tables post the LEMP conversion, but there are still 51 left. The reason it failed is that the primary key was too big. Apparently the max size of the key is 767 which is 768 bytes. This is not something Neville told me #blameneville. Obviously converting from the latin character set to UTF-8 means that each letter takes up more storage space. I'm guessing 4x the amount given utf8mb4 is a 4 byte UTF-8 encoding. I have some tables that have a 255 character varchar field as the key - apparently this is a bad thing to do anyway as it creates a huge index which I totally get, but didn't .... #blameneville. These 255 character fields are too big to be keys with the new encoding.
Right, so I could have cut the corner here and just converted tables that were going to store emojis rather than all the tables. But if a job's worth doing ... you really should get someone else to do it for you. Next step then, figure out which tables had inappropriate keys. Some of them were pretty easy - I could just assign an auto number as they key and move on. Unfortunately the biggest issue was the email field that I used as a key for the folks that are registered and to link to the race game and playlists (the latter now hidden, but still exists as I moved RagBox to personal use only).
Sidetrack for a minute. I made the email field 255 characters long because an email can be that long. Actually, I think it can be longer. Can you imagine though if your email address was 255 characters long and having to type it in every time you wanted to register or logon with something.
Let's get back on track - data conversion. The fix is to assign each person a unique ID, then to add that field to each of the tables that was linked via the email address. Iterate through the table and populate that new field with the ID associated with each email. Then make that field required and part of the key (as it would have other bits that make up the key such as the competition number). Then delete the email field as it's no longer needed in the referencing tables.
The structure's now in place and we can accept emojis. Only we can't because every page that puts data into the tables is trying to put an email address in an email field that no longer exists and is not populating a required ID field. This is for things like registering with the race game, changing registration, making selections, editing selections, becoming a member, changing your membership, password resets blah, blah, blah. It's more than you think.
Right, so just change those pages and we're up and running. Err, no. The whole logon process that I created recently uses email addresses as the ID and I changed it to the new ID (which has logged everybody out). This bit is actually quite funny. I knew I'd be logged out, but when I tried to log back in, I was locked out because it couldn't validate me for some of the back end stuff that I need to do to run the scripts to make the changes. So I had to un-code the logon, to push the changes, to then re-enable the logon so I could log back in.
We're done now though and the changes are in. I 💗 data conversion. There's something about just figuring out data structures and what needs to be done that I find really calming. I think it's the sense of putting order to something. Parts of the back end are still broken. I need to fix some stuff with the music, but it shouldn't be too difficult. I just need to finish the mobile friendly responsive pages for the front end first. The joy of hobbies.
