Update globals and database schema

[A-dam]

I would like to release update of the game with two new global variables (they are accessed during the game) and updated database schema. I have put some code for backward compatibility and tested it in studio where it works perfectly. But im not sure if it will work in real.

In case of new global variables in studio, even if they were not initialized, they worked. I noticed the proper column in table "global" occured, just after using

Code: Select all

 self::setGameStateValue()

even reading did not caused eny error.

For old games without new database schema, I used checks like this:

Code: Select all

  $sql = "SHOW COLUMNS FROM `player` LIKE 'end_vote_send' ";
  if ( !is_null(self::getUniqueValueFromDB( $sql)) )    ...  

Will the behaviour will be the same in real?

Thanks

[Een]

Studio and Prod are working the same way and aligned in terms of versions, so it should work the same in production.

Please note that upgrading the database schema is usually managed through upgradeTableDb rather than setting up checks on the structure in the code. See http://en.doc.boardgamearena.com/Post-r ... ase_schema

[sourisdudesert]

Hi,

Just to add that upgrading the database shema MUST be done using upgradeTableDb, as specified in the link provided by Een before

Using "show column" can leads to unexpected behavior and useless slowdown of the game.

[A-dam]

Thank you for reply and sorry I havent noticed that link earlier.

Is the function upgradeTableDb called only for games in production? In studio my old game will crash because of unknown columns in player tabel, which is not upgraded at all.

Also, if I understand it correclty, db name should be `DBPREFIX_player` ?

[Een]

Is the function upgradeTableDb called only for games in production? In studio my old game will crash because of unknown columns in player tabel, which is not upgraded at all.

Development version number is always 999999-9999. So you should be able to test the upgrade by using this version number, then making a game action, then changing the number back (so that it doesn't apply after every action).

Also, if I understand it correclty, db name should be `DBPREFIX_player` ?

Yes, DBPREFIX_ will be substituted at runtime by self::applyDbUpgradeToAllDB() to update all the zz_replay tables.

[A-dam]

Sorry to bother you again. But it seems I dont understand it.

I used Een's advice to use version 9999999999 in upgradeTableDb and I get into those problems.

1. apparantely the method was triggered in "game" state, not in "player" state nor at the game loading. As Im using db access to new columns in args functions, I had to bypass it again with using "show column".

2. after the trigger I got this error:

Code: Select all

Unexpected error: Error while processing SQL request: INSERT INTO zz_replay1_player (`player_no`,`player_id`,`player_canal`,`player_name`,`player_avatar`,`player_color`,`player_score`,`player_score_aux`,`player_zombie`,`player_ai`,`player_eliminated`,`player_next_notif_no`,`player_enter_game`,`player_over_time`,`player_is_multiactive`,`player_start_reflexion_time`,`player_remaining_reflexion_time`,`player_beginner`,`end_vote`,`end_vote_send`) SELECT `player_no`,`player_id`,`player_canal`,`player_name`,`player_avatar`,`player_color`,`player_score`,`player_score_aux`,`player_zombie`,`player_ai`,`player_eliminated`,`player_next_notif_no`,`player_enter_game`,`player_over_time`,`player_is_multiactive`,`player_start_reflexion_time`,`player_remaining_reflexion_time`,`player_beginner`,`end_vote`,`end_vote_send` FROM player Unknown column 'end_vote' in 'field list'

this for me means, that player table was not updated at all (possible mistake in my sql request?, omit DBPREFIX?) and worse, same error will now occur in every game with old DB schema or every new game with old DB schema. Doesnt matter if I re-write the upgradeTableDb function.

Does this mean when the function upgradeTableDb is once triggered, it is stored and called for every running game DB and for every new game with version 9999999999? If so, to get back, I would need to run game with new DB and rewrite upgradeTableDb function with DB downgrade?

I know this will be probably out of question in production because of different versions and when the sql reguest inside upgradeTableDb will be correct - and thats what I wanted to test primarily, to be sure!

Thank you!

[Een]

Yes, this is actually not easy to test on the studio...
The framework code runs the upgradeTableDb function if it detects a game running with an older database scheme.
Since in the studio the version is always the same, this function is not reliably called.

But you can run it from the chat with this handy trick http://en.doc.boardgamearena.com/Tools_ ... m_the_chat, by typing "upgradeTableDb( YYMMDDHHMM )" in the chat with YYMMDDHHMM your target prod version.

Then I forgot about the cache. PHP cache will store the old definition of the insert requests, and continue to use it after the upgrade which explains the error.
You can still check in the database that the upgrade went well (button "Go to game database" under the play zone). If the changes have been applied, then it's good.
When deploying a new version in production the web server is reloaded which clears the cache. Maybe we should add a "Clear PHP cache" function in the game management page on the studio to account for testing these upgrades even if they should be rare.

Hope this makes things easier and more understandable

EDIT: a clear PHP cache function has been added on the "manage game" page. So you should be able to test by
1) executing upgradeTableDb from the chat
2) clearing the PHP cache from the "manage game" page.
Please let me know how it goes

[A-dam]

Thank you ver much Een for clarification!

Using function from the chat and clearing the PHP cache is great. You can test your versions as you want now, it wokrs perfectly!

I have my new version ready (and now also well tested ) is it safe to move it to production or is it better to do it with your assistance (as noted in http://en.doc.boardgamearena.com/Post-r ... ase_schema)?

[Victoria_La]

I would be asking what kind of database schema it was that requires major modification after release?
Most games I know have one or two own tables which are so generic so they never require any kind of db changes.

[A-dam]

I would be asking what kind of database schema it was that requires major modification after release?
Most games I know have one or two own tables which are so generic so they never require any kind of db changes.

Hi Victoria, it is not exactly about the db of the game itself. I have decided that the game can end if the score cannot be changed anymore (as it was desired by the community). But stric automatic end in that case feels too harsh for me, because players can have different reasons to play the game till finish according to the rules. So I let them vote about the end of the game and for that I need extension to the player DB table...

I know it can be done without it, with the strict end, but I think this is the better way. Hope Im not wrong...