# market.fuzzwork.co.uk

With the introduction (a wee while ago) of the new eve market data endpoint, and the problems which surfaced at Eve Central while that was happening, I decided to create my own market data website. And so, I present market.fuzzwork.co.uk

I posted it was out a few days ago, but thought I should write a little more about it, and the process of creating it.

## Background

At fanfest, in one of the round tables, I brought up (again) the idea of an endpoint to get the order book for an entire region at once. While most people just want aggregates, that’s not something that would be easy for CCP to provide for multiple items, at the same time. In part because the aggregation takes time, and because the multiple items makes caching that data a lot harder (different people asking for different lists of things). Lo and behold, a short time later, such an endpoint came into being. It’s paginated into batches of 30,000 items, which is enough for most regions, though The Forge has 9 pages.

## Details

So I rented a new server (with an offshoot of OVH called kimsufi) and set about building one. It’s a PHP site, running on an NGINX server, with a PostgreSQL database, and Redis for caching the aggregates.

The downloading script I wrote in python, and after an abortive attempts at aggregating the data, first in the database itself, then breaking it out into the script to process, I settled on using Pandas to do the aggregation. The script version would have taken over an hour to process. The Pandas version runs in a few minutes. This lets me do the grabbing of data once every 30 minutes, retaining it for a couple of days; that means you can look at snapshots of an order over that timeframe, to see how it changes.

That retention brought problems of its own. Not so much in keeping the data (each grab adds a couple of hundred meg of data and indexes.) but cleaning it up. Or being specific, the effects on the import, of having deleted it. Turns out the database doesn’t like it when you’re inserting and deleting 1.7 million rows every thirty minutes. It’s down to how it stores it. I won’t get into technical details, but it went from a couple of minutes, to over 15. which impacted kind of negatively on the performance of the site. The process wasn’t taking much CPU time, but it completely pegged the disk activity at 100%, and led to the site timing out. Not good.

How to solve this issue? One way would be to get a server with SSDs. Unfortunately, these are kind of expensive, relative to the one I’m using. I’m not made of money, after all. So I put together a Patreon campaign. If you want to contribute to the upkeep of the servers, I’d appreciate a pledge, however small it is. (Small is good in fact. My expenses aren’t that high. I’d feel bad about offloading everything to someone else)

However, a thought came to me recently. I’m using Postgres, and it can partition data, based on specific conditions. So I can have the data being stored in a partition based on the order set, and just truncate out partitions as they age out. This is far more efficient than deletion, and shouldn’t impact on the import speed. It’s not fully tested yet, but it’s looking somewhat better already. It’ll increase my data retention a bit (up to 200 samples (4 days ish), rather than the 96  (2 days) I was planning) but space isn’t too much of a concern that way. And partitions allow for more efficient queries.

## Future

I still need to write the API interface for it, so you can request an arbitrary number of aggregate/region combinations, but that isn’t too far out. And I’ll provide a sample method for importing into google. In addition, I’m providing the downloaded orderbook as a gzipped csv, for an undetermined period of time (it’s all dependant on space, tbh. and zipped they’re not huge)

I also need to decide how I’m going to winnow the aggregates. The API will only allow for ‘live’ data, as that’s all I’m keeping in redis, but all the data is also being kept in a database table. I’ll likely add a page so you can see how they change over time. To keep the table from becoming an absolute monster, I’m thinking I’ll keep only a week at 30 minute intervals, then averages for the day, if you want to see over a longer time frame.

In case you’re interested, I’m generating a weighted average (price* volume summed up, then divided by the total volume), the max, the min, and a weighted average of the lowest/highest 5% of the orders (lowest for sell, highest for buy) ignoring any price more than 100 times the lowest/highest price. It’ll help when someone puts in a buy order for a million Gilas, at 0.01 isk. Which otherwise completely screws with the average prices.

## Other

Oh, and I put together a very simple fleet tracker. Doesn’t do anything other than show you where fleet members are, and what they’re flying. Needs a CREST privilege, and a link you get from the fleet window. And for you to be fleet boss. I may expand it a little in the future. Maybe for invites too. Though that would require me to store the access and refresh token somewhere other than the user’s session.

As normal, all my code is on my github. Might be a little behind the release, but that’s purely down to me not committing it yet.

I’m open to suggestions, if you have them.

Anyway, Steve signing out.

# BP Calculator: T2 Invention update

Just another quick update.

The Form Entry page now has another submit button, called ‘Enter list – T2 Invention’. If you use this, it will take a list of T1 blueprints which can be invented from (ignoring anything else) and produce a list of everything you can make, along with a basic price for them. It doesn’t deal with decryptors, so it’s not doing all the work for you. And I’m not going to simplify it to just take a list of blueprints without the rest of the line.

But if you already have a list of blueprints, it’ll help pick out the ones you might want to look at. Just remember, there’s no market volume data. Just price data.

If you were to drop by the blueprint calculator, you’ll have noticed a few changes.

# New features

• Regional prices. You can now pick the region on the price list. It will always  default to Jita for now.
• Collapsible price list. It will remember the  state you left it in.
• Parser for a blueprint list. If you open up the S&I interface on the blueprint list (or corp blueprint), then double click on the location you’ll get a window you cut and paste from. Paste it in, that you’ll get a list of links, with a profit per unit.
• General optimization. Performance since I added the table sorting has suffered. It should now be back up to normal.
• Added a while back: Decryptors in the invention figures.

# Future plans

• Once the SSO becomes available, you’ll be able to log in, and store blueprints, if you want to. This will be on the server, rather than in the client. I’ll probably leave the option to store with cookies too, if you don’t trust me with that. Other preferences will be stored
• Once CREST becomes available, with a character sheet end point, your skills (if desired) will autofill.
• Inclusion of invention prices on the parsed results.
• Inclusion of isk/hr (ish) on the parsed results

## SSO/CREST:

Don’t worry about the security of SSO. The way OAUTH2 works is:

• You hit the login button, which sends you to login.eveonline.com with a token that identifies where you’re coming from.
• You log into Eve online, and pick the character you want to be.
• You get sent back to the source site, with a long token.
• The source site sends the token to eve online asking ‘who is this?’
• Eve responds, identifying who you are and invalidating the token for future use. (CREST works a little differently but not significantly.)

At no time do you give anyone except CCP your password. Any third party site asking for it is either badly coded, or a scam. Ideally you’d log into the forums, and then you won’t need to type in a username or password into any site.

# Using the Eve Central API with PHP

We’re going back to basics here. One thing that many sites, which are using the SDE from Eve, want to do, is access price data from EVE Central api. I don’t do this myself, having a EMDR relay, and consumer to provide my market data, but back when I was starting out, I did this. It’s a pretty simple thing to do, the only pain in the neck being the data structures that PHP wants to use, when you’re using XML. It likes throwing in arrays where you don’t think it should, which can be a bit of a pain. If you ever need to debug this, var_dump() will be your friend, showing you exactly how the data has been structured.

So, here’s a fairly basic bit of code. It asks eve central for the information for Tritanium, then prints out the so called ‘Percentile’ price. This is the price if someone was to buy 5% of the market, then average the cost out. It’s handy for ignoring the outliers, if not entirely accurate. The code is more complicated than it needs to be, but we’ll get to why in the second example.

$typeid=34;$url="http://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=".$typeid;$pricexml=file_get_contents($url);$xml=new SimpleXMLElement($pricexml);$item=$xml->xpath('/evec_api/marketstat/type[@id='.$typeid.']');
$price= (float)$item[0]->sell->percentile;
$price=round($price,2);
echo $price;  Fairly simple, really. You get the type id for Tritanium, the url that will pull back the data from the forge, mash them together, then use file_get_contents to retrieve them. Please not, some PHP installs will stop this from working. There’s a more complicated version at the bottom which may get you round this; functionally identical but using curl instead of file_get_contents. Once you have the output from EVE Central, you push it into the SimpleXML parser, grab the bit that’s just about tritanium (the XPath bit does this. This is the over complicated bit), then pull just the sell percentile out of it. The reason for the [0] is because the Xpath bit could have returned more than one entry. In this case it can’t, but the parser is taking the safe option and giving you an array. The (float) is there as without it, you’re dealing with a SimpleXML data type, which is a pain to do math with. (float) just converts it into a regular number. Now for the more complicated version. Eve central allows for multiple items to be queried at the same time. This is why I put the xpath in there. It allows us to target a specific type being returned, rather than having to go through each in turn to see if it’s the one we want, before continuing. $typeids=array(34,35);
$url="http://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=".join('&typeid=',$typeids);
$pricexml=file_get_contents($url);
$xml=new SimpleXMLElement($pricexml);
foreach($typeids as$typeid)
{
$item=$xml->xpath('/evec_api/marketstat/type[@id='.$typeid.']');$price= (float) $item[0]->sell->percentile;$price=round($price,2); echo$typeid." ".$price."\n"; }  This time, instead of a single type id, we’re asking for multiple. We store them in an array, then mash that array into the url, with a join (or implode) which adds the right number of &typeid= to it. Then it goes on as before until just after the xml is parsed, where it goes and grabs each individual element out in turn, iterating through the array. If you wanted to pull the minimum buy price, you’d replace the$price= (float) $item[0]->sell->percentile; with$price= (float) $item[0]->buy->min; and so on. It’s fairly simple. Now, the curl version, of the first bit of code: $typeid=34;
$url="http://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=".$typeid;
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_HEADER, 0);
$response = curl_exec($ch);
if($response === false) { echo 'Curl error: ' . curl_error($ch);
}
else
{
$xml=new SimpleXMLElement($response);
$item=$xml->xpath('/evec_api/marketstat/type[@id='.$typeid.']');$price= (float) $item[0]->sell->percentile;$price=round($price,2); echo$price;
}


