From a2af298fd993a129b657671a41f20e3975baf0ef Mon Sep 17 00:00:00 2001
From: Andreas Habel <mail@exceptionfault.de>
Date: Wed, 17 Sep 2008 11:32:45 +0000
Subject: * Added installation scripts to set up database schemas for mysql,
 sqlite and postgresql. The create table statements have been completely
 removed out from the c++ source into separate, provider specific sql files.
 Accountserver will no longer create a sqlite file if none present. * Added
 database specific config parameters to configure each provider independent. *
 Simplified the connect routine of DALStorage class since every dataprovider
 is now responsible to retrieve its own parameters. * Extended abstract
 dataprovider to support transactions, functionally implemented for SQLite and
 mySQL. * Added methods to retrieve last inserted auto-increment value and the
 number of modified rows by the last statement. * Rewrite of DALStorage class
 to be a little more transactional. * Fixed a bug when deleting a character.
 Old function left data in quests table and guilds table. * Doxygen now also
 includes non-documented functions and provides a dictionary for all classes

---
 src/sql/mysql/createTables.sql | 153 +++++++++++++++++++++++++++++++++++++++++
 1 file changed, 153 insertions(+)
 create mode 100644 src/sql/mysql/createTables.sql

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

diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
new file mode 100644
index 0000000..3bcf665
--- /dev/null
+++ b/src/sql/mysql/createTables.sql
@@ -0,0 +1,153 @@
+--
+-- table: `tmw_accounts`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_accounts` (
+    `id`           int(10)      unsigned NOT NULL auto_increment,
+    `username`     varchar(64)           NOT NULL,
+    `password`     varchar(64)           NOT NULL,
+    `email`        varchar(32)           NOT NULL,
+    `level`        tinyint(3)   unsigned NOT NULL,
+    `banned`       int(10)      unsigned NOT NULL,
+    `registration` int(10)      unsigned NOT NULL,
+    `lastlogin`    int(10)      unsigned NOT NULL,
+    --
+    PRIMARY KEY  (`id`),
+    UNIQUE KEY `username` (`username`),
+    UNIQUE KEY `email` (`email`)
+) ENGINE=InnoDB 
+DEFAULT CHARSET=utf8_general_ci 
+AUTO_INCREMENT=1 ;
+
+--
+-- table: `tmw_characters`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_characters` (
+    `id`           int(10)      unsigned NOT NULL auto_increment,
+    `user_id`      int(10)      unsigned NOT NULL,
+	`name`         varchar(32)           NOT NULL,
+    --
+    `gender`       tinyint(3)   unsigned NOT NULL,
+    `hair_style`   tinyint(3)   unsigned NOT NULL,
+    `hair_color`   tinyint(3)   unsigned NOT NULL,
+    `level`        tinyint(3)   unsigned NOT NULL,
+    `char_pts`     smallint(5)  unsigned NOT NULL,
+    `correct_pts`  smallint(5)  unsigned NOT NULL,
+    `money`        int(10)      unsigned NOT NULL,
+    -- location on the map
+    `x`            smallint(5)  unsigned NOT NULL,
+    `y`            smallint(5)  unsigned NOT NULL,
+    `map_id`       tinyint(3)   unsigned NOT NULL,
+    -- attributes
+    `str`          smallint(5)  unsigned NOT NULL,
+    `agi`          smallint(5)  unsigned NOT NULL,
+    `dex`          smallint(5)  unsigned NOT NULL,
+    `vit`          smallint(5)  unsigned NOT NULL,
+    `int`          smallint(5)  unsigned NOT NULL,
+    `will`         smallint(5)  unsigned NOT NULL,
+    -- skill experience
+    `unarmed_exp`  smallint(5)  unsigned NOT NULL,
+    `knife_exp`    smallint(5)  unsigned NOT NULL,
+    `sword_exp`    smallint(5)  unsigned NOT NULL,
+    `polearm_exp`  smallint(5)  unsigned NOT NULL,
+    `staff_exp`    smallint(5)  unsigned NOT NULL,
+    `whip_exp`     smallint(5)  unsigned NOT NULL,
+    `bow_exp`      smallint(5)  unsigned NOT NULL,
+    `shoot_exp`    smallint(5)  unsigned NOT NULL,
+    `mace_exp`     smallint(5)  unsigned NOT NULL,
+    `axe_exp`      smallint(5)  unsigned NOT NULL,
+    `thrown_exp`   smallint(5)  unsigned NOT NULL,
+    --
+    PRIMARY KEY (`id`),
+    UNIQUE KEY `name` (`name`),
+    KEY `user_id` (`user_id`),
+    FOREIGN KEY (`user_id`) 
+    	REFERENCES `tmw_accounts` (`id`)
+    	ON DELETE CASCADE 
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8_general_ci 
+AUTO_INCREMENT=1 ;
+    
+--
+-- table: `tmw_inventories`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_inventories` (
+    `id`           int(10)      unsigned NOT NULL auto_increment,
+    `owner_id`     int(10)      unsigned NOT NULL,
+    `slot`         tinyint(3)   unsigned NOT NULL,
+    `class_id`     int(10)      unsigned NOT NULL,
+    `amount`       tinyint(3)   unsigned NOT NULL,
+    --
+    PRIMARY KEY (`id`),
+    UNIQUE KEY `owner_id` (`owner_id`, `slot`),
+    FOREIGN KEY (`owner_id`)
+    	REFERENCES `tmw_characters` (`id`)
+    	ON DELETE CASCADE 
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8_general_ci 
+AUTO_INCREMENT=1 ;
+
+--
+-- table: `tmw_world_states`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_world_states` (
+    `state_name`   varchar(100)          NOT NULL,
+    `map_id`       int(10)      unsigned default NULL,
+    `value`        varchar(255)          NOT NULL,
+    `moddate`      int(10)      unsigned NOT NULL,
+    --
+    KEY `state_name` (`state_name`)
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8_general_ci;
+
+--
+-- table: `tmw_guilds`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_guilds` (
+    `id`           int(10)      unsigned NOT NULL auto_increment,
+    `name`         varchar(35)           NOT NULL,
+    --
+    PRIMARY KEY (`id`),
+    UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8_general_ci
+AUTO_INCREMENT=1 ;
+
+--
+-- table: `tmw_guild_members`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_guild_members` (
+	`guild_id`     int(10)      unsigned NOT NULL,
+	`member_id`    int(10)      unsigned NOT NULL,
+	`rights`       int(10)      unsigned NOT NULL,
+	--
+	PRIMARY KEY (`guild_id`, `member_id`),
+	FOREIGN KEY (`guild_id`)
+		REFERENCES `tmw_guilds` (`id`)
+		ON DELETE CASCADE,
+	FOREIGN KEY (`member_id`)
+		REFERENCES `tmw_characters` (`id`)
+		ON DELETE CASCADE
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8_general_ci;
+        
+--
+-- table: `tmw_quests`
+--
+
+CREATE TABLE IF NOT EXISTS `tmw_quests` (
+	`owner_id`     int(10)      unsigned NOT NULL,
+	`name`         varchar(100)          NOT NULL,
+	`value`        varchar(200)          NOT NULL,
+	--
+	PRIMARY KEY (`owner_id`, `name`),
+	FOREIGN KEY (`owner_id`)
+		REFERENCES `tmw_characters` (`id`)
+		ON DELETE CASCADE
+) ENGINE=InnoDB
+DEFAULT CHARSET=utf8_general_ci;
-- 
cgit 

