schema.sql

CREATE TABLE `sc_players` ( 
	`id` bigint(20), 
	`name` varchar(16) NOT NULL, 
	`leader` tinyint(1) default '0', 
	`tag` varchar(25) NOT NULL, 
	`friendly_fire` tinyint(1) default '0', 
	`neutral_kills` int(11) default NULL, 
	`rival_kills` int(11) default NULL, 
	`civilian_kills` int(11) default NULL, 
	`deaths` int(11) default NULL, 
	`last_seen` bigint NOT NULL, 
	`join_date` bigint NOT NULL, 
	`trusted` tinyint(1) default '0', 
	`flags` text NOT NULL, 
	`packed_past_clans` text, 
	uuid VARCHAR( 255 ) DEFAULT NULL, 

	PRIMARY KEY  (`id`), UNIQUE (`name`)
);

CREATE UNIQUE INDEX `uq_player_uuid` ON `sc_players` (`uuid`);

CREATE TABLE `sc_kills` ( 
	`kill_id` bigint(20), 
	`attacker` varchar(16) NOT NULL, 
	`attacker_tag` varchar(16) NOT NULL, 
	`victim` varchar(16) NOT NULL, 
	`victim_tag` varchar(16) NOT NULL, 
	`kill_type` varchar(1) NOT NULL, 
	attacker_uuid VARCHAR( 255 ) DEFAULT NULL, 
	victim_uuid VARCHAR( 255 ) DEFAULT NULL, 

	PRIMARY KEY  (`kill_id`)
);

CREATE TABLE `sc_clans` ( 
	`id` bigint(20), 
	`verified` tinyint(1) default '0', 
	`tag` varchar(25) NOT NULL, 
	`color_tag` varchar(25) NOT NULL, 
	`name` varchar(100) NOT NULL, 
	`friendly_fire` tinyint(1) default '0', 
	`founded` bigint NOT NULL, 
	`last_used` bigint NOT NULL, 
	`packed_allies` text NOT NULL, 
	`packed_rivals` text NOT NULL, 
	`packed_bb` mediumtext NOT NULL, 
	`cape_url` varchar(255) NOT NULL, 
	`flags` text NOT NULL, 
	`balance` double(64,2) default 0.0,  

	PRIMARY KEY  (`id`), UNIQUE (`tag`)
);

timeline.json

[{
	"attacker": "Bluuefuzzy",
	"kill_type": "c",
	"name": "West Road Allance",
	"victim": "C45Y"
}, {
	"attacker": "Bluuefuzzy",
	"kill_type": "c",
	"name": "West Road Allance",
	"victim": "ProbablyNotC45Y"
}, {
	"attacker": "Bluuefuzzy",
	"kill_type": "n",
	"name": "West Road Allance",
	"victim": "C45Y"
}, {
	"attacker": "Bluuefuzzy",
	"kill_type": "n",
	"name": "West Road Allance",
	"victim": "C45Y"
}, {
	"attacker": "C45Y",
	"kill_type": "c",
	"name": "Team Stooge",
	"victim": "ProbablyNotC45Y"
}, [
	"SELECT                                                          ",
	"    sk.attacker,                                                ",
	"    sk.kill_type,                                               ",
	"    sc.name,                                                    ",
	"    sk.victim                                                   ",
	"FROM sc_kills as sk                                             ",
	"    INNER JOIN sc_players as sp ON sk.attacker_uuid = sp.uuid   ",
	"    INNER JOIN sc_clans as sc ON sp.tag = sc.tag                "
]]