Hacking EVE, Part 1 – Reading SDD Data

While the process of writing software remains a challenging, sometimes frustrating endeavor, I was reminded recently how simple some tasks have become. The data available on the internet (along with the tools we have to manipulate that data) make answering some difficult questions a rather enjoyable undertaking.

Today’s post is the first part in a series on using these tools to solve a rather frivolous problem — getting a leg up on my competition in EVE. I’ll be using MySQL, ruby, RSpec, and ActiveRecord to read in the static data needed to answer my questions.

EVE’s Data

For the majority of you who have never heard of EVE, it’s an online game with a player-generated economy. That means when you buy a ship or some other item in the game, another player has mined the materials needed, researched the blueprints, and coordinated the manufacturing jobs to build that item and put it on the market. Some items are overbuilt, and manufacturing these items is a money-losing proposition. Others are in low supply and can turn a pretty profit.

There are thousands of items that you can build, and each has a long list of material requirements with prices that change daily. As an industrialist, it can be fairly challenging to know which items you should be building for maximum return.

Fortunately, we have some resources at our disposal to help answer this question. CCP (the game’s manufacturer) provides an SDD (static data dump) including tables of all the static data used in the game. They also provide an API players can use to access a wealth of information about their characters and resources. Finally, we have third party sites that provide APIs with current market data.

Getting Access to the SDD

CCP provides the SDD at their community development site. If you enjoy working on Windows, then you’ll be happy to use the MSSQL format provided. I prefer working on my Mac, and so I had a couple options. I could load up the MSSQL data, run through a bunch of steps , and end up with something MySQL will import.

Or I could take advantage of the fact that others have done this work already and generously provided the files for download. To go this route, you’ll want to grab the mysql56-odyssey-1.1-91288.tar.bz2 file, unzip it:

bunzip2 mysql56-odyssey-1.1-91288.tar.bz2

Then untar it:

tar -xf mysql56-odyssey-1.1-91288.tar

At this point, you should have a mysql56-odyssey-1.1-91288.sql file available under the odyssey-1.1-91288 directory. Now you’ll need to import it into a MySQL instance.

Importing to MySQL

First, you’ll need to have MySQL installed.  It’s a fairly simple process of downloading the installer and running a couple dmg files.

Then you’ll want to start up the MySQL instance with something like:

sudo /usr/local/mysql/bin/mysqld_safe

Next run the MySQL console:

mysql -u root

And create a database for your SDD tables:

create database odyssey-1.1-91288;

Then exit the mysql console, and import our data to the new database:

mysql -u root -p -h localhost odyssey-1.1-91288 < ~/Downloads/odyssey-1.1-91288/mysql56-odyssey-1.1-91288.sql

Note that you’ll be prompted for a password. Just hit enter, as we haven’t set one for this database.

Once your import completes, you can fire up the mysql console again and poke around to make sure the data looks reasonable:

unknowne0f84730af22:ProductionTools jesse_hill$ mysql -u root 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 360 
Server version: 5.6.13 MySQL Community Server (GPL) 

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. 

Type '\c' to clear the current input statement. 

mysql> use odyssey-1.1-91288;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from invTypes where typeID=34;
+--------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+--------+----------+-------------+--------+-----------+-----------+---------------+---------------------+
| typeID | groupID | typeName | description | mass | volume | capacity | portionSize | raceID | basePrice | published | marketGroupID | chanceOfDuplicating |
+--------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+--------+----------+-------------+--------+-----------+-----------+---------------+---------------------+
| 34 | 18 | Tritanium | The main building block in space structures. A very hard, yet bendable metal. Cannot be used in human habitats due to its instability at atmospheric temperatures. Very common throughout the universe. | 0 | 0.01 | 0 | 1 | NULL | 2.0000 | 1 | 18 | 0 |
+--------+---------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+--------+----------+-------------+--------+-----------+-----------+---------------+---------------------+
1 row in set (0.01 sec)

mysql>

Ok, great, we have a MySQL instance with the SDD data. Now how do we read this in a way that will make it easy to work with the data?

RSpec and ActiveRecord

I prefer to use Ruby for my scripting, and ActiveRecord is the go to ORM for Ruby. Let’s install the gems we’ll need. Install bundler if you haven’t (gem install bundler) and then create a new directory for your scripts and a Gemfile with the contents:

