DD Poker Database Schema for Players, Hands, and Game History: Complete Guide

DD Poker persists player profiles, game sessions, and individual hand results across three MySQL tables—wan_profile, wan_game, and wan_history—defined in tools/db/create_tables.sql and mapped to JPA entities in the server-side Java code.

DD Poker, an open-source online poker platform, stores all persistent player and game data in a relational MySQL schema designed for real-time tournaments and cash games. The database centers on three core tables that track registered users, active game sessions, and detailed hand-by-hand history. This article examines the exact schema definitions, column specifications, and Java Persistence API (JPA) mappings used in the dougdonohoe/ddpoker repository.

Core Database Tables

The persistence layer is defined in tools/db/create_tables.sql and consists of three interconnected tables with foreign-key constraints ensuring referential integrity between players, games, and historical results.

Player Profiles: wan_profile

The wan_profile table stores one row per registered player, serving as the identity and authentication hub for the platform.

• Primary Key: wpr_id (INT, auto-increment)
• Identity Columns: wpr_name (VARCHAR 32), wpr_license_key (VARCHAR 55), wpr_email (VARCHAR 255)
• Security: wpr_password (VARCHAR 255) stores hashed credentials
• Status Flags: wpr_is_activated (BOOL), wpr_is_retired (BOOL)
• Audit: wpr_create_date (DATETIME), wpr_modify_date (DATETIME)

This table is accessed via the OnlineProfileImplJpa DAO in code/pokerserver/src/main/java/com/donohoedigital/games/poker/dao/impl/OnlineProfileImplJpa.java.

Game Sessions: wan_game

The wan_game table records every poker game instance, whether tournaments or cash games, tracking lifecycle from creation to completion.

• Primary Key: wgm_id (INT, auto-increment)
• Licensing: wgm_license_key (VARCHAR 55) links to the host player
• Network: wgm_url (VARCHAR 64), wgm_host_player (VARCHAR 64)
• Lifecycle: wgm_start_date (DATETIME NULL), wgm_end_date (DATETIME NULL)
• Configuration: wgm_mode (TINYINT) distinguishes game types, wgm_tournament_data (TEXT) stores JSON configuration
• Audit: wgm_create_date, wgm_modify_date

Hand History: wan_history

The wan_history table captures per-hand results for every participant, functioning as the central game history repository with foreign keys linking to both profiles and games.

• Primary Key: whi_id (INT, auto-increment)
• Foreign Keys: whi_game_id (INT) → wan_game.wgm_id, whi_profile_id (INT) → wan_profile.wpr_id
• Game Context: whi_tournament_name (VARCHAR 255), whi_num_players (INT), whi_is_ended (BOOL)
• Player Data: whi_player_name (VARCHAR 32), whi_player_type (TINYINT), whi_finish_place (SMALLINT)
• Financials: whi_prize (DECIMAL), whi_buy_in (DECIMAL), whi_total_rebuy (DECIMAL), whi_total_add_on (DECIMAL)
• Statistics: whi_rank_1 (DECIMAL(10,3)), whi_disco (DECIMAL(10,0)) tracks disconnections
• Timing: whi_end_date (DATETIME) with indexed columns for fast history lookups

Indexes on whi_end_date, whi_player_type, and whi_is_ended optimize query performance for history retrieval.

JPA Entity Mappings

The server-side Java implementation maps these tables to JPA entities using Hibernate annotations, with TournamentHistory representing the wan_history table.

TournamentHistory Entity

Located in code/pokerengine/src/main/java/com/donohoedigital/games/poker/model/TournamentHistory.java, this entity mirrors the wan_history schema with strict field-to-column mappings:

@Entity
@Table(name = "wan_history")
public class TournamentHistory implements BaseModel<Long>, DataMarshal, SimpleXMLEncodable {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "whi_id", nullable = false) 
    private Long id;

    @Column(name = "whi_buy_in", nullable = false) 
    private int buyin;
    
    @Column(name = "whi_total_rebuy", nullable = false) 
    private int rebuys;
    
    @Column(name = "whi_total_add_on", nullable = false) 
    private int addons;
    
    @Column(name = "whi_finish_place", nullable = false) 
    private int place;
    
    @Column(name = "whi_prize", nullable = false) 
    private int prize;
    
    @Column(name = "whi_player_name", nullable = false) 
    private String playerName;
    
    @Column(name = "whi_player_type", nullable = false) 
    private int playerType;
    
    @Column(name = "whi_end_date", nullable = false) 
    @Temporal(TemporalType.TIMESTAMP) 
    private Date endDate;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "whi_game_id", nullable = false, updatable = false)
    private OnlineGame onlineGame;          // FK → wan_game

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "whi_profile_id", nullable = false, updatable = false)
    private OnlineProfile profile;          // FK → wan_profile
}

The @ManyToOne relationships enforce the foreign-key constraints at the application layer, using lazy fetching to prevent unnecessary joins during standard queries.

Working with the Schema

The following JDBC examples demonstrate how to interact with the three core tables programmatically, matching the exact column names and data types defined in tools/db/create_tables.sql.

Inserting a New Player

To register a player in wan_profile, insert values corresponding to the authentication and status columns:

String sql = """
    INSERT INTO wan_profile
      (wpr_name, wpr_license_key, wpr_email, wpr_password,
       wpr_is_activated, wpr_is_retired, wpr_create_date, wpr_modify_date)
    VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())
    """;

