SQLStatement* SqliteStatementCache::InternalGetStatement(const char* func_name,
int func_number, const char* sql)
{
FuncID id;
id.name = func_name;
id.number = func_number;
StatementMap::const_iterator found = statements_.find(id);
if (found != statements_.end())
return found->second;
if (!sql)
return NULL; // Don't create a new statement when we were not given SQL.
// Create a new statement.
SQLStatement* statement = new SQLStatement();
if (statement->prepare(db_, sql) != SQLITE_OK)
{
const char* err_msg = sqlite3_errmsg(db_);
return NULL;
}
statements_[id] = statement;
return statement;
}
void
Repeater::do_load()
{
SQLStatement statement
( database_connection(),
"select interval_units, interval_type_id, next_date, journal_id "
"from repeaters where repeater_id = :p"
);
statement.bind(":p", id());
statement.step();
Repeater temp(*this);
temp.m_data->frequency = Frequency
( statement.extract<int>(0),
static_cast<IntervalType>(statement.extract<int>(1))
);
temp.m_data->next_date =
numeric_cast<DateRep>(statement.extract<long long>(2));
temp.m_data->journal_id = statement.extract<Id>(3);
swap(temp);
JEWEL_ASSERT
( is_valid_date_for_interval_type
( boost_date_from_julian_int(value(m_data->next_date)),
value(m_data->frequency).step_type()
)
);
return;
}
bool
PersistentSecurityRange::Exists(const String &name)
{
String whereClause = "rangename = '" + SQLStatement::Escape(name) + "'";
SQLStatement oStatement;
oStatement.SetStatementType(SQLStatement::STSelect);
oStatement.SetTable("hm_securityranges");
oStatement.AddColumn("count(*) as c");
oStatement.SetWhereClause(whereClause);
shared_ptr<DALRecordset> pRS = Application::Instance()->GetDBManager()->OpenRecordset(oStatement);
if (!pRS)
return false;
bool bRetVal = false;
if (!pRS->IsEOF())
{
int count = pRS->GetLongValue("c");
if (count > 0)
return true;
}
return false;
}
void SQLTransaction::deliverStatementCallback()
{
// Spec 4.3.2.6.6 and 4.3.2.6.3: If the statement callback went wrong, jump to the transaction error callback
// Otherwise, continue to loop through the statement queue
m_executeSqlAllowed = true;
SQLStatement* currentStatement = m_backend->currentStatement();
ASSERT(currentStatement);
bool result = currentStatement->performCallback(this);
m_executeSqlAllowed = false;
if (result) {
m_transactionError = SQLError::create(SQLError::UNKNOWN_ERR, "the statement callback raised an exception or statement error callback did not return false");
if (m_errorCallbackWrapper.hasCallback())
return deliverTransactionErrorCallback();
// No error callback, so fast-forward to:
// Transaction Step 11 - Rollback the transaction.
m_backend->requestTransitToState(SQLTransactionState::CleanupAfterTransactionErrorCallback);
return;
}
m_backend->requestTransitToState(SQLTransactionState::RunStatements);
}
bool
PersistentSecurityRange::SaveObject(shared_ptr<SecurityRange> pSR, String &result)
{
if (!Validate(pSR, result))
return false;
DateTime rangeExpiresTime = pSR->GetExpiresTime();
if (rangeExpiresTime.GetStatus() != DateTime::valid)
rangeExpiresTime.SetDateTime(2001,01,01,0,0,0);
String name = pSR->GetName();
if (name.GetLength() > 100)
name = name.Mid(0, 100);
IPAddressSQLHelper helper;
SQLStatement oStatement;
oStatement.SetTable("hm_securityranges");
oStatement.AddColumn("rangename", name);
oStatement.AddColumn("rangepriorityid", pSR->GetPriority());
helper.AppendStatement(oStatement, pSR->GetLowerIP(), "rangelowerip1", "rangelowerip2");
helper.AppendStatement(oStatement, pSR->GetUpperIP(), "rangeupperip1", "rangeupperip2");
oStatement.AddColumn("rangeoptions", pSR->GetOptions());
oStatement.AddColumn("rangeexpires", pSR->GetExpires());
oStatement.AddColumn("rangeexpirestime", Time::GetTimeStampFromDateTime(rangeExpiresTime));
if (pSR->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("rangeid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("rangeid = %I64d"), pSR->GetID());
oStatement.SetWhereClause(sWhere);
}
bool bNewObject = pSR->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pSR->SetID((int) iDBID);
if (!bRetVal)
result = "Failed to save. Please see the hMailServer error log for details.";
return bRetVal;
}
/*
Delete metadata info for messages no longer in the system.
*/
bool
PersistentMessageMetaData::DeleteOrphanedItems()
{
SQLStatement statement;
statement.SetTable("hm_message_metadata");
statement.SetStatementType(SQLStatement::STDelete);
statement.SetWhereClause("not exists (select messageid from hm_messages where messageid = metadata_messageid)");;
return Application::Instance()->GetDBManager()->Execute(statement);
}
bool
PersistentFetchAccountUID::SaveObject(shared_ptr<FetchAccountUID> pUID, String &result)
{
SQLStatement oStatement;
oStatement.SetTable("hm_fetchaccounts_uids");
oStatement.AddColumnInt64("uidfaid", pUID->GetAccountID());
oStatement.AddColumn("uidvalue", pUID->GetUID());
oStatement.AddColumnDate("uidtime", pUID->GetCreationDate());
if (pUID->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("uidid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
oStatement.SetWhereClause(Formatter::Format("uidid = {0}", pUID->GetID()));
}
bool bNewObject = pUID->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pUID->SetID((int) iDBID);
return bRetVal;
}
void
IPAddressSQLHelper::AppendStatement(SQLStatement &statement, const IPAddress &address, const AnsiString &address1Column, const AnsiString &address2Column) const
{
statement.AddColumnInt64(address1Column, address.GetAddress1());
if (address.GetType() == IPAddress::IPV6)
statement.AddColumnInt64(address2Column, address.GetAddress2());
else
statement.AddColumnNULL(address2Column);
}
bool
PersistentAlias::ReadObject(std::shared_ptr<Alias> pAlias, const String & sName)
{
SQLStatement statement;
statement.SetStatementType(SQLStatement::STSelect);
statement.SetTable("hm_aliases");
statement.AddWhereClauseColumn("aliasname", sName);
return ReadObject(pAlias, statement.GetCommand());
}
bool
PersistentDistributionList::ReadObject(std::shared_ptr<DistributionList> pDistList, const String &sAddress)
{
SQLStatement statement;
statement.SetStatementType(SQLStatement::STSelect);
statement.SetTable("hm_distributionlists");
statement.AddWhereClauseColumn("distributionlistaddress", sAddress);
return ReadObject(pDistList, statement.GetCommand());
}
bool
PersistentAccount::ReadObject(boost::shared_ptr<Account> pAccount, const String & sAddress)
{
SQLStatement statement;
statement.SetStatementType(SQLStatement::STSelect);
statement.SetTable("hm_accounts");
statement.AddWhereClauseColumn("accountaddress", sAddress);
bool bResult = ReadObject(pAccount, statement.GetCommand());
return bResult;
}
bool
PersistentRouteAddress::SaveObject(shared_ptr<RouteAddress> pRA)
{
SQLStatement oStatement;
oStatement.AddColumnInt64("routeaddressrouteid", pRA->GetRouteID());
oStatement.AddColumn("routeaddressaddress", pRA->GetAddress());
oStatement.SetTable("hm_routeaddresses");
if (pRA->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("routeaddressid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("routeaddressid = %I64d"), pRA->GetID());
oStatement.SetWhereClause(sWhere);
}
bool bNewObject = pRA->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pRA->SetID((int) iDBID);
return bRetVal;
}
bool
PersistentBlockedAttachment::SaveObject(shared_ptr<BlockedAttachment> pObject)
{
SQLStatement oStatement;
oStatement.SetTable("hm_blocked_attachments");
if (pObject->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("baid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("baid = %I64d"), pObject->GetID());
oStatement.SetWhereClause(sWhere);
}
oStatement.AddColumn(_T("bawildcard"), pObject->GetWildcard());
oStatement.AddColumn(_T("badescription"), pObject->GetDescription());
bool bNewObject = pObject->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pObject->SetID((int) iDBID);
return true;
}
bool
PersistentLogonFailure::AddFailure(const IPAddress &ipaddress)
{
SQLStatement statement;
IPAddressSQLHelper helper;
helper.AppendStatement(statement, ipaddress, "ipaddress1", "ipaddress2");
statement.AddColumnCommand("failuretime", SQLStatement::GetCurrentTimestamp());
statement.SetStatementType(SQLStatement::STInsert);
statement.SetTable("hm_logon_failures");
return Application::Instance()->GetDBManager()->Execute(statement);
}
bool
PersistentLogonFailure::ClearOldFailures(int olderThanMinutes)
{
String whereClause;
whereClause.Format(_T("failuretime < %s"), SQLStatement::GetCurrentTimestampPlusMinutes(-olderThanMinutes));
SQLStatement statement;
statement.SetStatementType(SQLStatement::STDelete);
statement.SetWhereClause(whereClause);
statement.SetTable("hm_logon_failures");
return Application::Instance()->GetDBManager()->Execute(statement);
}
void SQLCode::_BuildSQLStatement(SQLVarParms& varParms, SQLStatement& rSqlStatement)
{
BOOST_LOG_FUNCTION();
std::string strSQLKey;
SQLStatement SQLFormats;
int nDbType = 0;
size_t uiTotalSQLSize = 0;
try
{
size_t uVarCount = varParms.size();
if (uVarCount < 1)
{
throw DataAccessException(ER_DB_ERR_SQLCode, "the PrepareStatement parameter count error!");
}
_GetDbTypeAndSQLKey(varParms, strSQLKey);//strSQLID=strSQLKey=varParms[0]
_GetSQLFormat(strSQLKey, SQLFormats);
_BuildNormalSQL(varParms, SQLFormats, rSqlStatement);
rSqlStatement.logInfo();
}
catch (DataAccessException& e)
{
throw e;
}
catch (...)
{
throw DataAccessException(ER_DB_ERR_UNKNOWN, "buildSQLStatement error!");
}
}
void
Repeater::do_save_existing()
{
SQLStatement updater
( database_connection(),
"update repeaters set "
"interval_units = :interval_units, "
"interval_type_id = :interval_type_id, "
"next_date = :next_date, "
"journal_id = :journal_id "
"where repeater_id = :repeater_id"
);
updater.bind(":repeater_id", id());
process_saving_statement(updater);
return;
}
bool
PersistentTCPIPPort::SaveObject(std::shared_ptr<TCPIPPort> pObject, String &errorMessage, PersistenceMode mode)
{
if (mode == PersistenceModeNormal)
{
if (pObject->GetSSLCertificateID() == 0 &&
(pObject->GetConnectionSecurity() == CSSSL || pObject->GetConnectionSecurity() == CSSTARTTLSOptional || pObject->GetConnectionSecurity() == CSSTARTTLSRequired))
{
errorMessage = "Certificate must be specified.";
return false;
}
}
SQLStatement oStatement;
oStatement.SetTable("hm_tcpipports");
if (pObject->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("portid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("portid = %I64d"), pObject->GetID());
oStatement.SetWhereClause(sWhere);
}
__int64 iAddress = 0;
IPAddressSQLHelper helper;
oStatement.AddColumn("portprotocol", pObject->GetProtocol());
oStatement.AddColumn("portnumber", pObject->GetPortNumber());
oStatement.AddColumnInt64("portsslcertificateid", pObject->GetSSLCertificateID());
helper.AppendStatement(oStatement, pObject->GetAddress(), "portaddress1", "portaddress2");
oStatement.AddColumn("portconnectionsecurity", pObject->GetConnectionSecurity());
bool bNewObject = pObject->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pObject->SetID((int) iDBID);
return true;
}
bool
PersistentLogonFailure::ClearFailuresByIP(const IPAddress &ipaddress)
{
IPAddressSQLHelper helper;
String whereClause;
whereClause.Format(_T("ipaddress1 %s and ipaddress2 %s"),
String(helper.GetAddress1Equals(ipaddress)),
String(helper.GetAddress2Equals(ipaddress)));
SQLStatement statement;
statement.SetStatementType(SQLStatement::STDelete);
statement.SetWhereClause(whereClause);
statement.SetTable("hm_logon_failures");
return Application::Instance()->GetDBManager()->Execute(statement);
}
SQLTransactionState SQLTransaction::deliverStatementCallback()
{
// Spec 4.3.2.6.6 and 4.3.2.6.3: If the statement callback went wrong, jump to the transaction error callback
// Otherwise, continue to loop through the statement queue
m_executeSqlAllowed = true;
SQLStatement* currentStatement = m_backend->currentStatement();
ASSERT(currentStatement);
bool result = currentStatement->performCallback(this);
m_executeSqlAllowed = false;
if (result) {
m_database->reportCommitTransactionResult(2, SQLError::UNKNOWN_ERR, 0);
m_transactionError = SQLErrorData::create(SQLError::UNKNOWN_ERR, "the statement callback raised an exception or statement error callback did not return false");
return nextStateForTransactionError();
}
return SQLTransactionState::RunStatements;
}
void
Repeater::process_saving_statement(SQLStatement& statement)
{
Frequency const freq = value(m_data->frequency);
JEWEL_ASSERT
( is_valid_date_for_interval_type
( boost_date_from_julian_int(value(m_data->next_date)),
freq.step_type()
)
);
statement.bind(":interval_units", freq.num_steps());
statement.bind
( ":interval_type_id",
static_cast<int>(freq.step_type())
);
statement.bind(":next_date", value(m_data->next_date));
statement.bind(":journal_id", value(m_data->journal_id));
statement.step_final();
return;
}
bool
PersistentAlias::SaveObject(std::shared_ptr<Alias> pAlias, String &sErrorMessage, PersistenceMode mode)
{
if (!PreSaveLimitationsCheck::CheckLimitations(mode, pAlias, sErrorMessage))
return false;
SQLStatement oStatement;
oStatement.SetTable("hm_aliases");
oStatement.AddColumnInt64("aliasdomainid", pAlias->GetDomainID());
oStatement.AddColumn("aliasname", pAlias->GetName());
oStatement.AddColumn("aliasvalue", pAlias->GetValue());
oStatement.AddColumn("aliasactive", pAlias->GetIsActive());
if (pAlias->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("aliasid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("aliasid = %I64d"), pAlias->GetID());
oStatement.SetWhereClause(sWhere);
}
bool bNewObject = pAlias->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pAlias->SetID((int) iDBID);
Cache<Alias>::Instance()->RemoveObject(pAlias);
return bRetVal;
}
bool
PersistentTCPIPPort::SaveObject(boost::shared_ptr<TCPIPPort> pObject)
{
SQLStatement oStatement;
oStatement.SetTable("hm_tcpipports");
if (pObject->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("portid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("portid = %I64d"), pObject->GetID());
oStatement.SetWhereClause(sWhere);
}
__int64 iAddress = 0;
IPAddressSQLHelper helper;
oStatement.AddColumn("portprotocol", pObject->GetProtocol());
oStatement.AddColumn("portnumber", pObject->GetPortNumber());
oStatement.AddColumnInt64("portsslcertificateid", pObject->GetSSLCertificateID());
helper.AppendStatement(oStatement, pObject->GetAddress(), "portaddress1", "portaddress2");
oStatement.AddColumn("portusessl", pObject->GetUseSSL());
bool bNewObject = pObject->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pObject->SetID((int) iDBID);
return true;
}
bool
PersistentSURBLServer::SaveObject(shared_ptr<SURBLServer> pObject)
{
SQLStatement oStatement;
oStatement.SetTable("hm_surblservers");
if (pObject->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("surblid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("surblid = %I64d"), pObject->GetID());
oStatement.SetWhereClause(sWhere);
}
oStatement.AddColumn("surblactive", pObject->GetIsActive() == 1);
oStatement.AddColumn("surblhost", pObject->GetDNSHost());
oStatement.AddColumn("surblrejectmessage", pObject->GetRejectMessage());
oStatement.AddColumn("surblscore", pObject->GetScore());
bool bNewObject = pObject->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pObject->SetID((int) iDBID);
return true;
}
bool
PersistentGroupMember::SaveObject(shared_ptr<GroupMember> pObject)
{
SQLStatement oStatement;
oStatement.AddColumnInt64("membergroupid", pObject->GetGroupID());
oStatement.AddColumnInt64("memberaccountid", pObject->GetAccountID());
oStatement.SetTable("hm_group_members");
if (pObject->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("memberid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("memberid = %I64d"), pObject->GetID());
oStatement.SetWhereClause(sWhere);
}
bool bNewObject = pObject->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pObject->SetID((long) iDBID);
Cache<Group, PersistentGroup>::Instance()->RemoveObject(pObject->GetGroupID());
return bRetVal;
}
void SQLCode::prepareSQLStatement(SQLStatement& rSqlStatement, SQLVarParms& varParms)
{
BOOST_LOG_FUNCTION();
try
{
LOG_DEBUG<< "Begin prepare SQL statement...";
rSqlStatement.clear();
_BuildSQLStatement(varParms, rSqlStatement);
LOG_DEBUG<< "End prepare SQL statement...";
}
catch (DataAccessException& e)
{
e.logInfo();
throw e;
}
catch (...)
{
throw DataAccessException(ER_DB_ERR_UNKNOWN, "unknow buildSQLStatement erro!");
}
}
bool
PersistentDomainAlias::SaveObject(shared_ptr<DomainAlias> oDA, String &sErrorMessage)
{
if (!PreSaveLimitationsCheck::CheckLimitations(oDA, sErrorMessage))
return false;
SQLStatement oStatement;
oStatement.SetTable("hm_domain_aliases");
bool bNewObject = oDA->GetID() == 0;
oStatement.AddColumnInt64("dadomainid", oDA->GetDomainID());
oStatement.AddColumn("daalias", oDA->GetAlias());
if (bNewObject)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("daid");
}
else
{
String sWhere;
sWhere.Format(_T("daid = %I64d"), oDA->GetID());
oStatement.SetStatementType(SQLStatement::STUpdate);
oStatement.SetWhereClause(sWhere);
}
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
oDA->SetID((int) iDBID);
ObjectCache::Instance()->SetDomainAliasesNeedsReload();
return true;
}
bool
PersistentIncomingRelay::SaveObject(boost::shared_ptr<IncomingRelay> pSR)
{
SQLStatement oStatement;
oStatement.SetTable("hm_incoming_relays");
oStatement.AddColumn("relayname", pSR->GetName());
IPAddressSQLHelper helper;
helper.AppendStatement(oStatement, pSR->GetLowerIP(), "relaylowerip1", "relaylowerip2");
helper.AppendStatement(oStatement, pSR->GetUpperIP(), "relayupperip1", "relayupperip2");
if (pSR->GetID() == 0)
{
oStatement.SetStatementType(SQLStatement::STInsert);
oStatement.SetIdentityColumn("relayid");
}
else
{
oStatement.SetStatementType(SQLStatement::STUpdate);
String sWhere;
sWhere.Format(_T("relayid = %d"), (unsigned int) pSR->GetID());
oStatement.SetWhereClause(sWhere);
}
bool bNewObject = pSR->GetID() == 0;
// Save and fetch ID
__int64 iDBID = 0;
bool bRetVal = Application::Instance()->GetDBManager()->Execute(oStatement, bNewObject ? &iDBID : 0);
if (bRetVal && bNewObject)
pSR->SetID((int) iDBID);
return bRetVal;
}
std::shared_ptr<DALRecordset>
DatabaseConnectionManager::OpenRecordset(const SQLStatement &statement)
{
return OpenRecordset(statement.GetCommand());
}
bool
DatabaseConnectionManager::Execute(const SQLStatement &statement, __int64 *iInsertID, int iIgnoreErrors, String &sErrorMessage)
{
return Execute(statement.GetCommand(), iInsertID, iIgnoreErrors, sErrorMessage);
}
