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

1 files changed, 254 insertions, 0 deletions

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_