Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // Copyright (c) Athena Dev Teams - Licensed under GNU GPL
- // For more information, see LICENCE in the main folder
- #include "../common/malloc.h"
- #include "../common/mmo.h"
- #include "../common/showmsg.h"
- #include "../common/sql.h"
- #include "../common/strlib.h"
- #include "../common/timer.h"
- #include "account.h"
- #include <stdlib.h>
- #include <string.h>
- /// global defines
- #define ACCOUNT_SQL_DB_VERSION 20110114
- /// internal structure
- typedef struct AccountDB_SQL
- {
- AccountDB vtable; // public interface
- Sql* accounts; // SQL accounts storage
- // global sql settings
- char global_db_hostname[32];
- uint16 global_db_port;
- char global_db_username[32];
- char global_db_password[32];
- char global_db_database[32];
- char global_codepage[32];
- // local sql settings
- char db_hostname[32];
- uint16 db_port;
- char db_username[32];
- char db_password[32];
- char db_database[32];
- char codepage[32];
- // other settings
- bool case_sensitive;
- char account_db[32];
- char accreg_db[32];
- } AccountDB_SQL;
- /// internal structure
- typedef struct AccountDBIterator_SQL
- {
- AccountDBIterator vtable; // public interface
- AccountDB_SQL* db;
- int last_account_id;
- } AccountDBIterator_SQL;
- // (^~_~^) Gepard Shield Start
- static AccountDB_SQL* db_sql;
- void account_gepard_init(AccountDB* self)
- {
- db_sql = (AccountDB_SQL*)self;
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts,
- "CREATE TABLE IF NOT EXISTS `gepard_block` ("
- "`unique_id` int(11) unsigned NOT NULL DEFAULT '0',"
- "`unban_time` datetime NOT NULL,"
- "`reason` varchar(50) NOT NULL,"
- "UNIQUE KEY `unique_id` (`unique_id`)"
- ") ENGINE=MyISAM DEFAULT CHARSET=latin1;"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts,
- "CREATE TABLE IF NOT EXISTS `gepard_block_log` ("
- "`id` int(11) unsigned NOT NULL AUTO_INCREMENT,"
- "`unique_id` int(11) unsigned NOT NULL DEFAULT '0',"
- "`block_time` datetime NOT NULL,"
- "`unban_time` datetime NOT NULL,"
- "`violator_name` varchar(24) NOT NULL,"
- "`violator_account_id` int(11) NOT NULL,"
- "`initiator_name` varchar(24) NOT NULL,"
- "`initiator_account_id` int(11) NOT NULL,"
- "`reason` varchar(50) NOT NULL,"
- "PRIMARY KEY (`id`)"
- ") ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts,
- "CREATE TABLE IF NOT EXISTS `gepard_min_allowed_license_version` ("
- "`version` int(11) unsigned NOT NULL DEFAULT '2018033001'"
- ") ENGINE=MyISAM DEFAULT CHARSET=latin1;"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SELECT `version` FROM `gepard_min_allowed_license_version`"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- if (SQL_SUCCESS != Sql_NextRow(db_sql->accounts))
- {
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "INSERT INTO `gepard_min_allowed_license_version` VALUES ('0')"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- }
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SHOW COLUMNS FROM `login` LIKE 'last_unique_id'"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- if (Sql_NumRows(db_sql->accounts) == 0)
- {
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "ALTER TABLE `login` ADD `last_unique_id` INT(11) UNSIGNED NOT NULL DEFAULT '0';"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- }
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SHOW COLUMNS FROM `login` LIKE 'blocked_unique_id'"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- if (Sql_NumRows(db_sql->accounts) == 0)
- {
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "ALTER TABLE `login` ADD `blocked_unique_id` INT(11) UNSIGNED NOT NULL DEFAULT '0';"))
- {
- Sql_ShowDebug(db_sql->accounts);
- exit(EXIT_FAILURE);
- }
- }
- }
- int account_gepard_check_license_version(struct socket_data* s, int fd, int group_id)
- {
- const char* message_info_sql_error = "Tell administrator about SQL problem.";
- if (is_gepard_active == true)
- {
- return 0;
- }
- if (group_id == 99)
- {
- return 0;
- }
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SELECT `version` FROM `gepard_min_allowed_license_version`"))
- {
- Sql_ShowDebug(db_sql->accounts);
- gepard_send_info(fd, GEPARD_INFO_MESSAGE, message_info_sql_error);
- }
- else if (SQL_SUCCESS != Sql_NextRow(db_sql->accounts))
- {
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "INSERT INTO `gepard_min_allowed_license_version` VALUES ('0')"))
- {
- Sql_ShowDebug(db_sql->accounts);
- gepard_send_info(fd, GEPARD_INFO_MESSAGE, message_info_sql_error);
- }
- }
- else
- {
- char* data;
- const char* message_info_outdated_license = "You are using outdated Gepard Shield.\n\nYou have to close the game and run updater.";
- Sql_GetData(db_sql->accounts, 0, &data, NULL);
- min_allowed_license_version = (unsigned int)strtoul(data, NULL, 10);
- Sql_FreeResult(db_sql->accounts);
- if (s->gepard_info.license_version >= min_allowed_license_version)
- {
- return 0;
- }
- gepard_send_info(fd, GEPARD_INFO_OLD_LICENSE_VERSION, message_info_outdated_license);
- }
- return 1;
- }
- void account_gepard_update_last_unique_id(int account_id, unsigned int unique_id)
- {
- if (is_gepard_active == true)
- {
- return 0;
- }
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "UPDATE `login` SET `last_unique_id`= '%u' WHERE `account_id` = '%d'", unique_id, account_id))
- {
- Sql_ShowDebug(db_sql->accounts);
- }
- else if (SQL_SUCCESS == Sql_NextRow(db_sql->accounts))
- {
- Sql_ShowDebug(db_sql->accounts);
- }
- Sql_FreeResult(db_sql->accounts);
- }
- bool account_gepard_check_unique_id(int fd, struct socket_data* s)
- {
- unsigned int unique_id = s->gepard_info.unique_id;
- if (SQL_SUCCESS != Sql_Query(db_sql->accounts, "SELECT `unban_time`, `reason` FROM `gepard_block` WHERE `unique_id` = '%u'", unique_id))
- {
- Sql_ShowDebug(db_sql->accounts);
- gepard_send_info(fd, GEPARD_INFO_MESSAGE, "Tell administrator about SQL problem.");
- }
- else if (SQL_SUCCESS == Sql_NextRow(db_sql->accounts))
- {
- char* data;
- struct tm unblock_tm;
- time_t time_unban, time_server;
- int year, month, day, hour, min, sec;
- char reason_str[GEPARD_REASON_LENGTH];
- char unban_time_str[GEPARD_TIME_STR_LENGTH];
- memset((void*)&unblock_tm, 0, sizeof(unblock_tm));
- Sql_GetData(db_sql->accounts, 0, &data, NULL);
- safestrncpy(unban_time_str, data, sizeof(unban_time_str));
- sscanf(unban_time_str, "%d-%d-%d %d:%d:%d", &year, &month, &day, &hour, &min, &sec);
- unblock_tm.tm_year = year - 1900;
- unblock_tm.tm_mon = month - 1;
- unblock_tm.tm_mday = day;
- unblock_tm.tm_hour = hour;
- unblock_tm.tm_min = min;
- unblock_tm.tm_sec = sec;
- time_unban = mktime(&unblock_tm);
- time(&time_server);
- if (time_server <= time_unban)
- {
- char message_info[200];
- Sql_GetData(db_sql->accounts, 1, &data, NULL);
- safestrncpy(reason_str, data, sizeof(reason_str));
- safesnprintf(message_info, sizeof(message_info), "Unique ID has been banned!\r\rDate of unban: %s\r\rUnique id: %u\r\rReason: %s", unban_time_str, unique_id, reason_str);
- s->gepard_info.is_init_ack_received = false;
- gepard_send_info(fd, GEPARD_INFO_BANNED, message_info);
- }
- else if (SQL_ERROR == Sql_Query(db_sql->accounts, "DELETE FROM `gepard_block` WHERE `unique_id` = '%u'", unique_id))
- {
- Sql_ShowDebug(db_sql->accounts);
- }
- }
- Sql_FreeResult(db_sql->accounts);
- return false;
- }
- // (^~_~^) Gepard Shield End
- /// internal functions
- static bool account_db_sql_init(AccountDB* self);
- static void account_db_sql_destroy(AccountDB* self);
- static bool account_db_sql_get_property(AccountDB* self, const char* key, char* buf, size_t buflen);
- static bool account_db_sql_set_property(AccountDB* self, const char* option, const char* value);
- static bool account_db_sql_create(AccountDB* self, struct mmo_account* acc);
- static bool account_db_sql_remove(AccountDB* self, const int account_id);
- static bool account_db_sql_save(AccountDB* self, const struct mmo_account* acc);
- static bool account_db_sql_load_num(AccountDB* self, struct mmo_account* acc, const int account_id);
- static bool account_db_sql_load_str(AccountDB* self, struct mmo_account* acc, const char* userid);
- static AccountDBIterator* account_db_sql_iterator(AccountDB* self);
- static void account_db_sql_iter_destroy(AccountDBIterator* self);
- static bool account_db_sql_iter_next(AccountDBIterator* self, struct mmo_account* acc);
- static bool mmo_auth_fromsql(AccountDB_SQL* db, struct mmo_account* acc, int account_id);
- static bool mmo_auth_tosql(AccountDB_SQL* db, const struct mmo_account* acc, bool is_new);
- /// public constructor
- AccountDB* account_db_sql(void)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)aCalloc(1, sizeof(AccountDB_SQL));
- // set up the vtable
- db->vtable.init = &account_db_sql_init;
- db->vtable.destroy = &account_db_sql_destroy;
- db->vtable.get_property = &account_db_sql_get_property;
- db->vtable.set_property = &account_db_sql_set_property;
- db->vtable.save = &account_db_sql_save;
- db->vtable.create = &account_db_sql_create;
- db->vtable.remove = &account_db_sql_remove;
- db->vtable.load_num = &account_db_sql_load_num;
- db->vtable.load_str = &account_db_sql_load_str;
- db->vtable.iterator = &account_db_sql_iterator;
- // initialize to default values
- db->accounts = NULL;
- // global sql settings
- safestrncpy(db->global_db_hostname, "127.0.0.1", sizeof(db->global_db_hostname));
- db->global_db_port = 3306;
- safestrncpy(db->global_db_username, "ragnarok", sizeof(db->global_db_username));
- safestrncpy(db->global_db_password, "ragnarok", sizeof(db->global_db_password));
- safestrncpy(db->global_db_database, "ragnarok", sizeof(db->global_db_database));
- safestrncpy(db->global_codepage, "", sizeof(db->global_codepage));
- // local sql settings
- safestrncpy(db->db_hostname, "", sizeof(db->db_hostname));
- db->db_port = 3306;
- safestrncpy(db->db_username, "", sizeof(db->db_username));
- safestrncpy(db->db_password, "", sizeof(db->db_password));
- safestrncpy(db->db_database, "", sizeof(db->db_database));
- safestrncpy(db->codepage, "", sizeof(db->codepage));
- // other settings
- db->case_sensitive = false;
- safestrncpy(db->account_db, "login", sizeof(db->account_db));
- safestrncpy(db->accreg_db, "global_reg_value", sizeof(db->accreg_db));
- return &db->vtable;
- }
- /* ------------------------------------------------------------------------- */
- /// establishes database connection
- static bool account_db_sql_init(AccountDB* self)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- Sql* sql_handle;
- const char* username;
- const char* password;
- const char* hostname;
- uint16 port;
- const char* database;
- const char* codepage;
- db->accounts = Sql_Malloc();
- sql_handle = db->accounts;
- if( db->db_hostname[0] != '\0' )
- {// local settings
- username = db->db_username;
- password = db->db_password;
- hostname = db->db_hostname;
- port = db->db_port;
- database = db->db_database;
- codepage = db->codepage;
- }
- else
- {// global settings
- username = db->global_db_username;
- password = db->global_db_password;
- hostname = db->global_db_hostname;
- port = db->global_db_port;
- database = db->global_db_database;
- codepage = db->global_codepage;
- }
- if( SQL_ERROR == Sql_Connect(sql_handle, username, password, hostname, port, database) )
- {
- Sql_ShowDebug(sql_handle);
- Sql_Free(db->accounts);
- db->accounts = NULL;
- return false;
- }
- if( codepage[0] != '\0' && SQL_ERROR == Sql_SetEncoding(sql_handle, codepage) )
- Sql_ShowDebug(sql_handle);
- // (^~_~^) Gepard Shield Start
- if (is_gepard_active == true)
- {
- account_gepard_init(self);
- }
- // (^~_~^) Gepard Shield End
- return true;
- }
- /// disconnects from database
- static void account_db_sql_destroy(AccountDB* self)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- Sql_Free(db->accounts);
- db->accounts = NULL;
- aFree(db);
- }
- /// Gets a property from this database.
- static bool account_db_sql_get_property(AccountDB* self, const char* key, char* buf, size_t buflen)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- const char* signature;
- signature = "engine.";
- if( strncmpi(key, signature, strlen(signature)) == 0 )
- {
- key += strlen(signature);
- if( strcmpi(key, "name") == 0 )
- safesnprintf(buf, buflen, "sql");
- else
- if( strcmpi(key, "version") == 0 )
- safesnprintf(buf, buflen, "%d", ACCOUNT_SQL_DB_VERSION);
- else
- if( strcmpi(key, "comment") == 0 )
- safesnprintf(buf, buflen, "SQL Account Database");
- else
- return false;// not found
- return true;
- }
- signature = "sql.";
- if( strncmpi(key, signature, strlen(signature)) == 0 )
- {
- key += strlen(signature);
- if( strcmpi(key, "db_hostname") == 0 )
- safesnprintf(buf, buflen, "%s", db->global_db_hostname);
- else
- if( strcmpi(key, "db_port") == 0 )
- safesnprintf(buf, buflen, "%d", db->global_db_port);
- else
- if( strcmpi(key, "db_username") == 0 )
- safesnprintf(buf, buflen, "%s", db->global_db_username);
- else
- if( strcmpi(key, "db_password") == 0 )
- safesnprintf(buf, buflen, "%s", db->global_db_password);
- else
- if( strcmpi(key, "db_database") == 0 )
- safesnprintf(buf, buflen, "%s", db->global_db_database);
- else
- if( strcmpi(key, "codepage") == 0 )
- safesnprintf(buf, buflen, "%s", db->global_codepage);
- else
- return false;// not found
- return true;
- }
- signature = "account.sql.";
- if( strncmpi(key, signature, strlen(signature)) == 0 )
- {
- key += strlen(signature);
- if( strcmpi(key, "db_hostname") == 0 )
- safesnprintf(buf, buflen, "%s", db->db_hostname);
- else
- if( strcmpi(key, "db_port") == 0 )
- safesnprintf(buf, buflen, "%d", db->db_port);
- else
- if( strcmpi(key, "db_username") == 0 )
- safesnprintf(buf, buflen, "%s", db->db_username);
- else
- if( strcmpi(key, "db_password") == 0 )
- safesnprintf(buf, buflen, "%s", db->db_password);
- else
- if( strcmpi(key, "db_database") == 0 )
- safesnprintf(buf, buflen, "%s", db->db_database);
- else
- if( strcmpi(key, "codepage") == 0 )
- safesnprintf(buf, buflen, "%s", db->codepage);
- else
- if( strcmpi(key, "case_sensitive") == 0 )
- safesnprintf(buf, buflen, "%d", (db->case_sensitive ? 1 : 0));
- else
- if( strcmpi(key, "account_db") == 0 )
- safesnprintf(buf, buflen, "%s", db->account_db);
- else
- if( strcmpi(key, "accreg_db") == 0 )
- safesnprintf(buf, buflen, "%s", db->accreg_db);
- else
- return false;// not found
- return true;
- }
- return false;// not found
- }
- /// if the option is supported, adjusts the internal state
- static bool account_db_sql_set_property(AccountDB* self, const char* key, const char* value)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- const char* signature;
- signature = "sql.";
- if( strncmp(key, signature, strlen(signature)) == 0 )
- {
- key += strlen(signature);
- if( strcmpi(key, "db_hostname") == 0 )
- safestrncpy(db->global_db_hostname, value, sizeof(db->global_db_hostname));
- else
- if( strcmpi(key, "db_port") == 0 )
- db->global_db_port = (uint16)strtoul(value, NULL, 10);
- else
- if( strcmpi(key, "db_username") == 0 )
- safestrncpy(db->global_db_username, value, sizeof(db->global_db_username));
- else
- if( strcmpi(key, "db_password") == 0 )
- safestrncpy(db->global_db_password, value, sizeof(db->global_db_password));
- else
- if( strcmpi(key, "db_database") == 0 )
- safestrncpy(db->global_db_database, value, sizeof(db->global_db_database));
- else
- if( strcmpi(key, "codepage") == 0 )
- safestrncpy(db->global_codepage, value, sizeof(db->global_codepage));
- else
- return false;// not found
- return true;
- }
- signature = "account.sql.";
- if( strncmp(key, signature, strlen(signature)) == 0 )
- {
- key += strlen(signature);
- if( strcmpi(key, "db_hostname") == 0 )
- safestrncpy(db->db_hostname, value, sizeof(db->db_hostname));
- else
- if( strcmpi(key, "db_port") == 0 )
- db->db_port = (uint16)strtoul(value, NULL, 10);
- else
- if( strcmpi(key, "db_username") == 0 )
- safestrncpy(db->db_username, value, sizeof(db->db_username));
- else
- if( strcmpi(key, "db_password") == 0 )
- safestrncpy(db->db_password, value, sizeof(db->db_password));
- else
- if( strcmpi(key, "db_database") == 0 )
- safestrncpy(db->db_database, value, sizeof(db->db_database));
- else
- if( strcmpi(key, "codepage") == 0 )
- safestrncpy(db->codepage, value, sizeof(db->codepage));
- else
- if( strcmpi(key, "case_sensitive") == 0 )
- db->case_sensitive = config_switch(value);
- else
- if( strcmpi(key, "account_db") == 0 )
- safestrncpy(db->account_db, value, sizeof(db->account_db));
- else
- if( strcmpi(key, "accreg_db") == 0 )
- safestrncpy(db->accreg_db, value, sizeof(db->accreg_db));
- else
- return false;// not found
- return true;
- }
- return false;// not found
- }
- /// create a new account entry
- /// If acc->account_id is -1, the account id will be auto-generated,
- /// and its value will be written to acc->account_id if everything succeeds.
- static bool account_db_sql_create(AccountDB* self, struct mmo_account* acc)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- Sql* sql_handle = db->accounts;
- // decide on the account id to assign
- int account_id;
- if( acc->account_id != -1 )
- {// caller specifies it manually
- account_id = acc->account_id;
- }
- else
- {// ask the database
- char* data;
- size_t len;
- if( SQL_SUCCESS != Sql_Query(sql_handle, "SELECT MAX(`account_id`)+1 FROM `%s`", db->account_db) )
- {
- Sql_ShowDebug(sql_handle);
- return false;
- }
- if( SQL_SUCCESS != Sql_NextRow(sql_handle) )
- {
- Sql_ShowDebug(sql_handle);
- Sql_FreeResult(sql_handle);
- return false;
- }
- Sql_GetData(sql_handle, 0, &data, &len);
- account_id = ( data != NULL ) ? atoi(data) : 0;
- Sql_FreeResult(sql_handle);
- if( account_id < START_ACCOUNT_NUM )
- account_id = START_ACCOUNT_NUM;
- }
- // zero value is prohibited
- if( account_id == 0 )
- return false;
- // absolute maximum
- if( account_id > END_ACCOUNT_NUM )
- return false;
- // insert the data into the database
- acc->account_id = account_id;
- return mmo_auth_tosql(db, acc, true);
- }
- /// delete an existing account entry + its regs
- static bool account_db_sql_remove(AccountDB* self, const int account_id)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- Sql* sql_handle = db->accounts;
- bool result = false;
- if( SQL_SUCCESS != Sql_QueryStr(sql_handle, "START TRANSACTION")
- || SQL_SUCCESS != Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `account_id` = %d", db->account_db, account_id)
- || SQL_SUCCESS != Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `account_id` = %d", db->accreg_db, account_id) )
- Sql_ShowDebug(sql_handle);
- else
- result = true;
- result &= ( SQL_SUCCESS == Sql_QueryStr(sql_handle, (result == true) ? "COMMIT" : "ROLLBACK") );
- return result;
- }
- /// update an existing account with the provided new data (both account and regs)
- static bool account_db_sql_save(AccountDB* self, const struct mmo_account* acc)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- return mmo_auth_tosql(db, acc, false);
- }
- /// retrieve data from db and store it in the provided data structure
- static bool account_db_sql_load_num(AccountDB* self, struct mmo_account* acc, const int account_id)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- return mmo_auth_fromsql(db, acc, account_id);
- }
- /// retrieve data from db and store it in the provided data structure
- static bool account_db_sql_load_str(AccountDB* self, struct mmo_account* acc, const char* userid)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- Sql* sql_handle = db->accounts;
- char esc_userid[2*NAME_LENGTH+1];
- int account_id;
- char* data;
- Sql_EscapeString(sql_handle, esc_userid, userid);
- // get the list of account IDs for this user ID
- if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `account_id` FROM `%s` WHERE `userid`= %s '%s'",
- db->account_db, (db->case_sensitive ? "BINARY" : ""), esc_userid) )
- {
- Sql_ShowDebug(sql_handle);
- return false;
- }
- if( Sql_NumRows(sql_handle) > 1 )
- {// serious problem - duplicit account
- ShowError("account_db_sql_load_str: multiple accounts found when retrieving data for account '%s'!\n", userid);
- Sql_FreeResult(sql_handle);
- return false;
- }
- if( SQL_SUCCESS != Sql_NextRow(sql_handle) )
- {// no such entry
- Sql_FreeResult(sql_handle);
- return false;
- }
- Sql_GetData(sql_handle, 0, &data, NULL);
- account_id = atoi(data);
- return account_db_sql_load_num(self, acc, account_id);
- }
- /// Returns a new forward iterator.
- static AccountDBIterator* account_db_sql_iterator(AccountDB* self)
- {
- AccountDB_SQL* db = (AccountDB_SQL*)self;
- AccountDBIterator_SQL* iter = (AccountDBIterator_SQL*)aCalloc(1, sizeof(AccountDBIterator_SQL));
- // set up the vtable
- iter->vtable.destroy = &account_db_sql_iter_destroy;
- iter->vtable.next = &account_db_sql_iter_next;
- // fill data
- iter->db = db;
- iter->last_account_id = -1;
- return &iter->vtable;
- }
- /// Destroys this iterator, releasing all allocated memory (including itself).
- static void account_db_sql_iter_destroy(AccountDBIterator* self)
- {
- AccountDBIterator_SQL* iter = (AccountDBIterator_SQL*)self;
- aFree(iter);
- }
- /// Fetches the next account in the database.
- static bool account_db_sql_iter_next(AccountDBIterator* self, struct mmo_account* acc)
- {
- AccountDBIterator_SQL* iter = (AccountDBIterator_SQL*)self;
- AccountDB_SQL* db = (AccountDB_SQL*)iter->db;
- Sql* sql_handle = db->accounts;
- int account_id;
- char* data;
- // get next account ID
- if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `account_id` FROM `%s` WHERE `account_id` > '%d' ORDER BY `account_id` ASC LIMIT 1",
- db->account_db, iter->last_account_id) )
- {
- Sql_ShowDebug(sql_handle);
- return false;
- }
- if( SQL_SUCCESS == Sql_NextRow(sql_handle) &&
- SQL_SUCCESS == Sql_GetData(sql_handle, 0, &data, NULL) &&
- data != NULL )
- {// get account data
- account_id = atoi(data);
- if( mmo_auth_fromsql(db, acc, account_id) )
- {
- iter->last_account_id = account_id;
- Sql_FreeResult(sql_handle);
- return true;
- }
- }
- Sql_FreeResult(sql_handle);
- return false;
- }
- static bool mmo_auth_fromsql(AccountDB_SQL* db, struct mmo_account* acc, int account_id)
- {
- Sql* sql_handle = db->accounts;
- char* data;
- int i = 0;
- // retrieve login entry for the specified account
- if( SQL_ERROR == Sql_Query(sql_handle,
- "SELECT `account_id`,`userid`,`user_pass`,`sex`,`email`,`level`,`state`,`unban_time`,`expiration_time`,`logincount`,`lastlogin`,`last_ip`,`birthdate` FROM `%s` WHERE `account_id` = %d",
- db->account_db, account_id )
- ) {
- Sql_ShowDebug(sql_handle);
- return false;
- }
- if( SQL_SUCCESS != Sql_NextRow(sql_handle) )
- {// no such entry
- Sql_FreeResult(sql_handle);
- return false;
- }
- Sql_GetData(sql_handle, 0, &data, NULL); acc->account_id = atoi(data);
- Sql_GetData(sql_handle, 1, &data, NULL); safestrncpy(acc->userid, data, sizeof(acc->userid));
- Sql_GetData(sql_handle, 2, &data, NULL); safestrncpy(acc->pass, data, sizeof(acc->pass));
- Sql_GetData(sql_handle, 3, &data, NULL); acc->sex = data[0];
- Sql_GetData(sql_handle, 4, &data, NULL); safestrncpy(acc->email, data, sizeof(acc->email));
- Sql_GetData(sql_handle, 5, &data, NULL); acc->level = atoi(data);
- Sql_GetData(sql_handle, 6, &data, NULL); acc->state = strtoul(data, NULL, 10);
- Sql_GetData(sql_handle, 7, &data, NULL); acc->unban_time = atol(data);
- Sql_GetData(sql_handle, 8, &data, NULL); acc->expiration_time = atol(data);
- Sql_GetData(sql_handle, 9, &data, NULL); acc->logincount = strtoul(data, NULL, 10);
- Sql_GetData(sql_handle, 10, &data, NULL); safestrncpy(acc->lastlogin, data, sizeof(acc->lastlogin));
- Sql_GetData(sql_handle, 11, &data, NULL); safestrncpy(acc->last_ip, data, sizeof(acc->last_ip));
- Sql_GetData(sql_handle, 12, &data, NULL); safestrncpy(acc->birthdate, data, sizeof(acc->birthdate));
- Sql_FreeResult(sql_handle);
- // retrieve account regs for the specified user
- if( SQL_ERROR == Sql_Query(sql_handle, "SELECT `str`,`value` FROM `%s` WHERE `type`='1' AND `account_id`='%d'", db->accreg_db, acc->account_id) )
- {
- Sql_ShowDebug(sql_handle);
- return false;
- }
- acc->account_reg2_num = (int)Sql_NumRows(sql_handle);
- while( SQL_SUCCESS == Sql_NextRow(sql_handle) )
- {
- char* data;
- Sql_GetData(sql_handle, 0, &data, NULL); safestrncpy(acc->account_reg2[i].str, data, sizeof(acc->account_reg2[i].str));
- Sql_GetData(sql_handle, 1, &data, NULL); safestrncpy(acc->account_reg2[i].value, data, sizeof(acc->account_reg2[i].value));
- ++i;
- }
- Sql_FreeResult(sql_handle);
- if( i != acc->account_reg2_num )
- return false;
- return true;
- }
- static bool mmo_auth_tosql(AccountDB_SQL* db, const struct mmo_account* acc, bool is_new)
- {
- Sql* sql_handle = db->accounts;
- SqlStmt* stmt = SqlStmt_Malloc(sql_handle);
- bool result = false;
- int i;
- // try
- do
- {
- if( SQL_SUCCESS != Sql_QueryStr(sql_handle, "START TRANSACTION") )
- {
- Sql_ShowDebug(sql_handle);
- break;
- }
- if( is_new )
- {// insert into account table
- if( SQL_SUCCESS != SqlStmt_Prepare(stmt,
- "INSERT INTO `%s` (`account_id`, `userid`, `user_pass`, `sex`, `email`, `level`, `state`, `unban_time`, `expiration_time`, `logincount`, `lastlogin`, `last_ip`, `birthdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
- db->account_db)
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_INT, (void*)&acc->account_id, sizeof(acc->account_id))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, (void*)acc->userid, strlen(acc->userid))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 2, SQLDT_STRING, (void*)acc->pass, strlen(acc->pass))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 3, SQLDT_ENUM, (void*)&acc->sex, sizeof(acc->sex))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 4, SQLDT_STRING, (void*)&acc->email, strlen(acc->email))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 5, SQLDT_INT, (void*)&acc->level, sizeof(acc->level))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 6, SQLDT_UINT, (void*)&acc->state, sizeof(acc->state))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 7, SQLDT_LONG, (void*)&acc->unban_time, sizeof(acc->unban_time))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 8, SQLDT_INT, (void*)&acc->expiration_time, sizeof(acc->expiration_time))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 9, SQLDT_UINT, (void*)&acc->logincount, sizeof(acc->logincount))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 10, SQLDT_STRING, (void*)&acc->lastlogin, strlen(acc->lastlogin))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 11, SQLDT_STRING, (void*)&acc->last_ip, strlen(acc->last_ip))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 12, SQLDT_STRING, (void*)&acc->birthdate, strlen(acc->birthdate))
- || SQL_SUCCESS != SqlStmt_Execute(stmt)
- ) {
- SqlStmt_ShowDebug(stmt);
- break;
- }
- }
- else
- {// update account table
- if( SQL_SUCCESS != SqlStmt_Prepare(stmt, "UPDATE `%s` SET `userid`=?,`user_pass`=?,`sex`=?,`email`=?,`level`=?,`state`=?,`unban_time`=?,`expiration_time`=?,`logincount`=?,`lastlogin`=?,`last_ip`=?,`birthdate`=? WHERE `account_id` = '%d'", db->account_db, acc->account_id)
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_STRING, (void*)acc->userid, strlen(acc->userid))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, (void*)acc->pass, strlen(acc->pass))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 2, SQLDT_ENUM, (void*)&acc->sex, sizeof(acc->sex))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 3, SQLDT_STRING, (void*)acc->email, strlen(acc->email))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 4, SQLDT_INT, (void*)&acc->level, sizeof(acc->level))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 5, SQLDT_UINT, (void*)&acc->state, sizeof(acc->state))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 6, SQLDT_LONG, (void*)&acc->unban_time, sizeof(acc->unban_time))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 7, SQLDT_LONG, (void*)&acc->expiration_time, sizeof(acc->expiration_time))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 8, SQLDT_UINT, (void*)&acc->logincount, sizeof(acc->logincount))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 9, SQLDT_STRING, (void*)&acc->lastlogin, strlen(acc->lastlogin))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 10, SQLDT_STRING, (void*)&acc->last_ip, strlen(acc->last_ip))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 11, SQLDT_STRING, (void*)&acc->birthdate, strlen(acc->birthdate))
- || SQL_SUCCESS != SqlStmt_Execute(stmt)
- ) {
- SqlStmt_ShowDebug(stmt);
- break;
- }
- }
- // remove old account regs
- if( SQL_SUCCESS != Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `type`='1' AND `account_id`='%d'", db->accreg_db, acc->account_id) )
- {
- Sql_ShowDebug(sql_handle);
- break;
- }
- // insert new account regs
- if( SQL_SUCCESS != SqlStmt_Prepare(stmt, "INSERT INTO `%s` (`type`, `account_id`, `str`, `value`) VALUES ( 1 , '%d' , ? , ? );", db->accreg_db, acc->account_id) )
- {
- SqlStmt_ShowDebug(stmt);
- break;
- }
- for( i = 0; i < acc->account_reg2_num; ++i )
- {
- if( SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_STRING, (void*)acc->account_reg2[i].str, strlen(acc->account_reg2[i].str))
- || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, (void*)acc->account_reg2[i].value, strlen(acc->account_reg2[i].value))
- || SQL_SUCCESS != SqlStmt_Execute(stmt)
- ) {
- SqlStmt_ShowDebug(stmt);
- break;
- }
- }
- if( i < acc->account_reg2_num )
- {
- result = false;
- break;
- }
- // if we got this far, everything was successful
- result = true;
- } while(0);
- // finally
- result &= ( SQL_SUCCESS == Sql_QueryStr(sql_handle, (result == true) ? "COMMIT" : "ROLLBACK") );
- SqlStmt_Free(stmt);
- return result;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement