From b3d6d3889390843374fe0b9184dd77c5fcb84e8b Mon Sep 17 00:00:00 2001
From: Andreas Habel <mail@exceptionfault.de>
Date: Mon, 1 Dec 2008 18:27:14 +0100
Subject: Added new table to store online users. See mantis #553

This upgrade will be the first, we provide database installation scripts
and update scripts to upgrade from the previous version. For more details
about database upgrades see
http://wiki.themanaworld.org/index.php/Upgrade_Database and feel free to
comment.
---
 src/sql/mysql/createTables.sql | 37 ++++++++++++++++++++++++++++++++++---
 1 file changed, 34 insertions(+), 3 deletions(-)

(limited to 'src/sql/mysql/createTables.sql')

diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
index 9eda05a..5d41209 100644
--- a/src/sql/mysql/createTables.sql
+++ b/src/sql/mysql/createTables.sql
@@ -156,9 +156,9 @@ CREATE TABLE IF NOT EXISTS `tmw_world_states` (
 ) ENGINE=InnoDB
 DEFAULT CHARSET=utf8;
 
-INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,1226042339);
-INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,1226042339);
-INSERT INTO tmw_world_states VALUES('database_version',     NULL,'1', 1226042339);
+INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL,UNIX_TIMESTAMP());
+INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL,UNIX_TIMESTAMP());
+INSERT INTO tmw_world_states VALUES('database_version',     NULL,'2', UNIX_TIMESTAMP());
 
 --
 -- table: `tmw_guilds`
@@ -286,6 +286,7 @@ AUTO_INCREMENT=1 ;
 --
 -- table: `tmw_post_attachements`
 --
+
 CREATE TABLE IF NOT EXISTS `tmw_post_attachments` (
 	`attachment_id`    int(10)      unsigned  NOT NULL auto_increment,
 	`letter_id`        int(10)      unsigned  NOT NULL,
@@ -305,3 +306,33 @@ CREATE TABLE IF NOT EXISTS `tmw_post_attachments` (
 DEFAULT CHARSET=utf8
 AUTO_INCREMENT=1 ;
 
+--
+-- table: `tmw_online_list`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_online_list` (
+    `char_id`      int(10)      unsigned NOT NULL,
+    `login_date`   int(10)      NOT NULL,
+    --
+    PRIMARY KEY (`char_id`),
+    FOREIGN KEY (`char_id`) 
+    	REFERENCES `tmw_characters` (`id`)
+    	ON DELETE CASCADE
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8 ;
+
+-- create a view to show more details about online users
+CREATE VIEW tmw_v_online_chars
+AS
+   SELECT l.char_id    as char_id,
+          l.login_date as login_date,
+          c.user_id    as user_id,
+          c.name       as name,
+          c.gender     as gender,
+          c.level      as level,
+          c.map_id     as map_id
+     FROM tmw_online_list l
+     JOIN tmw_characters c
+       ON l.char_id = c.id;
+
+ 
\ No newline at end of file
-- 
cgit 

