Saving MMO inventories with Redis and MySQL

Intro

0:00 Hello and welcome back to episode forty, where we're going to have each player carry a bag and fight with what they wear, and save the bags, through Redis to MySQL. Let's begin with a quick look at where we're going to be by the end of the episode.

0:15 Each player carries a bag now, and wears the sword from the kit. What they wear adds to their damage, their health and their armour, and the world can have them drink a potion from the bag, with a cooldown. The bags are saved through Redis to MySQL, a row for each stack, and the last test saves forty full bags, more rows than the saver puts in one statement, so they go in two.

0:37 On the website of the series you can find more information about the project. The code for the project is open source on GitHub, tagged at each step of each episode so that you can follow along.

Last time

0:49 Now, before we get to today's work, let's quickly go over what we did last time. The world server kept one copy of the content, which each copy of each land shares. An item became the parts it has: how many stack, where it's worn and what it adds, what using one does and what each one rolls. The content's checks refuse parts that don't go together, like a weapon that isn't held in the hand. And the server got a bag, of twenty-four slots and five worn ones, which stacks, splits, swaps and wears items, with a property test that moves items around it twenty thousand times, and checks that none is made or lost.

Today

1:25 The bag that we wrote last time is the server's, but no player carries one yet, and each player still holds the sword. So first, each player is going to carry a bag, and a new player gets the kit in it, once. What a player wears is going to add up to their stats, the damage they do, the health they can have and the armour that takes a monster's blows, and they're going to fight with the weapon in their hand. Then the world is going to let a player drink a potion from the bag, with a cooldown, so that a stack of them doesn't go in a second. The client has no message for it yet, so the tests drink them for now. And the bags have to be saved, or each player loses theirs when they log out. They're going to go into Redis with the rest of the player, when they change and not on each tick, and from there the saver writes them to MySQL, a row for each stack. A bag's old rows go and its new ones come in, so each batch of players is going to be a transaction, which happens all at once or not at all. All right, let's get started, with the bag that each player carries.

A bag each

