Nullsec Alliance Data Transformation

Posted: 2011-09-21 | Author: | Filed under: eveonline | Tags: , , , , , , , , | 1 Comment

Messy numbers to some, or a wealth of information for others. Here is a quick rundown of how I processed the data to form the Nullsec Alliance Growth and Decline post.

1. Get raw data from Wollari at Dotlan.

allianceID,date,members
151380924,26.06.2007,622
166439722,26.06.2007,1130
284278305,26.06.2007,252
288377808,26.06.2007,1151
386292982,26.06.2007,412
477769446,26.06.2007,252
628991027,26.06.2007,1062
632866070,26.06.2007,2267
673381830,26.06.2007,416

2. Load Eve Alliance information via API into a table.

3. Import Dotlan data into MySQL table and join allianceID on both tables to generate more human friendly data set that includes Alliance Names and Tickets.

SELECT alliances.allianceID, alliances.name, alliances.shortName, date, members
FROM allianceHistory, alliances
WHERE alliances.allianceID = allianceHistory.allianceID

4. Export data into an Excel readable file.

I spent some time debating about working with this data using a MySQL table and a charting engine such as Highcharts, but settled on using an Excel PivotTable. The time involved with me tinkering with the JavaScript (not my most proficient programming language) would really hinder progress.

Once in Excel, I had to reformat the date from dd.mm.yyyy to yyyy-mm-dd to allow proper chart generation. First, I used the text to column feature with a comma ‘,’ set as the deliminator. Secondly, in a new column, I used a formula to combine the date in my desired format.

=DATEVALUE(F1&"-"&E1&"-"&D1)


Hide the D, E, and F columns and we’re got a good set of data to analyze.

5. Select all 87,543 rows and generate a PivotTable using the Alliance Name as the legend, date as the Axis Fields, and a summation of the Member Count as the Value.

Now the fun part is thinking of WHAT is significant.

Some of the first things that I wanted to show was the rise and fall of the NC, growth of the DRF, and some charting for the larger Alliances like Goons and Test. These charts can be seen in the original post linked at the top.

RipardTeg of Jester’s Trek came to me with idea for a new report that I think will prove rather interesting. I’m going to be working with him to answer the question of ‘whether small sov-holding null-sec alliances are surviving in the last two years, or being folded into the mega-alliances over time’.

This type of Eve Community collaboration with passionate, exemplary individuals is what really keeps me playing. Stay tuned.

Nullsec Alliance Growth and Decline

Posted: 2011-09-15 | Author: | Filed under: eveonline, history | Tags: , , , , , , , | 14 Comments

Individual Influence

One of Eve’s tenets is that anything created can also destroyed. With the passage of enough time, anything will eventually crumble as the universe is slowly heading towards a state of ever increasing entropy.

Players in Eve can be an agent of this entropy. Given enough time you can gain trust within your corporation, perhaps even eventually becoming an identifiable leader figure. If you choose to become involved at a higher level, you could propel yourself into alliance politics. Over time you could leverage your alliance clout to move into a leadership role and thus gain valuable privileges at the alliance level. Access to information, privileges to disband, and access to assets and money could all be yours.

Perhaps you will use that type power to hone the military might of your alliance into a campaign of territory conquest, or perhaps your goal is to engage in a take-the-money-and-run scam.

We’ve seen both scenarios play out and as shown in the Causality video, major events can be enacted by just one person. Large, influential alliance have formed out of nothing — TEST, while major contenders have declined and reformed again and again — BoB to KenZoku to IT to Raiden.


All Good Things…

Things ebb; Delve Dysprosium and Northern Technetium fortresses have fallen and changed hands. When I started playing Eve in 2008 people were using plate sniper Megathrons to wage war in nullsec and BoB was king; nothing could topple them. If you follow Eve politics, take a second to reflect on what has changed.

Visualizing the change of power can be done through the membership growth and decline rates of major alliances.

In order to graph alliance changes, I needed access to a lot of historical data regarding member counts. The alliance API only returns the current statistics so I reached out to a high profile member of the Eve community for assistance.

A special thanks to Wollari at Dotlan for providing me with the historical data for analysis. This post would not be possible without his data, which he so generously reproduced a few times after corrections to my source query were needed.


