Re: How to delete few elements from array beginning?

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hello Mike,

On Wed, Mar 9, 2016 at 2:42 PM, Mike Sofen <msofen@xxxxxxxxxx> wrote:
>
> Have you considered a normal (relational), non-array-based data model for this app (2 or 3 tables in a 1:M/M:M) instead of the single table model you’ve shown?  That would then allow you to use normal sql set-based operations that are readable, understandable, maintainable and very fast/scalable.
>
> When I see row by row operations (looping or cursors) in what should be a real time query…that’s my alarm bell that perhaps the code has wandered off a valid solution path.
>

thank you for your reply.

I have considered that for my Scrabble-like word game, but with rows I would not know how to -

1) swap several tiles with same letter values (like player hand is "AABBCCD" and she swaps "BBC"). With rows and DISTINCT I don't know how to do that

2) how to represent 15 x 15 game board.... ok I could store a varchar(225) string...

For the backend of my game I would like to implement as much as possible in PL/pgSQL and as little as possible in PHP.

A decade ago I implemented a card game as a "hobby programmer project" and I like how its data is still kept clean by PostgreSQL, despite 4000 active players.

Also while implementing the card game I was given a great advice on this mailing list (to use timestamptz instead of year-week strings) and later regretted ignoring it :-) So any advices are welcome

Regards

Alex

P.S. Here my current implementation of letter swapping, any comments are welcome:

CREATE TABLE words_games (
        gid SERIAL PRIMARY KEY,
        created timestamptz NOT NULL,

        player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
        player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

        played1 timestamptz,
        played2 timestamptz,

        hand1 varchar[7] NOT NULL,
        hand2 varchar[7] NOT NULL,
        pile  varchar[116] NOT NULL,

        board varchar[15][15] NOT NULL,
        style integer NOT NULL CHECK (1 <= style AND style <= 4)
);

CREATE OR REPLACE FUNCTION words_swap_game(
        IN in_uid integer,
        IN in_gid integer,
        IN in_swap varchar(7))
        RETURNS void AS
$func$
DECLARE
        i           integer;
        j           integer;
        letter      varchar;
        swapped     integer;
        swap_len    integer;
        hand_len    integer;
        pile_len    integer;
        swap_array  varchar[];
        pile_array  varchar[];
        old_hand    varchar[];
        new_hand    varchar[];
BEGIN
        swap_array := STRING_TO_ARRAY(in_swap, NULL);
        swap_len := ARRAY_LENGTH(swap_array, 1);

        SELECT
                hand1,
                pile,
                ARRAY_LENGTH(hand1, 1),
                ARRAY_LENGTH(pile, 1)
        INTO
                old_hand,
                pile_array,
                hand_len,
                pile_len
        FROM words_games
        WHERE gid = in_gid
        AND player1 = in_uid
        /* and it is first player's turn */
        AND (played1 IS NULL OR played1 < played2);

        IF NOT FOUND THEN
                SELECT
                        hand2,
                        pile,
                        ARRAY_LENGTH(hand2, 1),
                        ARRAY_LENGTH(pile, 1)
                INTO
                        old_hand,
                        pile_array,
                        hand_len,
                        pile_len
                FROM words_games
                WHERE gid = in_gid
                AND player2 = in_uid
                /* and it is second player's turn */
                AND (played2 IS NULL OR played2 < played1);
        END IF;

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

        swapped := 0;
        <<hand_loop>>
        FOR i IN 1..hand_len LOOP
                letter := old_hand[i];

                FOR j IN 1..swap_len LOOP
                        IF swap_array[j] IS NOT NULL AND
                           swap_array[j] = letter THEN
                                /* move letter from swap to pile */
                                pile_array := pile_array || letter;
                                swap_array[j] := NULL;
                                swapped := swapped + 1;
                                CONTINUE hand_loop;
                        END IF;
                END LOOP;

                /* letter was not found in swap, keep it in hand */
                new_hand := new_hand || letter;
        END LOOP;

        IF swapped = 0 OR swapped <> swap_len THEN
                RAISE EXCEPTION 'Invalid swap % for hand %', in_swap, old_hand;
        END IF;

        -- pile_array := words_shuffle(pile_array);
        new_hand   := new_hand || pile_array[1:swapped];
        pile_array := pile_array[(1 + swapped):(pile_len + swapped)];

        UPDATE words_games SET
                hand1   = new_hand,
                pile    = pile_array,
                played1 = CURRENT_TIMESTAMP
        WHERE gid = in_gid
        AND player1 = in_uid
        /* and it is first player's turn */
        AND (played1 IS NULL OR played1 < played2);

        IF NOT FOUND THEN
                UPDATE words_games SET
                        hand2   = new_hand,
                        pile    = pile_array,
                        played2 = CURRENT_TIMESTAMP
                WHERE gid = in_gid
                AND player2 = in_uid
                /* and it is second player's turn */
                AND (played2 IS NULL OR played2 < played1);
        END IF;
END
$func$ LANGUAGE plpgsql;