1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
DROP TABLE IF EXISTS `characters`;
DROP TABLE IF EXISTS `items`;
DROP TABLE IF EXISTS `item_costs`;
DROP TABLE IF EXISTS `kill_costs`;
DROP TABLE IF EXISTS `kills`;
CREATE TABLE `kills` (
`kill_id` int unsigned NOT NULL,
`solar_system_id` int NOT NULL,
`kill_time` datetime NOT NULL,
`moon_id` int unsigned NOT NULL,
PRIMARY KEY (`kill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `characters` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`kill_id` int unsigned NOT NULL,
`victim` tinyint(1) NOT NULL,
`character_id` int unsigned NOT NULL,
`character_name` varchar(64) NOT NULL,
`ship_type_id` int NOT NULL,
`alliance_id` int unsigned NOT NULL,
`alliance_name` varchar(64) NOT NULL,
`corporation_id` int unsigned NOT NULL,
`corporation_name` varchar(64) NOT NULL,
`faction_id` int NOT NULL,
`faction_name` varchar(64) NOT NULL,
`damage` int DEFAULT NULL,
`final_blow` tinyint(1) DEFAULT NULL,
`security_status` float DEFAULT NULL,
`weapon_type_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_char_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`),
INDEX `character_id_kill_id` (`character_id`, `kill_id`),
INDEX `alliance_id_kill_id` (`alliance_id`, `kill_id`),
INDEX `corporation_id_kill_id` (`corporation_id`, `kill_id`),
INDEX `character_name` (`character_name`),
INDEX `alliance_name` (`alliance_name`),
INDEX `corporation_name` (`corporation_name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `items` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`kill_id` int unsigned NOT NULL,
`type_id` int NOT NULL,
`flag` tinyint(3) unsigned NOT NULL,
`dropped` int unsigned NOT NULL,
`destroyed` int unsigned NOT NULL,
`singleton` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_item_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `item_costs` (
`type_id` int NOT NULL UNIQUE,
`cost` bigint unsigned NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
CREATE TABLE `kill_costs` (
`kill_id` int unsigned NOT NULL UNIQUE,
`cost` bigint unsigned NOT NULL,
PRIMARY KEY (`kill_id`),
INDEX `cost_kill_id` (`cost`, `kill_id`),
CONSTRAINT `fk_kill_cost_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
|
