open All Channels
seplocked EVE Technology Lab
blankseplocked Want a local mssql db continually updated with your api data?
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

Enraged Stoat
Posted - 2011.06.12 15:16:00 - [1]
 

Want to develop tools that use API data? Want to develop with a database back end rather than XML files (though they're available too)? Don't want to develop all the pipework yourself? I've developed an app that takes API keys, downloads API data and munges it into a MS SQL database. You can use this database as a datasource for developing your own tools. All you provide is an empty database on a SQL server, somewhere for the app to run and your API key(s).

Key features

* This is for you to run locally, I don't want your keys or API data :)

* It scales effortlessly (you can feed it as many API keys as you like).

* It takes limited or full keys and downloads all the data it can.

* Updates from multiple data vectors. E.g. if a character leaves corp that is reflected as soon as seen in any of the CharactersOnAccount, CorpMemberTracking, CharacterSheet or CharacterInfo API data.

* Continuous 23.5/7 downloads and updates to data as cache timers expire

* Conforms to best practices (I hope). I.e. it gives up on keys reported as bad and backs off during downtime or when certain api error codes are received.

It currently grabs all character info, skills, certs, contacts, assets, walletjournal, wallettrans. And all corp info, assets, POS and POS details. (Eventually it'll pickup all API data but this should be enough to start a beta with).

It also grabs map kills, sovereignty and alliance list. Kills and sovereignty build up historical data over time.

I'm currently looking for a few beta testers. If this sounds like something you would be interested in (for yourself, corp or alliance) please post here or contact me ingame.

The smallprint. The beta is free, the release won't be YARRRR!! probably be some kind of isk based subscription.

Vogue
Short Bus Pole Dancers
Posted - 2011.06.12 21:42:00 - [2]
 

Edited by: Vogue on 12/06/2011 21:42:25
Oh yay something for the asp.net crowd (I presume). I would like to be a beta tester.

DejaPeaux
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.12 21:43:00 - [3]
 

I would Like to test it as well..

Enraged Stoat
Posted - 2011.06.13 13:55:00 - [4]
 

Edited by: Enraged Stoat on 14/06/2011 05:46:02
Thanks for wanting to test :)

More details - requirements and some simple setup instructions - can be found here. There's also a schema diagram, it should be pretty obvious how the tables are laid out.

(edit) don't send me an evemail, decided to make it an open beta.

Lutz Major
Posted - 2011.06.13 18:07:00 - [5]
 

Hi, Stoat!
I took a quick look on your schema (I'm currently on my mobile):
Your wallet table has the transactionID as primary key defined, but the wallet transaction API can have two entries with the same transactionID when you buy your own stuff :)
AFAIK the only difference between those entries is the buy/sell flag

Enraged Stoat
Posted - 2011.06.13 20:57:00 - [6]
 

Originally by: Lutz Major

Your wallet table has the transactionID as primary key defined, but the wallet transaction API can have two entries with the same transactionID when you buy your own stuff :)



Well spotted, sir! Had not thought of that one Laughing Have fixed primary key to include the transactionType :) Will update the diagram later.

DaveTheGreat
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.13 20:58:00 - [7]
 

He has a 1 million isk charge for any evemails...

Enraged Stoat
Posted - 2011.06.13 21:11:00 - [8]
 

Edited by: Enraged Stoat on 13/06/2011 21:14:46
Edited by: Enraged Stoat on 13/06/2011 21:13:00
Originally by: DaveTheGreat
He has a 1 million isk charge for any evemails...


OMG forgot I had set that some years ago. Sorry EmbarassedLaughingShocked unsetting that now...

(edit) should be unset now? and you have your 1m back too ;) EmbarassedEmbarassed

DaveTheGreat
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.13 21:16:00 - [9]
 

thanks...

Gheent
Caldari
LazyBoyz Band of Recreational Flyers
Intergalactic Exports Group
Posted - 2011.06.14 03:07:00 - [10]
 