2:23 Each player is going to carry a bag, in place of the weapon id we kept for them, and what they wear is going to count in a fight. In world dot h, the protocol's header comes in, for the places in a bag, which moving an item takes, and inventory dot h, for the bag we wrote last time. Then a struct of its own, Stats, for what a player's worn items add up to. It's worked out as what they wear changes, so that a fight doesn't add it all up for each blow. It has the damage they add to each blow, the health they add to the most a player can have, and their armour, which comes off each blow a monster lands. It keeps a pointer to the weapon in the player's main hand too, from the content, or a null pointer when the hand is empty. That way, a swing reads the weapon without looking it up in the items on each tick. In the player, the weapon's id makes way for an inventory, which is what the player carries and wears, and their stats go next to it. Then, next to walk, two functions that read a player. Max health of is the most health a player can have, which is their level's, and what they wear adds. Main hand of is the id of what they hold in their main hand, or zero, which the server sends and saves as their weapon. In the class, move item moves a player's items around, the way the inventory's move does, wearing them and taking them off, or the error says why not, in words for the player. And destroy item throws away all of what's at a place. In the private part, dress works out a player's stats again, after what they wear has changed. It's a const member function, as it reads the world's content without changing the world, apart from the player it's handed. Weapon of goes, as the stats keep the weapon now. In world dot c c, in the unnamed namespace, add takes the stats and a modifier, and adds the modifier's amount to the stat it names, with a switch: to the damage, to the health or to the armour. Stat none, and any stat that this server doesn't know, falls to the default, which adds nothing. So a stack saved by a newer server, with a stat this one doesn't know, adds nothing, and the fight goes on. Max health of is the health of the player's level, which is what we had before, and the health in their stats on top. Main hand of looks at what's worn in the main hand, and returns its item, or zero when there's nothing there. In join, we keep a reference to the content's items, as join is going to use them a few times. Over it, we write who gets the kit, and why it's once. A bag with no revision yet hasn't been anyone's, so a new player, or one from before bags, gets the kit once, which a trip through a gate doesn't give them again. So when the inventory's revision is zero, each stack in the kit is worn, if it's an item that's worn and its slot is free, and if it isn't worn, it goes in the bag. Our kit is the sword and three potions, so a new player holds the sword, and has the potions in the first slot of their bag. Each of those changes moves the revision on, so a player who joins again with the bag they had doesn't get another kit. Then dress works out their stats from what they wear, before anything reads them. A player with no health saved starts with max health of, so that the health from what they wear counts. And the weapon a player had saved goes, with the starting weapon for a player who had none, as the kit gives a new player their sword. Next, move item, which finds the player in the world's map of players. If they aren't in this world, it returns a status of not found, which says there's nobody to move it. Then it takes a reference to the player, so that what changes is the world's player and not a copy of them. The player's inventory moves the items, with the content's items, from one place to the other. And if the move worked and either place is worn, what they wear has changed, so dress works out their stats again. A move within the bag doesn't change them, so it leaves the stats as they are. It returns the status of the move, which says why not when there's a problem. Destroy item goes like move item: it finds the player, the inventory throws away what's at the place, and when that place is worn, the player is dressed again. Then dress, which keeps the weapon the player had before, and starts their stats again from nothing. For each slot that's worn, it finds the item in the content, and skips one the items file doesn't have, which stays in its slot and adds nothing. It adds each modifier of the item's equip part, and each roll that the stack has of its own, like three more damage on one blade. And when the slot is the main hand and the item is a weapon, that weapon goes in the stats. After the loop, the player's health can't be more than the most they can have, with standard min, and over it, we write why. Less to wear can mean less health to have, and a swing with a weapon that's been put away is over. So a player who's attacking, with a weapon in the stats that isn't the one they swung, stops, and stands idle. In act, a player who comes back from the dead comes back with max health of. The weapon for a swing comes from the stats, and a swing goes on with a weapon in hand and not without one, so a player with an empty hand doesn't swing. And the swing reads the weapon from the stats. In hit, each blow adds the damage in the player's stats to the weapon's damage and to the extra damage of their level. In land, a skill's healing goes up to max health of, and a skill's damage gets the damage in the stats too, like a blow of the sword. In hurt, a player who goes up a level gets max health of. And when a monster hits a player, their armour comes off the monster's damage, down to one, so each blow does at least one, however much armour they wear. What the player wears takes the blow, down to one, so a monster whose blow is weaker than the armour still does some damage. In items dot h, the item table's starting weapon goes, the first weapon in the kit, which each player held until they had a bag. And in items dot c c, parse items doesn't look for it any more. In players dot h, the progress that we save keeps a weapon, and we write what it is now: what's in their main hand, which servers from before bags read, and the website might. In players dot c c, load doesn't read the weapon, from MySQL or from Redis, as the bag says what they hold, and the skills move up a place in what Redis sends back. We still save the weapon, for those older servers. And the players' test doesn't expect a weapon from load. In server dot c c, progress of turns a player into what we save of them, the progress, which goes into Redis. It saves what's in their main hand as their weapon. And the dead come back in the middle, at full health, so a player who died is saved with max health of, which counts what they wear. And the player we send to the clients has max health of as their max health, and what's in their main hand as their weapon, so the clients draw the sword in their hand as they did. In the world fuzzer, levels dot h goes, and its players join with no weapon, as each of them gets the kit. Its check on their health is against max health of, which counts what they wear.

Testing it

