The Procyon Holdings (PROHD) name is expanding again as we push out into another development area — the Android framework. When I abandoned my BlackBerry, the choice to move to the Android platform was an easy decision. Market saturation, the development community, and fluidity between devices make this platform shine.
I’ve been having a few end user problems with the current iteration of our trading website. First, the native Android Gingerbread browser lacks SVG support, an issue that has bewildered me since I read about it. I can solve this issue by using another browser such as Opera, but I would like to use the native one.
Also working with a HTML + Ajax website on a device with a touch interface feels like you are not getting the full user experience. I want to scroll down lists with my finder, click and hold to expose options, and speed around menus.
We had to expand our design and create a few new pieces to get data to the mobile world. New items include the creation of a daemon to simply database population, exposing data via XML, and working with an existing library to enable Push notifications.
Our previous design was that you would have to hit the transactions webpage and it would trigger an update. Getting a page to refresh on a desktop connected via broadband versus a mobile phone is quite the different experience.
Android has a framework called C2DM (Android Cloud to Device Messaging) which enables you to send lightweight push notifications to devices.
Our vision is that when a sell order clears, a large/special sale has occurred, or a significant milestone has been reached, we send a notice. This will be visible on phone’s Status Notification Bar and cause me to act if deemed important. I have been forgetting to buy or trade a particular item after the sell orders have cleared. Forget things — lose profit.
Getting data out of our database required the creation of a webservice to produce XML output from our wallet table.
Here is an example of the sale of one item in our XML feed:
<?xml version="1.0"?> <TransactionList> <Transaction> <transactionDateTime>2012-04-19 06:21:04</transactionDateTime> <typeID>21096</typeID> <typeName>Cynosural Field Generator I</typeName> <quantity>1</quantity> <price>1948939.91</price> <profit>147803</profit> <stationName>STATION</stationName> <icon>21096_32.png</icon> </Transaction> </TransactionList>
This screenshot is our first proof of concept. We’re able to get information from an XML feed, display it, and get/cache images from the Eve image server. Major credit goes to my trading and corporation partner James, who has been doing the development work and educating me on the Android framework.
There is a lot we want to do with the mobile version. Menus, tabs, charts, reports, and the ability to add items to a shopping cart/wish list are all things on the drawing board.
This project is our first exposure to the Android framework and after the initial learning curve, we might perhaps coolabrate with the Aideron Robotics team on Aura. So maybe some of our trading mechanics will make their way into the Aura application.
More Power to the People
The propose of this short guide is to empower a person that has limited or no experience working with the Eve dataset to perform queries and extract useful information.
Eve has two main data sets. The first is the static data that is published by CCP and updated with each expansion. This set contains information that doesn’t change such as stargate coordinates, number of Moons in VFK-IV, type of research agents in the Forge, or the minerals requirements for a Drake.
The other set is the dynamic data, which is exposed through the API. This data contains the types of assets you own, wallet transactions, or kill statics for a particular system — things that change and things that go boom.
More Data, More Problems
With every new release, CCP publishes a Microsoft SQL database backup file of their static data. You can find the most up to date file on the Community Toolkit website.
Along with the database backup, you will find other useful downloads such as images for every item in Eve — very important when trying to create something presentable out of boring database tables if you are designing a 3rd Party application.
Some people do not use Microsoft SQL and opt to run their database with MySQL, the most popular opensource database package. This format is not released by CCP and thus enterprising players routinely convert the data from Microsoft SQL to MySQL format. Nothing is lost, just converted.
Usually a few hours after a new file has been posted on the Community Toolkit site, people have conversions up for download. If you hang out in the Eve Technology Lab sub-forum, people will usually post links once available. I have been getting my releases from this site: http://zofu.no-ip.de/.
Noise. has written an excellent guide on how to install the free version of Microsoft SQL Server and work with CCP’s database backup file in order to get to a point where you can run a database query.
Table Prefix Nomenclature
The naming format of the tables is very intuitive and can be picked up fairly quickly.
|agt||all about agents both mission and research|
|chr||character information such as bloodlines, factions, and races|
|crp||NPC corporation divisions|
|dmg||damage attributes, effects, dogma (attribute system)|
|eve||basic information for icons and units|
|inv||market groups, item details, item reactions. I do a lot of work with these tables|
|map||xyz data, landmarks, regions, solar systems connections, and general universe data|
|planet||planetary interaction schematics|
|ram||industry, manufacturing, refine|
|sta||station services, xyz docking locations|
Tips and Tricks
An item’s typeID is a very important thing to know. This is a unique number that identifies a type of object in Eve.
Keep in mind that referring to a Drake (typeID 24698 in the static dataset) will be different than referring to your Drake (unique itemID 1002615059065 in the API feed). The itemID value is a unique number that identifies every object in Eve (1).
Eve Queries 101
SELECT * FROM [dbo].[invTypes] WHERE typeID = 24698 GO
List all Battlecruisers
SELECT * FROM [dbo].[invTypes] WHERE groupID = 419 GO
Eve Queries 201
PI Schematics are contained in the planetSchematicsTypeMap self referencing table. This means that the build requirements for P0, P1, P2, and P3 are all contained in one table. The isInput column is used to list build requirements when set to 1.
1. Listing all P0 items using the isInput value set to 0.
SELECT planetSchematicsTypeMap.*, invTypes.typeName FROM planetSchematicsTypeMap JOIN invTypes ON (planetSchematicsTypeMap.typeID = invTypes.typeID) WHERE planetSchematicsTypeMap.isInput = 0 AND invTypes.marketGroupID = 1335 ORDER BY invTypes.typeName
2. Listing items that go into Construction Blocks, a P1 with typeID 3828 and schematicID 74.
SELECT planetSchematicsTypeMap.*, invTypes.typeName FROM planetSchematicsTypeMap, invTypes WHERE planetSchematicsTypeMap.typeID = invTypes.typeID AND schematicID = 74 AND isInput = 1
Eve Queries 301
CCP’s dataset does not contain a table with Tech2 build requirements. We have to create a new table that combines information from a number of tables and then work off the new table.
1. Run this pastebin query once to create your new typeBuildReqs table. If successful, you will get a message like this:
(15791 row(s) affected).
2. Find the build requirements for a Cap Recharge II with input typeID of 2033 (Cap Recharge II Blueprint). The NOT IN statement excludes the skills required to build the item.
SELECT typeBuildReqs.*, invTypes.typeID, invTypes.typeName FROM typeBuildReqs JOIN invTypes ON (invTypes.typeID = typeBuildReqs.requiredTypeID) WHERE typeBuildReqs.typeID = 2033 AND invTypes.groupID NOT IN (268,269,270) AND activityID = 1
If you want some more example queries, comment or find me on twitter.
(1) If you remember that old Devblog post about Drake missile lag, one major component to the lag was that every missile fired had to have a unique itemID created and destroyed after it exploded. This database operation ate up a lot of time, which is critical to a simulation trying to play through a battle.
Jason Parks, the creator of the popular Aura suite for the Android platform, proposed a few questions on Google+ in regards to my platform for CSM7. For the past few months we have exchanged a few emails about our Eve projects, which would not even be possible without the wonderful API.
Given our shared passion for development work, he invited me to contribute to the Aura project. The fancy graphs and reporting logic that I have been able to generate out of my Wallet Manager site would be a wonderful addition to his suite (I hope to bring some of this design to the Eve client — more on that later).
Sadly, I don’t have a lot of time to pickup the Android framework so I haven’t taken him up on his offer to collaborate. I would, however, like to declare that given my background in 3rd party projects for Eve, the struggles of developers will be a major source of direction on my part if I become a member of the CSM.
Here are responses to Jason’s questions that will hopefully lock-in his vote for me:
POS management redesign
Can you elaborate a bit on this? I made a post here on Google+ that has my ideas (https://plus.google.com/u/0/115407184179295920691/posts/hJyoCgbo6tZ). What do you think about it? Would you push for something like this?
From what I have read from various CCP developers, the code that runs the POS’es is old and terribly maintained. I’m sure it was written years ago with no commenting or documentation and nobody wants to open that Pandora’s box. I need more time to solidify my stance on the POS rework. I need to pull up a recent CCP post about “castles in the sky” (?) and review CCP Greyscale’s Nullsec Development: Design Goals post. Look for a post soon on this.
Will you be our API champion? I would like someone to raise the suggestions that many of the 3rd-party devs. I have ideas that I will raise at fanfest when I can again but we have no one to follow through on them. Making a post in on the forums doesn’t help until we have a champion on the CSM.
This sounds like a perfect tagline for me. “Blake Armitage — API Champion”. I would not be so involved with Eve if there wasn’t such an active and passionate 3rd party community. The ability to get data out of Eve breeds innovation and allows us to work with data in ways that CCP wouldn’t have envisioned.
Traders and industry focused people have come up with systems to track profitability, product movements, and margins. Large alliances would not exist if they could not keep track of their POS network, reinforcement timers, and sovereignty information.
The free time that developers put into these applications shows us the depth of intrigue that Eve brings to our gaming lifestyle. Expanding the API, while keeping security and automation exploits in mind, can do nothing but enhance the game.
I do hereby accept the title of “API Champion”.
Will we be able to engage with you after you are elected? This past cycle we didn’t have CSM contact and I’ve been forced to troll The Mattani and Hilmar 😉
Most definitively. I love to talk shop with the other space nerds.
API Fanfest News
In other API news CPP has a session at Fanfest this year where they will talk about a read/write API called “Carbon REST“. This topic is of particular interest to me and it saddens me that I have a conflict for Fanfest this year.
A developer preview of a new RESTful, oAuth based read/write API for Eve Online – Carbon REST.
Carbon… REST…? Carbon (CCP’s new Framework) + REST (fancy client/server software architecture). From what I can gather, there are going to be some advances to the current API structure.
Having the ability to write data to the Eve database opens up a wide array of options for developers. Some of the items that I would like to see exposed are:
- ability to send mail
- add/remove and set standings for contacts
- add/remove/manage calendar items
- update the skill queue
- update personal and corp (role dependent) ship fits. This would allow applications such as EFT, Pyfa, or Aura the ability to work directly with the fits stored on the server. You could walk around with your phone, update your fit, put your phone in your pocket, get home, and have the updated fit on the Eve client. Drag that fit to the market window (thank you CCP) and purchase. No more managing XML files posted on forums
All these benefits do come with a price. First, CCP will have to remain conscious of the ability to script input. Having the ability to update market orders or submit industry jobs opens up a dark sea of automation. Additionally, the mentality of actually making us login to the game will have to remain a priority. If you can do all of your work outside the client, why even login? The social aspect of Eve will suffer as less and less people login. No more posting “creative” images to local while you gaze at a POS bubble.
As with everything computer related, there will need to be a balance between available and security. Given my background in network security I feel that I can keep these interests in mind. Seriously, look at my books at work:
I feel that exciting times for developers are in the works and I would love the opportunity to be a strong representative of the 3rd party development community on the CSM.
“the struggles of developers will be a major source of direction on my part if I become a member of the CSM”
“Expanding the API, while keeping security and automation exploits in mind, can do nothing but enhance the game”