From 88c1ff53281d8b7c37a78d7b62761f7cace5d7d5 Mon Sep 17 00:00:00 2001 From: Huynh Tran Date: Sat, 18 Jun 2005 11:57:08 +0000 Subject: Moved SQL queries for the creation of tables into a separate file and rewrote the initialization of the database. --- src/Makefile.am | 15 ++-- src/dalstorage.cpp | 188 +++++++++++++++++--------------------- src/dalstorage.h | 14 +++ src/dalstoragesql.h | 254 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 359 insertions(+), 112 deletions(-) create mode 100644 src/dalstoragesql.h (limited to 'src') 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 #include "dalstorage.h" +#include "dalstoragesql.h" namespace @@ -64,84 +65,6 @@ struct string_to: public std::unary_function }; -/* 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 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 #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 mDb; /**< the data provider */ typedef std::vector 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 + + +/** + * 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_ -- cgit v1.2.3-70-g09d2