Character retrieval from db

Rising ourselves by our own hair.
Post Reply
starkravingmad
Posts: 24
Joined: Sun Apr 20, 2008 2:40 am

Post by starkravingmad » Tue Nov 21, 2006 9:40 pm

First a few notes. The readconfig function doesn't seem to be working correctly. I had to remove the port from the AuthServer listen_address in local_1.cfg. But since the port is hardcoded it doesn't matter. CharacterDatabase::fill was trying to access the AdminServer. Fill was sending the ID and trying to set the ID and Max_Slots. I took out the query to the AdminDatabase, and since the ID was already handy I passed it along. I didn't fix the Max_Slots. Also, this code only retrieves 1 character from the db, it doesn't loop through the list yet. If anyone needs a sample charcter to test the code, just let me know.

[code:1:fc85751c25]
Index: AdminServer/sql/cohemu.sql
===================================================================
--- AdminServer/sql/cohemu.sql (revision 298)
+++ AdminServer/sql/cohemu.sql (working copy)
@@ -3,7 +3,8 @@
username character varying(32),
access_level smallint,
creation_date timestamp(6) without time zone DEFAULT now() NOT NULL,
- passw bytea
+ passw bytea,
+ max_slots int4
);
ALTER TABLE public.accounts OWNER TO cohadmin;

Index: AuthServer/src/main.cpp
===================================================================
--- AuthServer/src/main.cpp (revision 298)
+++ AuthServer/src/main.cpp (working copy)
@@ -109,7 +109,7 @@

