VYJMSZKP35WUQFU5RZ7JTNZTA4NHCXM3D2S3RNVVJB5WMPCEFBYQC
-- Add some notes to add in RSS
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'games' AND n.nspname = 'games' AND a.attname = 'notes_html'
) THEN
ALTER TABLE games.games ADD COLUMN notes_html VARCHAR(4096) NULL;
END IF;
END
$$;
-- Check permanent password without game uid
DROP FUNCTION IF EXISTS auth.check_otp(player_name_param CITEXT, otp CHAR(6));
CREATE OR REPLACE FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6))
RETURNS BOOLEAN AS
$$
WITH hashed_otp AS (DELETE FROM auth.otp WHERE otp.player_name = player_name_param RETURNING otp.otp)
SELECT (TABLE hashed_otp) IS NOT NULL AND (TABLE hashed_otp) = crypt(otp, (TABLE hashed_otp))
OR u.game_password = crypt(otp, u.game_password)
FROM auth.users u WHERE u.player_name = player_name_param;
$$ LANGUAGE sql VOLATILE;
-- Check permanent password
DROP FUNCTION IF EXISTS auth.check_otp(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20));
CREATE OR REPLACE FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
RETURNS TABLE (
authenticated BOOLEAN,
client_type auth.client_types) AS
$$
BEGIN
CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
WITH hashed_otp AS (DELETE FROM auth.otp WHERE otp.player_name = player_name_param RETURNING otp.otp)
SELECT (TABLE hashed_otp) IS NOT NULL AND (TABLE hashed_otp) = crypt(otp, (TABLE hashed_otp))
OR u.game_password = crypt(otp, u.game_password)
FROM auth.users u WHERE u.player_name = player_name_param;
RETURN QUERY SELECT t.*, p.client_type
FROM tmp_cnts t
CROSS JOIN games.players p
WHERE p.player_name = player_name_param
AND p.game_uid = game_uid_param;
END
$$ LANGUAGE plpgsql;
-- Add table to store tokens to reset password
CREATE TABLE IF NOT EXISTS auth.reset_tokens (
player_name CITEXT REFERENCES auth.users(player_name) PRIMARY KEY,
token UUID NOT NULL,
ip_address TEXT NULL,
create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
last_error TEXT NULL,
UNIQUE(token)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.reset_tokens TO freeorion;
-- Add fields to games for RSS channel
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'games' AND n.nspname = 'games' AND a.attname = 'fo_forum_url'
) THEN
ALTER TABLE games.games ADD COLUMN fo_forum_url VARCHAR(512) NULL;
END IF;
IF NOT EXISTS (
SELECT 1 FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'games' AND n.nspname = 'games' AND a.attname = 'create_ts'
) THEN
ALTER TABLE games.games ADD COLUMN create_ts TIMESTAMP WITHOUT TIME ZONE NULL;
END IF;
END
$$;
-- Add table to store turn time
CREATE TABLE IF NOT EXISTS games.turns (
game_uid VARCHAR(20) REFERENCES games.games(game_uid),
turn INT NOT NULL,
turn_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT pk_turns PRIMARY KEY (game_uid, turn)
);
GRANT SELECT, INSERT, UPDATE ON games.turns TO freeorion;
-- Allow NULL roles without game
DROP FUNCTION IF EXISTS auth.check_otp(player_name_param CITEXT, otp CHAR(6));
CREATE OR REPLACE FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6))
RETURNS BOOLEAN AS
$$
WITH hashed_otp AS (DELETE FROM auth.otp WHERE otp.player_name = player_name_param RETURNING otp.otp)
SELECT (TABLE hashed_otp) IS NOT NULL AND (TABLE hashed_otp) = crypt(otp, (TABLE hashed_otp));
$$ LANGUAGE sql VOLATILE;