I would love to beta test this.... can I get in on it?

Enraged Stoat
Posted - 2011.06.14 05:11:00 - [11]
 

Originally by: Gheent
I would love to beta test this.... can I get in on it?


Sure! It will be an open beta, anyone can sign up.

I have posted more lengthy instructions here. The only thing missing at this point is the zip with the poller binaries in :)

Just got a couple more things to tweak and it'll be ready for public consumption. But have to go out tonight so it'll probably be tomorrow now. Sorry :(

Enraged Stoat
Posted - 2011.06.14 12:26:00 - [12]
 

It's now available for download.

I know it's a giant wall of text (there are a couple of pictures) but do please RTFM everything you need to get up and running is in there.

Remember, this is a beta. This app has grown out of a console app I wrote years ago. It's a little clunky. There is much user interface work still to do. But the polling and munging of data part is fairly bulletproof - it now runs for months without falling over :) That said, it has not has more than a few peoples keys to play with, I'm sure some of you will have data that will upset it, sorry if that happens. Post here and let me know about it!

Next few weeks will be spent adding more API data calls and improving the UI.

Happy to answer any questions in this thread. Also want any constructive criticism. No flames please ;)

Pre-answer for the paranoid. The APIPoller does not communicate with anything other than the Eve API server over the network. Your keys are safe.

DejaPeaux
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.15 06:01:00 - [13]
 

i'm getting the following error..

Probing data file path... OK.
Opening EveOrg database... OK.
Opening CCP database... OK.
Validating EveOrg database... OK.
Validating CCP database... OK.
Validating admin email address...has already been tested,
OK.
Saving settings... OK.
Reading keys.xml file... found 0.
Performing daily DB maintenance... failed!
Invalid column name 'lastdbbackup'.

Enraged Stoat
Posted - 2011.06.15 12:34:00 - [14]
 

Originally by: DejaPeaux
i'm getting the following error..
Invalid column name 'lastdbbackup'.


Dang, my bad, missed one column out of the DBInit script.

You need to add a column to the 'global' table. Call the column lastdbbackup and make it a datetime (allow it to be null).

I have fixed the DBInit script in the zip download so any future downloads should work first time. Thanks for reporting it!

DejaPeaux
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.15 15:58:00 - [15]
 

Now i'm getting.


Processing Eve_RefTypes...
Processing Account_Characters...
Processing Account_AccountStatus...
Processing Char_CharacterSheet... found 1.
. completed in 0s.
Processing Corp_CorporationSheet...
Processing Char_SkillInTraining...
Processing Char_MarketOrders...
Processing Char_WalletTransactions...
Processing Char_WalletJournal...
Processing Char_AssetList...
Processing Corp_AssetList...
Processing Corp_MemberTracking...
Processing Corp_AllianceCorps... found 11362.
.........10.........20.........30.........40.........50 abort@50 completed in 4s.
Processing Char_ContactList...
Processing Eve_CharacterInfo...
Data maintenance... found 0
Last poll completed @ 15 Jun 2011 15:54:04 UTC.

*** THERE ARE CCP API KEYS IN THE DATABASE THAT DO NOT HAVE VALID REGISTRATION KEYS.
*** THE DATA ASSOCIATED WITH THESE KEYS HAS NOT BEEN UPDATED.
*** CHECK THE KEYS.XML FILE.

I have inserted 2 keys for my 2 accounts.. and done the register thing at your subscribe page.

Enraged Stoat
Posted - 2011.06.15 22:31:00 - [16]
 

Hm I found a bug, please grab a new copy of the zip and update everything from it except the setup.xml file. Let me know how that goes. Thanks again!

DejaPeaux
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.16 01:00:00 - [17]
 

It's still the same...

I also had a problem with the backup.. I had to manually backup the transaction log first before the automatic backup would work.


Enraged Stoat
Posted - 2011.06.16 01:24:00 - [18]
 