It’s a bit more complicated, but it gives you a way of dealing with a remote site not responding. Perhaps looking at a different site for price data, for example.

Remember, the more calls you have on remote sites, the slower your pages will be to load. Consider caching the price data locally, perhaps in a database, or if you have access to it, memcache (My preference. but it’s uncommon)

# Dev Track Day 2 – Part 2

This was the WebGL session.

It’s great. Really really great.

The only problem is, potentially, delivering the models to the clients. Other than that, it’s wonderful. If you can take the loading time, it’s great.

https://github.com/ccpgames/ go fork it.

Not a huge amount to say really. It was demos of graphics stuff. which looked great.

# Dev Track – Day 2 – Part 1

Just a small gap between the SSO session and the WebGL session.

The SSO looks to be fairly simple to work with. It’s a shame that the demo mobile applications which were demonstrated didn’t use a native browser, using an embedded one, which is far from good practice with OAUTH2, but they do make them easier to understand, with less explanation needed (Custom protocols for the call back to get the token to the application, for example. I believe Aura does this with getting the keys from the site.).

It was fairly obvious, from some of the questions being asked, that there were some fundamental misunderstandings, but in the end, it works well enough. More demonstration code will be needed, but I’m sure the community will provide some. I’ll probably do it, when I kick it around myself.

There was some discussion at the end, about situations like character transfers not being obvious to third party sites, but the devs seem receptive to possibly adding a userid characterid based hash. So you can’t tie users together, but you can tell when one is transferred.

No ETA on deployment, unfortunately. As it’s tied to the dev license, I wouldn’t expect it until at least the next draft (30 days+)

There was also a request from the devs to make sure that we make it obvious on the forums if we’d like an updated IGB, and two factor authentication. I’ve added posts in the tech lab, so if you could go and wave in them, I’d appreciate it.

# Useful bits and pieces – Eve SDE

Once you get comfortable with the CCP way of doing things, the SDE isn’t too bad to work with. You’ll have to dig around in it to get some of the numbers you want, and sometimes it’s the opposite way round from what you’d expect, but it’s not too bad, most of the time. Here are a few more useful snippets for getting information on things.

# Eve SDE SQL – Blueprint Details – Part 2

I covered the basics of Blueprint details from the SDE back in my last post, which covers most of what people actually want to know. There are a few extra bits which the more dedicated coders will want. They’re simple enough to work out, but it’s time you might not want to have to look at. Consider this a round up post.

## Perfect Blueprints

Perfecting blueprints is, in many cases, a waste of time. You want ‘good enough’, rather than perfect. It’s also unattainable for some. A Raven would take you over 4 million days to perfect. And half of that time (or so) would be to save a single Trit.

I don’t have a formula for a ‘good enough’ level. It’s very dependent on the types of materials being used. The Formula for perfect, for each material, is:

