/** @SYMTestCaseID SYSLIB-SQL-CT-1645 @SYMTestCaseDesc Testing database operations on a secure database. The test application's capabilities allow write-only access to the test secure database. Verify that any other kind of a database operation will fail with KErrPermissionDenied error. @SYMTestPriority High @SYMTestActions Testing database operations on a secure database. @SYMTestExpectedResults Test must not fail @SYMREQ REQ5792 REQ5793 */ void WriteOnlyDatabaseTest() { TInt err = TheDb.Open(KTestDbName); TEST2(err, KErrNone); //Attempt to modify the database schema err = TheDb.Exec(_L("CREATE TABLE C(FFF TEXT)")); TEST2(err, KErrPermissionDenied); err = TheDb.Exec(_L("CREATE TRIGGER upd_a_b1 UPDATE OF B1 ON A BEGIN UPDATE B SET F3 = 'AAAA' WHERE F2 = A.F1; END;")); TEST2(err, KErrPermissionDenied); err = TheDb.Exec(_L("CREATE TEMP TRIGGER upd_a_b1 UPDATE OF B1 ON A BEGIN UPDATE B SET F3 = 'AAAA' WHERE F2 = A.F1; END;")); TEST2(err, KErrPermissionDenied);//Temp trigger which attempts to update one of the tables. err = TheDb.Exec(_L("CREATE VIEW V1 AS SELECT * FROM A")); TEST2(err, KErrPermissionDenied); err = TheDb.Exec(_L("CREATE TEMP VIEW V1 AS SELECT * FROM A")); TEST(err >= 0); err = TheDb.Exec(_L("DROP VIEW V1")); TEST(err >= 0); //Attempt to update the user data (but it includes a READ operation) err = TheDb.Exec(_L("UPDATE A SET F1 = 11 WHERE F1 = 1")); TEST2(err, KErrPermissionDenied); //Attempt to update the user data (unconditional UPDATE, no READ operations) err = TheDb.Exec(_L("UPDATE A SET F1 = 11")); TEST(err >= 0); //Attempt to delete the user data (but it includes a READ operation) err = TheDb.Exec(_L("DELETE FROM B WHERE F2 = 2")); TEST2(err, KErrPermissionDenied); //Attempt to delete the user data (unconditional DELETE, no READ operations) err = TheDb.Exec(_L("DELETE FROM A")); TEST(err >= 0); //Restore the deleted table A err = TheDb.Exec(_L("INSERT INTO A(F1,B1) VALUES(1,x'41414141414141414141');INSERT INTO A(F1,B1) VALUES(2,x'42424242424242424242');INSERT INTO A(F1,B1) VALUES(3,x'43434343434343434343');INSERT INTO A(F1,B1) VALUES(4,x'44444444444444444444');")); TEST(err >= 0); //Attempt to insert new user data err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(22, 'AAA', x'47474747474747474747')")); TEST2(err, 1); //Attempt to change the isolation level. err = TheDb.SetIsolationLevel(RSqlDatabase::ESerializable); TEST2(err, KErrNone); err = TheDb.SetIsolationLevel(RSqlDatabase::EReadUncommitted); TEST2(err, KErrNone); //Attempt to read the user data RSqlStatement stmt; err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2")); TEST2(err, KErrPermissionDenied); //Attempt to read the system data err = stmt.Prepare(TheDb, _L("SELECT * FROM SQLITE_MASTER")); TEST2(err, KErrNone); err = stmt.Next(); TEST2(err, KSqlAtRow); TPtrC p; err = stmt.ColumnText(0, p); TEST2(err, KErrNone); RDebug::Print(_L("Value=%S\r\n"), &p); stmt.Close(); TheDb.Close(); }
/** * Method to fetch Authentication app ID from RegTokenValidityTable * giving the reg token * @param aRegToken The Registration token of the authentication app * @param aAuthAppId [out] The ID of the Authentication app */ void CSmfCredMgrDbUser::readAuthAppIdInRegTokenTable(const TDesC& aRegToken, TDes& aAuthAppId) { TInt err(KErrNone); RSqlStatement sqlReadStatement; TInt paramIndex(KErrNone); TInt64 duration; TBuf<KMaxBufSize> tokenBuf(aRegToken); err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadAuthAppIdInRegTokenTable); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); paramIndex = sqlReadStatement.ParameterIndex(_L(":iID")); err = sqlReadStatement.BindText(paramIndex, tokenBuf); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); while ((err = sqlReadStatement.Next()) == KSqlAtRow) { //sometimes sqlStmt.Next returns KSqlAtRow even if no row is present if (!sqlReadStatement.IsNull(0)) { duration = sqlReadStatement.ColumnInt(1); if (duration) //to be checked with epoch date-time { sqlReadStatement.ColumnText(0, aAuthAppId); } } else { __ASSERT_DEBUG( 0, User::Invariant()); } } sqlReadStatement.Close(); }
/** * Method to fetch Registration token of the Authentication app from RegTokenValidityTable * @param aAuthAppId The ID of the Authentication app * @param aRegToken [out] The Registration token of the authentication app */ void CSmfCredMgrDbUser::readRegistrationTokenL(const TDesC& aAuthAppId, TDesC& aRegToken) { TInt err(KErrNone); RSqlStatement sqlReadStatement; TInt paramIndex(KErrNone); err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadRegistrationToken); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); paramIndex = sqlReadStatement.ParameterIndex(_L(":iID")); err = sqlReadStatement.BindText(paramIndex, aAuthAppId); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); while ((err = sqlReadStatement.Next()) == KSqlAtRow) { //sometimes sqlStmt.Next returns KSqlAtRow even if no row is present if (!sqlReadStatement.IsNull(0)) { aRegToken = sqlReadStatement.ColumnTextL(0);//aKey.AllocL(); } else { __ASSERT_DEBUG( 0, User::Invariant()); } } sqlReadStatement.Close(); }
/** * Method to fetch Validity of the Authentication app * @param aAuthAppId The ID of the Authentication app * @param aValidity [out] The time by which the Auth set will expire */ void CSmfCredMgrDbUser::readValidity(const TDesC& aAuthAppId, TInt64& aValidity) { TInt err(KErrNone); RSqlStatement sqlReadStatement; TInt paramIndex(KErrNone); err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadValidity); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); paramIndex = sqlReadStatement.ParameterIndex(_L(":iID")); err = sqlReadStatement.BindText(paramIndex, aAuthAppId); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); while ((err = sqlReadStatement.Next()) == KSqlAtRow) { //sometimes sqlStmt.Next returns KSqlAtRow even if no row is present if (!sqlReadStatement.IsNull(0)) { aValidity = sqlReadStatement.ColumnInt64(1); } else { __ASSERT_DEBUG( 0, User::Invariant()); } } sqlReadStatement.Close(); }
/** * Method to fetch all the URLs associated with the Authentication app * @param aAuthAppId The ID of the Authentication app * @param aArray [out] The array to be updated with URLs */ void CSmfCredMgrDbUser::readUrlL(const TDesC& aAuthAppId, RPointerArray<HBufC>& aArray) { TInt err(KErrNone); RSqlStatement sqlReadStatement; TInt paramIndex(KErrNone); err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadURL); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); paramIndex = sqlReadStatement.ParameterIndex(_L(":iID")); err = sqlReadStatement.BindText(paramIndex, aAuthAppId); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); while ((err = sqlReadStatement.Next()) == KSqlAtRow) { //sometimes sqlStmt.Next returns KSqlAtRow even if no row is present if (!sqlReadStatement.IsNull(0)) { TBuf<KMaxBufSize> urlBuf; HBufC* buf = HBufC::NewL(KMaxBufSize); sqlReadStatement.ColumnText(0, urlBuf); buf->Des().Copy(urlBuf); aArray.Append(buf); } else { __ASSERT_DEBUG( 0, User::Invariant()); } } sqlReadStatement.Close(); }
/** @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(); }
/** * Method to read the IsEnabled flag from plugin Id table * @param aPluginID The ID of the plugin * @param aFlag [out] The flag that indicates the plugin id is enables or disabled */ void CSmfCredMgrDbUser::readFlagInPluginIdTable(const TDesC& aPluginID, TInt& aFlag) { TInt err(KErrNone); RSqlStatement sqlReadStatement; TInt paramIndex(KErrNone); err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadFlagInPluginTable); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); paramIndex = sqlReadStatement.ParameterIndex(_L(":iID")); err = sqlReadStatement.BindText(paramIndex, aPluginID); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); while ((err = sqlReadStatement.Next()) == KSqlAtRow) { //sometimes sqlStmt.Next returns KSqlAtRow even if no row is present if (!sqlReadStatement.IsNull(0)) { aFlag = sqlReadStatement.ColumnInt(0); } } sqlReadStatement.Close(); }
// ----------------------------------------------------------------------------- // CUpnpSecurityDbConnection::GetAllFilenamesL // Get all filenames. // ----------------------------------------------------------------------------- // void CUpnpSecurityDbConnection::GetAllFilenamesL( RPointerArray<HBufC>& aFilenameArray ) { TInt err; RSqlStatement statement; User::LeaveIfError( statement.Prepare( iDatabase, KUpnpSecSqlSelectAllFiles ) ); CleanupClosePushL( statement ); TInt columnIndex = statement.ColumnIndex( KUpnpSecSqlFilename ); while ( (err = statement.Next()) == KSqlAtRow ) { TPtrC data = statement.ColumnTextL( columnIndex ); HBufC* filename = data.AllocLC(); aFilenameArray.AppendL( filename ); CleanupStack::Pop( filename ); } if ( err != KSqlAtEnd ) { User::LeaveIfError( err ); } CleanupStack::PopAndDestroy( &statement ); }
// ----------------------------------------------------------------------------- // 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 ); }
// ----------------------------------------------------------------------------- // CUpnpSecurityDbConnection::Validate // Check validity of database. // ----------------------------------------------------------------------------- // TBool CUpnpSecurityDbConnection::Validate() { TBool result = EFalse; RSqlStatement statement; TInt err = statement.Prepare( iDatabase, KUpnpSecSqlValidateTableExistence ); result = !err && (statement.Next() == KSqlAtRow); statement.Close(); return result; }
/** * 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; }
/** * Method to Key-Secret pairs of the Authentication app * @param aAuthAppId The ID of the Authentication app * @param aArray [out] The array containing the key-secret pair */ void CSmfCredMgrDbUser::readAuthTokensL(const TDesC& aAuthAppId, RArray< TSmfAuthToken>& aArray) { TInt err(KErrNone); RSqlStatement sqlReadStatement; TInt paramIndex(KErrNone); err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadAuthTokens); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); paramIndex = sqlReadStatement.ParameterIndex(_L(":iID")); err = sqlReadStatement.BindText(paramIndex, aAuthAppId); __ASSERT_DEBUG( (err >= KErrNone), User::Invariant()); while ((err = sqlReadStatement.Next()) == KSqlAtRow) { //sometimes sqlStmt.Next returns KSqlAtRow even if no row is present if (!sqlReadStatement.IsNull(0)) { TSmfAuthToken Set; Set.iKey = HBufC::NewL(KMaxAuthTokenLength); Set.iSecret = HBufC::NewL(KMaxAuthTokenLength); TBuf<KMaxBufSize> keyBuf; TBuf<KMaxBufSize> secretBuf; sqlReadStatement.ColumnText(0, keyBuf); sqlReadStatement.ColumnText(1, secretBuf); Set.iKey->Des().Copy(keyBuf); Set.iSecret->Des().Copy(secretBuf); //add it to the array aArray.Append(Set); } else { __ASSERT_DEBUG( 0, User::Invariant()); } } sqlReadStatement.Close(); }
/** 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); }
/** @SYMTestCaseID PDS-SQLITE3SEC-UT-4041 @SYMTestCaseDesc SQL server single-select performance test. The test selects one randomly chosen record and stores the execution time for later use (comparison and printing). @SYMTestPriority High @SYMTestActions SQL server single-select performance test. @SYMTestExpectedResults Test must not fail @SYMREQ REQ11320 */ static void SqlServerSingleSelectTest(const char aSingleSelectSql[], TInt aSelectRecId) { TheTest.Next( _L("@SYMTestCaseID:PDS-SQLITE3SEC-UT-4041")); (void)KillProcess(KSqlSrvName); TInt err = TheDb.Open(KTestDbName); TEST2(err, KErrNone); TheSqlBuf.Copy(TPtrC8((const TUint8*)aSingleSelectSql)); TheSqlBuf.AppendNum((TInt64)aSelectRecId); RSqlStatement stmt; err = stmt.Prepare(TheDb, TheSqlBuf); TEST2(err, KErrNone); TInt recCnt = 0; TUint32 fc = FastCounterValue(); while((err = stmt.Next()) == KSqlAtRow) { TInt64 i64 = stmt.ColumnInt64(0); UNUSED_VAR(i64); TReal d = stmt.ColumnReal(1); UNUSED_VAR(d); TPtrC t; err = stmt.ColumnText(2, t); TEST2(err, KErrNone); UNUSED_PTR(t); TPtrC8 b; err = stmt.ColumnBinary(3, b); TEST2(err, KErrNone); UNUSED_PTR(b); ++recCnt; } StorePerfTestResult(EPerfTestSqlMode, EPerfTestSingleSelect, FastCounterValue() - fc); TEST2(err, KSqlAtEnd); TEST2(recCnt, 1); stmt.Close(); TheDb.Close(); }
/** Utility method used to read binary blob fields from contacts database. Provides a mechanism to fill a contact item with informations stored in binary blobs within contact database. A reference to the contact item to be fill has to be provided. A template has to be provided if the contact item is based on a template. Template can be NULL. Also a view definition can be provided to filter which fields are read from blob fields. @param aItem Contact item to be filled with information from binary blob field. @param aView View definition specifying what item fields should be read from binary blob field @param aTemplate Contact item representing a template based on which aItem should be read. Can be NULL @param aDatabase RSqlDatabase reference. @leave KErrNotFound if the specified contact item does not exist any more in contact database */ void TCntPersistenceUtility::ReadBinaryBlobL(CContactItem& aItem, const CContactItemViewDef& aView, const CContactItem* aTemplate, RSqlDatabase& aDatabase) { HBufC* selectString = HBufC::NewLC(KSelectTwoFields().Length() + KContactBinaryFieldHeader().Length() + KContactBinaryFields().Length() + KSqlContactTableName().Length() + KContactId().Length()); TPtr ptrSelectString = selectString->Des(); ptrSelectString.Format(KSelectTwoFields, &KContactBinaryFieldHeader, &KContactBinaryFields, &KSqlContactTableName, &KContactId, aItem.Id()); RSqlStatement selectStmt; CleanupClosePushL(selectStmt); User::LeaveIfError(selectStmt.Prepare(aDatabase, ptrSelectString)); TInt err = selectStmt.Next(); if(err != KSqlAtRow) { if(err == KSqlAtEnd) { User::Leave(KErrNotFound); } else { User::Leave(err); } } TPtrC8 binaryHeader; selectStmt.ColumnBinary(User::LeaveIfError(selectStmt.ColumnIndex(KContactBinaryFieldHeader)), binaryHeader); RDesReadStream binaryHeaderStream(binaryHeader); CleanupClosePushL(binaryHeaderStream); CEmbeddedStore* binaryHeaderStore = CEmbeddedStore::FromLC(binaryHeaderStream); TPtrC8 binaryFields; selectStmt.ColumnBinary(User::LeaveIfError(selectStmt.ColumnIndex(KContactBinaryFields)), binaryFields); RDesReadStream binaryFieldsStream(binaryFields); CleanupClosePushL(binaryFieldsStream); CEmbeddedStore* binaryBlobStore = CEmbeddedStore::FromLC(binaryFieldsStream); ReadBinaryBlobL(*binaryHeaderStore, *binaryBlobStore, aItem, aView, aTemplate); CleanupStack::PopAndDestroy(6, selectString); //binaryHeaderStore, binaryHeaderStrean, binaryBlobStore, binaryBlobStream, selectStmt, selectString }
// ----------------------------------------------------------------------------- // CUpnpSecurityDbConnection::GetAllIpAddressesL // Get all ips. // ----------------------------------------------------------------------------- // void CUpnpSecurityDbConnection::GetAllIpAddressesL( RArray<TInetAddr>& aAddressArray ) { TInt err; RSqlStatement statement; User::LeaveIfError( statement.Prepare( iDatabase, KUpnpSecSqlSelectAllIpAddresses ) ); CleanupClosePushL( statement ); TInt columnIndex = statement.ColumnIndex( KUpnpSecSqlIp ); while ( (err = statement.Next()) == KSqlAtRow ) { TUint32 data = statement.ColumnInt( columnIndex ); aAddressArray.AppendL( TInetAddr( data, 0 ) ); } if ( err != KSqlAtEnd ) { User::LeaveIfError( err ); } CleanupStack::PopAndDestroy( &statement ); }
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; }
/** Reads the contact item from the database using the given contact item ID. @param aItemId The Id number of the contact to be read. @param aView Specifies the fields to be read. @param aInfoToRead not used @param aSessionId The ID of the session that issued the request. This is used to prevent Phonebook Synchroniser deadlock. @param aIccOpenCheck Specifies if validation with the Phonebook Synchroniser is needed for this contact. @leave KErrArgument if the itemID can't be set within select statement @leave KErrNotFound if a contact item with aItemId does not exist within contact database @leave KSqlErrBusy the database file is locked; thrown if RSqlStatement::Next() returns this error @leave KErrNoMemory an out of memory condition has occurred - the statement will be reset;thrown if RSqlStatement::Next() returns this error @leave KSqlErrGeneral a run-time error has occured - this function must not be called again;thrown if RSqlStatement::Next() returns this error @leave KSqlErrMisuse this function has been called after a previous call returned KSqlAtEnd or KSqlErrGeneral.thrown if RSqlStatement::Next() returns this error @leave KSqlErrStmtExpired the SQL statement has expired (if new functions or collating sequences have been registered or if an authorizer function has been added or changed); thrown if RSqlStatement::Next() returns this error @return CContactItem created from reading the database tables. */ CContactItem* CPplContactItemManager::ReadLC(TContactItemId aItemId, const CContactItemViewDef& aView, TInt aInfoToRead, TUint aSessionId, TBool aIccOpenCheck) const { CContactTemplate* sysTemplate = NULL; if (aItemId != KGoldenTemplateId) { sysTemplate = const_cast<CContactTemplate*>(&iContactProperties.SystemTemplateL()); } RSqlStatement selectStmt; CleanupClosePushL(selectStmt); User::LeaveIfError(selectStmt.Prepare(iDatabase, iSelectStatement->SqlStringL())); TInt err = selectStmt.BindInt(KFirstParam, aItemId); if(err != KErrNone) { User::Leave(KErrArgument); } CContactItem* item = NULL; TUid type(KNullUid); if((err = selectStmt.Next()) == KSqlAtRow) { TInt contactId = selectStmt.ColumnInt(iSelectStatement->ParameterIndex(KContactId)); TInt templateId = selectStmt.ColumnInt(iSelectStatement->ParameterIndex(KContactTemplateId)); TInt typeFlags = selectStmt.ColumnInt(iSelectStatement->ParameterIndex(KContactTypeFlags)); type = TCntPersistenceUtility::TypeFlagsToContactTypeUid(typeFlags); item = CContactItem::NewLC(type); item->SetId(contactId); TPtrC guid = selectStmt.ColumnTextL(iSelectStatement->ParameterIndex(KContactGuidString)); item->SetUidStringL(guid); TInt attr = (typeFlags & EContactAttributes_Mask) >> EContactAttributes_Shift; item->SetAttributes(attr); item->SetTemplateRefId(templateId); item->SetLastModified(TTime(selectStmt.ColumnInt64(iSelectStatement->ParameterIndex(KContactLastModified)))); item->SetCreationDate(TTime(selectStmt.ColumnInt64(iSelectStatement->ParameterIndex(KContactCreationDate)))); item->SetAccessCount(selectStmt.ColumnInt(iSelectStatement->ParameterIndex(KContactAccessCount))); RArray<TPtrC> fastAccessFields; CleanupClosePushL(fastAccessFields); fastAccessFields.AppendL(selectStmt.ColumnTextL(iSelectStatement->ParameterIndex(KContactFirstName))); fastAccessFields.AppendL(selectStmt.ColumnTextL(iSelectStatement->ParameterIndex(KContactLastName))); fastAccessFields.AppendL(selectStmt.ColumnTextL(iSelectStatement->ParameterIndex(KContactCompanyName))); fastAccessFields.AppendL(selectStmt.ColumnTextL(iSelectStatement->ParameterIndex(KContactFirstNamePrn))); fastAccessFields.AppendL(selectStmt.ColumnTextL(iSelectStatement->ParameterIndex(KContactLastNamePrn))); fastAccessFields.AppendL(selectStmt.ColumnTextL(iSelectStatement->ParameterIndex(KContactCompanyNamePrn))); //set first name, last name, company name, first name pronunciation, last name pronunciation, company name pronunciation for (TInt fieldNum = item->CardFields().Count() - 1; fieldNum>=0; --fieldNum) { CContactItemField& textField = (item->CardFields())[fieldNum]; const TInt nameFieldIndex = NameFieldIndex(textField); // Check if field is first name, last name, company name, // first name pronunciation, last name pronunciation, company name pronunciation. if (nameFieldIndex != KErrNotFound) { HBufC* text = HBufC::NewLC(fastAccessFields[nameFieldIndex].Size()); text->Des() = fastAccessFields[nameFieldIndex]; textField.TextStorage()->SetText(text); CleanupStack::PopAndDestroy(text); } } CleanupStack::PopAndDestroy(&fastAccessFields); }
/** @SYMTestCaseID SYSLIB-SQL-CT-1614 @SYMTestCaseDesc Verifying that when having 2 database connections in different threads, both set the isolation level to "Read Uncommitted", the reading thread can make "dirty read" operations (can read the updated but not committed yet record values made by the writing thread). @SYMTestPriority High @SYMTestActions Testing "Read Uncommitted" database isolation level. @SYMTestExpectedResults Test must not fail @SYMREQ REQ5792 REQ5793 */ void TestIsolationLevel() { RDebug::Print(_L("+++:MainThread: Create critical sections\r\n")); TEST2(UpdateThreadCrS.CreateLocal(), KErrNone); UpdateThreadCrS.Wait(); TEST2(MainThreadCrS.CreateLocal(), KErrNone); MainThreadCrS.Wait(); RDebug::Print(_L("+++:MainThread: Create test database\r\n")); RSqlDatabase db; TInt err = db.Create(KTestDbName1); TEST2(err, KErrNone); RDebug::Print(_L("+++:MainThread: Set the isolation level to \"Read uncommitted\"\r\n")); err = db.SetIsolationLevel(RSqlDatabase::EReadUncommitted); TEST2(err, KErrNone); RDebug::Print(_L("+++:MainThread: Create a table in the test database\r\n")); _LIT8(KCreateSql, "CREATE TABLE A(Id INTEGER)"); err = db.Exec(KCreateSql); TEST(err >= 0); RDebug::Print(_L("+++:MainThread: Insert one record in the table\r\n")); _LIT8(KInsertSql, "INSERT INTO A(Id) VALUES("); TBuf8<64> sql(KInsertSql); sql.AppendNum((TInt64)KInitialValue); sql.Append(_L(")")); err = db.Exec(sql); TEST2(err, 1); RDebug::Print(_L("+++:MainThread: Create the \"update\" thread\r\n")); _LIT(KThreadName, "UpdTh"); RThread thread; TEST2(thread.Create(KThreadName, &UpdateThreadFunc, 0x2000, 0x1000, 0x10000, NULL, EOwnerThread), KErrNone); TRequestStatus status; thread.Logon(status); TEST2(status.Int(), KRequestPending); thread.Resume(); RDebug::Print(_L("+++:MainThread: Wait for record update completion...\r\n")); MainThreadCrS.Wait(); RDebug::Print(_L("+++:MainThread: Read the record and check the data...\r\n")); _LIT8(KSelectSql, "SELECT * FROM A"); RSqlStatement stmt; err = stmt.Prepare(db, KSelectSql); TEST2(err, KErrNone); err = stmt.Next(); TEST2(err, KSqlAtRow); TInt val = stmt.ColumnInt(0); TEST(val == KUpdatedValue); stmt.Close(); RDebug::Print(_L("+++:MainThread: Notify the update thread that it can rollback\r\n")); UpdateThreadCrS.Signal(); RDebug::Print(_L("+++:MainThread: Wait for rollback completion...\r\n")); MainThreadCrS.Wait(); RDebug::Print(_L("+++:MainThread: Read the record and check the data...\r\n")); err = stmt.Prepare(db, KSelectSql); TEST2(err, KErrNone); err = stmt.Next(); TEST2(err, KSqlAtRow); val = stmt.ColumnInt(0); TEST2(val, KInitialValue); stmt.Close(); User::WaitForRequest(status); thread.Close(); db.Close(); RDebug::Print(_L("+++:MainThread: Delete the test database\r\n")); (void)RSqlDatabase::Delete(KTestDbName1); RDebug::Print(_L("+++:MainThread: Close critical sections\r\n")); MainThreadCrS.Close(); UpdateThreadCrS.Close(); }
/** @SYMTestCaseID SYSLIB-SQL-CT-1613 @SYMTestCaseDesc Multiple connections to the same database from different threads. Each thread inserts set of record to the same table. Verify that all expected records and their column values meet the expectations. @SYMTestPriority High @SYMTestActions Testing SQL engine behaviour when having mutiple connections to the same database from different threads. @SYMTestExpectedResults Test must not fail @SYMREQ REQ5792 REQ5793 */ void TestMultiConnDiffThread() { //Create a test database RDebug::Print(_L("+++:MainThread: Create test database\r\n")); RSqlDatabase db; TInt err = db.Create(KTestDbName1); TEST2(err, KErrNone); //Create a test table RDebug::Print(_L("+++:MainThread: Create a table in the test database\r\n")); _LIT8(KCreateSql, "CREATE TABLE A(Id INTEGER PRIMARY KEY)"); err = db.Exec(KCreateSql); TEST(err >= 0); const TInt KThreadCnt = 4; const TInt KRange = 100; const TInt KIsolationLevelCnt = 2; TPtrC KIsolationLevelName[KIsolationLevelCnt] = {_L("Read Uncommitted"), _L("Serializable")}; const RSqlDatabase::TIsolationLevel KIsolationLevels[KIsolationLevelCnt] = { RSqlDatabase::EReadUncommitted, RSqlDatabase::ESerializable}; const TInt KTransTypeCnt = 2; //Do the tests: // - doing each per thread database operation in a single transaction; // - doing all per thread database operations in a single transaction; for(TInt transType=0;transType<KTransTypeCnt;++transType) { //For both supported isolation levels: read uncommitted and serializable for(TInt isolLevel=0;isolLevel<KIsolationLevelCnt;++isolLevel) { TInt low = 1; TInt high = KRange; RDebug::Print(_L("+++:MainThread: Test: thread count %d, records %d, trans type %d, isolation level: %S\r\n"), KThreadCnt, KRange, transType, &KIsolationLevelName[isolLevel]); RThread thread[KThreadCnt]; TRequestStatus status[KThreadCnt]; TThreadData data[KThreadCnt]; //Create the test threads and run them. Each thread establishes a connection with the test database //and attempts to write set of records in the test table. TInt j; for(j=0;j<KThreadCnt;++j,low=high+1,high+=KRange) { data[j].iTransType = transType; data[j].iIsolationLevel = KIsolationLevels[isolLevel]; data[j].iLowRecNo = low; data[j].iHighRecNo = high; _LIT(KThreadName,"Thr-"); TBuf<32> threadName(KThreadName); threadName.AppendNum((TInt64)j + 1); TEST2(thread[j].Create(threadName, &ThreadFunc, 0x2000, 0x1000, 0x10000, (void*)&data[j], EOwnerThread), KErrNone); thread[j].Logon(status[j]); TEST2(status[j].Int(), KRequestPending); thread[j].Resume(); } User::After(2000000); //Wait until threads finish the database operations and close them. for(j=0;j<KThreadCnt;++j) { User::WaitForRequest(status[j]); TEST(thread[j].ExitType() != EExitPanic); thread[j].Close(); } //Check that all records which are esupposed to be in the database, are there. RDebug::Print(_L("+++:MainThread: Check that all records have been written\r\n")); _LIT8(KSelectSql1, "SELECT COUNT(*) FROM A;"); RSqlStatement stmt; err = stmt.Prepare(db, KSelectSql1); TEST2(err, KErrNone); err = stmt.Next(); TEST2(err, KSqlAtRow); TInt cnt = stmt.ColumnInt(0); TEST2(cnt, KThreadCnt * KRange); stmt.Close(); //Check that all records have expected column values. RDebug::Print(_L("+++:MainThread: Check that all records have expected column values\r\n")); _LIT8(KSelectSql2, "SELECT * FROM A;"); err = stmt.Prepare(db, KSelectSql2); TEST2(err, KErrNone); for(TInt k=0;k<(KThreadCnt*KRange);++k) { err = stmt.Next(); TEST2(err, KSqlAtRow); TInt val = stmt.ColumnInt(0); TEST(val > 0 && val <= (KThreadCnt * KRange)); } stmt.Close(); //Prepare for the next test run - delete all records. RDebug::Print(_L("+++:MainThread: Delete all records\r\n")); _LIT8(KDeleteSql, "DELETE FROM A"); err = db.Exec(KDeleteSql); TEST(err >= 0); }//end of "for(TInt isolLevel=0;isolLevel<KIsolationLevelCnt;++isolLevel)" }//end of "for(TInt transType=0;transType<KTransTypeCnt;++transType)" db.Close(); RDebug::Print(_L("+++:MainThread: Delete the test database\r\n")); (void)RSqlDatabase::Delete(KTestDbName1); }
/** @SYMTestCaseID SYSLIB-SQL-CT-1612 @SYMTestCaseDesc Two connections to the same database in the same thread. Create a test database and insert some records from both connections. Verify that all records were inserted successfully. @SYMTestPriority High @SYMTestActions Testing SQL engine behaviour when having mutiple connections to the same database in the same thread. @SYMTestExpectedResults Test must not fail @SYMREQ REQ5792 REQ5793 */ void TestMultiConnSameThread() { //Connection 1 RSqlDatabase db1; TInt err = db1.Create(KTestDbName1); TEST2(err, KErrNone); //Create test database RDebug::Print(_L("###Create test database\r\n")); _LIT8(KCreateSql, "CREATE TABLE A(Id INTEGER PRIMARY KEY AUTOINCREMENT, Data INTEGER)"); err = db1.Exec(KCreateSql); TEST(err >= 0); //Connection 2 RSqlDatabase db2; err = db2.Open(KTestDbName1); TEST2(err, KErrNone); //Insert some records using both connections RDebug::Print(_L("###Insert some records\r\n")); const TInt KRecNum = 100; _LIT8(KInsertSql, "INSERT INTO A(Data) VALUES("); for(TInt i=0;i<KRecNum;++i) { TBuf8<100> sql(KInsertSql); sql.AppendNum((TInt64)i + 1); sql.Append(_L(");")); err = (i%2) ? db1.Exec(sql) : db2.Exec(sql); if(err < 0) { TPtrC msg = (i%2) ? db1.LastErrorMessage() : db2.LastErrorMessage(); RDebug::Print(_L("##Db Error msg: \"%S\"\n\r"), &msg); } TEST2(err, 1); } //Check the database content RDebug::Print(_L("###Check the database content\r\n")); _LIT8(KSelectSql, "SELECT * FROM A"); RSqlStatement stmt; err = stmt.Prepare(db1, KSelectSql); TEST2(err, KErrNone); for(TInt j=0;j<KRecNum;++j) { err = stmt.Next(); TEST2(err, KSqlAtRow); TEST(stmt.AtRow()); TInt id = stmt.ColumnInt(0); TInt data = stmt.ColumnInt(1); TEST(id == data); } stmt.Close(); //Cleanup db2.Close(); db1.Close(); RDebug::Print(_L("###Delete the test database\r\n")); (void)RSqlDatabase::Delete(KTestDbName1); }
/** @SYMTestCaseID SYSLIB-SQL-CT-1646 @SYMTestCaseDesc Testing database operations on a secure database. The test application's capabilities allow read/write access to the test secure database. Verify that any other kind of a database operation will fail with KErrPermissionDenied error. @SYMTestPriority High @SYMTestActions Testing database operations on a secure database. @SYMTestExpectedResults Test must not fail @SYMREQ REQ5792 REQ5793 */ void ReadWriteDatabaseTest() { RSqlDatabase db; TInt err = TheDb.Open(KTestDbName); TEST2(err, KErrNone); //Attempt to modify the database schema err = TheDb.Exec(_L("CREATE TABLE C(FFF TEXT)")); TEST2(err, KErrPermissionDenied); err = TheDb.Exec(_L("CREATE TEMP TABLE TBL1(COL1 INTEGER, COL2 INTEGER)")); TEST(err >= 0); err = TheDb.Exec(_L("CREATE TEMP TRIGGER del1 AFTER DELETE ON TBL1 BEGIN DELETE FROM A; END;")); TEST(err >= 0); err = TheDb.Exec(_L("DROP TRIGGER del1")); TEST(err >= 0); err = TheDb.Exec(_L("CREATE TEMP VIEW V1 AS SELECT * FROM TBL1")); TEST(err >= 0); err = TheDb.Exec(_L("DROP VIEW V1")); TEST(err >= 0); err = TheDb.Exec(_L("CREATE INDEX I1 ON TBL1(COL2)")); TEST(err >= 0); err = TheDb.Exec(_L("DROP INDEX I1")); TEST(err >= 0); err = TheDb.Exec(_L("DROP TABLE TBL1")); TEST(err >= 0); err = TheDb.Exec(_L("ANALYZE A")); TEST2(err, KErrPermissionDenied); err = TheDb.Exec(_L("CREATE VIEW V2 AS SELECT * FROM A")); TEST2(err, KErrPermissionDenied); //Attempt to update the user data (but it includes a READ operation) err = TheDb.Exec(_L("UPDATE A SET F1 = 11 WHERE F1 = 1")); TEST(err >= 0); //Attempt to update the user data (unconditional UPDATE, no READ operations) err = TheDb.Exec(_L("UPDATE A SET F1 = 11")); TEST(err >= 0); //Attempt to delete the user data (but it includes a READ operation) err = TheDb.Exec(_L("DELETE FROM B WHERE F2 = 2")); TEST(err >= 0); //Attempt to delete the user data (unconditional DELETE, no READ operations) err = TheDb.Exec(_L("DELETE FROM A")); TEST(err >= 0); //Restore the deleted table A err = TheDb.Exec(_L("INSERT INTO A(F1,B1) VALUES(1,x'41414141414141414141');INSERT INTO A(F1,B1) VALUES(2,x'42424242424242424242');INSERT INTO A(F1,B1) VALUES(3,x'43434343434343434343');INSERT INTO A(F1,B1) VALUES(4,x'44444444444444444444');")); TEST(err >= 0); //Restore the deleted record in table B err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(2, 'ABC', x'45454545454545454545');")); TEST2(err, 1); //Attempt to insert new user data err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(6, 'GHI', x'47474747474747474747');")); TEST2(err, 1); //Attempt to read the user data RSqlStatement stmt; err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2")); TEST2(err, KErrNone); //ColumnCount() has no capabilities assigned TInt colCnt = stmt.ColumnCount(); TEST2(colCnt, 1); //DeclaredColumnType() has no capabilities assigned TSqlColumnType colType; err = stmt.DeclaredColumnType(0, colType); TEST2(err, KErrNone); TEST2(colType, ESqlInt); err = stmt.Next(); TEST2(err, KSqlAtRow); RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0)); err = stmt.Next(); TEST2(err, KSqlAtRow); RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0)); stmt.Close(); //Attempt to read the system data err = stmt.Prepare(TheDb, _L("SELECT * FROM SQLITE_MASTER")); TEST2(err, KErrNone); err = stmt.Next(); TEST2(err, KSqlAtRow); TPtrC p; err = stmt.ColumnText(0, p); TEST2(err, KErrNone); RDebug::Print(_L("Value=%S\r\n"), &p); stmt.Close(); NonSecureDbTest(); TheDb.Close(); }