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.
 

lpDatabase v0.11 - SQL release of in-game LP Stores (Kronos 1.0)

First post
Author
Previous Topic Next Topic
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#41 - 2013-04-27 20:03:09 UTC
Let me guess Jess Technite, you added, umm, one index, and it sped it up? Blink

Anytime a DB app is slow, indexes will probably help Lol

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#42 - 2013-04-29 05:32:55 UTC
Put together the release with the database schema changes. Updated schema can be found here:
http://pastebin.com/yxiMSeWJ

Changelog:
- The way that data has been laid out. NO ADDITIONAL VERIFICATION HAS TAKEN PLACE, this release is strictly an update to how the database is organized.
- Each offer is now unique and stored in the `lpOffers` table. This reduces redundant information, making the data slighty more compact (which makes is slightly more faster)
- Offers are linked to corps via `lpStore`
- Required items are much the same (besdies now pointing to unique offers), the table has been relabeled as `lpOfferRequirements`
- Included in this release is a table labeled `lpConversion`. This table documents the change from the old ID (storeID) to the new, combined ID (offerID). This table will not be inclded in future releases

Updated OP with link to download
Jess Technite
Almost Absolute
#43 - 2013-05-02 17:35:07 UTC
Sable Blitzmann wrote:
Just a heads up, the database schema will most likely be changing with the next release. I'm gonna base it mostly on Ydnari's approach (https://forums.eveonline.com/default.aspx?g=posts&m=2546104#post2546104). Since a lot of the data in the database is redundant, I aim to slim it up a bit.

I'm doing this because my lpStore application (which I've been procrastinating with releasing, ugh) would benefit from it. Let me know if there's any concerns with this new approach

Jess Technite wrote:
The process that iterates all the original table have a duration of 45 minutes :S


Really? Yikes! x_x


Nah, it's only Ydnari's approach in VB.NET code :)
BTW, I don't know why the university (Software Engineering finished in January) decided to use PostgreSQL ¬¬

Steve Ronuken wrote:
Let me guess Jess Technite, you added, umm, one index, and it sped it up? Blink

Anytime a DB app is slow, indexes will probably help Lol


In fact, it's the same in three tables. When I iterate for calculating the LP/ISK ratio over all the rows, I only iterate over 1.584 rows (2 minutes) and not over the 29.070 (45 minutes) original ones. When the process is finished, I select the most worth and when I want to see their details, will query to the lpcorps with the offerID and also won't need to iterate over that huge amount of rows. More light :). But well, maybe that index in the lpCorps also plays his paper Bear
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#44 - 2013-06-04 02:14:22 UTC  |  Edited by: Sable Blitzmann
Updated for Odyssey. One of the things that needs to be confirmed is navy BC blueprints and where they can be found. I've added them all to store with built navy cruisers (same way I added the built navy BCs), but I know there are some corps out there that offer mostly Blueprints and I'm unsure if these would be included.

Changelog:

v0.5.2 - Navy Battlecruisers Added
Another small point release as it doesn't contain any verifications, this release adds the new navy Battlecruisers introduced in Odyssey (from Odyssey 1.0 Patchnotes)
Added:
- All 4 Navy Battlecruisers - this process was automated by adding the battlecruisers to corporations already offering Navy cruisers
- All 4 Navy Battlecruiser Blueprint copies were also added via the above method (using Built Cruisers )
- FW stores got their own offer with the discounted prices
Changed:
- Armageddon Navy Issue, Scorpion Navy Issue, Dominix Navy Issue, Typhoon Fleet Issue have had their LP cost increased to 250,000 LPs in the Factional Warfare LP Stores.
Confirm:
- There are a few corporations that offer mostly blueprints - I am not sure if they offer ship blueprints and if so if they offer the new navy BC blueprints. This needs to be confirmed, and if so, needs to be added
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#45 - 2013-06-05 17:05:21 UTC  |  Edited by: Sable Blitzmann
So, this snuck up in the expansion (thanks to Artemis Anva pointing it out)

- Navy Cap Boosters are now available in sizes of 25, 50 and 75, which are available through FW LP Stores.
- Navy Cap Booster 100 and 150 are now m-ore expensive when purchased in the LP Store.