$floor(Quantity*(\frac{(\frac{Waste Factor}{100})}{0.5})$

If you want the perfect for an entire blueprint, pick the material with the highest quantity.

As before, the Waste Factor comes out of invBlueprintTypes.

## Production Times

More details from invTypeBlueprints.

If the PE of the blueprint is 0 or above, the total time in seconds is:

$productionTime*(1-(\frac{productivityModifier}{productionTime}*\frac{Blueprint PE}{1+Blueprint PE}))$

If the PE of the blueprint below 0, the total time in seconds is:
$productionTime*(1-(\frac{productivityModifier}{productionTime}*(Blueprint PE-1))$

$Time from Above * (1 - (0.04 * Industry))$

So to recap, the time in the database is the maximum time it’ll take to manufacture something (assuming it’s not a negative PE), with everything else reducing the time. The changes also fall off rapidly. PE beyond 5 or so is often worthless.

## Materials to invent off of something

select t.typeid,t.typeName, r.quantity
from ramTypeRequirements r,invTypes t,invBlueprintTypes bt
where r.requiredTypeID = t.typeID
and r.typeID = bt.blueprintTypeID
and r.activityID = 8
and bt.productTypeID=?;


The bind variable is what you’re making. You could simplify the query if you had the blueprint ID already, eliminating the join to invBlueprintTypes, going straight to ramTypeRequirements. It’s possible to pull the damage too, but the SDE currently says the Interface is used up.

If you want to eliminate the interface from the pulled materials, use this sql. It’s eliminating interface, by ignoring the group it belongs to. To pull just Interface, then turn the != into a =

select invTypes.typeid,invTypes.typename,ramTypeRequirements.quantity
from ramTypeRequirements,invBlueprintTypes,invTypes
where producttypeid=?
and ramTypeRequirements.typeid=invBlueprintTypes.blueprintTypeID
and activityid=8
and invTypes.typeid=requiredTypeID
and groupid !=716;


## Skills to manufacture something

select t.typeName Name, r.quantity Quantity
from ramTypeRequirements r,invTypes t,invBlueprintTypes bt,invGroups g
where r.requiredTypeID = t.typeID
and r.typeID = bt.blueprintTypeID
and r.activityID = 1
and bt.productTypeID=?
and g.categoryID = 16
and t.groupID = g.groupID


The bind variable is what you’re making. You could simplify the query if you had the blueprint ID already, eliminating the join to invBlueprintTypes, going straight to ramTypeRequirements.

## Skills to invent something

This is a little bit of a pain. The skills are tied to the datacores and interface, rather than the job. Specifically they’re in dgmTypeAttributes, under the attributeIDs of 182 for the skill, and 277 for the level of the skill. As they’re done that way, pulling it with a single sql query is somewhat difficult, with a pivot table required (use if conditions, along with a max, then group them). When I’ve done it, I’ve just used 2 queries, and handled the pivoting in the code.

### The skills

select t.typeid,t.typeName skill,r.requiredTypeID
from ramTypeRequirements r,invTypes t,invBlueprintTypes bt,
dgmTypeAttributes dta
where coalesce(dta.valueInt,dta.valueFloat)= t.typeID
and r.typeID = bt.blueprintTypeID
and r.activityID = 8
and bt.productTypeID=?
and dta.typeid=r.requiredTypeID
and attributeID=182


The requiredTypeID refers to material you need the skill for. In this case, datacores and interfaces. Everything else is the skill.
The bind is what you’re making.

### The skill levels

select r.requiredTypeID,coalesce(dta.valueInt,dta.valueFloat) level
from ramTypeRequirements r,invBlueprintTypes bt,dgmTypeAttributes dta
where r.typeID = bt.blueprintTypeID
and r.activityID = 8
and bt.productTypeID=587
and dta.typeid=r.requiredTypeID
and attributeID=277


The requiredTypeID refers to material you need the skill for. Level is the minimum level for the skill.

In the previous 2 queries you’ll have seen the use of coalesce. That’s because CCP seem to have had trouble deciding which to store the value in. It’s normally the valueInt, but not always.

## Research times

researchMaterialTime and researchProductivityTime are straight out of invBlueprintTypes.

### Material Efficency research

Per level:
$researchMaterialTime*(1-(Your Metallurgy*0.05))$

### Productivity Level research

Per level:
$researchProductivityTime *(1-(Your Research*0.05))$

Times are all in seconds.

## Getting Basic Blueprint Information

select productionTime,wasteFactor,productivityModifier,researchProductivityTime,researchMaterialTime
from invBlueprintTypes
where productTypeID=?;


This is most of the information needed in previous equations.

## Is this obtainable through invention?

select parentTypeID
from invMetaTypes
where typeid=?
and metaGroupID=2


This will only return a result if you can invent the blueprint from another one. The parentTypeID is the typeid of the original type. Such as a rifter, if you’re looking at a wolf.

## Chance to invent

This isn’t actually in the SDE. However, I found some handy sql to stick it into an table you can use for lookups. The type id is that of the product, rather than the blueprint.

create table inventionChance(typeid int,chance float);

insert into inventionChance (typeid,chance)
select typeid,CASE
WHEN t.groupID IN (419,27) OR t.typeID = 17476
THEN 0.20
WHEN t.groupID IN (26,28) OR t.typeID = 17478
THEN 0.25
WHEN t.groupID IN (25,420,513) OR t.typeID = 17480
THEN 0.30
WHEN EXISTS (SELECT * FROM eve.invMetaTypes WHERE parentTypeID = t.typeID AND metaGroupID = 2)
THEN 0.40
ELSE 0.00
end
from eve.invTypes t,eve.invBlueprintTypes b where b.producttypeid=t.typeid;


That’s everything for now. I’ll cover how I deal with price data in a later post, but it’s a little more involved, as I’m taking it all from EMDR‘s firehose, and depending on memcache.

# Eve SDE SQL – Blueprint Details

As you may know, I’m the ‘author’ of one of the blueprint calculators that some people use in EVE Online. The one at http://www.fuzzwork.co.uk/blueprints/

If you didn’t know that, how did you get here? 😉

Anyway, it’s not uncommon to see people asking how to get the materials for building something out of the Static Data Export; and as such, I thought I’d write up a post here that I can direct people to. Oh, and if you’re looking for the code for the blueprint calculator, take a look at https://github.com/fuzzysteve/eve-blueprint-calc It’s mostly up to date, just missing a few twiddly bits.

# The SQL

Now for the meat of this post. Being able to run this SQL is dependent on having a copy of the SDE up and running. If you’re using anything other than mysql, postgresql or oracle, you may have problems with the greatest function, in the first bit of sql. replace it with a case statement. You’re wanting to return 0, if the value is negative.

select typeid,name,greatest(0,sum(quantity)) quantity from (
select invTypes.typeid typeid,invTypes.typeName name,quantity
from invTypes,invTypeMaterials
where invTypeMaterials.materialTypeID=invTypes.typeID
and invTypeMaterials.TypeID=?
union
select invTypes.typeid typeid,invTypes.typeName name,
invTypeMaterials.quantity*r.quantity*-1 quantity
from invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt
where invTypeMaterials.materialTypeID=invTypes.typeID
and invTypeMaterials.TypeID =r.requiredTypeID
and r.typeID = bt.blueprintTypeID
and r.activityID = 1 and bt.productTypeID=? and r.recycle=1
) t group by typeid,name
SELECT t.typeName tn, r.quantity qn, r.damagePerJob dmg,t.typeID typeid
FROM ramTypeRequirements r,invTypes t,invBlueprintTypes bt,invGroups g
where r.requiredTypeID = t.typeID and r.typeID = bt.blueprintTypeID
and r.activityID = 1 and bt.productTypeID=? and g.categoryID != 16
and t.groupID = g.groupID

## Notes

There are three bind variables in total, two in the first, one in the second. These are all the same value, the typeid of the item being manufactured. You could just replace them, but bind variables are your friend. Learn how to use them, and love them. People that do string replacement are asking for trouble. PHP has PDO, Perl has DBI, Python has DB-API, Java has JDBC. They all support bind variables.

The reason for the two queries is that CCP have split the materials into those affected by ME, which are also recovered when you recycle it (the base materials), and those which aren’t affected (the extra materials). The base materials query here doesn’t return the materials recovered, but that’s due to the removal of materials which are marked as recyclable in the extra materials.

# Extra Credit

The queries above only give you the materials for a perfect blueprint. It’s rare that you actually have one, so you’ll need to take ME and Production Efficiency into account as well. The waste factor referred to in the formulas is that one that’s defined in invBlueprintTypes. Perfect is the number of the materials defined by the above queries.

If your ME is 0 or greater, then for each material in your base materials: $round(Perfect + (Perfect * \frac{(\frac{Waste Factor}{ME of Blueprint+1})}{100}))$

If your ME is less than 0, then for each material in your base materials: $round(Perfect + (Perfect * (\frac{Waste Factor}{100}*(1-ME of Blueprint))))$

If your production efficiency is less than 5, then you also need to add $(Perfect*(0.25-(0.05*Production Efficiency)))$ to your materials. You only do this for:

• Base materials
• Extra materials that also appear in the base materials

If it’s 5, you’re adding a number that’s been multiplied by 0. You may wish to do this for completeness, or so that your code works for more than just your main producer.

That’s it for now. If you have any questions, feel free to ask here, or in Eve.

# Ship Fit Viewer

Been working on a little something, and it looks to be working out nicely.
I had been wanting to be able to put in ship fits here, and have them look semi reasonable. And now, I can.
Now up on github, with basic integration instructions. https://github.com/fuzzysteve/Ship.js
Feel free to use the code directly from here, but be aware this is also where I’ll be developing from.