//johnsus - serverStartType mod
void ServiceDB::SetServerOnlineStatus(bool onoff_status) {
DBerror err;
_log(onoff_status ? SERVER__INIT : SERVER__SHUTDOWN, "SrvStatus: Server is %s, setting serverStartTime.", onoff_status ? "coming Online" : "going Offline");
if(!DBcore::RunQuery(err,
"REPLACE INTO srvStatus (config_name, config_value)"
" VALUES ('%s', %s)",
"serverStartTime",
onoff_status ? "UNIX_TIMESTAMP(CURRENT_TIMESTAMP)" : "0"))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
_log(SERVER__INIT, "SrvStatus: Resetting ConnectSinceStartup.");
if(!DBcore::RunQuery(err, "REPLACE INTO srvStatus (config_name, config_value)"
" VALUES ('%s', '0')",
"connectSinceStartup"))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
_log(CLIENT__TRACE, "ChrStatus: Setting all characters and accounts offline.");
if(!DBcore::RunQuery(err,
"UPDATE srvCharacter, srvAccount"
" SET srvCharacter.online = 0,"
" srvAccount.online = 0"))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
}
bool CharacterDB::SetNote(uint32 ownerID, uint32 itemID, const char *str) {
DBerror err;
if (str[0] == '\0') {
// str is empty
if (!sDatabase.RunQuery(err,
"DELETE FROM `chrNotes` "
" WHERE itemID = %u AND ownerID = %u LIMIT 1",
ownerID, itemID)
)
{
codelog(CLIENT__ERROR, "Error on query: %s", err.c_str());
return false;
}
} else {
// escape it before insertion
std::string escaped;
sDatabase.DoEscapeString(escaped, str);
if (!sDatabase.RunQuery(err,
"REPLACE INTO `chrNotes` (itemID, ownerID, note) "
"VALUES (%u, %u, '%s')",
ownerID, itemID, escaped.c_str())
)
{
codelog(CLIENT__ERROR, "Error on query: %s", err.c_str());
return false;
}
}
return true;
}
bool CorporationDB::JoinCorporation(uint32 charID, uint32 corpID, uint32 oldCorpID, const CorpMemberInfo &roles) {
// TODO: check for free member place
DBerror err;
// Decrease previous corp's member count
if (!sDatabase.RunQuery(err,
"UPDATE corporation "
" SET corporation.memberCount = corporation.memberCount-1"
" WHERE corporation.corporationID = %u",
oldCorpID
))
{
codelog(SERVICE__ERROR, "Error in prev corp member decrease query: %s", err.c_str());
return false;
}
// Set new corp
if (!sDatabase.RunQuery(err,
"UPDATE character_ SET "
" corporationID = %u, corporationDateTime = "I64u", "
" corpRole = "I64u", rolesAtAll = "I64u", rolesAtBase = "I64u", rolesAtHQ = "I64u", rolesAtOther = "I64u" "
" WHERE characterID = %u",
corpID, Win32TimeNow(),
roles.corpRole, roles.rolesAtAll, roles.rolesAtBase, roles.rolesAtHQ, roles.rolesAtOther,
charID
))
{
codelog(SERVICE__ERROR, "Error in char update query: %s", err.c_str());
//TODO: undo previous member count decrement.
return false;
}
// Increase new corp's member number...
if (!sDatabase.RunQuery(err,
"UPDATE corporation "
" SET memberCount = memberCount+1"
" WHERE corporationID = %u",
corpID
))
{
codelog(SERVICE__ERROR, "Error in new corp member decrease query: %s", err.c_str());
//dont stop now, we are already moved... else we need to undo everything we just did.
}
// Add new employment history record
if (!sDatabase.RunQuery(err,
"INSERT INTO chrEmployment VALUES (%u, %u, "I64u", 0)",
charID, corpID, Win32TimeNow()
))
{
codelog(SERVICE__ERROR, "Error in employment insert query: %s", err.c_str());
//dont stop now, we are already moved... else we need to undo everything we just did.
}
return true;
}
bool MarketDB::BuildOldPriceHistory() {
DBerror err;
uint64 cutoff_time = Win32TimeNow();
cutoff_time -= cutoff_time % Win32Time_Day; //round down to an even day boundary.
cutoff_time -= HISTORY_AGGREGATION_DAYS * Win32Time_Day;
//build the history record from the recent market transactions.
if(!sDatabase.RunQuery(err,
"INSERT INTO"
" market_history_old"
" (regionID, typeID, historyDate, lowPrice, highPrice, avgPrice, volume, orders)"
" SELECT"
" regionID,"
" typeID,"
" transactionDateTime - ( transactionDateTime %% %" PRId64 " ) AS historyDate,"
" MIN(price) AS lowPrice,"
" MAX(price) AS highPrice,"
" AVG(price) AS avgPrice,"
" SUM(quantity) AS volume,"
" COUNT(transactionID) AS orders"
" FROM market_transactions "
" WHERE"
" transactionType=%d AND " //both buy and sell transactions get recorded, only compound one set of data... choice was arbitrary.
" ( transactionDateTime - ( transactionDateTime %% %" PRId64 " ) ) < %" PRId64
" GROUP BY regionID, typeID, historyDate",
Win32Time_Day,
TransactionTypeBuy,
Win32Time_Day,
cutoff_time
))
{
codelog(MARKET__ERROR, "Error in query: %s", err.c_str());
return false;
}
//now remove the transactions which have been aged out?
if(!sDatabase.RunQuery(err,
"DELETE FROM"
" market_transactions"
" WHERE"
" transactionDateTime < %" PRId64,
cutoff_time))
{
codelog(MARKET__ERROR, "Error in query: %s", err.c_str());
return false;
}
return true;
}
bool APIServiceDB::InsertNewUserIdApiKeyInfoToDatabase(uint32 accountID, std::string apiFullKey, std::string apiLimitedKey, uint32 apiRole)
{
// Check key lengths and report error and return if either are incorrect:
if( apiLimitedKey.length() != 64 )
{
sLog.Error( "APIServiceDB::UpdateUserIdApiKeyDatabaseRow()", "limitedApiKey length != 64, but rather %u", apiLimitedKey.length() );
return false;
}
if( apiFullKey.length() != 64 )
{
sLog.Error( "APIServiceDB::UpdateUserIdApiKeyDatabaseRow()", "fullApiKey length != 64, but rather %u", apiFullKey.length() );
return false;
}
DBerror err;
if( !sDatabase.RunQuery(err,
"INSERT INTO"
" accountApi ("
" accountID, fullKey, limitedKey, apiRole"
" ) VALUES ("
" %u, '%s', '%s', %u"
" )",
accountID, apiFullKey.c_str(), apiLimitedKey.c_str(), apiRole
))
{
sLog.Error( "", "Error in query: %s.", err.c_str());
return false;
}
else
return true;
}
bool ShipDB::InsertInsuranceByShipID(uint32 shipID, double fraction)
{
uint64 startDate = Win32TimeNow();
uint64 endDate = startDate + (Win32Time_Day * 84);
DBerror err;
if(!sDatabase.RunQuery(err,
"INSERT INTO "
" insurance ("
" shipID, "
" startDate, "
" endDate, "
" fraction "
") VALUES ("
" %d, "
" %" PRIu64 ", "
" %" PRIu64 ", "
" %.4f "
")",
shipID,
startDate,
endDate,
fraction
))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
return false;
}
return true;
}
bool RamProxyDB::InstallJob(const uint32 ownerID, const uint32 installerID, const uint32 assemblyLineID, const uint32 installedItemID, const uint64 beginProductionTime, const uint64 endProductionTime, const char *description, const uint32 runs, const EVEItemFlags outputFlag, const uint32 installedInSolarSystem, const int32 licensedProductionRuns) {
DBerror err;
// insert job
if(!DBcore::RunQuery(err,
"INSERT INTO srvRamJobs"
" (ownerID, installerID, assemblyLineID, installedItemID, installTime, beginProductionTime, endProductionTime, description, runs, outputFlag,"
" completedStatusID, installedInSolarSystemID, licensedProductionRuns)"
" VALUES"
" (%u, %u, %u, %u, %" PRIu64 ", %" PRIu64 ", %" PRIu64 ", '%s', %u, %d, 0, %u, %li)",
ownerID, installerID, assemblyLineID, installedItemID, Win32TimeNow(), beginProductionTime, endProductionTime, description,
runs, (int)outputFlag, installedInSolarSystem, licensedProductionRuns))
{
_log(DATABASE__ERROR, "Failed to insert new job to database: %s.", err.c_str());
return false;
}
// update nextFreeTime
if(!DBcore::RunQuery(err,
"UPDATE ramAssemblyLines"
" SET nextFreeTime = %" PRIu64
" WHERE assemblyLineID = %u",
endProductionTime, assemblyLineID))
{
_log(DATABASE__ERROR, "Failed to update next free time for assembly line %u: %s.", assemblyLineID, err.c_str());
return false;
}
return true;
}
bool DBcore::DoQuery_locked(DBerror &err, const char *query, int32 querylen, bool retry)
{
if (pStatus != Connected)
Open_locked();
if (mysql_real_query(&mysql, query, querylen)) {
int num = mysql_errno(&mysql);
if (num == CR_SERVER_GONE_ERROR)
pStatus = Error;
if (retry && (num == CR_SERVER_LOST || num == CR_SERVER_GONE_ERROR))
{
sLog.Error("DBCore", "Lost connection, attempting to recover....");
return DoQuery_locked(err, query, querylen, false);
}
pStatus = Error;
err.SetError(num, mysql_error(&mysql));
sLog.Error("DBCore Query", "#%d in '%s': %s", err.GetErrNo(), query, err.c_str());
return false;
}
err.ClearError();
return true;
}
bool MarketDB::RecordTransaction(
uint32 typeID,
uint32 quantity,
double price,
MktTransType transactionType,
uint32 charID,
uint32 regionID,
uint32 stationID
) {
DBerror err;
if(!sDatabase.RunQuery(err,
"INSERT INTO"
" market_transactions ("
" transactionID, transactionDateTime, typeID, quantity,"
" price, transactionType, clientID, regionID, stationID,"
" corpTransaction"
" ) VALUES ("
" NULL, %" PRIu64 ", %u, %u,"
" %f, %d, %u, %u, %u, 0"
" )",
Win32TimeNow(), typeID, quantity,
price, transactionType, charID, regionID, stationID
))
{
codelog(MARKET__ERROR, "Error in query: %s", err.c_str());
return false;
}
return true;
}
bool APIServiceDB::UpdateUserIdApiKeyDatabaseRow(uint32 userID, std::string apiFullKey, std::string apiLimitedKey)
{
// Check key lengths and report error and return if either are incorrect:
if( apiLimitedKey.length() != 64 )
{
sLog.Error( "APIServiceDB::UpdateUserIdApiKeyDatabaseRow()", "limitedApiKey length != 64, but rather %u", apiLimitedKey.length() );
return false;
}
if( apiFullKey.length() != 64 )
{
sLog.Error( "APIServiceDB::UpdateUserIdApiKeyDatabaseRow()", "fullApiKey length != 64, but rather %u", apiFullKey.length() );
return false;
}
// Update fullKey and limitedKey in the 'accountApi' table using userID:
DBerror err;
if( !sDatabase.RunQuery(err,
"UPDATE"
" accountApi"
" SET fullKey = '%s', limitedKey = '%s'"
" WHERE userID = %u",
apiFullKey.c_str(), apiLimitedKey.c_str(), userID ))
{
sLog.Error( "", "Error in query: %s.", err.c_str());
return false;
}
else
return true;
}
//temporarily relocated into ServiceDB until some things get cleaned up...
uint32 LSCDB::StoreMail(uint32 senderID, uint32 recipID, const char * subject, const char * message, uint64 sentTime) {
DBQueryResult res;
DBerror err;
DBResultRow row;
std::string escaped;
// Escape message header
sDatabase.DoEscapeString(escaped, subject);
// Store message header
uint32 messageID;
if (!sDatabase.RunQueryLID(err, messageID,
" INSERT INTO "
" eveMail "
" (channelID, senderID, subject, created) "
" VALUES (%u, %u, '%s', "I64u") ",
recipID, senderID, escaped.c_str(), sentTime ))
{
codelog(SERVICE__ERROR, "Error in query, message header couldn't be saved: %s", err.c_str());
return (0);
}
_log(SERVICE__MESSAGE, "New messageID: %u", messageID);
// Escape message content
sDatabase.DoEscapeString(escaped, message);
// Store message content
if (!sDatabase.RunQuery(err,
" INSERT INTO eveMailDetails "
" (messageID, mimeTypeID, attachment) VALUES (%u, 1, '%s') ",
messageID, escaped.c_str()
))
{
codelog(SERVICE__ERROR, "Error in query, message content couldn't be saved: %s", err.c_str());
// Delete message header
if (!sDatabase.RunQuery(err, "DELETE FROM `eveMail` WHERE `messageID` = %u;", messageID))
{
codelog(SERVICE__ERROR, "Failed to remove invalid header data for messgae id %u: %s", messageID, err.c_str());
}
return (0);
}
return (messageID);
}
bool ServiceDB::AddBalanceToCorp(uint32 corpID, double amount) {
DBerror err;
if (!sDatabase.RunQuery(err, "UPDATE corporation SET balance = balance + (%lf) WHERE corporationID = %u ", amount, corpID))
{
sLog.Error("Service DB", "Error in query: %s", err.c_str());
return false;
}
return true;
}
// Need to find out wether there is any kind of limit regarding the offices
uint32 CorporationDB::ReserveOffice(const OfficeInfo & oInfo) {
// oInfo should at this point contain the station, officeFolder and corporation infos
// First check if we have a free office at this station at all...
// Instead, assume that there is, and add one for this corporation
DBerror err;
// First add it into the entity table
uint32 officeID = 0;
if (!sDatabase.RunQueryLID(err, officeID,
" INSERT INTO entity ("
" itemName, typeID, ownerID, locationID, flag, contraband, singleton, "
" quantity, x, y, z, customInfo "
" ) VALUES ("
// office name should be more descriptive
// corporation owns the office, station locates the office
// x, y, z should be coords of the station?
// no extra info
" 'office', 27, %u, %u, 0, 0, 1, 1, 0, 0, 0, '' "
" ); ", oInfo.corporationID, oInfo.stationID ))
{
codelog(SERVICE__ERROR, "Error in query at ReserveOffice: %s", err.c_str());
return(0);
}
// inserts with the id gotten previously
if (!sDatabase.RunQuery(err,
" INSERT INTO crpOffices "
" (corporationID, stationID, itemID, typeID, officeFolderID) "
" VALUES "
" (%u, %u, %u, %u, %u) ",
oInfo.corporationID, oInfo.stationID, officeID, oInfo.typeID, oInfo.officeFolderID))
{
codelog(SERVICE__ERROR, "Error in query at ReserveOffice: %s", err.c_str());
// Ensure that officeID stays 0, whatever the RunQueryLID done...
return(0);
}
// If insert is successful, oInfo.officeID now contains the rented office's ID
// Nothing else to do...
return(officeID);
}
bool CorporationDB::DeleteApplication(const ApplicationInfo & info) {
DBerror err;
if (!sDatabase.RunQuery(err,
" DELETE FROM chrApplications "
" WHERE corporationID = %u AND characterID = %u ", info.corpID, info.charID))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
return false;
}
return true;
}
bool ShipDB::DeleteInsuranceByShipID(uint32 shipID)
{
DBerror err;
if(!sDatabase.RunQuery(err, "DELETE FROM insurance WHERE shipID=%d", shipID))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
return false;
}
return true;
}
void CharacterDB::SetAvatar(uint32 charID, PyRep* hairDarkness) {
//populate the DB wiht avatar information
DBerror err;
if(!sDatabase.RunQuery(err,
"INSERT INTO avatars ("
"charID, hairDarkness)"
"VALUES (%u, %f)",
charID, hairDarkness->AsFloat()->value()))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
}
void CharacterDB::SetAvatarColors(uint32 charID, uint32 colorID, uint32 colorNameA, uint32 colorNameBC, double weight, double gloss) {
//add avatar colors to the DB
DBerror err;
if(!sDatabase.RunQuery(err,
"INSERT INTO avatar_colors ("
"charID, colorID, colorNameA, colorNameBC, weight, gloss)"
"VALUES (%u, %u, %u, %u, %f, %f)",
charID, colorID, colorNameA, colorNameBC, weight, gloss))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
}
bool MarketDB::AddCharacterBalance(uint32 char_id, double delta)
{
DBerror err;
if(!sDatabase.RunQuery(err,
"UPDATE character_ SET balance=balance+%.2f WHERE characterID=%u",delta,char_id))
{
_log(SERVICE__ERROR, "Error in query : %s", err.c_str());
return false;
}
return (true);
}
uint32 MarketDB::_StoreOrder(
uint32 clientID,
uint32 accountID,
uint32 stationID,
uint32 typeID,
double price,
uint32 quantity,
uint8 orderRange,
uint32 minVolume,
uint8 duration,
bool isCorp,
bool isBuy
) {
DBerror err;
uint32 solarSystemID;
uint32 regionID;
if(!GetStationInfo(stationID, &solarSystemID, NULL, ®ionID, NULL, NULL, NULL)) {
codelog(MARKET__ERROR, "Char %u: Failed to find parents for station %u", clientID, stationID);
return(0);
}
//TODO: figure out what the orderState field means...
//TODO: implement the contraband flag properly.
//TODO: implement the isCorp flag properly.
uint32 orderID;
if(!sDatabase.RunQueryLID(err, orderID,
"INSERT INTO market_orders ("
" typeID, charID, regionID, stationID,"
" `range`, bid, price, volEntered, volRemaining, issued,"
" orderState, minVolume, contraband, accountID, duration,"
" isCorp, solarSystemID, escrow, jumps "
" ) VALUES ("
" %u, %u, %u, %u, "
" %u, %u, %f, %u, %u, %" PRIu64 ", "
" 1, %u, 0, %u, %u, "
" %u, %u, 0, 1"
" )",
typeID, clientID, regionID, stationID,
orderRange, isBuy?1:0, price, quantity, quantity, Win32TimeNow(),
minVolume, accountID, duration,
isCorp?1:0, solarSystemID
))
{
codelog(MARKET__ERROR, "Error in query: %s", err.c_str());
return(0);
}
return(orderID);
}
void ServiceDB::SetAccountOnlineStatus(uint32 accountID, bool onoff_status) {
DBerror err;
_log(CLIENT__TRACE, "AccStatus: Setting account %u %s.", accountID, onoff_status ? "Online" : "Offline");
if(!DBcore::RunQuery(err,
"UPDATE srvAccount "
" SET srvAccount.online = %d "
" WHERE accountID = %u ",
onoff_status, accountID))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
}
void ServiceDB::SetAccountBanStatus(uint32 accountID, bool onoff_status) {
DBerror err;
_log(CLIENT__TRACE, "AccStatus: %s account %u.", onoff_status ? "Banned" : "Removed ban on", accountID );
if(!DBcore::RunQuery(err,
" UPDATE srvAccount "
" SET srvAccount.banned = %d "
" WHERE accountID = %u ",
onoff_status, accountID))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
}
bool MarketDB::DeleteOrder(uint32 orderID) {
DBerror err;
if(!sDatabase.RunQuery(err,
"DELETE"
" FROM market_orders"
" WHERE orderID = %u",
orderID))
{
_log(MARKET__ERROR, "Error in query: %s.", err.c_str());
return false;
}
return true;
}
//johnsus - characterOnline mod
void ServiceDB::SetCharacterOnlineStatus(uint32 char_id, bool onoff_status) {
DBerror err;
_log(CLIENT__TRACE, "ChrStatus: Setting character %u %s.", char_id, onoff_status ? "Online" : "Offline");
if(!DBcore::RunQuery(err,
"UPDATE srvCharacter"
" SET online = %d"
" WHERE characterID = %u",
onoff_status, char_id))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
if( onoff_status )
{
_log(CLIENT__TRACE, "SrvStatus: Incrementing ConnectSinceStartup.");
if(!DBcore::RunQuery(err, "UPDATE srvStatus SET config_value = config_value + 1 WHERE config_name = 'connectSinceStartup'"))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
}
}
void CharacterDB::SetAvatarModifiers(uint32 charID, PyRep* modifierLocationID, PyRep* paperdollResourceID, PyRep* paperdollResourceVariation) {
//add avatar modifiers to the DB
DBerror err;
if(!sDatabase.RunQuery(err,
"INSERT INTO avatar_modifiers ("
"charID, modifierLocationID, paperdollResourceID, paperdollResourceVariation)"
"VALUES (%u, %u, %u, %u)",
charID,
modifierLocationID->AsInt()->value(),
paperdollResourceID->AsInt()->value(),
paperdollResourceVariation->IsInt() ? paperdollResourceVariation->AsInt()->value() : 0 ))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
}
}
bool LSCDB::MarkMessageRead(uint32 messageID) {
DBerror err;
if (!sDatabase.RunQuery(err,
" UPDATE eveMail "
" SET `read` = 1 "
" WHERE messageID=%u", messageID
))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
return false;
}
return true;
}
bool LSCDB::DeleteMessage(uint32 messageID, uint32 readerID) {
DBerror err;
bool ret = true;
if (!sDatabase.RunQuery(err,
" DELETE FROM eveMail "
" WHERE messageID=%u AND channelID=%u", messageID, readerID
))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
ret = false;
}
if (!sDatabase.RunQuery(err,
" DELETE FROM eveMailDetails "
" WHERE messageID=%u", messageID
))
{
codelog(SERVICE__ERROR, "Error in query: %s", err.c_str());
ret = false;
}
return ret;
}
PyRep *MarketDB::CompleteContract( uint32 contractID, uint32 characterID, uint32 unknown )
{
DBerror err;
if(!sDatabase.RunQuery(err,
"UPDATE contract"
" SET status=%d,"
" acceptorID=%d"
" WHERE contractID=%d", unknown, characterID, contractID))
{
codelog(MARKET__ERROR, "Error in query: %s", err.c_str() );
return new PyBool(false);
}
return new PyBool(true);
}
//NOTE: this logic needs some work if there are multiple concurrent market services running at once.
bool MarketDB::AlterOrderQuantity(uint32 orderID, uint32 new_qty) {
DBerror err;
if(!sDatabase.RunQuery(err,
"UPDATE"
" market_orders"
" SET volRemaining = %u"
" WHERE orderID = %u",
new_qty, orderID))
{
_log(MARKET__ERROR, "Error in query: %s.", err.c_str());
return false;
}
return true;
}
bool CharacterDB::EditOwnerNote(uint32 charID, uint32 noteID, const std::string & label, const std::string & content) {
DBerror err;
std::string contS;
sDatabase.DoEscapeString(contS, content);
if (!sDatabase.RunQuery(err,
"UPDATE chrOwnerNote SET note = '%s' WHERE ownerID = %u AND noteID = %u;",
contS.c_str(), charID, noteID))
{
codelog(SERVICE__ERROR, "Error on query: %s", err.c_str());
return false;
}
return true;
}
bool MarketDB::AlterOrderPrice(uint32 orderID, double new_price) {
DBerror err;
if(!sDatabase.RunQuery(err,
"UPDATE"
" market_orders"
" SET price = %f"
" WHERE orderID = %u",
new_price, orderID))
{
_log(MARKET__ERROR, "Error in query: %s.", err.c_str());
return false;
}
return true;
}