The Data

  1. Ranges from 2007-06-26 to 2011-09-15.
  2. Contains alliances that were in the Dotlan top 100 list on 2011-09-15.
  3. Added BoB, KenZoku, and IT Alliances to the query since they are historically significant.
  4. Anomaly on 2009-10-28 where the majority of member counts were inaccurate. The data points for this day has been removed.
  5. 87,543 rows of membership information for 114 alliances.


tl;dr Charts

Grown and decline of the Northern Coalition next to the rapid growth of the Drone Russian Federation over the past year.


Combined numbers for the NC and Deklein Coalitions.


On 20010-02-03 GoonSwarm (OHGOD) forgot to pay soverignty bills and lost all of their claimed nullsec space. They have since reformed under Goonswarm Federation (CONDI). Test is still smaller than the current incarnation of the Goons.

The above stacked graph shows the Big 10 (12) alliances over time. It looks as if there were only a few major powers back in 2007. Note that this shows how the current big 10 have changed and does not show who was the big 10 over time.

BoB transitioning into KenZoku seems to be an almost one to one member switchover while the formation of IT Alliance brought in a lot of new member corporations.

The fall of IT Alliance seems to have fractured the group. Corporations that were in IT Alliances are now in Black Star, Cartel, Nulli Tertius, Executive Outcomes, Northern Coalition., AAA, Razor, Cascade Imminent, and the Initiative. Only 33.2% of the members moved into Raiden.

Sev3rance is an interesting entity as they have been moving around a lot. Historically they have been the guardians to one of the entrance routes (KBP7-G) to Providence. Having lost their constellation systems in 2010-03 to Ushra’Khan, they found a home in the NC in Pure Blind and Cloud Ring only to move back to Providence in 2011-07.

Messy stacked graph of all 114 alliances in my data set. It does look like there is definite stagnation in nullsec in 2011.

If you would like to see additional graphs or a separate post on how I transformed the data set, please let me know. I only created a few graphs that I thought would be interesting for the vast majority of the Eve audience. Additionally, my nullsec political knowledge may be lacking and I am open to corrections. Again thanks to Wollari for letting me work with his data.

2 Year Anniversary Wormhole Prize Hunt Giveaway Bonanza

Posted: 2011-08-31 | Author: | Filed under: eveonline, wormhole | Tags: , | 20 Comments

Two years ago a corpmate responded to my boredom comments in corp chat with an offer to come join him in the new frontier known as Wormhole space.  I dove in headfirst with nothing to my name but a Covetor, a Dominix, and some 50 M in ISK.

At this time there were a lot of mechanics and logistical challenges to be figured out. We started this blog to help share some of our discoveries, shenanigans, and encounters in the unknown land of Wormhole space with the Eve community. Though we are currently not active in Wormhole space, I still keep the original mission of openly sharing information and experiences with the community alive.

So cheers to you, the Eve community, for being readers of our discourse. We would like to offer up prizes to everyone that has helped us stay passionately involved over the past two years.

As in true k162space fashion, we’re not going to simply give away a prize to the best comment, worst fit, or hold a lottery (thanks to CCP_OhWell for the wonderful suggestion).

Anchored in J124654, J212203, and J235219 around a planet is a can with a Bookmark. Comment on this post with the Bookmark codename as the comment will serve as your timestamp. Prizes will be given out based on discovery time.

1st Prize – PLEX [update] Found Sept 9 @ 17:01
2nd Prize – Pirate Frigate of your choice with T1 rigs and T2 modules
3rd Prize – Sisters Scanning Set

Fire up the scanning probes and get hunting!

Again, thanks everyone for the experiences so far and cheers to another two years!

Edit from miningzen: The cans are at 0 on planet III in all three systems and are called “k162space.com”, no password.

Capital Industry Calendar

Posted: 2011-08-30 | Author: | Filed under: industry | Tags: , , , , | 8 Comments

The industry posts at Eve fail have inspired me to start producing Capitals and this has spawned some new requirements out of our Wallet Manager program.

Problem: Expensive Capital BPOs that are idle mean that they are not producing profitable items

Solution: Use the Corporation Industry Jobs API to display a visual aid of capital job production progress as to easily tell when BPOs will be idle

As always, James and I start off with a quick sketch on paper of what we think the final layout should look like. After some scribbling, debates about tables/CSS/javascript, and layout design we settled on a design.

