/**
@SYMTestCaseID PDS-SQLITE3SEC-UT-4034
@SYMTestCaseDesc SQL server multi-insert performance test.
The test inserts 1000 records in a single transaction and stores
the execution time for later use (comparison and printing).
@SYMTestPriority High
@SYMTestActions SQL server multi-insert performance test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ11320
*/
static void SqlServerMultiInsertTest(const char aInsertSql[], TInt aInsertRecCnt)
{
TheTest.Next( _L("@SYMTestCaseID:PDS-SQLITE3SEC-UT-4034"));
(void)KillProcess(KSqlSrvName);
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
RSqlStatement stmt;
err = stmt.Prepare(TheDb, TPtrC8((const TUint8*)aInsertSql));
TEST2(err, KErrNone);
TUint32 fc = FastCounterValue();
err = TheDb.Exec(_L8("BEGIN"));
TEST(err >= 0);
for(TInt i=0;i<aInsertRecCnt;++i)
{
err = stmt.BindInt(0, i + 1);
TEST2(err, KErrNone);
err = stmt.Exec();
TEST2(err, 1);
err = stmt.Reset();
TEST2(err, KErrNone);
}
err = TheDb.Exec(_L8("COMMIT"));
TEST(err >= 0);
StorePerfTestResult(EPerfTestSqlMode, EPerfTestMultiInsert, FastCounterValue() - fc);
stmt.Close();
TheDb.Close();
}
/**
Deletes all the communication addresses for a particular contact item. Should be used when
deleting a contact item from the database altogether.
@param aItem The contact item whose communcation addresses are to be deleted.
*/
void CPplCommAddrTable::DeleteL(const CContactItem& aItem, TBool& aLowDiskErrorOccurred)
{
const TUid KType = aItem.Type();
if (KType != KUidContactCard && KType != KUidContactOwnCard && KType != KUidContactICCEntry && KType != KUidContactGroup)
{
return;
}
RSqlStatement stmnt;
CleanupClosePushL(stmnt);
stmnt.PrepareL(iDatabase, iAllForItemDeleteStmnt->SqlStringL() );
const TInt KContactIdParamIndex(KFirstIndex); // first and only parameter in query
User::LeaveIfError(stmnt.BindInt(KContactIdParamIndex, aItem.Id() ) );
TInt err = stmnt.Exec();
CleanupStack::PopAndDestroy(&stmnt);
if (err == KErrDiskFull)
{
aLowDiskErrorOccurred = ETrue;
}
else
{
User::LeaveIfError(err);
}
}
// -----------------------------------------------------------------------------
// CUpnpSecurityDbConnection::ExecStatementL
// Execute given db command
// -----------------------------------------------------------------------------
//
void CUpnpSecurityDbConnection::ExecStatementL( const TDesC8& aCommand )
{
RSqlStatement statement;
User::LeaveIfError( statement.Prepare( iDatabase, aCommand ) );
CleanupClosePushL( statement );
User::LeaveIfError( statement.Exec() );
CleanupStack::PopAndDestroy( &statement );
}
/**
* Method to change a plugin id in plugin Id table
* @param aNewPluginID The ID of the new plugin
* @param aFlag The flag that indicates the plugin id is enables or disabled
* @param aOldPluginID The ID of the plugin to be replaced
* @return Returns KErrNone if success. Refer ESqlDbError and system-wide
* error codes for detailed error description.
*/
TInt CSmfCredMgrDbUser::updatePlugin(const TDesC& aPluginID,
const TBool& aFlag, const TDesC& aOldPluginID)
{
TInt err(KErrNone);
RSqlStatement sqlStatement;
TInt paramIndex(KErrNone);
TBuf<KMaxBufSize> newPluginBuf(aPluginID);
TBuf<KMaxBufSize> OldPluginBuf(aOldPluginID);
RSqlDatabase db;
err = db.Open(iDbFilePathName);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
err = sqlStatement.Prepare(db, KUpdatePluginID);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
paramIndex = sqlStatement.ParameterIndex(_L(":iText"));
err = sqlStatement.BindText(paramIndex, newPluginBuf);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
paramIndex = sqlStatement.ParameterIndex(_L(":iFlag"));
err = sqlStatement.BindInt(paramIndex, aFlag);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
paramIndex = sqlStatement.ParameterIndex(_L(":iID"));
err = sqlStatement.BindText(paramIndex, OldPluginBuf);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
err = db.Exec(KBegin);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
err = sqlStatement.Exec();
if (KSqlErrConstraint == err)
{
//
}
else if (err < KErrNone)
{
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
}
err = db.Exec(KCommit);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
//if commit fails we have to roll back
if (err < KErrNone)
{
err = db.Exec(KRollback);
}
sqlStatement.Close();
db.Close();
if (err >= 0)
{
return KErrNone;
}
return err;
}
/**
* Method to insert AuthParamsTable
* @param aAuthAppId The ID of the Authentication app
* @param aKey The Key
* @param aSecret The Secret
* @return Returns KErrNone if success. Refer ESqlDbError and system-wide
* error codes for detailed error description.
*/
TInt CSmfCredMgrDbUser::AuthParamsTableInsert(const TDesC& aAuthAppId,
const TDesC& aKey, const TDesC& aSecret)
{
TInt err(KErrNone);
RSqlStatement sqlStatement;
RSqlDatabase db;
TBuf<KMaxBufSize> keyBuf(aKey);
TBuf<KMaxBufSize> secretBuf(aSecret);
err = db.Open(iDbFilePathName);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
err = sqlStatement.Prepare(db, KSmfAuthParamsTableInsert);
TInt paramIndex(KErrNone);
paramIndex = sqlStatement.ParameterIndex(_L(":Val1"));
err = sqlStatement.BindText(paramIndex, aAuthAppId);
paramIndex = sqlStatement.ParameterIndex(_L(":Val2"));
err = sqlStatement.BindText(paramIndex, keyBuf);
paramIndex = sqlStatement.ParameterIndex(_L(":Val3"));
err = sqlStatement.BindText(paramIndex, secretBuf);
err = db.Exec(KBegin);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
err = sqlStatement.Exec();
if (KSqlErrConstraint == err)
{
//Table already present.
}
else if (err < KErrNone)
{
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
}
err = db.Exec(KCommit);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
//if commit fails we have to roll back
if (err < KErrNone)
{
err = db.Exec(KRollback);
}
sqlStatement.Close();
db.Close();
if (err >= 0)
{
return KErrNone;
}
return err;
}
/**
Performs write (Insert/Update) operations for indiviual communication addresses of type "phone number".
*/
void CPplCommAddrTable::DoPhoneNumWriteOpL(const CPplCommAddrTable::TMatch& aPhoneNum, TCntSqlStatement aType,
TInt aCntId, TInt aCommAddrId,CPplCommAddrTable::TCommAddrExtraInfoType aExtraInfoType)
{
// leave if the statement type is not insert or update.
// also, we can't update if aCommAddrId is 0 as we don't know the record's id
if ((aType != EUpdate && aType != EInsert) || (aType == EUpdate && aCommAddrId == 0) )
{
User::Leave(KErrArgument);
}
RSqlStatement stmnt;
CleanupClosePushL(stmnt);
// temporary reference to the CCntSqlStatements member variables to take advantage
// of the commonality between update and insert operations.
CCntSqlStatement* tempCntStmnt = iUpdateStmnt;
if (aType == EInsert)
{
tempCntStmnt = iInsertStmnt;
}
User::LeaveIfError(stmnt.Prepare(iDatabase, tempCntStmnt->SqlStringL() ) );
User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrContactId() ), aCntId) );
User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrExtraValue() ), aPhoneNum.iUpperDigits) );
User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrValue() ), aPhoneNum.iLowerSevenDigits) );
User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrType() ), EPhoneNumber) );
User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrExtraTypeInfo() ), aExtraInfoType) );
if (aType == EInsert)
{
User::LeaveIfError(stmnt.BindNull(tempCntStmnt->ParameterIndex(KCommAddrId() ) ) );
}
else
{
// it's the sixth parameter in the query and is in the WHERE
// clause so we can't get its index from the CCntSqlStatement
const TInt KCommAddrIdParamIndex(KFirstIndex + 5);
User::LeaveIfError(stmnt.BindInt(KCommAddrIdParamIndex, aCommAddrId) );
}
User::LeaveIfError(stmnt.Exec() );
CleanupStack::PopAndDestroy(&stmnt);
}
/**
Deletes individual communication addresses from the database. In other words, deletes at
the sub-contact item level rather than deleting everything with a specific contact item ID.
*/
void CPplCommAddrTable::DeleteSingleCommAddrL(TInt aCommAddrId, TBool& aLowDiskErrorOccurred)
{
RSqlStatement stmnt;
CleanupClosePushL(stmnt);
stmnt.PrepareL(iDatabase, iSingleDeleteStmnt->SqlStringL() );
const TInt KCommAddrIdParamIndex(KFirstIndex); // first and only parameter in the query
User::LeaveIfError(stmnt.BindInt(KCommAddrIdParamIndex, aCommAddrId ) );
TInt err = stmnt.Exec();
CleanupStack::PopAndDestroy(&stmnt);
if (err == KErrDiskFull)
{
aLowDiskErrorOccurred = ETrue;
}
else
{
User::LeaveIfError(err);
}
}
/**
Deletes information about group for the passed contact item id
@param aItemId contact item id
@param aLowDiskErrorOccurred out parameter; will be set to ETrue if there was a deletion in
low disk condition
*/
void CPplGroupsTable::DeleteItemL(TContactItemId aItemId, TBool& aLowDiskErrorOccurred)
{
RSqlStatement stmnt;
CleanupClosePushL(stmnt);
stmnt.PrepareL(iDatabase, iDeleteStmnt->SqlStringL() );
const TInt KGroupIdIndex(KFirstIndex); // first parameter in query...
const TInt KMemberIdIndex(KGroupIdIndex + 1); // ...and the second parameter
User::LeaveIfError(stmnt.BindInt(KGroupIdIndex, aItemId) );
User::LeaveIfError(stmnt.BindInt(KMemberIdIndex, aItemId) );
TInt err = stmnt.Exec();
CleanupStack::PopAndDestroy(&stmnt);
if (err == KErrDiskFull)
{
aLowDiskErrorOccurred = ETrue;
}
else
{
User::LeaveIfError(err);
}
}
/**
Persist the items belonging to curent group into group table
@param aGroup referece to a contact group
*/
void CPplGroupsTable::WriteGroupMembersL(const CContactItem& aGroup)
{
if (aGroup.Type() != KUidContactGroup)
{
return;
}
const TContactItemId KGroupId(aGroup.Id() );
// make sure we clear out any previous, out-of-date data
TBool lowDiskErr(EFalse);
DeleteItemL(KGroupId, lowDiskErr);
if (lowDiskErr)
{
User::Leave(KErrDiskFull);
}
// build the RSqlStatement
RSqlStatement stmnt;
CleanupClosePushL(stmnt);
stmnt.PrepareL(iDatabase, iInsertStmnt->SqlStringL() );
const TInt KGroupIdIndex(KFirstIndex); // first parameter in query...
const TInt KMemberIdIndex(KGroupIdIndex + 1); // ...and the second parameter
// copy and sort the member id array so we can see if there are duplicates
const CContactIdArray* contactIdArray = static_cast<const CContactGroup&>(aGroup).ItemsContained(); //does not take the ownership
const TInt arrayCount = contactIdArray->Count();
CArrayFixFlat<TContactItemId>* sortedList = new(ELeave) CArrayFixFlat<TContactItemId>(KArrayGranularity);
CleanupStack::PushL(sortedList);
for(TInt loop = 0;loop < arrayCount; ++loop)
{
sortedList->AppendL((*contactIdArray)[loop]);
}
TKeyArrayFix key(0,ECmpTInt);
sortedList->Sort(key);
// insert the group-member relationships
const TInt KCountStmntParamIndex(KFirstIndex); // first and only parameter in query
const TInt listLen(sortedList->Count() );
TInt lastId(0);
for (TInt i = 0; i < listLen; ++i)
{
TInt itemId((*sortedList)[i]);
//check if a contact item with itemId id really exists in contact database
RSqlStatement countStmnt;
CleanupClosePushL(countStmnt);
countStmnt.PrepareL(iDatabase, iCountContactsStmnt->SqlStringL() );
User::LeaveIfError(countStmnt.BindInt(KCountStmntParamIndex, itemId) );
TInt count = 0;
TInt err = KErrNone;
if((err = countStmnt.Next() ) == KSqlAtRow)
{
count = countStmnt.ColumnInt(iCountContactsStmnt->ParameterIndex(KSqlCount) );
}
else
{
User::LeaveIfError(err);
}
if(count == 0)
{
User::Leave(KErrNotFound);
}
CleanupStack::PopAndDestroy(&countStmnt);
// only insert this if we haven't already seen it
if (itemId != lastId || i == 0)
{
User::LeaveIfError(stmnt.BindInt(KGroupIdIndex, KGroupId) );
User::LeaveIfError(stmnt.BindInt(KMemberIdIndex, itemId) );
User::LeaveIfError(stmnt.Exec() );
User::LeaveIfError(stmnt.Reset() );
}
lastId = itemId;
}
CleanupStack::PopAndDestroy(2, &stmnt); // and sortedList
}
TInt CMdSSqLiteConnection::ExecuteL( const TDesC& aCommand,
const RRowData& aVariables,
RMdsStatement* aStatement )
{
TInt err = KErrNone;
if ( aVariables.Size() == 0 )
{
// no variables
err = iMdeSqlDb.Exec( aCommand );
if ( err < KErrNone )
{
_LIT( KMdSExec, "Exec (no variables)" );
TraceAndLeaveL( KMdSExec, err );
}
}
else if ( aStatement )
{
if ( aStatement->iPrepared == EFalse )
{
err = aStatement->iStatement.Prepare( iMdeSqlDb, aCommand );
if ( err < KErrNone )
{
_LIT( KMdSPrepare, "Prepare" );
TraceAndLeaveL( KMdSPrepare, err );
}
aStatement->iPrepared = ETrue;
}
else
{
err = aStatement->iStatement.Reset();
if ( err < KErrNone )
{
_LIT( KMdSResume, "Resume" );
TraceAndLeaveL( KMdSResume, err );
}
}
DoBindL( aStatement->iStatement, aVariables );
err = aStatement->iStatement.Exec();
if ( err < KErrNone )
{
aStatement->iStatement.Reset();
aStatement->iPrepared = EFalse;
_LIT( KMdSExec, "Exec" );
TraceAndLeaveL( KMdSExec, err );
}
}
else
{
RSqlStatement mdeSqlDbStmt;
CleanupClosePushL( mdeSqlDbStmt );
err = mdeSqlDbStmt.Prepare( iMdeSqlDb, aCommand );
if ( err < KErrNone )
{
_LIT( KMdsPrepare, "Prepare (no statement)" );
TraceAndLeaveL( KMdsPrepare, err );
}
DoBindL( mdeSqlDbStmt, aVariables );
err = mdeSqlDbStmt.Exec();
if ( err < KErrNone )
{
_LIT( KMdsExec, "Exec (no statement)" );
TraceAndLeaveL( KMdsExec, err );
}
CleanupStack::PopAndDestroy( &mdeSqlDbStmt );
}
return err;
}
