GSP1074

Overview

Google Cloud Spanner is a fully managed horizontally scalable, globally distributed, relational database service that provides ACID transactions and SQL semantics without giving up performance and high availability. These features makes Spanner a great fit in the architecture of games that want to enable a global player base or are concerned about data consistency.

In this lab, you will create four Go services that interact with a regional Spanner database. The first two services, profile-service and matchmaking-service, enable players to sign up and start playing. The second pair of services, item-service and tradepost-service, enable players to acquire items and money, and then list items on the trading post for other players to purchase.

You will then generate data leveraging the Python load framework Locust.io to simulate players signing up and playing games to obtain games_played and games_won statistics. Players will also acquire money and items through the course of "game play". Players can then list items for sale on a tradepost, where other players with enough money can purchase those items.

You'll also query Spanner to determine how many players are playing, statistics about players' games won versus games played, players' account balances and number of items, and statistics about trade orders that are open or have been filled.

Objectives

In this lab, you will:

• Set up a Cloud Spanner instance
• Create a game database and schema
• Deploy Go apps to work with Cloud Spanner
• Use read-write transactions to ensure consistency for data changes
• Leverage DML and Spanner mutations to modify data
• Generate data using Locust
• Query data in Cloud Spanner to answer questions about games and players

Setup and Requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab---remember, once you start, you cannot pause a lab.

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details panel.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details panel.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left.

Activate Cloud Shell

Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.

1. Click Activate Cloud Shell at the top of the Google Cloud console.

When you are connected, you are already authenticated, and the project is set to your Project_ID, . The output contains a line that declares the Project_ID for this session:

Your Cloud Platform project in this session is set to {{{project_0.project_id | "PROJECT_ID"}}}

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

2. (Optional) You can list the active account name with this command:

gcloud auth list

3. Click Authorize.

Output:

ACTIVE: * ACCOUNT: {{{user_0.username | "ACCOUNT"}}} To set the active account, run: $ gcloud config set account `ACCOUNT`

4. (Optional) You can list the project ID with this command:

gcloud config list project

Output:

[core] project = {{{project_0.project_id | "PROJECT_ID"}}} Note: For full documentation of gcloud, in Google Cloud, refer to the gcloud CLI overview guide.

Task 1. Set up a Locust load generator

Locust is a Python load testing framework that is useful to test REST API endpoints. In this lab, we will have 2 different load tests in the generators directory that we will highlight:

• authentication_server.py: contains tasks to create players and to get a random player to imitate single point lookups.
• match_server.py: contains tasks to create games and close games. Creating games will assign 100 random players that aren't currently playing games. Closing games will update games_played and games_won statistics, and allow those players to be assigned to a future game.

1. Open a new Cloud Shell window and run the following command to clone the Spanner Gaming Samples repository:

git clone https://github.com/cloudspannerecosystem/spanner-gaming-sample.git -b master cd spanner-gaming-sample/

2. Validate that Python version 3.9 or above is installed:

python -V

You should see the similar output:

Python 3.9.2

3. Once you have Python installed, you can install the requirements for Locust.

pip3 install -r requirements.txt

4. Now, update the PATH so that the newly installed locust binary can be found:

PATH="~/.local/bin:$PATH" which locust

Task 2. Create a Spanner instance and database

In this section, you will set up the Cloud Spanner instance and database.

Create the Spanner instance

In this section you will set up your Spanner Instance for the lab.

1. From the Navigation Menu, under the Databases section click Spanner.

2. Click on Create a Provisioned Instance.

3. For the Spanner configuration, use the following:

   • Instance name: cloudspanner-gaming
   • Configuration: Regional and
   • Processing units: 500

4. Click Create.

Click Check my progress to verify the objective. Create the Spanner instance

Create the database and schema

Now you can create the database. Spanner allows for multiple databases on a single instance. The database is where you define your schema. You can also control who has access to the database, set up custom encryption, configure the optimizer, and set the retention period.

For this lab, you will create the database with default options, and supply the schema at creation time. You can read more about databases on Spanner here.

At this step in the lab, you will create two tables: players and games:

• Players can participate in many games over time, but only one game at a time. Players also have stats as a JSON data type to keep track of interesting statistics like games_played and games_won. Because other statistics might be added later, this is effectively a schema-less column for players.
• Games keep track of the players that participated using Spanner's ARRAY data type. A game's winner and finished attributes are not populated until the game is closed out.

There is one foreign key to ensure the player's current_game is a valid game.

1. From the Instance Overview page, click on Create Database.

2. For the Database name use sample-game and choose the Google Standard SQL dialect.

Note: You must use Google Standard SQL dialect for this lab to work.

3. For the schema, copy and paste this DDL into the box:

CREATE TABLE games ( gameUUID STRING(36) NOT NULL, players ARRAY<STRING(36)> NOT NULL, winner STRING(36), created TIMESTAMP, finished TIMESTAMP, ) PRIMARY KEY(gameUUID); CREATE TABLE players ( playerUUID STRING(36) NOT NULL, player_name STRING(64) NOT NULL, email STRING(MAX) NOT NULL, password_hash BYTES(60) NOT NULL, created TIMESTAMP, updated TIMESTAMP, stats JSON, account_balance NUMERIC NOT NULL DEFAULT (0.00), is_logged_in BOOL, last_login TIMESTAMP, valid_email BOOL, current_game STRING(36), FOREIGN KEY (current_game) REFERENCES games (gameUUID), ) PRIMARY KEY(playerUUID); CREATE UNIQUE INDEX PlayerAuthentication ON players(email) STORING (password_hash); CREATE INDEX PlayerGame ON players(current_game); CREATE UNIQUE INDEX PlayerName ON players(player_name);

4. Then, click the Create button and wait a few seconds for your database to be created.

The create database page should look like this:

Now, you need to set some environment variables in Cloud Shell to be used later in the code lab.

Click Check my progress to verify the objective. Create the database and schema 5. Run the following commands to set the following environment variables in Cloud Shell:

export PROJECT_ID=$(gcloud config get-value project) export SPANNER_PROJECT_ID=$PROJECT_ID export SPANNER_INSTANCE_ID=cloudspanner-gaming export SPANNER_DATABASE_ID=sample-game

Great! In this section you created a Spanner instance and the sample-game database. You have also defined the schema that this sample game uses.

Task 3. Deploy the profile service

In this section, you will deploy the profile service to allow players to sign up to play the game!

Service overview

The profile service is a REST API written in Go that leverages the gin framework. In this API, players can sign up to play games. This is created by a simple POST command that accepts a player name, email and password. The password is encrypted with bcrypt and the hash is stored in the database.

Email is treated as a unique identifier, while the player_name is used for display purposes for the game. This API currently does not handle login, but implementing this can be left to you as an additional exercise.

The ./src/golang/profile-service/main.go file for the profile service exposes two primary endpoints as follows:

func main() { configuration, _ := config.NewConfig() router := gin.Default() router.SetTrustedProxies(nil) router.Use(setSpannerConnection(configuration)) router.POST("/players", createPlayer) router.POST("/players", createPlayer) router.GET("/players", getPlayerUUIDs) router.GET("/players/:id", getPlayerByID) router.Run(configuration.Server.URL()) } Note: The getPlayerUUIDs function and GET /players endpoint is unused by this lab, so is not discussed below.

And the code for those endpoints will route to the player model.

func getPlayerByID(c *gin.Context) { var playerUUID = c.Param("id") ctx, client := getSpannerConnection(c) player, err := models.GetPlayerByUUID(ctx, client, playerUUID) if err != nil { c.IndentedJSON(http.StatusNotFound, gin.H{"message": "player not found"}) return } c.IndentedJSON(http.StatusOK, player) } func createPlayer(c *gin.Context) { var player models.Player if err := c.BindJSON(&player); err != nil { c.AbortWithError(http.StatusBadRequest, err) return } ctx, client := getSpannerConnection(c) err := player.AddPlayer(ctx, client) if err != nil { c.AbortWithError(http.StatusBadRequest, err) return } c.IndentedJSON(http.StatusCreated, player.PlayerUUID) }

One of the first things the service does is set the Spanner connection. This is implemented at the service level to create the session pool for the service.