v0.5.2 does not cover these changes. I will have to look at them throughout the week when I get a chance, unless someone can provide screenshots of the new prices (remember, FW stores can vary in price from regular stores!). CCP didn't think it pertinent to include the new/updated prices in the patch notes

EDIT: Thanks to Artemis Anva, I have updated the database and will release it tomorrow with the new cap boosters / prices for existing ones
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#46 - 2013-06-06 14:24:22 UTC
v0.5.3 is out with Navy Cap Booster changes for Odyssey. OP has been updated

Changelog:

v0.5.3 - Navy Cap Booster Changes
This version includes the navy cap booster changes that shipped with Odyssey
Added:
- Navy cap booster 25, 50, and 75 added to FW LP Stores, cost: 60, 125, 185 in LP and 60000, 125000, 185000 isk respectively
Changed:
- Navy cap booster 100 and 150 prices raised to 250 and 375 lp and 250000 and 375000 isk respectively
Confirm:
- (from before) There are a few corporations that offer mostly blueprints - I am not sure if they offer ship blueprints and if so if they offer the new navy BC blueprints. This needs to be confirmed, and if so, needs to be added
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#47 - 2013-06-06 16:20:36 UTC
bah, changed database structures make me have to rewrite SQL Smile

ah well, at least changes were highlighted that I needed to take care of. .= and += can lead to very different answers ;)


(in case I've not mentioned it, https://github.com/fuzzysteve/lpstore it depends on a memcache DB for price data, but if you don't have it, it'd be simple enough to replace with, say, database calls. I wouldn't recommend hitting eve central or eve market data individually for each price. )

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#48 - 2013-07-04 04:48:57 UTC
OP Updated

Thanks to those who mailed me about the changes to Navy Cruisers in Odyssey (thanks CCP for not including that in the patch notes). Note: these changes only affect the FW stores.

v0.6 - FW Navy Cruiser Fixes
I decided to bump this release up to a 0.x point release even tho no verification has been done due to significant changes have been done since v0.5. It was brought to my attention that CCP changed the Navy Cruiser requirements and added blueprints for FW stores.

Added:
- 2x Blueprints for the previous tier1 cruisers have been added to each FW corp, with prices at 45000 LP and either 5000000 isk or a cruiser-class pirate tag
Changed:
- Previous Tier1 Navy Cruisers have had their LP cost increased to 45000 for FW corps to bring in line with other Navy cruisers
Chic Botany
Doomheim
#49 - 2013-07-27 08:45:23 UTC
Kudos for doing this, and when I get my head round sql I'll have a look.

