Save scores of 3 players per game into PostgreSQL

Question

I'm trying to save scores of card game matches (always with 3 players) into PotsgreSQL 8.4.9

I have almost everything there (s. below please) just 2 minor parts are missing.

1. As first I'm trying to create a PL/pgSQL procedure for saving scores
2. And then I need a join select statement to retrieve match date, player names, gender, avatars, scores and if a player has quit the game prematurely

I've created 3 SQL tables:

    create table pref_users (
            uid varchar(32) primary key,
            first_name varchar(64),
            female boolean,
            avatar varchar(128)
    }

    create table pref_games {
            gid serial,
            rounds integer not null,
            finished timestamp default current_timestamp
    }

    create table pref_scores (
            uid varchar(32) references pref_users,
            gid serial references pref_games,  /* XXX serial ok here? */
            money integer not null,
            quit boolean
    );

Here is my PL/pgSQL procedure where I need some help please:

    create or replace function pref_insert_scores(
        _uid0 varchar, _money0 integer, _quit0 boolean,
        _uid1 varchar, _money1 integer, _quit1 boolean,
        _uid2 varchar, _money2 integer, _quit2 boolean,
        _rounds integer) returns void as $BODY$
            begin

            insert into pref_games (rounds) values (_rounds);

            -- XXX how do I get the _gid of this new game?

            insert into pref_scores (uid, gid, money, quit)
                values(_uid0, _gid, _money0, _quit0);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid1, _gid, _money1, _quit1);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid2, _gid, _money2, _quit2);
            end;
    $BODY$ language plpgsql;

And then I need some help to join the scores with first_name, female, avatar data from the first table - so that I can display a list of played games for the last 7 days in a table at a web page:

01.12.2011  Alice $10        Bob $20 Charlie -$30  17 rounds
01.12.2011  Alice $0 (quit)  Bob $20 Charlie -$20  5  rounds

UPDATE:

With mu is too short's help I have now my tables filled with data, but still can't figure out how to list all games performed by a player - together with his 2 opponents and their scores.

I have a table containing all games played:

# select * from pref_games limit 5;
 gid | rounds |          finished
-----+--------+----------------------------
  1 |     10 | 2011-10-26 14:10:35.46725
  2 |     12 | 2011-10-26 14:34:13.440868
  3 |     12 | 2011-10-26 14:34:39.279883
  4 |     14 | 2011-10-26 14:35:25.895376
  5 |     14 | 2011-10-26 14:36:56.765978

Then here I have all 3 players (and their scores) who participated in game #3:

# select * from pref_scores where gid=3;
         uid           | gid | money | quit
-----------------------+-----+-------+------
 OK515337846127        |   3 |   -37 | f
 OK40798070412         |   3 |   -75 | f
 MR2871175175044094219 |   3 |   112 | f

And these are all games played by the player with uid = DE9411:

# select * from pref_scores where id='DE9411';
  uid   | gid | money | quit
--------+-----+-------+------
 DE9411 |  43 |    64 | f
 DE9411 | 159 |  -110 | f
 DE9411 | 224 |    66 | f
 DE9411 | 297 |   -36 | f
 DE9411 | 385 |    29 | f
 DE9411 | 479 |   -40 | f
 DE9411 | 631 |   -14 | f
 DE9411 | 699 |   352 | f
 DE9411 | 784 |   -15 | f
 DE9411 | 835 |   242 | f

But how do I list the 2 other players and their scores in the above result set?

Answer 1

        gid serial references pref_games,  /* XXX serial ok here? */

Here you should use 'int' rather than 'serial' - as you need to reference a very specific record in pref_games.

Answer 2

In the line:

        finished timestamp default current_timestamp

I would strongly suggest using

        finished timestamptz default current_timestamp

as this allows for saner date/time arithmetic with respect to daylight saving changes as the actual date/time is stored in GMT - it is also better suited for internationalisation.

From the pg9.1.1 manual '8.5. Date/Time Types':

[...]

Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. (Releases prior to 7.3 treated it as timestamp with time zone.) timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

[...]

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

[...]

Answer 3

This query should work for grabbing the list you need with the current schema. However, with so many joins, performance might not be so great. Since you know you always have 3 users, depending on the expected size of this table, you might want to test performance for having a single game table with the user's information de-normalized.

select g.finished, u1.uid u1, s1.money m1, s1.quit q1,  u2.uid u2, s2.money m2, s2.quit
q2, u3.uid u3, s3.money m3, s3.quit q3, g.rounds
from pref_games g
inner join pref_scores s1 on s1.gid = g.gid 
inner join pref_scores s2 on s2.gid = g.gid and s2.uid > s1.uid
inner join pref_scores s3 on s3.gid = g.gid and s3.uid > s2.uid
inner join pref_users u1 on s1.uid = u1.uid
inner join pref_users u2 on s2.uid = u2.uid
inner join pref_users u3 on s3.uid = u3.uid
and g.finished > CURRENT_DATE - interval '1 week'

Answer 4

You don't want serial in pref_scores, just int:

create table pref_scores (
        uid varchar(32) references pref_users,
        gid int references pref_games,  /* XXX serial ok here? */
        money integer not null,
        quit boolean
);

You want to use INSERT ... RETURNING ... INTO:

create or replace function pref_insert_scores(
    _uid0 varchar, _money0 integer, _quit0 boolean,
    _uid1 varchar, _money1 integer, _quit1 boolean,
    _uid2 varchar, _money2 integer, _quit2 boolean,
    _rounds integer) returns void as $BODY$
declare
    _gid int;
begin
    insert into pref_games (rounds) values (_rounds) returning gid into _gid;
    -- etc...