Character Storage

Here we speak about Code and Design.
starkravingmad
Posts: 24
Joined: Sun Apr 20, 2008 2:40 am

Post by starkravingmad » Mon Nov 06, 2006 12:54 am

I don't get on the IRC channel so I don't know where the developers are on
character storage. I'm sure they are working on it, but in an attempt to
sharpen my skills I decided to go ahead and try to come up with a solution
on my own. My skill level is somewhere between basic and intermediate so
this might not be the best most efficient solution.

A few warnings before you delve into the code. This is just a first draft.
There are 2 changes I want to make for the second draft. The first change:
For this function to work properly it should be passed the account name,
so during character selection it only pulls up your characters. The second
change: This schema only allows for 1 costume, but since we can't get into
a map yet and walk around, this isn't a problem. In the next draft the
costume should be in its own table.

Also, this code is only for pulling your character list out of the
database. I haven't worked on adding characters to the database yet.
Fortunately I have some demo records from the live servers with some of my
favorite characters. I opened the demo with a text editor and copied all
of the values. I have included the PostgreSQL commands to insert 2 sample
characters into the database. Lastly, my skills never included cross
platform coding and I do not have a unix box to test things on. So all
of my code has only been tried on Windows XP.


[color=blue:79a5ba8654]cohemu_game.sql[/size:79a5ba8654][/color:79a5ba8654]
[code:1:79a5ba8654]
CREATE TABLE characters (
id serial NOT NULL,
username character varying(32),
charname character varying(32),
creation_date timestamp(6) without time zone DEFAULT now() NOT NULL,
archetype character varying(32),
origin character varying(16),
mapname character varying(32),
level int2,
isVillain int2,
bodytype 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),
facebits character varying(10)
);

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_charname_key UNIQUE (charname);
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;
[/code:1:79a5ba8654]

[code:1:79a5ba8654]
INSERT INTO characters (username, charname, archetype, origin,
mapname, level, isVillain, bodytype,
parts00a, parts00b, parts00c, parts00d, parts00e, parts00f,
parts01a, parts01b, parts01c, parts01d, parts01e, parts01f,
parts02a, parts02b, parts02c, parts02d, parts02e, parts02f,
parts03a, parts03b, parts03c, parts03d, parts03e, parts03f,
parts04a, parts04b, parts04c, parts04d, parts04e, parts04f,
parts05a, parts05b, parts05c, parts05d, parts05e, parts05f,
parts06a, parts06b, parts06c, parts06d, parts06e, parts06f,
parts08a, parts08b, parts08c, parts08d, parts08e, parts08f,
parts09a, parts09b, parts09c, parts09d, parts09e, parts09f,
parts10a, parts10b, parts10c, parts10d, parts10e, parts10f,
parts13a, parts13b, parts13c, parts13d, parts13e, parts13f,
facebits) VALUES (
'danjo', 'Mr Tough Guy', 'class_tanker', 'natural', 'City_01_01.txt', '33', '0', '0',
'Tight', 'Pants', 'none', 'none', '0x001f1f', '0x615131',
'Tight', 'skin_tshirt_01a', 'skin_tshirt_01b', 'none', '0x000000', '0x615131',
'Masked_Bald', 'skin_head_01', 'none', 'none', '0x001f1f', '0x615131',
'Smooth', 'skin_fingerless_01a', 'skin_fingerless_01b', 'none', '0x001f1f', '0x615131',
'Pants_Tight', 'Pants', 'Shoe_Color', 'none', '0x001f1f', '0x615131',
'Leather_02', 'Leather_03a', 'Leather_03b', 'none', '0x001f1f', '0x615131',
'Style_03', 'Style_01a', 'Style_01b', 'none', '0x001f1f', '0x615131',
'Goggle_07', 'Lens_01a', 'Lens_01b', 'none', '0x001f1f', '0x615131',
'Tight', 'base', 'Infinity', '', '0x00fdff', '0xffffff',
'', '', '', '', '0x001f1f', '0x615131',
'', '', '', '', '0x001f1f', '0x615131'
'0xc9e6ff');
[/code:1:79a5ba8654]

