Cloud Spanner Game Trading Post

1. Introduction

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 be creating two Go services that interact with a regional Spanner database to enable players to acquire items and money (item-service), and then list items on the trading post for other players to purchase (tradepost-service).

This lab is dependent on the Cloud Spanner Getting Started with Games Development codelab to have generated players and games using the profile-service and the matchmaking-service.

904c5193ee27626a.png

Next you'll generate data leveraging the Python load framework Locust.io to simulate players acquiring 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 players' account balances and number of items, and some statistics about trade orders that are open or have been filled.

Finally, you will clean up the resources that were created in this lab.

What you'll build

As part of this lab, you will:

  • Re-use the Spanner instance from the Cloud Spanner Getting Started with Games Development.
  • Deploy a Item service written in Go to handle players acquiring items and money
  • Deploy a Trading Post service written in Go to simulate players listing items for sale, and other players purchasing those items.

What you'll learn

  • How to use read-write transactions to ensure consistency for data changes
  • How to leverage DML and Spanner mutations to modify data

What you'll need

2. Setup and Requirements

Complete the Cloud Spanner Getting Started with Games Development codelab

Complete the Cloud Spanner Getting Started with Games Development codelab. This is required to get a dataset of players and games. Players and games are needed to acquire items and money, which in turn is used to list items for sale and purchase items from the trading post.

Configure environment variables in Cloud Shell

Open up Cloud Shell by clicking Activate Cloud Shell gcLMt5IuEcJJNnMId-Bcz3sxCd0rZn7IzT_r95C8UZeqML68Y1efBG_B0VRp7hc7qiZTLAF-TXD7SsOadxn8uadgHhaLeASnVS3ZHK39eOlKJOgj9SJua_oeGhMxRrbOg3qigddS2A (it should only take a few moments to provision and connect to the environment since you have done this previously).

JjEuRXGg0AYYIY6QZ8d-66gx_Mtc-_jDE9ijmbXLJSAXFvJt-qUpNtsBsYjNpv2W6BQSrDc1D-ARINNQ-1EkwUhz-iUK-FUCZhJ-NtjvIEx9pIkE-246DomWuCfiGHK78DgoeWkHRw

Screen Shot 2017-06-14 at 10.13.43 PM.png

Once connected to Cloud Shell, you should see that you are already authenticated and that the project is already set to your PROJECT_ID.

Set the SPANNER environment variables in Cloud Shell

export SPANNER_PROJECT_ID=$GOOGLE_CLOUD_PROJECT
export SPANNER_INSTANCE_ID=cloudspanner-gaming
export SPANNER_DATABASE_ID=sample-game

Create the schema

Now that your database is created, you can define the schema in the sample-game database.

This lab will create four new tables: game_items, player_items, player_ledger_entries and trade_orders.

402ce3310dd7141a.png

Player item relationships

5e1229d85b75906.png

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.

To create the schema, you will click on the Write DDL button in the Cloud Console:

e9ad931beb1d96b.png

Here, you will input the schema definition from the schema/trading.sql file:

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);

Click the ‘Submit' button to modify the schema, and wait until the schema update completes:

94f44b2774bce914.png

Next up

Next, you will deploy the item service.

3. Deploy the item service

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.

6c29a0831b5f588d.png

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.SetTrustedProxies(nil)
   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 codelab.

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
}

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

Running the service will download dependencies, and establish the service running on port 8082:

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

Command 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

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"}'

Command output:

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8
Date: <Date>
Content-Length: 38

"aecde380-0a79-48c0-ab5d-0da675d3412c"

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, it is: aecde380-0a79-48c0-ab5d-0da675d3412c.

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

curl http://localhost:8082/players

Command output:

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

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"}'

Command output:

Content-Type: application/json; charset=utf-8
Date: <Date>
Content-Length: 369

{
    "playerItemUUID": "a42b1899-4509-4fce-9958-265d2a2838a0",
    "playerUUID": "b74cc194-87b0-4a55-a67f-0f0742ef6352",
    "itemUUID": "109ec745-9906-402b-9d03-ca7153a10312",
    "source": "loot",
    "game_session": "7b97fa85-5658-4ded-a962-4c09269a0a79",
    "price": {},
    "acquire_time": "0001-01-01T00:00:00Z",
    "expires_time": null,
    "visible": false
}

Summary

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

Next Steps

In the next step, you will deploy the tradepost service.

4. Deploy the tradepost service

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.

c32372f9def89a4a.png

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.GET("/trades/open", getOpenOrder)
   router.PUT("/trades/buy", purchaseOrder)
   
   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 from posting 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
}

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.
// models/player_items.go
// 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.

 func NewConfig() (Config, error) {
   *snip*
   // Server defaults
   viper.SetDefault("server.host", "localhost")
   viper.SetDefault("server.port", 8083)

   // 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:8083 to avoid conflicts with other services*.*

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

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 . &

Command 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    /trades/player_items      --> main.getPlayerItem (4 handlers)
[GIN-debug] POST   /trades/sell              --> main.createOrder (4 handlers)
[GIN-debug] GET    /trades/open              --> main.getOpenOrder (4 handlers)
[GIN-debug] PUT    /trades/buy               --> main.purchaseOrder (4 handlers)
[GIN-debug] Listening and serving HTTP on localhost:8083

Post an item

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

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

Command output:

{
    "PlayerUUID": "b74cc194-87b0-4a55-a67f-0f0742ef6352",
    "PlayerItemUUID": "a42b1899-4509-4fce-9958-265d2a2838a0",
    "Price": "3.14"
}

Now, let's post an item for sale by calling the /trades/sell endpoint

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>"}'

Command output:

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8
Date: <Date> 
Content-Length: 38

"282ea691-b956-4c4c-95ff-f461d6415651"

Summary

In this step, you deployed the tradepost-service to handle creating sell orders. This service also handles the ability to buy those orders.

Next Steps

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

5. 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).

