Export CCP's SDE Tables to PostgreSQL & Rails ActiveRecord Models

When working with Rails and EVE-Online, one often finds themselves in need of access to CCP's SDE.

The data within CCP's dump changes periodically to reflect patches and other changes to the game. Because of this, an adaptable, easy to duplicate process is best when sourcing your data. This post will cover one strategy for making CCP's SDE data accessible within your rails application on a table by table basis.

This guide assumes you have a functioning postgres install and are familiar with how to get a psql prompt.

For the purposes of this demonstration, we'll be trying to provide easy typeName to typeID lookups within the rails application.

Step One: Acquire a Postgres formatted SDE

You can download a copy of Steve Ronuken's Postgres formatted SDE dump here: https://forums.eveonline.com/default.aspx?g=posts&t=414591&find=unread

Next you'll want to load that into your postgres server. To do so you'll need to create a database by the name of 'eve' and a role by the name of 'eve'. This can be done in psql with the following commands:

 CREATE ROLE eve WITH LOGIN CREATEDB;  

 CREATE DATABASE eve OWNER eve;  

A successful role creation will print "CREATE ROLE" to the screen, and a successful database creation will print "CREATE DATABASE" to the screen.

With role and database created and configured, you now need to populate the database. This can be done with pg_restore (thanks Steve!).

pg_restore <postgres_datadump> -d eve

EG:

pg_restore postgres-scylla-1.111482.sql -d eve  

A successful restore is silent. So if you get any errors, they'll be printed to the screen and you'll see a lovely avalanche of broken stuff.


Step Two: Identify the data you're looking for

In this case we're looking for typeID & typeName columns. These can be found in the invTypes table. It is important to note that the SDE does not use snake cased table names as Rails users may be accustomed to. For this reason you'll need to wrap any table names in your select queries in quotes. Thus instead of writing

 SELECT * FROM invTypes;  

You'll need to write

 SELECT * FROM "invTypes";  

note the quotes.


Step Three: Determine the table's schema

Once you've identified the location of the data you're looking for, you'll need to figure out what format that data is being stored in. This can be done using the psql \d+ command.

 \d+ <tablename>;  

EG:

 \d+ "invTypes";  

This command will list all columns, types, modifiers, indexes, etc on the table in question. It's output will look like this:

                  Table "public.invTypes"  
     Column    |    Type    | Modifiers | Storage | Stats target | Description   
 ---------------------+------------------+-----------+----------+--------------+-------------  
  typeID       | bigint      | not null | plain  |       |   
  groupID       | bigint      |      | plain  |       |   
  typeName      | text       |      | extended |       |   
  description     | text       |      | extended |       |   
  mass        | double precision |      | plain  |       |   
  volume       | double precision |      | plain  |       |   
  capacity      | double precision |      | plain  |       |   
  portionSize     | bigint      |      | plain  |       |   
  raceID       | smallint     |      | plain  |       |   
  basePrice      | numeric(19,4)  |      | main   |       |   
  published      | boolean     |      | plain  |       |   
  marketGroupID    | bigint      |      | plain  |       |   
  chanceOfDuplicating | double precision |      | plain  |       |   
 Indexes:  
   "invTypes_pkey" PRIMARY KEY, btree ("typeID")  
   "idx_149637_invTypes_IX_Group" btree ("groupID")  
 eve=#   

Step Four: Define the Rails Model

You can name the model whatever you see fit, but I personally prefer to keep the name as close to CCP's table names as possible.

 rails generate model invType typeID:bigint groupID:bigint typeName:text description:text mass:decimal volume:decimal capacity:decimal portionSize:bigint basePrice:decimal published:boolean marketGroupID:bigint chanceofDuplicating:bigint  

You'll then want to set id: false, change typeID to primary_key, and set the precision of each decimal. This will leave your migration looking something like this:

 class CreateInvTypes < ActiveRecord::Migration  
  def change  
   create_table :invTypes, id: false do |t|  
    t.primary_key :typeID  
    t.bigint :groupID  
    t.text :typeName  
    t.text :description  
    t.decimal :mass, :precision => 64, :scale => 12
    t.decimal :volume, :precision => 64, :scale => 12
    t.decimal :capacity, :precision => 64, :scale => 12  
    t.bigint :portionSize  
    t.decimal :basePrice, :precision => 19, :scale => 4  
    t.boolean :published  
    t.bigint :marketGroupID  
    t.bigint :chanceofDuplicating  
    t.timestamps null: false  
   end  
  end  
 end  

Getting the migration right can be a pain in the butt, so don't be surprised if this stage takes a while.


Step Six: Link your newly generated table & model.

ActiveRecord doesn't play nice with non-snake_cased table names, and the migration above creates a camel cased table name. To fix this issue, you'll need to set a custom table mapping on your model. This is done with the 'table_name' method in rails four and will leave your model looking something like this:

 class InvType < ActiveRecord::Base  
  self.table_name = "invTypes"  
 end  

At this point you should be able to open up your rails console and perform all the traditional CRUD actions on your new table.


Step Five: Copy the Table

With a rails model association setup, it is now merely a matter of populating the table. To do this, we will simply copy the table straight out of the SDE and right into our database. After which the data should be directly accessible within our rails app!

First drop the table from your rails database. This can be done by connecting to your database in psql and using the drop table command.

EG:

 DROP TABLE "invTypes";  

This will remove the table from the database without disturbing the rails application's schema expectations.

Lastly; copy the table from the SDE into your application's database using pg_dump and piping it into psql with the below command:

pg_dump <source_database> -t <sourceTable> | psql <target_database>

EG:

pg_dump eve -t "\"invTypes\"" | psql eveVideoManagementSystem_development  

Note the escaped quotes around the table name. This is important because pg_dump doesn't automagically quote your table names. A successful run will print something like this:

 SET  
 SET  
 SET  
 SET  
 SET  
 SET  
 SET  
 SET  
 SET  
 CREATE TABLE  
 ALTER TABLE  
 COPY 22531  
 ALTER TABLE  
 CREATE INDEX  

Step Six: Change the table's owner

Copying using pg_dump will set the table's owner to 'eve'. Depending on your rails and database configurations, this will likely result in your rails app being unable to access the table. To fix this, change the tables owner, which can be done, once connected to the database, with the below command.

ALTER TABLE <tableName> OWNER TO <new owner's role>
EG:

 ALTER TABLE "invTypes" OWNER TO evevideomanagementsystem  

At this point you should be able to access the table using ActiveRecord in the traditional manner.

Going forward, as new SDEs are released, it should be possible to simply repeat step 5 & 6 to update your application's data to the newest release.