pijul nest
guest › [sign in] [explore]

Next batch of SQL migrations

O01eg
Sep 10, 2022, 5:43 PM
VTZ5IPX6M76B26M42URFL5DY2WCJTHULZNJL7CBR44L2KCY75B3AC

Dependencies

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

Change contents

  • file addition: 20220910_9_auth-otp-roles.sql (----------)
    [2.15]
    -- Get available roles after authentication
    DROP FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6));
    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));
    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: 20220910_8_auth-users-passwords.sql (----------)
    [2.15]
    -- Add static passwords which could be set via protected with HTTPS web interface
    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 = 'users' AND n.nspname = 'auth' AND a.attname = 'web_password'
    ) THEN
    ALTER TABLE auth.users ADD COLUMN web_password TEXT 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 = 'users' AND n.nspname = 'auth' AND a.attname = 'game_password'
    ) THEN
    ALTER TABLE auth.users ADD COLUMN game_password TEXT NULL;
    END IF;
    END
    $$;
  • file addition: 20220910_7_auth-client-types.sql (----------)
    [2.15]
    -- Add allowed client type for user in the game
    DO $$
    BEGIN
    IF NOT EXISTS (
    SELECT 1 FROM pg_type t
    INNER JOIN pg_namespace p ON t.typnamespace=p.oid
    WHERE t.typname='client_types' AND p.nspname='auth'
    ) THEN
    CREATE TYPE auth.client_types AS ENUM ('p', 'o', 'm');
    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 = 'players' AND n.nspname = 'games' AND a.attname = 'client_type'
    ) THEN
    ALTER TABLE games.players ADD COLUMN client_type auth.client_types NOT NULL DEFAULT 'p';
    END IF;
    END
    $$;
  • file addition: 20220910_6_chat-server_id.sql (----------)
    [2.15]
    -- Share chat between servers (2020-10-03)
    -- Choose one server where chat will remain alive
    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 = 'chat_history' AND n.nspname = 'public' AND a.attname = 'server_id'
    ) THEN
    ALTER TABLE chat_history ADD COLUMN server_id INT NOT NULL DEFAULT 1;
    END IF;
    END
    $$;
  • file addition: 20220910_5_auth-deletage-no-otp.sql (----------)
    [2.15]
    -- Alter function to don't send OTP to delegate now:
    DROP FUNCTION auth.check_contact;
    CREATE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
    RETURNS TABLE (
    protocol auth.contact_protocol,
    address CITEXT) AS
    $$
    BEGIN
    CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
    SELECT c.protocol, c.address
    FROM auth.users u
    INNER JOIN auth.contacts c ON c.player_name = u.player_name
    AND c.is_active = TRUE
    AND c.delete_ts IS NULL
    WHERE u.player_name = player_name_param;
    IF EXISTS (SELECT * FROM tmp_cnts t WHERE t.protocol IS NOT NULL AND t.address IS NOT NULL) THEN
    WITH hashed_otp AS (VALUES (crypt(otp, gen_salt('bf', 8))))
    INSERT INTO auth.otp (player_name, otp, create_ts)
    VALUES (player_name_param, (TABLE hashed_otp), NOW()::timestamp)
    ON CONFLICT (player_name) DO UPDATE SET otp = (TABLE hashed_otp), create_ts = NOW()::timestamp;
    END IF;
    RETURN QUERY SELECT *
    FROM tmp_cnts;
    END
    $$ LANGUAGE plpgsql;
  • file addition: 20220910_4_auth-deletage-otp.sql (----------)
    [2.15]
    -- Alter function to send OTP to delegate also:
    DROP FUNCTION auth.check_contact;
    CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
    RETURNS TABLE (
    protocol auth.contact_protocol,
    address CITEXT) AS
    $$
    BEGIN
    CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
    SELECT c.protocol, c.address
    FROM auth.users u
    INNER JOIN auth.contacts c ON c.player_name = u.player_name
    AND c.is_active = TRUE
    AND c.delete_ts IS NULL
    WHERE u.player_name = player_name_param
    UNION ALL
    SELECT c.protocol, c.address
    FROM games.players p
    INNER JOIN auth.users u ON u.player_name = p.delegate_name
    INNER JOIN auth.contacts c ON c.player_name = u.player_name
    AND c.is_active = TRUE
    AND c.delete_ts IS NULL
    WHERE p.player_name = player_name_param
    AND p.game_uid = game_uid_param
    AND delegate_name IS NOT NULL;
    IF EXISTS (SELECT * FROM tmp_cnts t WHERE t.protocol IS NOT NULL AND t.address IS NOT NULL) THEN
    WITH hashed_otp AS (VALUES (crypt(otp, gen_salt('bf', 8))))
    INSERT INTO auth.otp (player_name, otp, create_ts)
    VALUES (player_name_param, (TABLE hashed_otp), NOW()::timestamp)
    ON CONFLICT (player_name) DO UPDATE SET otp = (TABLE hashed_otp), create_ts = NOW()::timestamp;
    END IF;
    RETURN QUERY SELECT *
    FROM tmp_cnts;
    END
    $$ LANGUAGE plpgsql;
  • file addition: 20220910_3_games-player-team_id.sql (----------)
    [2.15]
    -- Add field for team id:
    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 = 'players' AND n.nspname = 'games' AND a.attname = 'team_id'
    ) THEN
    ALTER TABLE games.players ADD COLUMN team_id INT NOT NULL DEFAULT -1;
    END IF;
    END
    $$;
  • file addition: 20220910_2_games-player-delegate_name.sql (----------)
    [2.15]
    -- Add field for delegate player:
    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 = 'players' AND n.nspname = 'games' AND a.attname = 'delegate_name'
    ) THEN
    ALTER TABLE games.players ADD COLUMN delegate_name CITEXT REFERENCES auth.users(player_name) NULL;
    END IF;
    END
    $$;
  • file addition: 20220910_1_auth-game-uid.sql (----------)
    [2.15]
    -- Add new argument to accept game UID to search for player delegate:
    DROP FUNCTION IF EXISTS auth.check_contact(player_name_param CITEXT, otp CHAR(6));
    DROP FUNCTION IF EXISTS auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid VARCHAR(20));
    CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid VARCHAR(20))
    RETURNS TABLE (
    protocol auth.contact_protocol,
    address CITEXT) AS
    $$
    BEGIN
    CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
    SELECT c.protocol, c.address
    FROM auth.users u
    LEFT JOIN auth.contacts c ON c.player_name = u.player_name
    AND c.is_active = TRUE
    AND c.delete_ts IS NULL
    WHERE u.player_name = player_name_param;
    IF EXISTS (SELECT * FROM tmp_cnts t WHERE t.protocol IS NOT NULL AND t.address IS NOT NULL) THEN
    WITH hashed_otp AS (VALUES (crypt(otp, gen_salt('bf', 8))))
    INSERT INTO auth.otp (player_name, otp, create_ts)
    VALUES (player_name_param, (TABLE hashed_otp), NOW()::timestamp)
    ON CONFLICT (player_name) DO UPDATE SET otp = (TABLE hashed_otp), create_ts = NOW()::timestamp;
    END IF;
    RETURN QUERY SELECT *
    FROM tmp_cnts;
    END
    $$ LANGUAGE plpgsql;
  • file addition: 20220910_0_games-tables.sql (----------)
    [2.15]
    -- Create table itself:
    CREATE TABLE IF NOT EXISTS games.games (
    game_uid VARCHAR(20) PRIMARY KEY,
    start_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL
    );
    GRANT SELECT ON games.games TO freeorion;
    -- Create table to link users and games:
    CREATE TABLE IF NOT EXISTS games.players (
    game_uid VARCHAR(20) REFERENCES games.games(game_uid),
    player_name CITEXT REFERENCES auth.users(player_name),
    is_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
    species VARCHAR(20) NOT NULL DEFAULT 'RANDOM',
    CONSTRAINT pk_players PRIMARY KEY (game_uid, player_name)
    );
    GRANT SELECT ON games.players TO freeorion;