* 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

5 files changed, 391 insertions, 0 deletions

diff --git a/src/sql/mysql/createDatabase.sql b/src/sql/mysql/createDatabase.sql
new file mode 100644
index 00000000..c40aa246
--- /dev/null
+++ b/src/sql/mysql/createDatabase.sql
@@ -0,0 +1,34 @@
+/*
+ *  The Mana World Server
+ *  Copyright 2008 The Mana World Development Team
+ *
+ *  This file is part of The Mana World.
+ *
+ *  The Mana World  is free software; you can redistribute  it and/or modify it
+ *  under the terms of the GNU General  Public License as published by the Free
+ *  Software Foundation; either version 2 of the License, or any later version.
+ *
+ *  The Mana  World is  distributed in  the hope  that it  will be  useful, but
+ *  WITHOUT ANY WARRANTY; without even  the implied warranty of MERCHANTABILITY
+ *  or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
+ *  more details.
+ *
+ *  You should  have received a  copy of the  GNU General Public  License along
+ *  with The Mana  World; if not, write to the  Free Software Foundation, Inc.,
+ *  59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+ *
+ *  $Id$
+ */
+
+CREATE USER 'tmw'@'%' IDENTIFIED BY 'testtest';
+CREATE USER 'tmw'@'localhost' IDENTIFIED BY 'testtest';
+
+GRANT USAGE ON * . * TO 'tmw'@'%' IDENTIFIED BY 'testtest' 
+	WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
+GRANT USAGE ON * . * TO 'tmw'@'localhost' IDENTIFIED BY 'testtest' 
+	WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
+
+CREATE DATABASE IF NOT EXISTS `tmw` ;
+
+GRANT ALL PRIVILEGES ON `tmw` . * TO 'tmw'@'%';
+GRANT ALL PRIVILEGES ON `tmw` . * TO 'tmw'@'localhost';
diff --git a/src/sql/mysql/createTables.sql b/src/sql/mysql/createTables.sql
new file mode 100644
index 00000000..3bcf665e
--- /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;
diff --git a/src/sql/postgresql/createTables.sql b/src/sql/postgresql/createTables.sql
new file mode 100644
index 00000000..0d95f384
--- /dev/null
+++ b/src/sql/postgresql/createTables.sql
@@ -0,0 +1,102 @@
+CREATE TABLE tmw_accounts 
+( 
+   id           SERIAL      PRIMARY KEY, 
+   username     TEXT        NOT NULL UNIQUE, 
+   password     TEXT        NOT NULL, 
+   email        TEXT        NOT NULL, 
+   level        SMALLINT    NOT NULL, 
+   banned       SMALLINT    NOT NULL, 
+   registration INTEGER     NOT NULL, 
+   lastlogin    INTEGER     NOT NULL  
+);
+
+CREATE INDEX tmw_accounts_username ON tmw_accounts ( username );
+
+
+CREATE TABLE tmw_characters 
+(
+   id           SERIAL      PRIMARY KEY,
+   user_id      INTEGER     NOT NULL,
+   name         TEXT        NOT NULL UNIQUE,
+   gender       SMALLINT    NOT NULL,
+   hair_style   SMALLINT    NOT NULL,
+   hair_color   INTEGER     NOT NULL,
+   level        INTEGER     NOT NULL,
+   char_pts     INTEGER     NOT NULL,
+   correct_pts  INTEGER     NOT NULL,
+   money        INTEGER     NOT NULL,
+   x            SMALLINT    NOT NULL,
+   y            SMALLINT    NOT NULL,
+   map_id       SMALLINT    NOT NULL,
+   str          SMALLINT    NOT NULL,
+   agi          SMALLINT    NOT NULL,
+   dex          SMALLINT    NOT NULL,
+   vit          SMALLINT    NOT NULL,
+   int          SMALLINT    NOT NULL,
+   will         SMALLINT    NOT NULL,
+   unarmed_exp  INTEGER     NOT NULL,
+   knife_exp    INTEGER     NOT NULL,
+   sword_exp    INTEGER     NOT NULL,
+   polearm_exp  INTEGER     NOT NULL,
+   staff_exp    INTEGER     NOT NULL,
+   whip_exp     INTEGER     NOT NULL,
+   bow_exp      INTEGER     NOT NULL,
+   shoot_exp    INTEGER     NOT NULL,
+   mace_exp     INTEGER     NOT NULL,
+   axe_exp      INTEGER     NOT NULL,
+   thrown_exp   INTEGER     NOT NULL,
+   --
+   FOREIGN KEY (user_id) REFERENCES tmw_accounts(id)
+);
+
+CREATE TABLE tmw_inventories 
+(
+   id           SERIAL      PRIMARY KEY,
+   owner_id     INTEGER     NOT NULL,
+   slot         SMALLINT    NOT NULL,
+   class_id     INTEGER     NOT NULL,
+   amount       SMALLINT    NOT NULL,
+   --
+   FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
+);
+
+CREATE TABLE tmw_guilds 
+(
+   id           SERIAL      PRIMARY KEY,
+   name 		TEXT        NOT NULL UNIQUE
+);
+
+CREATE TABLE tmw_guild_members 
+(
+   guild_id     INTEGER     NOT NULL,
+   member_id    INTEGER     NOT NULL,
+   rights       INTEGER     NOT NULL,
+   --
+   FOREIGN KEY (guild_id)  REFERENCES tmw_guilds(id),
+   FOREIGN KEY (member_id) REFERENCES tmw_characters(id)
+);
+
+CREATE INDEX tmw_guild_members_g ON tmw_guild_members ( guild_id );
+CREATE INDEX tmw_guild_members_m ON tmw_guild_members ( member_id );
+
+CREATE TABLE tmw_quests 
+(
+   owner_id     INTEGER     NOT NULL,
+   name         TEXT        NOT NULL,
+   value        TEXT        NOT NULL,
+   --
+   FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
+);
+
+CREATE TABLE tmw_world_states 
+( 
+   state_name   TEXT        PRIMARY KEY, 
+   map_id       INTEGER     NULL, 
+   value        TEXT        NULL, 
+   moddate      INTEGER     NOT NULL 
+);
+
+INSERT INTO "tmw_world_states" VALUES('accountserver_startup',NULL,NULL,1221633910);
+INSERT INTO "tmw_world_states" VALUES('accountserver_version',NULL,NULL,1221633910);
+
+
diff --git a/src/sql/sqlite/createTables.sql b/src/sql/sqlite/createTables.sql
new file mode 100644
index 00000000..4ff336bb
--- /dev/null
+++ b/src/sql/sqlite/createTables.sql
@@ -0,0 +1,102 @@
+CREATE TABLE tmw_accounts 
+( 
+   id           INTEGER     PRIMARY KEY, 
+   username     TEXT        NOT NULL UNIQUE, 
+   password     TEXT        NOT NULL, 
+   email        TEXT        NOT NULL, 
+   level        INTEGER     NOT NULL, 
+   banned       INTEGER     NOT NULL, 
+   registration INTEGER     NOT NULL, 
+   lastlogin    INTEGER     NOT NULL  
+);
+
+CREATE INDEX tmw_accounts_username ON tmw_accounts ( username );
+
+
+CREATE TABLE tmw_characters 
+(
+   id           INTEGER     PRIMARY KEY,
+   user_id      INTEGER     NOT NULL,
+   name         TEXT        NOT NULL UNIQUE,
+   gender       INTEGER     NOT NULL,
+   hair_style   INTEGER     NOT NULL,
+   hair_color   INTEGER     NOT NULL,
+   level        INTEGER     NOT NULL,
+   char_pts     INTEGER     NOT NULL,
+   correct_pts  INTEGER     NOT NULL,
+   money        INTEGER     NOT NULL,
+   x            INTEGER     NOT NULL,
+   y            INTEGER     NOT NULL,
+   map_id       INTEGER     NOT NULL,
+   str          INTEGER     NOT NULL,
+   agi          INTEGER     NOT NULL,
+   dex          INTEGER     NOT NULL,
+   vit          INTEGER     NOT NULL,
+   int          INTEGER     NOT NULL,
+   will         INTEGER     NOT NULL,
+   unarmed_exp  INTEGER     NOT NULL,
+   knife_exp    INTEGER     NOT NULL,
+   sword_exp    INTEGER     NOT NULL,
+   polearm_exp  INTEGER     NOT NULL,
+   staff_exp    INTEGER     NOT NULL,
+   whip_exp     INTEGER     NOT NULL,
+   bow_exp      INTEGER     NOT NULL,
+   shoot_exp    INTEGER     NOT NULL,
+   mace_exp     INTEGER     NOT NULL,
+   axe_exp      INTEGER     NOT NULL,
+   thrown_exp   INTEGER     NOT NULL,
+   --
+   FOREIGN KEY (user_id) REFERENCES tmw_accounts(id)
+);
+
+CREATE TABLE tmw_inventories 
+(
+   id           INTEGER     PRIMARY KEY,
+   owner_id     INTEGER     NOT NULL,
+   slot         INTEGER     NOT NULL,
+   class_id     INTEGER     NOT NULL,
+   amount       INTEGER     NOT NULL,
+   --
+   FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
+);
+
+CREATE TABLE tmw_guilds 
+(
+   id           INTEGER     PRIMARY KEY,
+   name 		TEXT        NOT NULL UNIQUE
+);
+
+CREATE TABLE tmw_guild_members 
+(
+   guild_id     INTEGER     NOT NULL,
+   member_id    INTEGER     NOT NULL,
+   rights       INTEGER     NOT NULL,
+   --
+   FOREIGN KEY (guild_id)  REFERENCES tmw_guilds(id),
+   FOREIGN KEY (member_id) REFERENCES tmw_characters(id)
+);
+
+CREATE INDEX tmw_guild_members_g ON tmw_guild_members ( guild_id );
+CREATE INDEX tmw_guild_members_m ON tmw_guild_members ( member_id );
+
+CREATE TABLE tmw_quests 
+(
+   owner_id     INTEGER     NOT NULL,
+   name         TEXT        NOT NULL,
+   value        TEXT        NOT NULL,
+   --
+   FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)
+);
+
+CREATE TABLE tmw_world_states 
+( 
+   state_name   TEXT        PRIMARY KEY, 
+   map_id       INTEGER     NULL, 
+   value        TEXT        NULL, 
+   moddate      INTEGER     NOT NULL 
+);
+
+INSERT INTO "tmw_world_states" VALUES('accountserver_startup',NULL,NULL,1221633910);
+INSERT INTO "tmw_world_states" VALUES('accountserver_version',NULL,NULL,1221633910);
+
+
diff --git a/src/sql/sqlite/tmw.db b/src/sql/sqlite/tmw.db
new file mode 100644
index 00000000..d212f5a5
--- /dev/null
+++ b/src/sql/sqlite/tmw.db