Creating Game Summary Tables

July 17, 2024 - 4 mins read

I need to implement behavior to track all games in which a user is a player. My initial idea is to have the game session process write summary updates to the database as the game progresses which includes the list of players and their status in the game. I will create a table to track game session summaries and an associated table which contains a row for each player in a game.

Ecto migrations

Looking for opportunities to integrate AI with my workflow, I prompt ChatGPT to write out a migration file for me given my requirements including using a composite primary key for the player summary table. It spits out some code:

defmodule MyApp.Repo.Migrations.CreateGameSessionSummaries do
  use Ecto.Migration

  def change do
    # Create Game Session Summary table
    create table(:game_session_summaries) do
      add :game_name, :string
      add :game_status, :string
      add :latest_round, :integer
      timestamps()
    end

    # Create Player Game Session table
    create table(:player_game_sessions, primary_key: false) do
      add :game_session_id, references(:game_session_summaries, on_delete: :delete_all), null: false
      add :user_id, :integer, null: false  # Assuming user_id is an integer
      add :player_status, :string

      timestamps()
    end

    # Create composite primary key
    create primary_key(:player_game_sessions, [:game_session_id, :user_id])

    # Create unique index
    create unique_index(:player_game_sessions, [:game_session_id, :user_id])
  end
end

This is a good reminder to diligently review any AI generated code. I notice it created a unique index for the same columns used in the composite primary key. I called out the AI on this and it acknowledged its mistake.

Yes, including the unique index in addition to the composite primary key was unnecessary. The composite primary key already enforces uniqueness for the specified columns, so a separate unique index is redundant. You can safely omit the unique index when using a composite primary key. Thank you for catching that!

It also made up the function primary_key which does not exist anywhere in the Ecto docs. I called this out to the AI and it gave me a very disappointing response:

The error undefined function primary_key occurs because Ecto.Migration does not have a built-in function called primary_key. Instead, you should define composite primary keys using the create statement with the primary_key: false option and use create index for setting up unique constraints.

I know composite primary keys can be created in Ecto migrations, but the AI does not know how to do it. Unfortunately, ChatGPT gives very confident responses even when the information provided is wrong. This is another reminder that code generated by AI in its current state should be never be blindly trusted.

However, the rest of the migration code provided good enough scaffolding for me to finish the code correctly. The Ecto docs for the add/3 function state how to create composite keys:

:primary_key - when true, marks this field as the primary key. If multiple fields are marked, a composite primary key will be created.

I implement a few fixes to the module and run the migrations for my local dev and test environments.

defmodule Minotaur.Repo.Migrations.AddGameSessionSummaryTables do
  use Ecto.Migration

  def change do
    create table(:game_session_summaries) do
      add :game_id, :string, size: 8, null: false
      add :game_status, :string, size: 40, null: false
      add :latest_round, :integer, null: false
      timestamps()
    end

    create table(:player_game_session_summaries, primary_key: false) do
      add :game_session_id, references(:game_session_summaries, on_delete: :delete_all),
        primary_key: true,
        null: false

      add :user_id, references(:users, on_delete: :delete_all), primary_key: true, null: false
      add :player_status, :string, size: 40, null: false

      timestamps()
    end
  end
end

Ecto schemas

I add schema modules for each table.

defmodule Minotaur.GameEngine.GameSessionSummary do
  @moduledoc false

  use Ecto.Schema

  import Ecto.Changeset

  alias Minotaur.GameEngine.PlayerGameSessionSummary

  schema "game_session_summaries" do
    field :game_id, :string
    field :game_status, :string
    field :latest_round, :integer

    timestamps()

    has_many :player_game_session_summaries, PlayerGameSessionSummary,
      foreign_key: :game_session_id
  end

  def changeset(game_session_summary, attrs) do
    game_session_summary
    |> cast(attrs, [:game_id, :game_status, :latest_round])
    |> validate_required([:game_id, :game_status, :latest_round])
  end
end

defmodule Minotaur.GameEngine.PlayerGameSessionSummary do
  @moduledoc false

  use Ecto.Schema

  import Ecto.Changeset

  alias Minotaur.Accounts.User
  alias Minotaur.GameEngine.GameSessionSummary

  @primary_key false

  schema "player_game_session_summaries" do
    field :player_status, :string

    belongs_to :game_session_summary, GameSessionSummary,
      foreign_key: :game_session_id,
      primary_key: true

    belongs_to :user, User, foreign_key: :user_id, primary_key: true

    timestamps()
  end

  def changeset(player_summary, attrs) do
    player_summary
    |> cast(attrs, [:game_session_id, :user_id, :player_status])
    |> validate_required([:game_session_id, :user_id, :player_status])
  end
end

Next steps will be to update get_active_games_for_user to query records from the database and to create these records when game sessions are started.