From 224f66a77aa3afa5e06daec656ae97f6e31e0592 Mon Sep 17 00:00:00 2001 From: raylu Date: Fri, 3 May 2013 03:41:50 -0700 Subject: start over --- db/schema.sql | 59 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 59 insertions(+) create mode 100644 db/schema.sql (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql new file mode 100644 index 0000000..7c601ce --- /dev/null +++ b/db/schema.sql @@ -0,0 +1,59 @@ +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_bin; + +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; + +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_bin; + +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_bin; + +CREATE TABLE `kill_costs` ( + `kill_id` int unsigned NOT NULL UNIQUE, + `cost` bigint unsigned NOT NULL, + PRIMARY KEY (`kill_id`), + CONSTRAINT `fk_kill_cost_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- cgit v1.2.3