From c90e9f42da90183a0be9c29a11beec7db606d44f Mon Sep 17 00:00:00 2001 From: zephyrus Date: Sun, 2 Mar 2008 19:07:14 +0000 Subject: - New optimizations for mail system and adjust to use it on Auctions. - Added a Sql patch to clear all deleted mails. (There is no need to keep that data) - Added more code for Auctions. Not implemented but just for study. git-svn-id: https://rathena.svn.sourceforge.net/svnroot/rathena/trunk@12278 54d463be-8e91-2dee-dedb-b68131a5f0ec --- Changelog-Trunk.txt | 5 + sql-files/upgrade_svn12278.sql | 1 + src/char_sql/int_auction.c | 225 ++++++++++++++++++++++++++++++++++++++++- src/char_sql/int_mail.c | 45 ++++----- src/char_sql/int_mail.h | 3 + src/common/mmo.h | 8 +- src/map/intif.c | 5 +- 7 files changed, 258 insertions(+), 34 deletions(-) create mode 100644 sql-files/upgrade_svn12278.sql diff --git a/Changelog-Trunk.txt b/Changelog-Trunk.txt index cf09ef960..25cabb3c2 100644 --- a/Changelog-Trunk.txt +++ b/Changelog-Trunk.txt @@ -3,6 +3,11 @@ Date Added AS OF SVN REV. 5091, WE ARE NOW USING TRUNK. ALL UNTESTED BUGFIXES/FEATURES GO INTO TRUNK. IF YOU HAVE A WORKING AND TESTED BUGFIX PUT IT INTO STABLE AS WELL AS TRUNK. +2008/03/02 + * New optimizations for mail system and adjust to use it on Auctions. [Zephyrus] + - Added a Sql patch to clear all deleted mails. (There is no need to keep that data) + - Added more code for Auctions. Not implemented but just for study. + 2008/02/29 * Rev. 12268 Added documentation for Cash shop NPC heading. [L0ne_W0lf] 2008/02/28 diff --git a/sql-files/upgrade_svn12278.sql b/sql-files/upgrade_svn12278.sql new file mode 100644 index 000000000..0942904f4 --- /dev/null +++ b/sql-files/upgrade_svn12278.sql @@ -0,0 +1 @@ +DELETE FROM `mail` WHERE `status` > 2; diff --git a/src/char_sql/int_auction.c b/src/char_sql/int_auction.c index a3213d647..5d2a7fa9e 100644 --- a/src/char_sql/int_auction.c +++ b/src/char_sql/int_auction.c @@ -8,25 +8,242 @@ #include "../common/socket.h" #include "../common/strlib.h" #include "../common/sql.h" +#include "../common/timer.h" #include "char.h" #include "inter.h" +#include "int_mail.h" #include #include #include -static DBMap* auction_db = NULL; // int auction_id -> struct auction* +static DBMap* auction_db_ = NULL; // int auction_id -> struct auction_data* -int inter_auction_sql_init(void) +void auction_delete(struct auction_data *auction); +static int auction_end_timer(int tid, unsigned int tick, int id, int data); + +// Copy Paste from map/mail.c +time_t calc_times(void) +{ + time_t temp = time(NULL); + return mktime(localtime(&temp)); +} + +void inter_auction_save(struct auction_data *auction) +{ + int j; + StringBuf buf; + SqlStmt* stmt; + + if( !auction ) + return; + + StringBuf_Init(&buf); + StringBuf_Printf(&buf, "UPDATE `%s` SET `seller_id` = '%d', `seller_name` = ?, `buyer_id` = '%d', `buyer_name` = ?, `price` = '%d', `buynow` = '%d', `hours` = '%d', `timestamp` = '%d', `nameid` = '%d', `refine` = '%d', `attribute` = '%d'", + auction_db, auction->seller_id, auction->buyer_id, auction->price, auction->buynow, auction->hours, auction->timestamp, auction->item.nameid, auction->item.refine, auction->item.attribute); + for( j = 0; j < MAX_SLOTS; j++ ) + StringBuf_Printf(&buf, ", `card%d` = '%d'", j, auction->item.card[j]); + StringBuf_Printf(&buf, " WHERE `auction_id` = '%d'", auction->auction_id); + + stmt = SqlStmt_Malloc(sql_handle); + if( SQL_SUCCESS != SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf)) + || SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_STRING, auction->seller_name, strnlen(auction->seller_name, NAME_LENGTH)) + || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, auction->buyer_name, strnlen(auction->buyer_name, NAME_LENGTH)) + || SQL_SUCCESS != SqlStmt_Execute(stmt) ) + { + SqlStmt_ShowDebug(stmt); + } + + SqlStmt_Free(stmt); + StringBuf_Destroy(&buf); +} + +static bool auction_create(struct auction_data *auction) { - auction_db = idb_alloc(DB_OPT_RELEASE_DATA); + int j; + StringBuf buf; + SqlStmt* stmt; + + if( !auction ) + return false; + + StringBuf_Init(&buf); + StringBuf_Printf(&buf, "INSERT INTO `%s` (`seller_id`,`seller_name`,`buyer_id`,`buyer_name`,`price`,`buynow`,`hours`,`timestamp`,`nameid`,`refine`,`attribute`"); + for( j = 0; j < MAX_SLOTS; j++ ) + StringBuf_Printf(&buf, ",`card%d`", j); + StringBuf_Printf(&buf, ") VALUES ('%d',?,'%d',?,'%d','%d','%d','%d','%d','%d','%d'", + auction->seller_id, auction->buyer_id, auction->price, auction->buynow, auction->hours, auction->timestamp, auction->item.nameid, auction->item.refine, auction->item.attribute); + for( j = 0; j < MAX_SLOTS; j++ ) + StringBuf_Printf(&buf, ",'%d'", auction->item.card[j]); + StringBuf_AppendStr(&buf, ")"); + stmt = SqlStmt_Malloc(sql_handle); + if( SQL_SUCCESS != SqlStmt_PrepareStr(stmt, StringBuf_Value(&buf)) + || SQL_SUCCESS != SqlStmt_BindParam(stmt, 0, SQLDT_STRING, auction->seller_name, strnlen(auction->seller_name, NAME_LENGTH)) + || SQL_SUCCESS != SqlStmt_BindParam(stmt, 1, SQLDT_STRING, auction->buyer_name, strnlen(auction->buyer_name, NAME_LENGTH)) ) + { + SqlStmt_ShowDebug(stmt); + auction->auction_id = 0; + } + else + { + auction->item.amount = 1; + auction->item.identify = 1; + + auction->auction_id = (unsigned int)SqlStmt_LastInsertId(stmt); + auction->auction_end_timer = add_timer( gettick() + ((auction->timestamp - (unsigned int)calc_times) * 1000) , auction_end_timer, auction->auction_id, 0); + idb_put(auction_db_, auction->auction_id, auction); + } + + SqlStmt_Free(stmt); + StringBuf_Destroy(&buf); + + return (auction->auction_id > 0); +} + +static int auction_end_timer(int tid, unsigned int tick, int id, int data) +{ + struct auction_data *auction; + if( (auction = (struct auction_data *)idb_get(auction_db_, id)) != NULL ) + { + struct mail_message msg; + memset(&msg, 0, sizeof(struct mail_message)); + + msg.send_id = auction->seller_id; + safestrncpy(msg.send_name, auction->seller_name, NAME_LENGTH); + msg.timestamp = (unsigned int)calc_times(); + + if( auction->buyer_id ) + { // Send item to Buyer's Mail (custom messages) + msg.dest_id = auction->buyer_id; + safestrncpy(msg.dest_name, auction->buyer_name, NAME_LENGTH); + safestrncpy(msg.title, "[Auction Winner] Your Item", MAIL_TITLE_LENGTH); + safestrncpy(msg.body, "Thanks, you won the auction!.", MAIL_BODY_LENGTH); + } + else + { // Return item to Seller's Mail (custom messages) + msg.dest_id = auction->seller_id; + safestrncpy(msg.dest_name, auction->seller_name, NAME_LENGTH); + safestrncpy(msg.title, "[Auction Fail] Your Item", MAIL_TITLE_LENGTH); + safestrncpy(msg.body, "Sorry, No one buy your item...", MAIL_BODY_LENGTH); + } + + memcpy(&msg.item, &auction->item, sizeof(struct item)); + + mail_savemessage(&msg); + mapif_Mail_new(&msg); + + if( auction->buyer_id ) + { // Send Money to Seller + memset(&msg, 0, sizeof(struct mail_message)); + + msg.send_id = auction->buyer_id; + safestrncpy(msg.send_name, auction->buyer_name, NAME_LENGTH); + msg.dest_id = auction->seller_id; + safestrncpy(msg.dest_name, auction->seller_name, NAME_LENGTH); + msg.timestamp = (unsigned int)calc_times(); + msg.zeny = auction->price; + + // Custom Messages, need more info + safestrncpy(msg.title, "[Auction] Your Zeny", MAIL_TITLE_LENGTH); + safestrncpy(msg.body, "Thanks, you won the auction!.", MAIL_BODY_LENGTH); + + mail_savemessage(&msg); + mapif_Mail_new(&msg); + } + + auction->auction_end_timer = -1; + auction_delete(auction); + } + + return 0; +} + +void auction_delete(struct auction_data *auction) +{ + unsigned int auction_id = auction->auction_id; + + if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `auction_id` = '%d'", auction_db, auction_id) ) + Sql_ShowDebug(sql_handle); + + if( auction->auction_end_timer != -1 ) + delete_timer(auction->auction_end_timer, auction_end_timer); + + aFree(auction); + idb_remove(auction_db_, auction_id); +} + +void inter_auctions_fromsql(void) +{ + int i; + struct auction_data *auction; + struct item *item; + char *data; + StringBuf buf; + unsigned int tick = gettick(), endtick, now = (unsigned int)calc_times(); + + StringBuf_Init(&buf); + StringBuf_AppendStr(&buf, "SELECT `auction_id`,`seller_id`,`seller_name`,`buyer_id`,`buyer_name`," + "`price`,`buynow`,`hours`,`timestamp`,`nameid`,`refine`,`attribute`"); + for( i = 0; i < MAX_SLOTS; i++ ) + StringBuf_Printf(&buf, ",`card%d`", i); + StringBuf_Printf(&buf, " FROM `%s` ORDER BY `id` DESC", auction_db); + + if( SQL_ERROR == Sql_Query(sql_handle, StringBuf_Value(&buf)) ) + Sql_ShowDebug(sql_handle); + + StringBuf_Destroy(&buf); + + while( SQL_SUCCESS == Sql_NextRow(sql_handle) ) + { + CREATE(auction, struct auction_data, 1); + Sql_GetData(sql_handle, 0, &data, NULL); auction->auction_id = atoi(data); + Sql_GetData(sql_handle, 1, &data, NULL); auction->seller_id = atoi(data); + Sql_GetData(sql_handle, 2, &data, NULL); safestrncpy(auction->seller_name, data, NAME_LENGTH); + Sql_GetData(sql_handle, 3, &data, NULL); auction->buyer_id = atoi(data); + Sql_GetData(sql_handle, 4, &data, NULL); safestrncpy(auction->buyer_name, data, NAME_LENGTH); + Sql_GetData(sql_handle, 5, &data, NULL); auction->price = atoi(data); + Sql_GetData(sql_handle, 6, &data, NULL); auction->buynow = atoi(data); + Sql_GetData(sql_handle, 7, &data, NULL); auction->hours = atoi(data); + Sql_GetData(sql_handle, 8, &data, NULL); auction->timestamp = atoi(data); + + item = &auction->item; + Sql_GetData(sql_handle, 9, &data, NULL); item->nameid = atoi(data); + Sql_GetData(sql_handle,10, &data, NULL); item->refine = atoi(data); + Sql_GetData(sql_handle,11, &data, NULL); item->attribute = atoi(data); + + item->identify = 1; + item->amount = 1; + + for( i = 0; i < MAX_SLOTS; i++ ) + { + Sql_GetData(sql_handle, 12 + i, &data, NULL); + item->card[i] = atoi(data); + } + + if( auction->timestamp > now ) + endtick = ((auction->timestamp - now) * 1000) + tick; + else + endtick = tick + 10000; // 10 Second's to process ended auctions + + auction->auction_end_timer = add_timer(endtick, auction_end_timer, auction->auction_id, 0); + idb_put(auction_db_, auction->auction_id, auction); + } + + Sql_FreeResult(sql_handle); +} + +int inter_auction_sql_init(void) +{ + auction_db_ = idb_alloc(DB_OPT_RELEASE_DATA); + inter_auctions_fromsql(); + return 0; } void inter_auction_sql_final(void) { - auction_db->destroy(auction_db,NULL); + auction_db_->destroy(auction_db_,NULL); return; } diff --git a/src/char_sql/int_mail.c b/src/char_sql/int_mail.c index 20db63d30..9ef9f9fd3 100644 --- a/src/char_sql/int_mail.c +++ b/src/char_sql/int_mail.c @@ -38,8 +38,10 @@ static int mail_fromsql(int char_id, struct mail_data* md) "`zeny`,`amount`,`nameid`,`refine`,`attribute`,`identify`"); for (i = 0; i < MAX_SLOTS; i++) StringBuf_Printf(&buf, ",`card%d`", i); - StringBuf_Printf(&buf, " FROM `%s` WHERE `dest_id`='%d' AND `status` >= %d AND `status` <= %d " - "ORDER BY `id` LIMIT %d", mail_db, char_id, MAIL_NEW, MAIL_READ, MAIL_MAX_INBOX + 1); + + // I keep the `status` < 3 just in case someone forget to apply the sqlfix + StringBuf_Printf(&buf, " FROM `%s` WHERE `dest_id`='%d' AND `status` < 3 ORDER BY `id` LIMIT %d", + mail_db, char_id, MAIL_MAX_INBOX + 1); if( SQL_ERROR == Sql_Query(sql_handle, StringBuf_Value(&buf)) ) Sql_ShowDebug(sql_handle); @@ -102,7 +104,7 @@ static int mail_fromsql(int char_id, struct mail_data* md) /// Stores a single message in the database. /// Returns the message's ID if successful (or 0 if it fails). -static int mail_savemessage(struct mail_message* msg) +int mail_savemessage(struct mail_message* msg) { StringBuf buf; SqlStmt* stmt; @@ -129,15 +131,14 @@ static int mail_savemessage(struct mail_message* msg) || SQL_SUCCESS != SqlStmt_Execute(stmt) ) { SqlStmt_ShowDebug(stmt); - j = 0; + msg->id = 0; } else - j = (int)SqlStmt_LastInsertId(stmt); + msg->id = (int)SqlStmt_LastInsertId(stmt); SqlStmt_Free(stmt); StringBuf_Destroy(&buf); - // return the ID of the new mail - return j; + return msg->id; } /// Retrieves a single message from the database. @@ -292,7 +293,7 @@ static void mapif_parse_Mail_getattach(int fd) static void mapif_Mail_delete(int fd, int char_id, int mail_id) { bool failed = false; - if ( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `status` = '%d' WHERE `id` = '%d'", mail_db, MAIL_DELETED, mail_id) ) + if ( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `id` = '%d'", mail_db, mail_id) ) { Sql_ShowDebug(sql_handle); failed = true; @@ -314,21 +315,19 @@ static void mapif_parse_Mail_delete(int fd) /*========================================== * Report New Mail to Map Server *------------------------------------------*/ -static void mapif_Mail_new(int mail_id) +void mapif_Mail_new(struct mail_message *msg) { - struct mail_message msg; + unsigned char buf[74]; + + if( !msg || !msg->id ) + return; - if( mail_loadmessage(mail_id, &msg) ) - { - unsigned char buf[74]; - - WBUFW(buf,0) = 0x3849; - WBUFL(buf,2) = msg.dest_id; - WBUFL(buf,6) = mail_id; - memcpy(WBUFP(buf,10), msg.send_name, NAME_LENGTH); - memcpy(WBUFP(buf,34), msg.title, MAIL_TITLE_LENGTH); - mapif_sendall(buf, 74); - } + WBUFW(buf,0) = 0x3849; + WBUFL(buf,2) = msg->dest_id; + WBUFL(buf,6) = msg->id; + memcpy(WBUFP(buf,10), msg->send_name, NAME_LENGTH); + memcpy(WBUFP(buf,34), msg->title, MAIL_TITLE_LENGTH); + mapif_sendall(buf, 74); } /*========================================== @@ -343,7 +342,7 @@ static void mapif_Mail_return(int fd, int char_id, int mail_id) { if( msg.dest_id != char_id) return; - else if( SQL_ERROR == Sql_Query(sql_handle, "UPDATE `%s` SET `status` = '%d' WHERE `id` = '%d'", mail_db, MAIL_RETURNED, mail_id) ) + else if( SQL_ERROR == Sql_Query(sql_handle, "DELETE FROM `%s` WHERE `id` = '%d'", mail_db, mail_id) ) Sql_ShowDebug(sql_handle); else { @@ -363,7 +362,7 @@ static void mapif_Mail_return(int fd, int char_id, int mail_id) msg.timestamp = (unsigned int)calc_times(); new_mail = mail_savemessage(&msg); - mapif_Mail_new(new_mail); + mapif_Mail_new(&msg); } } diff --git a/src/char_sql/int_mail.h b/src/char_sql/int_mail.h index 2bd7ca0c5..8496f61fb 100644 --- a/src/char_sql/int_mail.h +++ b/src/char_sql/int_mail.h @@ -9,4 +9,7 @@ int inter_mail_parse_frommap(int fd); int inter_mail_sql_init(void); void inter_mail_sql_final(void); +int mail_savemessage(struct mail_message* msg); +void mapif_Mail_new(struct mail_message *msg); + #endif /* _INT_MAIL_SQL_H_ */ diff --git a/src/common/mmo.h b/src/common/mmo.h index dc5931166..2ffcc33c8 100644 --- a/src/common/mmo.h +++ b/src/common/mmo.h @@ -244,9 +244,6 @@ enum mail_status { MAIL_NEW, MAIL_UNREAD, MAIL_READ, - MAIL_DELETED, - MAIL_RETURNED, - MAIL_INVALID, }; struct mail_message { @@ -283,9 +280,8 @@ struct auction_data { unsigned short hours; unsigned int price, buynow; - unsigned int timestamp; - - bool changed; // To know if data have been changed for save + unsigned int timestamp; // auction's end time + int auction_end_timer; }; struct registry { diff --git a/src/map/intif.c b/src/map/intif.c index e04710218..b15987018 100644 --- a/src/map/intif.c +++ b/src/map/intif.c @@ -1591,11 +1591,14 @@ int intif_parse_Mail_return(int fd) { int i; ARR_FIND(0, MAIL_MAX_INBOX, i, sd->mail.inbox.msg[i].id == mail_id); - if (i < MAIL_MAX_INBOX) + if( i < MAIL_MAX_INBOX ) { memset(&sd->mail.inbox.msg[i], 0, sizeof(struct mail_message)); sd->mail.inbox.amount--; } + + if( sd->mail.inbox.full ) + intif_Mail_requestinbox(sd->status.char_id, 1); // Free space is available for new mails } clif_Mail_return(sd->fd, mail_id, fail); -- cgit v1.2.3-60-g2f50