Looking for complete sample of upgradeTableDb and DBPREFIX_

[MikeIsHere]

I added the following to my dbmodel

Code: Select all

ALTER TABLE `player` ADD `previous_score` INT  NOT NULL DEFAULT '-1';

What would be the equivalent update statement in upgradeTableDB?
I don't want to run create table as I did not create the player table in the first place

[vincentt]

Hi

I guess it is the same.
This is what I did on Dice Forge after adding a field

Code: Select all

$sql = "ALTER TABLE DBPREFIX_player ADD twins int(1) DEFAULT 0";
self::applyDbUpgradeToAllDB( $sql );

Cheers

[MikeIsHere]

This is the code I ran, I tried $from_version as an int and string, doesn't seem to fix old games

Code: Select all

if( $from_version < '2006201516' ) {
            $sql = "ALTER TABLE DBPREFIX_player ADD previous_score int not null DEFAULT '-1'";
            self::applyDbUpgradeToAllDB( $sql );
        }

Error

20/06 23:21:49 [error] [T96592923] [85415875/yoyoyo2020] Error (1054) while processing SQL request: Unknown column 'previous_score' in 'field list' - Request: SELECT player_id id, player_score score, previous_score FROM player
20/06 23:21:49 [error] [T96592923] [85415875/yoyoyo2020] Unexpected exception: Error while processing SQL request: SELECT player_id id, player_score score, previous_score FROM player
Unknown column 'previous_score' in 'field list' (BGA service error 1592688109)
#0 /var/tournoi/release/tournoi-200603-1012-gs/www/include/APP_DbObject.inc.php(455): APP_DbObject::DbQuery('SELECT player_i...', NULL)
#1 /var/tournoi/release/games/cribbage/200620-2315/cribbage.game.php(195): APP_DbObject->getCollectionFromDB('SELECT player_i...')
#2 /var/tournoi/release/tournoi-200603-1012-gs/www/game/module/table/table.game.php(152): Cribbage->getAllDatas()
#3 /var/tournoi/release/tournoi-200603-1012-gs/www/view/common/game.view.php(417): Table->getAllTableDatas()
#4 /var/tournoi/release/tournoi-200603-1012-gs/www/view/common/game.view.php(341): game_view->post_generate(Array)
#5 /var/tournoi/release/tournoi-200603-1012-gs/www/view/common/ebg.view.php(23): game_view->generate_content(Array)
#6 /var/tournoi/release/tournoi-200603-1012-gs/www/include/webActionCore.inc.php(231): ebg_view->generate(Array)
#7 /var/tournoi/release/tournoi-200603-1012-gs/www/index.php(230): launchWebAction('cribbage', 'action_cribbage', '__default', false, false, NULL, true, false)
#8 {main}
http://boardgamearena.com/2/cribbage?table=96592923

[Een]

You used < instead of <=

To apply a db upgrade with this function, it should be <= to the game release number currently in prod.

[MikeIsHere]

So I am not grasping how this still works

Build 200620-1516 added the new field, there have been 2 builds after that 2006202233 and 2006202315
I would think I would only want to fix those games that are on anything less than 2006201516

If I do a new build and make $from_version less than or equal to the current build, will the column be applied twice (once in the dbmodel and once in the upgrade db)?

[Een]

If version X is in prod and you want to deploy version Y with a change to dbmodel.

Then your upgradeTableDb function should look like this:

if( $from_version <= 'X') {
$sql = "ALTER TABLE DBPREFIX_player ADD previous_score int not null DEFAULT '-1'";
self::applyDbUpgradeToAllDB( $sql );
}

After version Y is deployed, the first time there is an action on a table who was created with a version <= 'X' is loaded, this section of upgradeTableDb is applied, and the table version number becomes 'Y'.

For tables created after version Y is deployed, the table version number is directly Y, so this section of upgradeTableDb is never applied.

Hope it helps!

[Een]

