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.

1 files changed, 30 insertions, 0 deletions

diff --git a/src/sql/sqlite/updates/update_1_to_2.sql b/src/sql/sqlite/updates/update_1_to_2.sql
new file mode 100644
index 0000000..8187cd3
--- /dev/null
+++ b/src/sql/sqlite/updates/update_1_to_2.sql
@@ -0,0 +1,30 @@
+
+-- add table tmw_online_list to store online users
+CREATE TABLE tmw_online_list
+(
+    char_id     INTEGER     PRIMARY KEY,
+    login_date  INTEGER     NOT NULL,
+    --
+    FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
+);
+
+-- 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;
+
+-- update the database version, and set date of update
+UPDATE tmw_world_states 
+   SET value      = '2',
+       moddate    = strftime('%s','now')
+ WHERE state_name = 'database_version';
+ 
\ No newline at end of file