9:31 Then the tests, in world test dot c c. They use the tests' gear from last time, so in an unnamed namespace, we give three of its items names: k bone is three, k cap is four and k blade is five. One makes a stack of a single item, which is how a test hands a player a blade or a cap. It sets the item, and a count of one, and returns the stack. With makes a player who's had a bag, with the item in it, or worn. It reads the gear and makes a player, who wears the item when the test asks for it worn, and otherwise gets it in their bag. Their bag has a revision then, so they don't get the kit, which keeps the test to the item it gives them. The first test gives each new player the kit, once. A new player joins a world with the gear as its content, with nothing saved, and they hold the sword, item one, in their main hand. Their bag's first slot has the potions, and there are three of them. Then we copy the player as they are, the way a trip through a gate takes them. Over it, we write what a trip does: it joins them to another copy, with what they had, and someone who's thrown it all away doesn't get another kit either. So the player leaves this world, and joins it again with the copy, as they would join the copy of another land. After the trip, the bag holds one stack, the potions. A second kit would have put a second sword in the bag, as the hand is taken, so the bag would hold two. Then they throw the potions away, and the sword in their hand, and go through the gate again. Their bag is empty, and their hand too, as an empty bag that has a revision has been someone's. The second test fights with what they wear. The blade does twenty, one more for being worn, and this one rolled three more. So we add a roll to the stack, of three damage, and give alice the blade in her bag. She joins with it, and move item takes it from the first slot of her bag into her main hand, which works, and dresses her. Then a skeleton comes a metre away from her, close enough to hit. She attacks, for a second, which is long enough for one blow, and the skeleton's health is fifty, less twenty-four. Twenty-four is the blade's damage, its modifier and the stack's roll, added together, as a level one player has no extra damage. The third test swings with a weapon in hand, and not without one. The first player carries a bone in their bag, and nothing in their hand, and a new player joins with the kit, next to them. A skeleton comes a metre away from them. Set input takes where each player walks, whether they run and whether they attack, so both of them attack, standing where they are. After a single step of the world, a tick, the player with the bone isn't attacking, as there's nothing in their hand to swing. And the player with the sword is attacking, which is the swing that we're going to stop. Then the player with the sword puts it away, into their bag, and they're no longer attacking. Putting the sword away ends the swing, before it lands. After a second, the skeleton has all of its fifty health, so the swing that was put away didn't land. The last test is about armour, which takes the blows, and the health that comes with the cap. Fight is a lambda that takes a player, by value, as it's going to change where they stand. Over it, we write that it's two worlds that go alike, but for the cap. It makes a world with the gear, and puts the player two metres from the middle, along x, where they join. Then a skeleton comes a metre further along, close enough to see them and to come for them. The world runs four seconds, a second at a time, and the lambda returns the health the player has left after the skeleton's blows. So one player fights wearing the cap, and over it, we write why it matters: the cap adds ten health and two armour, which takes the skeleton's five down to three. The other player fights with a bone in the bag and nothing on their head. The capped player started with a hundred and ten, and lost some, in blows of three. And they took as many blows as the bare player, who lost theirs in blows of five, from a hundred. Comparing the blows, and not the health, keeps the test to the armour, whatever the skeleton does in those four seconds. Then a third world, with the gear, as taking the cap off should take its health with it, and that's the last thing the test checks. A player joins wearing the cap, with a hundred and ten health, which is the most they can have. When they move the cap from their head into the bag, their health is a hundred.

14:05 Docker builds the game and its tests at this step, sped up here, and the world's tests don't need MySQL, Redis or Consul, so they run with dash dash no deps. The tests from before pass, now that each player's sword comes from the kit, in their main hand, and so do the four new ones. The last of them, the armour test, passes, so the cap takes the skeleton's blows down to three.

A potion from the bag

