/**
@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();
}
TInt ThreadFunc(void* aData)
{
__UHEAP_MARK;
CTrapCleanup* tc = CTrapCleanup::New();
TTEST(tc != NULL);
TThreadData* data = static_cast<TThreadData*> (aData);
TTEST(data != NULL);
RSqlDatabase db;
TInt err = db.Open(KTestDbName1);
TTEST2(err, KErrNone);
err = db.SetIsolationLevel(data->iIsolationLevel);
TTEST2(err, KErrNone);
if(data->iTransType == 1)
{
_LIT8(KBeginTrans, "BEGIN");
err = db.Exec(KBeginTrans);
TTEST(err >= 0);
}
_LIT8(KInsertSql, "INSERT INTO A(Id) VALUES(");
for(TInt id=data->iLowRecNo;id<=data->iHighRecNo;++id)
{
TBuf8<128> sql(KInsertSql);
sql.AppendNum((TInt64)id);
sql.Append(_L(")"));
err = KSqlErrBusy;
const TInt KAttempts = 20;
for(TInt i=0;i<KAttempts&&err==KSqlErrBusy;++i)
{
err = db.Exec(sql);
if(err == KSqlErrBusy)
{
RThread th;
TName name = th.Name();
RDebug::Print(_L("!!!Database locked, Thread: %S, Attempt %d, column value %d\r\n"), &name, i + 1, id);
User::After(1000000);
}
}
TTEST2(err, 1);
}
if(data->iTransType == 1)
{
_LIT8(KCommitTrans, "COMMIT");
err = db.Exec(KCommitTrans);
TTEST(err >= 0);
}
db.Close();
delete tc;
__UHEAP_MARKEND;
return KErrNone;
}
/**
* 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;
}
///////////////////////////////////////////////////////////////////////////////////////
//Restore original test database function
void RestoreOriginalDb()
{
TheDb.Close();
TheDb.Open(KTestDbName);
// Delete and restore the content of table A (unconditional DELETE, no READ operations)
TheDb.Exec(_L("DELETE FROM A"));
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');"));
// Delete and restore the content of table B (unconditional DELETE, no READ operations)
TheDb.Exec(_L("DELETE FROM B"));
TheDb.Exec(_L("INSERT INTO B(F2,F3,B2) VALUES(2, 'ABC',x'45454545454545454545');INSERT INTO B(F2,F3,B2) VALUES(4,'DEF',x'46464646464646464646');"));
TheDb.Close();
}
TInt UpdateThreadFunc(void*)
{
__UHEAP_MARK;
CTrapCleanup* tc = CTrapCleanup::New();
TTEST(tc != NULL);
RSqlDatabase db;
TInt err = db.Open(KTestDbName1);
TTEST2(err, KErrNone);
RDebug::Print(_L("---:UpdThread: Set the isolation level to \"Read uncommitted\"\r\n"));
err = db.SetIsolationLevel(RSqlDatabase::EReadUncommitted);
TTEST2(err, KErrNone);
RDebug::Print(_L("---:UpdThread: Begin a write transaction\r\n"));
_LIT8(KBeginTransSql, "BEGIN IMMEDIATE TRANSACTION");
err = db.Exec(KBeginTransSql);
TTEST(err >= 0);
RDebug::Print(_L("---:UpdThread: Update the record\r\n"));
_LIT8(KUpdateSql, "UPDATE A SET Id = ");
TBuf8<64> sql(KUpdateSql);
sql.AppendNum((TInt64)KUpdatedValue);
err = db.Exec(sql);
TTEST(err >= 0);
RDebug::Print(_L("---:UpdThread: Notify the main thread about the update\r\n"));
MainThreadCrS.Signal();
RDebug::Print(_L("---:UpdThread: Wait for permisson to continue...\r\n"));
UpdateThreadCrS.Wait();
RDebug::Print(_L("---:UpdThread: Rollback the update\r\n"));
_LIT8(KRollBackTransSql, "ROLLBACK TRANSACTION");
err = db.Exec(KRollBackTransSql);
TTEST(err >= 0);
RDebug::Print(_L("---:UpdThread: Notify the main thread about the rollback\r\n"));
MainThreadCrS.Signal();
db.Close();
delete tc;
__UHEAP_MARKEND;
return KErrNone;
}
void CreateDatabaseL(const TDesC& aDbName)
{
// create the database now
RSqlSecurityPolicy securityPolicy;
CleanupClosePushL(securityPolicy);
TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass);
securityPolicy.Create(policy);
TSecurityPolicy schemaPolicy(TSecurityPolicy::EAlwaysPass);
TSecurityPolicy readPolicy(TSecurityPolicy::EAlwaysPass);
TSecurityPolicy writePolicy(TSecurityPolicy::EAlwaysPass);
User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy));
User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));
User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy));
TheTest.Printf(_L("Creating Database %S\n"), &aDbName);
TInt err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
if (KErrAlreadyExists == err)
{
// the file already exists
// make sure we delete the file
User::LeaveIfError(TheDbC.Delete(aDbName));
// try again
err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
}
User::LeaveIfError(err);
//Create tables
User::LeaveIfError(TheDbC.Exec(KMusicCreateTable));
User::LeaveIfError(TheDbC.Exec(KAuxiliaryCreateTable));
User::LeaveIfError(TheDbC.Exec(KAlbumCreateTable));
User::LeaveIfError(TheDbC.Exec(KArtistCreateTable));
User::LeaveIfError(TheDbC.Exec(KComposerCreateTable));
User::LeaveIfError(TheDbC.Exec(KGenreCreateTable));
User::LeaveIfError(TheDbC.Exec(KPlaylistCreateTable));
User::LeaveIfError(TheDbC.Exec(KPlaylistSongInfoCreateTable));
User::LeaveIfError(TheDbC.Exec(KPlaylistSongsCreateTable));
TheDbC.Close();
CleanupStack::PopAndDestroy(&securityPolicy);
}
static void CreateTestDatabase()
{
RSqlDatabase::Delete(KTestDbName);
_LIT8(KConfigStr, "encoding=\"UTF-8\"");
TInt err = TheDb.Create(KTestDbName, &KConfigStr);
TEST2(err, KErrNone);
err = TheDb.Exec(_L8("CREATE TABLE Tbl(I INTEGER PRIMARY KEY, I64 BIGINT, D DOUBLE, T TEXT, B BINARY)"));
TEST2(err, 1);
TheDb.Close();
}
/**
@SYMTestCaseID PDS-SQLITE3SEC-UT-4038
@SYMTestCaseDesc SQL server single-insert performance test.
The test inserts one record and stores
the execution time for later use (comparison and printing).
@SYMTestPriority High
@SYMTestActions SQL server single-insert performance test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ11320
*/
static void SqlServerSingleInsertTest(const char aSingleInsertSql[], TInt aInsertRecId)
{
TheTest.Next( _L("@SYMTestCaseID:PDS-SQLITE3SEC-UT-4038"));
(void)KillProcess(KSqlSrvName);
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
TheSqlBuf.Format(TPtrC8((const TUint8*)aSingleInsertSql), aInsertRecId);
TUint32 fc = FastCounterValue();
err = TheDb.Exec(TheSqlBuf);
StorePerfTestResult(EPerfTestSqlMode, EPerfTestSingleInsert, FastCounterValue() - fc);
TEST2(err, 1);
TheDb.Close();
}
/**
@SYMTestCaseID PDS-SQLITE3SEC-UT-4040
@SYMTestCaseDesc SQL server single-delete performance test.
The test deletes one randomly chosen record and stores
the execution time for later use (comparison and printing).
@SYMTestPriority High
@SYMTestActions SQL server single-delete performance test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ11320
*/
static void SqlServerSingleDeleteTest(const char aSingleDeleteSql[], TInt aDeleteRecId)
{
TheTest.Next( _L("@SYMTestCaseID:PDS-SQLITE3SEC-UT-4040"));
(void)KillProcess(KSqlSrvName);
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
TheSqlBuf.Copy(TPtrC8((const TUint8*)aSingleDeleteSql));
TheSqlBuf.AppendNum((TInt64)aDeleteRecId);
TUint32 fc = FastCounterValue();
err = TheDb.Exec(TheSqlBuf);
StorePerfTestResult(EPerfTestSqlMode, EPerfTestSingleDelete, FastCounterValue() - fc);
TEST2(err, 1);
TheDb.Close();
}
/**
@SYMTestCaseID PDS-SQLITE3SEC-UT-4036
@SYMTestCaseDesc SQL server multi-delete performance test.
The test deletes 100 randomly chosen records and stores
the execution time for later use (comparison and printing).
@SYMTestPriority High
@SYMTestActions SQL server multi-delete performance test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ11320
*/
static void SqlServerMultiDeleteTest(const char aDeleteSql[], TInt aDeleteRecIds[], TInt aDeleteRecCnt)
{
TheTest.Next( _L("@SYMTestCaseID:PDS-SQLITE3SEC-UT-4036"));
(void)KillProcess(KSqlSrvName);
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
FormatSqlStmt(TheSqlBuf, aDeleteSql, aDeleteRecIds, aDeleteRecCnt);
TUint32 fc = FastCounterValue();
err = TheDb.Exec(TheSqlBuf);
StorePerfTestResult(EPerfTestSqlMode, EPerfTestMultiDelete, FastCounterValue() - fc);
TEST2(err, aDeleteRecCnt);
TheDb.Close();
}
/**
@SYMTestCaseID SYSLIB-SQL-UT-4049
@SYMTestCaseDesc Database configuration string - injection test.
The test attempts to create a database using a configuration string
and passing a DELETE SQL statement as a compaction mode name.
The database should be created successfully, the invalid compaction mode - ignored,
the database system tables content shoud not be corrupted by the call.
@SYMTestPriority High
@SYMTestActions Database configuration string - injection test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ10405
*/
void InjectionTest()
{
(void)RSqlDatabase::Delete(KTestDbName);
TInt err = TheDb.Create(KTestDbName);
TEST2(err, KErrNone);
err = TheDb.Exec(_L("CREATE TABLE A(I INTEGER); INSERT INTO A(I) VALUES(1);"));
TEST(err > 0);
TheDb.Close();
_LIT8(KConfig1, "cache_size=128;DELETE FROM A");
err = TheDb.Open(KTestDbName, &KConfig1);
TEST2(err, KErrArgument);
err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
TSqlScalarFullSelectQuery query(TheDb);
TInt recCount = 0;
TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A")));
TEST2(err, KErrNone);
TEST2(recCount, 1);
TheDb.Close();
_LIT8(KConfig2, "cache_size=256;compaction=DELETE FROM A;");
err = TheDb.Open(KTestDbName, &KConfig2);
TEST2(err, KErrNone);
query.SetDatabase(TheDb);
recCount = 0;
TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A")));
TEST2(err, KErrNone);
TEST2(recCount, 1);
TheDb.Close();
(void)RSqlDatabase::Delete(KTestDbName);
_LIT8(KConfig3, "cache_size=256;compaction=DELETE FROM symbian_settings;");
err = TheDb.Create(KTestDbName, &KConfig3);
TEST2(err, KErrNone);
query.SetDatabase(TheDb);
recCount = 0;
TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM symbian_settings")));
TEST2(err, KErrNone);
TEST2(recCount, 1);
TheDb.Close();
(void)RSqlDatabase::Delete(KTestDbName);
}
/**
@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 PDS-SQL-UT-4151
@SYMTestCaseDesc Measures the performance of inserting multiple records
into the Music Player MPX database. This test is based on
a real Music Player Harvesting use case
@SYMTestPriority Medium
@SYMTestActions Reads SQL transactions from a file and executes them.
Records the time for executing each statement
@SYMTestExpectedResults All statements should be executed without error and
performance measurements logged
@SYMDEF DEF142306
*/
void RunTest()
{
//Open the file with the sql statements
_LIT(KSqlFileName,"z:\\test\\t_sqlperformance4.sql");
RFile sqlFile;
TInt err = sqlFile.Open(TheFs, KSqlFileName, EFileRead);
TEST2(err, KErrNone);
TInt fileLen = 0;
err = sqlFile.Size(fileLen);
TEST2(err, KErrNone);
HBufC8* sqlBuf = HBufC8::New(fileLen);
TEST(sqlBuf != NULL);
TPtr8 sql = sqlBuf->Des();
err = sqlFile.Read(sql);
sqlFile.Close();
TEST2(err, KErrNone);
TEST2(sql.Length(), fileLen);
//Open main database
err = TheDbC.Open(TheDbFileName, &TheSqlConfigString);
TEST2(err, KErrNone);
TheTest.Printf(_L("Beginning INSERTS...\n"));
const TInt KRecordCount = 6544;
TInt recordCount = 0;
TInt insertCnt = 0;
TInt updateCnt = 0;
TInt selectCnt = 0;
TInt trnCnt = 0;
TInt totalTime = 0;
TInt insertTrnCnt = 0;
TInt updateTrnCnt = 0;
TInt selectTrnCnt = 0;
for(;sql.Length()>0;)
{
TInt eolPos = sql.Locate(TChar('\n'));
if(eolPos < 0)
{
break;//No more SQL statements
}
TInt stmtLength = eolPos;
while (stmtLength > 0 && (sql[stmtLength-1] == '\r'))
{
--stmtLength; //Reduce length to remove carriage return characters from the end of the statement string
}
TPtrC8 sqlStmt8(sql.Ptr(), stmtLength);
TPtrC8 ptr = sql.Mid(eolPos + 1);//"eolPos + 1" - first character after '\n'
sql.Set(const_cast <TUint8*> (ptr.Ptr()), ptr.Length(), ptr.Length());
++recordCount;
//Convert to 16 bit query string
TBuf<1024> query;
query.Copy(sqlStmt8);
//Execute the statement
TInt start = User::FastCounter();
err = TheDbC.Exec(query);
TInt end = User::FastCounter();
TEST(err >= 0);
//Get the execution time for that statement
TInt duration = GetDuration(start, end);
totalTime += duration;
if(query == KBeginTransaction)
{
TheTest.Printf(_L("Execute Statement - BEGIN: %d us\n"), duration);
}
else if(query == KCommitTransaction)
{
++trnCnt;
TheTest.Printf(_L("Execute Statement - COMMIT: %d us, Trn#%d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"),
duration, trnCnt, insertTrnCnt, updateTrnCnt, selectTrnCnt);
insertTrnCnt = updateTrnCnt = selectTrnCnt = 0;
}
else
{
TPtrC queryType(query.Ptr(), 6);
TheTest.Printf(_L("Execute Statement - %S: %d us\n"),&queryType, duration);
if(queryType.FindF(_L("INSERT")) >= 0)
{
++insertCnt;
++insertTrnCnt;
}
else if(queryType.FindF(_L("UPDATE")) >= 0)
{
++updateCnt;
++updateTrnCnt;
}
else if(queryType.FindF(_L("SELECT")) >= 0)
{
++selectCnt;
++selectTrnCnt;
}
}
}
delete sqlBuf;
TheDbC.Close();
TheTest.Printf(_L("Total time to process Songs: %d us\n"), totalTime);
TheTest.Printf(_L("Transactions count: %d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"),
trnCnt, insertCnt, updateCnt, selectCnt);
TEST2(recordCount, KRecordCount);
}
/**
@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-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();
}
//This functnion is called while there is an open secure connection.
//The function will create a new, non-secure connection and check that the non-secure database schema can be modified,
//while there is another alive, secure database connection.
void NonSecureDbTest()
{
(void)RSqlDatabase::Delete(KTestDbName2);
RSqlDatabase db;
TInt err = db.Create(KTestDbName2);
TEST2(err, KErrNone);
err = db.Exec(_L("CREATE TABLE A(I1 INTEGER, I2 INTEGER)"));
TEST(err >= 0);
err = db.Exec(_L("CREATE TEMP TABLE B(I1 INTEGER, I2 INTEGER)"));
TEST(err >= 0);
//"CREATE VIRTUAL TABLE" statement not supported
err = db.Exec(_L("CREATE VIRTUAL TABLE V1 USING FTS3(ColOne TEXT, ColTwo DATETIME)"));
TPtrC msg = db.LastErrorMessage();
TheTest.Printf(_L("*** \"CREATE VIRTUAL TABLE\" expected failure, msg=\"%S\", err=%d\r\n"), &msg, err);
TEST(err != KErrNone);
err = db.Exec(_L("CREATE TRIGGER T1 AFTER INSERT ON A BEGIN INSERT INTO B VALUES(new.I1, new.I2); END;"));
TEST(err >= 0);
err = db.Exec(_L("DROP TRIGGER T1"));
TEST(err >= 0);
err = db.Exec(_L("CREATE TEMP TRIGGER T2 AFTER UPDATE OF I1 ON A BEGIN UPDATE B SET I1 = new.I1; END;"));
TEST(err >= 0);
err = db.Exec(_L("DROP TRIGGER T2"));
TEST(err >= 0);
err = db.Exec(_L("CREATE VIEW V1 AS SELECT * FROM A"));
TEST(err >= 0);
err = db.Exec(_L("DROP VIEW V1"));
TEST(err >= 0);
err = db.Exec(_L("CREATE TEMP VIEW V2 AS SELECT * FROM A"));
TEST(err >= 0);
err = db.Exec(_L("DROP VIEW V2"));
TEST(err >= 0);
err = db.Exec(_L("CREATE INDEX Idx1 ON A(I1)"));
TEST(err >= 0);
err = db.Exec(_L("ANALYZE A"));
TEST(err >= 0);
err = db.Exec(_L("DROP INDEX Idx1"));
TEST(err >= 0);
err = db.Exec(_L("CREATE INDEX Idx2 ON B(I1)"));
TEST(err >= 0);
err = db.Exec(_L("DROP INDEX Idx2"));
TEST(err >= 0);
err = db.Exec(_L("DROP TABLE B"));
TEST(err >= 0);
err = db.Exec(_L("DROP TABLE A"));
TEST(err >= 0);
db.Close();
(void)RSqlDatabase::Delete(KTestDbName2);
}
/**
@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();
}