1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sql/TEST/t_sqlperformance4.cpp Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,413 @@
1.4 +// Copyright (c) 2009-2010 Nokia Corporation and/or its subsidiary(-ies).
1.5 +// All rights reserved.
1.6 +// This component and the accompanying materials are made available
1.7 +// under the terms of "Eclipse Public License v1.0"
1.8 +// which accompanies this distribution, and is available
1.9 +// at the URL "http://www.eclipse.org/legal/epl-v10.html".
1.10 +//
1.11 +// Initial Contributors:
1.12 +// Nokia Corporation - initial contribution.
1.13 +//
1.14 +// Contributors:
1.15 +// NTT DOCOMO, INC - Fix for Bug 3170 "SQL library test T_SQLPERFORMANCE4 fails with USER 84 panic"
1.16 +//
1.17 +// Description:
1.18 +//
1.19 +
1.20 +#include <e32test.h>
1.21 +#include <bautils.h>
1.22 +#include <sqldb.h>
1.23 +#include <hal.h>
1.24 +#include "t_sqlcmdlineutil.h"
1.25 +
1.26 +///////////////////////////////////////////////////////////////////////////////////////
1.27 +
1.28 +RTest TheTest(_L("t_sqlperformance4 test"));
1.29 +RSqlDatabase TheDbC;
1.30 +RFs TheFs;
1.31 +
1.32 +_LIT(KCDriveDatabase, "c:[a000017f]t_sqlperformance4.db");
1.33 +
1.34 +TFileName TheDbFileName;
1.35 +TBuf<200> TheTestTitle;
1.36 +TCmdLineParams TheCmdLineParams;
1.37 +TBuf8<200> TheSqlConfigString;
1.38 +
1.39 +_LIT(KUtf8, "UTF8 ");
1.40 +_LIT(KUtf16, "UTF16");
1.41 +
1.42 +_LIT(KMusicCreateTable, "CREATE TABLE Music("
1.43 + L"UniqueId INTEGER PRIMARY KEY,"
1.44 + L"DbFlag INTEGER,"
1.45 + L"VolumeId INTEGER,"
1.46 + L"Title TEXT COLLATE NOCASE,"
1.47 + L"Artist INTEGER,"
1.48 + L"Art TEXT,"
1.49 + L"Deleted INTEGER DEFAULT 0,"
1.50 + L"Location TEXT,"
1.51 + L"AlbumTrack INTEGER,"
1.52 + L"PlayCount INTEGER DEFAULT 0,"
1.53 + L"TimeAdded TEXT,"
1.54 + L"TimePlayed TEXT DEFAULT '',"
1.55 + L"Duration INTEGER,"
1.56 + L"Sync INTEGER DEFAULT 0,"
1.57 + L"Modified INTEGER DEFAULT 0,"
1.58 + L"Album INTEGER,"
1.59 + L"Genre INTEGER,"
1.60 + L"Composer INTEGER,"
1.61 + L"ReleaseDate TEXT DEFAULT '',"
1.62 + L"Rating INTEGER,"
1.63 + L"Comment TEXT,"
1.64 + L"Copyright TEXT,"
1.65 + L"Url TEXT,"
1.66 + L"DRM INTEGER,"
1.67 + L"LastPlayPosition INTEGER DEFAULT 0,"
1.68 + L"SampleRate INTEGER,"
1.69 + L"BitRate INTEGER,"
1.70 + L"NumChannels INTEGER,"
1.71 + L"Codec INTEGER,"
1.72 + L"MimeType TEXT,"
1.73 + L"MTPDrmStatus INTEGER)");
1.74 +
1.75 +_LIT(KAuxiliaryCreateTable, "CREATE TABLE Auxiliary("
1.76 + L"Id INTEGER,"
1.77 + L"Version TEXT,"
1.78 + L"TimeRefreshed TEXT,"
1.79 + L"TimeSynced TEXT,"
1.80 + L"Corrupt INTEGER DEFAULT 0,"
1.81 + L"SaveDeletedRecordCount INTEGER DEFAULT 0)");
1.82 +
1.83 +_LIT(KAlbumCreateTable,"CREATE TABLE Album("
1.84 + L"UniqueId INTEGER PRIMARY KEY,"
1.85 + L"Name TEXT COLLATE NOCASE,"
1.86 + L"SongCount INTEGER,"
1.87 + L"Artist INTEGER,"
1.88 + L"Art TEXT)");
1.89 +
1.90 +_LIT(KArtistCreateTable,"CREATE TABLE Artist("
1.91 + L"UniqueId INTEGER PRIMARY KEY,"
1.92 + L"Name TEXT COLLATE NOCASE,"
1.93 + L"SongCount INTEGER)");
1.94 +
1.95 +_LIT(KComposerCreateTable,"CREATE TABLE Composer("
1.96 + L"UniqueId INTEGER PRIMARY KEY,"
1.97 + L"Name TEXT COLLATE NOCASE,"
1.98 + L"SongCount INTEGER)");
1.99 +
1.100 +_LIT(KGenreCreateTable,"CREATE TABLE Genre("
1.101 + L"UniqueId INTEGER PRIMARY KEY,"
1.102 + L"Name TEXT COLLATE NOCASE,"
1.103 + L"SongCount INTEGER)");
1.104 +
1.105 +_LIT(KPlaylistCreateTable, "CREATE TABLE Playlist("
1.106 + L"UniqueId INTEGER PRIMARY KEY,"
1.107 + L"VolumeId INTEGER,"
1.108 + L"DbFlag INTEGER,"
1.109 + L"Sync INTEGER,"
1.110 + L"Name TEXT COLLATE NOCASE,"
1.111 + L"Uri TEXT,"
1.112 + L"Time TEXT)");
1.113 +
1.114 +_LIT(KPlaylistSongsCreateTable, "CREATE TABLE PlaylistSongs("
1.115 + L"UniqueId INTEGER PRIMARY KEY AUTOINCREMENT,"
1.116 + L"SongId INTEGER,"
1.117 + L"PlaylistId INTEGER,"
1.118 + L"Ordinal INTEGER)");
1.119 +
1.120 +_LIT(KPlaylistSongInfoCreateTable, "CREATE TABLE PlaylistSongInfo("
1.121 + L"SongId INTEGER PRIMARY KEY,"
1.122 + L"VolumeId INTEGER,"
1.123 + L"DbFlag INTEGER,"
1.124 + L"Uri TEXT,"
1.125 + L"Title TEXT COLLATE NOCASE)");
1.126 +
1.127 +
1.128 +_LIT(KBeginTransaction, "BEGIN TRANSACTION");
1.129 +_LIT(KCommitTransaction, "COMMIT TRANSACTION");
1.130 +
1.131 +///////////////////////////////////////////////////////////////////////////////////////
1.132 +
1.133 +void TestEnvDestroy()
1.134 + {
1.135 + TheDbC.Close();
1.136 + (void)RSqlDatabase::Delete(TheDbFileName);
1.137 + TheFs.Close();
1.138 + }
1.139 +
1.140 +///////////////////////////////////////////////////////////////////////////////////////
1.141 +///////////////////////////////////////////////////////////////////////////////////////
1.142 +//Test macros and functions
1.143 +void Check1(TInt aValue, TInt aLine)
1.144 + {
1.145 + if(!aValue)
1.146 + {
1.147 + TestEnvDestroy();
1.148 + TheTest.Printf(_L("*** Line %d\r\n"), aLine);
1.149 + TheTest(EFalse, aLine);
1.150 + }
1.151 + }
1.152 +void Check2(TInt aValue, TInt aExpected, TInt aLine)
1.153 + {
1.154 + if(aValue != aExpected)
1.155 + {
1.156 + TestEnvDestroy();
1.157 + TheTest.Printf(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);
1.158 + TheTest(EFalse, aLine);
1.159 + }
1.160 + }
1.161 +#define TEST(arg) ::Check1((arg), __LINE__)
1.162 +#define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)
1.163 +
1.164 +
1.165 +TInt GetDuration(TUint32 aStartTicks, TUint32 aEndTicks)
1.166 + {
1.167 + static TInt freq = 0;
1.168 + if(freq == 0)
1.169 + {
1.170 + HAL::Get(HAL::EFastCounterFrequency, freq);
1.171 + }
1.172 + TInt64 diffTicks = (TInt64)aEndTicks - (TInt64)aStartTicks;
1.173 + if(diffTicks < 0)
1.174 + {
1.175 + diffTicks = KMaxTUint32 + diffTicks + 1;
1.176 + }
1.177 + const TInt KMicroSecIn1Sec = 1000000;
1.178 +
1.179 + return ((diffTicks * KMicroSecIn1Sec) / freq);
1.180 + }
1.181 +
1.182 +///////////////////////////////////////////////////////////////////////////////////////
1.183 +
1.184 +void CreateDatabaseL(const TDesC& aDbName)
1.185 + {
1.186 + // create the database now
1.187 + RSqlSecurityPolicy securityPolicy;
1.188 + CleanupClosePushL(securityPolicy);
1.189 +
1.190 + TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass);
1.191 + securityPolicy.Create(policy);
1.192 +
1.193 + TSecurityPolicy schemaPolicy(TSecurityPolicy::EAlwaysPass);
1.194 + TSecurityPolicy readPolicy(TSecurityPolicy::EAlwaysPass);
1.195 + TSecurityPolicy writePolicy(TSecurityPolicy::EAlwaysPass);
1.196 +
1.197 + User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy));
1.198 + User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy));
1.199 + User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy));
1.200 +
1.201 + TheTest.Printf(_L("Creating Database %S\n"), &aDbName);
1.202 +
1.203 + TInt err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
1.204 +
1.205 + if (KErrAlreadyExists == err)
1.206 + {
1.207 +
1.208 + // the file already exists
1.209 + // make sure we delete the file
1.210 + User::LeaveIfError(TheDbC.Delete(aDbName));
1.211 +
1.212 + // try again
1.213 + err = TheDbC.Create(aDbName, securityPolicy, &TheSqlConfigString);
1.214 +
1.215 + }
1.216 +
1.217 + User::LeaveIfError(err);
1.218 +
1.219 + //Create tables
1.220 + User::LeaveIfError(TheDbC.Exec(KMusicCreateTable));
1.221 + User::LeaveIfError(TheDbC.Exec(KAuxiliaryCreateTable));
1.222 + User::LeaveIfError(TheDbC.Exec(KAlbumCreateTable));
1.223 + User::LeaveIfError(TheDbC.Exec(KArtistCreateTable));
1.224 + User::LeaveIfError(TheDbC.Exec(KComposerCreateTable));
1.225 + User::LeaveIfError(TheDbC.Exec(KGenreCreateTable));
1.226 + User::LeaveIfError(TheDbC.Exec(KPlaylistCreateTable));
1.227 + User::LeaveIfError(TheDbC.Exec(KPlaylistSongInfoCreateTable));
1.228 + User::LeaveIfError(TheDbC.Exec(KPlaylistSongsCreateTable));
1.229 +
1.230 + TheDbC.Close();
1.231 +
1.232 + CleanupStack::PopAndDestroy(&securityPolicy);
1.233 + }
1.234 +
1.235 +void TestEnvInit()
1.236 + {
1.237 +
1.238 + TInt err = TheFs.Connect();
1.239 + TEST2(err, KErrNone);
1.240 +
1.241 + //Create database files
1.242 + TRAP(err,CreateDatabaseL(TheDbFileName));
1.243 + TEST2(err, KErrNone);
1.244 +
1.245 + }
1.246 +
1.247 +
1.248 +///////////////////////////////////////////////////////////////////////////////////////
1.249 +/**
1.250 +@SYMTestCaseID PDS-SQL-UT-4151
1.251 +@SYMTestCaseDesc Measures the performance of inserting multiple records
1.252 + into the Music Player MPX database. This test is based on
1.253 + a real Music Player Harvesting use case
1.254 +@SYMTestPriority Medium
1.255 +@SYMTestActions Reads SQL transactions from a file and executes them.
1.256 + Records the time for executing each statement
1.257 +@SYMTestExpectedResults All statements should be executed without error and
1.258 + performance measurements logged
1.259 +@SYMDEF DEF142306
1.260 +*/
1.261 +void RunTest()
1.262 + {
1.263 + //Open the file with the sql statements
1.264 + _LIT(KSqlFileName,"z:\\test\\t_sqlperformance4.sql");
1.265 + RFile sqlFile;
1.266 + TInt err = sqlFile.Open(TheFs, KSqlFileName, EFileRead);
1.267 + TEST2(err, KErrNone);
1.268 +
1.269 + TInt fileLen = 0;
1.270 + err = sqlFile.Size(fileLen);
1.271 + TEST2(err, KErrNone);
1.272 +
1.273 + HBufC8* sqlBuf = HBufC8::New(fileLen);
1.274 + TEST(sqlBuf != NULL);
1.275 + TPtr8 sql = sqlBuf->Des();
1.276 + err = sqlFile.Read(sql);
1.277 +
1.278 + sqlFile.Close();
1.279 + TEST2(err, KErrNone);
1.280 + TEST2(sql.Length(), fileLen);
1.281 +
1.282 + //Open main database
1.283 + err = TheDbC.Open(TheDbFileName, &TheSqlConfigString);
1.284 + TEST2(err, KErrNone);
1.285 +
1.286 + TheTest.Printf(_L("Beginning INSERTS...\n"));
1.287 +
1.288 + const TInt KRecordCount = 6544;
1.289 + TInt recordCount = 0;
1.290 + TInt insertCnt = 0;
1.291 + TInt updateCnt = 0;
1.292 + TInt selectCnt = 0;
1.293 + TInt trnCnt = 0;
1.294 + TInt totalTime = 0;
1.295 +
1.296 + TInt insertTrnCnt = 0;
1.297 + TInt updateTrnCnt = 0;
1.298 + TInt selectTrnCnt = 0;
1.299 +
1.300 + for(;sql.Length()>0;)
1.301 + {
1.302 + TInt eolPos = sql.Locate(TChar('\n'));
1.303 + if(eolPos < 0)
1.304 + {
1.305 + break;//No more SQL statements
1.306 + }
1.307 + TInt stmtLength = eolPos;
1.308 + while (stmtLength > 0 && (sql[stmtLength-1] == '\r'))
1.309 + {
1.310 + --stmtLength; //Reduce length to remove carriage return characters from the end of the statement string
1.311 + }
1.312 + TPtrC8 sqlStmt8(sql.Ptr(), stmtLength);
1.313 + TPtrC8 ptr = sql.Mid(eolPos + 1);//"eolPos + 1" - first character after '\n'
1.314 + sql.Set(const_cast <TUint8*> (ptr.Ptr()), ptr.Length(), ptr.Length());
1.315 + ++recordCount;
1.316 +
1.317 + //Convert to 16 bit query string
1.318 + TBuf<1024> query;
1.319 + query.Copy(sqlStmt8);
1.320 +
1.321 + //Execute the statement
1.322 + TInt start = User::FastCounter();
1.323 + err = TheDbC.Exec(query);
1.324 + TInt end = User::FastCounter();
1.325 +
1.326 + TEST(err >= 0);
1.327 +
1.328 + //Get the execution time for that statement
1.329 + TInt duration = GetDuration(start, end);
1.330 + totalTime += duration;
1.331 +
1.332 + if(query == KBeginTransaction)
1.333 + {
1.334 + TheTest.Printf(_L("Execute Statement - BEGIN: %d us\n"), duration);
1.335 + }
1.336 +
1.337 + else if(query == KCommitTransaction)
1.338 + {
1.339 + ++trnCnt;
1.340 + TheTest.Printf(_L("Execute Statement - COMMIT: %d us, Trn#%d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"),
1.341 + duration, trnCnt, insertTrnCnt, updateTrnCnt, selectTrnCnt);
1.342 + insertTrnCnt = updateTrnCnt = selectTrnCnt = 0;
1.343 + }
1.344 +
1.345 + else
1.346 + {
1.347 + TPtrC queryType(query.Ptr(), 6);
1.348 + TheTest.Printf(_L("Execute Statement - %S: %d us\n"),&queryType, duration);
1.349 + if(queryType.FindF(_L("INSERT")) >= 0)
1.350 + {
1.351 + ++insertCnt;
1.352 + ++insertTrnCnt;
1.353 + }
1.354 + else if(queryType.FindF(_L("UPDATE")) >= 0)
1.355 + {
1.356 + ++updateCnt;
1.357 + ++updateTrnCnt;
1.358 + }
1.359 + else if(queryType.FindF(_L("SELECT")) >= 0)
1.360 + {
1.361 + ++selectCnt;
1.362 + ++selectTrnCnt;
1.363 + }
1.364 + }
1.365 + }
1.366 + delete sqlBuf;
1.367 +
1.368 + TheDbC.Close();
1.369 +
1.370 + TheTest.Printf(_L("Total time to process Songs: %d us\n"), totalTime);
1.371 + TheTest.Printf(_L("Transactions count: %d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"),
1.372 + trnCnt, insertCnt, updateCnt, selectCnt);
1.373 + TEST2(recordCount, KRecordCount);
1.374 + }
1.375 +///////////////////////////////////////////////////////////////////////////////////
1.376 +///////////////////////////////////////////////////////////////////////////////////
1.377 +
1.378 +void DoTests()
1.379 + {
1.380 + TheTestTitle.Format(_L("@SYMTestCaseID:PDS-SQL-UT-4151; SQL Music Player Db Performance Test, encoding: \"%S\", page size: %d\r\n"),
1.381 + TheCmdLineParams.iDbEncoding == TCmdLineParams::EDbUtf16 ? &KUtf16 : &KUtf8, TheCmdLineParams.iPageSize);
1.382 + TheTest.Start(TheTestTitle);
1.383 +
1.384 + RunTest();
1.385 + }
1.386 +
1.387 +TInt E32Main()
1.388 + {
1.389 + TheTest.Title();
1.390 +
1.391 + CTrapCleanup* tc = CTrapCleanup::New();
1.392 + TheTest(tc != NULL);
1.393 +
1.394 + __UHEAP_MARK;
1.395 +
1.396 + GetCmdLineParamsAndSqlConfigString(TheTest, _L("t_sqlperformance4"), TheCmdLineParams, TheSqlConfigString);
1.397 + PrepareDbName(KCDriveDatabase, TheCmdLineParams.iDriveName, TheDbFileName);
1.398 +
1.399 + TheTest.Printf(_L("==Databases: %S\r\n"), &TheDbFileName);
1.400 +
1.401 + TestEnvInit();
1.402 +
1.403 + DoTests();
1.404 +
1.405 + TestEnvDestroy();
1.406 +
1.407 + __UHEAP_MARKEND;
1.408 +
1.409 + TheTest.End();
1.410 + TheTest.Close();
1.411 +
1.412 + delete tc;
1.413 +
1.414 + User::Heap().Check();
1.415 + return KErrNone;
1.416 + }