14:29 Each new player has three potions in their bag now, so next, the world is going to let them drink one, which the client has no message for yet. In world dot h, the player gets item cooldowns, next to the cooldowns of their skills, and we write what they are: how long until they can use each item again, by its id in the items file. A cooldown goes by the item, and not by the slot, so drinking from another stack of potions doesn't get around it. In the class, use item takes the player and a slot of their bag. We write that the player uses one of what's in that slot, such as drinks a potion, or the error says why not, in words for the player, like moving an item. In world dot c c, map comes in, the standard library's ordered map, for the cooldowns that a function of ours is going to take. Run down is what act did for the skills' cooldowns, moved into the unnamed namespace, so that the skills and the items can both use it. It goes through the map with an iterator, and takes the time off each cooldown. A cooldown that's run out is erased, as erase returns the iterator after the one it took out, and one that hasn't goes on to the next, with standard next. Then use item, which finds the player like move item does, or says there's nobody to use it. Then it makes the place in the bag with in bag, and looks at what's there, and with nothing there, it says so. It finds the item in the content, and one the items file doesn't have, or one with no use part, can't be used. The error names the item, you can't use the bone, say, or says you can't use that, for one the file doesn't have. We keep a reference to the item's name and to its use, for what comes next. A player can't drink while they're dead, so that's refused, with a message that says why. Then the cooldown: if one is running for the item, the error says how long until they can use another one, in seconds. Chrono's ceil rounds the time up to whole seconds, so that a player with a tenth of a second left reads one second, and not zero. Then, for an item that heals, a player at full health is told that they're at full health already, so they don't waste a potion. Otherwise, their health goes up by what the potion heals, up to max health of, the most they can have, with what they wear. When the item has a cooldown, it starts, from its milliseconds in the items file. And last, the inventory takes one off the stack, and use item returns how that went. In act, the loop makes way for run down, for the skills' cooldowns, and then for the items' cooldowns, which run down whatever the player is doing, even while they're dead. In world test dot c c, the test uses what's in the bag once it's cooled down. A player joins with fifty health and nothing else saved, so they get the kit, and a second player has a bone. Potions is a lambda that counts the potions in the first player's first slot. They drink one, which works, and their health is ninety, which is fifty and the potion's forty, and two potions are left. Another, straight away, is refused, with the message that they can use another potion in ten seconds. After ten seconds of the world, they drink again, which works, and their health is a hundred. Forty more would have been past the most they can have, so the potion stops at a hundred, and one potion is left. Ten seconds after that, at full health, a third is refused, and they keep their last potion. Using slot five, where there's nothing, says there's nothing there, and the second player can't use their bone, which the message names.

17:58 Docker builds the game at this step again, sped up here, as a build from nothing takes a couple of minutes. Then it runs the tests whose names start with uses, the skills' one from before and the new one. They don't need MySQL, Redis or Consul, so dash dash no deps leaves them out, and the world runs inside each test. The new one passes, with the potion's heal, its cooldown and each of the refusals.

Saving the bags

