Advertisements

Challenges of Archiving Industry Index Values

If you have any interest in 3rd party development or databases, this post will be a entertaining as I share my current lackluster architecture for saving some of the new Eve API data.

I have a Raspberry Pi running MySQL that I use as a basic storage location for various databases. One of them is a new database that contains index values from the CREST API endpoint with a timestamp so I can start to develop a archive of the values.

The current solution I have for importing the new Index data isn’t very elegant. I’m using Lockefox’s Crius Toolset sheet to grab a CSV every day, adding two columns for a primary key and a timestamp, and importing into my table to get a table that looks like this:

“transactionID” “transactionDateTime” “solarSystemName” “solarSystemID” “manufacturing”
“103724” “2014-08-19 13:28:00” “Jouvulen” “30011392” “0.0339317910000”
“103725” “2014-08-19 13:28:00” “Urhinichi” “30040141” “0.0236668590000”
“103726” “2014-08-19 13:28:00” “Akiainavas” “30011407” “0.0285709850000”
“103727” “2014-08-19 13:28:00” “Seitam” “30041672” “0.0162879230000”
“103728” “2014-08-19 13:28:00” “BK4-YC” “30003757” “0.0143238350000”

It’s growing and starting to show how under-powered the Raspberry Pi is for data processing. Most of my issue stems from a lack of salable design on my part. I have no table indexes and am joining with the bulky mapDenormalize table.

I have a love-hate relationship with the mapDenormalize table. If you have ever worked with this table, you know that it is a beast: 502,558 rows, 15 columns with five of them being DOUBLE values coming in at 214MB. Normally not a problem for server with a lot of CPU cycles and RAM, but the 700MHz ARM processor on the Raspberry Pi has a hard time with multiple JOIN operations and GROUP BYs.

Here’s a query I was running against my dataset that ran for 15.5 minutes (!).

SELECT systemCosts.solarSystemName, systemCosts.transactionDateTime ,systemCosts.manufacturing, mapRegions.regionName, mapDenormalize.security
FROM systemCosts
JOIN mapDenormalize
ON (systemCosts.solarSystemID = mapDenormalize.solarSystemID)
JOIN mapRegions
ON (mapDenormalize.regionID = mapRegions.regionID)
GROUP BY transactionDateTime,solarSystemName

So two full table JOIN and two GROUP operations and a table with no indexes, uf. I sent a screenshot off to James, my development partner.

2014-08-04_terrible_query

My first solution was to remove extraneous data from the mapDenomalize table. After removing the groupID, constellationID, orbitID, x, y, z, radius, celestialIndex, and orbitIndex I trimmed the table down even further by deleting all entries that were not star systems. What was left was 7,929 rows coming in at around 1MB.

I’m glad to report that my terrible query is now running in 20 seconds. This was a small step to getting my growing index dataset to a usable state while I write something more permanent.

Advertisements

Data Warehousing using EVE Market Data Relay (EMDR)

Overview

Knowing the price and volume of items moving in the Eve market at a specific point in time is a very powerful piece of information that can help further your space empire.

Historically eve-central was the repository of market data but with new advances in cache scraping, data transport methods, and large archival storage methods have brought us exciting new capabilities.

tl;dr EMDR

What the heck is EMDR and why should I care?

Original implementations of market aggregation sites had a user click the Market Export button in the Eve client to produce text files in their local Documents\EVE\logs\Marketlogs folder and then use an application to transfer the exported files to a database. This method was tedious, lacked region and item coverage, and was prone to people editing the data before it was sent off.

A renascence was generated when developers began to explore the cache in the Eve client. The client cache is a location that serves as temporary storage for information that you are working with in the client; it is volatile and changes all the time rather than holding static art, formulas, or universe data as seen in the client .stuff files.

2013-02-20_stuff_files

EMDR is a service that takes market orders from the cache of your local client and sends that immediately upon viewing it in the client to a relay service that people can subscribe to. If you click on an item in the market window, that piece of data is immediately sent off to many people that can receive it. The transfer is quick, the data is not tampered with, and it can easily be relayed to many interested parties. This is pure data, a statisticians dream!

Working with Eve-Central

What follows are notes from my partner, Raath, who has been withing with the EMDR method to improve our industrial operation.

When I first began adding price dependencies to the DRK Industry Tracker, it was back in the day before we had the EMDR. Eve-central was the bespoke out of game price lookup service and with the aid of their API I pulled prices from there.

At the time I don’t think it had the option to do a full pull of prices on every item in game so I devised a system where the tracker would cache prices locally and update them when they were older than an hour on a need to know mechanic so only the relevant data was requested.

