PVP Matches Design
thats the draft for example db designs for pvp matches
unknown
sql
10 months ago
31 kB
18
Indexable
/* ============================================================================
PVP WAGERED ARENA — FULL DB SCHEMA (SQL Server)
Author: Navixo Ops
============================================================================ */
SET NOCOUNT ON;
GO
/* ==============================
0) SAFETY (idempotency)
============================== */
IF OBJECT_ID('dbo.v_pvp_browse') IS NOT NULL DROP VIEW dbo.v_pvp_browse;
IF OBJECT_ID('dbo.v_pvp_pending_requests') IS NOT NULL DROP VIEW dbo.v_pvp_pending_requests;
IF OBJECT_ID('dbo.v_pvp_live_score') IS NOT NULL DROP VIEW dbo.v_pvp_live_score;
GO
-- Drop procedures if exist (minimal list)
DECLARE @procs TABLE(name sysname);
INSERT INTO @procs(name) VALUES
('usp_pvp_create_offer'),('usp_pvp_cancel_offer'),('usp_pvp_join_request'),
('usp_pvp_approve_request'),('usp_pvp_allocate_world'),('usp_pvp_start_match'),
('usp_pvp_record_kill'),('usp_pvp_finish_match'),('usp_pvp_world_free'),
('usp_pvp_add_spectator'),('usp_pvp_remove_spectator');
DECLARE @n sysname;
WHILE EXISTS(SELECT 1 FROM @procs)
BEGIN
SELECT TOP(1) @n=name FROM @procs;
IF OBJECT_ID('dbo.'+@n) IS NOT NULL EXEC('DROP PROCEDURE dbo.'+@n);
DELETE FROM @procs WHERE name=@n;
END
GO
/* ==============================
1) LOOKUPS
============================== */
IF OBJECT_ID('dbo.currency_lu') IS NOT NULL DROP TABLE dbo.currency_lu;
CREATE TABLE dbo.currency_lu (
currency_id TINYINT NOT NULL PRIMARY KEY, -- 1=Silks, 2=EP
code VARCHAR(12) NOT NULL UNIQUE
);
INSERT INTO dbo.currency_lu(currency_id, code) VALUES (1,'SILKS'),(2,'EP');
IF OBJECT_ID('dbo.offer_status_lu') IS NOT NULL DROP TABLE dbo.offer_status_lu;
CREATE TABLE dbo.offer_status_lu (
status_id TINYINT PRIMARY KEY, -- 1=OPEN,2=LOCKED,3=CANCELED,4=CONSUMED
name VARCHAR(20) UNIQUE
);
INSERT INTO dbo.offer_status_lu VALUES (1,'OPEN'),(2,'LOCKED'),(3,'CANCELED'),(4,'CONSUMED');
IF OBJECT_ID('dbo.join_status_lu') IS NOT NULL DROP TABLE dbo.join_status_lu;
CREATE TABLE dbo.join_status_lu (
status_id TINYINT PRIMARY KEY, -- 1=PENDING,2=ACCEPTED,3=DECLINED,4=EXPIRED,5=CANCELED
name VARCHAR(20) UNIQUE
);
INSERT INTO dbo.join_status_lu VALUES
(1,'PENDING'),(2,'ACCEPTED'),(3,'DECLINED'),(4,'EXPIRED'),(5,'CANCELED');
IF OBJECT_ID('dbo.match_state_lu') IS NOT NULL DROP TABLE dbo.match_state_lu;
CREATE TABLE dbo.match_state_lu (
state_id TINYINT PRIMARY KEY, -- 1=PENDING_CONFIRM,2=QUEUED,3=ALLOCATING,4=WARMUP,5=LIVE,6=OVERTIME,7=ENDED,8=CANCELED,9=ERROR
name VARCHAR(24) UNIQUE
);
INSERT INTO dbo.match_state_lu VALUES
(1,'PENDING_CONFIRM'),(2,'QUEUED'),(3,'ALLOCATING'),(4,'WARMUP'),
(5,'LIVE'),(6,'OVERTIME'),(7,'ENDED'),(8,'CANCELED'),(9,'ERROR');
IF OBJECT_ID('dbo.match_result_reason_lu') IS NOT NULL DROP TABLE dbo.match_result_reason_lu;
CREATE TABLE dbo.match_result_reason_lu (
reason_id TINYINT PRIMARY KEY, -- 1=TIME,2=TARGET_REACHED,3=SURRENDER,4=DC_FORFEIT,5=ERROR,6=CANCEL
name VARCHAR(24) UNIQUE
);
INSERT INTO dbo.match_result_reason_lu VALUES
(1,'TIME'),(2,'TARGET_REACHED'),(3,'SURRENDER'),(4,'DC_FORFEIT'),(5,'ERROR'),(6,'CANCEL');
IF OBJECT_ID('dbo.participant_role_lu') IS NOT NULL DROP TABLE dbo.participant_role_lu;
CREATE TABLE dbo.participant_role_lu (
role_id TINYINT PRIMARY KEY, -- 1=FIGHTER_A,2=FIGHTER_B
name VARCHAR(20) UNIQUE
);
INSERT INTO dbo.participant_role_lu VALUES (1,'FIGHTER_A'),(2,'FIGHTER_B');
-- Optional rules bitmask names
IF OBJECT_ID('dbo.rules_flag_lu') IS NOT NULL DROP TABLE dbo.rules_flag_lu;
CREATE TABLE dbo.rules_flag_lu (
bit_index TINYINT PRIMARY KEY, -- 0..31
name VARCHAR(40) UNIQUE,
description VARCHAR(200) NULL
);
-- Seed a few common toggles
INSERT INTO dbo.rules_flag_lu(bit_index,name,description) VALUES
(0,'ALLOW_ZERK','Allow berserk'),
(1,'NO_PETS','Disallow pets & summons'),
(2,'NO_JOB_CAPES','Disallow job capes'),
(3,'NO_POTS','Disallow potions'),
(4,'NORMALIZE_STATS','Normalize damage/defense'),
(5,'DISABLE_REZ','Disable resurrect/recall');
/* ==============================
2) CORE TABLES
============================== */
IF OBJECT_ID('dbo.pvp_offers') IS NOT NULL DROP TABLE dbo.pvp_offers;
CREATE TABLE dbo.pvp_offers (
challenge_id BIGINT IDENTITY(1,1) PRIMARY KEY,
creator_char_id BIGINT NOT NULL,
min_level SMALLINT NULL,
currency_id TINYINT NOT NULL REFERENCES dbo.currency_lu(currency_id),
stake_amount INT NOT NULL CHECK (stake_amount > 0),
mode TINYINT NOT NULL, -- 1=MOST_KILLS, 2=FIRST_TO_N
target_kills TINYINT NULL, -- used if mode=2
duration_seconds INT NOT NULL CHECK (duration_seconds BETWEEN 60 AND 3600),
rules_flags INT NOT NULL DEFAULT(0),
status_id TINYINT NOT NULL REFERENCES dbo.offer_status_lu(status_id) DEFAULT 1, -- OPEN
created_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
rowver ROWVERSION
);
CREATE INDEX ix_offers_open ON dbo.pvp_offers(status_id, currency_id, created_at DESC);
CREATE INDEX ix_offers_creator ON dbo.pvp_offers(creator_char_id, created_at DESC);
IF OBJECT_ID('dbo.pvp_join_requests') IS NOT NULL DROP TABLE dbo.pvp_join_requests;
CREATE TABLE dbo.pvp_join_requests (
request_id BIGINT IDENTITY(1,1) PRIMARY KEY,
challenge_id BIGINT NOT NULL REFERENCES dbo.pvp_offers(challenge_id),
requester_char_id BIGINT NOT NULL,
requester_level SMALLINT NULL,
status_id TINYINT NOT NULL REFERENCES dbo.join_status_lu(status_id) DEFAULT 1, -- PENDING
message_note NVARCHAR(120) NULL,
created_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
decided_at DATETIME2(0) NULL,
rowver ROWVERSION
);
CREATE INDEX ix_join_pending ON dbo.pvp_join_requests(challenge_id, status_id, created_at);
CREATE INDEX ix_join_requester ON dbo.pvp_join_requests(requester_char_id, created_at DESC);
IF OBJECT_ID('dbo.pvp_matches') IS NOT NULL DROP TABLE dbo.pvp_matches;
CREATE TABLE dbo.pvp_matches (
match_id BIGINT IDENTITY(1,1) PRIMARY KEY,
challenge_id BIGINT NOT NULL REFERENCES dbo.pvp_offers(challenge_id),
currency_id TINYINT NOT NULL REFERENCES dbo.currency_lu(currency_id),
stake_a INT NOT NULL,
stake_b INT NOT NULL,
tax_percent TINYINT NOT NULL CHECK (tax_percent BETWEEN 0 AND 100),
pot_amount INT NOT NULL, -- stake_a + stake_b
mode TINYINT NOT NULL, -- 1/2
target_kills TINYINT NULL,
duration_seconds INT NOT NULL,
rules_flags INT NOT NULL,
state_id TINYINT NOT NULL REFERENCES dbo.match_state_lu(state_id) DEFAULT 1,
world_id INT NULL,
started_at DATETIME2(0) NULL,
ends_at DATETIME2(0) NULL,
ended_at DATETIME2(0) NULL,
result_reason_id TINYINT NULL REFERENCES dbo.match_result_reason_lu(reason_id),
winner_char_id BIGINT NULL,
a_kills SMALLINT NOT NULL DEFAULT 0,
b_kills SMALLINT NOT NULL DEFAULT 0,
sudden_death BIT NOT NULL DEFAULT 0,
overtime_started_at DATETIME2(0) NULL,
tiebreak_used TINYINT NULL, -- 1=DAMAGE, 2=FIRST_BLOOD, 3=SEED
tiebreak_winner_role TINYINT NULL, -- 1=A,2=B
created_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
rowver ROWVERSION
);
CREATE INDEX ix_matches_state ON dbo.pvp_matches(state_id, created_at);
CREATE INDEX ix_matches_world ON dbo.pvp_matches(world_id);
CREATE INDEX ix_matches_winner ON dbo.pvp_matches(winner_char_id);
IF OBJECT_ID('dbo.pvp_match_participants') IS NOT NULL DROP TABLE dbo.pvp_match_participants;
CREATE TABLE dbo.pvp_match_participants (
match_id BIGINT NOT NULL REFERENCES dbo.pvp_matches(match_id) ON DELETE CASCADE,
char_id BIGINT NOT NULL,
role_id TINYINT NOT NULL REFERENCES dbo.participant_role_lu(role_id), -- A or B
original_map_id INT NULL,
original_x INT NULL,
original_y INT NULL,
original_z INT NULL,
joined_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
PRIMARY KEY(match_id, role_id)
);
CREATE INDEX ix_participant_char ON dbo.pvp_match_participants(char_id, match_id);
IF OBJECT_ID('dbo.pvp_match_spectators') IS NOT NULL DROP TABLE dbo.pvp_match_spectators;
CREATE TABLE dbo.pvp_match_spectators (
match_id BIGINT NOT NULL REFERENCES dbo.pvp_matches(match_id) ON DELETE CASCADE,
char_id BIGINT NOT NULL,
joined_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
left_at DATETIME2(0) NULL,
PRIMARY KEY(match_id, char_id)
);
IF OBJECT_ID('dbo.pvp_kill_events') IS NOT NULL DROP TABLE dbo.pvp_kill_events;
CREATE TABLE dbo.pvp_kill_events (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
match_id BIGINT NOT NULL REFERENCES dbo.pvp_matches(match_id) ON DELETE CASCADE,
killer_char_id BIGINT NOT NULL,
victim_char_id BIGINT NOT NULL,
occurred_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX ix_kills_match_time ON dbo.pvp_kill_events(match_id, occurred_at);
IF OBJECT_ID('dbo.pvp_match_participant_stats') IS NOT NULL DROP TABLE dbo.pvp_match_participant_stats;
CREATE TABLE dbo.pvp_match_participant_stats (
match_id BIGINT NOT NULL REFERENCES dbo.pvp_matches(match_id) ON DELETE CASCADE,
role_id TINYINT NOT NULL REFERENCES dbo.participant_role_lu(role_id),
char_id BIGINT NOT NULL,
kills SMALLINT NOT NULL DEFAULT(0),
deaths SMALLINT NOT NULL DEFAULT(0),
highest_streak TINYINT NOT NULL DEFAULT(0),
current_streak TINYINT NOT NULL DEFAULT(0),
first_blood BIT NOT NULL DEFAULT(0),
last_kill BIT NOT NULL DEFAULT(0),
damage_dealt INT NOT NULL DEFAULT(0),
damage_taken INT NOT NULL DEFAULT(0),
suicides TINYINT NOT NULL DEFAULT(0),
updated_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
PRIMARY KEY (match_id, role_id)
);
CREATE INDEX ix_part_stats_char ON dbo.pvp_match_participant_stats(char_id, match_id);
IF OBJECT_ID('dbo.pvp_escrows') IS NOT NULL DROP TABLE dbo.pvp_escrows;
CREATE TABLE dbo.pvp_escrows (
escrow_id BIGINT IDENTITY(1,1) PRIMARY KEY,
match_id BIGINT NOT NULL REFERENCES dbo.pvp_matches(match_id) ON DELETE CASCADE,
char_id BIGINT NOT NULL,
currency_id TINYINT NOT NULL REFERENCES dbo.currency_lu(currency_id),
amount INT NOT NULL CHECK (amount > 0),
status TINYINT NOT NULL DEFAULT 1, -- 1=LOCKED, 2=RELEASED, 3=REFUNDED
ts DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX ix_escrow_match ON dbo.pvp_escrows(match_id, status);
IF OBJECT_ID('dbo.pvp_worlds') IS NOT NULL DROP TABLE dbo.pvp_worlds;
CREATE TABLE dbo.pvp_worlds (
world_id INT PRIMARY KEY, -- 1..5
status TINYINT NOT NULL, -- 1=FREE,2=ALLOCATED,3=LIVE,4=CLEANING
current_match_id BIGINT NULL REFERENCES dbo.pvp_matches(match_id),
updated_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
-- Seed 5 worlds
MERGE dbo.pvp_worlds AS tgt
USING (VALUES (1),(2),(3),(4),(5)) AS src(world_id)
ON tgt.world_id=src.world_id
WHEN NOT MATCHED THEN INSERT(world_id,status,updated_at) VALUES(src.world_id,1,SYSUTCDATETIME());
IF OBJECT_ID('dbo.pvp_audit') IS NOT NULL DROP TABLE dbo.pvp_audit;
CREATE TABLE dbo.pvp_audit (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
match_id BIGINT NULL REFERENCES dbo.pvp_matches(match_id),
challenge_id BIGINT NULL REFERENCES dbo.pvp_offers(challenge_id),
char_id BIGINT NULL,
action VARCHAR(64) NOT NULL, -- OFFER_CREATE, JOIN_REQUEST, MATCH_START, PAYOUT, etc.
payload_json NVARCHAR(MAX) NULL,
ts DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX ix_audit_match ON dbo.pvp_audit(match_id, ts);
CREATE INDEX ix_audit_char ON dbo.pvp_audit(char_id, ts);
-- Optional: track DCs and penalties
IF OBJECT_ID('dbo.pvp_disconnects') IS NOT NULL DROP TABLE dbo.pvp_disconnects;
CREATE TABLE dbo.pvp_disconnects (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
match_id BIGINT NOT NULL REFERENCES dbo.pvp_matches(match_id) ON DELETE CASCADE,
char_id BIGINT NOT NULL,
started_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
ended_at DATETIME2(0) NULL
);
IF OBJECT_ID('dbo.pvp_penalties') IS NOT NULL DROP TABLE dbo.pvp_penalties;
CREATE TABLE dbo.pvp_penalties (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
match_id BIGINT NOT NULL REFERENCES dbo.pvp_matches(match_id) ON DELETE CASCADE,
char_id BIGINT NOT NULL,
code VARCHAR(32) NOT NULL, -- "ILLEGAL_BUFF","LEAVE_AREA",...
details NVARCHAR(200) NULL,
ts DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
/* ==============================
3) VIEWS (for GUIs)
============================== */
CREATE VIEW dbo.v_pvp_browse AS
SELECT
o.challenge_id,
o.creator_char_id,
o.min_level,
c.code AS currency,
o.stake_amount,
o.mode,
o.target_kills,
o.duration_seconds,
o.rules_flags,
'OFFER' AS row_type,
NULL AS match_id,
NULL AS a_char_id,
NULL AS b_char_id,
NULL AS score_a,
NULL AS score_b,
o.created_at AS created_or_started_at
FROM dbo.pvp_offers o
JOIN dbo.currency_lu c ON c.currency_id=o.currency_id
WHERE o.status_id = 1 -- OPEN
UNION ALL
SELECT
NULL AS challenge_id,
NULL AS creator_char_id,
NULL AS min_level,
c.code AS currency,
(m.stake_a + m.stake_b) AS stake_amount,
m.mode,
m.target_kills,
m.duration_seconds,
m.rules_flags,
'LIVE' AS row_type,
m.match_id,
pa.char_id AS a_char_id,
pb.char_id AS b_char_id,
m.a_kills AS score_a,
m.b_kills AS score_b,
COALESCE(m.started_at, m.created_at) AS created_or_started_at
FROM dbo.pvp_matches m
JOIN dbo.currency_lu c ON c.currency_id=m.currency_id
JOIN dbo.pvp_match_participants pa ON pa.match_id=m.match_id AND pa.role_id=1
JOIN dbo.pvp_match_participants pb ON pb.match_id=m.match_id AND pb.role_id=2
WHERE m.state_id IN (2,3,4,5,6); -- QUEUED..OVERTIME
GO
CREATE VIEW dbo.v_pvp_pending_requests AS
SELECT r.request_id, r.challenge_id, r.requester_char_id, r.requester_level,
r.status_id, r.created_at
FROM dbo.pvp_join_requests r
JOIN dbo.pvp_offers o ON o.challenge_id = r.challenge_id
WHERE r.status_id = 1 AND o.status_id IN (1,2); -- PENDING; offer OPEN/LOCKED
GO
CREATE VIEW dbo.v_pvp_live_score AS
SELECT
m.match_id, m.world_id, m.state_id, m.started_at, m.duration_seconds,
pa.char_id AS a_char_id, pb.char_id AS b_char_id,
m.a_kills, m.b_kills
FROM dbo.pvp_matches m
JOIN dbo.pvp_match_participants pa ON pa.match_id=m.match_id AND pa.role_id=1
JOIN dbo.pvp_match_participants pb ON pb.match_id=m.match_id AND pb.role_id=2
WHERE m.state_id IN (3,4,5,6); -- ALLOCATING/WARMUP/LIVE/OVERTIME
GO
/* ==============================
4) STORED PROCEDURES
============================== */
-- 4.1 Create offer
CREATE PROCEDURE dbo.usp_pvp_create_offer
@creator_char_id BIGINT,
@currency_id TINYINT,
@stake_amount INT,
@min_level SMALLINT = NULL,
@mode TINYINT, -- 1 or 2
@target_kills TINYINT = NULL, -- required if mode=2
@duration_seconds INT,
@rules_flags INT = 0,
@challenge_id BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
IF @mode=2 AND (@target_kills IS NULL OR @target_kills<=0)
THROW 52001, 'FIRST_TO_N requires target_kills > 0', 1;
INSERT INTO dbo.pvp_offers
(creator_char_id,min_level,currency_id,stake_amount,mode,target_kills,duration_seconds,rules_flags,status_id)
VALUES
(@creator_char_id,@min_level,@currency_id,@stake_amount,@mode,@target_kills,@duration_seconds,@rules_flags,1);
SET @challenge_id = SCOPE_IDENTITY();
INSERT INTO dbo.pvp_audit(challenge_id,char_id,action,payload_json)
VALUES(@challenge_id,@creator_char_id,'OFFER_CREATE',NULL);
END
GO
-- 4.2 Cancel offer
CREATE PROCEDURE dbo.usp_pvp_cancel_offer
@creator_char_id BIGINT,
@challenge_id BIGINT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
BEGIN TRY
UPDATE dbo.pvp_offers WITH(UPDLOCK, HOLDLOCK)
SET status_id=3 -- CANCELED
WHERE challenge_id=@challenge_id AND creator_char_id=@creator_char_id AND status_id=1; -- OPEN
IF @@ROWCOUNT=0
THROW 52002, 'Offer not cancellable', 1;
-- Expire pending join requests
UPDATE dbo.pvp_join_requests
SET status_id=4, decided_at=SYSUTCDATETIME()
WHERE challenge_id=@challenge_id AND status_id=1;
INSERT INTO dbo.pvp_audit(challenge_id,char_id,action,payload_json)
VALUES(@challenge_id,@creator_char_id,'OFFER_CANCEL',NULL);
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK;
THROW;
END CATCH
END
GO
-- 4.3 Join request (user clicks Accept on list; this is the "request to fight")
CREATE PROCEDURE dbo.usp_pvp_join_request
@requester_char_id BIGINT,
@challenge_id BIGINT,
@requester_level SMALLINT = NULL,
@request_id BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @min_level SMALLINT, @status_id TINYINT;
SELECT @min_level=min_level, @status_id=status_id
FROM dbo.pvp_offers WHERE challenge_id=@challenge_id;
IF @status_id<>1 -- not OPEN
THROW 52003, 'Offer not open', 1;
IF @min_level IS NOT NULL AND @requester_level IS NOT NULL AND @requester_level < @min_level
THROW 52004, 'Level too low', 1;
INSERT INTO dbo.pvp_join_requests(challenge_id, requester_char_id, requester_level)
VALUES(@challenge_id, @requester_char_id, @requester_level);
SET @request_id = SCOPE_IDENTITY();
INSERT INTO dbo.pvp_audit(challenge_id,char_id,action,payload_json)
VALUES(@challenge_id,@requester_char_id,'JOIN_REQUEST',NULL);
END
GO
-- 4.4 Creator approves one request -> creates match, locks offer & escrow
CREATE PROCEDURE dbo.usp_pvp_approve_request
@creator_char_id BIGINT,
@request_id BIGINT,
@tax_percent TINYINT,
@original_map_a INT = NULL, @orig_x_a INT=NULL, @orig_y_a INT=NULL, @orig_z_a INT=NULL,
@original_map_b INT = NULL, @orig_x_b INT=NULL, @orig_y_b INT=NULL, @orig_z_b INT=NULL,
@match_id BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
BEGIN TRY
DECLARE @challenge_id BIGINT, @requester BIGINT, @offer_status TINYINT,
@currency_id TINYINT, @stake INT, @mode TINYINT, @tk TINYINT,
@duration INT, @rules INT;
SELECT @challenge_id = r.challenge_id, @requester = r.requester_char_id
FROM dbo.pvp_join_requests r WITH(UPDLOCK, HOLDLOCK)
WHERE r.request_id=@request_id AND r.status_id=1; -- PENDING
IF @challenge_id IS NULL THROW 52005, 'Request not pending', 1;
SELECT @offer_status = o.status_id, @currency_id=o.currency_id, @stake=o.stake_amount,
@mode=o.mode, @tk=o.target_kills, @duration=o.duration_seconds, @rules=o.rules_flags
FROM dbo.pvp_offers o WITH(UPDLOCK, HOLDLOCK)
WHERE o.challenge_id=@challenge_id AND o.creator_char_id=@creator_char_id;
IF @offer_status<>1 THROW 52006, 'Offer not open', 1;
-- Lock the offer
UPDATE dbo.pvp_offers SET status_id=2 -- LOCKED
WHERE challenge_id=@challenge_id AND status_id=1;
IF @@ROWCOUNT=0 THROW 52007, 'Offer lock race lost', 1;
-- Accept this request and decline others
UPDATE dbo.pvp_join_requests SET status_id=2, decided_at=SYSUTCDATETIME()
WHERE request_id=@request_id AND status_id=1;
UPDATE dbo.pvp_join_requests SET status_id=3, decided_at=SYSUTCDATETIME()
WHERE challenge_id=@challenge_id AND status_id=1; -- decline remaining
-- Create match
INSERT INTO dbo.pvp_matches
(challenge_id,currency_id,stake_a,stake_b,tax_percent,pot_amount,
mode,target_kills,duration_seconds,rules_flags,state_id)
VALUES
(@challenge_id,@currency_id,@stake,@stake,@tax_percent,@stake*2,
@mode,@tk,@duration,@rules,1); -- PENDING_CONFIRM
SET @match_id = SCOPE_IDENTITY();
-- Insert participants (A = creator, B = requester)
INSERT INTO dbo.pvp_match_participants(match_id,char_id,role_id,original_map_id,original_x,original_y,original_z)
VALUES(@match_id,@creator_char_id,1,@original_map_a,@orig_x_a,@orig_y_a,@orig_z_a),
(@match_id,@requester,2,@original_map_b,@orig_x_b,@orig_y_b,@orig_z_b);
-- Seed participant stats
INSERT INTO dbo.pvp_match_participant_stats(match_id,role_id,char_id)
VALUES(@match_id,1,@creator_char_id),(@match_id,2,@requester);
-- Create escrow rows (wallet debits happen in app layer, but log here)
INSERT INTO dbo.pvp_escrows(match_id,char_id,currency_id,amount,status)
VALUES(@match_id,@creator_char_id,@currency_id,@stake,1), -- LOCKED
(@match_id,@requester,@currency_id,@stake,1);
INSERT INTO dbo.pvp_audit(match_id,challenge_id,char_id,action,payload_json)
VALUES(@match_id,@challenge_id,@creator_char_id,'MATCH_CREATED',NULL);
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK;
THROW;
END CATCH
END
GO
-- 4.5 Allocate a FREE world to the next match (call from scheduler)
CREATE PROCEDURE dbo.usp_pvp_allocate_world
@match_id BIGINT,
@world_id INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
BEGIN TRY
DECLARE @state TINYINT;
SELECT @state=state_id FROM dbo.pvp_matches WITH(UPDLOCK) WHERE match_id=@match_id;
IF @state NOT IN (1,2) -- PENDING_CONFIRM or QUEUED
THROW 52008, 'Match not allocatable', 1;
SELECT TOP(1) @world_id=world_id
FROM dbo.pvp_worlds WITH(UPDLOCK, HOLDLOCK)
WHERE status=1 -- FREE
ORDER BY world_id;
IF @world_id IS NULL THROW 52009, 'No free world', 1;
UPDATE dbo.pvp_worlds
SET status=2, current_match_id=@match_id, updated_at=SYSUTCDATETIME()
WHERE world_id=@world_id;
UPDATE dbo.pvp_matches
SET state_id=3, world_id=@world_id -- ALLOCATING
WHERE match_id=@match_id;
INSERT INTO dbo.pvp_audit(match_id,action,payload_json)
VALUES(@match_id,'WORLD_ALLOCATED',NULL);
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK;
THROW;
END CATCH
END
GO
-- 4.6 Start match (after teleports and warm-up)
CREATE PROCEDURE dbo.usp_pvp_start_match
@match_id BIGINT,
@duration_seconds INT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.pvp_matches
SET state_id=5, -- LIVE
started_at=SYSUTCDATETIME(),
ends_at=DATEADD(SECOND,@duration_seconds,SYSUTCDATETIME()),
duration_seconds=@duration_seconds
WHERE match_id=@match_id AND state_id IN (3,4,2,1); -- ALLOCATING/WARMUP/QUEUED/PENDING_CONFIRM
UPDATE dbo.pvp_worlds
SET status=3, updated_at=SYSUTCDATETIME()
WHERE current_match_id=@match_id;
INSERT INTO dbo.pvp_audit(match_id,action,payload_json)
VALUES(@match_id,'MATCH_START',NULL);
END
GO
-- 4.7 Record kill (authoritative server calls this)
CREATE PROCEDURE dbo.usp_pvp_record_kill
@match_id BIGINT,
@killer_char_id BIGINT,
@victim_char_id BIGINT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @state_id TINYINT, @mode TINYINT, @target TINYINT,
@role_killer TINYINT, @role_victim TINYINT,
@ak SMALLINT, @bk SMALLINT;
SELECT @state_id=state_id, @mode=mode, @target=target_kills
FROM dbo.pvp_matches WITH(UPDLOCK)
WHERE match_id=@match_id;
IF @state_id NOT IN (5,6) RETURN; -- only LIVE/OVERTIME
SELECT @role_killer = p.role_id
FROM dbo.pvp_match_participants p
WHERE p.match_id=@match_id AND p.char_id=@killer_char_id;
SELECT @role_victim = p.role_id
FROM dbo.pvp_match_participants p
WHERE p.match_id=@match_id AND p.char_id=@victim_char_id;
IF @role_killer IS NULL OR @role_victim IS NULL OR @role_killer=@role_victim RETURN;
INSERT INTO dbo.pvp_kill_events(match_id,killer_char_id,victim_char_id)
VALUES(@match_id,@killer_char_id,@victim_char_id);
IF @role_killer=1
UPDATE dbo.pvp_matches SET a_kills=a_kills+1 WHERE match_id=@match_id;
ELSE
UPDATE dbo.pvp_matches SET b_kills=b_kills+1 WHERE match_id=@match_id;
-- update participant stats
UPDATE s SET
kills = CASE WHEN @role_killer = s.role_id THEN kills + 1 ELSE kills END,
deaths = CASE WHEN @role_victim = s.role_id THEN deaths + 1 ELSE deaths END,
current_streak = CASE
WHEN @role_killer = s.role_id THEN current_streak + 1
WHEN @role_victim = s.role_id THEN 0
ELSE current_streak
END,
highest_streak = CASE
WHEN @role_killer = s.role_id AND current_streak + 1 > highest_streak
THEN current_streak + 1
ELSE highest_streak
END,
updated_at = SYSUTCDATETIME()
FROM dbo.pvp_match_participant_stats s
WHERE s.match_id=@match_id AND s.role_id IN (1,2);
-- First blood
IF NOT EXISTS (SELECT 1 FROM dbo.pvp_match_participant_stats WHERE match_id=@match_id AND first_blood=1)
UPDATE dbo.pvp_match_participant_stats SET first_blood=1 WHERE match_id=@match_id AND role_id=@role_killer;
-- Last kill flag
UPDATE dbo.pvp_match_participant_stats
SET last_kill = CASE WHEN role_id=@role_killer THEN 1 ELSE 0 END
WHERE match_id=@match_id AND role_id IN (1,2);
-- If FIRST_TO_N, check for finish
SELECT @ak=a_kills, @bk=b_kills FROM dbo.pvp_matches WHERE match_id=@match_id;
IF @mode=2 AND @target IS NOT NULL
BEGIN
IF @ak >= @target OR @bk >= @target
BEGIN
DECLARE @winner BIGINT;
IF @ak > @bk
SELECT @winner = char_id FROM dbo.pvp_match_participants WHERE match_id=@match_id AND role_id=1;
ELSE
SELECT @winner = char_id FROM dbo.pvp_match_participants WHERE match_id=@match_id AND role_id=2;
EXEC dbo.usp_pvp_finish_match @match_id=@match_id, @reason_id=2, @winner_char_id=@winner;
END
END
END
GO
-- 4.8 Finish match & payout (server calls on timer end or surrender/DC)
CREATE PROCEDURE dbo.usp_pvp_finish_match
@match_id BIGINT,
@reason_id TINYINT, -- per match_result_reason_lu
@winner_char_id BIGINT = NULL -- NULL allowed only if tie policy uses refund or later SD
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
BEGIN TRY
DECLARE @pot INT, @tax_pct TINYINT, @tax_amt INT, @win_amt INT,
@currency TINYINT, @a BIGINT, @b BIGINT, @ak SMALLINT, @bk SMALLINT;
SELECT @pot=pot_amount, @tax_pct=tax_percent, @currency=currency_id, @ak=a_kills, @bk=b_kills
FROM dbo.pvp_matches WITH(UPDLOCK) WHERE match_id=@match_id;
SELECT @a=char_id FROM dbo.pvp_match_participants WHERE match_id=@match_id AND role_id=1;
SELECT @b=char_id FROM dbo.pvp_match_participants WHERE match_id=@match_id AND role_id=2;
IF @winner_char_id IS NULL
BEGIN
-- Tie policy here (example: use score, else sudden death should have been used)
IF @ak > @bk SET @winner_char_id=@a;
ELSE IF @bk > @ak SET @winner_char_id=@b;
ELSE
BEGIN
-- If truly tie and no SD: pick first blood owner as tiebreak
DECLARE @fb_role TINYINT;
SELECT TOP(1) @fb_role = CASE WHEN role_id=1 THEN 1 WHEN role_id=2 THEN 2 END
FROM dbo.pvp_match_participant_stats WHERE match_id=@match_id AND first_blood=1;
IF @fb_role=1 SET @winner_char_id=@a; ELSE IF @fb_role=2 SET @winner_char_id=@b;
-- As a last resort (shouldn't happen often), pick lower char_id:
IF @winner_char_id IS NULL SET @winner_char_id = CASE WHEN @a<@b THEN @a ELSE @b END;
UPDATE dbo.pvp_matches SET tiebreak_used=2, tiebreak_winner_role=@fb_role WHERE match_id=@match_id;
END
END
SET @tax_amt = (@pot * @tax_pct) / 100;
SET @win_amt = @pot - @tax_amt;
UPDATE dbo.pvp_matches
SET state_id=7, ended_at=SYSUTCDATETIME(), result_reason_id=@reason_id, winner_char_id=@winner_char_id
WHERE match_id=@match_id;
-- Release escrows rows (status -> RELEASED), app layer should credit winner wallet with @win_amt
UPDATE dbo.pvp_escrows SET status=2, ts=SYSUTCDATETIME() WHERE match_id=@match_id AND status=1;
-- World -> CLEANING
UPDATE dbo.pvp_worlds SET status=4, updated_at=SYSUTCDATETIME()
WHERE current_match_id=@match_id;
-- Audit payout
INSERT INTO dbo.pvp_audit(match_id,action,payload_json)
VALUES(@match_id,'PAYOUT', CONCAT(N'{"winner":',@winner_char_id,',"currency":',@currency,',"pot":',@pot,',"tax":',@tax_amt,',"net":',@win_amt,'}'));
COMMIT;
-- NOTE: Credit winner wallet & route tax to sink/house in APP layer right after COMMIT.
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK;
THROW;
END CATCH
END
GO
-- 4.9 Free world after cleanup (call after you’ve teleported participants back)
CREATE PROCEDURE dbo.usp_pvp_world_free
@world_id INT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.pvp_worlds
SET status=1, current_match_id=NULL, updated_at=SYSUTCDATETIME()
WHERE world_id=@world_id;
END
GO
-- 4.10 Spectators add/remove
CREATE PROCEDURE dbo.usp_pvp_add_spectator
@match_id BIGINT,
@char_id BIGINT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @state TINYINT;
SELECT @state=state_id FROM dbo.pvp_matches WHERE match_id=@match_id;
IF @state NOT IN (3,4,5,6) THROW 52010, 'Match not spectatable', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.pvp_match_spectators WHERE match_id=@match_id AND char_id=@char_id)
INSERT INTO dbo.pvp_match_spectators(match_id,char_id) VALUES(@match_id,@char_id);
END
GO
CREATE PROCEDURE dbo.usp_pvp_remove_spectator
@match_id BIGINT,
@char_id BIGINT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.pvp_match_spectators
SET left_at=SYSUTCDATETIME()
WHERE match_id=@match_id AND char_id=@char_id AND left_at IS NULL;
END
GO
/* ==============================
5) INDICES & TUNING
============================== */
-- Filtered index for live/active matches list
IF NOT EXISTS (
SELECT 1 FROM sys.indexes WHERE name='ix_live_matches' AND object_id=OBJECT_ID('dbo.pvp_matches')
)
BEGIN
CREATE INDEX ix_live_matches ON dbo.pvp_matches(state_id, started_at DESC)
WHERE state_id IN (3,4,5,6);
END
GO
/* ==============================
DONE
============================== */
Editor is loading...
Leave a Comment