EVE Technology Lab

 
^ Back to top
5 Pages123Next pageLast page
 

lpDatabase v0.10 - SQL release of in-game LP Stores (Rubicon 1.3)

Author
Imperial Outlaws.
#1 Posted: 2013.01.23 21:42  |  Edited by: Sable Blitzmann
Hello thar fellow internet spaceship pilot!

I have put together a database that represents information regarding the various LP Stores in-game. This is an ongoing project to verify the accuracy of the data and keep it up-to-date with the release of expansions.

Initial data was based on a CSV file provided by Zanto Snix, which was apparently a web scrape of DaOpa's Ellatha LP store. As both of these were severely outdated at the time with many errors and typos, much of the information needed to be fixed. Additionally, due to both Ellatha and chruker.dk (who owns this?) being proprietary with the database not released to the community, I decided to make this available to everyone. =D

tl;dr:
lpDatabase.zip v0.10 (sql, json)

History

Recently, I needed to be able to take LP Store offers and manipulate the data for a project I'm working on. So I went on a trek to find the database somewhere. CCP doesn't include this information in their dump, so I figured surely someone, somewhere would have made one and posted a SQL file. Alas, turns out I was wrong (or maybe my GoogleFu was not up to par). The only hits I got back were the likes of the LP Store Database (http://www.ellatha.com/eve/LP_Stores.asp) and, indirectly, http://games.chruker.dk/eve_online/default.php (I didn't find an LP store, but most LP items show their requirements on their respective item page)

The problems with these sites was that they were not available in SQL format, and attempts to contact them for it did not work. I cannot work with webpage data - I need it in cold, hard SQL!

I finally came across usable data, thanks to Zanto Snix and his Google Doc (link). It looked like a scrape from Ellatha's LP Store site, and seemed to be more or less complete. And I can download to a .csv file! Now I have real data, and a real file format to work with. But it can't end there

The Google Doc (and indeed, most LP stores out there) was fairly outdated. Some of the LP store offers did not have typeIDs, you couldn't find some of the required items in the database, and then there were just plain typos and duplicates. The majority of the issues stems from the recent module name changes.

Anyway, to correct these issues I set up a web app that would parse the CSV file and, if it ran into an issue (couldn't find item in database, possible typos/bad format, etc), it would let me know and present a form to correct it. Worked out pretty well, but still took quite a while. After I finally finished with that part, I tried to cross reference all LP store offers with chruker.dk/eve_online (contains outdated item names, however typeIDs were still valid. Some items aren't represented as LP offers on that site tho) and then remove duplicate entries.

Once that was done, it was a matter of parsing the file into MySQL. I'm sure there's some of you that would appreciate this data, so I'm making it available.

However, I need help in 1) verifying the information is correct, and 2) keeping the database up to date. It's not often CCP adds or removes items in the database, however it could happen. If they change the name, it'll still be up to date as I use the typeIDs, however if they change the actual items or quantity, it'll need to be updated. So if you notice wrong information or items that need adding, post them here (with proof (image) preferably) and I'll correct it asap and post a new file.

Use
These projects are using lpDatabase. Let me know if you make / find one and want it added to the list! Currently all sites use the data to present it and calculate isk/lp in some way.

fuzzwork/lpstore - the first usage of the database, released just days after. Uses EMDR for pricing information.
blitzmann/lpStore - my own version, and the project that make me want to gather data for a database in the first place. Open source.
noonecares/LpStore - ialocin's version, using EVE Central's API.
Ivy League
#2 Posted: 2013.01.26 18:13
@Sable Blitzmann
That is awesome! Thank you for creating it and releasing it to all of us!
Even more so if you'll continue to maintain it... :)
It's sad it's not included in the SDE, really.
Download is the meaning of life, upload is the meaning of intelligent life
http://eve.nikr.net - home of jEveAssets
#3 Posted: 2013.01.26 21:18
I just got me a copy of it.
Hmmmm, I'm really tempted into adding an LP store browser in EVEMon. I'll scan the data and see what's possible.

Maybe I'll even release an MSSQL version.

Thanks anyways for the work done.
Imperial Outlaws.
#4 Posted: 2013.01.26 23:49  |  Edited by: Sable Blitzmann
Thanks! I already know of a few inaccuracies (mostly stuff like clothing that was not in the original data). Unfortunately, without help from others, I won't be able to keep it up to date. But please, if you find something wrong with it, let me know! It's easy to see what LP offers are happening when yo're docked. I may try to get a human-readable version online somewhere so that more people can check it.

