Validating a Collection of Data
Considering that we parsed out 164 monsters with dozens of properties each from the FAQ, we don't want to manually check all of that data to make sure every property that should be a number is a number and all property names are correctly spelled. That exercise would be way too tedious and error prone. This problem of validating the data sounds like it needs an extension to our script. Since we have the data in a list of hash tables, it should be fairly straightforward to create another hash table that can be used to validate each table in the list. The idea with this hash table is to have a set of valid properties as the keys in the table, and the values are regexes that should match each property value that they represent. These regexes will be more specific to each property, since those properties have already matched on the more general regexes that were used to collect the data in the first place. Additionally, every key in each monster hash should be in this template hash, and every template hash key should be in each monster hash. We could get even more detailed with our checks, but this validation should be enough to give us confidence in the data.To get started, we'll build up the first couple entries in the template hash and write the validation loop. Once it's working, we can fill out the rest of the entries more easily. Here are the name and minimum base HP entries along with the validation loop:
PROPER_NAME_REGEX = /^\w[\w\s]*\w$/
NUMBER_REGEX = /^\d+(:?,\d{3})?$/
VALID_MONSTER = {
"Name" => PROPER_NAME_REGEX,
"Minimum Base HP" => NUMBER_REGEX
}
data.each do |monster|
VALID_MONSTER.each do |key, regex|
if monster.key?(key)
unless monster[key] =~ regex
puts "Monster #{monster["Name"]} has invalid property #{key}: #{monster[key]}."
end
else
puts "Monster #{monster["Name"]} has missing property #{key}."
end
end
monster.each do |key, value|
unless VALID_MONSTER.key?(key)
puts "Monster #{monster["Name"]} has extra property #{key}: #{value}."
end
end
endThe number regex is similar to the proper name regex, except that it matches on numbers instead of words. The (:?,\d{3}) group matches on a comma followed by three digits because the {3} pattern means that the previous character type, in this case a digit, must be repeated three times. This group is optional, so the regex will match on 1234 as well as 1,234. The number regex is also wrapped in a '^' and a '$' so that the entire string must match the pattern.
The next constant is simply the start of our monster template hash with "Name" and "Minimum Base HP" entries. What follows is the validation loop, and it is laid out about how it was described. First, we iterate through each monster in the data list that we have already populated with the monsters from the FAQ. Within each monster we iterate through every entry of the valid monster template. If the monster has the property we're looking at, we check if the property value matches the regex for that property. If it doesn't, we print out an error. If the property doesn't exist, we print out a different error. Then we iterate through every property of the monster, and if a property doesn't exist in the template, we print out another error.
If we run this script now, we end up with a ton of errors for extra properties because we haven't added those properties to the template, yet. However, from looking at the first few monster's outputs, it appears that the other checks are working, so we can start filling out the rest of our template. We can quickly add in the obvious properties, checking the script periodically to make sure we haven't gone astray. The mostly finished template looks like this:
PROPER_NAME_REGEX = /^\w.*[\w)!%]$/
NUMBER_REGEX = /^\d+(:?,\d{3})?$/
SMALL_NUMBER_REGEX = /^(\d\d?\d?|N\/A)$/
PERCENTAGE_REGEX = /^(\d\d?\d?%|N\/A)$/
LIST_REGEX = /^((:?All )?\w+(:?, (:?All )?\w+)*|N\/A)$/
FREE_TEXT_REGEX = /^\S+(?:\s\S+)*$/
TIME_REGEX = /^\d\d?:\d\d$/
VALID_MONSTER = {
"Name" => PROPER_NAME_REGEX,
"Role" => PROPER_NAME_REGEX,
"Location" => PROPER_NAME_REGEX,
"Location2" => PROPER_NAME_REGEX,
"Location3" => PROPER_NAME_REGEX,
"Max Level" => SMALL_NUMBER_REGEX,
"Speed" => SMALL_NUMBER_REGEX,
"Tame Rate" => PERCENTAGE_REGEX,
"Minimum Base HP" => NUMBER_REGEX,
"Maximum Base HP" => NUMBER_REGEX,
"Minimum Base Strength" => SMALL_NUMBER_REGEX,
"Maximum Base Strength" => SMALL_NUMBER_REGEX,
"Minimum Base Magic" => SMALL_NUMBER_REGEX,
"Maximum Base Magic" => SMALL_NUMBER_REGEX,
"Growth" => PROPER_NAME_REGEX,
"Immune" => LIST_REGEX,
"Resistant" => LIST_REGEX,
"Halved" => LIST_REGEX,
"Weak" => LIST_REGEX,
"Constellation" => PROPER_NAME_REGEX,
"Feral Link" => PROPER_NAME_REGEX,
"Description" => FREE_TEXT_REGEX,
"Type" => PROPER_NAME_REGEX,
"Effect" => FREE_TEXT_REGEX,
"Damage Modifier" => FREE_TEXT_REGEX,
"Charge Time" => TIME_REGEX,
"PS3 Combo" => FREE_TEXT_REGEX,
"Xbox 360 Combo" => FREE_TEXT_REGEX,
"Default Passive" => PROPER_NAME_REGEX,
"Default Skill" => PROPER_NAME_REGEX,
"Special Notes" => FREE_TEXT_REGEX,
}The location property has an odd feature that makes it a bit difficult to handle. Some monsters appear in up to three different areas in the game, but it's only a handful of monsters that do this. Having multiple locations combined in the same property is less than ideal because we'll likely want to look up monsters by location in the database, and we'll want to index that field so each location value should be a unique name, not a list. Additionally, the FAQ puts each location on a separate line, but not prefixed with the "Location-----:" property name. This format causes problems for our script. To solve both problems at once, we can add "Location2" and "Location3" properties anywhere that a monster has a second or third location by directly editing the FAQ.
This template covers nearly all of the monster properties, except for the level skill and passive properties. We'll get to those properties in a second, but first we have another problem to fix. It turns out that the two location properties we added and the last three properties in the template don't always occur, so we have to modify our check on those properties slightly:
# ...
elsif !["Location2", "Location3", "Default Passive", "Default Skill", "Special Notes"].include? key
puts "Monster #{monster["Name"]} has missing property #{key}."
end
# ...Now we're ready to tackle the level properties. What we don't want to do here is list every single level from 1 to 99 for both skill and passive properties. There has to be a better way! The easiest thing to do is add a check for if the key matches the pattern of "Lv. XX (Skill|Passive)" in the loop that checks if each monster property exists in the template, and accept it if the key matches and the value matches the PROPER_NAME_REGEX. This fix is shown in the following code:
LEVEL_PROP_REGEX = /^Lv\. \d\d (Skill|Passive)$/
# ...
monster.each do |key, value|
unless VALID_MONSTER.key?(key)
if key =~ LEVEL_PROP_REGEX
unless value =~ PROPER_NAME_REGEX
puts "Monster #{monster["Name"]} has invalid level property #{key}: #{value}."
end
else
puts "Monster #{monster["Name"]} has extra property #{key}: #{value}."
end
end
end
# ...With this addition to the data validation checks, we've significantly reduced the list of errors from the script output, and we can actually see some real typos that were in the FAQ. The most common typo was using "Lvl." instead of "Lv." and there are other assorted typos to deal with. We don't want to change the regexes to accept these typos because then they'll appear in the database, and we don't want to add code to the script to fix various random typos because that's just tedious nonsense. It's best to fix the typos in the FAQ and rerun the script. It's not too bad a task for these few mistakes.
Exporting Monsters to a CSV File
Now that we have this nice data set of all of the monster properties we could ever want, we need to write it out to a .csv file so that we can then import it into the database. This is going to be some super complicated code. Are you ready? Here it goes:require 'csv'
opts = {headers: data.reduce(&:merge).keys, write_headers: true}
CSV.open("monsters.csv", "wb", opts) do |csv|
data.each { |hash| csv << hash }
endWe need to take care to enumerate all of the header names that we want in the .csv file, and that happens in the options that are passed to CSV.open. Specifically, headers: data.reduce(&:merge).keys tells the CSV::Writer what the list of header names is, and the writer is smart enough to put blank entries in wherever a particular header name is missing in the hash that it is currently writing out to the file. The way that code works to generate a list of header names is pretty slick, too. We simply tell the data array to use the Hash#merge function to combine all of the hashes into one hash that contains all of the keys. Since we don't care about the values that got merged in the process, we simply grab the keys from this merged hash, and voila, we have our headers.
The .csv file that's generated from this script is a real beast, with 204 unique columns for our 164 monsters. Most of those columns are the sparsely populated level-specific skills and passive abilities. We'll have to find ways to deal with this sparsely populated matrix when using the database, but it should be much better than dealing with one or two fields of long lists of abilities. At least, that's what I've read in books on database design. I'm learning here, so we'll see how this goes in practice.
Importing Monsters Into a Database
This part isn't going to be quite as easy as exporting because we'll need to write a database schema, but it shouldn't be too bad. Before we get to that, we need to create a new Ruby on Rails project. I'll assume Ruby 2.5.0 or higher and Rails 6.0 are installed. If not, see the start of this Rails Getting Started guide to get that set up. We start a new Rails project by going to the directory where we want to create it and using this Rails command:$ rails new ffxiii2_monster_taming$ cd ffxiii2_monster_taming
$ rails generate model Monster name:stringclass CreateMonsters < ActiveRecord::Migration[6.0]
def change
create_table :monsters do |t|
t.string :name
t.timestamps
end
end
endFirst, we'll want to make a couple changes in place so that the .csv header has the same names as the database attributes. This will make life easier when we import. All spaces should be replaced with underscores, and the periods in the "Lv." names should be removed. Finally, the whole line should be converted to lowercase to adhere to Rails conventions for attribute names. Once that's done, we can copy the header line to a new file, replace every comma with a newline character, and replace each beginning of a line with " t.string " to add in the attribute types. They are almost all going to be strings, and it's simple to go back and change the few that are not to integers, floats, and times. I did this all in Vim, but any decent text editor should be up to the task. Now we have a complete migration file:
class CreateMonsters < ActiveRecord::Migration[6.0]
def change
create_table :monsters do |t|
t.string :name
t.string :role
t.string :location
t.string :location2
t.string :location3
t.integer :max_level
t.integer :speed
t.string :tame_rate
t.string :growth
t.string :immune
t.string :resistant
t.string :halved
t.string :weak
t.string :constellation
t.integer :minimum_base_hp
t.integer :maximum_base_hp
t.integer :minimum_base_strength
t.integer :maximum_base_strength
t.integer :minimum_base_magic
t.integer :maximum_base_magic
t.string :feral_link
t.string :description
t.string :monster_type
t.string :effect
t.float :damage_modifier
t.time :charge_time
t.string :ps3_combo
t.string :xbox_360_combo
t.string :default_passive
t.string :default_skill
t.string :special_notes
t.string :lv_02_passive
t.string :lv_02_skill
#...
# over a hundred more lv_xx attributes
#...
t.string :lv_99_passive
t.string :lv_99_skill
t.timestamps
end
end
end$ rails db:migraterequire 'csv'
namespace :csv do
desc "Import Monster CSV Data"
task :import_monsters => :environment do
csv_file_path = 'db/monsters.csv'
CSV.foreach(csv_file_path, {headers: true}) do |row|
Model.create!(row.to_hash)
puts "#{row['name']} added!"
end
end
end$ rails db:seedWe've accomplished a lot in this post with running some validation checks on the monster data, exporting it to a .csv file, creating a database table, and importing the monsters.csv file into that table. We still have plenty to do, creating and importing the other tables and relating the data between tables. That will be the goal for next time.
