int32 Sql_Connect(Sql_t* self, const char* user, const char* passwd, const char* host, uint16 port, const char* db)
{
if( self == NULL )
return SQL_ERROR;
StringBuf_Clear(&self->buf);
if( !mysql_real_connect(&self->handle, host, user, passwd, db, (uint32)port, NULL/*unix_socket*/, 0/*clientflag*/) )
{
ShowSQL("%s\n", mysql_error(&self->handle));
return SQL_ERROR;
}
return SQL_SUCCESS;
}
/// Prepares the statement.
int SqlStmt_PrepareStr(SqlStmt* self, const char* query)
{
if( self == NULL )
return SQL_ERROR;
SqlStmt_FreeResult(self);
StringBuf_Clear(&self->buf);
StringBuf_AppendStr(&self->buf, query);
if( mysql_stmt_prepare(self->stmt, StringBuf_Value(&self->buf), (unsigned long)StringBuf_Length(&self->buf)) )
{
ShowSQL("DB error - %s\n", mysql_stmt_error(self->stmt));
return SQL_ERROR;
}
self->bind_params = false;
return SQL_SUCCESS;
}
/// Prepares the statement.
int SqlStmt_PrepareV(SqlStmt* self, const char* query, va_list args)
{
if( self == NULL )
return SQL_ERROR;
SqlStmt_FreeResult(self);
StringBuf_Clear(&self->buf);
StringBuf_Vprintf(&self->buf, query, args);
if( mysql_stmt_prepare(self->stmt, StringBuf_Value(&self->buf), (unsigned long)StringBuf_Length(&self->buf)) )
{
ShowSQL("DB error - %s\n", mysql_stmt_error(self->stmt));
hercules_mysql_error_handler(mysql_stmt_errno(self->stmt));
return SQL_ERROR;
}
self->bind_params = false;
return SQL_SUCCESS;
}
int32 Sql_QueryStr(Sql_t* self, const char* query)
{
if( self == NULL )
return SQL_ERROR;
Sql_FreeResult(self);
StringBuf_Clear(&self->buf);
StringBuf_AppendStr(&self->buf, query);
if( mysql_real_query(&self->handle, StringBuf_Value(&self->buf), (uint32)StringBuf_Length(&self->buf)) )
{
ShowSQL("DB error - %s\n", mysql_error(&self->handle));
return SQL_ERROR;
}
self->result = mysql_store_result(&self->handle);
if( mysql_errno(&self->handle) != 0 )
{
ShowSQL("DB error - %s\n", mysql_error(&self->handle));
return SQL_ERROR;
}
return SQL_SUCCESS;
}
/// Establishes a connection.
int Sql_Connect(Sql* self, const char* user, const char* passwd, const char* host, uint16 port, const char* db)
{
if( self == NULL )
return SQL_ERROR;
StringBuf_Clear(&self->buf);
if( !mysql_real_connect(&self->handle, host, user, passwd, db, (unsigned int)port, NULL/*unix_socket*/, 0/*clientflag*/) )
{
ShowSQL("%s\n", mysql_error(&self->handle));
return SQL_ERROR;
}
self->keepalive = Sql_P_Keepalive(self);
if( self->keepalive == INVALID_TIMER )
{
ShowSQL("Failed to establish keepalive for DB connection!\n");
return SQL_ERROR;
}
return SQL_SUCCESS;
}
/// Executes a query.
int Sql_QueryV(Sql* self, const char* query, va_list args)
{
if( self == NULL )
return SQL_ERROR;
Sql_FreeResult(self);
StringBuf_Clear(&self->buf);
StringBuf_Vprintf(&self->buf, query, args);
if( mysql_real_query(&self->handle, StringBuf_Value(&self->buf), (unsigned long)StringBuf_Length(&self->buf)) )
{
ShowSQL("DB error - %s\n", mysql_error(&self->handle));
return SQL_ERROR;
}
self->result = mysql_store_result(&self->handle);
if( mysql_errno(&self->handle) != 0 )
{
ShowSQL("DB error - %s\n", mysql_error(&self->handle));
return SQL_ERROR;
}
return SQL_SUCCESS;
}
/**
* ZI 0x3056 <char_id>.L <account_id>.L <guild_id>.W
* Pulls guild bound items for offline characters
* @author [Akinari]
*/
int mapif_parse_itembound_retrieve(int fd)
{
StringBuf buf;
SqlStmt *stmt;
unsigned short i = 0, count = 0;
struct item item, items[MAX_INVENTORY];
int j, guild_id = RFIFOW(fd,10);
uint32 char_id = RFIFOL(fd,2), account_id = RFIFOL(fd,6);
StringBuf_Init(&buf);
//Get bound items from player's inventory
StringBuf_AppendStr(&buf, "SELECT `id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `bound`");
for( j = 0; j < MAX_SLOTS; ++j )
StringBuf_Printf(&buf, ", `card%d`", j);
StringBuf_Printf(&buf, " FROM `%s` WHERE `char_id`='%d' AND `bound` = %d", inventory_db, char_id, BOUND_GUILD);
stmt = SqlStmt_Malloc(sql_handle);
if( SQL_ERROR == SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf)) ||
SQL_ERROR == SqlStmt_Execute(stmt) )
{
SqlStmt_ShowDebug(stmt);
SqlStmt_Free(stmt);
StringBuf_Destroy(&buf);
mapif_itembound_ack(fd, account_id, guild_id);
return 1;
}
SqlStmt_BindColumn(stmt, 0, SQLDT_INT, &item.id, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 1, SQLDT_USHORT, &item.nameid, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 2, SQLDT_SHORT, &item.amount, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 3, SQLDT_USHORT, &item.equip, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 4, SQLDT_CHAR, &item.identify, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 5, SQLDT_CHAR, &item.refine, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 6, SQLDT_CHAR, &item.attribute, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 7, SQLDT_UINT, &item.expire_time, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 8, SQLDT_UINT, &item.bound, 0, NULL, NULL);
for( j = 0; j < MAX_SLOTS; ++j )
SqlStmt_BindColumn(stmt, 9 + j, SQLDT_SHORT, &item.card[j], 0, NULL, NULL);
memset(&items, 0, sizeof(items));
while( SQL_SUCCESS == SqlStmt_NextRow(stmt) )
memcpy(&items[count++], &item, sizeof(struct item));
Sql_FreeResult(sql_handle);
ShowInfo("Found '"CL_WHITE"%d"CL_RESET"' guild bound item(s) from CID = "CL_WHITE"%d"CL_RESET", AID = %d, Guild ID = "CL_WHITE"%d"CL_RESET".\n", count, char_id, account_id, guild_id);
if( !count ) { //No items found - No need to continue
StringBuf_Destroy(&buf);
SqlStmt_Free(stmt);
mapif_itembound_ack(fd, account_id, guild_id);
return 1;
}
set_session_flag(account_id, 1);
//Delete bound items from player's inventory
StringBuf_Clear(&buf);
StringBuf_Printf(&buf, "DELETE FROM `%s` WHERE `bound` = %d", inventory_db, BOUND_GUILD);
if( SQL_ERROR == SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf)) ||
SQL_ERROR == SqlStmt_Execute(stmt) )
{
SqlStmt_ShowDebug(stmt);
SqlStmt_Free(stmt);
StringBuf_Destroy(&buf);
mapif_itembound_ack(fd, account_id, guild_id);
return 1;
}
//Send the deleted items to map-server to store them in guild storage [Cydh]
mapif_itembound_store2gstorage(fd, guild_id, items, count);
//Verifies equip bitmasks (see item.equip) and handles the sql statement
#define CHECK_REMOVE(var, mask, token, num) {\
if( (var)&(mask) && !(j&(num)) ) {\
if( j )\
StringBuf_AppendStr(&buf, ",");\
StringBuf_AppendStr(&buf, "`"#token"`='0'");\
j |= (1<<num);\
}\
}
StringBuf_Clear(&buf);
j = 0;
for( i = 0; i < count && i < MAX_INVENTORY; i++ ) {
if( !&items[i] || !items[i].equip )
continue;
//Equips can be at more than one slot at the same time
CHECK_REMOVE(items[i].equip, EQP_HAND_R, weapon, 0);
CHECK_REMOVE(items[i].equip, EQP_HAND_L, shield, 1);
CHECK_REMOVE(items[i].equip, EQP_HEAD_TOP|EQP_COSTUME_HEAD_TOP, head_top, 2);
CHECK_REMOVE(items[i].equip, EQP_HEAD_MID|EQP_COSTUME_HEAD_MID, head_mid, 3);
CHECK_REMOVE(items[i].equip, EQP_HEAD_LOW|EQP_COSTUME_HEAD_LOW, head_bottom, 4);
CHECK_REMOVE(items[i].equip, EQP_GARMENT|EQP_COSTUME_GARMENT, robe, 5);
}
#undef CHECK_REMOVE
//Update player's view
if( j ) {
StringBuf buf2;
StringBuf_Init(&buf2);
StringBuf_Printf(&buf2, "UPDATE `%s` SET %s WHERE `char_id`='%d'", char_db, StringBuf_Value(&buf), char_id);
if( SQL_ERROR == SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf)) ||
SQL_ERROR == SqlStmt_Execute(stmt) )
{
SqlStmt_ShowDebug(stmt);
SqlStmt_Free(stmt);
StringBuf_Destroy(&buf);
StringBuf_Destroy(&buf2);
mapif_itembound_ack(fd, account_id, guild_id);
return 1;
}
StringBuf_Destroy(&buf2);
}
StringBuf_Destroy(&buf);
SqlStmt_Free(stmt);
unset_session_flag(account_id, 1);
return 0;
}
//------------------------------------------------
//Guild bound items pull for offline characters [Akinari]
//------------------------------------------------
int mapif_parse_itembound_retrieve(int fd)
{
StringBuf buf;
SqlStmt* stmt;
struct item item;
int j, i=0, s;
bool found=false;
struct item items[MAX_INVENTORY];
int char_id = RFIFOL(fd,2);
int aid = RFIFOL(fd,6);
int guild_id = RFIFOW(fd,10);
StringBuf_Init(&buf);
StringBuf_AppendStr(&buf, "SELECT `id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `bound`");
for( j = 0; j < MAX_SLOTS; ++j )
StringBuf_Printf(&buf, ", `card%d`", j);
StringBuf_Printf(&buf, " FROM `%s` WHERE `char_id`='%d'",inventory_db,char_id);
stmt = SqlStmt_Malloc(sql_handle);
if( SQL_ERROR == SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf))
|| SQL_ERROR == SqlStmt_Execute(stmt) )
{
SqlStmt_ShowDebug(stmt);
SqlStmt_Free(stmt);
StringBuf_Destroy(&buf);
mapif_itembound_ack(fd,aid,guild_id);
return 1;
}
SqlStmt_BindColumn(stmt, 0, SQLDT_INT, &item.id, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 1, SQLDT_SHORT, &item.nameid, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 2, SQLDT_SHORT, &item.amount, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 3, SQLDT_USHORT, &item.equip, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 4, SQLDT_CHAR, &item.identify, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 5, SQLDT_CHAR, &item.refine, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 6, SQLDT_CHAR, &item.attribute, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 7, SQLDT_UINT, &item.expire_time, 0, NULL, NULL);
SqlStmt_BindColumn(stmt, 8, SQLDT_UINT, &item.bound, 0, NULL, NULL);
for( j = 0; j < MAX_SLOTS; ++j )
SqlStmt_BindColumn(stmt, 9+j, SQLDT_SHORT, &item.card[j], 0, NULL, NULL);
while( SQL_SUCCESS == SqlStmt_NextRow(stmt) ) {
if(item.bound == 2) {
memcpy(&items[i],&item,sizeof(struct item));
i++;
}
}
Sql_FreeResult(sql_handle);
if(!i) { //No items found - No need to continue
StringBuf_Destroy(&buf);
SqlStmt_Free(stmt);
mapif_itembound_ack(fd,aid,guild_id);
return 0;
}
//First we delete the character's items
StringBuf_Clear(&buf);
StringBuf_Printf(&buf, "DELETE FROM `%s` WHERE",inventory_db);
for(j=0; j<i; j++) {
if( found )
StringBuf_AppendStr(&buf, " OR");
else
found = true;
StringBuf_Printf(&buf, " `id`=%d",items[j].id);
}
if( SQL_ERROR == SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf))
|| SQL_ERROR == SqlStmt_Execute(stmt) )
{
SqlStmt_ShowDebug(stmt);
SqlStmt_Free(stmt);
StringBuf_Destroy(&buf);
mapif_itembound_ack(fd,aid,guild_id);
return 1;
}
//Now let's update the guild storage with those deleted items
found = false;
StringBuf_Clear(&buf);
StringBuf_Printf(&buf, "INSERT INTO `%s` (`guild_id`, `nameid`, `amount`, `identify`, `refine`, `attribute`, `expire_time`, `bound`", guild_storage_db);
for( j = 0; j < MAX_SLOTS; ++j )
StringBuf_Printf(&buf, ", `card%d`", j);
StringBuf_AppendStr(&buf, ") VALUES ");
for( j = 0; j < i; ++j ) {
if( found )
StringBuf_AppendStr(&buf, ",");
else
found = true;
StringBuf_Printf(&buf, "('%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d'",
guild_id, items[j].nameid, items[j].amount, items[j].identify, items[j].refine, items[j].attribute, items[j].expire_time, items[j].bound);
for( s = 0; s < MAX_SLOTS; ++s )
StringBuf_Printf(&buf, ", '%d'", items[j].card[s]);
StringBuf_AppendStr(&buf, ")");
}
if( SQL_ERROR == SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf))
|| SQL_ERROR == SqlStmt_Execute(stmt) )
{
SqlStmt_ShowDebug(stmt);
SqlStmt_Free(stmt);
StringBuf_Destroy(&buf);
mapif_itembound_ack(fd,aid,guild_id);
return 1;
}
StringBuf_Destroy(&buf);
SqlStmt_Free(stmt);
//Finally reload storage and tell map we're done
mapif_load_guild_storage(fd,aid,guild_id,0);
mapif_itembound_ack(fd,aid,guild_id);
return 0;
}
/// Stores a single message in the database.
/// Returns the message's ID if successful (or 0 if it fails).
int mail_savemessage(struct mail_message* msg)
{
StringBuf buf;
SqlStmt* stmt;
int i, j;
bool found = false;
// build message save query
StringBuf_Init(&buf);
StringBuf_Printf(&buf, "INSERT INTO `%s` (`send_name`, `send_id`, `dest_name`, `dest_id`, `title`, `message`, `time`, `status`, `zeny`,`type`", schema_config.mail_db);
StringBuf_Printf(&buf, ") VALUES (?, '%d', ?, '%d', ?, ?, '%lu', '%d', '%d', '%d'", msg->send_id, msg->dest_id, (unsigned long)msg->timestamp, msg->status, msg->zeny, msg->type);
StringBuf_AppendStr(&buf, ")");
// prepare and execute query
stmt = SqlStmt_Malloc(sql_handle);
if( SQL_SUCCESS != SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf))
|| SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_STRING, msg->send_name, strnlen(msg->send_name, NAME_LENGTH))
|| SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, msg->dest_name, strnlen(msg->dest_name, NAME_LENGTH))
|| SQL_SUCCESS != SqlStmt_BindParam(stmt, 2, SQLDT_STRING, msg->title, strnlen(msg->title, MAIL_TITLE_LENGTH))
|| SQL_SUCCESS != SqlStmt_BindParam(stmt, 3, SQLDT_STRING, msg->body, strnlen(msg->body, MAIL_BODY_LENGTH))
|| SQL_SUCCESS != SqlStmt_Execute(stmt) )
{
SqlStmt_ShowDebug(stmt);
StringBuf_Destroy(&buf);
return msg->id = 0;
} else
msg->id = (int)SqlStmt_LastInsertId(stmt);
SqlStmt_Free(stmt);
StringBuf_Clear(&buf);
StringBuf_Printf(&buf,"INSERT INTO `%s` (`id`, `index`, `amount`, `nameid`, `refine`, `attribute`, `identify`, `unique_id`, `bound`", schema_config.mail_attachment_db);
for (j = 0; j < MAX_SLOTS; j++)
StringBuf_Printf(&buf, ", `card%d`", j);
for (j = 0; j < MAX_ITEM_RDM_OPT; ++j) {
StringBuf_Printf(&buf, ", `option_id%d`", j);
StringBuf_Printf(&buf, ", `option_val%d`", j);
StringBuf_Printf(&buf, ", `option_parm%d`", j);
}
StringBuf_AppendStr(&buf, ") VALUES ");
for( i = 0; i < MAIL_MAX_ITEM; i++ ){
// skip empty and already matched entries
if( msg->item[i].nameid == 0 )
continue;
if( found ){
StringBuf_AppendStr(&buf, ",");
}else{
found = true;
}
StringBuf_Printf(&buf, "('%" PRIu64 "', '%hu', '%d', '%hu', '%d', '%d', '%d', '%" PRIu64 "', '%d'", (uint64)msg->id, i, msg->item[i].amount, msg->item[i].nameid, msg->item[i].refine, msg->item[i].attribute, msg->item[i].identify, msg->item[i].unique_id, msg->item[i].bound);
for (j = 0; j < MAX_SLOTS; j++)
StringBuf_Printf(&buf, ", '%hu'", msg->item[i].card[j]);
for (j = 0; j < MAX_ITEM_RDM_OPT; ++j) {
StringBuf_Printf(&buf, ", '%d'", msg->item[i].option[j].id);
StringBuf_Printf(&buf, ", '%d'", msg->item[i].option[j].value);
StringBuf_Printf(&buf, ", '%d'", msg->item[i].option[j].param);
}
StringBuf_AppendStr(&buf, ")");
}
if( found && SQL_ERROR == Sql_QueryStr(sql_handle, StringBuf_Value(&buf)) ){
Sql_ShowDebug(sql_handle);
}
StringBuf_Destroy(&buf);
return msg->id;
}