No Game and Any Game use magic ids that are different for ACUS and ACNW

[evoskamp]

It doesn't seem that these should a be a config: we should find these differently.

Either by title, or a flag on the event

[evoskamp]

I'm considering the evil that men do, and patch up game, game_submission, game_choice, game_assignment in the ACUS database to swap 596-603 with 1-8 and 604 with 144

Or add a "type" field to game: event|none|any

[evoskamp]

Only downside is both require a database call.

[evoskamp]

This is a bit crazy ...

CREATE OR REPLACE PROCEDURE change_game_id(
   source_game_id INT,
   target_game_id INT
)
LANGUAGE plpgsql    
AS $$
BEGIN
    -- clean out the target
    DELETE FROM game_choice WHERE game_id = target_game_id;
    DELETE FROM game_assignment WHERE game_id = target_game_id;
    DELETE FROM game_submission WHERE game_id = target_game_id;
    DELETE FROM game WHERE game_id = target_game_id;

    -- clone soure game to target
    INSERT INTO game (game_id, late_finish, player_max, player_min, room_id, slot_id, players_contact_gm, slot_preference, teen_friendly, "year", author_id, "full", game_contact_email, genre, gm_names, message, description, player_preference, late_start, name, "type", returning_players, setting, short_name, slot_conflicts, char_instructions, estimated_length) SELECT target_game_id, late_finish, player_max, player_min, room_id, slot_id, players_contact_gm, slot_preference, teen_friendly, "year", author_id, "full", game_contact_email, genre, gm_names, message, description, player_preference, late_start, name, "type", returning_players, setting, short_name, slot_conflicts, char_instructions, estimated_length FROM game WHERE id = source_game_id;
     -- update dependencies
    UPDATE game_choice SET game_id = target_game_id WHERE game_id = source_game_id;
    UPDATE game_assignment SET game_id = target_game_id WHERE game_id = source_game_id;
    UPDATE game_submission SET game_id = target_game_id WHERE game_id = source_game_id;
     -- remove cloned game
    DELETE FROM game WHERE game_id = source_game_id;

    COMMIT;
END;$$

We use the procedure above to do a three-way swap:

e.g. Any Game for ACUS is 604 and should be 144:

CALL change_game_id(144, -1);
CALL change_game_id(604, 144);
CALL change_game_id(-1, 604);

Then the No Game in Slot 1 through 8:

Of course we are putting that itself in a stored procedure:

CREATE OR REPLACE PROCEDURE swap_game_ids(
   game_id_a INT,
   game_id_b INT
)
LANGUAGE plpgsql    
AS $$
BEGIN
    -- before
    SELECT * FROM game WHERE id IN (game_id_a, game_id_b);
    SELECT COUNT(*) AS "game_choice_a" FROM game_choice WHERE id IN (game_id_a);
    SELECT COUNT(*) AS "game_choice_b" FROM game_choice WHERE id IN (game_id_b);
    SELECT COUNT(*) AS "game_assignment_a" FROM game_assignment WHERE id IN (game_id_a);
    SELECT COUNT(*) AS "game_assignment_b" FROM game_assignment WHERE id IN (game_id_b);

    -- swap 'em through id = -1 as a buffer
    CALL change_game_id(game_id_a, -1);
    CALL change_game_id(game_id_b, game_id_a);
    CALL change_game_id(-1, game_id_b);

    -- after
    SELECT * FROM game WHERE id IN (game_id_a, game_id_b);
    SELECT COUNT(*) AS "game_choice_a" FROM game_choice WHERE id IN (game_id_a);
    SELECT COUNT(*) AS "game_choice_b" FROM game_choice WHERE id IN (game_id_b);
    SELECT COUNT(*) AS "game_assignment_a" FROM game_assignment WHERE id IN (game_id_a);
    SELECT COUNT(*) AS "game_assignment_b" FROM game_assignment WHERE id IN (game_id_b);
    COMMIT;
END;$$

And then do the swaperoo game on the magic values:

-- any game
CALL swap_game_ids( 144, 604);

And the no game ones

-- no game in slot x
CALL swap_game_ids( 596, 1);
CALL swap_game_ids( 597, 2);
CALL swap_game_ids( 598, 3);
CALL swap_game_ids( 599, 4);
CALL swap_game_ids( 600, 5);
CALL swap_game_ids( 601, 6);
CALL swap_game_ids( 602, 7);
CALL swap_game_ids( 603, 8);

