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

On 6 September 2016 at 12:32, Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
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='SKIP' 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='SKIP' then 1 else 0 end) ... If the result is
6 the game ends

Regards,
Sándor