Cloud Spanner ゲーム開発の開始

gcloud auth list 

Credentialed accounts:  - <myaccount>@<mydomain>.com (active) 

gcloud config list project 

[core] project = <PROJECT_ID> 

gcloud config set project <PROJECT_ID> 

echo $GOOGLE_CLOUD_PROJECT 

<PROJECT_ID> 

git clone https://github.com/cloudspannerecosystem/spanner-gaming-sample.git cd spanner-gaming-sample/  # Check out v0.1.0 release git checkout tags/v0.1.0 -b v0.1.0-branch 

Cloning into 'spanner-gaming-sample'... *snip* Switched to a new branch 'v0.1.0-branch' 

python -V 

Python 3.9.12 

pip3 install -r requirements.txt 

Collecting locust==2.11.1 *snip* Successfully installed ConfigArgParse-1.5.3 Flask-BasicAuth-0.2.0 Flask-Cors-3.0.10 brotli-1.0.9 gevent-21.12.0 geventhttpclient-2.0.2 greenlet-1.1.3 locust-2.11.1 msgpack-1.0.4 psutil-5.9.2 pyzmq-22.3.0 roundrobin-0.0.4 zope.event-4.5.0 zope.interface-5.4.0 

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

/home/<user>/.local/bin/locust 

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

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

func main() {    configuration, _ := config.NewConfig()     router := gin.Default()    router.SetTrustedProxies(nil)     router.Use(setSpannerConnection(configuration))     router.POST("/players", createPlayer)    router.GET("/players", getPlayerUUIDs)    router.GET("/players/:id", getPlayerByID)     router.Run(configuration.Server.URL()) } 

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

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()    } } 

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

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 } 

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 } 

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 } 

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

[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 

curl http://localhost:8080/players \     --include \     --header "Content-Type: application/json" \     --request "POST" \     --data '{"email": "[email protected]","password": "s3cur3P@ss","player_name": "Test Player"}' 

HTTP/1.1 201 Created Content-Type: application/json; charset=utf-8 Date: <date> 18:55:08 GMT Content-Length: 38  "506a1ab6-ee5b-4882-9bb1-ef9159a72989" 

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()) } 

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

// 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 } 

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 } 

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

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

[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 

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

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" 

curl http://localhost:8081/games/close \     --include \     --header "Content-Type: application/json" \     --data '{"gameUUID": "f45b0f7f-405b-4e67-a3b8-a624e990285d"}' \     --request "PUT" 

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" 

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

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

cd ~/spanner-gaming-sample locust -H http://127.0.0.1:8080 -f ./generators/authentication_server.py --headless -u=2 -r=2 -t=30s 

from locust import HttpUser, task from locust.exception import RescheduleTask  import json  class GameMatch(HttpUser):    def on_start(self):        global openGames        openGames = []     @task(2)    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) 

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

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 ) 

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 ) 

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