[code:1:79a5ba8654]
INSERT INTO characters (username, charname, archetype, origin,
mapname, level, isVillain, bodytype,
parts00a, parts00b, parts00c, parts00d, parts00e, parts00f,
parts01a, parts01b, parts01c, parts01d, parts01e, parts01f,
parts02a, parts02b, parts02c, parts02d, parts02e, parts02f,
parts03a, parts03b, parts03c, parts03d, parts03e, parts03f,
parts04a, parts04b, parts04c, parts04d, parts04e, parts04f,
parts05a, parts05b, parts05c, parts05d, parts05e, parts05f,
parts06a, parts06b, parts06c, parts06d, parts06e, parts06f,
parts08a, parts08b, parts08c, parts08d, parts08e, parts08f,
parts09a, parts09b, parts09c, parts09d, parts09e, parts09f,
parts10a, parts10b, parts10c, parts10d, parts10e, parts10f,
parts13a, parts13b, parts13c, parts13d, parts13e, parts13f,
facebits) VALUES (
'danjo', 'Capt Frostbite', 'class_tanker', 'magic', 'City_01_01.txt', '44', '0', '0',
'Tight', 'tights', 'Stripe_2', '', '0xffffff', '0xff5700',
'Tight', 'Tech_Modern_01', 'Stripe_3', 'none', '0xffffff', '0xff5700',
'Masked_Bald', 'skin_head_01', 'none', 'none', '0xff5700', '0xffffff',
'Smooth', 'skin_wrapped_01a', 'skin_wrapped_01b', 'none', '0xff5700', '0xffffff',
'Smooth', 'wrapped_01a', 'none', 'none', '0xff5700', '0xffffff',
'Tech_01', 'Tech_01a', 'Tech_01b', 'none', '0x993100', '0xffffff',
'Style_03', 'Style_01a', 'Style_01b', 'none', '0xffffff', '0x000000',
'', '', '', '', '0x1f1f1f', '0xffffff',
'Tight', 'base', 'Lion_01', '', '0x993100', '0xffffff',
'V_MALE_SPADR.GEO/GEO_SpadR_Longbow_Pad', '!Hair_V_Helmet_Longbow', '!Hair_V_Helmet_Longbow_Mask', '', '0xffffff', '0x993100',
'Beard_Clean', 'Beard_01a', 'Beard_01b', '', '0xffffff', '0x000000'
'0x7289d4');
[/code:1:79a5ba8654]

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

Post by starkravingmad » Mon Nov 06, 2006 12:55 am

[color=blue:26768fe235]CharacterDatabase.h[/size:26768fe235][/color:26768fe235]
[code:1:26768fe235]
/*
* City of Heroes Emulator Project
* http://www.cohemu.net/
* Copyright (c) 2006 City of Heroes Emulator Team (see Authors.txt)
* This software is licensed! (See License.txt for details)
*
* $Id: AdminDatabase.h 267 2006-09-18 04:46:30Z nemerle $
*/
#pragma once
// ACE Logging
#include <string>
#include <ace/Log_Msg.h>
#include <ace/Singleton.h>

#include <vector> // My addon
#include "types.h"
#include "Database.h"
#include "Character.h" // My addon

class IClient;
class CharacterDatabase : public Database
{
public:
int AddCharacter(const std::string &username, const std::string &charname);
int RemoveCharacter(char *username, char *charname);
std::vector<Character *> GetListByUser(std::vector<Character *> m_characters); // My addon
};
[/code:1:26768fe235]

[color=blue:26768fe235]CharacterDatabase.cpp[/size:26768fe235][/color:26768fe235]
[code:1:26768fe235]
/*
* City of Heroes Emulator Project
* http://www.cohemu.net/
* Copyright (c) 2006 City of Heroes Emulator Team (see Authors.txt)
* This software is licensed! (See License.txt for details)
*
* $Id: AdminDatabase.cpp 267 2006-09-18 04:46:30Z nemerle $
*/

// CoHEmu includes
#include "CharacterDatabase.h"
#include <sstream>
#include <ace/OS_NS_time.h>
#include <ace/Log_Msg.h>
#include "CharacterClient.h"
#include "Character.h" // My addon

int CharacterDatabase::AddCharacter(const std::string &username, const std::string &charname)
{
// Who,
// Awaiting nemerle's direction on this function
return 0;
}

int CharacterDatabase::RemoveCharacter(char *username, char *charname)
{
// Will implement later after AddCharacter function is complete
return 0;
}