18:22 Nothing saves the bags yet, so a player who logs out and in again gets a new kit, and loses what they had. A player is saved into Redis on each change, and from there, the saver writes them to MySQL each five seconds. The bags are going to take that path too, so MySQL needs a table for them, which comes from a migration, version seven, like the skills'. Player items has a row for each stack in a bag, and each item that's worn. Each row has the account's id and the world, as a player has a bag in each world, and the place. The place is in words, like the lands are: bag zero to bag twenty-three, or the slot it's worn in, such as main hand. That way, the rows read well when we look at them. Then the item's id, and how many, a small int unsigned, which goes up to sixty-five thousand, with one by default. The state is JSON, which can be null, and we write what it holds: what this one has of its own, such as its rolls, as protobuf's JSON for a stack. A field that a later server adds to a stack, such as how worn down a sword is, goes into the state with no migration. The primary key is the account, the world and the place, so a place holds a single stack. And the foreign key points at the player's row, with on delete cascade, like the skills, so deleting an account deletes its bags. Version eight gives players an items revision, the revision of their bag, which is null for a player from before bags. A player who's emptied their bag has no rows, and without the revision, they'd look like one who's yet to have a bag, and get the kit again. Algorithm instant adds the column without copying the table, like our other columns. A bag's rows are going to be deleted and inserted again, and a failure between the two would empty the bag, so in database dot h, functional comes in, for a function that the database runs. Transaction runs the statements as one transaction, so that either all of them happen, or none does: none if one of them fails, or if the connection goes. And we write that a lost connection isn't tried again inside a transaction, as what ran before it on that connection is gone. The class keeps whether it's in a transaction, in a bool, in transaction. In database dot c c, functional comes in too. When run finds that MySQL dropped the connection, after eight hours of sitting idle or a restart, it connects again and retries, as nothing has run yet. Inside a transaction, the statements before it went with the connection, so a retry would run the rest of them without the first ones. So it doesn't retry in a transaction, and the reason goes above it too. Then transaction, which takes the statements as a standard function that returns a status, so that a caller can pass a lambda that runs them. First, it turns MySQL's autocommit off, so that the statements wait for a commit. Connecting again here is safe, though, and the reason goes above it. Turning autocommit off is the first thing to reach MySQL, so a connection that's gone stale fails there, before anything has run. So when that fails, it connects again, and turns autocommit off again, and if that fails too, it returns MySQL's error. Then it marks the database as in a transaction, runs the statements, and marks it as out of one. If they all worked, it commits, and a commit that fails is the transaction's error. If anything failed, it rolls back, so that MySQL undoes what ran, and either way, autocommit goes back on, for the statements that run on their own. In the server's CMake lists, the tests get a file of their own for the database, database test dot c c, on the line with the accounts' and the players' tests. In it, memory comes in for the unique pointer that holds the test database, string for the account's name, and absl's status and status or, for what the database returns. Then GoogleTest, and test services, which connects the tests to MySQL, and names their accounts. It has one test, which runs a transaction whole or not at all. Like the other tests that need MySQL, it skips without it. Its account has a random test name, so that it can run next to other tests. Create is a lambda that inserts an account with that name, and accounts is one that counts the accounts with it. The first transaction creates the account, and then inserts a row into a table called nowhere, which MySQL doesn't have. The transaction fails, and over it, we write that the second statement fails, so the first doesn't happen either. So MySQL has no account with that name afterwards. Then a transaction that creates it works, and there's one account, which the test deletes.

