From 54217640c85a97341df7395a9acc39a0b819692d Mon Sep 17 00:00:00 2001
From: Chuck Miller <shadowmil@gmail.com>
Date: Wed, 30 Sep 2009 00:10:58 -0400
Subject: Adds tmw_char_status_effects tables to mySQL and SQLite

---
 src/account-server/dalstorage.cpp        |  2 +-
 src/account-server/dalstoragesql.hpp     |  5 +++++
 src/sql/mysql/createTables.sql           | 20 +++++++++++++++++++-
 src/sql/sqlite/createTables.sql          | 15 ++++++++++++++-
 src/sql/sqlite/updates/update_5_to_6.sql | 20 ++++++++++++++++++++
 5 files changed, 59 insertions(+), 3 deletions(-)
 create mode 100644 src/sql/sqlite/updates/update_5_to_6.sql

(limited to 'src')

diff --git a/src/account-server/dalstorage.cpp b/src/account-server/dalstorage.cpp
index 7e27ae50..4628eca5 100644
--- a/src/account-server/dalstorage.cpp
+++ b/src/account-server/dalstorage.cpp
@@ -41,7 +41,7 @@
 
 // defines the supported db version
 #define DB_VERSION_PARAMETER "database_version"
-#define SUPPORTED_DB_VERSION "5"
+#define SUPPORTED_DB_VERSION "6"
 
 
 /**
diff --git a/src/account-server/dalstoragesql.hpp b/src/account-server/dalstoragesql.hpp
index 1661baa6..cab3aea9 100644
--- a/src/account-server/dalstoragesql.hpp
+++ b/src/account-server/dalstoragesql.hpp
@@ -75,6 +75,11 @@ static const char *CHARACTERS_TBL_NAME = "tmw_characters";
  */
 static const char *CHAR_SKILLS_TBL_NAME = "tmw_char_skills";
 
+/**
+ * TABLE: tmw_char_status_effects.
+ */
+static const char *CHAR_STATUS_EFFECTS_TBL_NAME = "tmw_char_status_effects";
+
 /**
  * TABLE: tmw_inventories.
  */
diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
index 306e7c0a..9d6a7385 100644
--- a/src/sql/mysql/createTables.sql
+++ b/src/sql/mysql/createTables.sql
@@ -72,6 +72,24 @@ CREATE TABLE IF NOT EXISTS `tmw_char_skills` (
 ) ENGINE=InnoDB
 DEFAULT CHARSET=utf8;
 
+--
+-- table: `tmw_char_status_effects`
+--
+CREATE TABLE IF NOT EXISTS `tmw_char_status_effects` (
+    `char_id`       int(10)         unsigned NOT NULL,
+    `status_id`     smallint(5)     unsigned NOT NULL,
+    `status_time`   int(10)         signed NOT NULL,
+    --
+    PRIMARY KEY (`char_id`, `status_id`),
+    FOREIGN KEY (`char_id`)
+        REFERENCES `tmw_characters` (`id`)
+        ON DELETE CASCADE
+) ENGING=InnoDB
+DEFAULT CHARSET=utf8;
+
+
+
+
 --
 -- table: `tmw_items`
 --
@@ -344,4 +362,4 @@ CREATE TABLE IF NOT EXISTS `tmw_transactions` (
   `time` int(11) NOT NULL,
   PRIMARY KEY  (`id`)
 )
-AUTO_INCREMENT=0 ;
\ No newline at end of file
+AUTO_INCREMENT=0 ;
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
index 5cd439d2..d71858fa 100644
--- a/src/sql/sqlite/createTables.sql
+++ b/src/sql/sqlite/createTables.sql
@@ -84,6 +84,19 @@ CREATE INDEX tmw_char_skills_char ON tmw_char_skills ( char_id );
 
 -----------------------------------------------------------------------------
 
+CREATE TABLE tmw_char_status_effects
+(
+    char_id     INTEGER     NOT NULL,
+    status_id   INTEGER     NOT NULL,
+    status_time INTEGER     NOT NULL,
+    --
+    FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
+);
+
+CREATE INDEX tmw_char_status_char on tmw_char_status_effects ( char_id );
+
+-----------------------------------------------------------------------------
+
 CREATE TABLE tmw_items
 (
     id           INTEGER    PRIMARY KEY,
@@ -348,7 +361,7 @@ AS
 
 INSERT INTO tmw_world_states VALUES('accountserver_startup',NULL,NULL, strftime('%s','now'));
 INSERT INTO tmw_world_states VALUES('accountserver_version',NULL,NULL, strftime('%s','now'));
-INSERT INTO tmw_world_states VALUES('database_version',     NULL,'5',  strftime('%s','now'));
+INSERT INTO tmw_world_states VALUES('database_version',     NULL,'6',  strftime('%s','now'));
 
 -- all known transaction codes
 
diff --git a/src/sql/sqlite/updates/update_5_to_6.sql b/src/sql/sqlite/updates/update_5_to_6.sql
new file mode 100644
index 00000000..8a1e552c
--- /dev/null
+++ b/src/sql/sqlite/updates/update_5_to_6.sql
@@ -0,0 +1,20 @@
+
+-- create table tmw_char_status_effects
+
+CREATE TABLE tmw_char_status_effects
+(
+    char_id     INTEGER     NOT NULL,
+    status_id   INTEGER     NOT NULL,
+    status_time INTEGER     NOT NULL,
+    --
+    FOREIGN KEY (char_id) REFERENCES tmw_characters(id)
+);
+
+CREATE INDEX tmw_char_status_char on tmw_char_status_effects ( char_id );
+
+-- update the database version, and set date of update
+UPDATE tmw_world_states
+   SET value      = '6',
+       moddate    = strftime('%s','now')
+ WHERE state_name = 'database_version';
+
-- 
cgit v1.2.3-70-g09d2