ACE_LOG_MSG->msg_callback(&t);
set_callbacked_logging();
- ACE_DEBUG((LM_ERROR,ACE_TEXT("main")));
+ ACE_DEBUG((LM_ERROR,ACE_TEXT("Main\\n")));
/*
AuthServerTP server_pool;
server_pool.activate(THR_NEW_LWP|THR_JOINABLE,4);
Index: GameServer/sql/cohemu_game.sql
===================================================================
--- GameServer/sql/cohemu_game.sql (revision 0)
+++ GameServer/sql/cohemu_game.sql (revision 0)
@@ -0,0 +1,110 @@
+CREATE TABLE characters (
+ id serial NOT NULL,
+ account_id integer,
+ slot_index integer,
+ level int2,
+ char_name character varying(32),
+ creation_date timestamp(6) without time zone DEFAULT now() NOT NULL,
+ archetype character varying(32),
+ origin character varying(16),
+ villain bool,
+ bodytype int2,
+ face_bits integer,
+ current_map character varying(48),
+ lastcostume int2,
+ unkn1 float,
+ unkn2 float,
+ unkn3 float,
+ unkn4 float
+);
+
+ALTER TABLE public.characters OWNER TO cohadmin;
+
+ALTER TABLE ONLY characters
+ ADD CONSTRAINT characters_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY characters
+ ADD CONSTRAINT characters_char_name_key UNIQUE (char_name);
+
+CREATE TABLE costumes (
+ characterId integer,
+ costumeSlot int2,
+ parts00a character varying(48),
+ parts00b character varying(48),
+ parts00c character varying(48),
+ parts00d character varying(48),
+ parts00e character varying(10),
+ parts00f character varying(10),
+ parts01a character varying(48),
+ parts01b character varying(48),
+ parts01c character varying(48),
+ parts01d character varying(48),
+ parts01e character varying(10),
+ parts01f character varying(10),
+ parts02a character varying(48),
+ parts02b character varying(48),
+ parts02c character varying(48),
+ parts02d character varying(48),
+ parts02e character varying(10),
+ parts02f character varying(10),
+ parts03a character varying(48),
+ parts03b character varying(48),
+ parts03c character varying(48),
+ parts03d character varying(48),
+ parts03e character varying(10),
+ parts03f character varying(10),
+ parts04a character varying(48),
+ parts04b character varying(48),
+ parts04c character varying(48),
+ parts04d character varying(48),
+ parts04e character varying(10),
+ parts04f character varying(10),
+ parts05a character varying(48),
+ parts05b character varying(48),
+ parts05c character varying(48),
+ parts05d character varying(48),
+ parts05e character varying(10),
+ parts05f character varying(10),
+ parts06a character varying(48),
+ parts06b character varying(48),
+ parts06c character varying(48),
+ parts06d character varying(48),
+ parts06e character varying(10),
+ parts06f character varying(10),
+ parts08a character varying(48),
+ parts08b character varying(48),
+ parts08c character varying(48),
+ parts08d character varying(48),
+ parts08e character varying(10),
+ parts08f character varying(10),
+ parts09a character varying(48),
+ parts09b character varying(48),
+ parts09c character varying(48),
+ parts09d character varying(48),
+ parts09e character varying(10),
+ parts09f character varying(10),
+ parts10a character varying(48),
+ parts10b character varying(48),
+ parts10c character varying(48),
+ parts10d character varying(48),
+ parts10e character varying(10),
+ parts10f character varying(10),
+ parts13a character varying(48),
+ parts13b character varying(48),
+ parts13c character varying(48),
+ parts13d character varying(48),
+ parts13e character varying(10),
+ parts13f character varying(10)
+);
+
+ALTER TABLE public.costumes OWNER TO cohadmin;
+ALTER TABLE ONLY costumes
+ ADD CONSTRAINT costumes_pkey PRIMARY KEY (characterId, costumeSlot);
+
+ALTER TABLE ONLY costumes
+ ADD CONSTRAINT costumes_charactersId_fkey FOREIGN KEY (characterId) REFERENCES characters(id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM postgres;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO PUBLIC;
\\ No newline at end of file
Index: GameServer/sql/cohemu_game.sql
===================================================================
--- GameServer/sql/cohemu_game.sql (revision 0)
+++ GameServer/sql/cohemu_game.sql (revision 0)
@@ -0,0 +1,110 @@
+CREATE TABLE characters (
+ id serial NOT NULL,
+ account_id integer,
+ slot_index integer,
+ level int2,
+ char_name character varying(32),
+ creation_date timestamp(6) without time zone DEFAULT now() NOT NULL,
+ archetype character varying(32),
+ origin character varying(16),
+ villain bool,
+ bodytype int2,
+ face_bits integer,
+ current_map character varying(48),
+ lastcostume int2,
+ unkn1 float,
+ unkn2 float,
+ unkn3 float,
+ unkn4 float
+);
+
+ALTER TABLE public.characters OWNER TO cohadmin;
+
+ALTER TABLE ONLY characters
+ ADD CONSTRAINT characters_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY characters
+ ADD CONSTRAINT characters_char_name_key UNIQUE (char_name);
+
+CREATE TABLE costumes (
+ characterId integer,
+ costumeSlot int2,
+ parts00a character varying(48),
+ parts00b character varying(48),
+ parts00c character varying(48),
+ parts00d character varying(48),
+ parts00e character varying(10),
+ parts00f character varying(10),
+ parts01a character varying(48),
+ parts01b character varying(48),
+ parts01c character varying(48),
+ parts01d character varying(48),
+ parts01e character varying(10),
+ parts01f character varying(10),
+ parts02a character varying(48),
+ parts02b character varying(48),
+ parts02c character varying(48),
+ parts02d character varying(48),
+ parts02e character varying(10),
+ parts02f character varying(10),
+ parts03a character varying(48),
+ parts03b character varying(48),
+ parts03c character varying(48),
+ parts03d character varying(48),
+ parts03e character varying(10),
+ parts03f character varying(10),
+ parts04a character varying(48),
+ parts04b character varying(48),
+ parts04c character varying(48),
+ parts04d character varying(48),
+ parts04e character varying(10),
+ parts04f character varying(10),
+ parts05a character varying(48),
+ parts05b character varying(48),
+ parts05c character varying(48),
+ parts05d character varying(48),
+ parts05e character varying(10),
+ parts05f character varying(10),
+ parts06a character varying(48),
+ parts06b character varying(48),
+ parts06c character varying(48),
+ parts06d character varying(48),
+ parts06e character varying(10),
+ parts06f character varying(10),
+ parts08a character varying(48),
+ parts08b character varying(48),
+ parts08c character varying(48),
+ parts08d character varying(48),
+ parts08e character varying(10),
+ parts08f character varying(10),
+ parts09a character varying(48),
+ parts09b character varying(48),
+ parts09c character varying(48),
+ parts09d character varying(48),
+ parts09e character varying(10),
+ parts09f character varying(10),
+ parts10a character varying(48),
+ parts10b character varying(48),
+ parts10c character varying(48),
+ parts10d character varying(48),
+ parts10e character varying(10),
+ parts10f character varying(10),
+ parts13a character varying(48),
+ parts13b character varying(48),
+ parts13c character varying(48),
+ parts13d character varying(48),
+ parts13e character varying(10),
+ parts13f character varying(10)
+);
+
+ALTER TABLE public.costumes OWNER TO cohadmin;
+ALTER TABLE ONLY costumes
+ ADD CONSTRAINT costumes_pkey PRIMARY KEY (characterId, costumeSlot);
+
+ALTER TABLE ONLY costumes
+ ADD CONSTRAINT costumes_charactersId_fkey FOREIGN KEY (characterId) REFERENCES characters(id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM postgres;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO PUBLIC;
\\ No newline at end of file
Index: GameServer/src/CharacterClient.cpp
===================================================================
--- GameServer/src/CharacterClient.cpp (revision 298)
+++ GameServer/src/CharacterClient.cpp (working copy)
@@ -26,26 +26,16 @@
Character *act;
CharacterDatabase * m_db = m_handler->getDb();
m_characters.resize(m_max_slots);
+
if(m_characters[0]==0)
m_characters[0] = act = new Character;
else
act = m_characters[0]; //reuse existing object
act->setIndex(0);
act->setAccountId(m_game_server_acc_id);
- m_db->fill(act);
- act->m_costume=new CharacterCostume;
- act->m_costume->m_parts.push_back(CostumePart(0,"tight","tights","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(1,"tight","tights","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(2,"v_male_head.geo/geo_head_v_asym_standard",
- "skin_v_head_06","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(3,"smooth","smooth_01","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(4,"smooth","smooth_01","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(5,"","","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(6,"martial_arts_01","style_01a","style_01b","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(8,"","","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(9,"","","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(10,"","","","",0xFF3D0029,0xFF1C67FF,0,0));
- act->m_costume->m_parts.push_back(CostumePart(13,"","","","",0xFF3D0029,0xFF1C67FF,0,0));
+
+ m_db->fill(act); //fills character data including costume from db
+
for(size_t i=1; i<m_characters.size(); i++)
{
if(m_characters) //even more reuse
Index: GameServer/src/CharacterDatabase.cpp
===================================================================
--- GameServer/src/CharacterDatabase.cpp (revision 298)
+++ GameServer/src/CharacterDatabase.cpp (working copy)
@@ -30,6 +30,7 @@
}
bool CharacterDatabase::fill( CharacterClient *c )
{
+ /*
stringstream query;
DbResults results;
ACE_ASSERT(c&&c->getId());
@@ -41,25 +42,36 @@
DbResultRow r=results.getRow(0);
c->setMaxSlots((u8)r.getColInt16("max_slots"));
c->setGameServerAccountId((u64)r.getColInt64("id"));
+ */
+
+ c->setGameServerAccountId((u64)c->getId());
+
+ ACE_ASSERT(c&&c->getId());
+ ACE_DEBUG((LM_INFO,"CharacterClient id: %i\\n",c->getId()));
+ ACE_DEBUG((LM_INFO,"CharacterClient slots: %i\\n",c->getNumSlots()));
return true;
}
#define STR_OR_EMPTY(c) ((c!=0) ? c:"EMPTY")
bool CharacterDatabase::fill( Character *c)
{
+
+ int n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20,n21,n22;
+
stringstream query;
DbResults results;
ACE_ASSERT(c&&c->getAccountId());
- query<<"SELECT * FROM characters WHERE account_id="<<c->getAccountId()<<" AND slot_index="<<(u16)c->getIndex();
+ query<<"SELECT * FROM characters WHERE account_id="<<c->getAccountId();
+
if(!execQuery(query.str(),results))
ACE_ERROR_RETURN((LM_ERROR, ACE_TEXT ("(%P|%t) CharacterDatabase::fill query %s failed. %s.\\n"), query.str().c_str(),results.m_msg),false);
+
if(results.num_rows()==0)
{
c->reset(); // empty slot
return true;
}
- else if (results.num_rows()>1)
- ACE_ERROR_RETURN((LM_ERROR, ACE_TEXT ("(%P|%t) CharacterDatabase::fill query returned wrong number of results. %s failed.\\n"), query.str().c_str()),false);
-
+ int m;
+
DbResultRow r=results.getRow(0);
c->setLevel((u8)r.getColInt16("level"));
c->setName(STR_OR_EMPTY(r.getColString("char_name")));
@@ -68,12 +80,144 @@
c->m_villain=r.getColBool("villain");
// appearance related.
c->setBodyType(r.getColInt32("bodytype")); // 0
- c->setFace_bits(r.getColInt32("face_bits")); //0xFF253158
+
+ sscanf(r.getColString("face_bits"),"%x",&m);
+ c->setFace_bits(m); //0xFF253158
+
c->setMapName(STR_OR_EMPTY(r.getColString("current_map"))); // "V_City_00_01.txt"
c->m_unkn1=r.getColFloat("unkn1");//20.0f
c->m_unkn2=r.getColFloat("unkn2");//30.0f;
c->m_unkn3=r.getColInt32("unkn3");//1;//rand()|(rand()<<16); // can enter map ??
c->m_unkn4=r.getColInt32("unkn4");//1;//0x7FFFFFFF^rand();

+ c->m_costume=new CharacterCostume;
+ stringstream costumeQuery;
+ DbResults costumeResults;
+ costumeQuery<<"SELECT * FROM costumes WHERE characterid="<<r.getColString("id");
+ if(!execQuery(costumeQuery.str(),costumeResults))
+ ACE_ERROR_RETURN((LM_ERROR, ACE_TEXT ("(%P|%t) CharacterDatabase::fill query %s failed. %s.\\n"), costumeQuery.str().c_str(),costumeResults.m_msg),false);
+
+ DbResultRow rc=costumeResults.getRow(0);
+
+ sscanf(rc.getColString("parts00e"),"%x",&n1);
+ sscanf(rc.getColString("parts00f"),"%x",&n2);
+ c->m_costume->m_parts.push_back(CostumePart(0,
+ STR_OR_EMPTY(rc.getColString("parts00a")),
+ STR_OR_EMPTY(rc.getColString("parts00b")),
+ STR_OR_EMPTY(rc.getColString("parts00c")),
+ STR_OR_EMPTY(rc.getColString("parts00d")),
+ n1,
+ n2,
+ 0,0));
+
+ sscanf(rc.getColString("parts01e"),"%x",&n3);
+ sscanf(rc.getColString("parts01f"),"%x",&n4);
+ c->m_costume->m_parts.push_back(CostumePart(1,
+ STR_OR_EMPTY(rc.getColString("parts01a")),
+ STR_OR_EMPTY(rc.getColString("parts01b")),
+ STR_OR_EMPTY(rc.getColString("parts01c")),
+ STR_OR_EMPTY(rc.getColString("parts01d")),
+ n3,
+ n4,
+ 0,0));
+
+ sscanf(rc.getColString("parts02e"),"%x",&n5);
+ sscanf(rc.getColString("parts02f"),"%x",&n6);
+ c->m_costume->m_parts.push_back(CostumePart(2,
+ STR_OR_EMPTY(rc.getColString("parts02a")),
+ STR_OR_EMPTY(rc.getColString("parts02b")),
+ STR_OR_EMPTY(rc.getColString("parts02c")),
+ STR_OR_EMPTY(rc.getColString("parts02d")),
+ n5,
+ n6,
+ 0,0));
+
+ sscanf(rc.getColString("parts03e"),"%x",&n7);
+ sscanf(rc.getColString("parts03f"),"%x",&n8);
+ c->m_costume->m_parts.push_back(CostumePart(3,
+ STR_OR_EMPTY(rc.getColString("parts03a")),
+ STR_OR_EMPTY(rc.getColString("parts03b")),
+ STR_OR_EMPTY(rc.getColString("parts03c")),
+ STR_OR_EMPTY(rc.getColString("parts03d")),
+ n7,
+ n8,
+ 0,0));
+
+ sscanf(rc.getColString("parts04e"),"%x",&n9);
+ sscanf(rc.getColString("parts04f"),"%x",&n10);
+ c->m_costume->m_parts.push_back(CostumePart(4,
+ STR_OR_EMPTY(rc.getColString("parts04a")),
+ STR_OR_EMPTY(rc.getColString("parts04b")),
+ STR_OR_EMPTY(rc.getColString("parts04c")),
+ STR_OR_EMPTY(rc.getColString("parts04d")),
+ n9,
+ n10,
+ 0,0));
+
+ sscanf(rc.getColString("parts05e"),"%x",&n11);
+ sscanf(rc.getColString("parts05f"),"%x",&n12);
+ c->m_costume->m_parts.push_back(CostumePart(5,
+ STR_OR_EMPTY(rc.getColString("parts05a")),
+ STR_OR_EMPTY(rc.getColString("parts05b")),
+ STR_OR_EMPTY(rc.getColString("parts05c")),
+ STR_OR_EMPTY(rc.getColString("parts05d")),
+ n11,
+ n12,
+ 0,0));
+
+ sscanf(rc.getColString("parts06e"),"%x",&n13);
+ sscanf(rc.getColString("parts06f"),"%x",&n14);
+ c->m_costume->m_parts.push_back(CostumePart(6,
+ STR_OR_EMPTY(rc.getColString("parts06a")),
+ STR_OR_EMPTY(rc.getColString("parts06b")),
+ STR_OR_EMPTY(rc.getColString("parts06c")),
+ STR_OR_EMPTY(rc.getColString("parts06d")),
+ n13,
+ n14,
+ 0,0));
+
+ sscanf(rc.getColString("parts08e"),"%x",&n15);
+ sscanf(rc.getColString("parts08f"),"%x",&n16);
+ c->m_costume->m_parts.push_back(CostumePart(8,
+ STR_OR_EMPTY(rc.getColString("parts08a")),
+ STR_OR_EMPTY(rc.getColString("parts08b")),
+ STR_OR_EMPTY(rc.getColString("parts08c")),
+ STR_OR_EMPTY(rc.getColString("parts08d")),
+ n15,
+ n16,
+ 0,0));
+ sscanf(rc.getColString("parts09e"),"%x",&n17);
+ sscanf(rc.getColString("parts09f"),"%x",&n18);
+ c->m_costume->m_parts.push_back(CostumePart(9,
+ STR_OR_EMPTY(rc.getColString("parts09a")),
+ STR_OR_EMPTY(rc.getColString("parts09b")),
+ STR_OR_EMPTY(rc.getColString("parts09c")),
+ STR_OR_EMPTY(rc.getColString("parts09d")),
+ n17,
+ n18,
+ 0,0));
+ sscanf(rc.getColString("parts10e"),"%x",&n19);
+ sscanf(rc.getColString("parts10f"),"%x",&n20);
+ c->m_costume->m_parts.push_back(CostumePart(10,
+ STR_OR_EMPTY(rc.getColString("parts10a")),
+ STR_OR_EMPTY(rc.getColString("parts10b")),
+ STR_OR_EMPTY(rc.getColString("parts10c")),
+ STR_OR_EMPTY(rc.getColString("parts10d")),
+ n19,
+ n20,
+ 0,0));
+ sscanf(rc.getColString("parts13e"),"%x",&n21);
+ sscanf(rc.getColString("parts13f"),"%x",&n22);
+ c->m_costume->m_parts.push_back(CostumePart(13,
+ STR_OR_EMPTY(rc.getColString("parts13a")),
+ STR_OR_EMPTY(rc.getColString("parts13b")),
+ STR_OR_EMPTY(rc.getColString("parts13c")),
+ STR_OR_EMPTY(rc.getColString("parts13d")),
+ n21,
+ n22,
+ 0,0));
+
+
+
return true;
}
\\ No newline at end of file

