bool APIAccountDB::GetAccountInfo(uint32 accountID, std::vector<std::string> & accountInfoList)
{
DBQueryResult res;
// Get account table info using the accountID:
if( !sDatabase.RunQuery(res,
" SELECT "
" online, "
" banned, "
" logonCount, "
" lastLogin "
" FROM account "
" WHERE `accountID` = %u ", accountID ))
{
sLog.Error( "APIAccountDB::GetAccountInfo()", "Cannot find accountID %u", accountID );
return false;
}
DBResultRow row;
if( !res.GetRow(row) )
{
sLog.Error( "APIServiceDB::GetAccountInfo()", "res.GetRow(row) failed for unknown reason." );
return false;
}
accountInfoList.push_back( std::string(row.GetText(0)) );
accountInfoList.push_back( std::string(row.GetText(1)) );
accountInfoList.push_back( std::string(row.GetText(2)) );
accountInfoList.push_back( std::string(row.GetText(3)) );
return true;
}
void LSCDB::GetChannelNames(uint32 charID, std::vector<std::string> & names) {
DBQueryResult res;
if (!sDatabase.RunQuery(res,
" SELECT "
" entity.itemName AS characterName, "
" corporation.corporationName, "
" mapSolarSystems.solarSystemName, "
" mapConstellations.constellationName, "
" mapRegions.regionName "
" FROM character_ "
" LEFT JOIN entity ON character_.characterID = entity.itemID "
" LEFT JOIN corporation ON character_.corporationID = corporation.corporationID "
" LEFT JOIN mapSolarSystems ON character_.solarSystemID = mapSolarSystems.solarSystemID "
" LEFT JOIN mapConstellations ON character_.constellationID = mapConstellations.constellationID "
" LEFT JOIN mapRegions ON character_.regionID = mapRegions.regionID "
" WHERE character_.characterID = %u ", charID))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return;
}
DBResultRow row;
if (!res.GetRow(row)) {
_log(SERVICE__ERROR, "CharID %u isn't present in the database", charID);
return;
}
names.push_back(row.GetText(0)); // charName
names.push_back(row.GetText(1)); // corpName
names.push_back(row.GetText(2)); // solsysName
names.push_back(row.GetText(3)); // constName
names.push_back(row.GetText(4)); // regionName
}
bool APIServiceDB::GetApiAccountInfoUsingAccountID(std::string accountID, uint32 * userID, std::string * apiFullKey,
std::string * apiLimitedKey, uint32 * apiRole)
{
DBQueryResult res;
// Find userID, fullKey, limitedKey, and apiRole from 'accountApi' table using accountID obtained from 'account' table:
if( !sDatabase.RunQuery(res,
"SELECT"
" userID, fullKey, limitedKey, apiRole "
" FROM accountApi "
" WHERE accountID='%s'" , accountID.c_str() ))
{
sLog.Error( "APIServiceDB::GetApiAccountInfoUsingAccountID()", "Cannot find accountID '%s' in 'accountApi' table", accountID.c_str() );
return false;
}
DBResultRow row;
if( !res.GetRow(row) )
{
sLog.Error( "APIServiceDB::GetApiAccountInfoUsingAccountID()", "res.GetRow(row) failed for unknown reason." );
return false;
}
*userID = row.GetUInt(0); // Grab userID from retrieved row from the 'accountApi' table
*apiFullKey = row.GetText(1); // Grab Full API Key from retrieved row from the 'accountApi' table
*apiLimitedKey = row.GetText(2); // Grab Limited API Key from retrieved row from the 'accountApi' table
*apiRole = row.GetUInt(3); // Grab API Role from retrieved row from the 'accountApi' table
return true;
}
bool APIAccountDB::GetCharactersList(uint32 accountID, std::vector<std::string> & charIDList, std::vector<std::string> & charNameList,
std::vector<std::string> & charCorpIDList, std::vector<std::string> & charCorpNameList)
{
DBQueryResult res;
// Get list of characters and their corporation info from the accountID:
if( !sDatabase.RunQuery(res,
" SELECT "
" character_.characterID, "
" character_.corporationID, "
" corporation.corporationName, "
" entity.itemName AS name "
" FROM `character_` "
" LEFT JOIN corporation ON corporation.corporationID = character_.corporationID "
" LEFT JOIN entity ON entity.itemID = character_.characterID "
" WHERE `accountID` = %u ", accountID ))
{
sLog.Error( "APIAccountDB::GetCharactersList()", "Cannot find accountID %u", accountID );
return false;
}
DBResultRow row;
std::map<std::string, std::string> charInfo;
while( res.GetRow( row ) )
{
charIDList.push_back( std::string(row.GetText(0)) );
charCorpIDList.push_back( std::string(row.GetText(1)) );
charCorpNameList.push_back( std::string(row.GetText(2)) );
charNameList.push_back( std::string(row.GetText(3)) );
}
return true;
}
bool APICharacterDB::GetCharacterInfo(uint32 characterID, std::vector<std::string> & charInfoList)
{
DBQueryResult res;
// Get list of characters and their corporation info from the accountID:
if( !sDatabase.RunQuery(res,
" SELECT "
" character_.balance, "
" character_.skillPoints, "
" character_.corporationID, "
" character_.corpRole, "
" character_.rolesAtAll, "
" character_.rolesAtBase, "
" character_.rolesAtHQ, "
" character_.rolesAtOther, "
" character_.startDateTime, "
" character_.gender, "
" chrAncestries.ancestryName, "
" chrBloodlines.bloodlineName, "
" chrRaces.raceName, "
" entity.itemName, "
" corporation.corporationName "
" FROM character_ "
" LEFT JOIN chrAncestries ON character_.ancestryID = chrAncestries.ancestryID "
" LEFT JOIN chrBloodlines ON chrAncestries.bloodlineID = chrBloodlines.bloodlineID "
" LEFT JOIN chrRaces ON chrBloodlines.raceID = chrRaces.raceID "
" LEFT JOIN entity ON entity.itemID = character_.characterID "
" LEFT JOIN corporation ON corporation.corporationID = character_.corporationID "
" WHERE character_.characterID = %u ", characterID ))
{
sLog.Error( "APIAccountDB::GetCharacterSkillsTrained()", "Cannot find characterID %u", characterID );
return false;
}
DBResultRow row;
if( !res.GetRow(row) )
{
sLog.Error( "APIServiceDB::GetAccountIdFromUsername()", "res.GetRow(row) failed for unknown reason." );
return false;
}
charInfoList.push_back( std::string(row.GetText(0)) ); // 0. Balance
charInfoList.push_back( std::string(row.GetText(1)) ); // 1. Skill Points
charInfoList.push_back( std::string(row.GetText(2)) ); // 2. corporationID
charInfoList.push_back( std::string(row.GetText(3)) ); // 3. corp Role
charInfoList.push_back( std::string(row.GetText(4)) ); // 4. roles At All
charInfoList.push_back( std::string(row.GetText(5)) ); // 5. roles At Base
charInfoList.push_back( std::string(row.GetText(6)) ); // 6. roles At HQ
charInfoList.push_back( std::string(row.GetText(7)) ); // 7. roles At Other
charInfoList.push_back( std::string(row.GetText(8)) ); // 8. birthday
charInfoList.push_back( std::string(row.GetText(10)) ); // 9. ancestry Name
charInfoList.push_back( std::string(row.GetText(11)) ); // 10. bloodline Name
charInfoList.push_back( std::string(row.GetText(12)) ); // 11. race Name
charInfoList.push_back( std::string(row.GetText(13)) ); // 12. char Name
charInfoList.push_back( std::string(row.GetText(14)) ); // 13. corp Name
charInfoList.push_back( std::string(row.GetText(9)) ); // 14. gender (0 = female, 1 = male)
return true;
}
bool ServiceDB::GetAccountInformation( const char* username, const char* password, AccountInfo & account_info )
{
std::string _username = username;
std::string _escaped_username;
DBcore::DoEscapeString(_escaped_username, _username);
DBQueryResult res;
if (!DBcore::RunQuery(res, "SELECT accountID, password, hash, role, online, banned, logonCount, lastLogin FROM srvAccount WHERE accountName = '%s'", _escaped_username.c_str()))
{
SysLog::Error( "ServiceDB", "Error in query: %s.", res.error.c_str() );
return false;
}
DBResultRow row;
if (!res.GetRow( row )) {
// account not found, create new one if autoAccountRole is not zero (0)
if(EVEServerConfig::account.autoAccountRole > 0) {
uint32 accountID = CreateNewAccount( _username.c_str(), password, EVEServerConfig::account.autoAccountRole);
if( accountID > 0 ) {
// add new account successful, get account info again
bool ret = GetAccountInformation(username, password, account_info);
return ret;
}
else
return false;
}
else
return false;
}
/* when any of the text gets are NULL it will fail... I think.. */
account_info.id = row.GetUInt(0);
if (!row.IsNull(1))
account_info.password = row.GetText(1);
if (!row.IsNull(2))
account_info.hash = row.GetText(2);
account_info.name = _escaped_username;
account_info.role = row.GetUInt64(3);
account_info.online = row.GetBool(4);
account_info.banned = row.GetBool(5);
account_info.visits = row.GetUInt(6);
if (!row.IsNull(7))
account_info.last_login = row.GetText(7);
return true;
}
bool SystemDB::LoadSystemEntities(uint32 systemID, std::vector<DBSystemEntity> &into) {
DBQueryResult res;
if(!sDatabase.RunQuery(res,
"SELECT "
" itemID,typeID,groupID,orbitID,"
" x,y,z,radius,security,itemName"
" FROM mapDenormalize"
" WHERE solarSystemID=%u", systemID
))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return false;
}
DBResultRow row;
DBSystemEntity entry;
while(res.GetRow(row)) {
entry.itemID = row.GetInt(0);
entry.typeID = row.GetInt(1);
entry.groupID = row.GetInt(2);
entry.orbitID = (row.IsNull(3) ? 0 : row.GetInt(3));
entry.position.x = row.GetDouble(4);
entry.position.y = row.GetDouble(5);
entry.position.z = row.GetDouble(6);
entry.radius = (row.IsNull(7) ? 1 : row.GetDouble(7));
entry.security = (row.IsNull(8) ? 0.0 : row.GetDouble(8));
entry.itemName = row.GetText(9);
into.push_back(entry);
}
return true;
}
bool APIServiceDB::GetAccountIdFromUsername(std::string username, std::string * accountID)
{
DBQueryResult res;
// Find accountID in 'account' table using accountName:
if( !sDatabase.RunQuery(res,
"SELECT"
" accountID "
" FROM account "
" WHERE accountName='%s'" , username.c_str() ))
{
sLog.Error( "APIServiceDB::GetAccountIdFromUsername()", "Cannot find accountID for username %s", username.c_str() );
return false;
}
DBResultRow row;
if( !res.GetRow(row) )
{
sLog.Error( "APIServiceDB::GetAccountIdFromUsername()", "res.GetRow(row) failed for unknown reason." );
return false;
}
*accountID = row.GetText(0); // Grab accountID from the retrieved row from the 'account' table
return true;
}
std::string LSCDB::GetChannelInfo(uint32 channelID, std::string & name, std::string & motd)
{
DBQueryResult res;
if (!sDatabase.RunQuery(res,
" SELECT "
" displayName, "
" motd "
" FROM channels "
" WHERE channelID = %u ", channelID))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
char err[20];
snprintf(err, 20, "Unknown %u", channelID);
return(err);
}
DBResultRow row;
if (!res.GetRow(row)) {
_log(SERVICE__ERROR, "Couldn't find %u in table channels", channelID);
char err[20];
snprintf(err, 20, "Unknown %u", channelID);
return(err);
}
name = row.GetText(0);
motd = row.GetText(1);
return ("");
}
std::string LSCDB::GetChannelName(uint32 id, const char * table, const char * column, const char * key) {
DBQueryResult res;
if (!sDatabase.RunQuery(res,
" SELECT "
" %s "
" FROM %s "
" WHERE %s = %u ", column, table, key, id))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
char err[20];
snprintf(err, 20, "Unknown %u", id);
return(err);
}
DBResultRow row;
if (!res.GetRow(row)) {
_log(SERVICE__ERROR, "Couldn't find %s %u in table %s", key, id, table);
char err[20];
snprintf(err, 20, "Unknown %u", id);
return(err);
}
return (row.GetText(0));
}
bool CorporationDB::CreateMemberAttributeUpdate(MemberAttributeUpdate & attrib, uint32 newCorpID, uint32 charID) {
// What are we doing here exactly?
// Corporation gets a new member
// it's new to it
DBQueryResult res;
DBResultRow row;
if (!sDatabase.RunQuery(res,
" SELECT "
" title, corporationDateTime, corporationID, "
" corpRole, rolesAtAll, rolesAtBase, "
" rolesAtHQ, rolesAtOther "
" FROM character_ "
" WHERE character_.characterID = %u ", charID))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return false;
}
if (!res.GetRow(row)) {
codelog(SERVICE__ERROR, "Cannot find character in database");
return false;
}
// this could be stored in the db
#define PRN new PyNone()
#define PRI(i) new PyInt(i)
#define PRL(i) new PyLong(i)
#define PRS(s) new PyString(s)
#define PRNI(i) (row.IsNull(i) ? PRI(0) : PRI(row.GetUInt64(i)))
#define F(name, o, n) \
attrib.name##Old = o; \
attrib.name##New = n
//element Old Value New Value
F(accountKey, PRN, PRN);
// i don't even know what this could refer to
F(baseID, PRN, PRN);
F(characterID, PRN, PRI(charID));
F(corporationID, PRI(row.GetUInt(2)), PRI(newCorpID));
// these also have to be queried from the db
F(divisionID, PRN, PRN);
F(roles, PRNI(3), PRI(0));
F(grantableRoles, PRNI(4), PRI(0));
F(grantableRolesAtBase, PRNI(5), PRI(0));
F(grantableRolesAtHQ, PRNI(6), PRI(0));
F(grantableRolesAtOther, PRNI(7), PRI(0));
F(squadronID, PRN, PRN);
F(startDateTime, PRL(row.GetUInt64(1)), PRL(Win32TimeNow()));
// another one i have no idea
F(titleMask, PRN, PRI(0));
F(baseID, PRS(row.GetText(0)), PRS(""));
#undef F
#undef PRN
#undef PRI
#undef PRS
#undef PRNI
return true;
}
bool CorporationDB::GetCurrentApplicationInfo(uint32 charID, uint32 corpID, ApplicationInfo & aInfo) {
DBQueryResult res;
if (!sDatabase.RunQuery(res,
" SELECT "
" status, applicationText, applicationDateTime, roles, grantableRoles, lastCorpUpdaterID, deleted "
" FROM chrApplications "
" WHERE characterID = %u AND corporationID = %u ",
charID, corpID))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
aInfo.valid = false;
return false;
}
DBResultRow row;
if (!res.GetRow(row)) {
_log(DATABASE__ERROR, "There's no previous application.");
aInfo.valid = false;
return false;
}
aInfo.charID = charID;
aInfo.corpID = corpID;
aInfo.status = row.GetUInt(0);
aInfo.appText = row.GetText(1);
aInfo.appTime = row.GetUInt64(2);
aInfo.role = row.GetUInt64(3);
aInfo.grantRole = row.GetUInt64(4);
aInfo.lastCID = row.GetUInt(5);
aInfo.deleted = row.GetUInt(6);
aInfo.valid = true;
return true;
}
void CharacterDB::load_name_validation_set()
{
DBQueryResult res;
if(!sDatabase.RunQuery(res,
"SELECT"
" characterID, itemName AS characterName"
" FROM character_"
" JOIN entity ON characterID = itemID"
))
{
codelog(SERVICE__ERROR, "Error in query for %s", res.error.c_str());
return;
}
DBResultRow row;
while(res.GetRow(row) == true)
{
uint32 characterID = row.GetUInt(0);
const char* name = row.GetText(1);
//printf("initializing name validation: %s\n", name);
uint32 hash = djb2_hash(name);
mNameValidation.insert(hash);
mIdNameContainer.insert(std::make_pair(characterID, name));
}
}
bool CommandDB::ItemSearch(const char *query, std::map<uint32, std::string> &into) {
std::string escaped;
sDatabase.DoEscapeString(escaped, query);
DBQueryResult result;
DBResultRow row;
into.clear();
//we need to query out the primary message here... not sure how to properly
//grab the "main message" though... the text/plain clause is pretty hackish.
if (!sDatabase.RunQuery(result,
" SELECT typeID,typeName"
" FROM invTypes"
" WHERE"
" typeName rlike '%s'",
escaped.c_str()
))
{
codelog(SERVICE__ERROR, "Error in query: %s", result.error.c_str());
return (false);
}
while(result.GetRow(row)) {
into[row.GetUInt(0)] = row.GetText(1);
}
return true;
}
bool SystemDB::LoadSystemDynamicEntities(uint32 systemID, std::vector<DBSystemDynamicEntity> &into) {
DBQueryResult res;
if(!sDatabase.RunQuery(res,
"SELECT"
" entity.itemID,"
" entity.itemName,"
" entity.typeID,"
" entity.ownerID,"
" entity.locationID,"
" entity.flag,"
" invTypes.groupID,"
" invGroups.categoryID,"
" 0,"//" character_.corporationID,"
" 0,"//" corporation.allianceID,"
" x,"
" y,"
" z"
" FROM entity, invTypes, invGroups"//, character_, corporation"
" WHERE"
" entity.typeID=invTypes.typeID"
" AND invTypes.groupID=invGroups.groupID"
" AND invGroups.categoryID NOT IN (%d,%d)"
//" AND character_.characterID = entity.ownerID"
//" AND corporation.corporationID = character_.corporationID"
" AND locationID=%u",
//excluded categories:
//celestials:
EVEDB::invCategories::_System, EVEDB::invCategories::Station,
//NPCs:
//EVEDB::invCategories::Entity,
systemID
))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return false;
}
DBResultRow row;
DBSystemDynamicEntity entry;
while(res.GetRow(row)) {
entry.itemID = row.GetInt(0);
entry.itemName = row.GetText(1);
entry.typeID = row.GetInt(2);
entry.ownerID = row.GetInt(3);
entry.locationID = row.GetInt(4);
entry.flag = row.GetInt(5);
entry.groupID = row.GetInt(6);
entry.categoryID = row.GetInt(7);
entry.corporationID = row.GetInt(8);
entry.allianceID = row.GetInt(9);
entry.x = row.GetDouble(10);
entry.y = row.GetDouble(11);
entry.z = row.GetDouble(12);
into.push_back(entry);
}
return true;
}
bool CorporationDB::UpdateDivisionNames(uint32 corpID, const Call_UpdateDivisionNames & divs, PyDict * notif) {
DBQueryResult res;
if (!sDatabase.RunQuery(res,
" SELECT "
" division1, division2, division3, division4, division5, division6, division7 "
" FROM corporation "
" WHERE corporationID = %u ", corpID))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return false;
}
DBResultRow row;
if (!res.GetRow(row)) {
_log(DATABASE__ERROR, "Corporation %u doesn't exists.", corpID);
return false;
}
// We are here, so something must have changed...
std::vector<std::string> dbQ;
ProcessStringChange("division1", row.GetText(0), divs.div1, notif, dbQ);
ProcessStringChange("division2", row.GetText(1), divs.div2, notif, dbQ);
ProcessStringChange("division3", row.GetText(2), divs.div3, notif, dbQ);
ProcessStringChange("division4", row.GetText(3), divs.div4, notif, dbQ);
ProcessStringChange("division5", row.GetText(4), divs.div5, notif, dbQ);
ProcessStringChange("division6", row.GetText(5), divs.div6, notif, dbQ);
ProcessStringChange("division7", row.GetText(6), divs.div7, notif, dbQ);
std::string query = " UPDATE corporation SET ";
int N = dbQ.size();
for (int i = 0; i < N; i++) {
query = dbQ[i];
if (i < N - 1) query += ", ";
}
query += " WHERE corporationID = %u";
if ((N > 0) && (!sDatabase.RunQuery(res.error, query.c_str(), corpID))) {
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return false;
}
return true;
}
PyRep *LSCDB::GetMailDetails(uint32 messageID, uint32 readerID) {
DBQueryResult result;
DBResultRow row;
//we need to query out the primary message here... not sure how to properly
//grab the "main message" though... the text/plain clause is pretty hackish.
if (!sDatabase.RunQuery(result,
" SELECT eveMail.messageID, eveMail.senderID, eveMail.subject, " // need messageID as char*
" eveMailDetails.attachment, eveMailDetails.mimeTypeID, "
" eveMailMimeType.mimeType, eveMailMimeType.`binary`, "
" eveMail.created, eveMail.channelID "
" FROM eveMail "
" LEFT JOIN eveMailDetails"
" ON eveMailDetails.messageID = eveMail.messageID "
" LEFT JOIN eveMailMimeType"
" ON eveMailMimeType.mimeTypeID = eveMailDetails.mimeTypeID "
" WHERE eveMail.messageID=%u"
" AND channelID=%u",
messageID, readerID
))
{
codelog(SERVICE__ERROR, "Error in query: %s", result.error.c_str());
return (NULL);
}
if (!result.GetRow(row)) {
codelog(SERVICE__MESSAGE, "No message with messageID %u", messageID);
return (NULL);
}
Rsp_GetEVEMailDetails details;
details.messageID = row.GetUInt(0);
details.senderID = row.GetUInt(1);
details.subject = row.GetText(2);
details.body = row.GetText(3);
details.created = row.GetUInt64(7);
details.channelID = row.GetUInt(8);
details.deleted = 0; // If a message's details are sent, then it isn't deleted. If it's deleted, details cannot be sent
details.mimeTypeID = row.GetInt(4);
details.mimeType = row.GetText(5);
details.binary = row.GetInt(6);
return(details.Encode());
}
//this is a crap load of work... there HAS to be a better way to do this..
PyRep *MarketDB::GetMarketGroups() {
DBQueryResult res;
DBResultRow row;
if(!sDatabase.RunQuery(res,
"SELECT * "
" FROM invMarketGroups"))
{
codelog(MARKET__ERROR, "Error in query: %s", res.error.c_str());
return NULL;
}
DBRowDescriptor *header = new DBRowDescriptor(res);
CFilterRowSet *filterRowset = new CFilterRowSet(&header);
PyDict *keywords = filterRowset->GetKeywords();
keywords->SetItemString("giveMeSets", new PyBool(false)); //+
keywords->SetItemString("allowDuplicateCompoundKeys", new PyBool(false)); //+
keywords->SetItemString("indexName", new PyNone); //+
keywords->SetItemString("columnName", new PyString("parentGroupID")); //+
std::map< int, PyRep* > tt;
while( res.GetRow(row) )
{
int parentGroupID = ( row.IsNull( 0 ) ? -1 : row.GetUInt( 0 ) );
PyRep *pid;
CRowSet *rowset;
if(tt.count(parentGroupID) == 0) {
pid = parentGroupID!=-1 ? (PyRep*)new PyInt(parentGroupID) : (PyRep*)new PyNone();
tt.insert( std::pair<int, PyRep*>(parentGroupID, pid) );
rowset = filterRowset->NewRowset(pid);
} else {
pid = tt[parentGroupID];
rowset = filterRowset->GetRowset(pid);
}
PyPackedRow* pyrow = rowset->NewRow();
pyrow->SetField((uint32)0, pid); //prentGroupID
pyrow->SetField(1, new PyInt(row.GetUInt( 1 ) ) ); //marketGroupID
pyrow->SetField(2, new PyString(row.GetText( 2 ) ) ); //marketGroupName
pyrow->SetField(3, new PyString(row.GetText( 3 ) ) ); //description
pyrow->SetField(4, row.IsNull( 4 ) ?
(PyRep*)(new PyNone()) : new PyInt(row.GetUInt( 4 )) ); //graphicID
pyrow->SetField(5, new PyBool(row.GetBool( 5 ) ) ); //hasTypes
pyrow->SetField(6, row.IsNull( 6 ) ?
(PyRep*)(new PyNone()) : new PyInt(row.GetUInt( 6 )) ); // iconID
pyrow->SetField(7, new PyInt( row.GetUInt(7) ) ); //dataID
pyrow->SetField(8, new PyInt( row.GetUInt(8) ) ); //marketGroupNameID
pyrow->SetField(9, new PyInt( row.GetUInt(9) ) ); //descriptionID
}
return filterRowset;
}
bool CorporationDB::UpdateCorporation(uint32 corpID, const Call_UpdateCorporation & upd, PyDict * notif) {
DBQueryResult res;
if (!sDatabase.RunQuery(res,
" SELECT description, url, taxRate "
" FROM corporation "
" WHERE corporationID = %u ", corpID))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return false;
}
DBResultRow row;
if (!res.GetRow(row)) {
_log(DATABASE__ERROR, "Corporation %u doesn't exists.", corpID);
return false;
}
std::vector<std::string> dbQ;
ProcessStringChange("description", row.GetText(0), upd.description, notif, dbQ);
ProcessStringChange("url", row.GetText(1), upd.address, notif, dbQ);
ProcessRealChange("taxRate", row.GetDouble(2), upd.tax, notif, dbQ);
std::string query = " UPDATE corporation SET ";
int N = dbQ.size();
for (int i = 0; i < N; i++) {
query += dbQ[i];
if (i < N - 1) query += ", ";
}
query += " WHERE corporationID = %u";
// only update if there is anything to update
if ((N > 0) && (!sDatabase.RunQuery(res.error, query.c_str(), corpID))) {
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return false;
}
return true;
}
bool BookmarkDB::GetBookmarkInformation(uint32 bookmarkID, uint32 &ownerID, uint32 &itemID, uint32 &typeID,
uint32 &flag, std::string &memo, uint64 &created, double &x, double &y,
double &z, uint32 &locationID, std::string ¬e, uint32 &creatorID,
uint32 folderID)
{
DBQueryResult res;
DBResultRow row;
// Query database 'srvBookmarks' table for the supplied bookmarkID and retrieve entire row:
if (!DBcore::RunQuery(res,
" SELECT "
" bookmarkID, "
" ownerID, "
" itemID, "
" typeID, "
" flag, "
" memo, "
" created, "
" x, "
" y, "
" z, "
" locationID, "
" note,"
" creatorID,"
" folderID"
" FROM srvBookmarks "
" WHERE bookmarkID = %u ", bookmarkID))
{
SysLog::Error( "BookmarkDB::GetBookmarkInformation()", "Error in query: %s", res.error.c_str() );
return false;
}
// Query went through, but check to see if there were zero rows, ie bookmarkID was invalid:
if ( !(res.GetRow(row)) )
return false;
// Bookmark 'bookmarkID' was found, Send back bookmark information:
ownerID = row.GetUInt(1);
itemID = row.GetUInt(2);
typeID = row.GetUInt(3);
flag = row.GetUInt(4);
memo = row.GetText(5);
created = row.GetUInt64(6);
x = row.GetDouble(7);
y = row.GetDouble(8);
z = row.GetDouble(9);
locationID = row.GetUInt(10);
note = row.GetUInt(11);
creatorID = row.GetUInt(12);
folderID = row.GetUInt(13);
return true;
}
bool ServiceDB::GetSystemInfo(uint32 systemID, uint32 *constellationID, uint32 *regionID, std::string *name, std::string *securityClass) {
if( constellationID == NULL
&& regionID == NULL
&& name == NULL
&& securityClass == NULL
)
return true;
DBQueryResult res;
if(!DBcore::RunQuery(res,
"SELECT"
" constellationID,"
" regionID,"
" solarSystemName,"
" securityClass"
" FROM mapSolarSystems"
" WHERE solarSystemID = %u",
systemID))
{
_log(DATABASE__ERROR, "Failed to query info for system %u: %s.", systemID, res.error.c_str());
return false;
}
DBResultRow row;
if(!res.GetRow(row)) {
_log(DATABASE__ERROR, "Failed to query info for system %u: System not found.", systemID);
return false;
}
if(constellationID != NULL)
*constellationID = row.GetUInt(0);
if(regionID != NULL)
*regionID = row.GetUInt(1);
if(name != NULL)
*name = row.GetText(2);
if(securityClass != NULL)
*securityClass = row.IsNull(3) ? "" : row.GetText(3);
return true;
}
PyRep *CharacterDB::GetCharSelectInfo(uint32 characterID) {
DBQueryResult res;
uint32 worldSpaceID = 0;
std::string shipName = "My Ship";
uint32 shipTypeID = 606;
DBQueryResult res2;
if(!sDatabase.RunQuery(res2, "SELECT itemName, typeID FROM entity WHERE itemID = (SELECT shipID FROM character_ WHERE characterID = %u)", characterID))
{
codelog(SERVICE__WARNING, "Unable to get current ship: %s", res.error.c_str());
}
else
{
DBResultRow row;
while(res2.GetRow(row))
{
sDatabase.DoEscapeString(shipName, row.GetText(0));
shipTypeID = row.GetUInt(1);
}
}
uint32 unreadMailCount = 0;
uint32 upcomingEventCount = 0;
uint32 unprocessedNotifications = 0;
uint32 daysLeft = 14;
uint32 userType = 23;
uint64 skillQueueEndTime = ( Win32TimeNow() + (5*Win32Time_Hour) + (25*Win32Time_Minute) );
uint64 allianceMemberStartDate = Win32TimeNow() - 15*Win32Time_Day;
uint64 startDate = Win32TimeNow() - 24*Win32Time_Day;
if(!sDatabase.RunQuery(res,
"SELECT "
" itemName AS shortName,bloodlineID,gender,bounty,character_.corporationID,allianceID,title,startDateTime,createDateTime,"
" securityRating,character_.balance, 0 As aurBalance,character_.stationID,solarSystemID,constellationID,regionID,"
" petitionMessage,logonMinutes,tickerName, %u AS worldSpaceID, '%s' AS shipName, %u AS shipTypeID, %u AS unreadMailCount,"
" %u AS upcomingEventCount, %u AS unprocessedNotifications, %u AS daysLeft, %u AS userType, 0 AS paperDollState, 0 AS newPaperdollState,"
" 0 AS oldPaperdollState, skillPoints, %" PRIu64 " AS skillQueueEndTime, %" PRIu64 " AS allianceMemberStartDate, %" PRIu64 " AS startDate,"
" 0 AS locationSecurity"
" FROM character_ "
" LEFT JOIN entity ON characterID = itemID"
" LEFT JOIN corporation USING (corporationID)"
" LEFT JOIN bloodlineTypes USING (typeID)"
" WHERE characterID=%u", worldSpaceID, shipName.c_str(), shipTypeID, unreadMailCount, upcomingEventCount, unprocessedNotifications, daysLeft, userType, skillQueueEndTime, allianceMemberStartDate, startDate, characterID))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return NULL;
}
return DBResultToCRowset(res);
}
PyString* MailDB::GetMailBody(int id) const
{
DBQueryResult res;
if (!sDatabase.RunQuery(res, "SELECT body FROM mailMessage WHERE messageID = %u", id))
return NULL;
if (res.GetRowCount() <= 0)
return NULL;
DBResultRow row;
if (!res.GetRow(row) || row.IsNull(0))
return NULL;
return new PyString(row.GetText(0), row.ColumnLength(0));
}
AgentLevel *MissionDB::LoadAgentLevel(uint8 level) {
AgentLevel *result = new AgentLevel;
DBQueryResult res;
if(!sDatabase.RunQuery(res,
"SELECT missionID,missionName,missionLevel,"
" agtMissions.missionTypeID,missionTypeName,"
" importantMission"
" FROM agtMissions"
" NATURAL JOIN agtMissionTypes"
" WHERE missionLevel=%d",
level
))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
delete result;
return NULL;
}
std::vector<uint32> IDs;
DBResultRow row;
IDs.clear();
while(res.GetRow(row)) {
AgentMissionSpec *spec = new AgentMissionSpec;
spec->missionID = row.GetUInt(0);
spec->missionName = row.GetText(1);
spec->missionLevel = row.GetUInt(2);
spec->missionTypeID = row.GetUInt(3);
spec->missionTypeName = row.GetText(1);
spec->importantMission = (row.GetUInt(2)==0)?false:true;
result->missions.push_back(spec);
}
}
PyString *CharacterDB::GetNote(uint32 ownerID, uint32 itemID) {
DBQueryResult res;
if (!sDatabase.RunQuery(res,
"SELECT `note` FROM `chrNotes` WHERE ownerID = %u AND itemID = %u",
ownerID, itemID)
)
{
codelog(SERVICE__ERROR, "Error on query: %s", res.error.c_str());
return NULL;
}
DBResultRow row;
if(!res.GetRow(row))
return NULL;
return(new PyString(row.GetText(0)));
}
PyRep* MailDB::GetLabels(int characterID) const
{
DBQueryResult res;
if (!sDatabase.RunQuery(res, "SELECT bit, name, color, ownerId FROM mailLabel WHERE ownerID = %u", characterID))
return NULL;
PyDict* ret = new PyDict();
DBResultRow row;
while (res.GetRow(row))
{
MailLabel label;
label.id = (int)pow((float)2, row.GetInt(0));
label.name = row.GetText(1);
label.color = row.GetInt(2);
ret->SetItem(new PyInt(label.id), label.Encode());
}
return ret;
}
std::string RamProxyDB::GetStationName(const uint32 stationID) {
DBQueryResult res;
if(!DBcore::RunQuery(res,
"SELECT stationName"
" FROM staStations"
" WHERE stationID = %u",
stationID))
{
_log(DATABASE__ERROR, "Failed to query station name of station %u: %s.", stationID, res.error.c_str());
return("");
}
DBResultRow row;
if(!res.GetRow(row)) {
_log(DATABASE__ERROR, "Station %u not found.", stationID);
return("");
}
return(row.GetText(0));
}
//replace all the typeID of the character's clones
bool CorporationDB::ChangeCloneType(uint32 characterID, uint32 typeID) {
DBQueryResult res;
DBerror err;
if(!sDatabase.RunQuery(res,
"SELECT "
" typeID, typeName "
"FROM "
" invTypes "
"WHERE typeID = %u",
typeID))
{
_log(DATABASE__ERROR, "Failed to change clone type of char %u: %s.", characterID, res.error.c_str());
return false;
}
DBResultRow row;
if( !(res.GetRow(row)) )
{
sLog.Error( "CorporationDB::ChangeCloneType()", "Could not find Clone typeID = %u in invTypes table.", typeID );
return false;
}
std::string typeNameString = row.GetText(1);
if(!sDatabase.RunQuery(err,
"UPDATE "
"entity "
"SET typeID=%u, itemName='%s' "
"where ownerID=%u "
"and flag='400'",
typeID,
typeNameString.c_str(),
characterID))
{
_log(DATABASE__ERROR, "Failed to change clone type of char %u: %s.", characterID, err.c_str());
return false;
}
sLog.Debug( "CorporationDB", "Clone upgrade successful" );
return true;
}
// Function: Query 'channels' table for the channel whose 'displayName' matches the name specified,
// then return all parameters for that channel.
void LSCDB::GetChannelInformation(std::string & name, uint32 & id,
std::string & motd, uint32 & ownerid, std::string & compkey,
bool & memberless, std::string & password, bool & maillist,
uint32 & cspa, uint32 & temp, uint32 & mode)
{
DBQueryResult res;
if (!sDatabase.RunQuery(res,
" SELECT "
" channelID, "
" displayName, "
" motd, "
" ownerID, "
" comparisonKey, "
" memberless, "
" password, "
" mailingList, "
" cspa, "
" temporary, "
" mode "
" FROM channels "
" WHERE displayName = upper('%s')", name.c_str()))
{
codelog(SERVICE__ERROR, "Error in query: %s", res.error.c_str());
return;
}
DBResultRow row;
if (!(res.GetRow(row)))
{
_log(SERVICE__ERROR, "Channel named '%s' isn't present in the database", name.c_str() );
return;
}
id = row.GetUInt(0);
name = (row.GetText(1) == NULL ? "" : row.GetText(1)); // empty displayName field in channels table row returns NULL, so fill this string with "" in that case
motd = (row.GetText(2) == NULL ? "" : row.GetText(2)); // empty motd field in channels table row returns NULL, so fill this string with "" in that case
ownerid = row.GetUInt(3);
compkey = (row.GetText(4) == NULL ? "" : row.GetText(4)); // empty comparisonKey field in channels table row returns NULL, so fill this string with "" in that case
memberless = row.GetUInt(5) ? true : false;
password = (row.GetText(6) == NULL ? "" : row.GetText(6)); // empty password field in channels table row returns NULL, so fill this string with "" in that case
maillist = row.GetUInt(7) ? true : false;
cspa = row.GetUInt(8);
temp = row.GetUInt(9);
mode = row.GetUInt(10);
}
bool CommandDB::ItemSearch(uint32 typeID, uint32 &actualTypeID,
std::string &actualTypeName, uint32 &actualGroupID, uint32 &actualCategoryID, double &actualRadius)
{
DBQueryResult result;
DBResultRow row;
if (!sDatabase.RunQuery(result,
"SELECT "
" invTypes.typeID,"
" invTypes.typeName,"
" invTypes.groupID,"
" invTypes.radius,"
" invGroups.categoryID"
" FROM invTypes"
" LEFT JOIN invGroups"
" ON invGroups.groupID = invTypes.groupID"
" WHERE typeID = %u",
typeID
))
{
sLog.Error( "CommandDB::ItemSearch()", "Error in query: %s", result.error.c_str() );
return (false);
}
if( !result.GetRow(row) )
{
sLog.Error( "CommandDB::ItemSearch()", "Query returned NO results: %s", result.error.c_str() );
return (false);
}
// Extract values from the first row:
actualTypeID = row.GetUInt( 0 );
actualTypeName = row.GetText( 1 );
actualGroupID = row.GetUInt( 2 );
actualCategoryID = row.GetUInt( 4 );
actualRadius = row.GetDouble( 3 );
return true;
}