Meh, ccp took the api offline while I was fixing the bug so I couldn't complete a proper test. Turns out there was another bug, which is now fixed. Please update from the zip again should take care of the key issue.

Not sure about the backup error, the poller just tells the sql server to run a backup so any failure is within sql. It should have thrown up any error sql reported and stopped polling?

Enraged Stoat
Posted - 2011.06.16 10:45:00 - [19]
 

DejaPeaux....

I spent this evening farming the poll process off to another thread, and including the CorporationContactList poll into the process.

Also found a couple more mistakes in the dbinit script so you'll probably still get errors with the poller you have.

Suggest you...

1. Grab the new poller zip file.

2. Update your install with everything from the zip file, sorry you will lose your current setup.

3. Drop the APIData DB and create a new blank DB.

4. Run the poller, reconfigure with your setup and let it init the new DB. (You won't need to re-enter keys, just datapath and odbc bits).

Thanks for all your time so far, hopefully you'll get a completed poll and all your data real soon now!

The new threading is so sweet, glad I took the time, keeps the UI fully responsive :)

DejaPeaux
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.16 12:21:00 - [20]
 

It looks good. I haven't got a chance to fully test it as i'm at work. Their's no errors, and i'm getting my assets now...Very Happy

Enraged Stoat
Posted - 2011.06.16 12:46:00 - [21]
 

Great, glad to hear it :) Thanks for being so patient.

DejaPeaux
Mine 'N' Refine
The Unforgiven Alliance
Posted - 2011.06.16 20:28:00 - [22]
 

Yes everything seems fine, would be hard to include the Industry Jobs for the character and Corp.

Enraged Stoat
Posted - 2011.06.16 22:51:00 - [23]
 

Nope not hard, will probably get those in this weekend. Any other requests? :)

Enraged Stoat
Posted - 2011.06.17 12:34:00 - [24]
 

The bad news, I found a bug that causes endless looping getting wallet data, might want to disable those polls for the time being.

The good news, it is fixed, new version will be out in ~24 hours.

The better news, the new version also polls...

Corp WalletJournal
Corp WalletTrans
Corp OutpostList
Corp OutpostServiceDetails
Char IndustryJobs
Corp IndustryJobs
Char Research

Going to try for char/corp killmails too :)

Enraged Stoat
Posted - 2011.06.18 14:17:00 - [25]
 

Updated APIPoller and instructions.

You'll probably want to update the APIPoller from the zip, drop/recreate the APIData DB and let it reinit first time the new poller is run.

Significantly more data is now polled and stored, see list of polls on the screenshot.

Known issues, only top layer of items in killmails are logged - i.e. exploded stuff in containers is not stored... yet.

Lost Hamster
Hamster Holding Corp
Posted - 2011.06.18 14:37:00 - [26]
 

Got an error with my key:
1 key found.
KeyID Bb00312b is invalid!

Enraged Stoat
Posted - 2011.06.18 14:45:00 - [27]
 

Edited by: Enraged Stoat on 18/06/2011 14:45:37
:( Please evemail me the key XML from the keys.xml file in the data folder.

I'll have to investigate tomorrow, it's almost 3am here, bedtime :)

Manentia
manic-recession
Posted - 2011.07.12 14:37:00 - [28]
 

Count me in for beta.

Manentia
manic-recession
Posted - 2011.07.12 18:00:00 - [29]
 

I'm unable to get past the Test Setup window:

Quote:
Version 0.81b
Probing data file path... OK.
Opening EveOrg database... OK.
Opening CCP database... OK.
Validating EveOrg database... OK.
Validating CCP database... failed!

Ensure the SQL user "User Id=EveOrgUser" has full DBO owner permissions on the database "StaticDataDump"

'CREATE VIEW' must be the first statement in a query batch.



I'm using the latest Incarna static dump (Incarna_1.0_43729_db.zip) and EveOrgUser is indeed given db_owner rights to both the APIData table (working correctly) and StaticDataDump.



 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only