《MMO 服务器开发》9.1 角色数据模型

// snippet: internal/store/model.go 中的主表与扩展表类型
type Player struct {
	ID      uint64
	Account string
	Name    string
	Level   int
	PosX    float64
	PosY    float64
	Gold    int64
	Version int64 // 9.2 的乐观锁载体，先埋下
}

type Item struct {
	InstID   uint64
	PlayerID uint64
	ItemID   uint32
	Count    int32
}

CREATE SCHEMA IF NOT EXISTS aegis_test_ch09;

CREATE TABLE IF NOT EXISTS aegis_test_ch09.player (
    player_id  BIGINT PRIMARY KEY,
    account    TEXT   NOT NULL,
    name       TEXT   NOT NULL,
    level      INT    NOT NULL DEFAULT 1,
    pos_x      DOUBLE PRECISION NOT NULL DEFAULT 0,
    pos_y      DOUBLE PRECISION NOT NULL DEFAULT 0,
    gold       BIGINT NOT NULL DEFAULT 0,
    version    BIGINT NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS aegis_test_ch09.player_item (
    inst_id   BIGINT PRIMARY KEY,
    player_id BIGINT NOT NULL,
    item_id   INT    NOT NULL,
    count     INT    NOT NULL
);
CREATE INDEX IF NOT EXISTS player_item_player_idx
    ON aegis_test_ch09.player_item (player_id);

CREATE TABLE IF NOT EXISTS aegis_test_ch09.player_quest (
    player_id BIGINT NOT NULL,
    quest_id  INT    NOT NULL,
    state     SMALLINT NOT NULL DEFAULT 0,
    PRIMARY KEY (player_id, quest_id)
);

CREATE TABLE IF NOT EXISTS aegis_test_ch09.player_attr (
    player_id BIGINT NOT NULL,
    key       TEXT   NOT NULL,
    value     BIGINT NOT NULL,
    PRIMARY KEY (player_id, key)
);

// file: internal/store/model_test.go
// tier: L2
// verify: go test ./internal/store/ -run TestPlayerDataModel -v

package store

import (
	"context"
	"testing"
)

func TestPlayerDataModel(t *testing.T) {
	pool := testPool(t)
	defer pool.Close()
	resetSchema(t, pool)
	ctx := context.Background()

	p := &Player{ID: 10001, Account: "acc-10001", Name: "晨光",
		Level: 12, PosX: 128, PosY: 64, Gold: 5000, Version: 1}
	if err := SavePlayer(ctx, pool, p); err != nil {
		t.Fatal(err)
	}
	if err := SaveItems(ctx, pool, []Item{
		{InstID: 9001, PlayerID: p.ID, ItemID: 1001, Count: 1},
		{InstID: 9002, PlayerID: p.ID, ItemID: 2005, Count: 20},
		{InstID: 9003, PlayerID: p.ID, ItemID: 3007, Count: 3},
	}); err != nil {
		t.Fatal(err)
	}
	if err := SaveQuests(ctx, pool, []Quest{
		{PlayerID: p.ID, QuestID: 1, State: 2},
		{PlayerID: p.ID, QuestID: 7, State: 0},
	}); err != nil {
		t.Fatal(err)
	}
	if err := SaveAttrs(ctx, pool, []Attr{
		{PlayerID: p.ID, Key: "atk", Value: 120},
		{PlayerID: p.ID, Key: "hp", Value: 3000},
	}); err != nil {
		t.Fatal(err)
	}

	got, err := LoadPlayer(ctx, pool, p.ID)
	if err != nil {
		t.Fatal(err)
	}
	if got.Level != 12 || got.Gold != 5000 || got.PosX != 128 || got.Name != "晨光" {
		t.Fatalf("hot row mismatch: %+v", got)
	}
	gi, _ := LoadItems(ctx, pool, p.ID)
	gq, _ := LoadQuests(ctx, pool, p.ID)
	ga, _ := LoadAttrs(ctx, pool, p.ID)
	if len(gi) != 3 || len(gq) != 2 || len(ga) != 2 {
		t.Fatalf("extension counts: items=%d quests=%d attrs=%d", len(gi), len(gq), len(ga))
	}

	// The split is the point: one hot row per player, one row per item. The
	// hot table stays narrow no matter how big the bag grows.
	nc, _ := CountRows(ctx, pool, "player")
	ni, _ := CountRows(ctx, pool, "player_item")
	if nc != 1 || ni != 3 {
		t.Fatalf("player rows=%d (want 1), player_item rows=%d (want 3)", nc, ni)
	}
	t.Logf("model ok: 1 hot row + 3 items + 2 quests + 2 attrs")
}