301 Days (redux)

Another metric leap year of gamedev experiments and such

Day 74 - Ale and SQL

Oct 1, 2016 - 8 minute read - OldDaysseitan-spin

Automated inebriation.

In which we just want a drink.

Test Every Day

More Effects - a day at the pub

Ok, now to proceed orderly through the character effects…

DragonsSpine/GameSystems/Effects/Effect.cs GitLab 
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623

switch (effectType)
{
    case EffectType.Dog_Follow:
        target.FollowName = caster.Name;
        effect.caster = null;
        break;
    #region Strictly Bottle Effects
    case EffectType.Ale:
        target.SendToAllInSight(target.Name + " burps loudly.");
        break;
    case EffectType.Balm: // healing over time
        Effect.DoBalmEffect(target, target.effectList[effectType]);
        break;
    case EffectType.Beer:
        target.SendToAllInSight(target.Name + " burps.");
        break;

Dog_Follow sounds interesting, but we’ll start with the effects which don’t need a “caster”. Ale and Beer effects have an initial audio cue, and nothing else as far as I can tell. That should be easy to test:

features/player_effects.feature GitLab 
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

## Ale
@WIP
Scenario: Ale effect gives the desired output - same player, beginning
    Given I use the "minimal" database
    And I add player and character "TestAle01"
    And I give the character an ale effect of "999" for "2" turns
    And the server is started
    When I log on as "TestAle01"
    And I enter the game
    And I saw a message "You burp loudly."

@WIP
Scenario: Ale effect gives the desired output - same player, wearing off
    Given I use the "minimal" database
    And I add player and character "TestAle01"
    And I give the character an ale effect of "999" for "2" turns
    And the server is started
    When I log on as "TestAle01"
    And I enter the game
    And within "2" turns I see the message "The Ale spell has worn off."

@WIP
Scenario: Ale effect gives the desired output - other player in same cell
    Given I use the "minimal" database
    And I add player and character "TestAle01"
    And I give the character an ale effect of "999" for "999" turns
    And I add player and character "TestAle02"
    And the server is started
    When I log on as "TestAle02"
    And I enter the game
    And I rest
    Then I cause "TestAle01" to log on and enter the game
    And within "4" turns I see the message "TestAle01_name burps loudly."

@WIP
Scenario: Ale effect gives the desired output - other player in neighboring cell
    Given I use the "minimal" database
    And I add player and character "TestAle01"
    And I give the character an ale effect of "999" for "999" turns
    And I add player and character "TestAle02"
    And the server is started
    When I log on as "TestAle02"
    And I enter the game
    And I move east
    Then I cause "TestAle01" to log on and enter the game
    And within "4" turns I see the message "TestAle01_name burps loudly."

Can you guess how many of these actually pass?

Failing Scenarios:
cucumber features/player_effects.feature:55 # Scenario: Ale effect gives the desired output - same player, beginning
cucumber features/player_effects.feature:75 # Scenario: Ale effect gives the desired output - other player in same cell

4 scenarios (2 failed, 2 passed)
34 steps (2 failed, 32 passed)
1m30.355s

Two things: There’s no indication to the player themselves that they burped, and the broadcast of their burping with SendToAllInSight seems to skip characters in the same cell. But there’s a third thing:

                              A dog
˜˜˜˜˜˜˜˜˜˜˜˜˜˜
˜˜. . . . . . 
˜˜. . > . . . 
˜˜. . . . . . 
˜˜. . . A . . 
˜˜. . . . . . 

TestAle01_name burps loudly.
TestAle01_name burps loudly.






 ->

R                           Hits       : 36/36            Hits Taken : 0
L                           Experience : 4996  Stamina    : 10

The ale’d character always burps twice. A quick glance at the server code indicates that the effects may be generated twice during the character load; duplicate effects are prevented on an ongoing basis, but the initial effect is doubled.

Of course we need to add a test to catch this bug:

features/player_effects.feature GitLab 
104
105
106
107
108
109
110
111
112
113
114

Scenario: Ale effect gives the desired output - burp only once
    Given I use the "minimal" database
    And I add player and character "TestAle01"
    And I give the character an ale effect of "999" for "999" turns
    And I add player and character "TestAle02"
    And the server is started
    When I log on as "TestAle02"
    And I enter the game
    And I move east
    Then I cause "TestAle01" to log on and enter the game
    And within "2" turns I see the message "TestAle01_name burps loudly." only once
features/steps/character_steps.rb GitLab 
161
162
163
164
165
166
167
168

Then(/^within "([^"]*)" turns I see (a|the) message "([^"]*)" only once$/) do |max_turns, athe, waited_for|
  sightings = 0
  (1..(max_turns.to_i)).each do |turn|
    sightings += @last_resp.scan(waited_for).size
    resp = telnet_command(@connection, "\"I'm waiting #{turn}", / ->/)
  end
  expect(sightings).to be_equal(1)
end

…which we immediately mark with @bug and run all of the non-bugged tests:

55 scenarios (55 passed)
486 steps (486 passed)
13m58.504s

Tackle a TODO every other day

One of my big TODOs on the test code is to abstract away the SQL-specific code. It’s very unlikely that the new server data stores will be SQL-based, and it’s a lot of repetition anyway.

Where do we start? A quick search shows seven separate calls to TinyTds::Client.new; let’s refactor those so they just call connect_to_db. And to make sure we haven’t broken anything, we’ll clear out the database server and run the tests…

Database 'minimal' does not exist. Make sure that the name is entered correctly. (TinyTds::Error)
./features/lib/db_helper_sql.rb:55:in `new'
./features/lib/db_helper_sql.rb:55:in `connect_to_db'
./features/lib/server_helper.rb:19:in `log_contains'
./features/steps/server_steps.rb:41:in `/^I allow time for the server to complete startup$/'
features/config_file.feature:10:in `And I allow time for the server to complete startup'

Oops. Turns out that renaming the databases leaves the filenames the same; so recreating them failed, but accessing them also failed. Properly deleting them and rerunning…

55 scenarios (55 passed)
486 steps (486 passed)
20m4.120s

Ok, now let’s find all of the SELECTs in the main test code and do something about them.

One Instructive Example

features/lib/hooks.rb GitLab 
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

After('@db_cleanup') do |scenario|
  client = connect_to_db(@server_database)
  result = client.execute("DELETE
    FROM [#{@server_database}].[dbo].[Account]
    WHERE account LIKE 'test%'
    AND email LIKE '%301days.com'")
  debug_msg "Rows deleted from [Account]: #{result.do}"
  result = client.execute("SELECT playerID, account, name
    FROM [#{@server_database}].[dbo].[Player]
    WHERE account LIKE 'test%'
    AND name LIKE 'Dude.%'")
  players_to_delete = []
  result.each(:symbolize_keys => true) do |rowset|
    players_to_delete << rowset[:playerID]
  end
  debug_msg "Players to delete: #{players_to_delete}"
  player_tables = ["PlayerBelt","PlayerEffects","PlayerFlags","PlayerHeld","PlayerLocker",
    "PlayerQuests","PlayerRings","PlayerSack","PlayerSettings","PlayerSkills","PlayerSpells",
    "PlayerWearing","Player"]
  players_to_delete.each do |player_to_delete|
    debug_msg "Deleting player #{player_to_delete}"
    player_tables.each do |table|
      result = client.execute("DELETE
      FROM [#{@server_database}].[dbo].[#{table}]
      WHERE playerID = '#{player_to_delete}'")
      debug_msg "Rows deleted from [#{table}]: #{result.do}"
    end
  end
    result = client.execute("DELETE
    FROM [#{@server_database}].[dbo].[Log]")
  debug_msg "Rows deleted from [Log]: #{result.do}"
end

There’s a SELECT in here to find the playerIDs of the test accounts, so we can delete the appropriate entries in the related tables. Let’s move that into db/player.rb:

features/lib/db/player.rb GitLab 
61
62
63
64
65
66
67
68
69
70
71
72

def get_all_test_player_ids()
  id_list = []
  client = connect_to_db(@server_database)
  query = "SELECT [playerID] FROM [#{@server_database}].[dbo].[Player] \
           WHERE account LIKE 'test%' \
           AND name LIKE 'Dude.%'"
  result = client.execute(query)
  result.each(:symbolize_keys => true) do |rowset|
    id_list << rowset[:playerID]
  end
  id_list
end

But before we simply replace the code in hooks.rb, we’ll do what I like to call “The Safety Dance”, where we query both ways and assert that the results are the same:

features/lib/hooks.rb GitLab 
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

After('@db_cleanup') do |scenario|
  client = connect_to_db(@server_database)
  result = client.execute("DELETE
    FROM [#{@server_database}].[dbo].[Account]
    WHERE account LIKE 'test%'
    AND email LIKE '%301days.com'")
  debug_msg "Rows deleted from [Account]: #{result.do}"
  result = client.execute("SELECT playerID, account, name
    FROM [#{@server_database}].[dbo].[Player]
    WHERE account LIKE 'test%'
    AND name LIKE 'Dude.%'")
  players_to_delete = []
  result.each(:symbolize_keys => true) do |rowset|
    players_to_delete << rowset[:playerID]
  end
  debug_msg "Players to delete: #{players_to_delete}"
  debug_msg "All test player IDs: #{get_all_test_player_ids()}"
  fail "Player ID issue" if players_to_delete.sort != get_all_test_player_ids().sort

Then we run some tests that use this hook, watching out for failures due to the two methods not getting the same set of user IDs. Luckily, we don’t.

Why do this, though? Normally we use tests as a safety net when we refactor application code, but what about when we refactor the test code? Instead of writing a test suite for the test suite (and then a test suite for that test suite, etc), we take care to temporarily insert these little bespoke test clauses to make sure we’re still doing what we want.

Once we’re satisfied, we complete this part of the refactor:

features/lib/hooks.rb GitLab 
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

After('@db_cleanup') do |scenario|
  client = connect_to_db(@server_database)
  result = client.execute("DELETE
    FROM [#{@server_database}].[dbo].[Account]
    WHERE account LIKE 'test%'
    AND email LIKE '%301days.com'")
  debug_msg "Rows deleted from [Account]: #{result.do}"
  player_tables = ["PlayerBelt","PlayerEffects","PlayerFlags","PlayerHeld","PlayerLocker",
    "PlayerQuests","PlayerRings","PlayerSack","PlayerSettings","PlayerSkills","PlayerSpells",
    "PlayerWearing","Player"]
  get_all_test_player_ids().each do |player_to_delete|
    debug_msg "Deleting player #{player_to_delete}"
    player_tables.each do |table|
      result = client.execute("DELETE
      FROM [#{@server_database}].[dbo].[#{table}]
      WHERE playerID = '#{player_to_delete}'")
      debug_msg "Rows deleted from [#{table}]: #{result.do}"
    end
  end
    result = client.execute("DELETE
    FROM [#{@server_database}].[dbo].[Log]")
  debug_msg "Rows deleted from [Log]: #{result.do}"
end

It didn’t take that much to move any SELECTs out of non-db-specific code. We’ll think about tackling the DELETEs and UPDATEs too. Tomorrow.

Useful Stuff

More to come

Day 74 code - tests