The Goonswarm EveOnline Killboard

1
The Goonswarm Eve-Online Killboard

• K. Alex Schreiner

2
Overview

• What is Eve?
• What is a Killboard?
• Conceptual diagram
• ER Diagram
• Limitations of previous system.
• Performance improvement
• SQL Queries

3
What is Eve Online?

• Eve Online is a Massively Multiplayer Online
  Game.
• The primary focus is on large scale Player versus
  Player combat
• 230,000 players all playing on same server, over
  26k concurrent users

4
What is a Killboard?

• A killboard is a database that tracks all combat
  losses and kills ingame
• Parses the mails sent to each player upon loss or
  kill of any ship and enters that into the
  database
• Web interface for entry, and queries

5
About the mail

• Each Mail Contains
• Location, Time, Security Rating
• Victims Name, Corporation, Alliance, Ship
  Destroyed
• Name, Corporation, Alliance of each pilot
  involved in kill
• Ships and weapon used by each pilot involved in
  kill
• Cargo and equipment destroyed
• Maximum of 20 Pilots per kill, any others will be
  truncated
• Minimum of 1 pilot per kill.

6
Conceptual diagram
Client Authenticates with forums
Forums Server
Forums return user id if authenticated
Web Client
DB returns queries which PHP formats into HTML
Database
User posts Killmail
DB checks authentication
7
Converts this

• 2006.12.04 1455 Victim Vahl Alliance
  GoonSwarm Corp GoonWaffe Destroyed Merlin
  System U2-28D Security 0.0 Involved parties
  Name Agent 6 (laid the final blow) Security
  4.2 Alliance Veritas Immortalis Corp German
  Cyberdome Corp Ship Heretic Weapon Medium
  Pulse Laser II Destroyed items 'Langour'
  Drive Disruptor I Rocket Launcher I Ballistic
  Control System I Small Nosferatu I Rocket
  Launcher I Light Neutron Blaster I Iron Charge
  S, Qty 920 (Cargo) Phalanx Rocket, Qty 22

8
Into this (see at http//killboard.goonfleet.com
)
9
E-R Diagram
10
3NF Model
11
Limitations of previous system

• MySQL 4 does not support views
• MySQL 4 does not support nested queries
• MySQL does not support triggers or stored
  procedures.
• MySQL 4 has unpatched security holes, as we
  discovered last month the hard way.

12
Choice of DBMS

• New DBMS Microsoft SQL Server 2005
• Free for CS Students
• Excellent Administration Tools
• Query Optimization Tools
• Performance and Scalability

13
Performance improvement

• The previous system used text data type to store
  80 of table data.
• Text is a variable string data type with a
  maximum length of 65,535 characters.
• Longest string was 64 characters.
• Text and BLOB data types do not index well, makes
  searches slow
• Changed all primary keys to indexed
  auto-incrementing integers.
• Search performance increased over 200

14
SQL Queries
SELECT kb3_pilots.plt_name, kb3_alliances.all_nam
e FROM kb3_pilots INNER JOIN kb3_corps ON
kb3_pilots.plt_crp_id kb3_corps.crp_id INNER
JOIN kb3_alliances ON kb3_corps.crp_all_id
kb3_alliances.all_id AND kb3_alliances.all_name
LIKE 'Goonswarm' ORDER BY kb3_pilots.plt_name
(2,383 rows total), query time 0.3s
Plt_name all_name
15
More Queries
SELECT kb3_kills.kll_timestamp as 'Date Killed',
victim_plt.plt_name AS Victim, kb3_ships.shp_name
AS 'Ship Lost', killer_plt.plt_name AS
Killer FROM kb3_pilots AS victim_plt INNER JOIN
kb3_kills ON victim_plt.plt_name LIKE 'Gamblor
Jebus' AND victim_plt.plt_id kll_victim_id INNER
JOIN kb3_pilots AS killer_plt ON
killer_plt.plt_id kll_fb_plt_id INNER JOIN
kb3_ships ON kb3_ships.shp_id
kb3_kills.kll_ship_id
Date Killed Victim
Ship Lost Killer
2 rows returned, query time 4s
16
Questions?