[/code:1:fc85751c25]

Runny
Posts: 63
Joined: Sun Sep 24, 2006 12:08 am

Post by Runny » Tue Nov 21, 2006 10:56 pm

This will save characters? If it will then....Cool

User avatar
nemerle
Posts: 397
Joined: Thu Jan 10, 2013 3:40 pm

Post by nemerle » Wed Nov 22, 2006 7:52 am

Oh man... StarkRavingMad, i have finished character storage 2 days ago :(
Maybe we can do things this way: I'm going to commit all my changes today, and I'm going to leave the rest of the character storage in your capable hands.
There are 2 things left to finish character manipulation: deleting a character, and storing character after the creation phase. While deleting shouldn't be a problem. the packet that client sends with a new character data contains hash id's instead of costume part names. We need to recreate the hashtable so we can do something like
[code:1:a5f366e776]
for(part_idx=0; idx<num_parts; idx++)
{
part_names[part_idx][0] = part_names_hash.find(pak->getPartId(part_idx,0));
part_names[part_idx][1] = part_names_hash.find(pak->getPartId(part_idx,1));
part_names[part_idx][2] = part_names_hash.find(pak->getPartId(part_idx,2));
part_names[part_idx][3] = part_names_hash.find(pak->getPartId(part_idx,3));
}
[/code:1:a5f366e776]
So, StarkRavingMad if you'd be willing to tackle this one, i could switch my focus to the MapServer.
* edited by nemerle *
"Ich was in one sumere dale,
in one suthe diyhele hale,
iherde ich holde grete tale
an hule and one niyhtingale."

starkravingmad
Posts: 24
Joined: Sun Apr 20, 2008 2:40 am

Post by starkravingmad » Wed Nov 22, 2006 3:26 pm

Well, i know the hash function we should use, main problem is with reading the list of all costume part names

Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests