//
// Load authorized serial list for the supplied account
//
void CAccountManager::LoadAccountSerialUsage(CAccount* pAccount)
{
auto& outSerialUsageList = pAccount->GetSerialUsageList();
outSerialUsageList.clear();
CRegistryResult result;
m_pDatabaseManager->QueryWithResultf(m_hDbConnection, &result,
"SELECT "
" serial"
" ,added_ip"
" ,added_date"
" ,auth_who"
" ,auth_date"
" ,last_login_ip"
" ,last_login_date"
" ,last_login_http_date"
" FROM serialusage"
" WHERE userid=?",
SQLITE_INTEGER, pAccount->GetID());
for (CRegistryResultIterator iter = result->begin(); iter != result->end(); ++iter)
{
const CRegistryResultRow& row = *iter;
outSerialUsageList.push_back(CAccount::SSerialUsage());
CAccount::SSerialUsage& info = outSerialUsageList.back();
info.strSerial = (const char*)row[0].pVal;
info.strAddedIp = (const char*)row[1].pVal;
info.tAddedDate = row[2].GetNumber<time_t>();
info.strAuthWho = (const char*)row[3].pVal;
info.tAuthDate = row[4].GetNumber<time_t>();
info.strLastLoginIp = (const char*)row[5].pVal;
info.tLastLoginDate = row[6].GetNumber<time_t>();
info.tLastLoginHttpDate = row[7].GetNumber<time_t>();
}
}
bool CAccountManager::GetAllAccountData( CAccount* pAccount, lua_State* pLua )
{
//Get the user ID
int iUserID = pAccount->GetID();
//create a new registry result for the query return
CRegistryResult result;
SString strKey;
//Select the value and type from the database where the user is our user and the key is the required key
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT key,value,type from userdata where userid=?", SQLITE_INTEGER, iUserID );
//Do we have any results?
if ( result->nRows > 0 )
{
//Loop through until i is the same as the number of rows
for ( CRegistryResultIterator iter = result->begin() ; iter != result->end() ; ++iter )
{
const CRegistryResultRow& row = *iter;
//Get our key
strKey = (const char *)row[0].pVal;
//Get our type
int iType = static_cast < int > ( row[2].nVal );
//Account data is stored as text so we don't need to check what type it is just return it
if ( iType == LUA_TNIL )
{
lua_pushstring ( pLua, strKey );
lua_pushnil ( pLua );
lua_settable ( pLua, -3 );
}
if ( iType == LUA_TBOOLEAN )
{
SString strResult = (const char *)row[1].pVal;
lua_pushstring ( pLua, strKey );
lua_pushboolean ( pLua, strResult == "true" ? true : false );
lua_settable ( pLua, -3 );
}
if ( iType == LUA_TNUMBER )
{
lua_pushstring ( pLua, strKey );
lua_pushnumber ( pLua, strtod ( (const char*)row[1].pVal, NULL ) );
lua_settable ( pLua, -3 );
}
else
{
lua_pushstring ( pLua, strKey );
lua_pushstring ( pLua, ( (const char*)row[1].pVal ) );
lua_settable ( pLua, -3 );
}
}
return true;
}
return false;
}
void CAccountManager::GetAccountsBySerial ( const SString& strSerial, std::vector<CAccount*>& outAccounts )
{
CRegistryResult result;
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT name FROM accounts WHERE serial = ?", SQLITE_TEXT, strSerial.c_str () );
for ( CRegistryResultIterator iter = result->begin (); iter != result->end (); ++iter )
{
const CRegistryResultRow& row = *iter;
CAccount* pAccount = Get ( (const char*)row[0].pVal );
outAccounts.push_back ( pAccount );
}
}
CAccount* CAccountManager::GetAccountByID(int ID)
{
CRegistryResult result;
m_pDatabaseManager->QueryWithResultf(m_hDbConnection, &result, "SELECT name FROM accounts WHERE id = ?", SQLITE_INTEGER, ID);
for (CRegistryResultIterator iter = result->begin(); iter != result->end(); ++iter)
{
const auto& row = *iter;
return Get(reinterpret_cast<const char*>(row[0].pVal));
}
return nullptr;
}
void CAccountManager::GetAccountsByData ( const SString& dataName, const SString& value, std::vector<CAccount*>& outAccounts )
{
Save();
CRegistryResult result;
m_pDatabaseManager->QueryWithResultf( m_hDbConnection, &result, "SELECT acc.name FROM accounts acc, userdata dat WHERE dat.key = ? AND dat.value = ? AND dat.userid = acc.id", SQLITE_TEXT, dataName.c_str(), SQLITE_TEXT, value.c_str() );
for ( CRegistryResultIterator iter = result->begin(); iter != result->end(); ++iter )
{
const CRegistryResultRow& row = *iter;
CAccount* pAccount = Get( (const char*) row[0].pVal );
if (pAccount)
outAccounts.push_back( pAccount );
}
}
bool CAccountManager::CopyAccountData( CAccount* pFromAccount, CAccount* pToAccount )
{
//Get the user ID of the from account
int iUserID = pFromAccount->GetID();
//create a new registry result for the from account query return value
CRegistryResult result;
//create a new registry result for the to account query return value
//initialize key and value strings
SString strKey;
SString strValue;
//Select the key and value from the database where the user is our from account
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT key,value,type from userdata where userid=? LIMIT 1", SQLITE_INTEGER, iUserID );
//Do we have any results?
if ( result->nRows > 0 ) {
//Loop through until i is the same as the number of rows
for ( CRegistryResultIterator iter = result->begin() ; iter != result->end() ; ++iter )
{
const CRegistryResultRow& row = *iter;
//Get our key
strKey = (const char *)row[0].pVal;
//Get our value
strValue = (const char *)row[1].pVal;
int iType = static_cast < int > ( row[2].nVal );
//Select the id and userid where the user is the to account and the key is strKey
CRegistryResult subResult;
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &subResult, "SELECT id,userid from userdata where userid=? and key=? LIMIT 1", SQLITE_INTEGER, iUserID, SQLITE_TEXT, strKey.c_str () );
//If there is a key with this value update it otherwise insert it and store the return value in bRetVal
if ( subResult->nRows > 0 )
m_pDatabaseManager->Execf ( m_hDbConnection, "UPDATE userdata SET value=?, type=? WHERE userid=? AND key=?", SQLITE_TEXT, strValue.c_str (), SQLITE_INTEGER, iType, SQLITE_INTEGER, pToAccount->GetID (), SQLITE_TEXT, strKey.c_str () );
else
m_pDatabaseManager->Execf ( m_hDbConnection, "INSERT INTO userdata (userid, key, value, type) VALUES(?,?,?,?)", SQLITE_INTEGER, pToAccount->GetID (), SQLITE_TEXT, strKey.c_str (), SQLITE_TEXT, strValue.c_str (), SQLITE_INTEGER, iType );
}
}
else
//We had no results so return false (Nothing has changed)
return false;
return true;
}
bool CAccountManager::CopyAccountData( CAccount* pFromAccount, CAccount* pToAccount )
{
// list to store pFromAccount data to
std::map < SString, CAccountData > copiedData;
if ( !pFromAccount->IsRegistered () ) // is not registered account, retrieve data from memory
{
std::map < SString, CAccountData > ::iterator iter = pFromAccount->DataBegin ();
for ( ; iter != pFromAccount->DataEnd (); iter++ )
{
MapSet( copiedData, iter->second.GetKey(), CAccountData( iter->second.GetKey(), iter->second.GetStrValue(), iter->second.GetType() ) );
}
}
else // is registered account, retrieve from database
{
SString strKey;
SString strValue;
//Get the user ID of the from account
int iUserID = pFromAccount->GetID ();
//create a new registry result for the from account query return value
CRegistryResult result;
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT key,value,type from userdata where userid=?", SQLITE_INTEGER, iUserID );
//Do we have any results?
if ( result->nRows > 0 )
{
for ( CRegistryResultIterator iter = result->begin() ; iter != result->end() ; ++iter )
{
const CRegistryResultRow& row = *iter;
//Get our key
strKey = (const char *)row[0].pVal;
//Get our value
strValue = (const char *)row[1].pVal;
int iType = static_cast < int > ( row[2].nVal );
MapSet( copiedData, strKey, CAccountData ( strKey, strValue, iType ) );
}
}
}
if (copiedData.size () > 0) // got anything to copy?
{
std::map < SString, CAccountData > ::iterator iter = copiedData.begin ();
for (; iter != copiedData.end(); iter++)
{
if ( !pToAccount->IsRegistered () ) // store to memory
{
pToAccount->SetData ( iter->second.GetKey (), iter->second.GetStrValue (), iter->second.GetType () );
}
else // store to database
{
CRegistryResult subResult;
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &subResult, "SELECT id,userid from userdata where userid=? and key=? LIMIT 1", SQLITE_INTEGER, pToAccount->GetID (), SQLITE_TEXT, iter->second.GetKey ().c_str() );
//If there is a key with this value update it otherwise insert it and store the return value in bRetVal
if ( subResult->nRows > 0 )
m_pDatabaseManager->Execf ( m_hDbConnection, "UPDATE userdata SET value=?, type=? WHERE userid=? AND key=?", SQLITE_TEXT, iter->second.GetStrValue ().c_str(), SQLITE_INTEGER, iter->second.GetType (), SQLITE_INTEGER, pToAccount->GetID (), SQLITE_TEXT, iter->second.GetKey ().c_str() );
else
m_pDatabaseManager->Execf ( m_hDbConnection, "INSERT INTO userdata (userid, key, value, type) VALUES(?,?,?,?)", SQLITE_INTEGER, pToAccount->GetID (), SQLITE_TEXT, iter->second.GetKey ().c_str(), SQLITE_TEXT, iter->second.GetStrValue ().c_str(), SQLITE_INTEGER, iter->second.GetType () );
}
}
return true;
}
else
return false;
}
bool CAccountManager::Load( void )
{
//Create a registry result
CRegistryResult result;
//Select all our required information from the accounts database
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT id,name,password,ip,serial,httppass from accounts" );
//Initialize all our variables
m_iAccounts = 0;
bool bNeedsVacuum = false;
CElapsedTime activityTimer;
bool bOutputFeedback = false;
for ( CRegistryResultIterator iter = result->begin() ; iter != result->end() ; ++iter )
{
const CRegistryResultRow& row = *iter;
//Fill User ID, Name & Password (Required data)
int iUserID = static_cast < int > ( row[0].nVal );
SString strName = (const char *)row[1].pVal;
SString strPassword = (const char *)row[2].pVal;
SString strIP = (const char *)row[3].pVal;
SString strSerial = (const char *)row[4].pVal;
SString strHttpPassAppend = (const char *)row[5].pVal;
// Check for overlong names and incorrect escapement
bool bRemoveAccount = false;
bool bChanged = false;
if ( strName.length () > 64 )
{
// Try to repair name
if ( strName.length () <= 256 )
{
strName = strName.Replace ( "\"\"", "\"", true ).substr ( 0, 64 );
bChanged = true;
}
// If name gone doolally or account with this name already exists, remove account
if ( strName.length () > 256 || Get ( strName ) )
{
bNeedsVacuum = true;
bRemoveAccount = true;
CLogger::LogPrintf ( "Removed duplicate or damaged account for %s\n", strName.substr ( 0, 64 ).c_str() );
}
}
// Check for disallowed account names
if ( strName == "*****" || strName == CONSOLE_ACCOUNT_NAME )
bRemoveAccount = true;
// Do account remove if required
if ( bRemoveAccount )
{
m_pDatabaseManager->Execf ( m_hDbConnection, "DELETE FROM accounts WHERE id=?", SQLITE_INTEGER, iUserID );
m_pDatabaseManager->Execf ( m_hDbConnection, "DELETE FROM userdata WHERE userid=?", SQLITE_INTEGER, iUserID );
m_pDatabaseManager->Execf ( m_hDbConnection, "DELETE FROM serialusage WHERE userid=?", SQLITE_INTEGER, iUserID );
continue;
}
//Create a new account with the specified information
CAccount* pAccount = g_pGame->GetAccountManager ()->AddPlayerAccount ( strName, strPassword, iUserID, strIP, strSerial, strHttpPassAppend );
if ( bChanged )
pAccount->SetChanged ( bChanged );
m_iAccounts = std::max ( m_iAccounts, iUserID );
// Feedback for the user
if ( activityTimer.Get() > 5000 )
{
activityTimer.Reset();
bOutputFeedback = true;
CLogger::LogPrintf ( "Reading accounts %d/%d\n", m_List.size(), result->nRows );
}
}
if ( bOutputFeedback )
CLogger::LogPrintf ( "Reading accounts done.\n");
if ( bNeedsVacuum )
m_pDatabaseManager->Execf ( m_hDbConnection, "VACUUM" );
// Save any upgraded accounts
{
CElapsedTime activityTimer;
bool bOutputFeedback = false;
uint uiSaveCount = 0;
for ( CMappedAccountList::const_iterator iter = m_List.begin () ; iter != m_List.end () ; iter++ )
{
CAccount* pAccount = *iter;
if ( pAccount->IsRegistered () && pAccount->HasChanged () && !pAccount->IsConsoleAccount () )
{
uiSaveCount++;
Save ( pAccount, false );
// Feedback for the user
if ( activityTimer.Get() > 5000 )
{
activityTimer.Reset();
bOutputFeedback = true;
CLogger::LogPrintf ( "Saving upgraded accounts %d\n", uiSaveCount );
}
}
}
if ( uiSaveCount > 100 )
{
bOutputFeedback = true;
CLogger::LogPrintf ( "Finishing accounts upgrade...\n" );
for ( uint i = 0 ; i < 10 ; i++ )
{
Sleep( 10 );
m_pDatabaseManager->DoPulse ();
}
}
if ( bOutputFeedback )
CLogger::LogPrintf ( "Completed accounts upgrade.\n");
}
return true;
}
///////////////////////////////////////////////////////////////
//
// CPerfStatBandwidthUsageImpl::LoadStats
//
// Load from permanent storage
//
///////////////////////////////////////////////////////////////
void CPerfStatBandwidthUsageImpl::LoadStats ( void )
{
SFixedArray < uint, BWSTAT_INDEX_COUNT > uiSizeList;
uiSizeList [ BWSTAT_INDEX_SPECIAL ] = 1;
uiSizeList [ BWSTAT_INDEX_HOURS ] = NUM_HOUR_STATS;
uiSizeList [ BWSTAT_INDEX_DAYS ] = NUM_DAY_STATS;
uiSizeList [ BWSTAT_INDEX_MONTHS ] = NUM_MONTH_STATS;
//
// Clear current
//
m_History.clear ();
m_History.resize ( BWSTAT_INDEX_COUNT );
for ( uint t = 0 ; t < m_History.size () ; t++ )
{
SNetStatHistoryType& type = m_History [ t ];
type.itemList.resize ( uiSizeList [ t ] );
type.nowIndex = -1;
for ( uint r = 0 ; r < type.itemList.size () ; r++ )
{
assert ( type.itemList [ r ].bDirty == false );
assert ( type.itemList [ r ].llGameRecv == 0 );
assert ( type.itemList [ r ].llGameRecvBlocked == 0 );
assert ( type.itemList [ r ].llGameSent == 0 );
assert ( type.itemList [ r ].llHttpSent == 0 );
}
}
//
// Fetch table from database
//
CDatabaseManager* pDatabaseManager = g_pGame->GetDatabaseManager ();
CDbJobData* pJobData = pDatabaseManager->QueryStartf ( m_DatabaseConnection, "SELECT `type`,`idx`,`GameRecv`,`GameSent`,`HttpSent`,`GameRecvBlocked`,`GameResent` from " BW_STATS_TABLE_NAME );
pDatabaseManager->QueryPoll ( pJobData, -1 );
CRegistryResult result = pJobData->result.registryResult;
// If data set is empty, try loading old data
if ( result->nRows == 0 )
{
CDbJobData* pJobData = pDatabaseManager->QueryStartf ( m_DatabaseConnection, "SELECT `type`,`idx`,`GameRecv`,`GameSent`,`HttpSent`,`GameRecvBlocked` from " BW_STATS_TABLE_NAME );
pDatabaseManager->QueryPoll ( pJobData, -1 );
result = pJobData->result.registryResult;
}
// If data set is empty, try loading old data
if ( result->nRows == 0 )
{
pJobData = pDatabaseManager->QueryStartf ( m_DatabaseConnection, "SELECT `type`,`idx`,`GameRecv`,`GameSent`,`HttpSent` from " BW_STATS_TABLE_NAME );
pDatabaseManager->QueryPoll ( pJobData, -1 );
result = pJobData->result.registryResult;
}
// If data set is empty, try loading old data
if ( result->nRows == 0 )
g_pGame->GetRegistry ()->Query ( &result, "SELECT `type`,`idx`,`GameRecv`,`GameSent`,`HttpSent` from `_perfstats_bandwidth_usage`" );
if ( result->nRows > 0 && result->nColumns >= 5 )
{
for ( CRegistryResultIterator iter = result->begin() ; iter != result->end() ; ++iter )
{
const CRegistryResultRow& row = *iter;
SString strType = (const char*)row[0].pVal;
uint uiIndex = static_cast < uint > ( row[1].nVal );
float GameRecv = Max ( 0.f, row[2].fVal );
float GameSent = Max ( 0.f, row[3].fVal );
float HttpSent = Max ( 0.f, row[4].fVal );
float GameRecvBlocked = 0;
if ( result->nColumns >= 6 )
GameRecvBlocked = Max ( 0.f, row[5].fVal );
float GameResent = 0;
if ( result->nColumns >= 7 )
GameResent = Max ( 0.f, row[6].fVal );
uint uiType = BWStatNameToIndex ( strType );
if ( uiType < m_History.size () )
{
SNetStatHistoryType& type = m_History [ uiType ];
if ( uiIndex < type.itemList.size () )
{
type.itemList [ uiIndex ].llGameRecv = (long long)GameRecv;
type.itemList [ uiIndex ].llGameRecvBlocked = (long long)GameRecvBlocked;
type.itemList [ uiIndex ].llGameSent = (long long)GameSent;
type.itemList [ uiIndex ].llGameResent = (long long)GameResent;
type.itemList [ uiIndex ].llHttpSent = (long long)HttpSent;
}
}
}
}
//
// Adjust for time difference between last save and now
//
{
time_t tTime = UnixTimeNow ();
// Special item
const SNetStatHistoryType& type = m_History[ BWSTAT_INDEX_SPECIAL ];
uint uiStatsHoursThen = (uint)type.itemList[0].llGameSent; // Hours since 1/1/2011
uint uiStatsHoursNow = UnixTimeToStatsHours ( tTime ); // Hours since 1/1/2011
int iHoursElpased = uiStatsHoursNow - uiStatsHoursThen;
// Max elapsed time of 13 months
iHoursElpased = Min ( iHoursElpased, 730 * 13 );
// Skip forward in time to clear out past data
for ( int i = iHoursElpased - 1 ; i >= 0; i-- )
{
time_t tTime = StatsHoursToUnixTime ( uiStatsHoursNow - i );
AddSampleAtTime ( tTime, 0, 0, 0, 0, 0 );
}
}
//
// (Re)create table to ensure it's in sync with what we have
//
pDatabaseManager->Execf ( m_DatabaseConnection, "DROP TABLE " BW_STATS_TABLE_NAME );
pDatabaseManager->Execf ( m_DatabaseConnection, "CREATE TABLE IF NOT EXISTS " BW_STATS_TABLE_NAME " (`type` TEXT,`idx` INT, `GameRecv` REAL, `GameRecvBlocked` REAL, `GameSent` REAL, `GameResent` REAL, `HttpSent` REAL)" );
pDatabaseManager->Execf ( m_DatabaseConnection, "CREATE INDEX IF NOT EXISTS IDX_BW_STATS_TYPE_IDX on " BW_STATS_TABLE_NAME "(`type`,`idx`)" );
for ( uint t = 0 ; t < m_History.size () ; t++ )
{
const SNetStatHistoryType& type = m_History [ t ];
for ( uint r = 0 ; r < type.itemList.size () ; r++ )
{
pDatabaseManager->Execf ( m_DatabaseConnection,
"INSERT INTO " BW_STATS_TABLE_NAME " (`type`,`idx`,`GameRecv`,`GameRecvBlocked`,`GameSent`,`GameResent`,`HttpSent`) VALUES (?,?,?,?,?,?,?)"
, SQLITE_TEXT, *BWStatIndexToName ( t )
, SQLITE_INTEGER, r
, SQLITE_FLOAT, (float)type.itemList [ r ].llGameRecv
, SQLITE_FLOAT, (float)type.itemList [ r ].llGameRecvBlocked
, SQLITE_FLOAT, (float)type.itemList [ r ].llGameSent
, SQLITE_FLOAT, (float)type.itemList [ r ].llGameResent
, SQLITE_FLOAT, (float)type.itemList [ r ].llHttpSent
);
}
}
}
int CLuaDatabaseDefs::ExecuteSQLSelect ( lua_State* luaVM )
{
SString strTable;
SString strColumns;
SString strWhere;
SString strError;
unsigned int uiLimit;
CScriptArgReader argStream ( luaVM );
argStream.ReadString ( strTable );
argStream.ReadString ( strColumns );
argStream.ReadString ( strWhere, "" );
argStream.ReadNumber ( uiLimit, 0 );
if ( !argStream.HasErrors () )
{
CRegistryResult Result;
CPerfStatSqliteTiming::GetSingleton ()->SetCurrentResource ( luaVM );
if ( CStaticFunctionDefinitions::ExecuteSQLSelect ( strTable, strColumns, strWhere, uiLimit, &Result ) )
{
lua_newtable ( luaVM );
int i = 0;
for ( CRegistryResultIterator iter = Result->begin (); iter != Result->end (); ++iter, ++i )
{
const CRegistryResultRow& row = *iter;
// for ( int i = 0; i < Result.nRows; i++ ) {
lua_newtable ( luaVM ); // new table
lua_pushnumber ( luaVM, i + 1 ); // row index number (starting at 1, not 0)
lua_pushvalue ( luaVM, -2 ); // value
lua_settable ( luaVM, -4 ); // refer to the top level table
for ( int j = 0; j < Result->nColumns; j++ )
{
const CRegistryResultCell& cell = row[j];
if ( cell.nType == SQLITE_NULL )
continue;
// Push the column name
lua_pushlstring ( luaVM, Result->ColNames[j].c_str (), Result->ColNames[j].size () );
switch ( cell.nType ) // push the value with the right type
{
case SQLITE_INTEGER:
lua_pushnumber ( luaVM, static_cast <double> ( cell.nVal ) );
break;
case SQLITE_FLOAT:
lua_pushnumber ( luaVM, cell.fVal );
break;
case SQLITE_BLOB:
lua_pushlstring ( luaVM, (const char *) cell.pVal, cell.nLength );
break;
case SQLITE_TEXT:
lua_pushlstring ( luaVM, (const char *) cell.pVal, cell.nLength - 1 );
break;
default:
lua_pushnil ( luaVM );
}
lua_settable ( luaVM, -3 );
}
lua_pop ( luaVM, 1 ); // pop the inner table
}
return 1;
}
else
{
strError = "Database query failed: " + CStaticFunctionDefinitions::SQLGetLastError ();
m_pScriptDebugging->LogError ( luaVM, "%s", strError.c_str () );
lua_pushstring ( luaVM, strError );
lua_pushboolean ( luaVM, false );
return 2;
}
}
else
m_pScriptDebugging->LogCustom ( luaVM, argStream.GetFullErrorMessage () );
lua_pushboolean ( luaVM, false );
return 1;
}
CAccountManager::CAccountManager ( const char* szFileName, SString strBuffer ): CXMLConfig ( szFileName )
, m_AccountProtect( 6, 30000, 60000 * 1 ) // Max of 6 attempts per 30 seconds, then 1 minute ignore
{
m_bAutoLogin = false;
m_llLastTimeSaved = GetTickCount64_ ();
m_bChangedSinceSaved = false;
//set loadXML to false
m_bLoadXML = false;
m_iAccounts = 1;
//Load internal.db
m_pDatabaseManager = g_pGame->GetDatabaseManager ();
m_hDbConnection = m_pDatabaseManager->Connect ( "sqlite", PathConform ( strBuffer ) );
// Check if new installation
CRegistryResult result;
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT name FROM sqlite_master WHERE type='table' AND name='accounts'" );
bool bNewInstallation = ( result->nRows == 0 );
//Create all our tables (Don't echo the results)
m_pDatabaseManager->Execf ( m_hDbConnection, "CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, name TEXT, password TEXT, ip TEXT, serial TEXT)" );
m_pDatabaseManager->Execf ( m_hDbConnection, "CREATE TABLE IF NOT EXISTS userdata (id INTEGER PRIMARY KEY, userid INTEGER, key TEXT, value TEXT, type INTEGER)" );
m_pDatabaseManager->Execf ( m_hDbConnection, "CREATE TABLE IF NOT EXISTS settings (id INTEGER PRIMARY KEY, key TEXT, value INTEGER)" );
// Check if unique index on accounts exists
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT name FROM sqlite_master WHERE type='index' AND name='IDX_ACCOUNTS_NAME_U'" );
if ( result->nRows == 0 )
{
// Need to add unique index on accounts
if ( !bNewInstallation )
CLogger::LogPrintNoStamp ( "Updating accounts table...\n" );
// Make sure we have a non-unique index to speed up the duplication removal
m_pDatabaseManager->Execf ( m_hDbConnection, "CREATE INDEX IF NOT EXISTS IDX_ACCOUNTS_NAME on accounts(name)" );
// Remove any duplicate name entries
m_pDatabaseManager->Execf ( m_hDbConnection, "DELETE FROM accounts WHERE rowid in "
" (SELECT A.rowid"
" FROM accounts A, accounts B"
" WHERE A.rowid > B.rowid AND A.name = B.name)" );
// Remove non-unique index
m_pDatabaseManager->Execf ( m_hDbConnection, "DROP INDEX IF EXISTS IDX_ACCOUNTS_NAME" );
// Add unique index
m_pDatabaseManager->Execf ( m_hDbConnection, "CREATE UNIQUE INDEX IF NOT EXISTS IDX_ACCOUNTS_NAME_U on accounts(name)" );
}
// Check if unique index on userdata exists
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT name FROM sqlite_master WHERE type='index' AND name='IDX_USERDATA_USERID_KEY_U'" );
if ( result->nRows == 0 )
{
// Need to add unique index on userdata
if ( !bNewInstallation )
CLogger::LogPrintNoStamp ( "Updating userdata table...\n" );
// Make sure we have a non-unique index to speed up the duplication removal
m_pDatabaseManager->Execf ( m_hDbConnection, "CREATE INDEX IF NOT EXISTS IDX_USERDATA_USERID_KEY on userdata(userid,key)" );
// Remove any duplicate userid+key entries
m_pDatabaseManager->Execf ( m_hDbConnection, "DELETE FROM userdata WHERE rowid in "
" (SELECT A.rowid"
" FROM userdata A, userdata B"
" WHERE A.rowid > B.rowid AND A.userid = B.userid AND A.key = B.key)" );
// Remove non-unique index
m_pDatabaseManager->Execf ( m_hDbConnection, "DROP INDEX IF EXISTS IDX_USERDATA_USERID_KEY" );
// Add unique index
m_pDatabaseManager->Execf ( m_hDbConnection, "CREATE UNIQUE INDEX IF NOT EXISTS IDX_USERDATA_USERID_KEY_U on userdata(userid,key)" );
}
// Ensure old indexes are removed
m_pDatabaseManager->Execf ( m_hDbConnection, "DROP INDEX IF EXISTS IDX_ACCOUNTS_NAME" );
m_pDatabaseManager->Execf ( m_hDbConnection, "DROP INDEX IF EXISTS IDX_USERDATA_USERID" );
m_pDatabaseManager->Execf ( m_hDbConnection, "DROP INDEX IF EXISTS IDX_USERDATA_USERID_KEY" );
//Pull our settings
m_pDatabaseManager->QueryWithResultf ( m_hDbConnection, &result, "SELECT key, value from settings" );
//Did we get any results
if ( result->nRows == 0 )
{
//Set our settings and clear the accounts/userdata tables just in case
m_pDatabaseManager->Execf ( m_hDbConnection, "INSERT INTO settings (key, value) VALUES(?,?)", SQLITE_TEXT, "XMLParsed", SQLITE_INTEGER, 0 );
//Tell the Server to load the xml file rather than the SQL
m_bLoadXML = true;
}
else
{
bool bLoadXMLMissing = true;
for ( CRegistryResultIterator iter = result->begin() ; iter != result->end() ; ++iter )
{
const CRegistryResultRow& row = *iter;
SString strSetting = (const char *)row[0].pVal;
//Do we have a result for XMLParsed
if ( strSetting == "XMLParsed" )
{
//Is XMLParsed zero
if ( row[1].nVal == 0 )
{
//Tell the Server to load the xml file rather than the SQL
m_bLoadXML = true;
}
bLoadXMLMissing = false;
}
}
//if we didn't load the XMLParsed variable
if ( bLoadXMLMissing )
{
//Insert it
m_pDatabaseManager->Execf ( m_hDbConnection, "INSERT INTO settings (key, value) VALUES(?,?)", SQLITE_TEXT, "XMLParsed", SQLITE_INTEGER, 0 );
//Tell the Server to load the xml file rather than the SQL
m_bLoadXML = true;
}
}
//Check whether autologin was enabled in the main config
m_bAutoLogin = g_pGame->GetConfig()->IsAutoLoginEnabled();
}