The only gripe is WHY THE HELL DOESN'T CCP GIVE THIS INFORMATION FREELY IN THE DUMPS ETC, it's not like it's confidential info or anything.
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#50 - 2013-08-31 17:45:58 UTC
lpDatabase should be updated very soon after Odyssey 1.1 hits. I have all the changes queued, I just need to know the new typeIDs for the various new store items (gotta wait for the SDE to come out as I'm too lazy to gather it from the test server).
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#51 - 2013-09-02 23:00:16 UTC
Updated for Odyssey 1.1: http://dl.eve-files.com/media/1309/lpDatabase_v0.7.zip

v0.7 - Odyssey 1.1
Odyssey 1.1 introduced new mindlinks to stores. This has obviously not been verified as it was all automated based on what CCP has stated in forum posts / patch notes
Added:
- Navy Mindlinks added to their respective stores for 100,000,000 isk / 100,000 lp. Per CCP, Navy Mindlinks were added to every racial store, including FW stores. I took all corps belonging to each of the four main factions and autmatically added their respective Mindlink (Imperial Navy Warfare Mindlink for all of Amarr faction with LP store). This has obviously not been verified as it was automated
- Added T2 mindlinks to CONCORD LP Store for 20,000,000 isk / 20,000 LP
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#52 - 2013-09-03 13:17:18 UTC
I've been informed that for the Fed Navy Comet, it should be using an Incursus, rather than a Tristan

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#53 - 2013-09-03 17:27:10 UTC
Steve Ronuken wrote:
I've been informed that for the Fed Navy Comet, it should be using an Incursus, rather than a Tristan


Thank you. I've check a couple stations and this is indeed the case. I wonder if this was a recent change, because I remember it being Tristins
Jess Technite
Almost Absolute
#54 - 2013-09-19 09:55:17 UTC
For all those that (like me) use PostgreSQL there's a conversion of the file published by Sable Blitzmann in #51:

http://dl.eve-files.com/media/1309/lpDatabase.zip
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#55 - 2013-10-22 06:12:36 UTC  |  Edited by: Sable Blitzmann
v0.7.1 - Cruiser BP Fixes
Removed:
- With v0.6, new Navy Cruiser BPCs were added to the FW store. Unfortunately, all BPCs were added to all FW stores. This removes the erronous entries. (Thanks Mr Turing!)
Changed:
- Fed Navy Comet required item changed from Tristan to Incursus (Thanks Steve!)
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#56 - 2013-11-16 04:51:48 UTC  |  Edited by: Sable Blitzmann
Update for Rubicon 1.0. This also fixes some changes to a couple navy frigs. A previous update fixed the Comet, however it seems that the Slicer and Hookbill were affected as well without notice. (Thanks Andronitis and Jericho for pointing this out!)

http://eve-files.com/dl/265884

v0.8 - Rubicon 1.0
Added:
- Astero: 100,000LP/10,000,000ISK for SoE corp, and 80,000LP/5,000,000ISK for The Sanctuary
- Astero BPC: 30,000LP/15,000,000ISK for SoE corp, and 20,000LP/10,000,000ISK for The Sanctuary
- Stratios: 300,000LP/20,000,000ISK for SOE corp, and 240,000LP/15,000,000ISK for The Sanctuary
- Stratios BPC: 120,000LP/30,000,000ISK for SoE corp, and 80,000LP/20,000,000ISK for The Sanctuary
Fixed:
- Slicer has had it's ship requirement changed from Punisher to Tormentor
- Hookbill has had it's ship requirement changed from Merlin to Kestrel

Also, OP is getting too big. Copying changelog to this post until I figure out what to do with it. I don't want to lose links to previous versions in case someone is interested in them for whatever reason. =)


  • v0.8 - Update for Rubicon 1.0
  • v0.7.1 -Point release for fixes
  • v0.7 - Updated for Odyssey 1.1
  • v0.6 - Fixes changes made in Odyssey
  • v0.5.3 - Point release. Includes Navy Cap Booster changes/addition with Odyssey.
  • v0.5.2 - Point release. Navy Battlecruisers added for Odyssey expansion!
  • v0.5.1 - Point release. Database schema has changed to remove redundancy, please see this post for changes.
  • v0.5 - Nothing really new with this release besides the continued fixes.
  • v0.4 - With this release, I took the easy way out. As described here, I've started to use already-verified corps as a template for other verifications. Many corps in the game share the same LP store, and so I set up a script that would compare multiple corps to a template corp. Template corps are always manually verified.
  • v0.3 - Second round of fixes include all 4 FW LP Stores. I tried to focus on them as they are very important in the LP Store business, and making sure that the data was correct was a priority (this also knocks out 4 of the biggest corps to verify \o/).
  • v0.2 - First round of verification's and fixes tot he database. Some items are still out of whack, but I believe that some of the more glaring ones are under control now. I have personally verified 5 corps so far. That's a far cry from the few hundred that there are, but it's a start.
  • v0.1 - initial release. Data might not be up to par. NEED TESTERS
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#57 - 2013-11-16 17:32:29 UTC
Thanks Smile

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Drailen
Doomheim
#58 - 2013-11-23 22:34:35 UTC
Been admiring this work for a while and would like to start using it, but...any chance of a version that doesn't require MySQL to be installed? Big smile

CSV, MSSQL, SQLite would be great if it's available.
Sable Blitzmann
24th Imperial Crusade
Amarr Empire
#59 - 2013-11-23 23:37:45 UTC
Drailen wrote:
Been admiring this work for a while and would like to start using it, but...any chance of a version that doesn't require MySQL to be installed? Big smile

CSV, MSSQL, SQLite would be great if it's available.


I've been floating the idea of making json version available which should be easy to then convert to any other format. This will give me an excuse to do that. I'll try to get something out by tomorrow. =)
Drailen
Doomheim
#60 - 2013-11-23 23:43:55 UTC
That would be great, thanks!