23:00 Now the players' saving can write the bags. In players dot h, c standard def comes in, for size t, and the protocol's header, for the inventory message. The progress that we save gets the bag's revision, items revision. Over it, we write why it's a revision, and not the bag: it goes up with each change to the bag, so telling whether a bag needs saving takes a number. The server compares each player's progress with the one it saved last, on each tick, and comparing a number is quicker than comparing a bag of twenty-nine slots. The class gets k item rows, a thousand, the most rows of the bags that go in one statement, which we're going to need in flush. Save takes the bags too, of those whose bags changed, as a map from the player to their inventory, empty by default, and we write that those bags are marked unsaved in a set of their own. And flush saves each batch in a transaction with the bags that changed. In server dot c c, progress of copies the inventory's revision into the progress. In save changed, we collect the bags next to the changed players, in a map of their own, from each player's id to their inventory, which save is going to take. A player whose progress changed has their bag sent as well when its revision has moved, and we note that above the check. A player who walks has a new place, but not a new bag, so we don't write it each time. Then save gets the bags, with the changed players. In players dot c c, utility comes in, for standard move, and items dot h, for the names of the slots, and protobuf's JSON utilities, to turn a stack into JSON and back. And inventory dot h, for the places, in bag and worn. Next to the set of unsaved players, a second set, items unsaved, for the players whose bags changed, who are in the first set too. The saver writes a player's bag when they're in this set, and not each time they take a step. Place name writes a place in words, and over it, we write why: in words, as lands are, so that the rows read well, bag zero to bag twenty-three, or the slot that's worn, such as main hand. That way, a query that we type by hand can ask for what's in a player's main hand by its name. So it's str cat for bag and its slot, or slot name for a slot that's worn. Place named goes the other way, from a name to a place. A name that starts with bag and a space, followed by a number, is that slot in the bag. Otherwise we go through the slots, from slot min to slot max, and is valid, which protobuf generates for an enum, would skip any number in between that isn't a slot. The slot whose name it is gives us a place that's worn. And a name that's neither is an internal error, which says that an item was saved in a place that we don't know. State of is what goes in the state column. It's what one of an item has of its own, past its item and count, as protobuf's JSON for a stack, or nothing. A field that a later server adds goes in without a migration. So it copies the stack, and clears its item and count, which have columns of their own. A stack with nothing left, such as a potion, which has no rolls, has a size of zero bytes, so its state is empty, which goes in as null. Otherwise, message to JSON string writes it as protobuf's JSON, with the fields under their names and an enum's value by its name, such as stat damage. If that fails, the state is empty too, and the stack goes in as a row with no state. Bag of builds the bag from its rows, and the revision from the players table, which is empty when it's null. A revision that isn't a number is an error. Then the options for protobuf's JSON parser ignore unknown fields. That way, a server from before a field leaves out what it doesn't know, from a state that a later server wrote, and the rest of the bag loads. For each row, place named reads the place. A state that isn't empty is parsed into the stack, and one that isn't a stack's is an error. Then the item and the count come from their columns, with simple a to i, and a column that isn't a number is an error too. They go in the stack after the state is parsed, so that they win over anything the state had. Slots is a reference to the bag's map, for a place in the bag, or to the map of what's worn, as both are protobuf maps from a slot's number to a stack. The key is the slot in the bag, or the number of the slot that's worn, and the stack is moved into the map under it. So what an earlier server doesn't know of a later one's state, it leaves out. In load, the select reads the items revision too, at the end of the row. So the saved fields stop before the last column, which is the revision. After the skills, we read the bag's rows, with the account and the world in the where clause, which lead the table's key. That's a row for each stack in the bag, and each item worn. If MySQL fails, the load fails, and otherwise, bag of makes the bag from the rows and the revision. The hash in Redis is read for its items field too, at the end, and the hash gets a name, as we read it a few times now. When the hash is there, its fields win, but for the last one, the items. When Redis has a bag, parse from string reads its bytes into the bag, over MySQL's. MySQL's bag is the newest when Redis has none. Redis has the bag from when it last changed, and a hash from a server from before bags has none, and neither does one written since the bag came from MySQL. And bytes that aren't a bag are an error. Then the player's inventory is made from the bag, with standard move, so that the bag's messages aren't copied, and the rest of load reads the player's fields like before. Save takes the bags of the players whose bags changed, and it returns early when nobody changed, as before. Then, next to the S add for the unsaved players, there's one for those whose bags changed. Each player's H set goes into the commands with emplace back, which returns a reference to it, so that we can add the bag to the end of it, after the fields it had. The if statement starts with a declaration, which keeps bag inside the if. Redis strings are binary safe, so the bag goes as its message, in protobuf's bytes, which Redis keeps as they are, and the bytes don't have to be text. So when the player's bag changed, the H set gets an items field, with the bag serialised as a string, and the player's key goes in items unsaved. And the S add for the bags goes into the pipeline when it has a key past the command and the set's name, so that save sends it when a bag changed. In flush, each player's key comes off items unsaved with an S rem, in the reads before their hash. Each player's mark for their bag comes off first, so that a bag that changes in between is marked again, for next time. If the server changes the bag after we've read it, its S add puts the key back for the next flush, and if we took the mark off after reading, we'd lose that. So the reads are twice as many, and we reserve room for them: first an S rem for each key, then an H M get for each, which reads the items too. S rem answers one when the key was in the set, which tells us whether that player's bag changed, so the replies are the S rems' answers, and then the hashes. The insert into players gets the items revision. Then the bags, where bag keys keeps the players whose bags we write, so that we can mark them again if the write fails. Over it, we write what happens to a bag that changed: its old rows in player items go, and its new ones go in. The delete removes the rows of each of those players, by their account and world, with a list of pairs after in, and gone values holds its values. And item values holds the values of the rows that go in, six to a row. For each key, bag changed is whether its S rem answered one, and its hash is in the second half of the replies, after the S rems' answers. A player whose hash is gone has nothing to save, like before. The row for players gets a question mark more, for the revision, which goes in as null when it's empty, and we write that a bag that hasn't changed keeps the revision MySQL has. A key from before worlds has no bag, so its revision is empty. For the others, the world and the account get names, and the players row takes the first eight fields of the hash. When the bag hasn't changed, or the hash has no bag, or the bytes aren't one, the revision is empty, and we don't touch the bag's rows. Otherwise, the revision goes in, the key goes in bag keys, and the player's account and world go in the delete, with a comma before each pair but the first. Add is a lambda that adds a row's six values, the account, the world, the place's name, the item, the count and the state. It captures by reference, so it adds to the item values of this flush. Then we add a row for each stack in the bag, and one for each item that's worn. The skills are the ninth field now, and their rows take the world and the account by name, with the list split at its commas as before. On a duplicate key, the players row updates its items revision with coalesce, which takes the first of its values that isn't null. So a player whose bag didn't change keeps the revision they had, and the delete's list gets its closing bracket. Then the batch goes in a transaction, whose lambda captures by reference, and runs the players insert, then the skills insert, like before, with the skills' result taking the place of the players'. When there are bags that changed, the delete runs with its values, which takes away their old rows, and then their new rows go in. Each row has six values, and above it goes why the rows go in more than one statement. A statement takes up to sixty-five thousand, five hundred and thirty-five values, and a batch is five hundred players, whose full bags have more rows than that, so they go in statements of k item rows. Five hundred bags, each with twenty-nine slots, are fourteen thousand five hundred rows, which is eighty-seven thousand values, past what MySQL takes. So the loop goes through the values a thousand rows at a time, while each statement works. First is the index of this statement's first value, and it goes up by a thousand rows of six values each time. Last is the end of this statement's values, or the end of them all. Each statement is an insert into player items, with ignore, like the skills' insert. Then a loop over its rows adds six question marks for each, with a comma before each row but the first, and the statement runs with its part of the values, from first to last. The lambda returns the status of the last statement that ran, which is the one that failed, when one did. And the batch is a transaction for a reason. A bag's rows can't go without the rows that take their place, or a failure between the two would empty it, so the batch is a transaction. When it fails, the keys go back into the unsaved players, as they did, and we put the marks back on the bags we would have written, in items unsaved too, in a single pipeline. Without those marks, the next flush would find the players, but not that their bags changed. Then flush returns the transaction's error, for the saver to log.