Generate game items

First, you will want to generate items. The ./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)

The following command calls 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

Command output:

*snip*
/INFO/locust.main: --run-time limit reached. Stopping Locust
/INFO/locust.main: Shutting down (exit code 0)
 Name                                                                              # reqs      # fails  |     Avg     Min     Max  Median  |   req/s failures/s
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 POST /items                                                                          606     0(0.00%)  |      16      12     161      15  |   60.61    0.00
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregated                                                                           606     0(0.00%)  |      16      12     161      15  |   60.61    0.00

Response time percentiles (approximated)
 Type     Name                                                                                  50%    66%    75%    80%    90%    95%    98%    99%  99.9% 99.99%   100% # reqs
--------|--------------------------------------------------------------------------------|---------|------|------|------|------|------|------|------|------|------|------|------|
 POST     /items                                                                                 15     16     16     17     18     19     21     34    160    160    160    606
--------|--------------------------------------------------------------------------------|---------|------|------|------|------|------|------|------|------|------|------|------|
 None     Aggregated                                                                             15     16     16     17     18     19     21     34    160    160    160    606

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'")

This command will allow players to acquire items and money for 60 seconds:

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

Command output:

*snip*
dev-machine/INFO/locust.main: --run-time limit reached. Stopping Locust
dev-machine/INFO/locust.main: Shutting down (exit code 0)
 Name                                                                              # reqs      # fails  |     Avg     Min     Max  Median  |   req/s failures/s
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 GET /players                                                                         231     0(0.00%)  |      14       9      30      13  |   23.16    0.00
 PUT /players/balance                                                                  53     0(0.00%)  |      33      30      39      34  |    5.31    0.00
 POST /players/items                                                                  178     0(0.00%)  |      26      22      75      26  |   17.85    0.00
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregated                                                                           462     0(0.00%)  |      21       9      75      23  |   46.32    0.00

Response time percentiles (approximated)
 Type     Name                                                                                  50%    66%    75%    80%    90%    95%    98%    99%  99.9% 99.99%   100% # reqs
--------|--------------------------------------------------------------------------------|---------|------|------|------|------|------|------|------|------|------|------|------|
 GET      /players                                                                               13     16     17     17     19     20     21     23     30     30     30    231
 PUT      /players/balance                                                                       34     34     35     35     36     37     38     40     40     40     40     53
 POST     /players/items                                                                         26     27     27     27     28     29     34     53     76     76     76    178
--------|--------------------------------------------------------------------------------|---------|------|------|------|------|------|------|------|------|------|------|------|
 None     Aggregated                                                                             23     26     27     27     32     34     36     37     76     76     76    462

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 ./generators/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"])

  # Currently don't have any items that can be sold, retry
               if playerItemUUID == "":
                   raise RescheduleTask()


               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"]

                    # Currently don't have any buyers that can fill the order, retry
               if buyerUUID == "":
                   raise RescheduleTask()

               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'")

This command will 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 ./generators/trading_server.py --headless -u=1 -r=1 -t=10s

Command output:

*snip*
 Name                                                                              # reqs      # fails  |     Avg     Min     Max  Median  |   req/s failures/s
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 PUT /trades/buy                                                                       20    5(25.00%)  |      43      10      78      43  |    2.07    0.52
 GET /trades/open                                                                      20     0(0.00%)  |     358       7     971     350  |    2.07    0.00
 GET /trades/player_items                                                              20     0(0.00%)  |      49      35     113      41  |    2.07    0.00
 POST /trades/sell                                                                     20     0(0.00%)  |      29      21     110      24  |    2.07    0.00
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregated                                                                            80     5(6.25%)  |     120       7     971      42  |    8.29    0.52

Response time percentiles (approximated)
 Type     Name                                                                                  50%    66%    75%    80%    90%    95%    98%    99%  99.9% 99.99%   100% # reqs
--------|--------------------------------------------------------------------------------|---------|------|------|------|------|------|------|------|------|------|------|------|
 PUT      /trades/buy                                                                            43     45     49     50     71     78     78     78     78     78     78     20
 GET      /trades/open                                                                          360    500    540    550    640    970    970    970    970    970    970     20
 GET      /trades/player_items                                                                   43     55     57     59     72    110    110    110    110    110    110     20
 POST     /trades/sell                                                                           24     25     25     27     50    110    110    110    110    110    110     20
--------|--------------------------------------------------------------------------------|---------|------|------|------|------|------|------|------|------|------|------|------|
 None     Aggregated                                                                             42     50     71    110    440    550    640    970    970    970    970     80

Summary

In this step, 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).

Next Steps

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

6. 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.

b5e3154c6f7cb0cf.png

Checking open vs fulfilled trade orders

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

This query will all you 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

159

Filled Trades

454

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

, use this 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;

Result:

playerUUID

account_balance

numItems

current_game

04d14288-a7c3-4515-9296-44eeae184d6d

1396.55

201

79f24cd4-102d-4d7d-bbe8-be69d364ebbf

03836948-d591-4967-a8a8-80506454916d

2005.085

192

053c353e-e56d-4b2e-9431-76eedf58bea5


snip


snip


snip


snip

Summary

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

Next Steps

Next, it's time to clean up!

7. Cleaning up

After all the fun playing with Spanner we need to clean up our playground. Luckily this is an easy step, just go into the Cloud Spanner section of the Cloud Console and delete the instance we created for this codelab.

8. Congratulations!

Congratulations, you have successfully deployed a sample game on Spanner

What's next?

In this lab, you have 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.