std::vector<Character *> CharacterDatabase::GetListByUser(std::vector<Character *> m_characters)
{
// Will get list of characters from database
Character *act;
static char SQLStmt[256]; // Variable to hold our query
sprintf(SQLStmt, "SELECT * FROM characters ORDER BY creation_date");

pResult = PQexec(pConnection,SQLStmt); // Send our query to the PostgreSQL db server to process
if (PQresultErrorMessage(pResult) != NULL)
{
printf("Result status: %s\\n",PQresultErrorMessage(pResult)); // Print the reason why the query failed

}
// PQgetvalue(*Result, int_row, int_column)

int m,n,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20,n21,n22,bt;
if(m_characters.size()==0)
act = new Character;
else
act = m_characters[0]; //reuse existing object

// PQntuples returns the number of rows in our database query,
// and starts a for statement to loop through all the rows.
for(int i=0; i<PQntuples(pResult); i++)
{
// sscanf is used to pull the integer value out of the char return value
sscanf(PQgetvalue(pResult,i,7),"%u",&m);
if (m<50 && m>=0)
act->setLevel(m); // these are 0 based
else
act->setLevel(0);
act->setName(PQgetvalue(pResult,i,2));
act->setArchetype(PQgetvalue(pResult,i,4));
act->setOrigin(PQgetvalue(pResult,i,5));

sscanf(PQgetvalue(pResult,i,9),"%u",&bt);
act->setBodyType(bt);

sscanf(PQgetvalue(pResult,i,76),"%x",&bt);
act->setFace_bits(bt);
act->setMapName(PQgetvalue(pResult,i,6));
sscanf(PQgetvalue(pResult,i,8),"%u",&n);
if (n==0)
act->m_villain=0;
else
act->m_villain=1;
act->m_unkn1=20.0f;
act->m_unkn2=30.0f;
act->m_unkn3=1;//rand()|(rand()<<16); // can enter map ??
act->m_unkn4=1;//0x7FFFFFFF^rand();
act->m_costume=new CharacterCostume;

sscanf(PQgetvalue(pResult,i,14),"%x",&n1); //Get string value from db
sscanf(PQgetvalue(pResult,i,15),"%x",&n2); //and turn it into a u32
sscanf(PQgetvalue(pResult,i,20),"%x",&n3); //hex number, to be used
sscanf(PQgetvalue(pResult,i,21),"%x",&n4); //for the color of the
sscanf(PQgetvalue(pResult,i,26),"%x",&n5); //costume parts
sscanf(PQgetvalue(pResult,i,27),"%x",&n6);
sscanf(PQgetvalue(pResult,i,32),"%x",&n7);
sscanf(PQgetvalue(pResult,i,33),"%x",&n8);
sscanf(PQgetvalue(pResult,i,38),"%x",&n9);
sscanf(PQgetvalue(pResult,i,39),"%x",&n10);
sscanf(PQgetvalue(pResult,i,44),"%x",&n11);
sscanf(PQgetvalue(pResult,i,45),"%x",&n12);
sscanf(PQgetvalue(pResult,i,50),"%x",&n13);
sscanf(PQgetvalue(pResult,i,51),"%x",&n14);
sscanf(PQgetvalue(pResult,i,56),"%x",&n15);
sscanf(PQgetvalue(pResult,i,57),"%x",&n16);
sscanf(PQgetvalue(pResult,i,62),"%x",&n17);
sscanf(PQgetvalue(pResult,i,63),"%x",&n18);
sscanf(PQgetvalue(pResult,i,68),"%x",&n19);
sscanf(PQgetvalue(pResult,i,69),"%x",&n20);
sscanf(PQgetvalue(pResult,i,74),"%x",&n21);
sscanf(PQgetvalue(pResult,i,75),"%x",&n22);

act->m_costume->m_parts.push_back(CostumePart(0,
PQgetvalue(pResult,i,10), // Get parts00a from the character database
PQgetvalue(pResult,i,11), // Get parts00b from the character database
PQgetvalue(pResult,i,12), // Get parts00c from the character database
PQgetvalue(pResult,i,13), // Get parts00d from the character database
n1, // Get parts00e from the character database
n2, // Get parts00f from the character database
0,0));
act->m_costume->m_parts.push_back(CostumePart(1,
PQgetvalue(pResult,i,16), // Get parts01a from the character database
PQgetvalue(pResult,i,17), // Get parts01b from the character database
PQgetvalue(pResult,i,18), // Get parts01c from the character database
PQgetvalue(pResult,i,19), // Get parts01d from the character database
n3, // Get parts01e from the character database
n4, // Get parts01f from the character database
0,0));
act->m_costume->m_parts.push_back(CostumePart(2,
PQgetvalue(pResult,i,22), // Get parts02a from the character database
PQgetvalue(pResult,i,23), // Get parts02b from the character database
PQgetvalue(pResult,i,24), // Get parts02c from the character database
PQgetvalue(pResult,i,25), // Get parts02d from the character database
n5,
n6,
0,0));
act->m_costume->m_parts.push_back(CostumePart(3,
PQgetvalue(pResult,i,28), // Get parts03a from the character database
PQgetvalue(pResult,i,29), // Get parts03b from the character database
PQgetvalue(pResult,i,30), // Get parts03c from the character database
PQgetvalue(pResult,i,31), // Get parts03d from the character database
n7,
n8,
0,0));
act->m_costume->m_parts.push_back(CostumePart(4,
PQgetvalue(pResult,i,34), // Get parts04a from the character database
PQgetvalue(pResult,i,35), // Get parts04b from the character database
PQgetvalue(pResult,i,36), // Get parts04c from the character database
PQgetvalue(pResult,i,37), // Get parts04d from the character database
n9,
n10,
0,0));
act->m_costume->m_parts.push_back(CostumePart(5,
PQgetvalue(pResult,i,40), // Get parts05a from the character database
PQgetvalue(pResult,i,41), // Get parts05b from the character database
PQgetvalue(pResult,i,42), // Get parts05c from the character database
PQgetvalue(pResult,i,43), // Get parts05d from the character database
n11,
n12,
0,0));
act->m_costume->m_parts.push_back(CostumePart(6,
PQgetvalue(pResult,i,46), // Get parts06a from the character database
PQgetvalue(pResult,i,47), // Get parts06b from the character database
PQgetvalue(pResult,i,48), // Get parts06c from the character database
PQgetvalue(pResult,i,49), // Get parts06d from the character database
n13,
n14,
0,0));
act->m_costume->m_parts.push_back(CostumePart(8,
PQgetvalue(pResult,i,52), // Get parts08a from the character database
PQgetvalue(pResult,i,53), // Get parts08b from the character database
PQgetvalue(pResult,i,54), // Get parts08c from the character database
PQgetvalue(pResult,i,55), // Get parts08d from the character database
n15,
n16,
0,0));
act->m_costume->m_parts.push_back(CostumePart(9,
PQgetvalue(pResult,i,58), // Get parts09a from the character database
PQgetvalue(pResult,i,59), // Get parts09b from the character database
PQgetvalue(pResult,i,60), // Get parts09c from the character database
PQgetvalue(pResult,i,61), // Get parts09d from the character database
n17,
n18,
0,0));
act->m_costume->m_parts.push_back(CostumePart(10,
PQgetvalue(pResult,i,64), // Get parts10a from the character database
PQgetvalue(pResult,i,65), // Get parts10b from the character database
PQgetvalue(pResult,i,66), // Get parts10c from the character database
PQgetvalue(pResult,i,67), // Get parts10d from the character database
n19,
n20,
0,0));
act->m_costume->m_parts.push_back(CostumePart(13,
PQgetvalue(pResult,i,70), // Get parts13a from the character database
PQgetvalue(pResult,i,71), // Get parts13b from the character database
PQgetvalue(pResult,i,72), // Get parts13c from the character database
PQgetvalue(pResult,i,73), // Get parts13d from the character database
n21,
n22,
0,0));
m_characters.push_back(act);
act = new Character;
act->reset();

}

PQclear(pResult); // Clear result
return m_characters;
}
[/code:1:26768fe235]

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