func setSpannerConnection() gin.HandlerFunc { ctx := context.Background() client, err := spanner.NewClient(ctx, configuration.Spanner.URL()) if err != nil { log.Fatal(err) } return func(c *gin.Context) { c.Set("spanner_client", *client) c.Set("spanner_context", ctx) c.Next() } }

The Player and PlayerStats are structs defined as follows:

type Player struct { PlayerUUID string `json:"playerUUID" validate:"omitempty,uuid4"` Player_name string `json:"player_name" validate:"required_with=Password Email"` Email string `json:"email" validate:"required_with=Player_name Password,email"` // not stored in DB Password string `json:"password" validate:"required_with=Player_name Email"` // stored in DB Password_hash []byte `json:"password_hash"` created time.Time updated time.Time Stats spanner.NullJSON `json:"stats"` Account_balance big.Rat `json:"account_balance"` last_login time.Time is_logged_in bool valid_email bool Current_game string `json:"current_game" validate:"omitempty,uuid4"` } type PlayerStats struct { Games_played spanner.NullInt64 `json:"games_played"` Games_won spanner.NullInt64 `json:"games_won"` } Note: Golang has a validator that is leveraged when values are bound to the Player struct. This struct leverages required fields, as well as uuid4 and email validation. It even allows for optional fields to only be validated when a value is present using omitempty.

The function to add the player leverages a DML insert inside a ReadWrite transaction, because adding players is a single statement rather than batch inserts. The function looks like this:

func (p *Player) AddPlayer(ctx context.Context, client spanner.Client) error { // Validate based on struct validation rules err := p.Validate() if err != nil { return err } // take supplied password+salt, hash. Store in user_password passHash, err := hashPassword(p.Password) if err != nil { return errors.New("Unable to hash password") } p.Password_hash = passHash // Generate UUIDv4 p.PlayerUUID = generateUUID() // Initialize player stats emptyStats := spanner.NullJSON{Value: PlayerStats{ Games_played: spanner.NullInt64{Int64: 0, Valid: true}, Games_won: spanner.NullInt64{Int64: 0, Valid: true}, }, Valid: true} // insert into spanner _, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { stmt := spanner.Statement{ SQL: `INSERT players (playerUUID, player_name, email, password_hash, created, stats) VALUES (@playerUUID, @playerName, @email, @passwordHash, CURRENT_TIMESTAMP(), @pStats) `, Params: map[string]interface{}{ "playerUUID": p.PlayerUUID, "playerName": p.Player_name, "email": p.Email, "passwordHash": p.Password_hash, "pStats": emptyStats, }, } _, err := txn.Update(ctx, stmt) return err }) if err != nil { return err } // return empty error on success return nil }

To retrieve a player based on their UUID, a simple read is issued. This retrieves the player playerUUID, player_name, email, and stats.

func GetPlayerByUUID(ctx context.Context, client spanner.Client, uuid string) (Player, error) { row, err := client.Single().ReadRow(ctx, "players", spanner.Key{uuid}, []string{"playerUUID", "player_name", "email", "stats"}) if err != nil { return Player{}, err } player := Player{} err = row.ToStruct(&player) if err != nil { return Player{}, err } return player, nil }

By default, the service is configured using environment variables. See the relevant section of the ./src/golang/profile-service/config/config.go file.

func NewConfig() (Config, error) { *snip* // Server defaults viper.SetDefault("server.host", "localhost") viper.SetDefault("server.port", 8080) // Bind environment variable override viper.BindEnv("server.host", "SERVICE_HOST") viper.BindEnv("server.port", "SERVICE_PORT") viper.BindEnv("spanner.project_id", "SPANNER_PROJECT_ID") viper.BindEnv("spanner.instance_id", "SPANNER_INSTANCE_ID") viper.BindEnv("spanner.database_id", "SPANNER_DATABASE_ID") *snip* return c, nil }

You can see that the default behavior is to run the service on localhost:8080. With this information it is time to run the service.

Run the profile service

1. Run the service using the following Go command. This will download dependencies, and establish the service running on port 8080:

cd ~/spanner-gaming-sample/src/golang/profile-service go run . & Note: It can take a few moments to download the golang dependencies. Warning: Configuration is handled through the SPANNER_ environment variables set earlier in the lab. If you have reconnected to the Cloud Shell terminal, please reset SPANNER_PROJECT_ID, SPANNER_INSTANCE_ID, SPANNER_DATABASE_ID.

If the command ran successfully you should see the following output:

[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached. [GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production. - using env: export GIN_MODE=release - using code: gin.SetMode(gin.ReleaseMode) [GIN-debug] POST /players --> main.createPlayer (4 handlers) [GIN-debug] GET /players --> main.getPlayerUUIDs (4 handlers) [GIN-debug] GET /players/:id --> main.getPlayerByID (4 handlers) [GIN-debug] GET /players/:id/stats --> main.getPlayerStats (4 handlers) [GIN-debug] Listening and serving HTTP on localhost:8080

2. Open a new tab in Cloud Shell and test the service by issuing a curl command:

curl http://localhost:8080/players \ --include \ --header "Content-Type: application/json" \ --request "POST" \ --data '{"email": "test@gmail.com","password": "s3cur3P@ss","player_name": "Test Player"}' Note: No plaintext passwords are stored in this database. The password supplied will be encrypted using the bcrypt library, and the resulting output is then stored in the Players table.

You should see similar output:

[GIN] 2022/09/14 - 20:35:13 | 201 | 306.298433ms | 127.0.0.1 | POST "/players" HTTP/1.1 201 Created Content-Type: application/json; charset=utf-8 Date: Wed, 14 Sep 2022 20:35:13 GMT Content-Length: 38 "506a1ab6-ee5b-4882-9bb1-ef9159a72989" Note: The output UUID will be different for each request.

Great! In this section, you deployed the profile service that allows players to sign up to play your game, and you tested out the service by issuing a POST API call to create a new player.

Task 4. Deploy the match-making service

In this section, you will deploy the match-making service. The match-making service is a REST API written in Go that leverages the gin framework.

In this API, games are created and closed. When a game is created, 100 players who are not currently playing a game are assigned to the game.

When a game is closed, a winner is randomly selected and each players' stats for games_played and games_won are adjusted. Also, each player is updated to indicate they are no longer playing and so are available to play future games.

Note: Normally closing the game would not be the responsibility of the matchmaking service. It is included here to show how this can be accomplished without complicating the architecture.

The ./src/golang/matchmaking-service/main.go file for the matchmaking service follows a similar setup and code as the profile service, so it is not repeated here. This service exposes two primary endpoints as follows:

func main() { router := gin.Default() router.SetTrustedProxies(nil) router.Use(setSpannerConnection()) router.POST("/games/create", createGame) router.PUT("/games/close", closeGame) router.Run(configuration.Server.URL()) }

This service provides a Game struct, as well as slimmed down Player and PlayerStats structs:

type Game struct { GameUUID string `json:"gameUUID"` Players []string `json:"players"` Winner string `json:"winner"` Created time.Time `json:"created"` Finished spanner.NullTime `json:"finished"` } type Player struct { PlayerUUID string `json:"playerUUID"` Stats spanner.NullJSON `json:"stats"` Current_game string `json:"current_game"` } type PlayerStats struct { Games_played int `json:"games_played"` Games_won int `json:"games_won"` }

To create a game, the matchmaking service grabs a random selection of 10 players that are not currently playing a game.

Spanner mutations are chosen to create the game and assign the players, since mutations are more performant than DML for large changes.

// Create a new game and assign players // Players that are not currently playing a game are eligble to be selected for the new game // Current implementation allows for less than numPlayers to be placed in a game func (g *Game) CreateGame(ctx context.Context, client spanner.Client) error { // Initialize game values g.GameUUID = generateUUID() numPlayers := 10 // Create and assign _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { var m []*spanner.Mutation // get players query := fmt.Sprintf("SELECT playerUUID FROM (SELECT playerUUID FROM players WHERE current_game IS NULL LIMIT 10000) TABLESAMPLE RESERVOIR (%d ROWS)", numPlayers) stmt := spanner.Statement{SQL: query} iter := txn.Query(ctx, stmt) playerRows, err := readRows(iter) if err != nil { return err } var playerUUIDs []string for _, row := range playerRows { var pUUID string if err := row.Columns(&pUUID); err != nil { return err } playerUUIDs = append(playerUUIDs, pUUID) } // Create the game gCols := []string{"gameUUID", "players", "created"} m = append(m, spanner.Insert("games", gCols, []interface{}{g.GameUUID, playerUUIDs, time.Now()})) // Update players to lock into this game for _, p := range playerUUIDs { pCols := []string{"playerUUID", "current_game"} m = append(m, spanner.Update("players", pCols, []interface{}{p, g.GameUUID})) } txn.BufferWrite(m) return nil }) if err != nil { return err } return nil }

The random selection of players is done with SQL using the TABLESPACE RESERVOIR capability of GoogleSQL.

Caution: This method for assigning random players is not very performant, because it requires an index scan of the PlayerGame index. Ideally, a matchmaking service would be more sophisticated in choosing players to assign to a game. However, it is useful enough for demonstration purposes in working with Spanner.

Closing a game is slightly more complicated. It involves choosing a random winner amongst the players of the game, marking the time the game is finished, and updating each players' stats for games_played and games_won.

Because of this complexity and the amount of changes, mutations are again chosen to close the game out.

func determineWinner(playerUUIDs []string) string { if len(playerUUIDs) == 0 { return "" } var winnerUUID string rand.Seed(time.Now().UnixNano()) offset := rand.Intn(len(playerUUIDs)) winnerUUID = playerUUIDs[offset] return winnerUUID } // Given a list of players and a winner's UUID, update players of a game // Updating players involves closing out the game (current_game = NULL) and // updating their game stats. Specifically, we are incrementing games_played. // If the player is the determined winner, then their games_won stat is incremented. func (g Game) updateGamePlayers(ctx context.Context, players []Player, txn *spanner.ReadWriteTransaction) error { for _, p := range players { // Modify stats var pStats PlayerStats json.Unmarshal([]byte(p.Stats.String()), &pStats) pStats.Games_played = pStats.Games_played + 1 if p.PlayerUUID == g.Winner { pStats.Games_won = pStats.Games_won + 1 } updatedStats, _ := json.Marshal(pStats) p.Stats.UnmarshalJSON(updatedStats) // Update player // If player's current game isn't the same as this game, that's an error if p.Current_game != g.GameUUID { errorMsg := fmt.Sprintf("Player '%s' doesn't belong to game '%s'.", p.PlayerUUID, g.GameUUID) return errors.New(errorMsg) } cols := []string{"playerUUID", "current_game", "stats"} newGame := spanner.NullString{ StringVal: "", Valid: false, } txn.BufferWrite([]*spanner.Mutation{ spanner.Update("players", cols, []interface{}{p.PlayerUUID, newGame, p.Stats}), }) } return nil } // Closing game. When provided a Game, choose a random winner and close out the game. // A game is closed by setting the winner and finished time. // Additionally all players' game stats are updated, and the current_game is set to null to allow // them to be chosen for a new game. func (g *Game) CloseGame(ctx context.Context, client spanner.Client) error { // Close game _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { // Get game players playerUUIDs, players, err := g.getGamePlayers(ctx, txn) if err != nil { return err } // Might be an issue if there are no players! if len(playerUUIDs) == 0 { errorMsg := fmt.Sprintf("No players found for game '%s'", g.GameUUID) return errors.New(errorMsg) } // Get random winner g.Winner = determineWinner(playerUUIDs) // Validate game finished time is null row, err := txn.ReadRow(ctx, "games", spanner.Key{g.GameUUID}, []string{"finished"}) if err != nil { return err } if err := row.Column(0, &g.Finished); err != nil { return err } // If time is not null, then the game is already marked as finished. // That's an error. if !g.Finished.IsNull() { errorMsg := fmt.Sprintf("Game '%s' is already finished.", g.GameUUID) return errors.New(errorMsg) } cols := []string{"gameUUID", "finished", "winner"} txn.BufferWrite([]*spanner.Mutation{ spanner.Update("games", cols, []interface{}{g.GameUUID, time.Now(), g.Winner}), }) // Update each player to increment stats.games_played // (and stats.games_won if winner), // and set current_game to null so they can be chosen for a new game playerErr := g.updateGamePlayers(ctx, players, txn) if playerErr != nil { return playerErr } return nil }) if err != nil { return err } return nil }

Configuration is again handled via environment variables as described in the `./src/golang/matchmaking-service/config/config.go for the service.

// Server defaults viper.SetDefault("server.host", "localhost") viper.SetDefault("server.port", 8081) // Bind environment variable override viper.BindEnv("server.host", "SERVICE_HOST") viper.BindEnv("server.port", "SERVICE_PORT") viper.BindEnv("spanner.project_id", "SPANNER_PROJECT_ID") viper.BindEnv("spanner.instance_id", "SPANNER_INSTANCE_ID") viper.BindEnv("spanner.database_id", "SPANNER_DATABASE_ID")

To avoid conflicts with the profile-service, this service runs on localhost:8081 by default.

With this information, it is now time to run the matchmaking service.

Run the match-making service

1. In Cloud Shell, re-run the following commands to set the following environment variables:

export PROJECT_ID=$(gcloud config get-value project) export SPANNER_PROJECT_ID=$PROJECT_ID export SPANNER_INSTANCE_ID=cloudspanner-gaming export SPANNER_DATABASE_ID=sample-game

2. Run the service using the go command. This will establish the service running on port 8082. This service has many of the same dependencies as the profile-service, so new dependencies will not be downloaded.

cd ~/spanner-gaming-sample/src/golang/matchmaking-service go run . & Warning: Configuration is handled through the SPANNER_ environment variables set earlier in the lab. If you have had to reconnect to the Cloud Shell terminal, please reset SPANNER_PROJECT_ID, SPANNER_INSTANCE_ID, SPANNER_DATABASE_ID.

If the command ran successfully, you should see the following output:

[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached. [GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production. - using env: export GIN_MODE=release - using code: gin.SetMode(gin.ReleaseMode) [GIN-debug] POST /games/create --> main.createGame (4 handlers) [GIN-debug] PUT /games/close --> main.closeGame (4 handlers) [GIN-debug] Listening and serving HTTP on localhost:8081

Create a game

You will now test the service to create a game.

1. First, leave the service running in the open terminal and open a new tab in Cloud Shell.

2. Next, issue the following curl command:

curl http://localhost:8081/games/create \ --include \ --header "Content-Type: application/json" \ --request "POST"

You should see the following output:

HTTP/1.1 201 Created Content-Type: application/json; charset=utf-8 Date: <date> 19:38:45 GMT Content-Length: 38 "f45b0f7f-405b-4e67-a3b8-a624e990285d"

3. Copy the UUID output from the curl command. Here the UUID is f45b0f7f-405b-4e67-a3b8-a624e990285d. This will be used to close the game in the next step.

Close the game

1. Run the following command to close the game. Make sure to replace <Your UUID> with the UUID you copied in the previous step.

curl http://localhost:8081/games/close \ --include \ --header "Content-Type: application/json" \ --data '{"gameUUID": "<Your UUID>"}' \ --request "PUT"

Your output should resemble the following:

HTTP/1.1 200 OK Content-Type: application/json; charset=utf-8 Date: <date> 19:43:58 GMT Content-Length: 38 "506a1ab6-ee5b-4882-9bb1-ef9159a72989" Note: The UUID output by this command is the winner of that game!

In this section, you deployed the matchmaking-service to handle creating games and assigning players to that game. This service also handles closing out a game, which picks a random winner and updates all the game players' stats for games_played and games_won. Now that your services are running, it's time to get players signing up and playing games!

Task 5. Start playing games

Now that the profile and matchmaking services are running, you can generate load using provided locust generators. Locust offers a web-interface for running the generators, but in this lab you will use the command line (--headless option).

1. First, you will want to generate players. Run the following command to call the ./generators/authentication_server.py file that will generate new players for 30s (t=30s):

cd ~/spanner-gaming-sample locust -H http://127.0.0.1:8080 -f ./generators/authentication_server.py --headless -u=2 -r=2 -t=30s Note: Over the course of running the generator, the locust command will output statistics of the requests being made, including request/s, failure/s, and latency information.

In the command above, two users (u=2) are concurrently signing up players for the duration of the test. The python code to create players in the ./generators/authentication_server.py file looks like this:

from locust import HttpUser, task, events from locust.exception import RescheduleTask import string import json import random import requests class PlayerLoad(HttpUser): def on_start(self): global pUUIDs pUUIDs = [] def generatePlayerName(self): return ''.join(random.choices(string.ascii_lowercase + string.digits, k=32)) def generatePassword(self): return ''.join(random.choices(string.ascii_lowercase + string.digits, k=32)) def generateEmail(self): return ''.join(random.choices(string.ascii_lowercase + string.digits, k=32) + ['@'] + random.choices(['gmail', 'yahoo', 'microsoft']) + ['.com']) @task def createPlayer(self): headers = {"Content-Type": "application/json"} data = {"player_name": self.generatePlayerName(), "email": self.generateEmail(), "password": self.generatePassword()} with self.client.post("/players", data=json.dumps(data), headers=headers, catch_response=True) as response: try: pUUIDs.append(response.json()) except json.JSONDecodeError: response.failure("Response could not be decoded as JSON") except KeyError: response.failure("Response did not contain expected key 'gameUUID'")

Player names, emails and passwords are randomly generated. Players that are successfully signed up will be retrieved by a second task to generate read load.

@task(5) def getPlayer(self): # No player UUIDs are in memory, reschedule task to run again later. if len(pUUIDs) == 0: raise RescheduleTask() # Get first player in our list, removing it to avoid contention from concurrent requests pUUID = pUUIDs[0] del pUUIDs[0] headers = {"Content-Type": "application/json"} self.client.get(f"/players/{pUUID}", headers=headers, name="/players/[playerUUID]")

Now that you have players signed up, they want to start playing games!

2. Run the following command to create and close games for 10 seconds:

locust -H http://127.0.0.1:8081 -f ./generators/match_server.py --headless -u=1 -r=1 -t=10s

The python code to create and close games in the ./generators/match_server.py file looks like this:

from locust import HttpUser, task from locust.exception import RescheduleTask import json class GameMatch(HttpUser): def on_start(self): global openGames openGames = [] @task def createGame(self): headers = {"Content-Type": "application/json"} # Create the game, then store the response in memory of list of open games. with self.client.post("/games/create", headers=headers, catch_response=True) as response: try: openGames.append({"gameUUID": response.json()}) except json.JSONDecodeError: response.failure("Response could not be decoded as JSON") except KeyError: response.failure("Response did not contain expected key 'gameUUID'") @task def closeGame(self): # No open games are in memory, reschedule task to run again later. if len(openGames) == 0: raise RescheduleTask() headers = {"Content-Type": "application/json"} # Close the first open game in our list, removing it to avoid # contention from concurrent requests game = openGames[0] del openGames[0] data = {"gameUUID": game["gameUUID"]} self.client.put("/games/close", data=json.dumps(data), headers=headers)

In this section, you simulated players signing up to play games and then ran simulations for players to play games using the matchmaking service. These simulations leveraged the Locust Python framework to issue requests to our services' REST API. Feel free to modify the time spent creating players and playing games, as well as the number of concurrent users (-u).

After the simulation, you will want to check on various statistics by querying Spanner.

Task 6. Retrieve game statistics

Now that you have simulated players being able to sign up and play games, you can check on your statistics.

1. From the Navigation Menu, under the Databases section click Spanner.

2. Click on the cloudspanner-gaming instance.

3. Under Databases click the sample-game database you created.

4. On the left navigation pane, click Spanner studio.

You are now ready to run some queries on Spanner to check the game statistics.

Checking open vs. closed games

A closed game is one that has the finished timestamp populated, while an open game will have finished being NULL. This value is set when the game is closed.

1. Run the following query to check how many games are open and how many are closed:

SELECT Type, NumGames FROM (SELECT "Open Games" as Type, count(*) as NumGames FROM games WHERE finished IS NULL UNION ALL SELECT "Closed Games" as Type, count(*) as NumGames FROM games WHERE finished IS NOT NULL)

Result:

Type NumGames Open Games 0 Closed Games 21 Note: Exact results of open vs. closed games will be different.

Checking amount of players playing vs not playing

A player is playing a game if their current_game column is set. Otherwise, they are not currently playing a game.

1. Run the following query to compare how many players are currently playing and not playing:

SELECT Type, NumPlayers FROM (SELECT "Playing" as Type, count(*) as NumPlayers FROM players WHERE current_game IS NOT NULL UNION ALL SELECT "Not Playing" as Type, count(*) as NumPlayers FROM players WHERE current_game IS NULL)

Result:

Type NumPlayers Playing 0 Not Playing 69 Note: Exact results of playing and not playing will be different.

Determine top winners

When a game is closed, one of the players is randomly selected to be the winner. That player's games_won statistic is incremented during closing out the game.

1. Run the following query to determine the top winners of the games:

SELECT playerUUID, stats FROM players WHERE CAST(JSON_VALUE(stats, "$.games_won") AS INT64)>0 LIMIT 10;

The output should resemble the following:

Warning: ORDER BY cannot be used on a JSON path, so the results cannot be ordered by '$.games_won'. For more information about this and other restrictions, review the JSON restrictions.

Great! In this section, you reviewed various statistics of players and games by using the Cloud Console to query Spanner.

Task 7. Update the database schema

Now you are going to add two more services: item-service and tradepost-service. Before you can do that, you need to update the schema to create four new tables: game_items, player_items, player_ledger_entries and trade_orders.

The player-item relationship is as follows:

Trade order relationships

Game items are added in the game_items table, and then can be acquired by players. The player_items table has foreign keys to both an itemUUID and a playerUUID to ensure players are acquiring only valid items.

The player_ledger_entries table keeps track of any monetary changes to the player's account balance. This can be acquiring money from loot, or by selling items on the trading post.

And finally, the trade_orders table is used to handle posting sell orders, and for buyers to fulfill those orders.

1. Navigate back to the sample-game database in Spanner.

2. To create the schema, click on the Write DDL button in the Cloud Console:

3. Copy the following input the schema definition from the schema/trading.sql file and paste it into the box:

CREATE TABLE game_items ( itemUUID STRING(36) NOT NULL, item_name STRING(MAX) NOT NULL, item_value NUMERIC NOT NULL, available_time TIMESTAMP NOT NULL, duration int64 )PRIMARY KEY (itemUUID); CREATE TABLE player_items ( playerItemUUID STRING(36) NOT NULL, playerUUID STRING(36) NOT NULL, itemUUID STRING(36) NOT NULL, price NUMERIC NOT NULL, source STRING(MAX) NOT NULL, game_session STRING(36) NOT NULL, acquire_time TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP()), expires_time TIMESTAMP, visible BOOL NOT NULL DEFAULT(true), FOREIGN KEY (itemUUID) REFERENCES game_items (itemUUID), FOREIGN KEY (game_session) REFERENCES games (gameUUID) ) PRIMARY KEY (playerUUID, playerItemUUID), INTERLEAVE IN PARENT players ON DELETE CASCADE; CREATE TABLE player_ledger_entries ( playerUUID STRING(36) NOT NULL, source STRING(MAX) NOT NULL, game_session STRING(36) NOT NULL, amount NUMERIC NOT NULL, entryDate TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true), FOREIGN KEY (game_session) REFERENCES games (gameUUID) ) PRIMARY KEY (playerUUID, entryDate DESC), INTERLEAVE IN PARENT players ON DELETE CASCADE; CREATE TABLE trade_orders ( orderUUID STRING(36) NOT NULL, lister STRING(36) NOT NULL, buyer STRING(36), playerItemUUID STRING(36) NOT NULL, trade_type STRING(5) NOT NULL, list_price NUMERIC NOT NULL, created TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP()), ended TIMESTAMP, expires TIMESTAMP NOT NULL DEFAULT (TIMESTAMP_ADD(CURRENT_TIMESTAMP(), interval 24 HOUR)), active BOOL NOT NULL DEFAULT (true), cancelled BOOL NOT NULL DEFAULT (false), filled BOOL NOT NULL DEFAULT (false), expired BOOL NOT NULL DEFAULT (false), FOREIGN KEY (playerItemUUID) REFERENCES player_items (playerItemUUID) ) PRIMARY KEY (orderUUID); CREATE INDEX TradeItem ON trade_orders(playerItemUUID, active);

3. Click the Submit button to modify the schema, and wait until the schema update completes.

Click Check my progress to verify the objective. Create the sample-game database schema

Task 8. Deploy the item service

In this section, you will deploy the item service that allows creation of game items, and players assigned to open games to be able to acquire money and game items.

Service overview

The item service is a REST API written in Go that leverages the gin framework. In this API, players that are participating in open games acquire money and items.

The ./src/golang/item-service/main.go file configures the following endpoints to work with game items and players acquiring those items. In addition, there is an endpoint for players to acquire money:

func main() { configuration, _ := config.NewConfig() router := gin.Default() router.Use(setSpannerConnection(configuration)) router.GET("/items", getItemUUIDs) router.POST("/items", createItem) router.GET("/items/:id", getItem) router.PUT("/players/balance", updatePlayerBalance) router.GET("/players", getPlayer) router.POST("/players/items", addPlayerItem) router.Run(configuration.Server.URL()) }

Configuration and using Spanner Connections is handled exactly like the profile-service and matchmaking-service from the previous sections.

The item service works with GameItem, Player, PlayerLedger, and PlayerItem with the following definitions:

// models/game_items.go type GameItem struct { ItemUUID string `json:"itemUUID"` Item_name string `json:"item_name"` Item_value big.Rat `json:"item_value"` Available_time time.Time `json:"available_time"` Duration int64 `json:"duration"` } // models/players.go type Player struct { PlayerUUID string `json:"playerUUID" binding:"required,uuid4"` Updated time.Time `json:"updated"` Account_balance big.Rat `json:"account_balance"` Current_game string `json:"current_game"` } type PlayerLedger struct { PlayerUUID string `json:"playerUUID" binding:"required,uuid4"` Amount big.Rat `json:"amount"` Game_session string `json:"game_session"` Source string `json:"source"` } // models/player_items.go type PlayerItem struct { PlayerItemUUID string `json:"playerItemUUID" binding:"omitempty,uuid4"` PlayerUUID string `json:"playerUUID" binding:"required,uuid4"` ItemUUID string `json:"itemUUID" binding:"required,uuid4"` Source string `json:"source" binding:"required"` Game_session string `json:"game_session" binding:"omitempty,uuid4"` Price big.Rat `json:"price"` AcquireTime time.Time `json:"acquire_time"` ExpiresTime spanner.NullTime `json:"expires_time"` Visible bool `json:"visible"` }

First, the game must have some items created. To do this, a POST request to the /items endpoint is called. This is a very simple DML insert into the game_items table.

// main.go func createItem(c *gin.Context) { var item models.GameItem if err := c.BindJSON(&item); err != nil { c.AbortWithError(http.StatusBadRequest, err) return } ctx, client := getSpannerConnection(c) err := item.Create(ctx, client) if err != nil { c.AbortWithError(http.StatusBadRequest, err) return } c.IndentedJSON(http.StatusCreated, item.ItemUUID) } // models/game_items.go func (i *GameItem) Create(ctx context.Context, client spanner.Client) error { // Initialize item values i.ItemUUID = generateUUID() if i.Available_time.IsZero() { i.Available_time = time.Now() } // insert into spanner _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { stmt := spanner.Statement{ SQL: `INSERT game_items (itemUUID, item_name, item_value, available_time, duration) VALUES (@itemUUID, @itemName, @itemValue, @availableTime, @duration) `, Params: map[string]interface{}{ "itemUUID": i.ItemUUID, "itemName": i.Item_name, "itemValue": i.Item_value, "availableTime": i.Available_time, "duration": i.Duration, }, } _, err := txn.Update(ctx, stmt) return err }) if err != nil { return err } // return empty error on success return nil }

To acquire an item, a POST request to the /players/items endpoint is called. The logic for this endpoint is to retrieve a game item's current value, and the player's current game session. Then insert the appropriate information into the player_items table indicating the source and time of item acquisition.

This maps to the following functions:

// main.go func addPlayerItem(c *gin.Context) { var playerItem models.PlayerItem if err := c.BindJSON(&playerItem); err != nil { c.AbortWithError(http.StatusBadRequest, err) return } ctx, client := getSpannerConnection(c) err := playerItem.Add(ctx, client) if err != nil { c.AbortWithError(http.StatusBadRequest, err) return } c.IndentedJSON(http.StatusCreated, playerItem) } // models/player_items.go func (pi *PlayerItem) Add(ctx context.Context, client spanner.Client) error { // insert into spanner _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { // Get item price at time of transaction price, err := GetItemPrice(ctx, txn, pi.ItemUUID) if err != nil { return err } pi.Price = price // Get Game session session, err := GetPlayerSession(ctx, txn, pi.PlayerUUID) if err != nil { return err } pi.Game_session = session pi.PlayerItemUUID = generateUUID() // Insert cols := []string{"playerItemUUID", "playerUUID", "itemUUID", "price", "source", "game_session"} txn.BufferWrite([]*spanner.Mutation{ spanner.Insert("player_items", cols, []interface{}{pi.PlayerItemUUID, pi.PlayerUUID, pi.ItemUUID, pi.Price, pi.Source, pi.Game_session}), }) return nil }) if err != nil { return err } // return empty error on success return nil }

To acquire an item, a POST request to the /players/items endpoint is called. The logic for this endpoint is to retrieve a game item's current value, and the player's current game session. Then insert the appropriate information into the player_items table indicating the source and time of item acquisition.

This maps to the following functions in main.go and models/player_items.go.

// main.go func addPlayerItem(c *gin.Context) { var playerItem models.PlayerItem if err := c.BindJSON(&playerItem); err != nil { c.AbortWithError(http.StatusBadRequest, err) return } ctx, client := getSpannerConnection(c) err := playerItem.Add(ctx, client) if err != nil { c.AbortWithError(http.StatusBadRequest, err) return } c.IndentedJSON(http.StatusCreated, playerItem) } // models/player_items.go func (pi *PlayerItem) Add(ctx context.Context, client spanner.Client) error { // insert into spanner _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { // Get item price at time of transaction price, err := GetItemPrice(ctx, txn, pi.ItemUUID) if err != nil { return err } pi.Price = price // Get Game session session, err := GetPlayerSession(ctx, txn, pi.PlayerUUID) if err != nil { return err } pi.Game_session = session pi.PlayerItemUUID = generateUUID() // Insert cols := []string{"playerItemUUID", "playerUUID", "itemUUID", "price", "source", "game_session"} txn.BufferWrite([]*spanner.Mutation{ spanner.Insert("player_items", cols, []interface{}{pi.PlayerItemUUID, pi.PlayerUUID, pi.ItemUUID, pi.Price, pi.Source, pi.Game_session}), }) return nil }) if err != nil { return err } // return empty error on success return nil }

For a player to acquire money, a PUT request to the /players/updatebalance endpoint is called.

The logic for this endpoint is to update the player's balance after applying the amount, as well as update the player_ledger_entries table with a record of the acquisition. The player's account_balance is modified to be returned to the caller. DML is used to modify both players and player_ledger_entries.

This maps to the following functions:

// main.go func updatePlayerBalance(c *gin.Context) { var player models.Player var ledger models.PlayerLedger if err := c.BindJSON(&ledger); err != nil { c.AbortWithError(http.StatusBadRequest, err) return } ctx, client := getSpannerConnection(c) err := ledger.UpdateBalance(ctx, client, &player) if err != nil { c.AbortWithError(http.StatusBadRequest, err) return } type PlayerBalance struct { PlayerUUID, AccountBalance string } balance := PlayerBalance{PlayerUUID: player.PlayerUUID, AccountBalance: player.Account_balance.FloatString(2)} c.IndentedJSON(http.StatusOK, balance) } // models/players.go func (l *PlayerLedger) UpdateBalance(ctx context.Context, client spanner.Client, p *Player) error { // Update balance with new amount _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { p.PlayerUUID = l.PlayerUUID stmt := spanner.Statement{ SQL: `UPDATE players SET account_balance = (account_balance + @amount) WHERE playerUUID = @playerUUID`, Params: map[string]interface{}{ "amount": l.Amount, "playerUUID": p.PlayerUUID, }, } numRows, err := txn.Update(ctx, stmt) if err != nil { return err } // No rows modified. That's an error if numRows == 0 { errorMsg := fmt.Sprintf("Account balance for player '%s' could not be updated", p.PlayerUUID) return errors.New(errorMsg) } // Get player's new balance (read after write) stmt = spanner.Statement{ SQL: `SELECT account_balance, current_game FROM players WHERE playerUUID = @playerUUID`, Params: map[string]interface{}{ "playerUUID": p.PlayerUUID, }, } iter := txn.Query(ctx, stmt) defer iter.Stop() for { row, err := iter.Next() if err == iterator.Done { break } if err != nil { return err } var accountBalance big.Rat var gameSession string if err := row.Columns(&accountBalance, &gameSession); err != nil { return err } p.Account_balance = accountBalance l.Game_session = gameSession } stmt = spanner.Statement{ SQL: `INSERT INTO player_ledger_entries (playerUUID, amount, game_session, source, entryDate) VALUES (@playerUUID, @amount, @game, @source, PENDING_COMMIT_TIMESTAMP())`, Params: map[string]interface{}{ "playerUUID": l.PlayerUUID, "amount": l.Amount, "game": l.Game_session, "source": l.Source, }, } numRows, err = txn.Update(ctx, stmt) if err != nil { return err } return nil }) if err != nil { return err } return nil }

By default, the service is configured using environment variables. See the relevant section of the ./src/golang/item-service/config/config.go file.

func NewConfig() (Config, error) { *snip* // Server defaults viper.SetDefault("server.host", "localhost") viper.SetDefault("server.port", 8082) // Bind environment variable override viper.BindEnv("server.host", "SERVICE_HOST") viper.BindEnv("server.port", "SERVICE_PORT") viper.BindEnv("spanner.project_id", "SPANNER_PROJECT_ID") viper.BindEnv("spanner.instance_id", "SPANNER_INSTANCE_ID") viper.BindEnv("spanner.database_id", "SPANNER_DATABASE_ID") *snip* return c, nil }

You can see that the default behavior is to run the service on localhost:8082. With this information it is time to run the service.

Run the service

1. In Cloud Shell, re-run the following commands to set the following environment variables:

export PROJECT_ID=$(gcloud config get-value project) export SPANNER_PROJECT_ID=$PROJECT_ID export SPANNER_INSTANCE_ID=cloudspanner-gaming export SPANNER_DATABASE_ID=sample-game

2. Next, run the service to download dependencies and establish the service running on port 8082:

cd ~/spanner-gaming-sample/src/golang/item-service go run . &

You should see the following output:

[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached. [GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production. - using env: export GIN_MODE=release - using code: gin.SetMode(gin.ReleaseMode) [GIN-debug] GET /items --> main.getItemUUIDs (4 handlers) [GIN-debug] POST /items --> main.createItem (4 handlers) [GIN-debug] GET /items/:id --> main.getItem (4 handlers) [GIN-debug] PUT /players/balance --> main.updatePlayerBalance (4 handlers) [GIN-debug] GET /players --> main.getPlayer (4 handlers) [GIN-debug] POST /players/items --> main.addPlayerItem (4 handlers) [GIN-debug] Listening and serving HTTP on localhost:8082

3. Test the service by issuing a curl command to create an item:

curl http://localhost:8082/items \ --include \ --header "Content-Type: application/json" \ --request "POST" \ --data '{"item_name": "test_item","item_value": "3.14"}'

You should see the following output:

HTTP/1.1 201 Created Content-Type: application/json; charset=utf-8 Date: <Date> 14:59:43 GMT Content-Length: 38 "aecde380-0a79-48c0-ab5d-0da675d3412c" Note: The output UUID will be different for each request.

4. Next, you want a player to acquire this item. To do this, you need an ItemUUID and PlayerUUID. The ItemUUID is the output from the previous command. In this example, case it's: aecde380-0a79-48c0-ab5d-0da675d3412c.

To get a PlayerUUID, make a call to the GET /players endpoint:

curl http://localhost:8082/players

You should see the following output:

{ "playerUUID": "b74cc194-87b0-4a55-a67f-0f0742ef6352", "updated": "0001-01-01T00:00:00Z", "account_balance": {}, "current_game": "7b97fa85-5658-4ded-a962-4c09269a0a79" }

5. For the player to acquire the item, make a request to the POST /players/items endpoint:

curl http://localhost:8082/players/items \ --include \ --header "Content-Type: application/json" \ --request "POST" \ --data '{"playerUUID": "b74cc194-87b0-4a55-a67f-0f0742ef6352","itemUUID": "109ec745-9906-402b-9d03-ca7153a10312", "source": "loot"}' Note: Substitute the values for ItemUUID and PlayerUUID from the above outputs.

Task 9. Deploy the tradepost service

In this section, you will deploy the tradepost service to handle creating sell orders. This service also handles the ability to buy those orders.

Service overview

The tradepost service is a REST API written in Go that leverages the gin framework. In this API, player items are posted to sell. Players of games can then get open trades, and if they have enough money, can purchase the item.

The ./src/golang/tradepost-service/main.go file for the tradepost service follows a similar setup and code as the other services, so it is not repeated here. This service exposes several endpoints as follows:

func main() { configuration, _ := config.NewConfig() router := gin.Default() router.SetTrustedProxies(nil) router.Use(setSpannerConnection(configuration)) router.GET("/trades/player_items", getPlayerItem) router.POST("/trades/sell", createOrder) router.PUT("/trades/buy", purchaseOrder) router.GET("/trades/open", getOpenOrder) router.Run(configuration.Server.URL()) }

This service provides a TradeOrder struct, as well as required structs for GameItem, PlayerItem, Player, and PlayerLedger structs:

type TradeOrder struct { OrderUUID string `json:"orderUUID" binding:"omitempty,uuid4"` Lister string `json:"lister" binding:"omitempty,uuid4"` Buyer string `json:"buyer" binding:"omitempty,uuid4"` PlayerItemUUID string `json:"playerItemUUID" binding:"omitempty,uuid4"` TradeType string `json:"trade_type"` ListPrice big.Rat `json:"list_price" spanner:"list_price"` Created time.Time `json:"created"` Ended spanner.NullTime `json:"ended"` Expires time.Time `json:"expires"` Active bool `json:"active"` Cancelled bool `json:"cancelled"` Filled bool `json:"filled"` Expired bool `json:"expired"` } type GameItem struct { ItemUUID string `json:"itemUUID"` ItemName string `json:"item_name"` ItemValue big.Rat `json:"item_value"` AvailableTime time.Time `json:"available_time"` Duration int64 `json:"duration"` } type PlayerItem struct { PlayerItemUUID string `json:"playerItemUUID" binding:"omitempty,uuid4"` PlayerUUID string `json:"playerUUID" binding:"required,uuid4"` ItemUUID string `json:"itemUUID" binding:"required,uuid4"` Source string `json:"source"` GameSession string `json:"game_session" binding:"omitempty,uuid4"` Price big.Rat `json:"price"` AcquireTime time.Time `json:"acquire_time" spanner:"acquire_time"` ExpiresTime spanner.NullTime `json:"expires_time" spanner:"expires_time"` Visible bool `json:"visible"` } type Player struct { PlayerUUID string `json:"playerUUID" binding:"required,uuid4"` Updated time.Time `json:"updated"` AccountBalance big.Rat `json:"account_balance" spanner:"account_balance"` CurrentGame string `json:"current_game" binding:"omitempty,uuid4" spanner:"current_game"` } type PlayerLedger struct { PlayerUUID string `json:"playerUUID" binding:"required,uuid4"` Amount big.Rat `json:"amount"` GameSession string `json:"game_session" spanner:"game_session"` Source string `json:"source"` }

To create a trade order, a POST request is issued to the API endpoint /trades/sell. The required information is the playerItemUUID of the player_item to be sold, the lister, and the list_price.

Spanner mutations are chosen to create the trade order and mark the player_item as not visible. Doing this prevents the seller to post duplicate items for sale.

func (o *TradeOrder) Create(ctx context.Context, client spanner.Client) error { // insert into spanner _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { // get the Item to be listed pi, err := GetPlayerItem(ctx, txn, o.Lister, o.PlayerItemUUID) if err != nil { return err } // Set expires to 1 day by default if o.Expires.IsZero() { currentTime := time.Now() o.Expires = currentTime.Add(time.Hour * 24) } // Item is not visible or expired, so it can't be listed. That's an error if !validateSellOrder(pi) { errorMsg := fmt.Sprintf("Item (%s, %s) cannot be listed.", o.Lister, o.PlayerItemUUID) return errors.New(errorMsg) } // Initialize order values o.OrderUUID = generateUUID() o.Active = true // TODO: Have to set this by default since testing with emulator does not support 'DEFAULT' schema option // Insert the order var m []*spanner.Mutation cols := []string{"orderUUID", "playerItemUUID", "lister", "list_price", "trade_type", "expires", "active"} m = append(m, spanner.Insert("trade_orders", cols, []interface{}{o.OrderUUID, o.PlayerItemUUID, o.Lister, o.ListPrice, "sell", o.Expires, o.Active})) // Mark the item as invisible cols = []string{"playerUUID", "playerItemUUID", "visible"} m = append(m, spanner.Update("player_items", cols, []interface{}{o.Lister, o.PlayerItemUUID, false})) txn.BufferWrite(m) return nil }) if err != nil { return err } // return empty error on success return nil }

Prior to actually creating the order, the PlayerItem is validated to ensure it can be listed for sale. Primarily this means the PlayerItem is visible to the player, and that it has not expired.

// Validate that the order can be placed: Item is visible and not expired func validateSellOrder(pi PlayerItem) bool { // Item is not visible, can't be listed if !pi.Visible { return false } // item is expired. can't be listed if !pi.ExpiresTime.IsNull() && pi.ExpiresTime.Time.Before(time.Now()) { return false } // All validation passed. Item can be listed return true } Note: All items generated in this example application do not expire. Handling expired items would be a background process by another service, but this has not been implemented.

Making a purchase is done by a PUT request to the /trades/buy endpoint. The required information is the orderUUID and the buyer, which is the UUID of the player making the purchase.

Because of this complexity and the amount of changes, mutations are again chosen to purchase the order. The following operations are done in a single read-write transaction:

• Validate the order can be filled because it hasn't previously been filled and is not expired.

  // Validate that the order can be filled: Order is active and not expired func validatePurchase(o TradeOrder) bool { // Order is not active if !o.Active { return false } // order is expired. can't be filled if !o.Expires.IsZero() && o.Expires.Before(time.Now()) { return false } // All validation passed. Order can be filled return true }

• Retrieve the buyer information, and validate they can purchase the item. This means the buyer cannot be the same as the lister, and they have enough money.

  // Validate that a buyer can buy this item. func validateBuyer(b Player, o TradeOrder) bool { // Lister can't be the same as buyer if b.PlayerUUID == o.Lister { return false } // Big.rat returns -1 if Account_balance is less than price if b.AccountBalance.Cmp(&o.ListPrice) == -1 { return false } return true }

• Add the order's list_price to the lister's account balance, with a matching ledger entry. // models/trade_order.go // Buy an order func (o *TradeOrder) Buy(ctx context.Context, client spanner.Client) error { snip // Update seller's account balance lister.UpdateBalance(ctx, txn, o.ListPrice) snip } // models/players.go // Update a player's balance, and add an entry into the player ledger func (p *Player) UpdateBalance(ctx context.Context, txn *spanner.ReadWriteTransaction, newAmount big.Rat) error { // This modifies player's AccountBalance, which is used to update the player entry p.AccountBalance.Add(&p.AccountBalance, &newAmount) txn.BufferWrite([]*spanner.Mutation{ spanner.Update("players", []string{"playerUUID", "account_balance"}, []interface{}{p.PlayerUUID, p.AccountBalance}), spanner.Insert("player_ledger_entries", []string{"playerUUID", "amount", "game_session", "source", "entryDate"}, []interface{}{p.PlayerUUID, newAmount, p.CurrentGame, "tradepost", spanner.CommitTimestamp}), }) return nil }

• Subtract the order's list_price from the buyer's account balance, with a matching ledger entry.

  // Update buyer's account balance negAmount := o.ListPrice.Neg(&o.ListPrice) buyer.UpdateBalance(ctx, txn, *negAmount)

• Move the player_item to the new player by inserting a new instance of the game item with the game and buyer details into the PlayerItems table, and remove the lister's instance of the item.

  // Move an item to a new player, removes the item entry from the old player func (pi *PlayerItem) MoveItem(ctx context.Context, txn *spanner.ReadWriteTransaction, toPlayer string) error { fmt.Printf("Buyer: %s", toPlayer) txn.BufferWrite([]*spanner.Mutation{ spanner.Insert("player_items", []string{"playerItemUUID", "playerUUID", "itemUUID", "price", "source", "game_session"}, []interface{}{pi.PlayerItemUUID, toPlayer, pi.ItemUUID, pi.Price, pi.Source, pi.GameSession}), spanner.Delete("player_items", spanner.Key{pi.PlayerUUID, pi.PlayerItemUUID}), }) return nil }

• Updates the Orders entry to indicate the item has been filled and is no longer active. All together, the Buy function looks as follows:

  // Buy an order func (o *TradeOrder) Buy(ctx context.Context, client spanner.Client) error { // Fulfil the order _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error { // Get Order information err := o.getOrderDetails(ctx, txn) if err != nil { return err } // Validate order can be filled if !validatePurchase(*o) { errorMsg := fmt.Sprintf("Order (%s) cannot be filled.", o.OrderUUID) return errors.New(errorMsg) } // Validate buyer has the money buyer := Player{PlayerUUID: o.Buyer} err = buyer.GetBalance(ctx, txn) if err != nil { return err } if !validateBuyer(buyer, *o) { errorMsg := fmt.Sprintf("Buyer (%s) cannot purchase order (%s).", buyer.PlayerUUID, o.OrderUUID) return errors.New(errorMsg) } // Move money from buyer to seller (which includes ledger entries) var m []*spanner.Mutation lister := Player{PlayerUUID: o.Lister} err = lister.GetBalance(ctx, txn) if err != nil { return err } // Update seller's account balance lister.UpdateBalance(ctx, txn, o.ListPrice) // Update buyer's account balance negAmount := o.ListPrice.Neg(&o.ListPrice) buyer.UpdateBalance(ctx, txn, *negAmount) // Move item from seller to buyer, mark item as visible. pi, err := GetPlayerItem(ctx, txn, o.Lister, o.PlayerItemUUID) if err != nil { return err } pi.GameSession = buyer.CurrentGame // Moves the item from lister (current pi.PlayerUUID) to buyer pi.MoveItem(ctx, txn, o.Buyer) // Update order information cols := []string{"orderUUID", "active", "filled", "buyer", "ended"} m = append(m, spanner.Update("trade_orders", cols, []interface{}{o.OrderUUID, false, true, o.Buyer, time.Now()})) txn.BufferWrite(m) return nil }) if err != nil { return err } // return empty error on success return nil }

By default, the service is configured using environment variables. See the relevant section of the /src/golang/tradepost-service/config/config.go file.

With this information, it is now time to run the tradepost service.

Run the service

• Run the following command to run the service. Running the service will establish the service running on port 8083. This service has many of the same dependencies as the item-service, so new dependencies will not be downloaded.

cd ~/spanner-gaming-sample/src/golang/tradepost-service go run . &

Post an item

1. Test the service by issuing a GET request to retrieve a PlayerItem to sell:

curl http://localhost:8083/trades/player_items

Your output should resemble the following:

{ "PlayerUUID": "0111d981-7763-41bf-8fb1-ab400b004e3c", "PlayerItemUUID": "a4bd6c99-9cc1-483a-b633-dc2cc19bc078", "Price": "54.77" } Note: This item information will be used to create a sell order in the next step

2. Now, run the following command to post an item for sale by calling the /trades/sell endpoint. Fill in the <PlayerUUID> and <list price>:

curl http://localhost:8083/trades/sell \ --include \ --header "Content-Type: application/json" \ --request "POST" \ --data '{"lister": "<PlayerUUID>","playerItemUUID": "<PlayerItemUUID>", "list_price": "<some price higher than item's price>"}' Note: Be sure to fill in the PlayerUUID, PlayerItemUUID and list_price data from the GET request in the previous step.

Your output should resemble the following:

HTTP/1.1 201 Created Content-Type: application/json; charset=utf-8 Date: Sat, 24 Sep 2022 08:58:23 GMT Content-Length: 38

Now that your services are running, it's time to simulate players selling and buying on the trading post!

Task 10. Start trading

Now that the item and tradepost services are running, you can generate load using provided locust generators. Locust offers a web-interface for running the generators, but in this lab you will use the command line (--headless option).

In this section, you will simulate players signing up to play games and then run simulations for players to play games using the matchmaking service. These simulations will leverage the Locust Python framework to issue requests to our services' REST API.

Generate game items

First, you will want to generate items. The ./generators/item_generator.py(https://github.com/cloudspannerecosystem/spanner-gaming-sample/blob/master/generators/item_generator.py) file includes a task to create game items with random strings for names, and random price values:

# Generate random items class ItemLoad(HttpUser): def generateItemName(self): return ''.join(random.choices(string.ascii_lowercase + string.digits, k=32)) def generateItemValue(self): return str(decimal.Decimal(random.randrange(100, 10000))/100) @task def createItem(self): headers = {"Content-Type": "application/json"} data = {"item_name": self.generateItemName(), "item_value": self.generateItemValue()} self.client.post("/items", data=json.dumps(data), headers=headers)

1. Run the following command to call the item_generator.py file that will generate game items for 10 seconds (t=10s):

cd ~/spanner-gaming-sample locust -H http://127.0.0.1:8082 -f ./generators/item_generator.py --headless -u=1 -r=1 -t=10s Note: Over the course of running the generator, the locust command will output statistics of the requests being made, including request/s, failure/s, and latency information.

Players acquire items and money

Next, let's have the players acquire items and money so that they can participate on the trading post. To do this, the ./generators/game_server.py file provides tasks to retrieve game items to assign to players as well as random amounts of currency.

# Players generate items and money at 5:2 ratio. We don't want to devalue the currency! class GameLoad(HttpUser): def on_start(self): self.getItems() def getItems(self): headers = {"Content-Type": "application/json"} r = requests.get(f"{self.host}/items", headers=headers) global itemUUIDs itemUUIDs = json.loads(r.text) def generateAmount(self): return str(round(random.uniform(1.01, 49.99), 2)) @task(2) def acquireMoney(self): headers = {"Content-Type": "application/json"} # Get a random player that's part of a game, and update balance with self.client.get("/players", headers=headers, catch_response=True) as response: try: data = {"playerUUID": response.json()["playerUUID"], "amount": self.generateAmount(), "source": "loot"} self.client.put("/players/balance", data=json.dumps(data), headers=headers) except json.JSONDecodeError: response.failure("Response could not be decoded as JSON") except KeyError: response.failure("Response did not contain expected key 'playerUUID'") @task(5) def acquireItem(self): headers = {"Content-Type": "application/json"} # Get a random player that's part of a game, and add an item with self.client.get("/players", headers=headers, catch_response=True) as response: try: itemUUID = itemUUIDs[random.randint(0, len(itemUUIDs)-1)] data = {"playerUUID": response.json()["playerUUID"], "itemUUID": itemUUID, "source": "loot"} self.client.post("/players/items", data=json.dumps(data), headers=headers) except json.JSONDecodeError: response.failure("Response could not be decoded as JSON") except KeyError: response.failure("Response did not contain expected key 'playerUUID'") Note: Only players currently in 'open games' can acquire items and money. If there are no open games, you must generate some with the match_server.py generator.

2. Run the following command to allow players to acquire items and money for 30 seconds:

cd generators locust -H http://127.0.0.1:8082 -f game_server.py --headless -u=1 -r=1 -t=30s

Players buying and selling on the trading post

Now that players have items and the money to buy items, they can begin using the trading post!

The trading_server.py generator file provides tasks to create sell orders and fulfill those orders.

# Players can sell and buy items class TradeLoad(HttpUser): def itemMarkup(self, value): f = float(value) return str(f*1.5) @task def sellItem(self): headers = {"Content-Type": "application/json"} # Get a random item with self.client.get("/trades/player_items", headers=headers, catch_response=True) as response: try: playerUUID = response.json()["PlayerUUID"] playerItemUUID = response.json()["PlayerItemUUID"] list_price = self.itemMarkup(response.json()["Price"]) data = {"lister": playerUUID, "playerItemUUID": playerItemUUID, "list_price": list_price} self.client.post("/trades/sell", data=json.dumps(data), headers=headers) except json.JSONDecodeError: response.failure("Response could not be decoded as JSON") except KeyError: response.failure("Response did not contain expected key 'playerUUID'") @task def buyItem(self): headers = {"Content-Type": "application/json"} # Get a random item with self.client.get("/trades/open", headers=headers, catch_response=True) as response: try: orderUUID = response.json()["OrderUUID"] buyerUUID = response.json()["BuyerUUID"] data = {"orderUUID": orderUUID, "buyer": buyerUUID} self.client.put("/trades/buy", data=json.dumps(data), headers=headers) except json.JSONDecodeError: response.failure("Response could not be decoded as JSON") except KeyError: response.failure("Response did not contain expected key 'playerUUID'")

• Run the following command to allow players to list items they've acquired for sale, and other players to purchase those item for 10 seconds:

locust -H http://127.0.0.1:8083 -f trading_server.py --headless -u=1 -r=1 -t=10s

Feel free to modify the time spent creating players and playing games, as well as the number of concurrent users (-u). After the simulation, you will want to check on various statistics by querying Spanner.

Task 11. Retrieve trade statistics

Now that we have simulated players acquiring money and items, then selling those items on the trading post, let's check some statistics. To do this, use Cloud Console to issue query requests to Spanner.

1. From the Navigation Menu, under the Databases section click Spanner.

2. Click on the cloudspanner-gaming instance.

3. Under Databases click the sample-game database you created.

4. On the left navigation pane, click Spanner studio.

You are now ready to run some queries on Spanner to check the game statistics.

Checking open vs fulfilled trade orders

When a TradeOrder is purchased on the trading post, the filled metadata field is updated.

• Run the following query to check how many orders are open and how many are filled:

-- Open vs Filled Orders SELECT Type, NumTrades FROM (SELECT "Open Trades" as Type, count(*) as NumTrades FROM trade_orders WHERE active=true UNION ALL SELECT "Filled Trades" as Type, count(*) as NumTrades FROM trade_orders WHERE filled=true ) Result: Type NumTrades Open Trades 0 Closed Trades 175 Note: Exact results of open vs closed trades will be different.

Checking player account balance and number of items

A player is playing a game if their current_game column is set. Otherwise, they are not currently playing a game.

• To get to top 10 players currently playing games with the most items, with their account_balance, run the following query:

SELECT playerUUID, account_balance, (SELECT COUNT(*) FROM player_items WHERE playerUUID=p.PlayerUUID) AS numItems, current_game FROM players AS p WHERE current_game IS NOT NULL ORDER BY numItems DESC LIMIT 10;

Great! In this section, you reviewed various statistics of player and trade orders by using the Cloud Console to query Spanner.

Congratulations!

Congratulations, you have successfully deployed a sample game on Spanner! In this lab, you created a Spanner instance, deployed a Profile service written in Go to handle player signup, and deployed a Matchmaking service written in Go to assign players to games, determine winners and update players' game statistics. You then completed setting up two services to handle game item generation and players acquiring items to be sold on the trading post.

These code samples should give you a better understanding of how Cloud Spanner consistency within transactions works for both DML and Spanner mutations. Feel free to use the generators provided to explore scaling Spanner.

Next Steps / Learn More

In this lab, you have been introduced to various topics of working with Spanner using the golang driver. It should give you a better foundation to understand critical concepts such as:

• Schema design
• DML vs Mutations
• Working with Cloud Spanner using Golang

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated October 18, 2023

Lab Last Tested October 18, 2023

Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.