try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, "Alice");
    ps.setString(2, "LIC-12345");
    ps.setString(3, "alice@example.com");
    ps.setString(4, hashPassword("secret"));
    ps.setBoolean(5, true);
    ps.setBoolean(6, false);
    ps.executeUpdate();
    ResultSet rs = ps.getGeneratedKeys();
    if (rs.next()) {
        long playerId = rs.getLong(1);   // wpr_id
    }
}

Creating a Game Session

New tournaments or cash games are inserted into wan_game with the license key and mode specified:

String sql = """
    INSERT INTO wan_game
      (wgm_license_key, wgm_url, wgm_host_player,
       wgm_create_date, wgm_modify_date, wgm_mode, wgm_tournament_data)
    VALUES (?, ?, ?, NOW(), NOW(), ?, ?)
    """;

PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "LIC-12345");
ps.setString(2, "game123");
ps.setString(3, "HostPlayer");
ps.setInt(4, 1);                         // wgm_mode – tournament type
ps.setString(5, "{\"type\":\"tournament\",\"buyin\":10}");
ps.executeUpdate();

Recording Hand Results

Each hand completion inserts a row into wan_history, linking the player and game via foreign keys while capturing financial and placement data:

String sql = """
    INSERT INTO wan_history
      (whi_game_id, whi_tournament_name, whi_num_players, whi_is_ended,
       whi_profile_id, whi_player_name, whi_player_type,
       whi_finish_place, whi_prize, whi_buy_in,
       whi_total_rebuy, whi_total_add_on, whi_rank_1,
       whi_disco, whi_end_date)
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    """;

PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong   (1, gameId);
ps.setString (2, "Spring Championship");
ps.setInt    (3, 9);
ps.setBoolean(4, true);                     // game finished
ps.setLong   (5, playerId);
ps.setString (6, "Alice");
ps.setInt    (7, TournamentHistory.PLAYER_TYPE_ONLINE);
ps.setInt    (8, 1);                         // 1st place
ps.setInt    (9, 1500);                      // prize
ps.setInt   (10, 30);                        // buy-in
ps.setInt   (11, 20);                        // rebuy total
ps.setInt   (12, 10);                        // add-on total
ps.setDouble(13, 0.985);                    // rank-1 value
ps.setInt   (14, 0);                         // disconnects
ps.setTimestamp(15, new Timestamp(System.currentTimeMillis()));
ps.executeUpdate();

Key Implementation Files

The complete persistence layer for players, hands, and game history is implemented across these source files:

• tools/db/create_tables.sql – SQL DDL defining wan_profile, wan_game, and wan_history
• code/pokerengine/src/main/java/com/donohoedigital/games/poker/model/TournamentHistory.java – JPA entity for hand history
• code/pokerserver/src/main/java/com/donohoedigital/games/poker/dao/impl/OnlineProfileImplJpa.java – Data access for player profiles
• code/pokerserver/src/main/java/com/donohoedigital/games/poker/dao/impl/TournamentHistoryImplJpa.java – Data access for hand history
• code/pokerserver/src/main/java/com/donohoedigital/games/poker/service/impl/OnlineProfileServiceImpl.java – Business logic for player CRUD operations
• code/pokerserver/src/main/java/com/donohoedigital/games/poker/server/OnlineProfilePurger.java – Utility for cleaning historic player data

Summary

• Three-table architecture: wan_profile stores players, wan_game stores sessions, and wan_history stores per-hand results with foreign keys linking all three.
• JPA mapping: The TournamentHistory entity in TournamentHistory.java maps wan_history columns to Java fields with @ManyToOne relationships enforcing referential integrity.
• Monetary precision: Financial columns use DECIMAL types to avoid floating-point errors for buy-ins, prizes, and rebuys.
• Performance optimization: Indexes on whi_end_date and whi_player_type ensure rapid history queries even with large datasets.
• Schema creation: The complete DDL is maintained in tools/db/create_tables.sql for consistent database initialization.

Frequently Asked Questions

What is the primary key structure for DD Poker tables?

Each of the three core tables uses an auto-incrementing integer primary key: wpr_id for wan_profile, wgm_id for wan_game, and whi_id for wan_history. These are defined as INT columns with AUTO_INCREMENT in MySQL and mapped to Long fields with GenerationType.IDENTITY in the JPA entities.

How does the wan_history table relate to players and games?

The wan_history table contains two mandatory foreign keys: whi_game_id references wan_game.wgm_id to identify which poker session the hand belongs to, and whi_profile_id references wan_profile.wpr_id to identify the specific player. These relationships are enforced by database constraints and JPA @ManyToOne annotations.

What data type does DD Poker use for monetary values?

DD Poker uses DECIMAL columns for all financial data in wan_history, including whi_prize, whi_buy_in, whi_total_rebuy, and whi_total_add_on. This ensures precise storage of currency values without floating-point rounding errors. The whi_rank_1 column uses DECIMAL(10,3) for high-precision statistical rankings.

Where is the database schema creation script located?

The complete SQL schema definition resides in tools/db/create_tables.sql at the repository root. This script creates the wan_profile, wan_game, and wan_history tables with their indexes, foreign keys, and column specifications, serving as the single source of truth for database initialization.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}