[evoskamp]

Testing locally

[evoskamp]

And, wow, it turns out Postgres doesn't like returning select from stored procedures or functions, let alone multiple ones. Scrap the display.

[evoskamp]

Here's the resulting trickery:

CREATE OR REPLACE PROCEDURE change_game_id(
   source_game_id INT,
   target_game_id INT
)
LANGUAGE plpgsql    
AS $$
BEGIN
    -- clean out the target
    DELETE FROM game_choice WHERE game_id = target_game_id;
    DELETE FROM game_assignment WHERE game_id = target_game_id;
    DELETE FROM game WHERE id = target_game_id;

    -- clone soure game to target
    INSERT INTO game (id, late_finish, player_max, player_min, room_id, slot_id, players_contact_gm, slot_preference, teen_friendly, "year", author_id, "full", game_contact_email, genre, gm_names, message, description, player_preference, late_start, name, "type", returning_players, setting, short_name, slot_conflicts, char_instructions, estimated_length) SELECT target_game_id, late_finish, player_max, player_min, room_id, slot_id, players_contact_gm, slot_preference, teen_friendly, "year", author_id, "full", game_contact_email, genre, gm_names, message, description, player_preference, late_start, name, "type", returning_players, setting, short_name, slot_conflicts, char_instructions, estimated_length FROM game WHERE id = source_game_id;
     -- update dependencies
    UPDATE game_choice SET game_id = target_game_id WHERE game_id = source_game_id;
    UPDATE game_assignment SET game_id = target_game_id WHERE game_id = source_game_id;
     -- remove cloned game
    DELETE FROM game WHERE id = source_game_id;

    COMMIT;
END;$$

CREATE OR REPLACE PROCEDURE swap_game_ids(
   game_id_a INT,
   game_id_b INT
)
LANGUAGE plpgsql    
AS $$
BEGIN
    -- swap 'em through id = -1 as a buffer
    CALL change_game_id(game_id_a, -1);
    CALL change_game_id(game_id_b, game_id_a);
    CALL change_game_id(-1, game_id_b);

    COMMIT;
END;$$

-- any game
SELECT * FROM game WHERE id IN (144,604);
SELECT game_id, COUNT(*) AS "game_choice" FROM game_choice WHERE game_id IN (144,604) GROUP BY game_id;
SELECT game_id, COUNT(*) AS "game_assignment" FROM game_assignment WHERE game_id IN (144,604) GROUP BY game_id;

CALL swap_game_ids( 144, 604);

SELECT * FROM game WHERE id IN (144,604);
SELECT game_id, COUNT(*) AS "game_choice" FROM game_choice WHERE game_id IN (144,604) GROUP BY game_id;
SELECT game_id, COUNT(*) AS "game_assignment" FROM game_assignment WHERE game_id IN (144,604) GROUP BY game_id;

-- no game in slot x
CALL swap_game_ids( 596, 1);
CALL swap_game_ids( 597, 2);
CALL swap_game_ids( 598, 3);
CALL swap_game_ids( 599, 4);
CALL swap_game_ids( 600, 5);
CALL swap_game_ids( 601, 6);
CALL swap_game_ids( 602, 7);
CALL swap_game_ids( 603, 8);

DROP PROCEDURE IF EXISTS swap_game_ids;

DROP PROCEDURE IF EXISTS change_game_id;

[evoskamp]

I'll do more testing, but I'm running locally with the new IDs and cleaned up code. I'll do some specific testing before calling this one good.

[evoskamp]

It works, but it would be nicer to add a type field to the event: game|any game|no game but it require rejiggering of a fair bit with regards to the id checks in game dialog.

[ggascoigne]

I do think that that's a good idea, but also a lot of work. Perhaps something for the summer when we're not in a hurry. Your approach makes a lot of sense for right now.

[evoskamp]

Yeah I looked at it and it was too ugly. Right now it has methods to check if the id is slot or any magical, and I really didn't want to touch it. That seemed asking for trouble. Playing SQL games ... eh. Not a big deal. Worst case I mess up past game choices and assignments which currently we don't use.

[evoskamp]

I'll hack toLocal.sh to keep a local copy, just in case, but I've done it on local, tested it against several people I know take slots off. It's pretty safe.