Updated schema

JonnyD · Jan 25, 2014 · 32fe978 · 32fe978
1 parent b819254
commit 32fe978
Showing 1 changed file with 22 additions and 16 deletions.
diff --git a/README.md b/README.md
@@ -18,26 +18,29 @@ A Minecraft bot for logging player activity on multiplayer servers.
 CREATE DATABASE IF NOT EXISTS `skynet`;
 USE `skynet`;
 
-CREATE TABLE IF NOT EXISTS `event_type` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `name` varchar(32) NOT NULL,
-  PRIMARY KEY (`id`)
-);
-
 CREATE TABLE IF NOT EXISTS `player` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(32) NOT NULL,
-  `last_login` timestamp,
-  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `timestamp` timestamp NOT NULL DEFAULT NULL,
+  `last_login` timestamp NOT NULL DEFAULT NULL,
+  `last_logout` timestamp NOT NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
-  UNIQUE KEY `username` (`username`)
+  UNIQUE KEY `username` (`username`),
+  UNIQUE KEY `unique_username` (`username`),
+  KEY `index_username` (`username`)
+);
+
+CREATE TABLE IF NOT EXISTS `event_type` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(32) NOT NULL,
+  PRIMARY KEY (`id`)
 );
 
 CREATE TABLE IF NOT EXISTS `event` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
-  `event_type_id` int(4) NOT NULL,
+  `event_type_id` int(11) NOT NULL,
   `player_id` int(32) NOT NULL,
-  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `timestamp` timestamp NOT NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_event_type` (`event_type_id`),
   KEY `fk_event_player` (`player_id`)
@@ -51,20 +54,23 @@ CREATE TABLE IF NOT EXISTS `session` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `player_id` int(11) NOT NULL,
   `login` int(11) NOT NULL,
+  `login_timestamp` timestamp NULL DEFAULT NULL,
   `logout` int(11) DEFAULT NULL,
-  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `duration` int(50) NOT NULL,
+  `logout_timestamp` timestamp NULL DEFAULT NULL,
+  `duration` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
-  KEY `fk_login_event` (`login`),
-  KEY `fk_logout_event` (`logout`),
-  KEY `fk_session_player` (`player_id`)
+  UNIQUE KEY `unique_login` (`login`),
+  UNIQUE KEY `unique_logout` (`logout`),
+  KEY `index_player` (`player_id`)
 );
 
 ALTER TABLE `session`
   ADD CONSTRAINT `fk_login_event` FOREIGN KEY (`login`) REFERENCES `event` (`id`),
   ADD CONSTRAINT `fk_logout_event` FOREIGN KEY (`logout`) REFERENCES `event` (`id`),
   ADD CONSTRAINT `fk_session_player` FOREIGN KEY (`player_id`) REFERENCES `player` (`id`);
+
 ```
+
 * Update Skynet.js with your own MySQL connection settings:
 
 ```js