Build 200620-1516 added the new field, there have been 2 builds after that 2006202233 and 2006202315
I would think I would only want to fix those games that are on anything less than 2006201516

Actually that should work too, it's another way to write the same thing. If it doesn't, not sure why.

[MikeIsHere]

Here is the latest code

Code: Select all

  if( $from_version < '2006201509' || $from_version < 2006201509 || $from_version < '200620-1509') {
            $sql = "ALTER TABLE DBPREFIX_player ADD previous_score int not null DEFAULT '-1'";
            self::applyDbUpgradeToAllDB( $sql );
        }

I confirm the change was in the 1509 build not 1516 but all tables having issue were created on the 19th
https://boardgamearena.com/table?table=96506083
https://boardgamearena.com/table?table=96592923

I have one player worried about loosing karma -- is there anyway they can abandon the game without penalty?

I might have to make another DB update again -- adding a player preference on handling the GO action, anything I can do different the next time around -- can I ensure no games are active before release?

[Een]

In alpha, since games cannot be considered stable yet, the default is "training mode" so players can quit without penalty (unless they changed the default to normal, which they should not do). Anyway, I have closed these two tables.

Since some tables are turn based, it's not possible to make sure that no table is active when you push your change. But as I said, it's alpha. It's acceptable to break some games (and in principle, your update should work). So I would say, go, and if needed I'll close tables if some are broken.

[MikeIsHere]

Thank you for killing those tables -- I am still doing something wrong -- had another DB update today

Code: Select all

    function upgradeTableDb( $from_version ) { 

        if( $from_version < '2006201509'  || $from_version < '200620-1509') {
            $sql = "ALTER TABLE DBPREFIX_player ADD previous_score int not null DEFAULT '-1'";
            self::applyDbUpgradeToAllDB( $sql );
        }


        if( $from_version <= '2006220146' || $from_version <= '200622-0146') {
            $sql = "ALTER TABLE DBPREFIX_player ADD player_auto_go BOOLEAN NOT NULL DEFAULT '0'";
            self::applyDbUpgradeToAllDB( $sql );
        }   
    }    

Still getting

22/06 20:02:35 [error] [T96824699] [86410058/MikeIsHere] Error (1054) while processing SQL request: Unknown column 'player_auto_go' in 'field list' - Request: SELECT player_id id, player_score score, previous_score, player_auto_go auto_go FROM player
22/06 20:02:35 [error] [T96824699] [86410058/MikeIsHere] Unexpected exception: Error while processing SQL request: SELECT player_id id, player_score score, previous_score, player_auto_go auto_go FROM player
Unknown column 'player_auto_go' in 'field list' (BGA service error 1592848955)
#0 /var/tournoi/release/tournoi-200622-1731-gs/www/include/APP_DbObject.inc.php(455): APP_DbObject::DbQuery('SELECT player_i...', NULL)
#1 /var/tournoi/release/games/cribbage/200622-1958/cribbage.game.php(195): APP_DbObject->getCollectionFromDB('SELECT player_i...')
#2 /var/tournoi/release/tournoi-200622-1731-gs/www/game/module/table/table.game.php(152): Cribbage->getAllDatas()
#3 /var/tournoi/release/tournoi-200622-1731-gs/www/view/common/game.view.php(417): Table->getAllTableDatas()
#4 /var/tournoi/release/tournoi-200622-1731-gs/www/view/common/game.view.php(341): game_view->post_generate(Array)
#5 /var/tournoi/release/tournoi-200622-1731-gs/www/view/common/ebg.view.php(23): game_view->generate_content(Array)
#6 /var/tournoi/release/tournoi-200622-1731-gs/www/include/webActionCore.inc.php(231): ebg_view->generate(Array)
#7 /var/tournoi/release/tournoi-200622-1731-gs/www/index.php(230): launchWebAction('cribbage', 'action_cribbage', '__default', false, false, NULL, true, false)
#8 {main}
http://boardgamearena.com/2/cribbage?table=96824699