Post by starkravingmad » Mon Nov 06, 2006 12:56 am

[color=blue:460e6d0cca]CharacterClient.cpp[/size:460e6d0cca][/color:460e6d0cca]
[code:1:460e6d0cca]
bool CharacterClient::getCharsFromDb()
{
Character *act;
CharacterDatabase * m_db = m_handler->getDb();

m_characters=m_db->GetListByUser(m_characters); // This is a call to the new function

for(size_t i=m_characters.size(); i<MAX_CHARACTER_SLOTS; i++)
{
if(m_characters.size()>i) //even more reuse
{
m_characters->reset();
continue;
}
act = new Character;
act->reset();
m_characters.push_back(act);
}
return true;
}
[/code:1:460e6d0cca]

calsmurf2904
Posts: 15
Joined: Sat Oct 28, 2006 6:34 pm

Post by calsmurf2904 » Tue Nov 07, 2006 3:22 pm

for MYSQL i have an World of warcraft server emulator with mySQL but maybe you can use the character storage part in mysql for city of heroes ....

It is just an idea....


(if you want that server to check it out than just PM me :D )

I got the source code too !!

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

Post by starkravingmad » Tue Nov 07, 2006 10:39 pm

I kept getting an empty string when trying to fetch the account name, but I have found a way to fetch the account id. Which is just as good and uniquely identifies the person logging in. Now I just have to update the code and finish the addcharacter function.

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

Post by starkravingmad » Wed Nov 08, 2006 1:10 pm

Here is the second version of the character database schema. This version seperates
costumes into their own table. That way 1 character can have multiple costumes. Also
the login now uses account_id to locate your account and list only your characters
instead of listing all characters.


[color=blue:d87595367e]cohemu_game.sql[/size:d87595367e][/color:d87595367e]
[code:1:d87595367e]
CREATE TABLE characters (
id serial NOT NULL,
account_id integer,
charname character varying(32),
creation_date timestamp(6) without time zone DEFAULT now() NOT NULL,
archetype character varying(32),
origin character varying(16),
mapname character varying(32),
level int2,
isVillain int2,
bodytype int2,
lastcostume int2,
facebits character varying(10)
);

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_charname_key UNIQUE (charname);

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;
[/code:1:d87595367e]

The following is sample character 1, with 1 costume.
[code:1:d87595367e]
INSERT INTO characters (account_id, charname, archetype, origin, mapname,
level, isVillain, bodytype, lastcostume, facebits) VALUES (
'1', 'Mr Tough Guy', 'class_tanker', 'natural', 'City_01_01.txt', '33', '0', '0', '0','0xc9e6ff');