Testing the saving

34:07 Then the players' tests, in players test dot c c, where map comes in, for the bags that we save, as save takes them in a map. Message differencer is protobuf's utility to compare two messages, field by field, as a message has no equals operator, and the protocol's header comes in for the inventory. In the fixture, tear down deletes the test's account, and its hashes in Redis, and takes them off the set of unsaved players. It takes the test player's keys off items unsaved too, in both worlds, so that a test leaves no marks behind for the next one. Bag makes a bag at a revision, and over it, we write what it holds: twelve bones in the bag, and a blade in hand that rolled three more damage. So twelve bones go in its first slot, item three with a count of twelve. In the main hand goes item six, which is the ash blade in the game's items, one of them, with a roll of three more damage, and the bag gets its revision. Then a helper compares two inventories, with message differencer's equals. The first test loads the bag they left with, with players on the test's MySQL and Redis. Before anything is saved, a load gives a bag with no revision, which the world takes as a new player's. Then we save the player at a revision of two, with their bag. A second save moves them to an x of one, without the bag, and over it, we write that a save without the bag, as it hasn't changed, leaves the one Redis has. The load reads the player with an x of one, and the bag they left with, which the helper compares with a new bag at revision two. When they differ, the message prints the bag that loaded, with debug string. The second test flushes the bags that changed to MySQL. Rows is a lambda that reads the player's rows in player items. It selects the place, the item, the count and the state, for this account in Ember, ordered by place, so that bag zero comes before main hand. We save the bag at revision two, and flush it, which writes its rows to MySQL. The rows are the bones in bag zero, twelve of them, with no state, as they have nothing of their own. Then the blade in the main hand, whose state is its roll, in protobuf's JSON, with the stat's name and the amount. Then we delete the player's hash in Redis, as if Redis had lost it. With Redis's copy gone, the bag comes back from the rows. The load gives back the bag at revision two, as the rows have its stacks and the players' row has its revision. So the bag is Bag of two, revision and all. Then we change a row by hand, with an update that sets the bones' count to seven. A save of where they are alone doesn't write the rows again, so one we change by hand stays as it is. So we save them at an x of two, with the bag's revision as it was, and flush, and the first row's count is still seven. Last, an empty bag, at revision three, is saved. A bag that's emptied empties its rows, and keeps its revision, so that nobody takes it for a new player's. We save it and flush, and the player has no rows. With the hash deleted again, the load comes from MySQL, with revision three and an empty bag. The last test flushes more rows of bags than a statement takes. Over k players, which is forty, we write what they hold: forty players, each with a stack in each slot of the bag, and an item in each slot that's worn, which is more rows than go in one statement. Accounts creates forty accounts, each with a random test name, and we keep their ids, as the players' rows and the bags' rows are keyed by them. Each create has to work, or the test stops there with its status. Full is a bag, and a loop over k bag slots puts bones in each of its twenty-four slots, from one bone in the first to twenty-four in the last. And there's a sword in each of the five worn slots. The saver doesn't check what's worn where, so a sword on the head is fine for this test. Changed and bags give each player a progress at revision one, and the full bag, and we save them all. Then we assert that forty bags of twenty-nine rows, which is eleven hundred and sixty, are more than k item rows, so that the flush has to split them. The flush works, for all forty of them. Keys starts a del command for Redis, which is going to remove the players' hashes at the end. Then for each player, we count their rows in player items with a select, which has to work, and the count is twenty-nine: the bag's twenty-four and the five that are worn. Their hash's key goes on the del, and the test deletes their account, which deletes their rows with it. Last, the del removes all of their hashes, in a single command.

