23 lines
1.1 KiB
SQL
23 lines
1.1 KiB
SQL
-- Add OpenSkill rating fields to teams and users
|
|
-- OpenSkill uses mu (mean skill) and sigma (uncertainty)
|
|
-- ordinal = mu - 3*sigma (display rating)
|
|
|
|
-- Add OpenSkill fields to teams
|
|
ALTER TABLE teams ADD COLUMN IF NOT EXISTS mu REAL NOT NULL DEFAULT 25.0;
|
|
ALTER TABLE teams ADD COLUMN IF NOT EXISTS sigma REAL NOT NULL DEFAULT 8.333;
|
|
ALTER TABLE teams ADD COLUMN IF NOT EXISTS ordinal REAL NOT NULL DEFAULT 0.0;
|
|
|
|
-- Add OpenSkill fields to users (for individual player ratings)
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS mu REAL NOT NULL DEFAULT 25.0;
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS sigma REAL NOT NULL DEFAULT 8.333;
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS ordinal REAL NOT NULL DEFAULT 0.0;
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS mmr REAL NOT NULL DEFAULT 1000.0;
|
|
|
|
-- Update existing ordinal values based on default mu/sigma
|
|
UPDATE teams SET ordinal = mu - (3.0 * sigma) WHERE ordinal = 0.0;
|
|
UPDATE users SET ordinal = mu - (3.0 * sigma) WHERE ordinal = 0.0;
|
|
|
|
-- Create indexes for faster ranking queries
|
|
CREATE INDEX IF NOT EXISTS idx_teams_ordinal ON teams(ordinal DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_users_ordinal ON users(ordinal DESC);
|