source 'https://rubygems.org'

gem 'activerecord'
gem 'activerecord-mysql-adapter'
gem 'mysql2'
gem 'rspec'

Run bundle install. This will grab the ActiveRecord gems and the mysql adapters, along with RSpec for testing. Next create a spec directory for tests and add a test for some of the data we’d like to grab from the database. Add a invCategory_spec.rb file under ./spec/models/database/ with the contents:

require 'spec_helper'
require './models/database/invCategory.rb'

describe InvCategory do

   it 'should answer the correct name for a category id' do
      # category ID 16 == Skill
      category = InvCategory.find_by(categoryID: 16)
      category.categoryName.should eq('Skill')
   end

   it 'should answer whether or not the category is a skill' do
      # category ID 16 == Skill
      category = InvCategory.find_by(categoryID: 16)
      category.is_skill?.should be_true

      # category ID 9 == Blueprint
      category = InvCategory.find_by(categoryID: 9)
      category.is_skill?.should be_false
   end

end

You’ll need to run some code before your tests to set up the ActiveRecord connection. Put the following in a spec/spec_helper.rb file:

require 'active_record'
require 'yaml'

RSpec.configure do |config|
   config.order = 'random'

   config.before(:suite) {
      dbconfig = YAML::load(File.open('./config/database.yml'))
      ActiveRecord::Base.establish_connection(dbconfig)
   }
end

Finally, you’ll need to add a config/database.yml file to hold your database settings:

adapter: mysql2
database: odyssey-1.1-91288
host: localhost
username: root

To run your tests, just invoke rspec from the script root, and you should see something like the following:

unknowne0f84730af22:ProductionTools jesse_hill$ rspec
/Users/jesse_hill/Projects/EVE/ProductionTools/spec/models/database/invCategory_spec.rb:2:in `require': cannot load such file -- ./models/database/invCategory.rb (LoadError)
from /Users/jesse_hill/Projects/EVE/Blog1/spec/models/database/invCategory_spec.rb:2:in `'
from /Users/jesse_hill/.rvm/gems/ruby-1.9.3-p194/gems/rspec-core-2.14.5/lib/rspec/core/configuration.rb:896:in `load'
from /Users/jesse_hill/.rvm/gems/ruby-1.9.3-p194/gems/rspec-core-2.14.5/lib/rspec/core/configuration.rb:896:in `block in load_spec_files'
from /Users/jesse_hill/.rvm/gems/ruby-1.9.3-p194/gems/rspec-core-2.14.5/lib/rspec/core/configuration.rb:896:in `each'
from /Users/jesse_hill/.rvm/gems/ruby-1.9.3-p194/gems/rspec-core-2.14.5/lib/rspec/core/configuration.rb:896:in `load_spec_files'
from /Users/jesse_hill/.rvm/gems/ruby-1.9.3-p194/gems/rspec-core-2.14.5/lib/rspec/core/command_line.rb:22:in `run'
from /Users/jesse_hill/.rvm/gems/ruby-1.9.3-p194/gems/rspec-core-2.14.5/lib/rspec/core/runner.rb:80:in `run'
from /Users/jesse_hill/.rvm/gems/ruby-1.9.3-p194/gems/rspec-core-2.14.5/lib/rspec/core/runner.rb:17:in `block in autorun'

The test is telling us we need to define an InvCategory class. Create an invCategory.rb file under ./models/database with the contents:

require 'active_record'

class InvCategory < ActiveRecord::Base
   self.table_name = "invCategories"
   self.primary_key = "categoryID"

   def is_skill?
      categoryName == 'Skill'
   end
end

And when we run rspec again, we should see our tests pass.

Conclusion

So what have we accomplished? We’ve grabbed SDD files and imported them into a new MySQL database. Then we wrote some simple RSpec tests to validate that we’re grabbing data correctly out of an SDD table, and we wrote the model to actually read from the table. Next time around, we’ll work on using web APIs to figure out how much a particular component is worth.

Hacking EVE

• Part 1 – Reading SDD Data
• Part 2 – Reading Market Data
• Part 3 – Let’s Build Something!
• Part 4 – Prettying Things Up with Mustache