38:42 These tests need MySQL and Redis, so without dash dash no deps, Compose starts them, and Flyway runs the migrations, versions seven and eight with the others. Then Docker builds the game and its tests at this step, sped up here, and the players' tests and the database's run. They pass, and so does the test with forty full bags, whose rows go in two statements.

39:04 So let's see the bags in MySQL, where the saver has written them. With twenty bots playing on Ember in Compose for a few seconds, we ask MySQL for the rows of the first two bots' bags, with their names from the accounts. Each bot has two rows: the sword in their main hand, item one, and the three potions, item two, in bag zero, and neither has a state, as neither has rolls. Then we ask for the players, with the lowest and the highest items revision. All twenty of them are saved at revision two, one for wearing the sword and one for the potions going in the bag.

Wrap-up

39:37 So, today each player carries a bag, and a new player gets the kit in it, once, the sword in hand and three potions. What they wear adds up to their stats, the damage, the health and the armour, as it changes, and they fight with the weapon in their hand. A potion from the bag heals forty, with a cooldown of ten seconds by the item. The bags go into Redis when their revision moves, as protobuf's bytes, and the saver writes them to MySQL, a row for each stack, with its own state in JSON. Each batch is a transaction, so a bag's rows don't go without their new ones, and they go in statements of a thousand rows, which MySQL takes.

Playing it

41:07 Let me know in the comments what you'd like to see next. And if you want to follow along with the series, subscribe to the channel. I hope you find this useful, and thanks for watching.