INSERT INTO costumes ( characterId, costumeSlot,
parts00a, parts00b, parts00c, parts00d, parts00e, parts00f,
parts01a, parts01b, parts01c, parts01d, parts01e, parts01f,
parts02a, parts02b, parts02c, parts02d, parts02e, parts02f,
parts03a, parts03b, parts03c, parts03d, parts03e, parts03f,
parts04a, parts04b, parts04c, parts04d, parts04e, parts04f,
parts05a, parts05b, parts05c, parts05d, parts05e, parts05f,
parts06a, parts06b, parts06c, parts06d, parts06e, parts06f,
parts08a, parts08b, parts08c, parts08d, parts08e, parts08f,
parts09a, parts09b, parts09c, parts09d, parts09e, parts09f,
parts10a, parts10b, parts10c, parts10d, parts10e, parts10f,
parts13a, parts13b, parts13c, parts13d, parts13e, parts13f ) VALUES ( '1', '0',
'Tight', 'Pants', 'none', 'none', '0x001f1f', '0x615131',
'Tight', 'skin_tshirt_01a', 'skin_tshirt_01b', 'none', '0x000000', '0x615131',
'Masked_Bald', 'skin_head_01', 'none', 'none', '0x001f1f', '0x615131',
'Smooth', 'skin_fingerless_01a', 'skin_fingerless_01b', 'none', '0x001f1f', '0x615131',
'Pants_Tight', 'Pants', 'Shoe_Color', 'none', '0x001f1f', '0x615131',
'Leather_02', 'Leather_03a', 'Leather_03b', 'none', '0x001f1f', '0x615131',
'Style_03', 'Style_01a', 'Style_01b', 'none', '0x001f1f', '0x615131',
'Goggle_07', 'Lens_01a', 'Lens_01b', 'none', '0x001f1f', '0x615131',
'Tight', 'base', 'Infinity', '', '0x00fdff', '0xffffff',
'', '', '', '', '0x001f1f', '0x615131',
'', '', '', '', '0x001f1f', '0x615131'
);
[/code:1:d87595367e]

The following is sample character 2, with 2 different costumes.
[code:1:d87595367e]
INSERT INTO characters (account_id, charname, archetype, origin, mapname,
level, isVillain, bodytype, lastcostume, facebits) VALUES (
'1', 'Capt Frostbite', 'class_tanker', 'magic', 'City_01_01.txt', '44', '0', '0', '0', '0x7289d4');

INSERT INTO costumes ( characterId, costumeSlot,
parts00a, parts00b, parts00c, parts00d, parts00e, parts00f,
parts01a, parts01b, parts01c, parts01d, parts01e, parts01f,
parts02a, parts02b, parts02c, parts02d, parts02e, parts02f,
parts03a, parts03b, parts03c, parts03d, parts03e, parts03f,
parts04a, parts04b, parts04c, parts04d, parts04e, parts04f,
parts05a, parts05b, parts05c, parts05d, parts05e, parts05f,
parts06a, parts06b, parts06c, parts06d, parts06e, parts06f,
parts08a, parts08b, parts08c, parts08d, parts08e, parts08f,
parts09a, parts09b, parts09c, parts09d, parts09e, parts09f,
parts10a, parts10b, parts10c, parts10d, parts10e, parts10f,
parts13a, parts13b, parts13c, parts13d, parts13e, parts13f ) VALUES ( '2', '0',
'Tight', 'tights', 'Stripe_2', '', '0xffffff', '0xff5700',
'Tight', 'Tech_Modern_01', 'Stripe_3', 'none', '0xffffff', '0xff5700',
'Masked_Bald', 'skin_head_01', 'none', 'none', '0xff5700', '0xffffff',
'Smooth', 'skin_wrapped_01a', 'skin_wrapped_01b', 'none', '0xff5700', '0xffffff',
'Smooth', 'wrapped_01a', 'none', 'none', '0xff5700', '0xffffff',
'Tech_01', 'Tech_01a', 'Tech_01b', 'none', '0x993100', '0xffffff',
'Style_03', 'Style_01a', 'Style_01b', 'none', '0xffffff', '0x000000',
'', '', '', '', '0x1f1f1f', '0xffffff',
'Tight', 'base', 'Lion_01', '', '0x993100', '0xffffff',
'V_MALE_SPADR.GEO/GEO_SpadR_Longbow_Pad', '!Hair_V_Helmet_Longbow', '!Hair_V_Helmet_Longbow_Mask', '', '0xffffff', '0x993100',
'Beard_Clean', 'Beard_01a', 'Beard_01b', '', '0xffffff', '0x000000'
);