I did this so that I didn’t swamp eve-central with hundreds of requests every hour for information that 95% of which would never be used. It was a system that worked well except for the occasional lag caused by the prices updating. As I said before, it only updated when prices were older than an hour as the users requested them. But when I started to think of releasing the tracker publicly, I needed something a little more reliable.

Transition to EMDR

At this point in time the EMDR was a fully developed solution so I started to look into how I could begin using this as my price basis and integrating data into our industrial workflow. Not really knowing the volume of information that would soon be assaulting our little virtual machine, I make a few mistakes.

Flood of Data

The term for the EMDR feed that people have adopted is the “fire hose” as it is literally a flood of overwhelming data being constantly sent at you with no regard for your ability to process it. As clients all across the world are clicking around the market window, updates are being sent to you in near real-time. The function name of “engage_fire_hose” only seemed natural.

2013-02-20_engage_fire_hose

Statistics

Our Industry site currently consumes 8 gig of data, serves 6-700 MB and calls around 150,000 API requests per day with the vast majority of our incoming data coming from the EMDR updates. In January we received 179 GB and sent 17 GB for an average of 5.8 GB/day of incoming data.

2013-02-20_january_bandwidth_utilization

Processing Challenges

With the large amount of incoming price data, we needed to be able to efficiently process and store it while keeping the server responsive.

The first method I devised was a system where I stored transactionIDs in a hash table. This design soon showed its weakness as we hit a memory limit when the hash table started to fill up with millions of transactionIDs.

Additionally my attempts to keep load on MySQL server down were also in vain as the information was coming in so quickly that my consumer was having trouble keeping up. We soon had problems with locked rows and inserts failing so I had to completely revise my entire approach.

What resulted was a consumer process that guzzles around 8 GB of data per day and spits all the information to CSV files. There is no logical processing done in the consumer now, it just munches and spits data to file. Another cron job that runs every minute scans the temp file directory for CSVs, parses them all into one single large CSV, and then performs a LOAD DATA IN FILE into MySQL, a method which I’ve found is not only lightening fast but also keeps the load down.

Not all of the data is useful as the majority of it is duplicates. When the server has free time, we run a cleanup process in the form of flag checking remove expired records so that the data we display is as up to date.

2013-02-20_market_database

Future Plans

All this work for clean price data. We have plans to further expand some of the market data functionality in projects to start using the cached market history to show the historical price or items and build costs compared to live data.


Trading 202: Challenges of Asset Tracking

Overview

You buy something and then sell it somewhere else at a higher price. This process is easy enough to do, but for accurate profit records it turns out to be rather troublesome due to how asset identification is implemented in Eve at the database level.

If you want to preserve a chain of custody for bookkeeping reasons, you need some additional tools for asset tracking.

Stacks and itemIDs

A single item or a stack of items is stored in the database as a single row with a unique itemID. Below is an exmaple of a Medium Shield Drone that I have in a Dodixie station.

2012-12-16_asset_example-1

Every item in the game as a unique itemID that is created and destroyed when it is stacked or split.  Here is an example of what happens when you take a stack of ore, split it, and then combine it again.

assets_combined

This is a very simple example, but imagine a larger trading operation. You are buying 200 implants and 3 are for your personal characters, 100 are going to your primary trade hub, and the other 97 to your secondary trade hub. As you split up the stacks, your itemIDs change. The chain of ownership gets lost along the way.

While your current orders are selling, you are going to buy more of the same implants so you can replenish stock in the trade hub. This new purchase is at a slightly higher cost, but well within good profit margins.

So how do you calculate profit? You have items at a certain price currently selling and then a new price to work with for the next batch. Do you use the last price that you bought it at? A global price from a source like Eve-Central? An average?

The ideal solution involves working with a record of quantity and price at a point in time. Once you have this information, you can keep better track of profits as you continuously sell and buy items.

Solutions

1. First In, First Out

This system can be explained by James, my coding right hand man. Here is a quote when we introduced a ‘inventory’ table to our Wallet Manager.

“As far as getting profit and costing, that’s pretty simple. We get costs in a FIFO basis (first in, first out). As purchases come in through the API, they go to the ‘inventory’ table. As things are sold, the quantities are deducted from the items in that table and the profit calculated on the original purchase cost. This allows for a pretty accurate profit calculation.

The caveat is that method is pretty prone to drift. You might buy 1,000 heavy missiles you don’t plan on selling, but they’ll end up in the table. However, we have a way of combating that.

One is that the tool works best when the characters being tracked do nothing but trade or produce. Because the Eve API doesn’t allow you to track specific instances of items well enough, that’s really the first line of defense. You can always spin an alt to buy your toys and keep them off the tool’s books.”

2. Assign Assets to a Project

Raath, my production partner that heads up development at industry.darkshadowindustries.com, uses Projects.

A asset or resource that enters the system can be assigned to a project. That item and the price can then be tracked and rolled into the final profit calculation of the job when it is completed.


Wallet Manager Project Code

If you haven’t been following this blog, I’ve periodically posted about a “Wallet Manager” project that myself and my trading partners, Raath and James, have been working on for the past two years to help us manage our Eve activities using the Eve API.

The project started small, with the first page being driven only by the wallet API feed. As we expanded our industrial scope in the game so too did the Wallet Manager’s requirements. Soon I needed to keep track of blueprints, assets, capital production, PI, and tech 2 invention; each new requirement expanded the original scope.

What grew was a project that contained many methods by coders of different proficiencies. James took my undergraduate C/C++ knowledge and showed me how to work with more objects, models, JavaScript, and even Ajax. Over the course of two years we spent many hours developing methods to handle assets (when items get stacked their unique ID changes), minerals pricing, API caching, and ultimately usability.

The code backend is not something we are extremely proud of, but the presentation and workflow has suited our operations well. If you have the fortitude to tolerate our design, then feel free to give our project a test run.

Disclaimer *IMPORTANT*

  1. Code is provided as is.
  2. If you want to try to setup your own instance, I would advice that you have an understanding of PHP, MySQL, and general code architecture. Getting this to run is not a beginner project.
  3. I cannot provide any commitment for bugs corrections, adding features, or providing support.
  4. There are many, many non-sanitized input points that are major security issues as noted in this post. I would not use this as a public-facing site.
  5. There are unfinished pages, hacked together solutions, code in the view, display code in the controllers, etc. — don’t expect clean, production quality code.
  6. Be prepared to edit the database table to enter your users and API keys. The ‘admin’ section is only partially completed.

Prerequisites

  1. MySQL, PHP5+
  2. Yii Framework
  3. phpMyAdmin to efficiently work with the database

Installation

  1. Download the ZIP here. [April 2014 Update] I’ve moved the project to GitHub here.
  2. Extract the code to your var/www folder
  3. Edit protected/config/main.php.changeme
    1. Line 29 contains IP limitations for working with Gii. Enter your WAN IP address here if you want to work with this module.
    2. Line 57/58 contain your database connection information.
    3. Rename to main.php
  4. Edit protected/config/console.php.changeme
    1. Line 22 contains the database name. My project was named after my holding corporation PROHD so the database name is ‘prohd’.
    2. Line 24/25 contain the user/password to login to the application.
    3. Rename to console.php.
  5. Modify Yii path in index.php
    1. Change the path to your Yii framework location $yii=’/usr/local/lib/yii/yii.php’;
  6. Replace /usr/local/lib/yii/caching/CDbCache.php with the one in the zip.
  7. Create database structure with prohd.sql
  8. Create a login in the ‘accounts’ table. The default userLevel is ‘1’.
  9. Enable the API feed by adding a 1,1 row to the apiStatus table.
  10. Define a group in trackingGroups table.
  11. Add your character information in the ‘characters’  table.
  12. Import the Eve static data tables from https://www.fuzzwork.co.uk/dump/
  13. Import the following table into typeBuildReqs for Tech 2 items. You can generate this table using my query or import from the previous table export.

Recommendations

If you are intimated by the scope of the setup procedure, I would recommend that you use the DRK Industry Tracker for your construction projects as my partner Raath is actively developing this project.

Update

https://k162space.com/2013/08/13/wallet-manager-code-update/


Blake’s Papercuts

Inspired by Jester’s post about accepting Eve as it stands, I started to record my own personal headaches while performing Industry and Trade related tasks.

Manufacturing/Invention
1. Menu items on Blueprints: I want to be able to Right-click, build or Right-click, invent from the hangar.
3. Batch processing: multi-select right-click, build/invent.
3. Two stage jobs, like capitals that have components that are build from minerals, can be built with one click from the capital Blueprint.
4. Easy display of Blueprints ME/PE from hanger view.
5. Stack Blueprints of equal ME/PE levels.
6. Multi cycle runs. Make 10 items 10 times as long as you have the required items in stock.
7. Pause jobs if they run out of materials and allow resume when sufficient items are placed in the hangar.

