ArcEmu: [Applied]Core Development: Reputation Database Storage - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (07 December 2014 - 12:06 PM) Arcemu is in hibernation mode, please read http://arcemu.org/fo...showtopic=26903
dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Resize Shouts Area

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[Applied]Core Development: Reputation Database Storage This patch moves reputation out of characters table and into playerrep

#1 User is offline   Wil 

  • Advanced Member
  • Group: Super Moderator
  • Posts: 224
  • Joined: 31-January 09
  • Gender:Male
  • Server OS:Windows

Posted 02 October 2011 - 11:43 AM

Detailed Explanation:
This patch moves reputation out of the characters table and into a new table playerreputation. With this change it makes it easier for web based developers to use this data. Also it removes the need to parse the field when each character is loaded. I am no expert but in my opinion this improves the speed at which this data is loaded into the core. This patch will also assist in future development of things like character customization and any things else that needs to alter this field.

Tested:
I have tested this by creating a new characters, deleting characters, logging in characters. All seems to be working as intended

Mysql Query:
-- New Table
DROP TABLE IF EXISTS `playerreputation`;

CREATE TABLE `playerreputation` (
  `guid` int(10) NOT NULL,
  `faction` int(10) NOT NULL,
  `flag` int(10) NOT NULL,
  `basestanding` bigint(100) NOT NULL,
  `standing` bigint(100) NOT NULL,
  PRIMARY KEY (`guid`,`faction`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

DROP PROCEDURE IF EXISTS `insert_rep_row`;
DROP PROCEDURE IF EXISTS `parse_and_insert_rep_rows`;
DROP PROCEDURE IF EXISTS `convert_rep`;

DELIMITER $

CREATE PROCEDURE `insert_rep_row` ( `in_guid` INTEGER, `in_faction` INTEGER, `in_flag` INTEGER, `in_basestanding` INTEGER, `in_standing` INTEGER )
BEGIN
	INSERT INTO `playerreputation` VALUE (`in_guid`, `in_faction`, `in_flag`, `in_basestanding`, `in_standing`);
END $

DELIMITER ;


DELIMITER $

CREATE PROCEDURE `parse_and_insert_rep_rows` ( `in_index` INTEGER, `in_string` TEXT)
BEGIN
	
	DECLARE `prevpos` INTEGER;
	DECLARE `currpos` INTEGER;
	DECLARE `str_len` INTEGER;
	DECLARE `in_delim` CHAR;
	DECLARE `faction` VARCHAR(10);
	DECLARE `flag` VARCHAR(10);
	DECLARE `basestanding` VARCHAR(10);
	DECLARE `standing` VARCHAR(10);
	DECLARE `startpos` INTEGER;
	DECLARE `substrlen` INTEGER;
	DECLARE `currchar` CHAR;
	DECLARE `counts` INTEGER;
	
	SET `in_delim` = ',';
	SET `prevpos` = '0';
	SET `counts` = '1';
	SET `currpos` = LOCATE( `in_delim`, `in_string` );
	SET `str_len` = LENGTH( `in_string` );
		
	IF `currpos` != '0' THEN
		WHILE `currpos` <= `str_len` DO
		
				SET `currchar` = SUBSTR( `in_string`, `currpos`, 1);
						
				IF `currchar` = `in_delim` THEN
					SET `startpos` = `prevpos` + '1';
					SET `substrlen` = `currpos` - `prevpos` - '1';
					
					IF `counts` = '1' THEN
						SET `faction` = SUBSTR( `in_string`, `startpos`, `substrlen` );
						SET `counts` = '2';
						SET `prevpos` = `currpos`;
					ELSE
						IF `counts` = '2' THEN
							SET `flag` = SUBSTR( `in_string`, `startpos`, `substrlen` );
							SET `counts` = '3';
							SET `prevpos` = `currpos`;							
						ELSE
							IF `counts` = '3' THEN
								SET `basestanding` = SUBSTR( `in_string`, `startpos`, `substrlen` );
								SET `counts` = '4';
								SET `prevpos` = `currpos`;
							ELSE
								IF `counts` = '4' THEN
									SET `standing` = SUBSTR( `in_string`, `startpos`, `substrlen` );
									SET `counts` = '1';
									CALL `insert_rep_row`( `in_index`, `faction`, `flag`, `basestanding`, `standing` );
									SET `prevpos` = `currpos`;
								END IF;
							END IF;
						END IF;
					END IF;
				END IF;
				
				SET `currpos` = `currpos` + '1';
									
		END WHILE;
	END IF;
	
	
END$
	
DELIMITER ;
	
DELIMITER $
	
CREATE PROCEDURE `convert_rep`()
BEGIN
	DECLARE `ci` INTEGER;
	DECLARE `no_more_rows` INTEGER;
	DECLARE `myfield` TEXT;
       	
	DECLARE `c` CURSOR FOR
	SELECT `guid` FROM `characters`;
               	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET `no_more_rows` = '1';
	
	SET `no_more_rows` = '0';
   
	OPEN `c`;
   
	FETCH `c` INTO `ci`;
   
	WHILE `no_more_rows` = '0' DO
		SELECT `reputation` INTO `myfield` FROM `characters` WHERE `guid` = `ci`;
		CALL `parse_and_insert_rep_rows`( `ci`, `myfield` );
		FETCH `c` INTO `ci`;
	END WHILE;
   	
	CLOSE `c`;
END$

DELIMITER ;
	
CALL `convert_rep`();

DROP PROCEDURE IF EXISTS `insert_rep_row`;
DROP PROCEDURE IF EXISTS `parse_and_insert_rep_rows`;
DROP PROCEDURE IF EXISTS `convert_rep`;

ALTER TABLE `characters` DROP COLUMN `reputation`



Core Patch:
Index: src/arcemu-world/CharacterHandler.cpp
===================================================================
--- src/arcemu-world/CharacterHandler.cpp	(revision 4559)
+++ src/arcemu-world/CharacterHandler.cpp	(working copy)
@@ -559,6 +559,7 @@
 		CharacterDatabase.Execute("DELETE FROM playerspells WHERE GUID = '%u'", guid);
 		CharacterDatabase.Execute("DELETE FROM playerdeletedspells WHERE GUID = '%u'", guid);
 		CharacterDatabase.Execute("DELETE FROM playerskills WHERE GUID = '%u'", guid);
+		CharacterDatabase.Execute("DELETE FROM playerreputation WHERE guid = '%u'", guid);
 
 		/* remove player info */
 		objmgr.DeletePlayerInfo((uint32)guid);
Index: src/arcemu-world/Player.cpp
===================================================================
--- src/arcemu-world/Player.cpp	(revision 4559)
+++ src/arcemu-world/Player.cpp	(working copy)
@@ -2487,13 +2487,7 @@
 
 	SaveDeletedSpells(bNewCharacter, buf);
 
-	// Dump reputation data
-	ReputationMap::iterator iter = m_reputation.begin();
-	for(; iter != m_reputation.end(); ++iter)
-	{
-		ss << int32(iter->first) << "," << int32(iter->second->flag) << "," << int32(iter->second->baseStanding) << "," << int32(iter->second->standing) << ",";
-	}
-	ss << "','";
+	SaveReputation(bNewCharacter, buf);
 
 	// Add player action bars
 	for(uint8 s = 0; s < MAX_SPEC_COUNT; ++s)
@@ -2742,7 +2736,7 @@
 		return;
 	}
 
-	const uint32 fieldcount = 90;
+	const uint32 fieldcount = 89;
 
 	if(result->GetFieldCount() != fieldcount)
 	{
@@ -3085,58 +3079,8 @@
 
 	LoadDeletedSpells(results[ 13 ].result);
 
-	// Load Reputatation CSV Data
-	start = (char*) get_next_field.GetString();
-	FactionDBC* factdbc ;
-	FactionReputation* rep;
-	uint32 id;
-	int32 basestanding;
-	int32 standing;
-	uint32 fflag;
-	while(true)
-	{
-		end = strchr(start, ',');
-		if(!end)break;
-		*end = 0;
-		id = atol(start);
-		start = end + 1;
+	LoadReputation();
 
-		end = strchr(start, ',');
-		if(!end)break;
-		*end = 0;
-		fflag = atol(start);
-		start = end + 1;
-
-		end = strchr(start, ',');
-		if(!end)break;
-		*end = 0;
-		basestanding = atoi(start);//atol(start);
-		start = end + 1;
-
-		end = strchr(start, ',');
-		if(!end)break;
-		*end = 0;
-		standing  = atoi(start);// atol(start);
-		start = end + 1;
-
-		// listid stuff
-		factdbc = dbcFaction.LookupEntryForced(id);
-		if(factdbc == NULL || factdbc->RepListId < 0) continue;
-		ReputationMap::iterator rtr = m_reputation.find(id);
-		if(rtr != m_reputation.end())
-			delete rtr->second;
-
-		rep = new FactionReputation;
-		rep->baseStanding = basestanding;
-		rep->standing = standing;
-		rep->flag = static_cast<uint8>(fflag);
-		m_reputation[id] = rep;
-		reputationByListId[factdbc->RepListId] = rep;
-	}
-
-	if(!m_reputation.size())
-		_InitialReputation();
-
 	// Load saved actionbars
 	for(uint8 s = 0; s < MAX_SPEC_COUNT; ++s)
 	{
@@ -10577,18 +10521,15 @@
 
 void Player::save_Reputation()
 {
-	char buffer[10000] = {0};
-	int p = 0;
 
+	CharacterDatabase.Execute("DELETE FROM `playerreputation` WHERE guid = '%u'", m_uint32Values[OBJECT_FIELD_GUID]);
+
 	ReputationMap::iterator iter = m_reputation.begin();
 	for(; iter != m_reputation.end(); ++iter)
 	{
-		p += sprintf(&buffer[p], "%d,%d,%d,%d,",
-	             	iter->first, iter->second->flag, iter->second->baseStanding, iter->second->standing);
+		CharacterDatabase.Execute("INSERT INTO `playerreputation` (guid,faction,flag,basestanding,standing) VALUES ('%u','%u','%u','%u','%u')", m_uint32Values[OBJECT_FIELD_GUID], (uint32)iter->first, (uint32)iter->second->flag, (uint32)iter->second->baseStanding, (uint32)iter->second->standing);
 	}
 
-	ARCEMU_ASSERT(p < 10000);
-	CharacterDatabase.Execute("UPDATE characters SET reputation = '%s' WHERE guid = %u", buffer, m_uint32Values[OBJECT_FIELD_GUID]);
 }
 
 void Player::save_Actions()
@@ -13530,6 +13471,53 @@
 	sHookInterface.OnQuestAccept(this, qst, qst_giver);
 }
 
+bool Player::LoadReputation()
+{
+	FactionDBC* factdbc ;
+	FactionReputation* rep;
+
+	uint32 fguid;
+	uint32 id;
+	int32 basestanding;
+	int32 standing;
+	uint32 fflag;
+	
+	QueryResult* RepResults = CharacterDatabase.Query("SELECT guid, faction, flag, basestanding, standing FROM `playerreputation` WHERE guid = '%u'", (unsigned int)GetLowGUID() );
+
+	if(RepResults)
+	{
+		do
+		{
+			fguid = RepResults->Fetch()[0].GetUInt32();
+			id = RepResults->Fetch()[1].GetUInt32();
+			fflag = RepResults->Fetch()[2].GetUInt32();
+			basestanding = RepResults->Fetch()[3].GetUInt32();
+			standing = RepResults->Fetch()[4].GetUInt32();
+
+			factdbc = dbcFaction.LookupEntryForced(id);
+			if(factdbc == NULL || factdbc->RepListId < 0) continue;
+			ReputationMap::iterator rtr = m_reputation.find(id);
+			if(rtr != m_reputation.end())
+				delete rtr->second;
+
+			rep = new FactionReputation;
+			rep->baseStanding = basestanding;
+			rep->standing = standing;
+			rep->flag = static_cast<uint8>(fflag);
+			m_reputation[id] = rep;
+			reputationByListId[factdbc->RepListId] = rep;
+
+		}
+		while(RepResults->NextRow());
+		delete RepResults;
+	}
+
+	if(!m_reputation.size())
+		_InitialReputation();
+
+	return true;
+}
+
 bool Player::LoadSpells(QueryResult* result)
 {
 	if(result == NULL)
@@ -13553,6 +13541,46 @@
 	return true;
 }
 
+bool Player::SaveReputation(bool NewCharacter, QueryBuffer* buf) 
+{
+	if(!NewCharacter && buf == NULL)
+		return false;
+
+	std::stringstream ds;
+	uint32 guid = GetLowGUID();
+
+	ds << "DELETE FROM playerreputation WHERE guid = '";
+	ds << guid;
+	ds << "';";
+
+	if ( !NewCharacter )
+		buf->AddQueryStr( ds.str() );
+	else
+		CharacterDatabase.ExecuteNA( ds.str().c_str() );
+
+	ReputationMap::iterator iter = m_reputation.begin();
+	for(; iter != m_reputation.end(); ++iter)
+	{
+
+		std::stringstream ss;
+
+		ss << "INSERT INTO `playerreputation` VALUES ('";
+		ss << guid << "','";
+		ss << iter->first << "','";
+		ss << (uint32)iter->second->flag << "','";
+		ss << iter->second->baseStanding << "','";
+		ss << iter->second->standing << "');";
+
+		if(!NewCharacter)
+			buf->AddQueryStr(ss.str());
+		else
+			CharacterDatabase.ExecuteNA(ss.str().c_str());
+
+	}
+
+	return true;
+}
+
 bool Player::SaveSpells(bool NewCharacter, QueryBuffer* buf)
 {
 	if(!NewCharacter && buf == NULL)
Index: src/arcemu-world/Player.h
===================================================================
--- src/arcemu-world/Player.h	(revision 4559)
+++ src/arcemu-world/Player.h	(working copy)
@@ -1501,8 +1501,10 @@
 		bool LoadFromDB(uint32 guid);
 		void LoadFromDBProc(QueryResultVector & results);
 
+		bool LoadReputation();
 		bool LoadSpells(QueryResult* result);
 		bool SaveSpells(bool NewCharacter, QueryBuffer* buf);
+		bool SaveReputation(bool NewCharacter, QueryBuffer* buf);
 
 		bool LoadDeletedSpells(QueryResult* result);
 		bool SaveDeletedSpells(bool NewCharacter, QueryBuffer* buf);



Attached File  ReputationMove.sql (3.46K)
Number of downloads: 1
Attached File  ReputationMove.patch (6.7K)
Number of downloads: 2
Nothing
0

#2 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,189
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Server OS:Linux

Posted 16 October 2011 - 08:54 PM

+   	int32 basestanding;
+   	int32 standing;
.....
+                   	basestanding = RepResults->Fetch()[3].GetUInt32();
+                   	standing = RepResults->Fetch()[4].GetUInt32();


Assigning unsigned value to signed, are you serious?
Although in this case it's not a big deal since the result will be ok, on a simple exam this would be instant fail, without the examiner reading anything else.
Lots of bugs in Arcemu are/were due to such careless / ignorant assignments. I will have to refer you back to a basic book about "The C++ programming language".

Also Arcemu uses an async data loader, which means the database queries are queued, they are all executed, and when it's all loaded the loader methods are called. It is done so, because it's faster to do lots of db work and then pull the result than polling the db, and keeping up the thread while doing so. ( other characters for example could be loaded in the meantime )
In contrast to this, you are doing a db operation in the method you added Player::LoadReputation().
At the very least you could have taken a look at the other db loader methods, and use them as a template.

Otherwise it looks somewhat ok, at first glance.

Often people ask why we are reluctant to review patches, the usual incompetence/carelessness/ignorance is the main reason.

EDIT:
My version so far: http://pastebin.com/rvAkhaER
"The demand for free goods is infinite."
0

#3 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,189
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Server OS:Linux

Posted 17 October 2011 - 11:32 AM

- Applied.
- Moved to applied patches.
"The demand for free goods is infinite."
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users