Here is a shot of our paper sketch.

My knowledge of javascript is rather limited and James is usually the one spearheading new and innovative solutions when I present a design challenges.

Notes from James:

Blake often comes to me with a “I need this thing. Can you make this thing?”, to which I often pause and perform what I like to call shriveled programmer stare. This maneuver involves me scrunching my face as much as possible, raising one eyebrow, and giving a bit of a pained look. I’m quite sure I gave this look when the idea was first proposed. The basic idea actually is just a Gantt chart. I have x number of capital ships in production at any given time and I would like to know exactly, at a quick glance, when they come out of the oven so the next batch of parts will be ready. Simple enough, on paper. The real question is implementation.

A first idea was to use a table, and just change the background color if the job is still running on that day. However, we decided we would like to have the chart auto-scale, so that it would always display far enough out to have the end of the longest job on the chart. Doing this as a pure table would have been inelegant at best, with quite possibly a ludicrous amount of table columns needed for very long jobs. Instead, I took a more difficult, but ultimately more elegant approach; use a single row per job and utilize the margin-left and width css properties to control the positioning and length of each bar.

This, however, required that we have non-table-based vertical lines to delineate our days. For this I used Raphael. Raphael is a nifty little SVG canvas library with decent documentation and a good community base.


//Initialize our variables
var width = $this.width();
var height = $this.children('.industry-job-calendar-table').height();
var offset = globals.headerOffset;

var canvasDiv = $this.children('.industry-job-calendar-table').children('.industry-job-calendar-canvas')[0];

//Create the canvas
var paper = Raphael(canvasDiv, width, height + offset);

A lot of work went into getting our start and end dates, and anyone who’s worked with the JS Date object knows how painful operating in the non-native timezone (even if it is UTC) can be. After our fancy date math, we set the job bar properties, draw the canvas object behind the table rows, attach our tooltip hover() events to the bars, then do a pretty animation of the bars to their final widths.

All in all, a nice little idea that came in at around 325 lines of JS and 20 lines of PHP. And to use it is quite simple:

$('#industry-calendar').phdIndustryCalendar({url: './getCapitalJobs.php'});

And the final result is a clean looking display of our Capital production line.

Wallet Manager Security Issues

Posted: 2011-08-26 | Author: | Filed under: industry | Tags: , , , | 8 Comments

If you have been following this blog for a while, you know that my corpmate James and I have been working on a Wallet Manager site to help manage our Eve ventures. Over time it has grown into our all-encompassing-project-management-thing which now has a trading, manufacturing, invention, and cost analysis sections.

I wanted to disclose why this darn thing is not open to the public as the majority of the feedback that I have been hearing has been, “awesome, now when can I use it!?”

We have not made the site public because of security issues, specifically due to the numerous SQL injection abilities in our code.

Here is a common function that we use that takes the typeID of an item and returns its name. We use this so when we display a Cap Recharger II for example, you can see the name of the item and not just the ‘2032’ number identifier that is easier to work with from a programmability standpoint.

This PHP function retrieves the item name from an input of its typeID.

public function getName($typeID)
{
$sql = ‘SELECT typeName FROM invTypes WHERE typeID = ‘.$typeID.’‘;
$connection=Yii::app()->db;
$command=$connection->createCommand($sql);

//Run the query
$results = $command->query();
$itemName = $results->read();

return $itemName[‘typeName’];
}

The database query is highlighted in green and the terrible part has been highlighted in red.

What you are seeing is a database query that is fed a non-sanitized input. Good programmers will take the $typeID variable and sanitize it before putting it into the SQL query. A common check is to limit the variable to only have characters such as A-Z and 1-3 characters. This check will not allow any special characters such as  : ; ‘ ” $ that are used for SQL operations to be allowed in the query.

With our current function with the unsanitized input variable, you can plug in all sort of things into the query. You could inject code in place of the variable to read, drop, and modify the database,  something we obviously don’t want happening.

Sadly around half of our function were written in this fashion in order to get the pages up and working. Because it has been an internal project, the focus has been on the aesthetic result and not the security of the code behind it. If we were to release it to the public we would have to go over each function and check to make sure that it is secure.

Let me quote CCP and say Soon(tm) for the release.

← Older Entries
Newer Entries →