|
1 // Copyright (c) 2008-2009 Nokia Corporation and/or its subsidiary(-ies). |
|
2 // All rights reserved. |
|
3 // This component and the accompanying materials are made available |
|
4 // under the terms of "Eclipse Public License v1.0" |
|
5 // which accompanies this distribution, and is available |
|
6 // at the URL "http://www.eclipse.org/legal/epl-v10.html". |
|
7 // |
|
8 // Initial Contributors: |
|
9 // Nokia Corporation - initial contribution. |
|
10 // |
|
11 // Contributors: |
|
12 // |
|
13 // Description: |
|
14 // |
|
15 |
|
16 #include <e32test.h> |
|
17 #include <bautils.h> |
|
18 #include <sqldb.h> |
|
19 #include <hal.h> |
|
20 |
|
21 /////////////////////////////////////////////////////////////////////////////////////// |
|
22 |
|
23 RTest TheTest(_L("t_sqlperformance4 test")); |
|
24 RSqlDatabase TheDbC; |
|
25 RFs TheFs; |
|
26 |
|
27 _LIT(KCDriveDatabase, "c:[a000017f]t_sqlperformance4.db"); |
|
28 |
|
29 TFileName TheDbFileName; |
|
30 TBuf<256> TheCmd; |
|
31 TDriveName TheDriveName; |
|
32 TParse TheParse; |
|
33 |
|
34 _LIT8( KMCSqlConfig, "cache_size=1024; page_size=1024; " ); |
|
35 |
|
36 _LIT(KMusicCreateTable, "CREATE TABLE Music(" |
|
37 L"UniqueId INTEGER PRIMARY KEY," |
|
38 L"DbFlag INTEGER," |
|
39 L"VolumeId INTEGER," |
|
40 L"Title TEXT COLLATE NOCASE," |
|
41 L"Artist INTEGER," |
|
42 L"Art TEXT," |
|
43 L"Deleted INTEGER DEFAULT 0," |
|
44 L"Location TEXT," |
|
45 L"AlbumTrack INTEGER," |
|
46 L"PlayCount INTEGER DEFAULT 0," |
|
47 L"TimeAdded TEXT," |
|
48 L"TimePlayed TEXT DEFAULT ''," |
|
49 L"Duration INTEGER," |
|
50 L"Sync INTEGER DEFAULT 0," |
|
51 L"Modified INTEGER DEFAULT 0," |
|
52 L"Album INTEGER," |
|
53 L"Genre INTEGER," |
|
54 L"Composer INTEGER," |
|
55 L"ReleaseDate TEXT DEFAULT ''," |
|
56 L"Rating INTEGER," |
|
57 L"Comment TEXT," |
|
58 L"Copyright TEXT," |
|
59 L"Url TEXT," |
|
60 L"DRM INTEGER," |
|
61 L"LastPlayPosition INTEGER DEFAULT 0," |
|
62 L"SampleRate INTEGER," |
|
63 L"BitRate INTEGER," |
|
64 L"NumChannels INTEGER," |
|
65 L"Codec INTEGER," |
|
66 L"MimeType TEXT," |
|
67 L"MTPDrmStatus INTEGER)"); |
|
68 |
|
69 _LIT(KAuxiliaryCreateTable, "CREATE TABLE Auxiliary(" |
|
70 L"Id INTEGER," |
|
71 L"Version TEXT," |
|
72 L"TimeRefreshed TEXT," |
|
73 L"TimeSynced TEXT," |
|
74 L"Corrupt INTEGER DEFAULT 0," |
|
75 L"SaveDeletedRecordCount INTEGER DEFAULT 0)"); |
|
76 |
|
77 _LIT(KAlbumCreateTable,"CREATE TABLE Album(" |
|
78 L"UniqueId INTEGER PRIMARY KEY," |
|
79 L"Name TEXT COLLATE NOCASE," |
|
80 L"SongCount INTEGER," |
|
81 L"Artist INTEGER," |
|
82 L"Art TEXT)"); |
|
83 |
|
84 _LIT(KArtistCreateTable,"CREATE TABLE Artist(" |
|
85 L"UniqueId INTEGER PRIMARY KEY," |
|
86 L"Name TEXT COLLATE NOCASE," |
|
87 L"SongCount INTEGER)"); |
|
88 |
|
89 _LIT(KComposerCreateTable,"CREATE TABLE Composer(" |
|
90 L"UniqueId INTEGER PRIMARY KEY," |
|
91 L"Name TEXT COLLATE NOCASE," |
|
92 L"SongCount INTEGER)"); |
|
93 |
|
94 _LIT(KGenreCreateTable,"CREATE TABLE Genre(" |
|
95 L"UniqueId INTEGER PRIMARY KEY," |
|
96 L"Name TEXT COLLATE NOCASE," |
|
97 L"SongCount INTEGER)"); |
|
98 |
|
99 _LIT(KPlaylistCreateTable, "CREATE TABLE Playlist(" |
|
100 L"UniqueId INTEGER PRIMARY KEY," |
|
101 L"VolumeId INTEGER," |
|
102 L"DbFlag INTEGER," |
|
103 L"Sync INTEGER," |
|
104 L"Name TEXT COLLATE NOCASE," |
|
105 L"Uri TEXT," |
|
106 L"Time TEXT)"); |
|
107 |
|
108 _LIT(KPlaylistSongsCreateTable, "CREATE TABLE PlaylistSongs(" |
|
109 L"UniqueId INTEGER PRIMARY KEY AUTOINCREMENT," |
|
110 L"SongId INTEGER," |
|
111 L"PlaylistId INTEGER," |
|
112 L"Ordinal INTEGER)"); |
|
113 |
|
114 _LIT(KPlaylistSongInfoCreateTable, "CREATE TABLE PlaylistSongInfo(" |
|
115 L"SongId INTEGER PRIMARY KEY," |
|
116 L"VolumeId INTEGER," |
|
117 L"DbFlag INTEGER," |
|
118 L"Uri TEXT," |
|
119 L"Title TEXT COLLATE NOCASE)"); |
|
120 |
|
121 |
|
122 _LIT(KBeginTransaction, "BEGIN TRANSACTION"); |
|
123 _LIT(KCommitTransaction, "COMMIT TRANSACTION"); |
|
124 |
|
125 /////////////////////////////////////////////////////////////////////////////////////// |
|
126 |
|
127 void TestEnvDestroy() |
|
128 { |
|
129 TheDbC.Close(); |
|
130 (void)RSqlDatabase::Delete(TheDbFileName); |
|
131 TheFs.Close(); |
|
132 } |
|
133 |
|
134 /////////////////////////////////////////////////////////////////////////////////////// |
|
135 /////////////////////////////////////////////////////////////////////////////////////// |
|
136 //Test macros and functions |
|
137 void Check1(TInt aValue, TInt aLine) |
|
138 { |
|
139 if(!aValue) |
|
140 { |
|
141 TestEnvDestroy(); |
|
142 TheTest.Printf(_L("*** Line %d\r\n"), aLine); |
|
143 TheTest(EFalse, aLine); |
|
144 } |
|
145 } |
|
146 void Check2(TInt aValue, TInt aExpected, TInt aLine) |
|
147 { |
|
148 if(aValue != aExpected) |
|
149 { |
|
150 TestEnvDestroy(); |
|
151 TheTest.Printf(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue); |
|
152 TheTest(EFalse, aLine); |
|
153 } |
|
154 } |
|
155 #define TEST(arg) ::Check1((arg), __LINE__) |
|
156 #define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__) |
|
157 |
|
158 |
|
159 TInt GetDuration(TUint32 aStartTicks, TUint32 aEndTicks) |
|
160 { |
|
161 static TInt freq = 0; |
|
162 if(freq == 0) |
|
163 { |
|
164 HAL::Get(HAL::EFastCounterFrequency, freq); |
|
165 } |
|
166 TInt64 diffTicks = (TInt64)aEndTicks - (TInt64)aStartTicks; |
|
167 if(diffTicks < 0) |
|
168 { |
|
169 diffTicks = KMaxTUint32 + diffTicks + 1; |
|
170 } |
|
171 const TInt KMicroSecIn1Sec = 1000000; |
|
172 |
|
173 return ((diffTicks * KMicroSecIn1Sec) / freq); |
|
174 } |
|
175 |
|
176 /////////////////////////////////////////////////////////////////////////////////////// |
|
177 |
|
178 void CreateDatabaseL(const TDesC& aDbName) |
|
179 { |
|
180 // create the database now |
|
181 RSqlSecurityPolicy securityPolicy; |
|
182 CleanupClosePushL(securityPolicy); |
|
183 |
|
184 TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass); |
|
185 securityPolicy.Create(policy); |
|
186 |
|
187 TSecurityPolicy schemaPolicy(TSecurityPolicy::EAlwaysPass); |
|
188 TSecurityPolicy readPolicy(TSecurityPolicy::EAlwaysPass); |
|
189 TSecurityPolicy writePolicy(TSecurityPolicy::EAlwaysPass); |
|
190 |
|
191 User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy)); |
|
192 User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy)); |
|
193 User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy)); |
|
194 |
|
195 TheTest.Printf(_L("Creating Database %S\n"), &aDbName); |
|
196 |
|
197 TInt err = TheDbC.Create(aDbName, securityPolicy, &KMCSqlConfig); |
|
198 |
|
199 TBuf<64> tmp; |
|
200 tmp.Copy(KMCSqlConfig); |
|
201 tmp.Append(_L("\n")); |
|
202 |
|
203 TheTest.Printf(tmp); |
|
204 |
|
205 if (KErrAlreadyExists == err) |
|
206 { |
|
207 |
|
208 // the file already exists |
|
209 // make sure we delete the file |
|
210 User::LeaveIfError(TheDbC.Delete(aDbName)); |
|
211 |
|
212 // try again |
|
213 err = TheDbC.Create(aDbName, securityPolicy, &KMCSqlConfig); |
|
214 |
|
215 } |
|
216 |
|
217 User::LeaveIfError(err); |
|
218 |
|
219 //Create tables |
|
220 User::LeaveIfError(TheDbC.Exec(KMusicCreateTable)); |
|
221 User::LeaveIfError(TheDbC.Exec(KAuxiliaryCreateTable)); |
|
222 User::LeaveIfError(TheDbC.Exec(KAlbumCreateTable)); |
|
223 User::LeaveIfError(TheDbC.Exec(KArtistCreateTable)); |
|
224 User::LeaveIfError(TheDbC.Exec(KComposerCreateTable)); |
|
225 User::LeaveIfError(TheDbC.Exec(KGenreCreateTable)); |
|
226 User::LeaveIfError(TheDbC.Exec(KPlaylistCreateTable)); |
|
227 User::LeaveIfError(TheDbC.Exec(KPlaylistSongInfoCreateTable)); |
|
228 User::LeaveIfError(TheDbC.Exec(KPlaylistSongsCreateTable)); |
|
229 |
|
230 TheDbC.Close(); |
|
231 |
|
232 CleanupStack::PopAndDestroy(&securityPolicy); |
|
233 } |
|
234 |
|
235 void TestEnvInit() |
|
236 { |
|
237 |
|
238 TInt err = TheFs.Connect(); |
|
239 TEST2(err, KErrNone); |
|
240 |
|
241 //Create database files |
|
242 TRAP(err,CreateDatabaseL(TheDbFileName)); |
|
243 TEST2(err, KErrNone); |
|
244 |
|
245 } |
|
246 |
|
247 |
|
248 /////////////////////////////////////////////////////////////////////////////////////// |
|
249 /** |
|
250 @SYMTestCaseID PDS-SQL-UT-4151 |
|
251 @SYMTestCaseDesc Measures the performance of inserting multiple records |
|
252 into the Music Player MPX database. This test is based on |
|
253 a real Music Player Harvesting use case |
|
254 @SYMTestPriority Medium |
|
255 @SYMTestActions Reads SQL transactions from a file and executes them. |
|
256 Records the time for executing each statement |
|
257 @SYMTestExpectedResults All statements should be executed without error and |
|
258 performance measurements logged |
|
259 @SYMDEF DEF142306 |
|
260 */ |
|
261 void RunTest() |
|
262 { |
|
263 //Open the file with the sql statements |
|
264 _LIT(KSqlFileName,"z:\\test\\t_sqlperformance4.sql"); |
|
265 RFile sqlFile; |
|
266 TInt err = sqlFile.Open(TheFs, KSqlFileName, EFileRead); |
|
267 TEST2(err, KErrNone); |
|
268 |
|
269 TInt fileLen = 0; |
|
270 err = sqlFile.Size(fileLen); |
|
271 TEST2(err, KErrNone); |
|
272 |
|
273 HBufC8* sqlBuf = HBufC8::New(fileLen); |
|
274 TEST(sqlBuf != NULL); |
|
275 TPtr8 sql = sqlBuf->Des(); |
|
276 err = sqlFile.Read(sql); |
|
277 |
|
278 sqlFile.Close(); |
|
279 TEST2(err, KErrNone); |
|
280 TEST2(sql.Length(), fileLen); |
|
281 |
|
282 //Open main database |
|
283 err = TheDbC.Open(TheDbFileName); |
|
284 TEST2(err, KErrNone); |
|
285 |
|
286 TheTest.Printf(_L("Beginning INSERTS...\n")); |
|
287 |
|
288 const TInt KRecordCount = 6544; |
|
289 TInt recordCount = 0; |
|
290 TInt insertCnt = 0; |
|
291 TInt updateCnt = 0; |
|
292 TInt selectCnt = 0; |
|
293 TInt trnCnt = 0; |
|
294 TInt totalTime = 0; |
|
295 |
|
296 TInt insertTrnCnt = 0; |
|
297 TInt updateTrnCnt = 0; |
|
298 TInt selectTrnCnt = 0; |
|
299 |
|
300 for(;sql.Length()>0;) |
|
301 { |
|
302 TInt eolPos = sql.Locate(TChar('\n')); |
|
303 if(eolPos < 0) |
|
304 { |
|
305 break;//No more SQL statements |
|
306 } |
|
307 TPtrC8 sqlStmt8(sql.Ptr(), eolPos - 1);//"eolPos - 1" - to cut off the '\r' character |
|
308 TPtrC8 ptr = sql.Mid(eolPos + 1);//"eolPos + 1" - first character after '\n' |
|
309 sql.Set(const_cast <TUint8*> (ptr.Ptr()), ptr.Length(), ptr.Length()); |
|
310 ++recordCount; |
|
311 |
|
312 //Convert to 16 bit query string |
|
313 TBuf<1024> query; |
|
314 query.Copy(sqlStmt8); |
|
315 |
|
316 //Execute the statement |
|
317 TInt start = User::FastCounter(); |
|
318 err = TheDbC.Exec(query); |
|
319 TInt end = User::FastCounter(); |
|
320 |
|
321 TEST(err >= 0); |
|
322 |
|
323 //Get the execution time for that statement |
|
324 TInt duration = GetDuration(start, end); |
|
325 totalTime += duration; |
|
326 |
|
327 if(query == KBeginTransaction) |
|
328 { |
|
329 TheTest.Printf(_L("Execute Statement - BEGIN: %d us\n"), duration); |
|
330 } |
|
331 |
|
332 else if(query == KCommitTransaction) |
|
333 { |
|
334 ++trnCnt; |
|
335 TheTest.Printf(_L("Execute Statement - COMMIT: %d us, Trn#%d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"), |
|
336 duration, trnCnt, insertTrnCnt, updateTrnCnt, selectTrnCnt); |
|
337 insertTrnCnt = updateTrnCnt = selectTrnCnt = 0; |
|
338 } |
|
339 |
|
340 else |
|
341 { |
|
342 TPtrC queryType(query.Ptr(), 6); |
|
343 TheTest.Printf(_L("Execute Statement - %S: %d us\n"),&queryType, duration); |
|
344 if(queryType.FindF(_L("INSERT")) >= 0) |
|
345 { |
|
346 ++insertCnt; |
|
347 ++insertTrnCnt; |
|
348 } |
|
349 else if(queryType.FindF(_L("UPDATE")) >= 0) |
|
350 { |
|
351 ++updateCnt; |
|
352 ++updateTrnCnt; |
|
353 } |
|
354 else if(queryType.FindF(_L("SELECT")) >= 0) |
|
355 { |
|
356 ++selectCnt; |
|
357 ++selectTrnCnt; |
|
358 } |
|
359 } |
|
360 } |
|
361 delete sqlBuf; |
|
362 |
|
363 TheDbC.Close(); |
|
364 |
|
365 TheTest.Printf(_L("Total time to process Songs: %d us\n"), totalTime); |
|
366 TheTest.Printf(_L("Transactions count: %d, \"INSERT\" count: %d, \"UPDATE\" count: %d, \"SELECT\" count: %d\n"), |
|
367 trnCnt, insertCnt, updateCnt, selectCnt); |
|
368 TEST2(recordCount, KRecordCount); |
|
369 } |
|
370 /////////////////////////////////////////////////////////////////////////////////// |
|
371 /////////////////////////////////////////////////////////////////////////////////// |
|
372 |
|
373 void DoTests() |
|
374 { |
|
375 TheTest.Start(_L("@SYMTestCaseID:PDS-SQL-UT-4151; SQL Music Player Db Performance Test")); |
|
376 |
|
377 RunTest(); |
|
378 } |
|
379 |
|
380 //Usage: "t_sqlperformance4 [<drive letter>:]" |
|
381 |
|
382 TInt E32Main() |
|
383 { |
|
384 TheTest.Title(); |
|
385 |
|
386 CTrapCleanup* tc = CTrapCleanup::New(); |
|
387 TheTest(tc != NULL); |
|
388 |
|
389 __UHEAP_MARK; |
|
390 |
|
391 User::CommandLine(TheCmd); |
|
392 TheCmd.TrimAll(); |
|
393 if(TheCmd.Length() > 0) |
|
394 { |
|
395 TheDriveName.Copy(TheCmd); |
|
396 } |
|
397 TheParse.Set(TheDriveName, &KCDriveDatabase, 0); |
|
398 const TDesC& dbFilePath = TheParse.FullName(); |
|
399 TheDbFileName.Copy(dbFilePath); |
|
400 TheTest.Printf(_L("==Database file name: %S\r\n"), &TheDbFileName); |
|
401 |
|
402 TestEnvInit(); |
|
403 |
|
404 DoTests(); |
|
405 |
|
406 TestEnvDestroy(); |
|
407 |
|
408 __UHEAP_MARKEND; |
|
409 |
|
410 TheTest.End(); |
|
411 TheTest.Close(); |
|
412 |
|
413 delete tc; |
|
414 |
|
415 User::Heap().Check(); |
|
416 return KErrNone; |
|
417 } |