These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Some help needed on the static dump data (and some api help maybe)
Author
Previous Topic Next Topic
Resender
Freedom-Technologies
#1 - 2011-10-19 11:42:10 UTC
Hello all,
I'm currently developing a database in SQL Server 2008 R2 to suit my daily activities in EvE, monitor how much I'm making and when the best moment is to sell or buy.

I recently changed allot so that it works with the item ID's ccp uses.

Centrally I got an items table which contains the data from the invType table, I've moved groups and categories also to this level,in the static dump types belong to a group and group belongs to category. Making queries a bit hard to write.

Got a table itemMineralContent which tells me how much minerals are in an item and the quantity needed when refining/recycling.

A table itemprices keeps track of the days itemPrices, I loads these in using SSIS package. I get the data from the eve markets site.
An inventory table lists all current assets and where the are, the database also contains tables referring to stations, regions,systems and such (Can even track player build outposts). I use EveAssetManager to generate an csv file for this.

I still need to create table to show the market groups.

However I'm a bit lost on the data concerning blueprints, I don't really store all the mumbo jumbo about efficiency.
I need to know how I can get what item is used in what blueprint and how much the base quantity is.

2 other problems I would like to resolve is my current dependency on the eve market site and the use of EveAssetManager

Any help is welcome.
Lutz Major
Austriae Est Imperare Orbi Universo
#2 - 2011-10-19 12:20:04 UTC  |  Edited by: Lutz Major
Resender wrote:
However I'm a bit lost on the data concerning blueprints, I don't really store all the mumbo jumbo about efficiency.
I need to know how I can get what item is used in what blueprint and how much the base quantity is.

In the invTypeMaterials and ramTypeRequirements are all base materials listed.

The invTypeMaterials.typeID connects with the invTypes.typeID, the ramTypeRequirements.typeID with the invBlueprintTypes.blueprintTypeID !

The mumbo jumbo about efficiency is based on formulas (http://wiki.eve-id.net/Equations) and there is currently no way to determine your ME/PE levels through the API.

Hope that helps.
Resender
Freedom-Technologies
#3 - 2011-10-19 12:39:42 UTC  |  Edited by: Resender
It helped,when looking in the direction you ointed out I came across the base query I needed.


So this query gives me what I need
-- Base materials
select i.itemID ,t.typeID, m.quantity
from invTypeMaterials m
inner join invTypes t ON m.materialTypeID = t.typeID
--inner join invTypes t2 on m.typeID = t2.typeID
inner join EOData.items.BlueprintProduces bpp on bpp.ItemID = m.typeID
inner join EOData.items.Items i on bpp.BlueprintID = i.itemID
where 1 = 1

With EOData being the name of my database
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#4 - 2011-10-19 13:30:22 UTC
http://wiki.eveonline.com/en/wiki/EVE_API_Character_Asset_List has the details for the asset api.

If you're doing this entirely within sql server, I'd suggest looking at
http://programming4.us/database/2576.aspx though I'm unsure how you would get the api call in the first place. possibly something like http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx

As for the market data, you'd need to look at a cache scraper. They seem to mostly be written in python.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Resender
Freedom-Technologies
#5 - 2011-10-19 13:55:24 UTC
Txn, I found a ssis script that I'm looking into that can take data of an rss feed, that way I could get the market data off eve markets automated instead of having to download the xml files
Resender
Freedom-Technologies
#6 - 2011-10-27 11:16:20 UTC  |  Edited by: Resender
I succeeded into creating an SSIS (SQL Server Integration Services) package for my database.

I had to make the following changes to my database
added table items.MarketGroups
-MGID
-MarketgroupName
changed table items.Items
-MGID added

I made sure the data was correct in both tables.

The SSIS package is called DailyMarketDatav2
The first thing I do is get all the MGID for the items I want to follow.
In this case ore,ice,iceproducts and minerals exluding missionitems that belong to these and have
subsequently have a MGID NULL.

These MGID's are stored in a recordset, a first for each loop runs through this recordset, a script source collects the data
off the XML directly off the URL
For instance
http://www.eve-markets.net/xml.php?listid=mg18&key=
for minerals, the number after listid=mg is replaced by the MGID in the recordset.
From the database the groupID and categoryID are added to the result from the URL.

I get itemID,itemName,groupID,categoryID,price (median),I don't really need the itemName I included it so that during debugging I could see
if the correct data was gathered.

First I check if the combination itemID, groupID,categoryID already exists in the table itemPrices, if not those records are immediatly inserted
into itemPrices

Second the check of itemID,groupID,categoryID,itemPrice existsthose that differ are changes in the market prices.
These are stored in a second recordset

A second for each loop (within the first) will then loop through the recordset containing the changed market prices and do the neccesairy updates.

Finally,after both loops are done, the current date is added to the itemPrices and they are stored in history.Itemprices table.

The code I wrote is in VB 2008, and I would post it here but the forum doesn't let me