Search Postgresql Archives

Re: Check if there 6 last records of same type without gaps

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 6 September 2016 at 12:32, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:
Good afternoon,

for a 2-player game I store moves in the following 9.5.4 table:

CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign');

CREATE TABLE words_moves (
        mid SERIAL PRIMARY KEY,
        action words_action NOT NULL,
        gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
        uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
        played timestamptz NOT NULL,
        tiles jsonb,
        score integer CHECK (score > 0)
);

ALTER TABLE words_games ADD CONSTRAINT words_mid_fk FOREIGN KEY (mid) REFERENCES words_moves;

And then I have a custom function for skipping a move (and inserting a 'skip' into the above table):

CREATE OR REPLACE FUNCTION words_skip_game(
        IN in_uid integer,
        IN in_gid integer,
        OUT out_gid integer)
        RETURNS integer AS
$func$
DECLARE
        _finished timestamptz;
        _score1   integer;
        _score2   integer;
        _uid2     integer;
BEGIN
        INSERT INTO words_moves (
                action,
                gid,
                uid,
                played
        ) VALUES (
                'skip',
                in_gid,
                in_uid,
                CURRENT_TIMESTAMP
        );

Could you please suggest a good way to check that the last 6 moves where 'skip', so that I can end the game when each player skipped her move 3 times in a row?

        IF /* there are 6 'skip's - how to do it please? */ THEN
                _finished = CURRENT_TIMESTAMP;
        END IF;

Below is the rest of my function, thank you for any ideas -

Regards
Alex

        UPDATE words_games SET
                finished = _finished,
                played1  = CURRENT_TIMESTAMP
        WHERE
                gid      = in_gid AND
                player1  = in_uid AND
                finished IS NULL AND
                -- and it is first player's turn
                (played1 IS NULL OR played1 < played2)
        RETURNING
                gid,
                score1,
                score2,
                player2
        INTO
                out_gid,
                _score1, -- my score
                _score2, -- her score
                _uid2;

        IF NOT FOUND THEN
                UPDATE words_games SET
                        finished = _finished,
                        played2  = CURRENT_TIMESTAMP
                WHERE
                        gid      = in_gid AND
                        player2  = in_uid AND
                        finished IS NULL AND
                        -- and it is second player's turn
                        (played2 IS NULL OR played2 < played1);
                RETURNING
                        gid,
                        score2,  -- swapped
                        score1,
                        player1
                INTO
                        out_gid,
                        _score1,  -- my score
                        _score2,  -- her score
                        _uid2;
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        -- game over, update win/loss/draw stat for both players
        IF _finished IS NOT NULL THEN
                IF _score1 > _score2 THEN

                        UPDATE words_users SET
                                win = win + 1
                        WHERE uid = in_uid;

                        UPDATE words_users SET
                                loss = loss + 1
                        WHERE uid = _uid2;

                ELSIF _score1 < _score2 THEN

                        UPDATE words_users SET
                                loss = loss + 1
                        WHERE uid = in_uid;

                        UPDATE words_users SET
                                win = win + 1
                        WHERE uid = _uid2;
                ELSE
                        UPDATE words_users SET
                                draw = draw + 1
                        WHERE uid = in_uid OR uid = _uid2;
                END IF;
        END IF;
END
$func$ LANGUAGE plpgsql;

Get the last 6 record and 

1.  ... action="" as isskip ... then you can group on and count the skip moves. If there is 6 of them the game ends.

2.  ... sum(case when action="" then 1 else 0 end) ... If the result is 6 the game ends

Regards,
Sándor

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux