Moved SQL queries for the creation of tables into a separate file and rewrote the initialization of the database.

4 files changed, 359 insertions, 112 deletions

diff --git a/src/Makefile.am b/src/Makefile.am
index 7c4d59e8..0efc4646 100644
--- a/src/Makefile.am
+++ b/src/Makefile.am
@@ -42,18 +42,21 @@ tmwserv_SOURCES = main.cpp \
 		  dal/recordset.cpp \
 		  dal/dalexcept.h \
 		  dalstorage.h \
+		  dalstoragesql.h \
 		  dalstorage.cpp
 
 #		  dal/mysqldataprovider.h
 #		  dal/mysqldataprovider.cpp
 
 
-if BUILD_SQLITE
-tmwserv_SOURCES += sqlite/SQLiteWrapper.cpp \
-	           sqlite/SQLiteWrapper.h \
-		   sqlitestorage.cpp \
-		   sqlitestorage.h
-endif
+# Obsoleted by DAL.
+#
+#if BUILD_SQLITE
+#tmwserv_SOURCES += sqlite/SQLiteWrapper.cpp \
+#	           sqlite/SQLiteWrapper.h \
+#		   sqlitestorage.cpp \
+#		   sqlitestorage.h
+#endif
 
 # set the include path found by configure
 INCLUDES= $(all_includes) $(SQLITE_CFLAGS)
diff --git a/src/dalstorage.cpp b/src/dalstorage.cpp
index 268c674f..b41975be 100644
--- a/src/dalstorage.cpp
+++ b/src/dalstorage.cpp
@@ -24,6 +24,7 @@
 #include <sstream>
 
 #include "dalstorage.h"
+#include "dalstoragesql.h"
 
 
 namespace
@@ -64,84 +65,6 @@ struct string_to: public std::unary_function<std::string, T>
 };
 
 
-/* Values for user level could be:
- *  0: Normal user
- *  1: Moderator (has medium level rights)
- *  2: Administrator (can do basically anything)
- */
-const char sqlAccountTable[] =
-  "create table tmw_accounts ("
-    "id        int          unique primary key not null,"
-    "username  varchar(32)  not null,"
-    "password  varchar(32)  not null,"
-    "email     varchar(128) not null,"
-    "level     int          not null,"  // User level (normal, admin, etc.)
-    "banned    int          not null"   // The UNIX time of unban (0 default)
-  ");";
-
-/* Note: The stats will need to be thought over, as we'll be implementing a
- *  much more elaborate skill based system. We should probably have a separate
- *  table for storing the skill levels.
- *
- * Gender is 0 for male, 1 for female.
- */
-const char sqlCharacterTable[] =
-  "create table tmw_characters ("
-    "id        int          unique primary key not null,"
-    "user_id   int          not null,"
-    "name      varchar(32)  not null,"
-    "gender    int          not null,"  // Player information
-    "level     int          not null,"
-    "money     int          not null,"
-    "x         int          not null,"  // Location
-    "y         int          not null,"
-    "map       text         not null,"
-    "str       int          not null,"  // Stats
-    "agi       int          not null,"
-    "vit       int          not null,"
-    "int       int          not null,"
-    "dex       int          not null,"
-    "luck      int          not null,"
-    "foreign key(user_id) references tmw_accounts(id)"
-  ");";
-
-/*
- * All items in the game world are stored in this table.
- */
-const char sqlItemTable[] =
-  "create table tmw_items ("
-    "id        int          unique primary key not null,"
-    "amount    int          not null,"  // Items of same kind can stack
-    "type      int          not null,"  // Type as defined in item database
-    "state     text"                    // Optional item state saved by script
-  ");";
-
-/*
- * Items on the ground in the game world.
- */
-const char sqlWorldItemTable[] =
-  "create table tmw_world_items ("
-    "id        int          not null,"
-    "map       text,"
-    "x         int          not null,"  // Location of item on map
-    "y         int          not null,"
-    "deathtime int          not null,"  // Time to die (UNIX time)
-    "primary key(id, map),"
-    "foreign key(id) references tmw_items(id)"
-  ");";
-
-/*
- * Character Inventory
- */
-const char sqlInventoryTable[] =
-  "create table tmw_inventory ("
-    "id        int          primary key not null," // Item ID
-    "owner_id  int          not null," // Owner character ID
-    "foreign key(id) references tmw_items(id),"
-    "foreign key(owner_id) references tmw_characters(id)"
-  ");";
-
-
 } // anonymous namespace
 
 
@@ -209,15 +132,16 @@ DALStorage::getAccountCount(void)
 
     try {
         // query the database.
-        const std::string sql = "select count(*) from tmw_accounts;";
+        std::string sql("select count(*) from ");
+        sql += ACCOUNTS_TBL_NAME;
+        sql += ";";
         const RecordSet& rs = mDb->execSql(sql);
 
-        // convert the result into a number.
-        std::istringstream s(rs(0, 0));
-        unsigned int value;
-        s >> value;
+        // specialize the string_to functor to convert
+        // a string to an unsigned int.
+        string_to<unsigned int> toUint;
 
-        return value;
+        return toUint(rs(0, 0));
     } catch (const DbSqlQueryExecFailure& f) {
         std::cout << "Get accounts count failed :'(" << std::endl;
     }
@@ -249,7 +173,9 @@ DALStorage::getAccount(const std::string& userName)
 
     // the account was not in the list, look for it in the database.
     try {
-        std::string sql("select * from tmw_accounts where username = '");
+        std::string sql("select * from ");
+        sql += ACCOUNTS_TBL_NAME;
+        sql + " where username = '";
         sql += userName;
         sql += "';";
         const RecordSet& accountInfo = mDb->execSql(sql);
@@ -271,7 +197,9 @@ DALStorage::getAccount(const std::string& userName)
         mAccounts.push_back(account);
 
         // load the characters associated with the account.
-        sql = "select * from tmw_characters where id = '";
+        sql = "select * from ";
+        sql += CHARACTERS_TBL_NAME;
+        sql += " where id = '";
         sql += accountInfo(0, 0);
         sql += "';";
         const RecordSet& charInfo = mDb->execSql(sql);
@@ -330,26 +258,47 @@ DALStorage::connect(void)
         // from a configuration manager.
         mDb->connect("tmw", "", "");
 
-        bool doInitDb = true;
-
-        // TODO: check the existence of the tables first and
-        // create only those that are missing.
-
-        if (doInitDb) {
-            // create the tables.
-            mDb->execSql(sqlAccountTable);
-            mDb->execSql(sqlCharacterTable);
-            mDb->execSql(sqlItemTable);
-            mDb->execSql(sqlWorldItemTable);
-            mDb->execSql(sqlInventoryTable);
-
-            // Example data :)
-            mDb->execSql("insert into tmw_accounts values (0, 'nym', 'tHiSiSHaShEd', 'nym@test', 1, 0);");
-            mDb->execSql("insert into tmw_accounts values (1, 'Bjorn', 'tHiSiSHaShEd', 'bjorn@test', 1, 0);");
-            mDb->execSql("insert into tmw_accounts values (2, 'Usiu', 'tHiSiSHaShEd', 'usiu@test', 1, 0);");
-            mDb->execSql("insert into tmw_accounts values (3, 'ElvenProgrammer', 'tHiSiSHaShEd', 'elven@test', 1, 0);");
-            mDb->execSql("insert into tmw_characters values (0, 0, 'Nym the Great', 0, 99, 1000000, 0, 0, 'main.map', 1, 2, 3, 4, 5, 6);");
-        }
+        // ensure that the required tables are created.
+        //
+        // strategy1: find a way to obtain the list of tables from the
+        //            underlying database and create the tables that are
+        //            missing.
+        //
+        // strategy2: try to create the tables and check the exceptions
+        //            thrown.
+        //
+        // comments:
+        //     - strategy1 is easy to achieve if we are using MysQL as
+        //       executing the request "show tables;" returns the list of
+        //       tables. However, there is not such a query for SQLite3.
+        //       When using SQLite3 from the interactive shell or the
+        //       command line, the command ".tables" returns the list of
+        //       tables but sqlite3_exec() does not validate this statement
+        //       and fails.
+        //       The cost of this strategy is:
+        //           (num. tables to create + 1) queries at most and
+        //           1 at minimum.
+        //
+        //     - strategy2 will work with probably most databases.
+        //       The cost of this strategy is:
+        //           (num. tables to create) queries.
+
+        // we will stick with strategy2 for the moment as we are focusing
+        // on SQLite.
+
+        createTable(MAPS_TBL_NAME, SQL_MAPS_TABLE);
+        createTable(ACCOUNTS_TBL_NAME, SQL_ACCOUNTS_TABLE);
+        createTable(CHARACTERS_TBL_NAME, SQL_CHARACTERS_TABLE);
+        createTable(ITEMS_TBL_NAME, SQL_ITEMS_TABLE);
+        createTable(WORLD_ITEMS_TBL_NAME, SQL_WORLD_ITEMS_TABLE);
+        createTable(INVENTORIES_TBL_NAME, SQL_INVENTORIES_TABLE);
+
+        // Example data :)
+        mDb->execSql("insert into tmw_accounts values (0, 'nym', 'tHiSiSHaShEd', 'nym@test', 1, 0);");
+        mDb->execSql("insert into tmw_accounts values (1, 'Bjorn', 'tHiSiSHaShEd', 'bjorn@test', 1, 0);");
+        mDb->execSql("insert into tmw_accounts values (2, 'Usiu', 'tHiSiSHaShEd', 'usiu@test', 1, 0);");
+        mDb->execSql("insert into tmw_accounts values (3, 'ElvenProgrammer', 'tHiSiSHaShEd', 'elven@test', 1, 0);");
+        mDb->execSql("insert into tmw_characters values (0, 0, 'Nym the Great', 0, 99, 1000000, 0, 0, 'main.map', 1, 2, 3, 4, 5, 6);");
     }
     catch (const DbConnectionFailure& e) {
         std::cout << "unable to connect to the database: "
@@ -361,4 +310,31 @@ DALStorage::connect(void)
 }
 
 
+/**
+ * Create the specified table.
+ */
+void
+DALStorage::createTable(const std::string& tblName,
+                        const std::string& sql)
+{
+    try {
+        mDb->execSql(sql);
+    }
+    catch (const dal::DbSqlQueryExecFailure& e) {
+        // error message to check against.
+        std::string alreadyExists("table ");
+        alreadyExists += tblName;
+        alreadyExists += " already exists";
+
+        const std::string msg(e.what());
+
+        // oops, another problem occurred.
+        if (msg != alreadyExists) {
+            // rethrow to let other error handlers manage the problem.
+            throw;
+        }
+    }
+}
+
+
 } // namespace tmwserv
diff --git a/src/dalstorage.h b/src/dalstorage.h
index a95ede9f..a29739e8 100644
--- a/src/dalstorage.h
+++ b/src/dalstorage.h
@@ -28,6 +28,7 @@
 #include <vector>
 
 #include "dal/dataproviderfactory.h"
+#include "dalstoragesql.h"
 #include "storage.h"
 
 
@@ -110,6 +111,19 @@ class DALStorage: public Storage
         connect(void);
 
 
+        /**
+         * Create the specified table.
+         *
+         * @param tblName the table name.
+         * @param sql the SQL query to execute.
+         *
+         * @exception tmwserv::dal::DbSqlQueryExecFailure.
+         */
+        void
+        createTable(const std::string& tblName,
+                    const std::string& sql);
+
+
     private:
         std::auto_ptr<dal::DataProvider> mDb; /**< the data provider */
         typedef std::vector<Account*> Accounts;