Booster Trade
1. Expand profession. Currently I know of a few major manufacturers of drugs and distribute to niche markets. I want to be able to have more players in the market, many more markets, and a feeling of sneaking contraband around the galaxy.
2. Given the limitations on production to POS’es, the barrier of entry for manufacturers is high. I remember a long time ago hearing about how Mittani proposed a drug manufacturing ship; imagine a stealthy ship that arrives out of nowhere, makes you drugs, and retreats into the abyss.

API
1. Expose Blueprint ME/PE/Run/Remaining.
2. Expose tower CPU/Power utilization and any timers such as anchoring, on-lining, or reinforced. (Metagame++;)
3. Expose saved ship fits for read/write to allow developers to create alliance/corp/mobile 3rd party applications for working with fits using XML.
4. Do not allow the buying, selling, modification of orders (I’ve wrote about the potential botting implications here).

POS Rework
1. Grant the ability to move POS’es between characters, corporations, and alliances.
2. Scrap the worthless Refining Arrays. They refine at too high a loss, are cumbersome to load and start refine jobs. In the new system you should be able to create paths between modules like we have in PI to transfer goods for constant production.
3. Add a mechanic for hacking/taking over offlined and abondoned towers.

Unified Inventory
1. Dragging something to the station Item or Ship hangar should automatically place it in the proper hangar. I hate having to drag ships to ships and item to items. The goods are going to my local hangar, just put them there.

General
1. Automatically remove me from my ship, stop skills from training, and initiate the clone jump.
2. Prompt/warn me that my clone is out of date before undocking.

Timers
1. I want to be able to see the time remaining for various things (GCC, etc) in the same fashion as the undock timer.

Kill Reports
1. Add Logistic repair amount.
2. Add coordinate and time data down to the millisecond to allow people to develop applications to replay and analyse battles.


Mobile Trading

The Procyon Holdings (PROHD) name is expanding again as we push out into another development area — the Android framework. When I abandoned my BlackBerry, the choice to move to the Android platform was an easy decision. Market saturation, the development community, and fluidity between devices make this platform shine.

I’ve been having a few end user problems with the current iteration of our trading website. First, the native Android Gingerbread browser lacks SVG support, an issue that has bewildered me since I read about it. I can solve this issue by using another browser such as Opera, but I would like to use the native one.

Also working with a HTML + Ajax website on a device with a touch interface feels like you are not getting the full user experience. I want to scroll down lists with my finder, click and hold to expose options, and speed around menus.

Mobile Architecture

We had to expand our design and create a few new pieces to get data to the mobile world. New items include the creation of a daemon to simply database population, exposing data via XML, and working with an existing library to enable Push notifications.

Information Flow

The Daemon runs on our server, periodically pulls transactions from the Eve API, and places that information in our wallet table.

Our previous design was that you would have to hit the transactions webpage and it would trigger an update. Getting a page to refresh on a desktop connected via broadband versus a mobile phone is quite the different experience.

Android has a framework called C2DM (Android Cloud to Device Messaging) which enables you to send lightweight push notifications to devices.

Our vision is that when a sell order clears, a large/special sale has occurred, or a significant milestone has been reached, we send a notice. This will be visible on phone’s Status Notification Bar and cause me to act if deemed important. I have been forgetting to buy or trade a particular item after the sell orders have cleared. Forget things — lose profit.

Getting data out of our database required the creation of a webservice to produce XML output from our wallet table.

Here is an example of the sale of one item in our XML feed:

<?xml version="1.0"?>
<TransactionList>
<Transaction>
<transactionDateTime>2012-04-19 06:21:04</transactionDateTime>
<typeID>21096</typeID>
<typeName>Cynosural Field Generator I</typeName>
<quantity>1</quantity>
<price>1948939.91</price>
<profit>147803</profit>
<stationName>STATION</stationName>
<icon>21096_32.png</icon>
</Transaction>
</TransactionList>

Alpha Screenshot

This screenshot is our first proof of concept. We’re able to get information from an XML feed, display it, and get/cache images from the Eve image server. Major credit goes to my trading and corporation partner James, who has been doing the development work and educating me on the Android framework.

There is a lot we want to do with the mobile version. Menus, tabs, charts, reports, and the ability to add items to a shopping cart/wish list are all things on the drawing board.

Future Collaboration

This project is our first exposure to the Android framework and after the initial learning curve, we might perhaps coolabrate with the Aideron Robotics team on Aura. So maybe some of our trading mechanics will make their way into the Aura application.


Working with Eve Static Data

More Power to the People

The propose of this short guide is to empower a person that has limited or no experience working with the Eve dataset to perform queries and extract useful information.