Hope you guy's can use the data!

EDIT: Okay, so I actually took a look at some LP stored in game today, and data is definitely not complete. Various items missing. I will be going to various LP stores in the vicinity of my HQ and adding them in throughout the coming weeks and uploading periodic updates.
Vote Steve Ronuken for CSM
#5 Posted: 2013.01.31 04:00
Great data, thanks for making it available. Smile

I've played with it a bit, and worked it together with the pricing data I'm gathering (I think I need to work on the caching a little, to reduce the load and speed it up. But that's all behind the scenes stuff) to produce a calculator for working out what is worth buying. It falls down on the blueprints and similar items which aren't on the market. Or have components which aren't on the market.

http://www.fuzzwork.co.uk/lpstore/
Vote Steve Ronuken for CSM
#6 Posted: 2013.01.31 14:02
First couple of issues:

Starbase charters come in packs of 100. There's a couple of places where stores have a quantity of 1.
You get Customs Gantry blueprints from the LP store, not Customs Gantries.
Imperial Outlaws.
#7 Posted: 2013.01.31 16:54
Steve Ronuken wrote:
First couple of issues:

Starbase charters come in packs of 100. There's a couple of places where stores have a quantity of 1.
You get Customs Gantry blueprints from the LP store, not Customs Gantries.


Just saw your post on Reddit. Wonderful work! I replied with a few questions of my own, I'd be interested in hearing your reply.

Thank you for reporting on some inaccuracies. I'll look into them and release a point release soon. =)

Project Wildfire
#8 Posted: 2013.01.31 18:07
Very nice.

Just started looking at this, found a couple of things.

Caldari Navy Stasis Webifier Blueprint has repeated Federation Navy Fleet Major Insignia I instead of Federation Navy Fleet Colonel Insignia I x21

update lpRequiredItems
set typeID = 15594
where parentID in (5533, 5532, 5531, 5524, 5536, 5538, 5526, 5527, 5534, 5528, 5525, 5530, 5537, 5523, 5529, 5535)
and typeID = 15593
and quantity = 21;

There's some duplicate offers for same item in same corp in there.

And there's definitely some 5% implants with the costs repeated as for 1% and 3%, for example Zainou 'Gnome' Launcher CPU Efficiency LE-605 but I think that's been mentioned on the reddit thread and here already.


For what it's worth I wanted the data turned around slightly, so I created three tables and populated them from your data - this process helped to shake out some of the duplicates too:

lpOffer - one row per unique reward / LP / ISK / items combination
lpOfferRequiredItem - 0, 1 or many rows for items for an offer
lpStore - corporation, offer ID

That way the isk/lp/items isn't repeated per corporation, since there's a load of shared offers across corporations with the same requirements.

What do you think? I've knocked up a script that converts from your format to mine anyway, don't want to get in the way of getting the source data cleaned up.
--
Imperial Outlaws.
#9 Posted: 2013.01.31 23:23  |  Edited by: Sable Blitzmann
Ydnari wrote:
Very nice.

Just started looking at this, found a couple of things.

Caldari Navy Stasis Webifier Blueprint has repeated Federation Navy Fleet Major Insignia I instead of Federation Navy Fleet Colonel Insignia I x21

update lpRequiredItems
set typeID = 15594
where parentID in (5533, 5532, 5531, 5524, 5536, 5538, 5526, 5527, 5534, 5528, 5525, 5530, 5537, 5523, 5529, 5535)
and typeID = 15593
and quantity = 21;

There's some duplicate offers for same item in same corp in there.

And there's definitely some 5% implants with the costs repeated as for 1% and 3%, for example Zainou 'Gnome' Launcher CPU Efficiency LE-605 but I think that's been mentioned on the reddit thread and here already.


For what it's worth I wanted the data turned around slightly, so I created three tables and populated them from your data - this process helped to shake out some of the duplicates too:

lpOffer - one row per unique reward / LP / ISK / items combination
lpOfferRequiredItem - 0, 1 or many rows for items for an offer
lpStore - corporation, offer ID

That way the isk/lp/items isn't repeated per corporation, since there's a load of shared offers across corporations with the same requirements.

What do you think? I've knocked up a script that converts from your format to mine anyway, don't want to get in the way of getting the source data cleaned up.


Thanks for the info!

Yeah, implants are a mess. I've already fixed the LE-605, along with a few other things. My alt is sitting in Jita, I'll verify the CN Stasis Webifier bit and fix it

Your way of storing the data is interesting. I was going to lay out the data the way (or similar) however I don't know just how uniform all the LP store offers are across the board. For example, customs offic gantry blueprints are 3,000/10,000,000 at the 4 FW LP Stores, but double the price at CONCORD (and that's confirmed). So I'm reluctant to group offers together like that until they can be verified. Then again, CONCORD (and the FW) stores are kinda unique; I'm sure the vast majority of offers are duplicates.

