Hi Adrian, thank you for the reply -
On Fri, Mar 2, 2018 at 3:05 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 03/02/2018 05:52 AM, Alexander Farber wrote:
>
>>
>> in PostgreSQL 10.3 I have the following table with a jsonb column:
>>
>> # \d words_moves;
>> Table "public.words_moves"
>> Column | Type | Collation | Nullable |
>> Default
>> --------+--------------------------+-----------+----------+-
>> -----------------------------------------
>> mid | bigint | | not null |
>> nextval('words_moves_mid_seq'::regclass)
>> action | text | | not null |
>> gid | integer | | not null |
>> uid | integer | | not null |
>> played | timestamp with time zone | | not null |
>> tiles | jsonb | | |
>> score | integer | | |
>> Indexes:
>> "words_moves_pkey" PRIMARY KEY, btree (mid)
>> Check constraints:
>> "words_moves_score_check" CHECK (score >= 0)
>> Foreign-key constraints:
>> "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
>> ON DELETE CASCADE
>> "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
>> ON DELETE CASCADE
>> Referenced by:
>> TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>> TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>>
>>
>> # select mid, jsonb_array_length(tiles) from words_moves where gid=609;
>> ERROR: 22023: cannot get array length of a scalar
>> LOCATION: jsonb_array_length, jsonfuncs.c:1579
>>
>> What am I doing wrong here please?
>>
>
> Are you sure all the values in tiles are correctly formatted because when
> I use jsonb_array_length with the provided data:
>
> test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2,
> "letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 5,
> "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row": 7, "value": 0,
> "letter": "Л"}, {"col": 7, "row": 7, "value": 3, "letter": "Я"}]');
> jsonb_array_length
> --------------------
> 5
>
>
I fill that table with the following stored function (please pardon the
huge listing):
CREATE OR REPLACE FUNCTION words_play_game(
in_uid integer,
in_gid integer,
in_tiles jsonb
) RETURNS table (
out_uid integer, -- the player to be notified
out_fcm text,
out_apns text,
out_adm text,
out_body text
) AS
$func$
DECLARE
_tile jsonb;
_letter char;
_value integer;
_col integer;
_row integer;
_pos integer;
_mid bigint;
_total integer;
_hand_len integer;
_pile_len integer;
_move_len integer;
_pile char[];
_hand char[];
_letters char[][];
_values integer[][];
_opponent integer;
_finished timestamptz;
_reason text;
_score1 integer;
_score2 integer;
BEGIN
IF EXISTS (SELECT 1 FROM words_users
WHERE uid = in_uid AND
banned_until > CURRENT_TIMESTAMP) THEN
RAISE EXCEPTION 'User % is banned', in_uid;
END IF;
-- fetch the 4 arrays (_hand, _pile, _letters, _values) for the
current game
SELECT
hand1,
pile,
letters,
values
INTO
_hand,
_pile,
_letters,
_values
FROM words_games WHERE
gid = in_gid AND
player1 = in_uid AND
-- game is not over yet
finished IS NULL AND
-- and it is first player's turn
(played1 IS NULL OR played1 < played2);
IF NOT FOUND THEN
SELECT
hand2,
pile,
letters,
values
INTO
_hand,
_pile,
_letters,
_values
FROM words_games WHERE
gid = in_gid AND
player2 = in_uid AND
-- game is not over yet
finished IS NULL AND
-- and it is second player's turn
(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;
PERFORM words_check_positions(in_uid, in_gid, in_tiles);
FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
LOOP
_letter := _tile->>'letter';
_value := (_tile->>'value')::int;
_col := (_tile->>'col')::int + 1;
_row := (_tile->>'row')::int + 1;
IF NOT words_valid_tile(_letter, _value) THEN
RAISE EXCEPTION 'Invalid tile = %', _tile;
END IF;
-- search for the played tile in the player hand
IF _value = 0 THEN
_pos := ARRAY_POSITION(_hand, '*');
ELSE
_pos := ARRAY_POSITION(_hand, _letter);
END IF;
IF _pos >= 1 THEN
_hand[_pos] := NULL;
ELSE
RAISE EXCEPTION 'Tile % not found in hand %',
_tile, _hand;
END IF;
_letters[_col][_row] := _letter;
_values[_col][_row] := _value;
END LOOP;
-- remove played tiles from player hand
_hand := ARRAY_REMOVE(_hand, NULL);
-- move up to 7 missing tiles from pile to hand
_hand_len := CARDINALITY(_hand);
_pile_len := CARDINALITY(_pile);
_move_len := LEAST(7 - _hand_len, _pile_len);
_hand := _hand || _pile[1:_move_len];
_pile := _pile[(1 + _move_len):_pile_len];
INSERT INTO words_moves (
action,
gid,
uid,
played,
tiles
) VALUES (
'play',
in_gid,
in_uid,
CURRENT_TIMESTAMP,
in_tiles
) RETURNING mid INTO STRICT _mid;
INSERT INTO words_scores (
mid,
gid,
uid,
word,
score
) ( SELECT
_mid,
in_gid,
in_uid,
out_word,
max(out_score)
FROM words_check_words(in_uid, in_gid, in_tiles)
GROUP BY out_word);
SELECT
SUM(score),
words_get_given(in_uid) || ': ' || STRING_AGG(FORMAT('%s
(%s)', word, score), ', ')
INTO STRICT
_total,
out_body
FROM words_scores
WHERE mid = _mid;
if _move_len = 7 THEN
_total := _total + 15;
out_body := out_body || ' +15 бонус';
END IF;
-- player has no tiles, game over
IF CARDINALITY(_hand) = 0 THEN
_finished := CURRENT_TIMESTAMP;
_reason := 'regular';
-- TODO append win, loss, draw to out_body
END IF;
UPDATE words_moves SET
score = _total
WHERE mid = _mid;
-- RAISE NOTICE '_hand = %', _hand;
-- RAISE NOTICE '_pile = %', _pile;
-- RAISE NOTICE '_letters = %', _letters;
-- RAISE NOTICE '_values = %', _values;
-- RAISE NOTICE '_hand_len = %', _hand_len;
-- RAISE NOTICE '_pile_len = %', _pile_len;
-- RAISE NOTICE '_move_len = %', _move_len;
-- RAISE NOTICE '_total = %', _total;
-- TODO update score and store played words and stats
UPDATE words_games SET
finished = _finished,
reason = _reason,
played1 = CURRENT_TIMESTAMP,
score1 = score1 + _total,
hand1 = _hand,
pile = _pile,
letters = _letters,
values = _values,
state1 = words_get_state(_finished, score1 + _total,
score2),
state2 = words_get_state(_finished, score2, score1 +
_total),
hint1 = words_get_hint(_finished, FALSE, score1 +
_total, score2),
hint2 = words_get_hint(_finished, TRUE, score2, score1 +
_total)
WHERE
gid = in_gid AND
player1 = in_uid AND
-- game is not over yet
finished IS NULL AND
-- and it is first player's turn
(played1 IS NULL OR played1 < played2)
RETURNING
player2,
score1,
score2
INTO
_opponent,
_score1,
_score2;
IF NOT FOUND THEN
UPDATE words_games SET
finished = _finished,
reason = _reason,
played2 = CURRENT_TIMESTAMP,
score2 = score2 + _total,
hand2 = _hand,
pile = _pile,
letters = _letters,
values = _values,
state1 = words_get_state(_finished, score1,
score2 + _total),
state2 = words_get_state(_finished, score2 +
_total, score1),
hint1 = words_get_hint(_finished, TRUE, score1,
score2 + _total),
hint2 = words_get_hint(_finished, FALSE, score2
+ _total, score1)
WHERE
gid = in_gid AND
player2 = in_uid AND
-- game is not over yet
finished IS NULL AND
-- and it is second player's turn
(played2 IS NULL OR played2 < played1)
RETURNING
player1,
score2,
score1
INTO
_opponent,
_score1,
_score2;
END IF;
IF NOT FOUND THEN
RAISE EXCEPTION 'Game % not found for user %', in_gid,
in_uid;
END IF;
-- this is the very first move in 1-player game, notification not
needed
IF _opponent IS NULL THEN
RETURN;
END IF;
SELECT
_opponent,
fcm,
apns,
adm
FROM words_users
WHERE uid = _opponent
INTO STRICT
out_uid,
out_fcm,
out_apns,
out_adm;
-- add 1 row (containing notification) to the output table
RETURN NEXT;
END
$func$ LANGUAGE plpgsql;