Eve has two main data sets. The first is the static data that is published by CCP and updated with each expansion. This set contains information that doesn’t change such as stargate coordinates, number of Moons in VFK-IV, type of research agents in the Forge, or the minerals requirements for a Drake.

The other set is the dynamic data, which is exposed through the API. This data contains the types of assets you own, wallet transactions, or kill statics for a particular system — things that change and things that go boom.

In this post I won’t be covering the dymanic data, as the Eve Development Network as an excellent introduction guide and full list of everything exposed.

More Data, More Problems

With every new release, CCP publishes a Microsoft SQL database backup file of their static data. You can find the most up to date file on the Community Toolkit website.

Along with the database backup, you will find other useful downloads such as images for every item in Eve — very important when trying to create something presentable out of boring database tables if you are designing a 3rd Party application.

Some people do not use Microsoft SQL and opt to run their database with MySQL, the most popular opensource database package. This format is not released by CCP and thus enterprising players routinely convert the data from Microsoft SQL to MySQL format. Nothing is lost, just converted.

Usually a few hours after a new file has been posted on the Community Toolkit site, people have conversions up for download. If you hang out in the Eve Technology Lab sub-forum, people will usually post links once available. I have been getting my releases from this site: http://zofu.no-ip.de/.

Install Procedure

Noise. has written an excellent guide on how to install the free version of Microsoft SQL Server and work with CCP’s database backup file in order to get to a point where you can run a database query.

Table Prefix Nomenclature

The naming format of the tables is very intuitive and can be picked up fairly quickly.

Example Tables

agt all about agents both mission and research
chr character information such as bloodlines, factions, and races
crp NPC corporation divisions
crt certificates
dmg damage attributes, effects, dogma (attribute system)
eve basic information for icons and units
inv market groups, item details, item reactions. I do a lot of work with these tables
map xyz data, landmarks, regions, solar systems connections, and general universe data
planet planetary interaction schematics
ram industry, manufacturing, refine
sta station services, xyz docking locations
trn multilingual stuff
war faction warfare

Tips and Tricks

An item’s typeID is a very important thing to know. This is a unique number that identifies a type of object in Eve.

Keep in mind that referring to a Drake (typeID 24698 in the static dataset) will be different than referring to your Drake (unique itemID 1002615059065 in the API feed). The itemID value is a unique number that identifies every object in Eve (1).

Eve Queries 101

Drake Details

SELECT *
FROM [dbo].[invTypes]
WHERE typeID = 24698
GO

List all Battlecruisers

SELECT *
FROM [dbo].[invTypes]
WHERE groupID = 419
GO

Eve Queries 201

PI Schematics are contained in the planetSchematicsTypeMap self referencing table. This means that the build requirements for P0, P1, P2, and P3 are all contained in one table. The isInput column is used to list build requirements when set to 1.

1. Listing all P0 items using the isInput value set to 0.

SELECT planetSchematicsTypeMap.*, invTypes.typeName FROM planetSchematicsTypeMap
JOIN invTypes ON (planetSchematicsTypeMap.typeID = invTypes.typeID)
WHERE planetSchematicsTypeMap.isInput = 0
AND invTypes.marketGroupID = 1335
ORDER BY invTypes.typeName

2. Listing items that go into Construction Blocks, a P1 with typeID 3828 and schematicID 74.

SELECT planetSchematicsTypeMap.*, invTypes.typeName
FROM planetSchematicsTypeMap, invTypes
WHERE planetSchematicsTypeMap.typeID = invTypes.typeID
AND schematicID = 74
AND isInput = 1

Eve Queries 301

CCP’s dataset does not contain a table with Tech2 build requirements. We have to create a new table that combines information from a number of tables and then work off the new table.

1. Run this pastebin query once to create your new typeBuildReqs table. If successful, you will get a message like this:

(15791 row(s) affected).

2. Find the build requirements for a Cap Recharge II with input typeID of 2033 (Cap Recharge II Blueprint). The NOT IN statement excludes the skills required to build the item.

SELECT typeBuildReqs.*, invTypes.typeID, invTypes.typeName FROM typeBuildReqs
 JOIN invTypes ON (invTypes.typeID = typeBuildReqs.requiredTypeID)
 WHERE typeBuildReqs.typeID = 2033
 AND invTypes.groupID NOT IN (268,269,270)
 AND activityID = 1

Moar!

If you want some more example queries, comment or find me on twitter.

(1) If you remember that old Devblog post about Drake missile lag, one major component to the lag was that every missile fired had to have a unique itemID created and destroyed after it exploded. This database operation ate up a lot of time, which is critical to a simulation trying to play through a battle.