54 lines
3.0 KiB
SQL
54 lines
3.0 KiB
SQL
|
-- Migration: 0005_population_columns
|
||
|
|
-- Date: 2026-05-07
|
||
|
|
-- Purpose: Schema additions for the world-population subsystem
|
||
|
|
-- (BotPopulationManager + BotSetupPipeline). Tracks the
|
||
|
|
-- one-shot distribution levelup, idempotent setup-pipeline
|
||
|
|
-- progress, JIT queue-fill provenance, and LRU timestamps.
|
||
|
|
-- Reverts: yes (DROP COLUMN per added column).
|
||
|
|
|
||
|
|
ALTER TABLE playerbot_v2_character
|
||
|
|
ADD COLUMN distribution_level TINYINT UNSIGNED NOT NULL DEFAULT 0
|
||
|
|
AFTER owner_verbose,
|
||
|
|
ADD COLUMN distribution_at TIMESTAMP NULL DEFAULT NULL
|
||
|
|
AFTER distribution_level,
|
||
|
|
ADD COLUMN setup_pipeline_state TINYINT UNSIGNED NOT NULL DEFAULT 0
|
||
|
|
AFTER distribution_at,
|
||
|
|
ADD COLUMN jit_for_queue VARCHAR(48) NULL DEFAULT NULL
|
||
|
|
AFTER setup_pipeline_state,
|
||
|
|
ADD COLUMN last_seen_at TIMESTAMP NULL DEFAULT NULL
|
||
|
|
AFTER jit_for_queue,
|
||
|
|
ADD KEY idx_distribution_level (distribution_level),
|
||
|
|
ADD KEY idx_jit_for_queue (jit_for_queue),
|
||
|
|
ADD KEY idx_last_seen_at (last_seen_at);
|
||
|
|
|
||
|
|
-- Column docs (kept here below the ALTER so the migration body stays a
|
||
|
|
-- single statement free of comment-embedded semicolons that the migration
|
||
|
|
-- parser used to choke on):
|
||
|
|
-- distribution_level Floor level the BotPopulationManager set this
|
||
|
|
-- bot to. 0 = never distributed. The shaper
|
||
|
|
-- refuses to lower a bot's level (see
|
||
|
|
-- project_v2_world_population.md A5). Gameplay
|
||
|
|
-- leveling beyond this floor proceeds normally.
|
||
|
|
-- distribution_at When the distribution levelup happened.
|
||
|
|
-- Used by hygiene + diagnostics; NULL until set.
|
||
|
|
-- setup_pipeline_state Bitmask of completed BotSetupPipeline steps.
|
||
|
|
-- bit 0 = SetLevel, 1 = StarterKit,
|
||
|
|
-- 2 = GenerateGear, 3 = AutoEquip,
|
||
|
|
-- 4 = ApplyTalents, 5 = LearnProfessions,
|
||
|
|
-- 6 = AcquireMount, 7 = PlaceInCapital,
|
||
|
|
-- 8 (high bits in 0xFF marker) = TravelToZone +
|
||
|
|
-- Mark complete. 0xFF = pipeline complete.
|
||
|
|
-- jit_for_queue Tag set when the bot was JIT-spawned to fill
|
||
|
|
-- a player's queue (e.g. "BG_AB", "LFG_5man").
|
||
|
|
-- Used by hygiene cleanup to delete bots that
|
||
|
|
-- were created solely for queue-fill and have
|
||
|
|
-- outlived their retention window. NULL = bot
|
||
|
|
-- is part of the regular distribution pool.
|
||
|
|
-- last_seen_at UTC timestamp of last login or significant
|
||
|
|
-- AI tick. Drives LRU log-out when the
|
||
|
|
-- population exceeds target.
|
||
|
|
|
||
|
|
-- Record this migration as applied.
|
||
|
|
INSERT INTO playerbot_v2_schema_version (version, sha256)
|
||
|
|
VALUES (5, 'pending-fill-at-release-time-with-actual-sha256-of-this-file');
|