I would be interested in that script if you could post it. I may be able to use it.

Also, what do you mean by this:
Quote:
There's some duplicate offers for same item in same corp in there.

I thought I removed all the duplicates. =(
Project Wildfire
#10 Posted: 2013.02.01 11:35
Sable Blitzmann wrote:
I would be interested in that script if you could post it. I may be able to use it.
tables:
http://pastebin.com/raw.php?i=9BSTQWLQ

script (Perl):
http://pastebin.com/raw.php?i=hf18kJ6j

needs your tables renaming to "oldLpStore" and "oldLpRequiredItems" before running

Sable Blitzmann wrote:
Also, what do you mean by this:
Quote:
There's some duplicate offers for same item in same corp in there.

I thought I removed all the duplicates. =(

Looks like in the end there's only one - EE-605 for Mordu's Legion, the offer looks right but there's two of them (ids 1992, 4039).
--
Imperial Outlaws.
#11 Posted: 2013.02.01 18:34
Ydnari wrote:
Sable Blitzmann wrote:
I would be interested in that script if you could post it. I may be able to use it.
tables:
http://pastebin.com/raw.php?i=9BSTQWLQ

script (Perl):
http://pastebin.com/raw.php?i=hf18kJ6j

needs your tables renaming to "oldLpStore" and "oldLpRequiredItems" before running

Sable Blitzmann wrote:
Also, what do you mean by this:
Quote:
There's some duplicate offers for same item in same corp in there.

I thought I removed all the duplicates. =(

Looks like in the end there's only one - EE-605 for Mordu's Legion, the offer looks right but there's two of them (ids 1992, 4039).

Ah, thanks. I'll definitely look into it. =)
Gallente Federation
#12 Posted: 2013.02.03 04:45
The FW and Concord stores are definitely 'off' - that can be seen looking at the table that Steve Ronuken has up off fuzzworks.

This dump is really helpful, overall - but I still can't get past the idea that there's still going to have to be a manual validation of the data, and that's just looking to be incredibly painful.

Pretty sure we can make some general assumptions - there are the FW corps which have 'different' values for their LP stores, CONCORD is pretty much separate, as well, within each faction you have different corp arch-types, with multiple corps having the effectively the same LP stores.

Of course, this does raise the question regarding whether or not it's worth too much effort to take their vagaries into consideration, beyond default values :)
Ivy League
#13 Posted: 2013.02.03 10:59
I wonder if the LP stores is somewhere in the cache/client files? Would make it so much easier to validate...
I would rather write a tool to do the boring work, then do it by hand... not that I'm volunteering or anything :P
Download is the meaning of life, upload is the meaning of intelligent life
http://eve.nikr.net - home of jEveAssets
Vote Steve Ronuken for CSM
#14 Posted: 2013.02.03 18:08  |  Edited by: Steve Ronuken
I'm slowly doing a little confirmation of the stores. 3 done so far (State Protectorate, Modern Finances,Royal Amarr Institute)

I'm putting my notes into:
https://docs.google.com/spreadsheet/ccc?key=0As88qKwn3d59dEV5UDJKQVBnMV9QbEhabjdiY1ZwNUE#gid=0

But it's just notes, rather than something you can automate off.



If anyone wants to validate a store, I'd suggest copying it all off into a google spreadsheet then doing a side by side comparison, marking any discrepancies. If you send it over to me, I'll update appropriately.
Imperial Outlaws.
#15 Posted: 2013.02.03 23:09
Golden Gnu wrote:
I wonder if the LP stores is somewhere in the cache/client files? Would make it so much easier to validate...
I would rather write a tool to do the boring work, then do it by hand... not that I'm volunteering or anything :P


I do not think the info is available in the Cache, otherwise someone would have done something like this by now. I agree, it would make it so much easier. If someone who is familiar with the cache/client files wants to look into this or provide a little more insight, that's be great.

Steve Ronuken wrote:
I'm slowly doing a little confirmation of the stores. 3 done so far (State Protectorate, Modern Finances,Royal Amarr Institute)

I'm putting my notes into:
https://docs.google.com/spreadsheet/ccc?key=0As88qKwn3d59dEV5UDJKQVBnMV9QbEhabjdiY1ZwNUE#gid=0

But it's just notes, rather than something you can automate off.



If anyone wants to validate a store, I'd suggest copying it all off into a google spreadsheet then doing a side by side comparison, marking any discrepancies. If you send it over to me, I'll update appropriately.


Thanks for the help! I've set up a quick web app that helps with the process. I check each item in the in-game store with the web app, and if it's the same I can just remove it from the DOM. If it needs to be changed, it presents the store ID and stuff. I also have a query running on each item that checks the other costs and if there's differences it tells me so I can investigate. So it helps a bit.

If you can be a bit more accurate or detailed with your notes (such as every change you make with IDs and stuff) that'd be much easier to follow.

I'll be putting up updated dumps tonight.
Imperial Outlaws.
#16 Posted: 2013.02.03 23:10
Steve Ronuken wrote:
If anyone wants to validate a store, I'd suggest copying it all off into a google spreadsheet then doing a side by side comparison, marking any discrepancies. If you send it over to me, I'll update appropriately.


Also, what do you mean by copying it to a spreadsheet? I thought I tried copying the LP store to clipboard and it didn't work...
Vote Steve Ronuken for CSM
#17 Posted: 2013.02.03 23:37  |  Edited by: Steve Ronuken
Sable Blitzmann wrote:
Steve Ronuken wrote:
If anyone wants to validate a store, I'd suggest copying it all off into a google spreadsheet then doing a side by side comparison, marking any discrepancies. If you send it over to me, I'll update appropriately.


Also, what do you mean by copying it to a spreadsheet? I thought I tried copying the LP store to clipboard and it didn't work...



I was meaning copy the web page (from mine. guess I should have been clearer. Or posted in my own thread. oops.) into a spreadsheet. because from there, you can make notes more easily.


I've added an extra table just to maintain a list of which stores I've gone through and checked.. But that's not an important one.

Anyway, some sql. Those three stores looked pretty close to right. corporationids of: 1000180, 1000077, 1000030.

Don't just cut and paste it. The lpRequiredItems insert depends on the id inserted in the row before. It's because of the missing Caldari heavy assault missile launcher from the modern finances store.

update lpStore set lpcost=1850,iskcost=1850000 where typeid=27315;
insert into lpStore (corporationid,typeid,quantity,lpcost,iskcost) values (1000030,28377,1,24000,9600000);
insert into lpRequiredItems (parentid,typeid,quantity) select 28779,typeid,quantity from lpRequiredItems where parentid=8791;
update lpRequiredItems set typeID=15594 where parentid=5525 and quantity=21;
update lpRequiredItems settypeid=17244 where parentid=6228;
update lpRequiredItems set typeid=17244 where parentid=6228;
update lpStore set typeid=24641,iskcost=10875000,lpcost=10875 where id=3762;
Imperial Outlaws.
#18 Posted: 2013.02.04 08:15  |  Edited by: Sable Blitzmann
v0.2 is posted in OP.

Verified corps (personally):
- University of Calle
- Mordu's Legion
- CONCORD
- Modern Finances
- Lai Dai Protection Service

Along with various fixes.

EDIT: Ytiri is also verified with no modifications in v0.2
Imperial Outlaws.
#19 Posted: 2013.02.13 03:51
Uploaded v0.3. Biggest thing here is that all 4 FW militia stores are now verified

Verified in v0.3:
- Ytiri
- State Protectorate
- FedMart
- Chemal Tech
- 24th Imperial Crusade
- Federal Defense Union
- Tribal Liberation Force
Vote Steve Ronuken for CSM
#20 Posted: 2013.02.13 04:29
Sable Blitzmann wrote:
Uploaded v0.3. Biggest thing here is that all 4 FW militia stores are now verified

Verified in v0.3:
- Ytiri
- State Protectorate
- FedMart
- Chemal Tech
- 24th Imperial Crusade
- Federal Defense Union
- Tribal Liberation Force



I've just updated to your latest version. Big smile Thanks a bunch for doing this.

Even if you gave me a minor heart attack by changing it to storeid rather than id Blink
5 Pages123Next pageLast page
Forum Jump