--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/persistentstorage/sql/TEST/t_sqlperformance4.cpp Fri Jan 22 11:06:30 2010 +0200
@@ -0,0 +1,417 @@
+// Copyright (c) 2008-2009 Nokia Corporation and/or its subsidiary(-ies).
+// All rights reserved.
+// This component and the accompanying materials are made available
+// under the terms of "Eclipse Public License v1.0"
+// which accompanies this distribution, and is available
+// at the URL "http://www.eclipse.org/legal/epl-v10.html".
+//
+// Initial Contributors:
+// Nokia Corporation - initial contribution.
+//
+// Contributors:
+//
+// Description:
+//
+
+#include <e32test.h>
+#include <bautils.h>
+#include <sqldb.h>
+#include <hal.h>
+
+///////////////////////////////////////////////////////////////////////////////////////
+
+RTest TheTest(_L("t_sqlperformance4 test"));
+RSqlDatabase TheDbC;
+RFs TheFs;
+
+_LIT(KCDriveDatabase, "c:[a000017f]t_sqlperformance4.db");
+
+TFileName TheDbFileName;
+TBuf<256> TheCmd;
+TDriveName TheDriveName;
+TParse TheParse;
+
+_LIT8( KMCSqlConfig, "cache_size=1024; page_size=1024; " );
+
+_LIT(KMusicCreateTable, "CREATE TABLE Music("
+ L"UniqueId INTEGER PRIMARY KEY,"
+ L"DbFlag INTEGER,"
+ L"VolumeId INTEGER,"
+ L"Title TEXT COLLATE NOCASE,"
+ L"Artist INTEGER,"
+ L"Art TEXT,"
+ L"Deleted INTEGER DEFAULT 0,"
+ L"Location TEXT,"
+ L"AlbumTrack INTEGER,"
+ L"PlayCount INTEGER DEFAULT 0,"
+ L"TimeAdded TEXT,"
+ L"TimePlayed TEXT DEFAULT '',"
+ L"Duration INTEGER,"
+ L"Sync INTEGER DEFAULT 0,"
+ L"Modified INTEGER DEFAULT 0,"
+ L"Album INTEGER,"
+ L"Genre INTEGER,"
+ L"Composer INTEGER,"
+ L"ReleaseDate TEXT DEFAULT '',"
+ L"Rating INTEGER,"
+ L"Comment TEXT,"
+ L"Copyright TEXT,"
+ L"Url TEXT,"
+ L"DRM INTEGER,"
+ L"LastPlayPosition INTEGER DEFAULT 0,"
+ L"SampleRate INTEGER,"
+ L"BitRate INTEGER,"
+ L"NumChannels INTEGER,"
+ L"Codec INTEGER,"
+ L"MimeType TEXT,"
+ L"MTPDrmStatus INTEGER)");
+
+_LIT(KAuxiliaryCreateTable, "CREATE TABLE Auxiliary("
+ L"Id INTEGER,"
+ L"Version TEXT,"
+ L"TimeRefreshed TEXT,"
+ L"TimeSynced TEXT,"
+ L"Corrupt INTEGER DEFAULT 0,"
+ L"SaveDeletedRecordCount INTEGER DEFAULT 0)");
+
+_LIT(KAlbumCreateTable,"CREATE TABLE Album("
+ L"UniqueId INTEGER PRIMARY KEY,"
+ L"Name TEXT COLLATE NOCASE,"
+ L"SongCount INTEGER,"
+ L"Artist INTEGER,"
+ L"Art TEXT)");
+
+_LIT(KArtistCreateTable,"CREATE TABLE Artist("
+ L"UniqueId INTEGER PRIMARY KEY,"
+ L"Name TEXT COLLATE NOCASE,"
+ L"SongCount INTEGER)");
+
+_LIT(KComposerCreateTable,"CREATE TABLE Composer("
+ L"UniqueId INTEGER PRIMARY KEY,"
+ L"Name TEXT COLLATE NOCASE,"
+ L"SongCount INTEGER)");
+
+_LIT(KGenreCreateTable,"CREATE TABLE Genre("
+ L"UniqueId INTEGER PRIMARY KEY,"
+ L"Name TEXT COLLATE NOCASE,"
+ L"SongCount INTEGER)");
+
+_LIT(KPlaylistCreateTable, "CREATE TABLE Playlist("
+ L"UniqueId INTEGER PRIMARY KEY,"
+ L"VolumeId INTEGER,"
+ L"DbFlag INTEGER,"
+ L"Sync INTEGER,"
+ L"Name TEXT COLLATE NOCASE,"
+ L"Uri TEXT,"
+ L"Time TEXT)");
+
+_LIT(KPlaylistSongsCreateTable, "CREATE TABLE PlaylistSongs("
+ L"UniqueId INTEGER PRIMARY KEY AUTOINCREMENT,"
+ L"SongId INTEGER,"
+ L"PlaylistId INTEGER,"
+ L"Ordinal INTEGER)");
+
+_LIT(KPlaylistSongInfoCreateTable, "CREATE TABLE PlaylistSongInfo("
+ L"SongId INTEGER PRIMARY KEY,"
+ L"VolumeId INTEGER,"
+ L"DbFlag INTEGER,"
+ L"Uri TEXT,"
+ L"Title TEXT COLLATE NOCASE)");
+
+
+_LIT(KBeginTransaction, "BEGIN TRANSACTION");
+_LIT(KCommitTransaction, "COMMIT TRANSACTION");
+
+///////////////////////////////////////////////////////////////////////////////////////
+
+void TestEnvDestroy()
+ {
+ TheDbC.Close();
+ (void)RSqlDatabase::Delete(TheDbFileName);
+ TheFs.Close();
+ }
+
+///////////////////////////////////////////////////////////////////////////////////////
+///////////////////////////////////////////////////////////////////////////////////////
+//Test macros and functions
+void Check1(TInt aValue, TInt aLine)
+ {
+ if(!aValue)
+ {
+ TestEnvDestroy();
+ TheTest.Printf(_L("*** Line %d\r\n"), aLine);
+ TheTest(EFalse, aLine);
+ }
+ }
+void Check2(TInt aValue, TInt aExpected, TInt aLine)
+ {
+ if(aValue != aExpected)
+ {
+ TestEnvDestroy();
+ TheTest.Printf(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);
+ TheTest(EFalse, aLine);
+ }
+ }
+#define TEST(arg) ::Check1((arg), __LINE__)
+#define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)
+
+
+TInt GetDuration(TUint32 aStartTicks, TUint32 aEndTicks)
+ {
+ static TInt freq = 0;
+ if(freq == 0)
+ {
+ HAL::Get(HAL::EFastCounterFrequency, freq);
+ }
+ TInt64 diffTicks = (TInt64)aEndTicks - (TInt64)aStartTicks;
+ if(diffTicks < 0)
+ {
+ diffTicks = KMaxTUint32 + diffTicks + 1;
+ }
+ const TInt KMicroSecIn1Sec = 1000000;
+
+ return ((diffTicks * KMicroSecIn1Sec) / freq);
+ }
+
+///////////////////////////////////////////////////////////////////////////////////////
+
+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, &KMCSqlConfig);
+
+ TBuf<64> tmp;
+ tmp.Copy(KMCSqlConfig);
+ tmp.Append(_L("\n"));
+
+ TheTest.Printf(tmp);
+
+ 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, &KMCSqlConfig);
+
+ }
+
+ 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);
+ }
+
+void TestEnvInit()
+ {
+
+ TInt err = TheFs.Connect();
+ TEST2(err, KErrNone);
+
+ //Create database files
+ TRAP(err,CreateDatabaseL(TheDbFileName));
+ TEST2(err, KErrNone);
+
+ }
+
+
+///////////////////////////////////////////////////////////////////////////////////////
+/**
+@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);
+ 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
+ }
+ TPtrC8 sqlStmt8(sql.Ptr(), eolPos - 1);//"eolPos - 1" - to cut off the '\r' character
+ 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);
+ }
+///////////////////////////////////////////////////////////////////////////////////
+///////////////////////////////////////////////////////////////////////////////////
+
+void DoTests()
+ {
+ TheTest.Start(_L("@SYMTestCaseID:PDS-SQL-UT-4151; SQL Music Player Db Performance Test"));
+
+ RunTest();
+ }
+
+//Usage: "t_sqlperformance4 [<drive letter>:]"
+
+TInt E32Main()
+ {
+ TheTest.Title();
+
+ CTrapCleanup* tc = CTrapCleanup::New();
+ TheTest(tc != NULL);
+
+ __UHEAP_MARK;
+
+ User::CommandLine(TheCmd);
+ TheCmd.TrimAll();
+ if(TheCmd.Length() > 0)
+ {
+ TheDriveName.Copy(TheCmd);
+ }
+ TheParse.Set(TheDriveName, &KCDriveDatabase, 0);
+ const TDesC& dbFilePath = TheParse.FullName();
+ TheDbFileName.Copy(dbFilePath);
+ TheTest.Printf(_L("==Database file name: %S\r\n"), &TheDbFileName);
+
+ TestEnvInit();
+
+ DoTests();
+
+ TestEnvDestroy();
+
+ __UHEAP_MARKEND;
+
+ TheTest.End();
+ TheTest.Close();
+
+ delete tc;
+
+ User::Heap().Check();
+ return KErrNone;
+ }