summaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorsmokexyz <sagunkho@hotmail.com>2017-06-03 16:39:34 +0800
committerSmokexyz <sagunkho@hotmail.com>2017-07-05 23:00:58 +0530
commit7a2447204644b53e64a730c9c8428cc5b33f5aa2 (patch)
tree284e042b6b0bc3f819301fd47281a411f2670a1a /src
parentd2af893049845c4be0710f8939d09ba87485dddc (diff)
downloadhercules-7a2447204644b53e64a730c9c8428cc5b33f5aa2.tar.gz
hercules-7a2447204644b53e64a730c9c8428cc5b33f5aa2.tar.bz2
hercules-7a2447204644b53e64a730c9c8428cc5b33f5aa2.tar.xz
hercules-7a2447204644b53e64a730c9c8428cc5b33f5aa2.zip
Optimisation of char_memitemdata_to_sql()
Implements the storage saving algorithm for inventory, cart and guild storage tables. Total queries to a table in any call would be no more than 4 per call, replacing the original algorithm that could make a lot more through single update queries per call. This significantly reduces the run time speed for saving/loading of item data from the game server.
Diffstat (limited to 'src')
-rw-r--r--src/char/char.c463
-rw-r--r--src/char/char.h3
-rw-r--r--src/char/int_storage.c2
3 files changed, 243 insertions, 225 deletions
diff --git a/src/char/char.c b/src/char/char.c
index f6556073e..b6844dfb5 100644
--- a/src/char/char.c
+++ b/src/char/char.c
@@ -430,7 +430,7 @@ int char_mmo_char_tosql(int char_id, struct mmo_charstatus* p)
//map inventory data
if( memcmp(p->inventory, cp->inventory, sizeof(p->inventory)) ) {
- if (!chr->memitemdata_to_sql(p->inventory, MAX_INVENTORY, p->char_id, TABLE_INVENTORY))
+ if (!chr->memitemdata_to_sql(p->inventory, p->char_id, TABLE_INVENTORY))
strcat(save_status, " inventory");
else
errors++;
@@ -438,7 +438,7 @@ int char_mmo_char_tosql(int char_id, struct mmo_charstatus* p)
//map cart data
if( memcmp(p->cart, cp->cart, sizeof(p->cart)) ) {
- if (!chr->memitemdata_to_sql(p->cart, MAX_CART, p->char_id, TABLE_CART))
+ if (!chr->memitemdata_to_sql(p->cart, p->char_id, TABLE_CART))
strcat(save_status, " cart");
else
errors++;
@@ -696,192 +696,287 @@ int char_mmo_char_tosql(int char_id, struct mmo_charstatus* p)
}
/**
- * Saves an array of 'item' entries into the specified table.
- *
- * @param items The items array.
- * @param max The array size.
- * @param id The character/account/guild ID (depending on tableswitch).
- * @param tableswitch The type of table (@see enum inventory_table_type).
- * @return Error code.
- * @retval 0 in case of success.
+ * Gets a player object's item data from an sql table. [Smokexyz/Hercules]
+ * @param[in|out] items reference to the item list of a character/account/guild.
+ * @param[in] max Max amount of items to be pulled into the list.
+ * @param[in] guid Unique ID of the player object (account_id, char_id, guild_id).
+ * @param[in] table Table to be used for the transaction.
+ * @return -1 on failure or number of items added to the list if successful.
*/
-int char_memitemdata_to_sql(const struct item items[], int max, int id, int tableswitch)
+int char_getitemdata_from_sql(struct item *items, int max, int guid, enum inventory_table_type table)
{
- StringBuf buf;
+ int i = 0;
struct SqlStmt *stmt = NULL;
- int i, j;
const char *tablename = NULL;
const char *selectoption = NULL;
bool has_favorite = false;
+ StringBuf buf;
struct item item = { 0 }; // temp storage variable
- bool *flag = NULL; // bit array for inventory matching
- bool found;
- int errors = 0;
-
- nullpo_ret(items);
- switch (tableswitch) {
- case TABLE_INVENTORY: tablename = inventory_db; selectoption = "char_id"; has_favorite = true; break;
- case TABLE_CART: tablename = cart_db; selectoption = "char_id"; break;
- case TABLE_STORAGE: tablename = storage_db; selectoption = "account_id"; break;
- case TABLE_GUILD_STORAGE: tablename = guild_storage_db; selectoption = "guild_id"; break;
+ nullpo_retr(-1, items);
+ Assert_retr(-1, guid > 0);
+ Assert_retr(-1, max > 0);
+
+ // Initialize the array.
+ memset(items, 0x0, sizeof(struct item) * max);
+
+ switch (table) {
+ case TABLE_INVENTORY:
+ tablename = inventory_db;
+ selectoption = "char_id";
+ has_favorite = true;
+ break;
+ case TABLE_CART:
+ tablename = cart_db;
+ selectoption = "char_id";
+ break;
+ case TABLE_GUILD_STORAGE:
+ tablename = guild_storage_db;
+ selectoption = "guild_id";
+ break;
default:
- ShowError("Invalid table name!\n");
- Assert_retr(1, tableswitch);
+ ShowError("char_getitemdata_from_sql: Invalid table type %d!\n", (int) table);
+ Assert_retr(-1, table);
+ return -1;
}
- // The following code compares inventory with current database values
- // and performs modification/deletion/insertion only on relevant rows.
- // This approach is more complicated than a trivial delete&insert, but
- // it significantly reduces cpu load on the database server.
-
StrBuf->Init(&buf);
StrBuf->AppendStr(&buf, "SELECT `id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `bound`, `unique_id`");
- for (j = 0; j < MAX_SLOTS; ++j)
- StrBuf->Printf(&buf, ", `card%d`", j);
- for (j = 0; j < MAX_ITEM_OPTIONS; ++j)
- StrBuf->Printf(&buf, ", `opt_idx%d`, `opt_val%d`", j, j);
-
+ for(i = 0; i < MAX_SLOTS; i++)
+ StrBuf->Printf(&buf, ", `card%d`", i);
+ for(i = 0; i < MAX_ITEM_OPTIONS; i++)
+ StrBuf->Printf(&buf, ", `opt_idx%d`, `opt_val%d`", i, i);
if (has_favorite)
StrBuf->AppendStr(&buf, ", `favorite`");
- StrBuf->Printf(&buf, " FROM `%s` WHERE `%s`='%d'", tablename, selectoption, id);
+ StrBuf->Printf(&buf, " FROM `%s` WHERE `%s`=?", tablename, selectoption);
stmt = SQL->StmtMalloc(inter->sql_handle);
if (SQL_ERROR == SQL->StmtPrepareStr(stmt, StrBuf->Value(&buf))
- || SQL_ERROR == SQL->StmtExecute(stmt)) {
+ || SQL_ERROR == SQL->StmtBindParam(stmt, 0, SQLDT_INT, &guid, 0)
+ || SQL_ERROR == SQL->StmtExecute(stmt)) {
SqlStmt_ShowDebug(stmt);
SQL->StmtFree(stmt);
StrBuf->Destroy(&buf);
- return 1;
+ return -1;
}
- SQL->StmtBindColumn(stmt, 0, SQLDT_INT, &item.id, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 1, SQLDT_SHORT, &item.nameid, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 2, SQLDT_SHORT, &item.amount, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 3, SQLDT_UINT, &item.equip, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 4, SQLDT_CHAR, &item.identify, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 5, SQLDT_CHAR, &item.refine, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 6, SQLDT_CHAR, &item.attribute, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 7, SQLDT_UINT, &item.expire_time, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 8, SQLDT_UCHAR, &item.bound, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 9, SQLDT_UINT64, &item.unique_id, 0, NULL, NULL);
- for (j = 0; j < MAX_SLOTS; ++j)
- SQL->StmtBindColumn(stmt, 10 + j, SQLDT_SHORT, &item.card[j], 0, NULL, NULL);
- for (j = 0; j < MAX_ITEM_OPTIONS; ++j) {
- SQL->StmtBindColumn(stmt, 10 + MAX_SLOTS + j * 2, SQLDT_INT16, &item.option[j].index, 0, NULL, NULL);
- SQL->StmtBindColumn(stmt, 11 + MAX_SLOTS + j * 2, SQLDT_INT16, &item.option[j].value, 0, NULL, NULL);
+ if (SQL_ERROR == SQL->StmtBindColumn(stmt, 0, SQLDT_INT, &item.id, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 1, SQLDT_SHORT, &item.nameid, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 2, SQLDT_SHORT, &item.amount, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 3, SQLDT_UINT, &item.equip, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 4, SQLDT_CHAR, &item.identify, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 5, SQLDT_CHAR, &item.refine, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 6, SQLDT_CHAR, &item.attribute, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 7, SQLDT_UINT, &item.expire_time, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 8, SQLDT_UCHAR, &item.bound, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 9, SQLDT_UINT64, &item.unique_id, 0, NULL, NULL))
+ {
+ SqlStmt_ShowDebug(stmt);
}
+
+ for (i = 0; i < MAX_SLOTS; i++)
+ if (SQL_ERROR == SQL->StmtBindColumn(stmt, 10 + i, SQLDT_SHORT, &item.card[i], 0, NULL, NULL))
+ SqlStmt_ShowDebug(stmt);
+
+ for (i = 0; i < MAX_ITEM_OPTIONS; i++)
+ if (SQL_ERROR == SQL->StmtBindColumn(stmt, 10 + MAX_SLOTS + i * 2, SQLDT_INT16, &item.option[i].index, 0, NULL, NULL)
+ || SQL_ERROR == SQL->StmtBindColumn(stmt, 11 + MAX_SLOTS + i * 2, SQLDT_INT16, &item.option[i].value, 0, NULL, NULL))
+ SqlStmt_ShowDebug(stmt);
+
if (has_favorite)
- SQL->StmtBindColumn(stmt, 10 + MAX_SLOTS + MAX_ITEM_OPTIONS * 2, SQLDT_UCHAR, &item.favorite, 0, NULL, NULL);
+ if (SQL_ERROR == SQL->StmtBindColumn(stmt, 10 + MAX_SLOTS + MAX_ITEM_OPTIONS * 2, SQLDT_UCHAR, &item.favorite, 0, NULL, NULL))
+ SqlStmt_ShowDebug(stmt);
- // bit array indicating which inventory items have already been matched
- flag = aCalloc(max, sizeof(bool));
+ if (SQL->StmtNumRows(stmt) > 0 ) {
+ i = 0;
+ while (SQL_SUCCESS == SQL->StmtNextRow(stmt) && i < max) {
+ items[i++] = item;
+ }
+ }
- while (SQL_SUCCESS == SQL->StmtNextRow(stmt)) {
- found = false;
- // search for the presence of the item in the char's inventory
- for (i = 0; i < max; ++i) {
- // skip empty and already matched entries
- if (items[i].nameid == 0 || flag[i])
- continue;
+ SQL->StmtFree(stmt);
+ StrBuf->Destroy(&buf);
- if (items[i].nameid == item.nameid
- && items[i].unique_id == item.unique_id
- && items[i].card[0] == item.card[0]
- && items[i].card[2] == item.card[2]
- && items[i].card[3] == item.card[3]
- ) {
- int k = 0;
- // They are the same item.
- ARR_FIND(0, MAX_SLOTS, j, items[i].card[j] != item.card[j]);
- ARR_FIND(0, MAX_ITEM_OPTIONS, k, items[i].option[k].index != item.option[k].index || items[i].option[k].value != item.option[k].value);
-
- if (j == MAX_SLOTS && k == MAX_ITEM_OPTIONS
- && items[i].amount == item.amount
- && items[i].equip == item.equip
- && items[i].identify == item.identify
- && items[i].refine == item.refine
- && items[i].attribute == item.attribute
- && items[i].expire_time == item.expire_time
- && items[i].bound == item.bound
- && (!has_favorite || items[i].favorite == item.favorite)
- ) {
- ; //Do nothing.
- } else {
- // update all fields.
- StrBuf->Clear(&buf);
- StrBuf->Printf(&buf, "UPDATE `%s` SET `amount`='%d', `equip`='%u', `identify`='%d', `refine`='%d',`attribute`='%d', `expire_time`='%u', `bound`='%d'",
- tablename, items[i].amount, items[i].equip, items[i].identify, items[i].refine, items[i].attribute, items[i].expire_time, items[i].bound);
- for (j = 0; j < MAX_SLOTS; ++j)
- StrBuf->Printf(&buf, ", `card%d`='%d'", j, items[i].card[j]);
- for (j = 0; j < MAX_ITEM_OPTIONS; ++j)
- StrBuf->Printf(&buf, ", `opt_idx%d`='%d', `opt_val%d`='%d'", j, items[i].option[j].index, j, items[i].option[j].value);
- if (has_favorite)
- StrBuf->Printf(&buf, ", `favorite`='%d'", items[i].favorite);
- StrBuf->Printf(&buf, " WHERE `id`='%d' LIMIT 1", item.id);
+ return i;
+}
+
+/**
+ * Saves an array of 'item' entries into the specified table. [Smokexyz/Hercules]
+ * @param[in] items The items array.
+ * @param[in] guid The character/account/guild ID (depending on table).
+ * @param[in] tableswitch The type of table (@see enum inventory_table_type).
+ * @retval -1 in case of failure, or number of changes made within the table.
+ */
+int char_memitemdata_to_sql(const struct item *p_items, int guid, enum inventory_table_type table)
+{
+ StringBuf buf;
+ int i = 0, j = 0;
+ const char *tablename = NULL;
+ const char *selectoption = NULL;
+ bool has_favorite = false;
+ struct item *cp_items = NULL; // temp item storage variable
+ bool *matched_p = NULL;
+ int total_updates = 0, total_deletes = 0, total_inserts = 0, total_changes = 0;
+ int item_count = 0, db_max = 0;
+
+ nullpo_ret(p_items);
+
+ switch (table) {
+ case TABLE_INVENTORY:
+ tablename = inventory_db;
+ selectoption = "char_id";
+ has_favorite = true;
+ item_count = MAX_INVENTORY;
+ break;
+ case TABLE_CART:
+ tablename = cart_db;
+ selectoption = "char_id";
+ item_count = MAX_CART;
+ break;
+ case TABLE_GUILD_STORAGE:
+ tablename = guild_storage_db;
+ selectoption = "guild_id";
+ item_count = MAX_GUILD_STORAGE;
+ break;
+ default:
+ ShowError("Invalid table type %d!\n", (int) table);
+ Assert_retr(-1, table);
+ return -1;
+ }
+
+ cp_items = aCalloc(item_count, sizeof(struct item));
+ matched_p = aCalloc(item_count, sizeof(bool));
+
+ StrBuf->Init(&buf);
+
+ /**
+ * If the storage table is not empty, check for items and replace or delete where needed.
+ */
+ if ((db_max = chr->getitemdata_from_sql(cp_items, item_count, guid, table)) > 0) {
+ int *deletes = aCalloc(db_max, sizeof(struct item));
+
+ for (i = 0; i < db_max; i++) {
+ struct item *cp_it = &cp_items[i];
+
+ ARR_FIND(0, item_count, j,
+ matched_p[j] != true
+ && p_items[j].nameid != 0
+ && cp_it->nameid == p_items[j].nameid
+ && cp_it->unique_id == p_items[j].unique_id
+ && memcmp(p_items[j].card, cp_it->card, sizeof(short) * MAX_SLOTS) == 0
+ && memcmp(p_items[j].option, cp_it->option, 5 * MAX_ITEM_OPTIONS) == 0);
+
+ if (j < item_count) { // Item found.
+ matched_p[j] = true; // Mark the item as matched.
+
+ // If the amount has changed, set for replacement with current item properties.
+ if (memcmp(cp_it, &p_items[j], sizeof(struct item)) != 0) {
+ int k = 0;
+
+ if (total_updates == 0) {
+ StrBuf->Clear(&buf);
+ StrBuf->Printf(&buf, "REPLACE INTO `%s` (`id`, `%s`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`", tablename, selectoption);
+ for (k = 0; k < MAX_SLOTS; k++)
+ StrBuf->Printf(&buf, ", `card%d`", k);
+ for (k = 0; k < MAX_ITEM_OPTIONS; k++)
+ StrBuf->Printf(&buf, ", `opt_idx%d`, `opt_val%d`", k, k);
+ StrBuf->AppendStr(&buf, ", `expire_time`, `bound`, `unique_id`");
+ if (has_favorite)
+ StrBuf->AppendStr(&buf, ", `favorite`");
+
+ StrBuf->AppendStr(&buf, ") VALUES ");
- if (SQL_ERROR == SQL->QueryStr(inter->sql_handle, StrBuf->Value(&buf))) {
- Sql_ShowDebug(inter->sql_handle);
- errors++;
}
- }
- found = flag[i] = true; //Item dealt with,
- break; //skip to next item in the db.
+ StrBuf->Printf(&buf, "%s('%d', '%d', '%d', '%d', '%u', '%d', '%d', '%d'",
+ total_updates > 0 ? ", " : "", cp_it->id, guid, p_items[j].nameid, p_items[j].amount, p_items[j].equip, p_items[j].identify, p_items[j].refine, p_items[j].attribute);
+ for (k = 0; k < MAX_SLOTS; k++)
+ StrBuf->Printf(&buf, ", '%d'", p_items[j].card[k]);
+ for (k = 0; k < MAX_ITEM_OPTIONS; ++k)
+ StrBuf->Printf(&buf, ", '%d', '%d'", p_items[j].option[k].index, p_items[j].option[k].value);
+ StrBuf->Printf(&buf, ", '%u', '%d', '%"PRIu64"'", p_items[j].expire_time, p_items[j].bound, p_items[j].unique_id);
+ if (has_favorite)
+ StrBuf->Printf(&buf, ", %d", p_items[j].favorite);
+
+ StrBuf->AppendStr(&buf, ")");
+
+ total_updates++;
+ }
+ } else { // Doesn't exist in the table, set for deletion.
+ deletes[total_deletes++] = cp_it->id;
}
}
- if (!found) {
- // Item not present in inventory, remove it.
- if (SQL_ERROR == SQL->Query(inter->sql_handle, "DELETE from `%s` where `id`='%d' LIMIT 1", tablename, item.id)) {
+
+ if (total_updates > 0 && SQL_ERROR == SQL->QueryStr(inter->sql_handle, StrBuf->Value(&buf)))
+ Sql_ShowDebug(inter->sql_handle);
+
+ /**
+ * Handle deletions, if any.
+ */
+ if (total_deletes > 0) {
+ StrBuf->Clear(&buf);
+ StrBuf->Printf(&buf, "DELETE FROM `%s` WHERE `id` IN (", tablename);
+ for (i = 0; i < total_deletes; i++)
+ StrBuf->Printf(&buf, "%s'%d'", i == 0 ? "" : ", ", deletes[i]);
+
+ StrBuf->AppendStr(&buf, ");");
+
+ if (SQL_ERROR == SQL->QueryStr(inter->sql_handle, StrBuf->Value(&buf)))
Sql_ShowDebug(inter->sql_handle);
- errors++;
- }
}
+
+ aFree(deletes);
}
- SQL->StmtFree(stmt);
- StrBuf->Clear(&buf);
- StrBuf->Printf(&buf, "INSERT INTO `%s`(`%s`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `bound`, `unique_id`", tablename, selectoption);
- for (j = 0; j < MAX_SLOTS; ++j)
- StrBuf->Printf(&buf, ", `card%d`", j);
- for (j = 0; j < MAX_ITEM_OPTIONS; ++j)
- StrBuf->Printf(&buf, ", `opt_idx%d`, `opt_val%d`", j, j);
- if (has_favorite)
- StrBuf->AppendStr(&buf, ", `favorite`");
- StrBuf->AppendStr(&buf, ") VALUES ");
+ /**
+ * Check for new items and add if required.
+ */
+ for (i = 0; i < item_count; i++) {
+ const struct item *p_it = &p_items[i];
- found = false;
- // insert non-matched items into the db as new items
- for (i = 0; i < max; ++i) {
- // skip empty and already matched entries
- if (items[i].nameid == 0 || flag[i])
+ if (matched_p[i] || p_it->nameid == 0)
continue;
- if (found)
- StrBuf->AppendStr(&buf, ",");
- else
- found = true;
+ if (total_inserts == 0) {
+ StrBuf->Clear(&buf);
+ StrBuf->Printf(&buf, "INSERT INTO `%s` (`%s`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `bound`, `unique_id`", tablename, selectoption);
+ for (j = 0; j < MAX_SLOTS; ++j)
+ StrBuf->Printf(&buf, ", `card%d`", j);
+ for (j = 0; j < MAX_ITEM_OPTIONS; ++j)
+ StrBuf->Printf(&buf, ", `opt_idx%d`, `opt_val%d`", j, j);
+ if (has_favorite)
+ StrBuf->AppendStr(&buf, ", `favorite`");
+ StrBuf->AppendStr(&buf, ") VALUES ");
+ }
+
+ StrBuf->Printf(&buf, "%s('%d', '%d', '%d', '%u', '%d', '%d', '%d', '%u', '%d', '%"PRIu64"'",
+ total_inserts > 0 ? ", " : "", guid, p_it->nameid, p_it->amount, p_it->equip, p_it->identify, p_it->refine,
+ p_it->attribute, p_it->expire_time, p_it->bound, p_it->unique_id);
- StrBuf->Printf(&buf, "('%d', '%d', '%d', '%u', '%d', '%d', '%d', '%u', '%d', '%"PRIu64"'",
- id, items[i].nameid, items[i].amount, items[i].equip, items[i].identify, items[i].refine, items[i].attribute, items[i].expire_time, items[i].bound, items[i].unique_id);
for (j = 0; j < MAX_SLOTS; ++j)
- StrBuf->Printf(&buf, ", '%d'", items[i].card[j]);
+ StrBuf->Printf(&buf, ", '%d'", p_it->card[j]);
for (j = 0; j < MAX_ITEM_OPTIONS; ++j)
- StrBuf->Printf(&buf, ", '%d', '%d'", items[i].option[j].index, items[i].option[j].value);
+ StrBuf->Printf(&buf, ", '%d', '%d'", p_it->option[j].index, p_it->option[j].value);
+
if (has_favorite)
- StrBuf->Printf(&buf, ", '%d'", items[i].favorite);
+ StrBuf->Printf(&buf, ", '%d'", p_it->favorite);
+
StrBuf->AppendStr(&buf, ")");
+
+ total_inserts++;
}
- if (found && SQL_ERROR == SQL->QueryStr(inter->sql_handle, StrBuf->Value(&buf))) {
+ if (total_inserts > 0 && SQL_ERROR == SQL->QueryStr(inter->sql_handle, StrBuf->Value(&buf)))
Sql_ShowDebug(inter->sql_handle);
- errors++;
- }
StrBuf->Destroy(&buf);
- aFree(flag);
- return errors;
+ aFree(cp_items);
+ aFree(matched_p);
+
+ ShowInfo("%s save complete - guid: %d (replace: %d, insert: %d, delete: %d)\n", tablename, guid, total_updates, total_inserts, total_deletes);
+
+ return total_changes;
}
/**
@@ -1033,16 +1128,14 @@ int char_mmo_chars_fromsql(struct char_session_data* sd, uint8* buf)
//=====================================================================================================
int char_mmo_char_fromsql(int char_id, struct mmo_charstatus* p, bool load_everything)
{
- int i,j;
+ int i = 0;
char t_msg[128] = "";
struct mmo_charstatus* cp;
- StringBuf buf;
struct SqlStmt *stmt;
char last_map[MAP_NAME_LENGTH_EXT];
char save_map[MAP_NAME_LENGTH_EXT];
char point_map[MAP_NAME_LENGTH_EXT];
struct point tmp_point;
- struct item tmp_item;
struct s_skill tmp_skill;
struct s_friend tmp_friend;
#ifdef HOTKEY_SAVING
@@ -1194,89 +1287,13 @@ int char_mmo_char_fromsql(int char_id, struct mmo_charstatus* p, bool load_every
}
strcat(t_msg, " memo");
- //read inventory
- //`inventory` (`id`,`char_id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `card0`, `card1`, `card2`, `card3`, `expire_time`, `favorite`, `bound`, `unique_id`)
- StrBuf->Init(&buf);
- StrBuf->AppendStr(&buf, "SELECT `id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `favorite`, `bound`, `unique_id`");
- for (i = 0; i < MAX_SLOTS; ++i)
- StrBuf->Printf(&buf, ", `card%d`", i);
- for (i = 0; i < MAX_ITEM_OPTIONS; ++i)
- StrBuf->Printf(&buf, ", `opt_idx%d`, `opt_val%d`", i, i);
- StrBuf->Printf(&buf, " FROM `%s` WHERE `char_id`=? LIMIT %d", inventory_db, MAX_INVENTORY);
-
- memset(&tmp_item, 0, sizeof(tmp_item));
- if (SQL_ERROR == SQL->StmtPrepareStr(stmt, StrBuf->Value(&buf))
- || SQL_ERROR == SQL->StmtBindParam(stmt, 0, SQLDT_INT, &char_id, 0)
- || SQL_ERROR == SQL->StmtExecute(stmt)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 0, SQLDT_INT, &tmp_item.id, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 1, SQLDT_SHORT, &tmp_item.nameid, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 2, SQLDT_SHORT, &tmp_item.amount, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 3, SQLDT_UINT, &tmp_item.equip, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 4, SQLDT_CHAR, &tmp_item.identify, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 5, SQLDT_CHAR, &tmp_item.refine, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 6, SQLDT_CHAR, &tmp_item.attribute, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 7, SQLDT_UINT, &tmp_item.expire_time, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 8, SQLDT_CHAR, &tmp_item.favorite, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 9, SQLDT_UCHAR, &tmp_item.bound, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 10, SQLDT_UINT64, &tmp_item.unique_id, 0, NULL, NULL)
- )
- SqlStmt_ShowDebug(stmt);
- /* Card Slots */
- for (i = 0; i < MAX_SLOTS; ++i)
- if (SQL_ERROR == SQL->StmtBindColumn(stmt, 11 + i, SQLDT_SHORT, &tmp_item.card[i], 0, NULL, NULL))
- SqlStmt_ShowDebug(stmt);
- /* Item Options */
- for (i = 0; i < MAX_ITEM_OPTIONS; i++)
- if (SQL_ERROR == SQL->StmtBindColumn(stmt, 11 + MAX_SLOTS + i * 2, SQLDT_INT16, &tmp_item.option[i].index, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 12 + MAX_SLOTS + i * 2, SQLDT_INT16, &tmp_item.option[i].value, 0, NULL, NULL))
- SqlStmt_ShowDebug(stmt);
-
- for( i = 0; i < MAX_INVENTORY && SQL_SUCCESS == SQL->StmtNextRow(stmt); ++i )
- memcpy(&p->inventory[i], &tmp_item, sizeof(tmp_item));
-
- strcat(t_msg, " inventory");
+ /* read inventory [Smokexyz/Hercules] */
+ if (chr->getitemdata_from_sql(p->inventory, MAX_INVENTORY, p->char_id, TABLE_INVENTORY) > 0)
+ strcat(t_msg, " inventory");
- //read cart
- //`cart_inventory` (`id`,`char_id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `card0`, `card1`, `card2`, `card3`, expire_time`, `bound`, `unique_id`)
- StrBuf->Clear(&buf);
- StrBuf->AppendStr(&buf, "SELECT `id`, `nameid`, `amount`, `equip`, `identify`, `refine`, `attribute`, `expire_time`, `bound`, `unique_id`");
- for (j = 0; j < MAX_SLOTS; ++j)
- StrBuf->Printf(&buf, ", `card%d`", j);
- for (j = 0; j < MAX_ITEM_OPTIONS; ++j)
- StrBuf->Printf(&buf, ", `opt_idx%d`, `opt_val%d`", j, j);
- StrBuf->Printf(&buf, " FROM `%s` WHERE `char_id`=? LIMIT %d", cart_db, MAX_CART);
-
- memset(&tmp_item, 0, sizeof(tmp_item));
- if (SQL_ERROR == SQL->StmtPrepareStr(stmt, StrBuf->Value(&buf))
- || SQL_ERROR == SQL->StmtBindParam(stmt, 0, SQLDT_INT, &char_id, 0)
- || SQL_ERROR == SQL->StmtExecute(stmt)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 0, SQLDT_INT, &tmp_item.id, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 1, SQLDT_SHORT, &tmp_item.nameid, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 2, SQLDT_SHORT, &tmp_item.amount, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 3, SQLDT_UINT, &tmp_item.equip, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 4, SQLDT_CHAR, &tmp_item.identify, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 5, SQLDT_CHAR, &tmp_item.refine, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 6, SQLDT_CHAR, &tmp_item.attribute, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 7, SQLDT_UINT, &tmp_item.expire_time, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 8, SQLDT_UCHAR, &tmp_item.bound, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 9, SQLDT_UINT64, &tmp_item.unique_id, 0, NULL, NULL)
- ) {
- SqlStmt_ShowDebug(stmt);
- }
- /* Card Slots */
- for (i = 0; i < MAX_SLOTS; ++i)
- if( SQL_ERROR == SQL->StmtBindColumn(stmt, 10 + i, SQLDT_SHORT, &tmp_item.card[i], 0, NULL, NULL) )
- SqlStmt_ShowDebug(stmt);
- /* Item Options */
- for (i = 0; i < MAX_ITEM_OPTIONS; ++i)
- if (SQL_ERROR == SQL->StmtBindColumn(stmt, 10 + MAX_SLOTS + i * 2, SQLDT_INT16, &tmp_item.option[i].index, 0, NULL, NULL)
- || SQL_ERROR == SQL->StmtBindColumn(stmt, 11 + MAX_SLOTS + i * 2, SQLDT_INT16, &tmp_item.option[i].value, 0, NULL, NULL))
- SqlStmt_ShowDebug(stmt);
-
- for( i = 0; i < MAX_CART && SQL_SUCCESS == SQL->StmtNextRow(stmt); ++i )
- memcpy(&p->cart[i], &tmp_item, sizeof(tmp_item));
-
- strcat(t_msg, " cart");
+ /* read cart [Smokexyz/Hercules] */
+ if (chr->getitemdata_from_sql(p->cart, MAX_CART, p->char_id, TABLE_CART) > 0)
+ strcat(t_msg, " cart");
//read skill
//`skill` (`char_id`, `id`, `lv`)
@@ -1367,7 +1384,6 @@ int char_mmo_char_fromsql(int char_id, struct mmo_charstatus* p, bool load_every
if (chr->show_save_log)
ShowInfo("Loaded char (%d - %s): %s\n", char_id, p->name, t_msg); //ok. all data load successfully!
SQL->StmtFree(stmt);
- StrBuf->Destroy(&buf);
/* load options into proper vars */
if( opt & OPT_ALLOW_PARTY )
@@ -6432,6 +6448,7 @@ void char_defaults(void)
chr->create_charstatus = char_create_charstatus;
chr->mmo_char_tosql = char_mmo_char_tosql;
chr->memitemdata_to_sql = char_memitemdata_to_sql;
+ chr->getitemdata_from_sql = char_getitemdata_from_sql;
chr->mmo_gender = char_mmo_gender;
chr->mmo_chars_fromsql = char_mmo_chars_fromsql;
chr->mmo_char_fromsql = char_mmo_char_fromsql;
diff --git a/src/char/char.h b/src/char/char.h
index 499b633f7..6b081e536 100644
--- a/src/char/char.h
+++ b/src/char/char.h
@@ -139,7 +139,8 @@ struct char_interface {
void (*set_all_offline_sql) (void);
struct DBData (*create_charstatus) (union DBKey key, va_list args);
int (*mmo_char_tosql) (int char_id, struct mmo_charstatus* p);
- int (*memitemdata_to_sql) (const struct item items[], int max, int id, int tableswitch);
+ int (*getitemdata_from_sql) (struct item *items, int max, int guid, enum inventory_table_type table);
+ int (*memitemdata_to_sql) (const struct item items[], int id, enum inventory_table_type table);
int (*mmo_gender) (const struct char_session_data *sd, const struct mmo_charstatus *p, char sex);
int (*mmo_chars_fromsql) (struct char_session_data* sd, uint8* buf);
int (*mmo_char_fromsql) (int char_id, struct mmo_charstatus* p, bool load_everything);
diff --git a/src/char/int_storage.c b/src/char/int_storage.c
index 65301127f..aafba8520 100644
--- a/src/char/int_storage.c
+++ b/src/char/int_storage.c
@@ -238,7 +238,7 @@ int inter_storage_fromsql(int account_id, struct storage_data *p)
int inter_storage_guild_storage_tosql(int guild_id, const struct guild_storage *p)
{
nullpo_ret(p);
- chr->memitemdata_to_sql(p->items, MAX_GUILD_STORAGE, guild_id, TABLE_GUILD_STORAGE);
+ chr->memitemdata_to_sql(p->items, guild_id, TABLE_GUILD_STORAGE);
ShowInfo ("guild storage save to DB - guild: %d\n", guild_id);
return 0;
}