INSERT INTO costumes ( characterId, costumeSlot,
parts00a, parts00b, parts00c, parts00d, parts00e, parts00f,
parts01a, parts01b, parts01c, parts01d, parts01e, parts01f,
parts02a, parts02b, parts02c, parts02d, parts02e, parts02f,
parts03a, parts03b, parts03c, parts03d, parts03e, parts03f,
parts04a, parts04b, parts04c, parts04d, parts04e, parts04f,
parts05a, parts05b, parts05c, parts05d, parts05e, parts05f,
parts06a, parts06b, parts06c, parts06d, parts06e, parts06f,
parts08a, parts08b, parts08c, parts08d, parts08e, parts08f,
parts09a, parts09b, parts09c, parts09d, parts09e, parts09f,
parts10a, parts10b, parts10c, parts10d, parts10e, parts10f,
parts13a, parts13b, parts13c, parts13d, parts13e, parts13f
) VALUES ( '2', '1',
'Tight', 'Pants', 'none', 'none', '0x001f1f', '0x615131',
'Tight', 'skin_tshirt_01a', 'skin_tshirt_01b', 'none', '0x000000', '0x615131',
'Masked_Bald', 'skin_head_01', 'none', 'none', '0x001f1f', '0x615131',
'Smooth', 'skin_fingerless_01a', 'skin_fingerless_01b', 'none', '0x001f1f', '0x615131',
'Pants_Tight', 'Pants', 'Shoe_Color', 'none', '0x001f1f', '0x615131',
'Leather_02', 'Leather_03a', 'Leather_03b', 'none', '0x001f1f', '0x615131',
'Style_03', 'Style_01a', 'Style_01b', 'none', '0x001f1f', '0x615131',
'Goggle_07', 'Lens_01a', 'Lens_01b', 'none', '0x001f1f', '0x615131',
'Tight', 'base', 'Infinity', '', '0x00fdff', '0xffffff',
'', '', '', '', '0x001f1f', '0x615131',
'', '', '', '', '0x001f1f', '0x615131'
);
[/code:1:d87595367e]

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

Post by starkravingmad » Wed Nov 08, 2006 1:12 pm

[color=blue:1b8742f554]CharacterDatabase.h[/size:1b8742f554][/color:1b8742f554]
[code:1:1b8742f554]
/*
* City of Heroes Emulator Project
* http://www.cohemu.net/
* Copyright (c) 2006 City of Heroes Emulator Team (see Authors.txt)
* This software is licensed! (See License.txt for details)
*
* $Id: AdminDatabase.h 267 2006-09-18 04:46:30Z nemerle $
*/
#pragma once
// ACE Logging
#include <string>
#include <ace/Log_Msg.h>
#include <ace/Singleton.h>

#include <vector> // My addon
#include "types.h"
#include "Database.h"
#include "Character.h" // My addon

class IClient;
class CharacterDatabase : public Database
{
public:
int AddCharacter(const std::string &username, const std::string &charname);
int RemoveCharacter(char *username, char *charname);
std::vector<Character *> GetListByUser(u64 cid, std::vector<Character *> m_characters); // My addon
};
[/code:1:1b8742f554]

[color=blue:1b8742f554]CharacterDatabase.cpp[/size:1b8742f554][/color:1b8742f554]
[code:1:1b8742f554]
/*
* City of Heroes Emulator Project
* http://www.cohemu.net/
* Copyright (c) 2006 City of Heroes Emulator Team (see Authors.txt)
* This software is licensed! (See License.txt for details)
*
* $Id: AdminDatabase.cpp 267 2006-09-18 04:46:30Z nemerle $
*/

// CoHEmu includes
#include "CharacterDatabase.h"
#include <sstream>
#include <ace/OS_NS_time.h>
#include <ace/Log_Msg.h>
#include "CharacterClient.h"
#include "Character.h" // My addon


// UserToken,
int CharacterDatabase::AddCharacter(const std::string &username, const std::string &charname)
{
// Who,
// Awaiting nemerle's direction on this function

static char SQLStmt[256]; // Variable to hold our query
sprintf(SQLStmt, "SELECT * FROM characters WHERE charname=%s", charname);

pResult = PQexec(pConnection,SQLStmt); // Send our query to the PostgreSQL db server to process

if (pResult ==0)
{
// The character name is not taken
}else{
// The name already exists
}

PQclear(pResult); // Clear result
return 0;
}

int CharacterDatabase::RemoveCharacter(char *username, char *charname)
{
// Will implement later after AddCharacter function is complete
return 0;
}

std::vector<Character *> CharacterDatabase::GetListByUser(u64 cid, std::vector<Character *> m_characters)
{
// Will get list of characters from database

Character *act;

static char SQLStmt[256]; // Variable to hold our query
sprintf(SQLStmt, "SELECT * FROM characters, costumes WHERE account_id=%d AND characterId=id AND costumeSlot=0 ORDER BY creation_date", cid);

pResult = PQexec(pConnection,SQLStmt); // Send our query to the PostgreSQL db server to process
if (PQresultErrorMessage(pResult) == NULL)
{
printf("Result status: %s\\n",PQresultErrorMessage(pResult)); // Print the reason why the query failed

}

int m,n,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20,n21,n22,bt;

if(m_characters.size()==0)
act = new Character;
else
act = m_characters[0]; //reuse existing object

// PQntuples returns the number of rows in our database query,
// and starts a for statement to loop through all the rows.
for(int i=0; i<PQntuples(pResult); i++)
{
// sscanf is used to pull the integer value out of the char return value
// PQgetvalue(*Result, int_row, int_column)
sscanf(PQgetvalue(pResult,i,7),"%u",&m);
if (m<50 && m>=0)
act->setLevel(m); // these are 0 based
else
act->setLevel(0);
act->setName(PQgetvalue(pResult,i,2));
act->setArchetype(PQgetvalue(pResult,i,4));
act->setOrigin(PQgetvalue(pResult,i,5));

sscanf(PQgetvalue(pResult,i,9),"%u",&bt);
act->setBodyType(bt);

sscanf(PQgetvalue(pResult,i,11),"%x",&bt);
act->setFace_bits(bt);

act->setMapName(PQgetvalue(pResult,i,6));

sscanf(PQgetvalue(pResult,i,8),"%u",&n);
if (n==0)
act->m_villain=0;
else
act->m_villain=1;
act->m_unkn1=20.0f;
act->m_unkn2=30.0f;
act->m_unkn3=1;//rand()|(rand()<<16); // can enter map ??
act->m_unkn4=1;//0x7FFFFFFF^rand();

// here we fill the costume from the database
act->m_costume=new CharacterCostume;

sscanf(PQgetvalue(pResult,i,18),"%x",&n1);
sscanf(PQgetvalue(pResult,i,19),"%x",&n2);

act->m_costume->m_parts.push_back(CostumePart(0,
PQgetvalue(pResult,i,14), // Get parts00a from the character database
PQgetvalue(pResult,i,15), // Get parts00b from the character database
PQgetvalue(pResult,i,16), // Get parts00c from the character database
PQgetvalue(pResult,i,17), // Get parts00d from the character database
n1, // Get parts00e from the character database
n2, // Get parts00f from the character database
0,0));

sscanf(PQgetvalue(pResult,i,24),"%x",&n3);
sscanf(PQgetvalue(pResult,i,25),"%x",&n4);

act->m_costume->m_parts.push_back(CostumePart(1,
PQgetvalue(pResult,i,20), // Get parts01a from the character database
PQgetvalue(pResult,i,21), // Get parts01b from the character database
PQgetvalue(pResult,i,22), // Get parts01c from the character database
PQgetvalue(pResult,i,23), // Get parts01d from the character database
n3, // Get parts01e from the character database
n4, // Get parts01f from the character database
0,0));

sscanf(PQgetvalue(pResult,i,30),"%x",&n5);
sscanf(PQgetvalue(pResult,i,31),"%x",&n6);

act->m_costume->m_parts.push_back(CostumePart(2,
PQgetvalue(pResult,i,26), // Get parts02a from the character database
PQgetvalue(pResult,i,27), // Get parts02b from the character database
PQgetvalue(pResult,i,28), // Get parts02c from the character database
PQgetvalue(pResult,i,29), // Get parts02d from the character database
n5,
n6,
0,0));

sscanf(PQgetvalue(pResult,i,36),"%x",&n7);
sscanf(PQgetvalue(pResult,i,37),"%x",&n8);

act->m_costume->m_parts.push_back(CostumePart(3,
PQgetvalue(pResult,i,32), // Get parts03a from the character database
PQgetvalue(pResult,i,33), // Get parts03b from the character database
PQgetvalue(pResult,i,34), // Get parts03c from the character database
PQgetvalue(pResult,i,35), // Get parts03d from the character database
n7,
n8,
0,0));

sscanf(PQgetvalue(pResult,i,42),"%x",&n9);
sscanf(PQgetvalue(pResult,i,43),"%x",&n10);

act->m_costume->m_parts.push_back(CostumePart(4,
PQgetvalue(pResult,i,38), // Get parts04a from the character database
PQgetvalue(pResult,i,39), // Get parts04b from the character database
PQgetvalue(pResult,i,40), // Get parts04c from the character database
PQgetvalue(pResult,i,41), // Get parts04d from the character database
n9,
n10,
0,0));

sscanf(PQgetvalue(pResult,i,48),"%x",&n11);
sscanf(PQgetvalue(pResult,i,49),"%x",&n12);

act->m_costume->m_parts.push_back(CostumePart(5,
PQgetvalue(pResult,i,44), // Get parts05a from the character database
PQgetvalue(pResult,i,45), // Get parts05b from the character database
PQgetvalue(pResult,i,46), // Get parts05c from the character database
PQgetvalue(pResult,i,47), // Get parts05d from the character database
n11,
n12,
0,0));

sscanf(PQgetvalue(pResult,i,54),"%x",&n13);
sscanf(PQgetvalue(pResult,i,55),"%x",&n14);

act->m_costume->m_parts.push_back(CostumePart(6,
PQgetvalue(pResult,i,50), // Get parts06a from the character database
PQgetvalue(pResult,i,51), // Get parts06b from the character database
PQgetvalue(pResult,i,52), // Get parts06c from the character database
PQgetvalue(pResult,i,53), // Get parts06d from the character database
n13,
n14,
0,0));

sscanf(PQgetvalue(pResult,i,60),"%x",&n15);
sscanf(PQgetvalue(pResult,i,61),"%x",&n16);

act->m_costume->m_parts.push_back(CostumePart(8,
PQgetvalue(pResult,i,56), // Get parts08a from the character database
PQgetvalue(pResult,i,57), // Get parts08b from the character database
PQgetvalue(pResult,i,58), // Get parts08c from the character database
PQgetvalue(pResult,i,59), // Get parts08d from the character database
n15,
n16,
0,0));

sscanf(PQgetvalue(pResult,i,66),"%x",&n17);
sscanf(PQgetvalue(pResult,i,67),"%x",&n18);

act->m_costume->m_parts.push_back(CostumePart(9,
PQgetvalue(pResult,i,62), // Get parts09a from the character database
PQgetvalue(pResult,i,63), // Get parts09b from the character database
PQgetvalue(pResult,i,64), // Get parts09c from the character database
PQgetvalue(pResult,i,65), // Get parts09d from the character database
n17,
n18,
0,0));

sscanf(PQgetvalue(pResult,i,72),"%x",&n19);
sscanf(PQgetvalue(pResult,i,73),"%x",&n20);

act->m_costume->m_parts.push_back(CostumePart(10,
PQgetvalue(pResult,i,68), // Get parts10a from the character database
PQgetvalue(pResult,i,69), // Get parts10b from the character database
PQgetvalue(pResult,i,70), // Get parts10c from the character database
PQgetvalue(pResult,i,71), // Get parts10d from the character database
n19,
n20,
0,0));

sscanf(PQgetvalue(pResult,i,78),"%x",&n21);
sscanf(PQgetvalue(pResult,i,79),"%x",&n22);

act->m_costume->m_parts.push_back(CostumePart(13,
PQgetvalue(pResult,i,74), // Get parts13a from the character database
PQgetvalue(pResult,i,75), // Get parts13b from the character database
PQgetvalue(pResult,i,76), // Get parts13c from the character database
PQgetvalue(pResult,i,77), // Get parts13d from the character database
n21,
n22,
0,0));

m_characters.push_back(act);
act = new Character;
act->reset();

}

PQclear(pResult); // Clear result
return m_characters;
}

[/code:1:1b8742f554]

[color=blue:1b8742f554]CharacterClient.cpp[/size:1b8742f554][/color:1b8742f554]
[code:1:1b8742f554]
/*
* City of Heroes Emulator Project
* http://www.cohemu.net/
* Copyright (c) 2006 City of Heroes Emulator Team (see Authors.txt)
* This software is licensed! (See License.txt for details)
*
* $Id: Client.cpp 253 2006-08-31 22:00:14Z malign $
*/
#include "CharacterClient.h"
#include "Character.h"
#include "CharacterDatabase.h"
#include "ClientHandler.h"
#include "Client.h" // My addon

CharacterClient::~CharacterClient()
{
reset();
}

size_t CharacterClient::getNumSlots()
{
return m_characters.size();
}
bool CharacterClient::getCharsFromDb()
{
Character *act;
CharacterDatabase * m_db = m_handler->getDb();

u64 cid = getId(); // This is the account_id

m_characters=m_db->GetListByUser(cid, m_characters); // This is a call to the new function

for(size_t i=m_characters.size(); i<MAX_CHARACTER_SLOTS; i++)
{
if(m_characters.size()>i) //even more reuse
{
m_characters->reset();
continue;
}
act = new Character;
act->reset();
m_characters.push_back(act);
}
return true;
}

Character * CharacterClient::getCharacter( size_t idx )
{
ACE_ASSERT(idx<m_characters.size());
return m_characters[idx];
}

void CharacterClient::reset()
{
for(size_t i=0; i<m_characters.size(); i++)
{
delete m_characters;
}
m_characters.clear();
}

[/code:1:1b8742f554]

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

Post by starkravingmad » Wed Nov 08, 2006 1:59 pm

Hmm. All of my work was based off of the official build 296. I finished my work last night but didn't post until this morning. It looks like the devs went to build 297 while I slept. I will have to spend some time looking over the official changes.

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

Post by nemerle » Wed Nov 08, 2006 2:55 pm

I'm sorry, i had forgotten about those uncommited changes to the char storage, and your fine work reminded me of them :)
"Ich was in one sumere dale,
in one suthe diyhele hale,
iherde ich holde grete tale
an hule and one niyhtingale."

deadlock
Posts: 2
Joined: Tue Nov 07, 2006 5:18 pm

Post by deadlock » Wed Nov 08, 2006 7:45 pm

do you have some diagrams of the database? it'll help the developers to work with the db. :wink:

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest