Last batc hof old migrations for database

O01eg

Sep 11, 2022, 3:04 PM

VYJMSZKP35WUQFU5RZ7JTNZTA4NHCXM3D2S3RNVVJB5WMPCEFBYQC

Dependencies

[2] IXY6NZLM Start to use migrations files for PostgreSQL database

Change contents

file addition: 20220911_6_games_notes.sql (----------)

[2.15]

-- 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
$$;
file addition: 20220911_5_auth-password-without-game-uid.sql (----------)

[2.15]

-- 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;
file addition: 20220911_4_auth-password-with-game-uid.sql (----------)

[2.15]

-- 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;
file addition: 20220911_3_auth-reset-tokens.sql (----------)

[2.15]

-- 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;
file addition: 20220911_2_games_rss_channel.sql (----------)

[2.15]

-- 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
$$;
file addition: 20220911_1_games_turns.sql (----------)

[2.15]

-- 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;
file addition: 20220911_0_auth-otp-roles-without-game.sql (----------)

[2.15]

-- 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;