Hello Mike,
On Wed, Mar 9, 2016 at 2:42 PM, Mike Sofen <mso...@runbox.com> 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;
