sl@0: // Copyright (c) 2009-2010 Nokia Corporation and/or its subsidiary(-ies).
sl@0: // All rights reserved.
sl@0: // This component and the accompanying materials are made available
sl@0: // under the terms of "Eclipse Public License v1.0"
sl@0: // which accompanies this distribution, and is available
sl@0: // at the URL "http://www.eclipse.org/legal/epl-v10.html".
sl@0: //
sl@0: // Initial Contributors:
sl@0: // Nokia Corporation - initial contribution.
sl@0: //
sl@0: // Contributors:
sl@0: // NTT DOCOMO, INC - Fix for Bug 3170 "SQL library test T_SQLPERFORMANCE4 fails with USER 84 panic"
sl@0: //
sl@0: // Description:
sl@0: //
sl@0: 
sl@0: #include <e32test.h>
sl@0: #include <bautils.h>
sl@0: #include <sqldb.h>
sl@0: #include <hal.h>
sl@0: #include "t_sqlcmdlineutil.h"
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: RTest			TheTest(_L("t_sqlperformance4 test"));
sl@0: RSqlDatabase 	TheDbC;
sl@0: RFs				TheFs;
sl@0: 
sl@0: _LIT(KCDriveDatabase, "c:[a000017f]t_sqlperformance4.db");
sl@0: 
sl@0: TFileName		TheDbFileName;
sl@0: TBuf<200> TheTestTitle;
sl@0: TCmdLineParams TheCmdLineParams;
sl@0: TBuf8<200> TheSqlConfigString;
sl@0: 
sl@0: _LIT(KUtf8,  "UTF8 ");
sl@0: _LIT(KUtf16, "UTF16");
sl@0: 
sl@0: _LIT(KMusicCreateTable, "CREATE TABLE Music("
sl@0:     L"UniqueId INTEGER PRIMARY KEY,"
sl@0:     L"DbFlag INTEGER,"
sl@0:     L"VolumeId INTEGER,"
sl@0:     L"Title TEXT COLLATE NOCASE,"
sl@0:     L"Artist INTEGER,"
sl@0:     L"Art TEXT,"
sl@0:     L"Deleted INTEGER DEFAULT 0,"
sl@0:     L"Location TEXT,"
sl@0:     L"AlbumTrack INTEGER,"
sl@0:     L"PlayCount INTEGER DEFAULT 0,"
sl@0:     L"TimeAdded TEXT,"
sl@0:     L"TimePlayed TEXT DEFAULT '',"
sl@0:     L"Duration INTEGER,"
sl@0:     L"Sync INTEGER DEFAULT 0,"
sl@0:     L"Modified INTEGER DEFAULT 0,"
sl@0:     L"Album INTEGER,"
sl@0:     L"Genre INTEGER,"
sl@0:     L"Composer INTEGER,"
sl@0:     L"ReleaseDate TEXT DEFAULT '',"
sl@0:     L"Rating INTEGER,"
sl@0:     L"Comment TEXT,"
sl@0:     L"Copyright TEXT,"
sl@0:     L"Url TEXT,"
sl@0:     L"DRM INTEGER,"
sl@0:     L"LastPlayPosition INTEGER DEFAULT 0,"
sl@0:     L"SampleRate INTEGER,"
sl@0:     L"BitRate INTEGER,"
sl@0:     L"NumChannels INTEGER,"
sl@0:     L"Codec INTEGER,"
sl@0:     L"MimeType TEXT,"
sl@0:     L"MTPDrmStatus INTEGER)");
sl@0: 
sl@0: _LIT(KAuxiliaryCreateTable, "CREATE TABLE Auxiliary("
sl@0:     L"Id INTEGER,"
sl@0:     L"Version TEXT,"
sl@0:     L"TimeRefreshed TEXT,"
sl@0:     L"TimeSynced TEXT,"
sl@0:     L"Corrupt INTEGER DEFAULT 0,"
sl@0:     L"SaveDeletedRecordCount INTEGER DEFAULT 0)");
sl@0: 
sl@0: _LIT(KAlbumCreateTable,"CREATE TABLE Album("
sl@0:     L"UniqueId INTEGER PRIMARY KEY,"
sl@0:     L"Name TEXT COLLATE NOCASE,"
sl@0:     L"SongCount INTEGER,"
sl@0:     L"Artist INTEGER,"
sl@0:     L"Art TEXT)");
sl@0: 
sl@0: _LIT(KArtistCreateTable,"CREATE TABLE Artist("
sl@0:     L"UniqueId INTEGER PRIMARY KEY,"
sl@0:     L"Name TEXT COLLATE NOCASE,"
sl@0:     L"SongCount INTEGER)");
sl@0: 
sl@0: _LIT(KComposerCreateTable,"CREATE TABLE Composer("
sl@0:     L"UniqueId INTEGER PRIMARY KEY,"
sl@0:     L"Name TEXT COLLATE NOCASE,"
sl@0:     L"SongCount INTEGER)");
sl@0: 
sl@0: _LIT(KGenreCreateTable,"CREATE TABLE Genre("
sl@0:     L"UniqueId INTEGER PRIMARY KEY,"
sl@0:     L"Name TEXT COLLATE NOCASE,"
sl@0:     L"SongCount INTEGER)");
sl@0: 
sl@0: _LIT(KPlaylistCreateTable, "CREATE TABLE Playlist("
sl@0:     L"UniqueId INTEGER PRIMARY KEY,"
sl@0:     L"VolumeId INTEGER,"
sl@0:     L"DbFlag INTEGER,"
sl@0:     L"Sync INTEGER,"
sl@0:     L"Name TEXT COLLATE NOCASE,"
sl@0:     L"Uri TEXT,"
sl@0:     L"Time TEXT)");
sl@0: 
sl@0: _LIT(KPlaylistSongsCreateTable, "CREATE TABLE PlaylistSongs("
sl@0:     L"UniqueId INTEGER PRIMARY KEY AUTOINCREMENT,"
sl@0:     L"SongId INTEGER,"
sl@0:     L"PlaylistId INTEGER,"
sl@0:     L"Ordinal INTEGER)");
sl@0: 
sl@0: _LIT(KPlaylistSongInfoCreateTable, "CREATE TABLE PlaylistSongInfo("
sl@0:     L"SongId INTEGER PRIMARY KEY,"
sl@0:     L"VolumeId INTEGER,"
sl@0:     L"DbFlag INTEGER,"
sl@0:     L"Uri TEXT,"
sl@0:     L"Title TEXT COLLATE NOCASE)");
sl@0: 
sl@0: 
sl@0: _LIT(KBeginTransaction, "BEGIN TRANSACTION");
sl@0: _LIT(KCommitTransaction, "COMMIT TRANSACTION");
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: void TestEnvDestroy()
sl@0: 	{
sl@0: 	TheDbC.Close();
sl@0: 	(void)RSqlDatabase::Delete(TheDbFileName);
sl@0: 	TheFs.Close();
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: //Test macros and functions
sl@0: void Check1(TInt aValue, TInt aLine)
sl@0: 	{
sl@0: 	if(!aValue)
sl@0: 		{
sl@0: 		TestEnvDestroy();
sl@0: 		TheTest.Printf(_L("*** Line %d\r\n"), aLine);
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: void Check2(TInt aValue, TInt aExpected, TInt aLine)
sl@0: 	{
sl@0: 	if(aValue != aExpected)
sl@0: 		{
sl@0: 		TestEnvDestroy();
sl@0: 		TheTest.Printf(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);
sl@0: 		TheTest(EFalse, aLine);
sl@0: 		}
sl@0: 	}
sl@0: #define TEST(arg) ::Check1((arg), __LINE__)
sl@0: #define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)
sl@0: 
sl@0: 
sl@0: TInt GetDuration(TUint32 aStartTicks, TUint32 aEndTicks)
sl@0: 	{
sl@0: 	static TInt freq = 0;
sl@0: 	if(freq == 0)
sl@0: 		{
sl@0: 		HAL::Get(HAL::EFastCounterFrequency, freq);
sl@0: 		}
sl@0: 	TInt64 diffTicks = (TInt64)aEndTicks - (TInt64)aStartTicks;
sl@0: 	if(diffTicks < 0)
sl@0: 		{
sl@0: 		diffTicks = KMaxTUint32 + diffTicks + 1;
sl@0: 		}
sl@0: 	const TInt KMicroSecIn1Sec = 1000000;
sl@0: 	
sl@0: 	return ((diffTicks * KMicroSecIn1Sec) / freq);
sl@0: 	}
sl@0: 
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: void CreateDatabaseL(const TDesC& aDbName)
sl@0: 	{			
sl@0: 	// create the database now
sl@0: 	RSqlSecurityPolicy securityPolicy;
sl@0: 	CleanupClosePushL(securityPolicy);
sl@0: 	
sl@0: 	TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass);
sl@0: 	securityPolicy.Create(policy);
sl@0: 	
sl@0: 	TSecurityPolicy schemaPolicy(TSecurityPolicy::EAlwaysPass);
sl@0: 	TSecurityPolicy readPolicy(TSecurityPolicy::EAlwaysPass);
sl@0: 	TSecurityPolicy writePolicy(TSecurityPolicy::EAlwaysPass);
sl@0: 	
sl@0: 	User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy));
sl@0: 	User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));
sl@0: 	User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy));
sl@0:  
sl@0: 	TheTest.Printf(_L("Creating Database %S\n"),  &aDbName);
sl@0: 		
sl@0: 	TInt err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
sl@0: 
sl@0: 	if (KErrAlreadyExists == err)
sl@0: 		{
sl@0: 		
sl@0: 		// the file already exists
sl@0: 		// make sure we delete the file
sl@0:         User::LeaveIfError(TheDbC.Delete(aDbName));
sl@0: 
sl@0:         // try again
sl@0:         err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
sl@0: 
sl@0: 		}
sl@0: 	
sl@0: 	User::LeaveIfError(err);
sl@0: 	
sl@0: 	//Create tables	
sl@0: 	User::LeaveIfError(TheDbC.Exec(KMusicCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KAuxiliaryCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KAlbumCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KArtistCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KComposerCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KGenreCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KPlaylistCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KPlaylistSongInfoCreateTable));
sl@0: 	User::LeaveIfError(TheDbC.Exec(KPlaylistSongsCreateTable));
sl@0: 	
sl@0: 	TheDbC.Close();
sl@0: 	
sl@0: 	CleanupStack::PopAndDestroy(&securityPolicy);
sl@0: 	}
sl@0: 
sl@0: void TestEnvInit()
sl@0:     {
sl@0:     
sl@0: 	TInt err = TheFs.Connect();
sl@0: 	TEST2(err, KErrNone);	
sl@0: 	
sl@0: 	//Create database files
sl@0: 	TRAP(err,CreateDatabaseL(TheDbFileName));
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	}
sl@0: 	
sl@0: 		
sl@0: ///////////////////////////////////////////////////////////////////////////////////////
sl@0: /**
sl@0: @SYMTestCaseID			PDS-SQL-UT-4151
sl@0: @SYMTestCaseDesc		Measures the performance of inserting multiple records
sl@0: 						into the Music Player MPX database.  This test is based on 
sl@0: 						a real Music Player Harvesting use case
sl@0: @SYMTestPriority		Medium
sl@0: @SYMTestActions			Reads SQL transactions from a file and executes them.  
sl@0: 						Records the time for executing each statement
sl@0: @SYMTestExpectedResults All statements should be executed without error and 
sl@0: 						performance measurements logged
sl@0: @SYMDEF					DEF142306
sl@0: */
sl@0: void RunTest()
sl@0: 	{
sl@0: 	//Open the file with the sql statements 
sl@0: 	_LIT(KSqlFileName,"z:\\test\\t_sqlperformance4.sql");
sl@0: 	RFile sqlFile;
sl@0: 	TInt err = sqlFile.Open(TheFs, KSqlFileName, EFileRead); 
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	TInt fileLen = 0;
sl@0: 	err = sqlFile.Size(fileLen); 
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	HBufC8* sqlBuf = HBufC8::New(fileLen); 
sl@0: 	TEST(sqlBuf != NULL);
sl@0: 	TPtr8 sql = sqlBuf->Des();
sl@0: 	err = sqlFile.Read(sql);
sl@0: 	
sl@0: 	sqlFile.Close();
sl@0: 	TEST2(err, KErrNone);
sl@0: 	TEST2(sql.Length(), fileLen);
sl@0: 	
sl@0: 	//Open main database
sl@0: 	err = TheDbC.Open(TheDbFileName, &TheSqlConfigString);
sl@0: 	TEST2(err, KErrNone);
sl@0: 	
sl@0: 	TheTest.Printf(_L("Beginning INSERTS...\n"));
sl@0: 	
sl@0: 	const TInt KRecordCount = 6544;
sl@0: 	TInt recordCount = 0;
sl@0: 	TInt insertCnt = 0;
sl@0: 	TInt updateCnt = 0;
sl@0: 	TInt selectCnt = 0;
sl@0: 	TInt trnCnt = 0;
sl@0: 	TInt totalTime = 0;
sl@0: 
sl@0: 	TInt insertTrnCnt = 0;
sl@0: 	TInt updateTrnCnt = 0;
sl@0: 	TInt selectTrnCnt = 0;
sl@0: 	
sl@0: 	for(;sql.Length()>0;)
sl@0: 		{
sl@0: 		TInt eolPos = sql.Locate(TChar('\n'));
sl@0: 		if(eolPos < 0)
sl@0: 			{
sl@0: 			break;//No more SQL statements
sl@0: 			}
sl@0: 		TInt stmtLength = eolPos;
sl@0: 		while (stmtLength > 0 && (sql[stmtLength-1] == '\r'))
sl@0: 			{
sl@0: 			--stmtLength; //Reduce length to remove carriage return characters from the end of the statement string
sl@0: 			}
sl@0: 		TPtrC8 sqlStmt8(sql.Ptr(), stmtLength);
sl@0: 		TPtrC8 ptr = sql.Mid(eolPos + 1);//"eolPos + 1" - first character after '\n'
sl@0: 		sql.Set(const_cast <TUint8*> (ptr.Ptr()), ptr.Length(), ptr.Length());
sl@0: 		++recordCount;
sl@0: 		
sl@0: 		//Convert to 16 bit query string
sl@0: 		TBuf<1024> query;
sl@0: 		query.Copy(sqlStmt8);
sl@0: 		
sl@0: 		//Execute the statement
sl@0: 		TInt start = User::FastCounter();
sl@0: 		err = TheDbC.Exec(query);
sl@0: 		TInt end = User::FastCounter();
sl@0: 		
sl@0: 		TEST(err >= 0);
sl@0: 		
sl@0: 		//Get the execution time for that statement
sl@0: 		TInt duration = GetDuration(start, end);
sl@0: 		totalTime += duration;
sl@0: 		
sl@0: 		if(query == KBeginTransaction)
sl@0: 			{		
sl@0: 			TheTest.Printf(_L("Execute Statement - BEGIN: %d us\n"), duration);
sl@0: 			}
sl@0: 		
sl@0: 		else if(query == KCommitTransaction)
sl@0: 			{
sl@0: 			++trnCnt;
sl@0: 			TheTest.Printf(_L("Execute Statement - COMMIT: %d us, Trn#%d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"), 
sl@0: 					duration, trnCnt, insertTrnCnt, updateTrnCnt, selectTrnCnt);
sl@0: 			insertTrnCnt = updateTrnCnt = selectTrnCnt = 0;
sl@0: 			}
sl@0: 
sl@0: 		else
sl@0: 			{	
sl@0: 			TPtrC queryType(query.Ptr(), 6);
sl@0: 			TheTest.Printf(_L("Execute Statement - %S: %d us\n"),&queryType, duration);
sl@0: 			if(queryType.FindF(_L("INSERT")) >= 0)
sl@0: 				{
sl@0: 				++insertCnt;
sl@0: 				++insertTrnCnt;
sl@0: 				}
sl@0: 			else if(queryType.FindF(_L("UPDATE")) >= 0)
sl@0: 				{
sl@0: 				++updateCnt;
sl@0: 				++updateTrnCnt;
sl@0: 				}
sl@0: 			else if(queryType.FindF(_L("SELECT")) >= 0)
sl@0: 				{
sl@0: 				++selectCnt;
sl@0: 				++selectTrnCnt;
sl@0: 				}
sl@0: 			}
sl@0: 		}
sl@0: 	delete sqlBuf;
sl@0: 	
sl@0: 	TheDbC.Close();
sl@0: 	
sl@0: 	TheTest.Printf(_L("Total time to process Songs: %d us\n"), totalTime);
sl@0: 	TheTest.Printf(_L("Transactions count: %d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"), 
sl@0: 			               trnCnt, insertCnt, updateCnt, selectCnt);
sl@0: 	TEST2(recordCount, KRecordCount);
sl@0: 	}
sl@0: ///////////////////////////////////////////////////////////////////////////////////
sl@0: ///////////////////////////////////////////////////////////////////////////////////
sl@0: 
sl@0: void DoTests()
sl@0: 	{
sl@0: 	TheTestTitle.Format(_L("@SYMTestCaseID:PDS-SQL-UT-4151; SQL Music Player Db Performance Test, encoding: \"%S\", page size: %d\r\n"), 
sl@0: 			TheCmdLineParams.iDbEncoding == TCmdLineParams::EDbUtf16 ? &KUtf16 : &KUtf8, TheCmdLineParams.iPageSize);
sl@0: 	TheTest.Start(TheTestTitle);
sl@0: 	
sl@0: 	RunTest();
sl@0: 	}
sl@0: 
sl@0: TInt E32Main()
sl@0: 	{
sl@0: 	TheTest.Title();
sl@0: 
sl@0: 	CTrapCleanup* tc = CTrapCleanup::New();
sl@0: 	TheTest(tc != NULL);
sl@0: 
sl@0: 	__UHEAP_MARK;
sl@0: 
sl@0: 	GetCmdLineParamsAndSqlConfigString(TheTest, _L("t_sqlperformance4"), TheCmdLineParams, TheSqlConfigString);
sl@0: 	PrepareDbName(KCDriveDatabase, TheCmdLineParams.iDriveName, TheDbFileName);
sl@0: 
sl@0: 	TheTest.Printf(_L("==Databases: %S\r\n"), &TheDbFileName); 
sl@0: 	
sl@0: 	TestEnvInit();
sl@0: 	
sl@0: 	DoTests();
sl@0: 	
sl@0: 	TestEnvDestroy();
sl@0: 	
sl@0: 	__UHEAP_MARKEND;
sl@0: 	
sl@0: 	TheTest.End();
sl@0: 	TheTest.Close();
sl@0: 	
sl@0: 	delete tc;
sl@0: 
sl@0: 	User::Heap().Check();
sl@0: 	return KErrNone;
sl@0: 	}