diff --git a/src/dalstoragesql.h b/src/dalstoragesql.h
new file mode 100644
index 00000000..3de9b33f
--- /dev/null
+++ b/src/dalstoragesql.h
@@ -0,0 +1,254 @@
+/*
+ *  The Mana World Server
+ *  Copyright 2004 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$
+ */
+
+
+#ifndef _TMWSERV_DALSTORAGE_SQL_H_
+#define _TMWSERV_DALSTORAGE_SQL_H_
+
+
+#if !defined (MYSQL_SUPPORT) && !defined (SQLITE_SUPPORT)
+#error "no database backend defined"
+#endif
+
+
+#include <string>
+
+
+/**
+ * MySQL specificities:
+ *     - TINYINT is an integer (1 byte) type defined as an extension to
+ *       the SQL standard.
+ *     - all integer types can have an optional (non-standard) attribute
+ *       UNSIGNED (http://dev.mysql.com/doc/mysql/en/numeric-types.html)
+ *
+ * SQLite3 specificities:
+ *     - any column (but only one for each table) with the exact type of
+ *       'INTEGER PRIMARY KEY' is taken as auto-increment.
+ *     - the supported data types are: NULL, INTEGER, REAL, TEXT and BLOB
+ *       (http://www.sqlite.org/datatype3.html)
+ *     - the size of TEXT cannot be set, it is just ignored by the engine.
+ *     - IMPORTANT: foreign key constraints are not yet supported
+ *       (http://www.sqlite.org/omitted.html)
+ *
+ * Notes:
+ *     - the SQL queries will take advantage of the most appropriate data
+ *       types supported by a particular database engine in order to
+ *       optimize the server database size.
+ */
+
+
+namespace {
+
+
+/**
+ * TABLE: tmw_maps.
+ */
+const std::string MAPS_TBL_NAME("tmw_maps");
+const std::string SQL_MAPS_TABLE(
+    "CREATE TABLE tmw_maps ("
+#if defined (MYSQL_SUPPORT)
+        "id  TINYINT PRIMARY KEY AUTO_INCREMENT,"
+        "map TEXT    NOT NULL,"
+        "INDEX (id)"
+#elif defined (SQLITE_SUPPORT)
+        "id  INTEGER PRIMARY KEY,"
+        "map TEXT    NOT NULL"
+#endif
+    ");"
+);
+
+
+/**
+ * TABLE: tmw_accounts.
+ *
+ * Notes:
+ *     - the user levels are:
+ *           0: normal user
+ *           1: moderator (has medium level rights)
+ *           2: administrator (i am god :))
+ *     - the 'banned' field contains the UNIX time of unban (default = 0)
+ */
+const std::string ACCOUNTS_TBL_NAME("tmw_accounts");
+const std::string SQL_ACCOUNTS_TABLE(
+    "CREATE TABLE tmw_accounts ("
+#if defined (MYSQL_SUPPORT)
+        "id       INTEGER     PRIMARY KEY AUTO_INCREMENT,"
+        "username VARCHAR(32) NOT NULL,"
+        "password VARCHAR(32) NOT NULL,"
+        "email    VARCHAR(64) NOT NULL,"
+        "level    TINYINT     UNSIGNED NOT NULL,"
+        "banned   TINYINT     UNSIGNED NOT NULL,"
+        "INDEX (id)"
+#elif defined (SQLITE_SUPPORT)
+        "id       INTEGER     PRIMARY KEY,"
+        "username TEXT        NOT NULL,"
+        "password TEXT        NOT NULL,"
+        "email    TEXT        NOT NULL,"
+        "level    INTEGER     NOT NULL,"
+        "banned   INTEGER     NOT NULL"
+#endif
+    ");"
+);
+
+
+/**
+ * TABLE: tmw_characters.
+ *
+ * Notes:
+ *     - the stats will need to be thought over, as we'll be implementing a
+ *       much more elaborate skill based system; we should probably have a
+ *       separate table for storing the skill levels.
+ *     - gender is 0 for male, 1 for female.
+ */
+const std::string CHARACTERS_TBL_NAME("tmw_characters");
+const std::string SQL_CHARACTERS_TABLE(
+    "CREATE TABLE tmw_characters ("
+#if defined (MYSQL_SUPPORT)
+        "id      INTEGER     PRIMARY KEY AUTO_INCREMENT,"
+        "user_id INTEGER     UNSIGNED NOT NULL,"
+        "name    VARCHAR(32) NOT NULL,"
+        // general information about the character
+        "gender  TINYINT     UNSIGNED NOT NULL,"
+        "level   TINYINT     UNSIGNED NOT NULL,"
+        "money   INTEGER     UNSIGNED NOT NULL,"
+        // location on the map
+        "x       SMALLINT    UNSIGNED NOT NULL,"
+        "y       SMALLINT    UNSIGNED NOT NULL,"
+        "map_id  TINYINT     NOT NULL,"
+        // stats
+        "str     SMALLINT    UNSIGNED NOT NULL,"
+        "agi     SMALLINT    UNSIGNED NOT NULL,"
+        "vit     SMALLINT    UNSIGNED NOT NULL,"
+        // note: int must be backquoted as it's a MySQL keyword
+        "`int`   SMALLINT    UNSIGNED NOT NULL,"
+        "dex     SMALLINT    UNSIGNED NOT NULL,"
+        "luck    SMALLINT    UNSIGNED NOT NULL,"
+        "FOREIGN KEY (user_id) REFERENCES tmw_accounts(id),"
+        "FOREIGN KEY (map_id)  REFERENCES tmw_maps(id),"
+        "INDEX (id)"
+#elif defined (SQLITE_SUPPORT)
+        "id      INTEGER     PRIMARY KEY,"
+        "user_id INTEGER     NOT NULL,"
+        "name    TEXT        NOT NULL,"
+        // general information about the character
+        "gender  INTEGER     NOT NULL,"
+        "level   INTEGER     NOT NULL,"
+        "money   INTEGER     NOT NULL,"
+        // location on the map
+        "x       INTEGER     NOT NULL,"
+        "y       INTEGER     NOT NULL,"
+        "map_id  INTEGER     NOT NULL,"
+        // stats
+        "str     INTEGER     NOT NULL,"
+        "agi     INTEGER     NOT NULL,"
+        "vit     INTEGER     NOT NULL,"
+        "int     INTEGER     NOT NULL,"
+        "dex     INTEGER     NOT NULL,"
+        "luck    INTEGER     NOT NULL"
+#endif
+    ");"
+);
+
+
+/**
+ * TABLE: tmw_items.
+ *
+ * Notes:
+ *     - amount: indicates how many items of the same kind can stack.
+ *     - state: (optional) item state saved by script.
+ */
+const std::string ITEMS_TBL_NAME("tmw_items");
+const std::string SQL_ITEMS_TABLE(
+    "CREATE TABLE tmw_items ("
+#if defined (MYSQL_SUPPORT)
+        "id     SMALLINT PRIMARY KEY AUTO_INCREMENT,"
+        "amount TINYINT  UNSIGNED NOT NULL,"
+        "type   TINYINT  UNSIGNED NOT NULL,"
+        "state  TEXT,"
+        "INDEX (id)"
+#elif defined (SQLITE_SUPPORT)
+        "id     INTEGER  PRIMARY KEY,"
+        "amount INTEGER  NOT NULL,"
+        "type   INTEGER  NOT NULL,"
+        "state  TEXT"
+#endif
+    ");"
+);
+
+
+/**
+ * TABLE: tmw_world_items.
+ *
+ * Notes:
+ *     - store items on the ground in the game world.
+ */
+const std::string WORLD_ITEMS_TBL_NAME("tmw_world_items");
+const std::string SQL_WORLD_ITEMS_TABLE(
+    "CREATE TABLE tmw_world_items ("
+#if defined (MYSQL_SUPPORT)
+        "id        SMALLINT UNSIGNED NOT NULL,"
+        // location on the map
+        "x         SMALLINT UNSIGNED NOT NULL,"
+        "y         SMALLINT UNSIGNED NOT NULL,"
+        "map_id    TINYINT  NOT NULL,"
+        // time to die (UNIX time)
+        "deathtime INTEGER  UNSIGNED NOT NULL,"
+        "PRIMARY KEY (id, map_id),"
+        "FOREIGN KEY (id)     REFERENCES tmw_items(id),"
+        "FOREIGN KEY (map_id) REFERENCES tmw_maps(id)"
+#elif defined (SQLITE_SUPPORT)
+        "id        INTEGER  NOT NULL,"
+        // location on the map
+        "x         INTEGER  NOT NULL,"
+        "y         INTEGER  NOT NULL,"
+        "map_id    INTEGER  NOT NULL,"
+        // time to die (UNIX time)
+        "deathtime INTEGER  NOT NULL,"
+        "PRIMARY KEY (id, map_id)"
+#endif
+    ");"
+);
+
+
+/**
+ * TABLE: tmw_inventory.
+ */
+const std::string INVENTORIES_TBL_NAME("tmw_inventories");
+const std::string SQL_INVENTORIES_TABLE(
+    "CREATE TABLE tmw_inventories ("
+#if defined (MYSQL_SUPPORT)
+        "id       SMALLINT NOT NULL,"
+        "owner_id INTEGER  NOT NULL,"
+        "FOREIGN KEY (id)       REFERENCES tmw_items(id),"
+        "FOREIGN KEY (owner_id) REFERENCES tmw_characters(id)"
+#elif defined (SQLITE_SUPPORT)
+        "id       INTEGER  NOT NULL,"
+        "owner_id INTEGER  NOT NULL"
+#endif
+    ");"
+);
+
+
+} // anonymous namespace
+
+
+#endif // _TMWSERV_DALSTORAGE_SQL_H_