|
1 // Copyright (c) 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: MDS harvesting performance test |
|
14 // |
|
15 #include <e32test.h> |
|
16 #include <e32math.h> |
|
17 #include <bautils.h> |
|
18 #include <hal.h> |
|
19 #include <sqldb.h> |
|
20 |
|
21 RTest TheTest(_L("t_sqlperformance3 test")); |
|
22 RSqlDatabase TheDb; |
|
23 |
|
24 _LIT(KDbName, "c:\\test\\t_sqlperformance3.db"); |
|
25 |
|
26 TFileName TheDbFileName; |
|
27 TBuf<256> TheCmd; |
|
28 TDriveName TheDriveName; |
|
29 TParse TheParse; |
|
30 |
|
31 TInt TheFastCounterFreq = 0; |
|
32 |
|
33 const TInt KItemCnt = 1000; |
|
34 const TInt KItemBlockSize = 100; |
|
35 |
|
36 TInt TheIterations[100]; |
|
37 TInt TheIterationsCount = -1; |
|
38 |
|
39 void CalcIterationsCount() |
|
40 { |
|
41 TheIterationsCount = KItemCnt / KItemBlockSize; |
|
42 TInt r = KItemCnt % KItemBlockSize; |
|
43 if(r > 0) |
|
44 { |
|
45 ++TheIterationsCount; |
|
46 } |
|
47 else |
|
48 { |
|
49 r = KItemBlockSize; |
|
50 } |
|
51 for(TInt i=0;i<(TheIterationsCount-1);++i) |
|
52 { |
|
53 TheIterations[i] = KItemBlockSize; |
|
54 } |
|
55 TheIterations[TheIterationsCount - 1] = r; |
|
56 } |
|
57 |
|
58 //////////////////////////////////////////////////////////////////////////////////////////////////// |
|
59 |
|
60 void TestEnvDestroy() |
|
61 { |
|
62 TheDb.Close(); |
|
63 (void)RSqlDatabase::Delete(TheDbFileName); |
|
64 } |
|
65 |
|
66 /////////////////////////////////////////////////////////////////////////////////////// |
|
67 /////////////////////////////////////////////////////////////////////////////////////// |
|
68 //Test macros and functions |
|
69 void Check1(TInt aValue, TInt aLine) |
|
70 { |
|
71 if(!aValue) |
|
72 { |
|
73 TestEnvDestroy(); |
|
74 TheTest.Printf(_L("*** Line %d\r\n"), aLine); |
|
75 TheTest(EFalse, aLine); |
|
76 } |
|
77 } |
|
78 void Check2(TInt aValue, TInt aExpected, TInt aLine) |
|
79 { |
|
80 if(aValue != aExpected) |
|
81 { |
|
82 TSqlRetCodeClass cl = SqlRetCodeClass(aValue); |
|
83 if(cl == ESqlDbError) |
|
84 { |
|
85 TPtrC errmsg = TheDb.LastErrorMessage(); |
|
86 TheTest.Printf(_L("*** SQLite err=\"%S\"\r\n"), &errmsg); |
|
87 } |
|
88 TestEnvDestroy(); |
|
89 TheTest.Printf(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue); |
|
90 TheTest(EFalse, aLine); |
|
91 } |
|
92 } |
|
93 #define TEST(arg) ::Check1((arg), __LINE__) |
|
94 #define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__) |
|
95 |
|
96 /////////////////////////////////////////////////////////////////////////////////////// |
|
97 |
|
98 void TestEnvInit() |
|
99 { |
|
100 RFs fs; |
|
101 TInt err = fs.Connect(); |
|
102 TEST2(err, KErrNone); |
|
103 err = fs.MkDirAll(TheDbFileName); |
|
104 TEST(err == KErrNone || err == KErrAlreadyExists); |
|
105 fs.Close(); |
|
106 } |
|
107 |
|
108 TInt TimeDiffUs(TUint32 aStartTicks, TUint32 aEndTicks) |
|
109 { |
|
110 if(TheFastCounterFreq == 0) |
|
111 { |
|
112 TEST2(HAL::Get(HAL::EFastCounterFrequency, TheFastCounterFreq), KErrNone); |
|
113 } |
|
114 TInt64 diffTicks = (TInt64)aEndTicks - (TInt64)aStartTicks; |
|
115 if(diffTicks < 0) |
|
116 { |
|
117 diffTicks = KMaxTUint32 + diffTicks + 1; |
|
118 } |
|
119 const TInt KMicroSecIn1Sec = 1000000; |
|
120 TInt us = (diffTicks * KMicroSecIn1Sec) / TheFastCounterFreq; |
|
121 return us; |
|
122 } |
|
123 |
|
124 void PrintTime(const TDesC& aFmt, TUint32 aStartTicks, TUint32 aEndTicks) |
|
125 { |
|
126 TInt us = TimeDiffUs(aStartTicks, aEndTicks); |
|
127 TheTest.Printf(aFmt, us); |
|
128 } |
|
129 |
|
130 //============================================================================= |
|
131 TInt TheCreateDbCreateConnTime = 0; |
|
132 TInt TheCreateDbOpenConnTime = 0; |
|
133 TInt TheLastItemIdTime = 0; |
|
134 TInt TheTableExistsTime = 0; |
|
135 TInt TheMdeTablesCreationTime = 0; |
|
136 TInt TheLastInsertedRowIdTime = 0; |
|
137 TInt TheCreateTables1Time = 0; |
|
138 TInt TheCreateTables2Time = 0; |
|
139 TInt TheCreateTables3Time = 0; |
|
140 TInt TheTransactionTime = 0; |
|
141 TInt TheDbCreateTime = 0; |
|
142 TInt TheCreateTriggersTime = 0; |
|
143 TInt TheInsertEventRelationTime = 0; |
|
144 TInt TheDeleteObjPropertyTime = 0; |
|
145 TInt TheInsertCol2PropTime = 0; |
|
146 TInt TheHarvestTime = 0; |
|
147 TInt TheHarvestSelectTime = 0; |
|
148 TInt TheHarvestInsertObjImageTime = 0; |
|
149 TInt TheHarvestSelect2Time = 0; |
|
150 TInt TheHarvestInsertEventTime = 0; |
|
151 TInt TheHarvestUpdateObjImageTime = 0; |
|
152 TInt TheHarvestSelect3Time = 0; |
|
153 TInt TheHarvestSelect4Time = 0; |
|
154 TInt TheHarvestUpdateObjTime = 0; |
|
155 //============================================================================= |
|
156 |
|
157 void DoCreateDbFile() |
|
158 { |
|
159 _LIT8(KConfig, "page_size=4096;cache_size=10000;"); |
|
160 TUint32 fc1 = User::FastCounter(); |
|
161 TInt err = TheDb.Create(TheDbFileName, &KConfig); |
|
162 if(err != KErrNone) |
|
163 { |
|
164 if(err == KErrAlreadyExists) |
|
165 { |
|
166 err = TheDb.Open(TheDbFileName, &KConfig); |
|
167 if(err == KErrNone) |
|
168 { |
|
169 TUint32 fc2 = User::FastCounter(); |
|
170 TheCreateDbCreateConnTime += TimeDiffUs(fc1, fc2); |
|
171 } |
|
172 } |
|
173 } |
|
174 else |
|
175 { |
|
176 TUint32 fc2 = User::FastCounter(); |
|
177 TheCreateDbOpenConnTime += TimeDiffUs(fc1, fc2); |
|
178 } |
|
179 TEST2(err, KErrNone); |
|
180 } |
|
181 |
|
182 void DoGetlastItemId() |
|
183 { |
|
184 _LIT(KGetLastMaxId, "SELECT max(seq) FROM SQLITE_SEQUENCE WHERE name!='symbian_security';"); |
|
185 TUint32 fc1 = User::FastCounter(); |
|
186 RSqlStatement stmt; |
|
187 TInt err = stmt.Prepare(TheDb, KGetLastMaxId); |
|
188 if(err == KErrNone) |
|
189 { |
|
190 (void)stmt.Next(); |
|
191 } |
|
192 stmt.Close(); |
|
193 TUint32 fc2 = User::FastCounter(); |
|
194 TheLastItemIdTime += TimeDiffUs(fc1, fc2); |
|
195 } |
|
196 |
|
197 TBool MdePreferencesCheck() |
|
198 { |
|
199 _LIT(KCheck, "SELECT COUNT(*) FROM MdE_Preferences;"); |
|
200 TUint32 fc1 = User::FastCounter(); |
|
201 RSqlStatement stmt; |
|
202 TInt err = stmt.Prepare(TheDb, KCheck); |
|
203 if(err == KErrNone) |
|
204 { |
|
205 err = stmt.Next(); |
|
206 } |
|
207 stmt.Close(); |
|
208 TUint32 fc2 = User::FastCounter(); |
|
209 TheTableExistsTime += TimeDiffUs(fc1, fc2); |
|
210 return err == KSqlAtRow; |
|
211 } |
|
212 |
|
213 void CreateMdeTables() |
|
214 { |
|
215 _LIT(KSql1, "CREATE TABLE MdE_Preferences(Key TEXT,Value NONE,ExtraValue LARGEINT,UNIQUE(Key,Value))"); |
|
216 _LIT(KSql2, "CREATE TABLE MdS_Medias(MediaId INTEGER PRIMARY KEY,Drive INTEGER,PresentState INTEGER,Time LARGEINT)"); |
|
217 _LIT(KSql3, "INSERT INTO MdE_Preferences(Key, Value, ExtraValue) VALUES(?, ?, ?)"); |
|
218 |
|
219 TUint32 fc1 = User::FastCounter(); |
|
220 |
|
221 TInt rc = TheDb.Exec(KSql1); |
|
222 TEST(rc >= 0); |
|
223 rc = TheDb.Exec(KSql2); |
|
224 TEST(rc >= 0); |
|
225 |
|
226 RSqlStatement stmt; |
|
227 rc = stmt.Prepare(TheDb, KSql3); |
|
228 TEST2(rc, KErrNone); |
|
229 rc = stmt.BindInt(0, 1); |
|
230 TEST2(rc, KErrNone); |
|
231 rc = stmt.BindInt(1, 2); |
|
232 TEST2(rc, KErrNone); |
|
233 rc = stmt.BindInt(2, 3); |
|
234 TEST2(rc, KErrNone); |
|
235 rc = stmt.Exec(); |
|
236 TEST2(rc, 1); |
|
237 stmt.Close(); |
|
238 |
|
239 TUint32 fc2 = User::FastCounter(); |
|
240 TheMdeTablesCreationTime += TimeDiffUs(fc1, fc2); |
|
241 } |
|
242 |
|
243 TInt64 LastInsertedRowId() |
|
244 { |
|
245 _LIT(KSql, "SELECT last_insert_rowid();"); |
|
246 TUint32 fc1 = User::FastCounter(); |
|
247 RSqlStatement stmt; |
|
248 TInt err = stmt.Prepare(TheDb, KSql); |
|
249 TEST2(err, KErrNone); |
|
250 err = stmt.Next(); |
|
251 TEST2(err, KSqlAtRow); |
|
252 TInt64 rowid = stmt.ColumnInt64(0); |
|
253 stmt.Close(); |
|
254 TUint32 fc2 = User::FastCounter(); |
|
255 TheLastInsertedRowIdTime += TimeDiffUs(fc1, fc2); |
|
256 return rowid; |
|
257 } |
|
258 |
|
259 void DoCreateTables1() |
|
260 { |
|
261 _LIT(KSql1, "CREATE TABLE IF NOT EXISTS NamespaceDef(NamespaceDefId INTEGER PRIMARY KEY,ReadOnly INTEGER,VendorId INTEGER,Name TEXT)"); |
|
262 _LIT(KSql2, "CREATE TABLE IF NOT EXISTS ObjectDef(ObjectDefId INTEGER PRIMARY KEY,NamespaceDefId INTEGER NOT NULL,ParentDefId INTEGER NOT NULL,Flags INTEGER,Name TEXT)"); |
|
263 _LIT(KSql3, "CREATE TABLE IF NOT EXISTS PropertyDef(PropertyDefId INTEGER PRIMARY KEY,ObjectDefId INTEGER NOT NULL,Flags INTEGER,Type INTEGER,MinValue NUMERIC,MaxValue NUMERIC,Name TEXT)"); |
|
264 _LIT(KSql4, "CREATE TABLE IF NOT EXISTS RelationDef(RelationDefId INTEGER PRIMARY KEY,NamespaceDefId INTEGER NOT NULL,Name TEXT)"); |
|
265 _LIT(KSql5, "CREATE TABLE IF NOT EXISTS EventDef(EventDefId INTEGER PRIMARY KEY,NamespaceDefId INTEGER NOT NULL,Priority INTEGER,Name TEXT)"); |
|
266 |
|
267 TUint32 fc1 = User::FastCounter(); |
|
268 |
|
269 TInt rc = TheDb.Exec(KSql1); |
|
270 TEST(rc >= 0); |
|
271 rc = TheDb.Exec(KSql2); |
|
272 TEST(rc >= 0); |
|
273 rc = TheDb.Exec(KSql3); |
|
274 TEST(rc >= 0); |
|
275 rc = TheDb.Exec(KSql4); |
|
276 TEST(rc >= 0); |
|
277 rc = TheDb.Exec(KSql5); |
|
278 TEST(rc >= 0); |
|
279 |
|
280 _LIT(KSql6, "INSERT INTO NamespaceDef(ReadOnly,VendorId,Name) Values(?,?,?);"); |
|
281 RSqlStatement stmt; |
|
282 rc = stmt.Prepare(TheDb, KSql6); |
|
283 TEST2(rc, KErrNone); |
|
284 rc = stmt.BindInt(0, 1); |
|
285 TEST2(rc, KErrNone); |
|
286 rc = stmt.BindInt(1, 2); |
|
287 TEST2(rc, KErrNone); |
|
288 rc = stmt.BindText(2, _L("Nokia")); |
|
289 TEST2(rc, KErrNone); |
|
290 rc = stmt.Exec(); |
|
291 TEST2(rc, 1); |
|
292 stmt.Close(); |
|
293 |
|
294 TUint32 fc2 = User::FastCounter(); |
|
295 TheCreateTables1Time += TimeDiffUs(fc1, fc2); |
|
296 } |
|
297 |
|
298 void BeginTransaction() |
|
299 { |
|
300 TInt err = TheDb.Exec(_L("BEGIN")); |
|
301 TEST(err >= 0); |
|
302 } |
|
303 |
|
304 void CommitTransaction() |
|
305 { |
|
306 TInt err = TheDb.Exec(_L("COMMIT")); |
|
307 TEST(err >= 0); |
|
308 } |
|
309 |
|
310 void DoCreateTables2() |
|
311 { |
|
312 _LIT(KSql1, "CREATE TABLE IF NOT EXISTS Object1(ObjectId INTEGER PRIMARY KEY AUTOINCREMENT,ObjectDefId INTEGER NOT NULL,Flags INTEGER,MediaId LARGEINT,UsageCount LARGEINT DEFAULT 0,GuidHigh LARGEINT,GuidLow LARGEINT,URI TEXT NOT NULL COLLATE NOCASE,Origin INTEGER,Size LARGEINT,TimeOffset INTEGER,CreationDate LARGEINT,LastModifiedDate LARGEINT,ItemType TEXT,Title TEXT,UNIQUE(GuidHigh,GuidLow),UNIQUE(MediaId,URI))"); |
|
313 _LIT(KSql2, "CREATE TABLE IF NOT EXISTS Relations1(RelationId INTEGER PRIMARY KEY AUTOINCREMENT,Flags INTEGER,RelationDefId INTEGER NOT NULL,LeftObjectId INTEGER NOT NULL,RightObjectId INTEGER NOT NULL,Parameter INTEGER,GuidHigh LARGEINT,GuidLow LARGEINT,LastModifiedDate LARGEINT)"); |
|
314 _LIT(KSql3, "CREATE TRIGGER DeleteRelations1 BEFORE DELETE ON Object1 BEGIN DELETE FROM Relations1 WHERE LeftObjectId=OLD.ObjectId OR RightObjectId=OLD.ObjectId; END"); |
|
315 _LIT(KSql4, "CREATE TABLE IF NOT EXISTS Event1(EventId INTEGER PRIMARY KEY AUTOINCREMENT,ObjectId INTEGER NOT NULL, EventDefId INTEGER NOT NULL, Timestamp INTEGER NOT NULL, Source TEXT, Participant TEXT)"); |
|
316 _LIT(KSql5, "CREATE TRIGGER DeleteEvent1 BEFORE DELETE ON Object1 BEGIN DELETE FROM Event1 WHERE ObjectId=OLD.ObjectId; END"); |
|
317 _LIT(KSql6, "CREATE TABLE IF NOT EXISTS TextSearch1(WordId INTEGER NOT NULL,ObjectId INTEGER NOT NULL,Position INTEGER)"); |
|
318 _LIT(KSql7, "CREATE TRIGGER DeleteTextSearch1 BEFORE DELETE ON Object1 BEGIN DELETE FROM TextSearch1 WHERE ObjectId=OLD.ObjectId; END"); |
|
319 _LIT(KSql8, "CREATE INDEX FreetextWordIdIndex1 ON TextSearch1(WordId)"); |
|
320 _LIT(KSql9, "CREATE INDEX FreetextObjectIdIndex1 ON TextSearch1(ObjectId)"); |
|
321 _LIT(KSql10, "CREATE TABLE IF NOT EXISTS TextSearchDictionary1(WordId INTEGER PRIMARY KEY,Word TEXT NOT NULL)"); |
|
322 _LIT(KSql11, "CREATE TRIGGER InsertRelations1 INSERT ON Relations1 BEGIN UPDATE Object1 SET UsageCount = UsageCount + 1 WHERE ObjectId=NEW.LeftObjectId OR ObjectId=NEW.RightObjectId; END"); |
|
323 _LIT(KSql12, "CREATE TRIGGER UpdateRelations1 UPDATE OF LeftObjectId, RightObjectId ON Relations1 WHEN OLD.Flags=NEW.Flags BEGIN UPDATE Object1 SET UsageCount = UsageCount - 1 WHERE ObjectId=OLD.LeftObjectId OR ObjectId=OLD.RightObjectId; UPDATE Object1 SET UsageCount = UsageCount + 1 WHERE ObjectId=NEW.LeftObjectId OR ObjectId=NEW.RightObjectId; END"); |
|
324 _LIT(KSql13, "CREATE TRIGGER UpdateNotRemovedRelations1 UPDATE OF Flags ON Relations1 WHEN OLD.Flags!=NEW.Flags AND NOT NEW.Flags&3 BEGIN UPDATE Object1 SET UsageCount = UsageCount + 1 WHERE ObjectId=NEW.LeftObjectId OR ObjectId=NEW.RightObjectId; END"); |
|
325 _LIT(KSql14, "CREATE TRIGGER UpdateRemovedRelations1 UPDATE OF Flags ON Relations1 WHEN OLD.Flags!=NEW.Flags AND NEW.Flags&3 AND NOT OLD.Flags&3 BEGIN UPDATE Object1 SET UsageCount = UsageCount - 1 WHERE ObjectId=OLD.LeftObjectId OR ObjectId=OLD.RightObjectId; END"); |
|
326 _LIT(KSql15, "CREATE INDEX RelationsLeftObjectIdIndex1 ON Relations1(LeftObjectId)"); |
|
327 _LIT(KSql16, "CREATE INDEX RelationsRightObjectIdIndex1 ON Relations1(RightObjectId)"); |
|
328 _LIT(KSql17, "CREATE INDEX ObjectDefIndex1 ON Object1(ObjectDefId)"); |
|
329 _LIT(KSql18, "CREATE TABLE IF NOT EXISTS Location1(ObjectId INTEGER NOT NULL,CellID LARGEINT,LocationAreaCode LARGEINT,Speed REAL,Direction REAL,Quality REAL,Latitude REAL,Longitude REAL,Altitude REAL,CountryCodeStr TEXT,NetworkCodeStr TEXT,Satellites TEXT,Country TEXT,CellName TEXT, UNIQUE(ObjectId))"); |
|
330 _LIT(KSql19, "CREATE TABLE IF NOT EXISTS Calendar1(ObjectId INTEGER NOT NULL,CalendarType INTEGER,LocalUid LARGEINT,StartTime LARGEINT,EndTime LARGEINT,Location TEXT,vCalendar TEXT, UNIQUE(ObjectId))"); |
|
331 _LIT(KSql20, "CREATE TABLE IF NOT EXISTS Contact1(ObjectId INTEGER NOT NULL,Number TEXT,EmailAddress TEXT,Company TEXT,JobTitle TEXT,Address TEXT,ContactURL TEXT,vCard TEXT, UNIQUE(ObjectId))"); |
|
332 _LIT(KSql21, "CREATE TABLE IF NOT EXISTS Message1(ObjectId INTEGER NOT NULL,Received INTEGER,ToWhom TEXT,Sender TEXT, UNIQUE(ObjectId))"); |
|
333 _LIT(KSql22, "CREATE TABLE IF NOT EXISTS Album1(ObjectId INTEGER NOT NULL,Ordered INTEGER,Type INTEGER, UNIQUE(ObjectId))"); |
|
334 _LIT(KSql23, "CREATE TABLE IF NOT EXISTS Tag1(ObjectId INTEGER NOT NULL, UNIQUE(ObjectId))"); |
|
335 _LIT(KSql24, "CREATE TABLE IF NOT EXISTS MediaObject1(ObjectId INTEGER NOT NULL,Protected INTEGER,DRM INTEGER,ThumbnailPresent INTEGER,Resized INTEGER,Sample INTEGER,Rating INTEGER,Privacy INTEGER,ResolutionUnit INTEGER,Bitrate INTEGER,Width INTEGER,Height INTEGER,Preinstalled INTEGER,AccessCount LARGEINT,DownloadID LARGEINT,Duration REAL,ReleaseDate LARGEINT,CaptureDate LARGEINT,Artist TEXT,Author TEXT,Copyright TEXT,Legal TEXT,Description TEXT,Comment TEXT,Genre TEXT, UNIQUE(ObjectId))"); |
|
336 _LIT(KSql25, "CREATE TABLE IF NOT EXISTS Video1(ObjectId INTEGER NOT NULL,Protected INTEGER,DRM INTEGER,ThumbnailPresent INTEGER,Resized INTEGER,Sample INTEGER,Rating INTEGER,Privacy INTEGER,ResolutionUnit INTEGER,Bitrate INTEGER,Width INTEGER,Height INTEGER,Preinstalled INTEGER,AccessCount LARGEINT,DownloadID LARGEINT,Duration REAL,ReleaseDate LARGEINT,CaptureDate LARGEINT,AgeProfile INTEGER,AudioFourCC LARGEINT,RecordingFlags LARGEINT,LastPlayPosition REAL,Framerate REAL,Artist TEXT,Author TEXT,Copyright TEXT,Legal TEXT,Description TEXT,Comment TEXT,Genre TEXT,AudioLanguage TEXT, UNIQUE(ObjectId))"); |
|
337 _LIT(KSql26, "CREATE TABLE IF NOT EXISTS Image1(ObjectId INTEGER NOT NULL,Protected INTEGER,DRM INTEGER,ThumbnailPresent INTEGER,Resized INTEGER,Sample INTEGER,Rating INTEGER,Privacy INTEGER,ResolutionUnit INTEGER,Bitrate INTEGER,Width INTEGER,Height INTEGER,Preinstalled INTEGER,AccessCount LARGEINT,DownloadID LARGEINT,Duration REAL,ReleaseDate LARGEINT,CaptureDate LARGEINT,FocalPlaneResolutionUnit INTEGER,ExposureProgram INTEGER,FocalLengthIn35mmFilm INTEGER,ISOSpeedRatings INTEGER,MeteringMode INTEGER,WhiteBalance INTEGER,Flash INTEGER,ColourSpace INTEGER,Orientation INTEGER,SamplesPerPixel INTEGER,BitsPerSample INTEGER,YCbCrPositioning INTEGER,ThumbCompression INTEGER,ThumbResolutionUnit INTEGER,ThumbOrientation INTEGER,FrameCount INTEGER,PixelXDimension LARGEINT,PixelYDimension LARGEINT,ExifVersion LARGEINT,ComponentsConfiguration LARGEINT,FlashPixVersion LARGEINT,ThumbXResolution LARGEINT,ThumbYResolution LARGEINT,FocalPlaneXResolution REAL,FocalPlaneYResolution REAL,ExposureTime REAL,ExposureBiasValue REAL,FNumber REAL,FocalLength REAL,ApertureValue REAL,MaxAperture REAL,ShutterSpeedValue REAL,XResolution REAL,YResolution REAL,DateTime LARGEINT,DateTimeDigitized LARGEINT,DateTimeOriginal LARGEINT,Artist TEXT,Author TEXT,Copyright TEXT,Legal TEXT,Description TEXT,Comment TEXT,Genre TEXT,Model TEXT,Make TEXT,RelatedSoundFile TEXT, UNIQUE(ObjectId))"); |
|
338 _LIT(KSql27, "CREATE TABLE IF NOT EXISTS Audio1(ObjectId INTEGER NOT NULL,Protected INTEGER,DRM INTEGER,ThumbnailPresent INTEGER,Resized INTEGER,Sample INTEGER,Rating INTEGER,Privacy INTEGER,ResolutionUnit INTEGER,Bitrate INTEGER,Width INTEGER,Height INTEGER,Preinstalled INTEGER,AccessCount LARGEINT,DownloadID LARGEINT,Duration REAL,ReleaseDate LARGEINT,CaptureDate LARGEINT,Track INTEGER,SamplingFrequency REAL,Artist TEXT,Author TEXT,Copyright TEXT,Legal TEXT,Description TEXT,Comment TEXT,Genre TEXT,Album TEXT,Composer TEXT,OriginalArtist TEXT,AlbumArtist TEXT, UNIQUE(ObjectId))"); |
|
339 _LIT(KSql28, "CREATE TABLE IF NOT EXISTS TrackLog1(ObjectId INTEGER NOT NULL,Length LARGEINT,StartTime LARGEINT,StopTime LARGEINT, UNIQUE(ObjectId))"); |
|
340 |
|
341 const TPtrC KSql[] = |
|
342 { |
|
343 KSql1(), KSql2(), KSql3(), KSql4(), KSql5(), KSql6(), KSql7(), KSql8(), KSql9(), KSql10(), |
|
344 KSql11(), KSql12(), KSql13(), KSql14(), KSql15(), KSql16(), KSql17(), KSql18(), KSql19(), KSql20(), |
|
345 KSql21(), KSql22(), KSql23(), KSql24(), KSql25(), KSql26(), KSql27(), KSql28() |
|
346 }; |
|
347 |
|
348 TUint32 fc1 = User::FastCounter(); |
|
349 for(TInt i=0;i<(sizeof(KSql)/sizeof(KSql[0]));++i) |
|
350 { |
|
351 TInt err = TheDb.Exec(KSql[i]); |
|
352 TEST(err >= 0); |
|
353 } |
|
354 TUint32 fc2 = User::FastCounter(); |
|
355 TheCreateTables2Time += TimeDiffUs(fc1, fc2); |
|
356 } |
|
357 |
|
358 void DoCreateTables3() |
|
359 { |
|
360 const TInt KInsObjDefRecCnt = 12; |
|
361 const TInt KInsPropDefRecCnt[KInsObjDefRecCnt] = {7, 11, 6, 7, 3, 2, 0, 24, 6, 40, 6, 3}; |
|
362 TUint32 fc1 = User::FastCounter(); |
|
363 for(TInt i=0;i<KInsObjDefRecCnt;++i) |
|
364 { |
|
365 TBuf<10> objDefName; |
|
366 RSqlStatement stmt1; |
|
367 TInt err = stmt1.Prepare(TheDb, _L("INSERT INTO ObjectDef(NamespaceDefId, ParentDefId, Flags, Name) Values(?,?,?,?)")); |
|
368 TEST2(err, KErrNone); |
|
369 err = stmt1.BindInt(0, 1); |
|
370 TEST2(err, KErrNone); |
|
371 err = stmt1.BindInt(1, 2); |
|
372 TEST2(err, KErrNone); |
|
373 err = stmt1.BindInt(2, 3); |
|
374 TEST2(err, KErrNone); |
|
375 objDefName.Copy(_L("ObjDef")); |
|
376 objDefName.AppendNum(i + 1); |
|
377 err = stmt1.BindText(3, objDefName); |
|
378 TEST2(err, KErrNone); |
|
379 err = stmt1.Exec(); |
|
380 TEST2(err, 1); |
|
381 stmt1.Close(); |
|
382 TInt64 objdefid = LastInsertedRowId(); |
|
383 for(TInt j=0;j<KInsPropDefRecCnt[i];++j) |
|
384 { |
|
385 TBuf<10> propDefName; |
|
386 RSqlStatement stmt2; |
|
387 err = stmt2.Prepare(TheDb, _L("INSERT INTO PropertyDef(ObjectDefId,Flags,Type,MinValue,MaxValue,Name) Values(?,?,?,?,?,?);")); |
|
388 TEST2(err, KErrNone); |
|
389 err = stmt2.BindInt64(0, objdefid); |
|
390 TEST2(err, KErrNone); |
|
391 err = stmt2.BindInt(1, 2); |
|
392 TEST2(err, KErrNone); |
|
393 err = stmt2.BindInt(2, 3); |
|
394 TEST2(err, KErrNone); |
|
395 err = stmt2.BindInt(3, 100); |
|
396 TEST2(err, KErrNone); |
|
397 err = stmt2.BindInt(4, 200); |
|
398 TEST2(err, KErrNone); |
|
399 propDefName.Copy(_L("PropDef")); |
|
400 propDefName.AppendNum(j + 1); |
|
401 err = stmt2.BindText(5, propDefName); |
|
402 TEST2(err, KErrNone); |
|
403 err = stmt2.Exec(); |
|
404 TEST2(err, 1); |
|
405 stmt2.Close(); |
|
406 LastInsertedRowId(); |
|
407 } |
|
408 } |
|
409 TUint32 fc2 = User::FastCounter(); |
|
410 TheCreateTables3Time += TimeDiffUs(fc1, fc2); |
|
411 } |
|
412 |
|
413 void DoCreateTriggers() |
|
414 { |
|
415 _LIT(KSql1, "CREATE TRIGGER DeleteLocation1 DELETE ON Object1 BEGIN DELETE FROM Location1 WHERE ObjectId=OLD.ObjectId; END"); |
|
416 _LIT(KSql2, "CREATE TRIGGER DeleteCalendar1 DELETE ON Object1 BEGIN DELETE FROM Calendar1 WHERE ObjectId=OLD.ObjectId; END"); |
|
417 _LIT(KSql3, "CREATE TRIGGER DeleteContact1 DELETE ON Object1 BEGIN DELETE FROM Contact1 WHERE ObjectId=OLD.ObjectId; END"); |
|
418 _LIT(KSql4, "CREATE TRIGGER DeleteMessage1 DELETE ON Object1 BEGIN DELETE FROM Message1 WHERE ObjectId=OLD.ObjectId; END"); |
|
419 _LIT(KSql5, "CREATE TRIGGER DeleteAlbum1 DELETE ON Object1 BEGIN DELETE FROM Album1 WHERE ObjectId=OLD.ObjectId; END"); |
|
420 _LIT(KSql6, "CREATE TRIGGER DeleteTag1 DELETE ON Object1 BEGIN DELETE FROM Tag1 WHERE ObjectId=OLD.ObjectId; END"); |
|
421 _LIT(KSql7, "CREATE TRIGGER DeleteMediaObject1 DELETE ON Object1 BEGIN DELETE FROM MediaObject1 WHERE ObjectId=OLD.ObjectId; END"); |
|
422 _LIT(KSql8, "CREATE TRIGGER DeleteVideo1 DELETE ON Object1 BEGIN DELETE FROM Video1 WHERE ObjectId=OLD.ObjectId; END"); |
|
423 _LIT(KSql9, "CREATE TRIGGER DeleteImage1 DELETE ON Object1 BEGIN DELETE FROM Image1 WHERE ObjectId=OLD.ObjectId; END"); |
|
424 _LIT(KSql10, "CREATE TRIGGER DeleteAudio1 DELETE ON Object1 BEGIN DELETE FROM Audio1 WHERE ObjectId=OLD.ObjectId; END"); |
|
425 _LIT(KSql11, "CREATE TRIGGER DeleteTrackLog1 DELETE ON Object1 BEGIN DELETE FROM TrackLog1 WHERE ObjectId=OLD.ObjectId; END"); |
|
426 |
|
427 const TPtrC KSql[] = |
|
428 { |
|
429 KSql1(), KSql2(), KSql3(), KSql4(), KSql5(), KSql6(), KSql7(), KSql8(), KSql9(), KSql10(), KSql11() |
|
430 }; |
|
431 |
|
432 TUint32 fc1 = User::FastCounter(); |
|
433 for(TInt i=0;i<(sizeof(KSql)/sizeof(KSql[0]));++i) |
|
434 { |
|
435 TInt err = TheDb.Exec(KSql[i]); |
|
436 TEST(err >= 0); |
|
437 } |
|
438 TUint32 fc2 = User::FastCounter(); |
|
439 TheCreateTriggersTime += TimeDiffUs(fc1, fc2); |
|
440 } |
|
441 |
|
442 void DoInsertEventRelationDefs() |
|
443 { |
|
444 _LIT(KSql1, "INSERT INTO EventDef (NamespaceDefId, Priority, Name ) Values(?, ?, ?);"); |
|
445 const TInt KSql1Cnt = 7; |
|
446 _LIT(KSql2, "INSERT INTO RelationDef (NamespaceDefId,Name) Values(?,?);"); |
|
447 const TInt KSql2Cnt = 8; |
|
448 |
|
449 TUint32 fc1 = User::FastCounter(); |
|
450 |
|
451 for(TInt i=0;i<KSql1Cnt;++i) |
|
452 { |
|
453 TBuf<10> eventDefName; |
|
454 RSqlStatement stmt; |
|
455 TInt err = stmt.Prepare(TheDb, KSql1); |
|
456 TEST2(err, KErrNone); |
|
457 err = stmt.BindInt(0, 1); |
|
458 TEST2(err, KErrNone); |
|
459 err = stmt.BindInt(1, 2); |
|
460 TEST2(err, KErrNone); |
|
461 eventDefName.Copy(_L("EvtDef")); |
|
462 eventDefName.AppendNum(i + 1); |
|
463 err = stmt.BindText(2, eventDefName); |
|
464 TEST2(err, KErrNone); |
|
465 err = stmt.Exec(); |
|
466 TEST2(err, 1); |
|
467 stmt.Close(); |
|
468 LastInsertedRowId(); |
|
469 } |
|
470 |
|
471 for(TInt i=0;i<KSql2Cnt;++i) |
|
472 { |
|
473 TBuf<10> relDefName; |
|
474 RSqlStatement stmt; |
|
475 TInt err = stmt.Prepare(TheDb, KSql2); |
|
476 TEST2(err, KErrNone); |
|
477 err = stmt.BindInt(0, 1); |
|
478 TEST2(err, KErrNone); |
|
479 relDefName.Copy(_L("RelDef")); |
|
480 relDefName.AppendNum(i + 1); |
|
481 err = stmt.BindText(1, relDefName); |
|
482 TEST2(err, KErrNone); |
|
483 err = stmt.Exec(); |
|
484 TEST2(err, 1); |
|
485 stmt.Close(); |
|
486 LastInsertedRowId(); |
|
487 } |
|
488 |
|
489 TUint32 fc2 = User::FastCounter(); |
|
490 TheInsertEventRelationTime += TimeDiffUs(fc1, fc2); |
|
491 } |
|
492 |
|
493 void DoDeleteObjPropertyDef() |
|
494 { |
|
495 _LIT(KSql1, "DELETE FROM ObjectDef WHERE ObjectDefId=?;"); |
|
496 _LIT(KSql2, "DELETE FROM PropertyDef WHERE ObjectDefId=?;"); |
|
497 |
|
498 TUint32 fc1 = User::FastCounter(); |
|
499 |
|
500 RSqlStatement stmt1; |
|
501 TInt err = stmt1.Prepare(TheDb, KSql1); |
|
502 TEST2(err, KErrNone); |
|
503 err = stmt1.BindInt(0, 0); |
|
504 TEST2(err, KErrNone); |
|
505 err = stmt1.Exec(); |
|
506 TEST(err >= 0); |
|
507 stmt1.Close(); |
|
508 |
|
509 RSqlStatement stmt2; |
|
510 err = stmt2.Prepare(TheDb, KSql2); |
|
511 TEST2(err, KErrNone); |
|
512 err = stmt2.BindInt(0, 0); |
|
513 TEST2(err, KErrNone); |
|
514 err = stmt2.Exec(); |
|
515 TEST(err >= 0); |
|
516 stmt2.Close(); |
|
517 |
|
518 TUint32 fc2 = User::FastCounter(); |
|
519 TheDeleteObjPropertyTime += TimeDiffUs(fc1, fc2); |
|
520 } |
|
521 |
|
522 void DoInsertCol2Prop() |
|
523 { |
|
524 _LIT(KSql1, "CREATE TABLE IF NOT EXISTS Col2Prop(ObjectDefId INTEGER NOT NULL,PropertyDefId INTEGER NOT NULL,ColumnId INTEGER NOT NULL)"); |
|
525 TInt KCol2PropCnt = KItemCnt; |
|
526 _LIT(KSql2, "INSERT INTO Col2Prop(ObjectDefId,PropertyDefId,ColumnId) Values(?,?,?);"); |
|
527 |
|
528 TUint32 fc1 = User::FastCounter(); |
|
529 |
|
530 TInt err = TheDb.Exec(KSql1); |
|
531 TEST(err >= 0); |
|
532 for(TInt i=0;i<KCol2PropCnt;++i) |
|
533 { |
|
534 RSqlStatement stmt; |
|
535 err = stmt.Prepare(TheDb, KSql2); |
|
536 TEST2(err, KErrNone); |
|
537 err = stmt.BindInt(0, 10); |
|
538 TEST2(err, KErrNone); |
|
539 err = stmt.BindInt(1, 20); |
|
540 TEST2(err, KErrNone); |
|
541 err = stmt.BindInt(2, i); |
|
542 TEST2(err, KErrNone); |
|
543 err = stmt.Exec(); |
|
544 TEST(err >= 0); |
|
545 stmt.Close(); |
|
546 } |
|
547 |
|
548 TUint32 fc2 = User::FastCounter(); |
|
549 TheInsertCol2PropTime += TimeDiffUs(fc1, fc2); |
|
550 } |
|
551 |
|
552 /** |
|
553 @SYMTestCaseID PDS-SQL-UT-4149 |
|
554 @SYMTestCaseDesc MDE database creation performance test. |
|
555 The test creates the MDE database using the SQL statements from the |
|
556 production code of the MetaDataEngine server. The time taken for the execution of |
|
557 SQL statements is measured and printed out. |
|
558 @SYMTestActions MDE database creation performance test. |
|
559 @SYMTestExpectedResults Test must not fail |
|
560 @SYMTestPriority High |
|
561 @SYMDEF DEF142327 |
|
562 */ |
|
563 void CreateDb() |
|
564 { |
|
565 DoCreateDbFile(); |
|
566 TheDb.Close(); |
|
567 DoCreateDbFile(); |
|
568 |
|
569 TUint32 fc1 = User::FastCounter(); |
|
570 |
|
571 //////////////////////////////// |
|
572 DoGetlastItemId(); |
|
573 MdePreferencesCheck(); |
|
574 CreateMdeTables(); |
|
575 //////////////////////////////// |
|
576 TUint32 fc2 = User::FastCounter(); |
|
577 BeginTransaction(); |
|
578 // |
|
579 DoCreateTables1(); |
|
580 DoCreateTables2(); |
|
581 DoCreateTables3(); |
|
582 DoCreateTriggers(); |
|
583 DoInsertEventRelationDefs(); |
|
584 DoDeleteObjPropertyDef(); |
|
585 DoInsertCol2Prop(); |
|
586 // |
|
587 CommitTransaction(); |
|
588 TUint32 fc3 = User::FastCounter(); |
|
589 TheTransactionTime += TimeDiffUs(fc2, fc3); |
|
590 TheDbCreateTime += TimeDiffUs(fc1, fc3); |
|
591 } |
|
592 |
|
593 void DoHarvestSelect() |
|
594 { |
|
595 TUint32 fc1 = User::FastCounter(); |
|
596 for(TInt i=0;i<TheIterationsCount;++i) |
|
597 { |
|
598 BeginTransaction(); |
|
599 for(TInt j=0;j<TheIterations[i];++j) |
|
600 { |
|
601 _LIT(KSql, "SELECT ObjectId, Flags, LastModifiedDate, Size FROM Object1 WHERE NOT Flags&? AND (Flags&? OR Flags&?) AND MediaId=? AND URI=?;"); |
|
602 RSqlStatement stmt; |
|
603 TInt err = stmt.Prepare(TheDb, KSql); |
|
604 TEST2(err, KErrNone); |
|
605 err = stmt.BindInt(0, 8); |
|
606 TEST2(err, KErrNone); |
|
607 err = stmt.BindInt(1, 16); |
|
608 TEST2(err, KErrNone); |
|
609 err = stmt.BindInt(2, 32); |
|
610 TEST2(err, KErrNone); |
|
611 TBuf<16> media; |
|
612 media.Copy(_L("media")); |
|
613 media.AppendNum(j + 1); |
|
614 err = stmt.BindText(3, media); |
|
615 TEST2(err, KErrNone); |
|
616 TBuf<16> uri; |
|
617 uri.Copy(_L("uri")); |
|
618 uri.AppendNum(j + 1); |
|
619 err = stmt.BindText(4, uri); |
|
620 TEST2(err, KErrNone); |
|
621 while((err = stmt.Next()) == KSqlAtRow) |
|
622 { |
|
623 } |
|
624 TEST2(err, KSqlAtEnd); |
|
625 stmt.Close(); |
|
626 } |
|
627 CommitTransaction(); |
|
628 } |
|
629 TUint32 fc2 = User::FastCounter(); |
|
630 TheHarvestSelectTime += TimeDiffUs(fc1, fc2); |
|
631 } |
|
632 |
|
633 void DoHarvestInsertObjImage() |
|
634 { |
|
635 _LIT(KSql1, "INSERT INTO Object1(ObjectId,ObjectDefId,Flags,MediaId,GuidHigh,GuidLow,URI,Origin,Size,CreationDate,LastModifiedDate) VALUES(?,?,?,?,?,?,?,?,?,?,?);"); |
|
636 _LIT(KSql2, "INSERT INTO Image1(ObjectId) VALUES(?);"); |
|
637 |
|
638 TUint32 fc1 = User::FastCounter(); |
|
639 TInt objdef = 0; |
|
640 for(TInt i=0;i<TheIterationsCount;++i) |
|
641 { |
|
642 BeginTransaction(); |
|
643 for(TInt j=0;j<TheIterations[i];++j) |
|
644 { |
|
645 ++objdef; |
|
646 RSqlStatement stmt1; |
|
647 TInt err = stmt1.Prepare(TheDb, KSql1); |
|
648 TEST2(err, KErrNone); |
|
649 err = stmt1.BindInt(0, objdef); |
|
650 TEST2(err, KErrNone); |
|
651 err = stmt1.BindInt(1, 9); |
|
652 TEST2(err, KErrNone); |
|
653 err = stmt1.BindInt(2, 48); |
|
654 TEST2(err, KErrNone); |
|
655 err = stmt1.BindInt(3, 3); |
|
656 TEST2(err, KErrNone); |
|
657 err = stmt1.BindInt(4, j + 1 + i * 100); |
|
658 TEST2(err, KErrNone); |
|
659 err = stmt1.BindInt(5, j + 1 + i * 10000); |
|
660 TEST2(err, KErrNone); |
|
661 TBuf<16> uri; |
|
662 uri.Copy(_L("URI-")); |
|
663 uri.AppendNum(j + 1 + i * 100); |
|
664 err = stmt1.BindText(6, uri); |
|
665 TEST2(err, KErrNone); |
|
666 err = stmt1.BindInt(7, 0); |
|
667 TEST2(err, KErrNone); |
|
668 err = stmt1.BindInt(8, 1000000); |
|
669 TEST2(err, KErrNone); |
|
670 err = stmt1.BindInt64(9, 32324234218723LL); |
|
671 TEST2(err, KErrNone); |
|
672 err = stmt1.BindInt64(10, 5675069785676565LL); |
|
673 TEST2(err, KErrNone); |
|
674 err = stmt1.Exec(); |
|
675 TEST(err >= 0); |
|
676 stmt1.Close(); |
|
677 |
|
678 RSqlStatement stmt2; |
|
679 err = stmt2.Prepare(TheDb, KSql2); |
|
680 TEST2(err, KErrNone); |
|
681 err = stmt2.BindInt(0, objdef); |
|
682 TEST2(err, KErrNone); |
|
683 err = stmt2.Exec(); |
|
684 TEST(err >= 0); |
|
685 stmt2.Close(); |
|
686 } |
|
687 CommitTransaction(); |
|
688 } |
|
689 TUint32 fc2 = User::FastCounter(); |
|
690 TheHarvestInsertObjImageTime += TimeDiffUs(fc1, fc2); |
|
691 } |
|
692 |
|
693 void DoHarvestSelect2() |
|
694 { |
|
695 TUint32 fc1 = User::FastCounter(); |
|
696 /////////////////////////////////////////////////////////////////////// |
|
697 _LIT(KSql1, "SELECT count(*) FROM Object1 AS BO WHERE(NOT Flags&?)AND (NOT Flags&?) AND (((BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE((NOT Flags&?)AND (NOT Flags&?)AND (NOT Flags&?)AND ObjectDefId=?))OR BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE((NOT Flags&?)AND (NOT Flags&?)AND (NOT Flags&?)AND ObjectDefId=?)))AND BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE(Origin=?)))) ORDER BY LastModifiedDate DESC,BO.ObjectId DESC ;"); |
|
698 RSqlStatement stmt1; |
|
699 TInt err = stmt1.Prepare(TheDb, KSql1); |
|
700 TEST2(err, KErrNone); |
|
701 err = stmt1.BindInt(0, 8); |
|
702 TEST2(err, KErrNone); |
|
703 err = stmt1.BindInt(1, 16); |
|
704 TEST2(err, KErrNone); |
|
705 err = stmt1.BindInt(2, 32); |
|
706 TEST2(err, KErrNone); |
|
707 err = stmt1.BindInt(3, 64); |
|
708 TEST2(err, KErrNone); |
|
709 err = stmt1.BindInt(4, 128); |
|
710 TEST2(err, KErrNone); |
|
711 err = stmt1.BindInt(5, 1); |
|
712 TEST2(err, KErrNone); |
|
713 err = stmt1.BindInt(6, 8); |
|
714 TEST2(err, KErrNone); |
|
715 err = stmt1.BindInt(7, 16); |
|
716 TEST2(err, KErrNone); |
|
717 err = stmt1.BindInt(8, 32); |
|
718 TEST2(err, KErrNone); |
|
719 err = stmt1.BindInt(9, 1); |
|
720 TEST2(err, KErrNone); |
|
721 err = stmt1.BindInt(10, 1); |
|
722 TEST2(err, KErrNone); |
|
723 while((err = stmt1.Next()) == KSqlAtRow) |
|
724 { |
|
725 } |
|
726 TEST2(err, KSqlAtEnd); |
|
727 stmt1.Close(); |
|
728 /////////////////////////////////////////////////////////////////////// |
|
729 _LIT(KSql2, "SELECT count(*) FROM Object1 AS BO WHERE(NOT Flags&?)AND (NOT Flags&?)AND ((BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE(Origin=?)))) ORDER BY CreationDate DESC,BO.ObjectId DESC ;"); |
|
730 RSqlStatement stmt2; |
|
731 err = stmt2.Prepare(TheDb, KSql2); |
|
732 TEST2(err, KErrNone); |
|
733 err = stmt2.BindInt(0, 8); |
|
734 TEST2(err, KErrNone); |
|
735 err = stmt2.BindInt(1, 16); |
|
736 TEST2(err, KErrNone); |
|
737 err = stmt2.BindInt(2, 1); |
|
738 TEST2(err, KErrNone); |
|
739 while((err = stmt2.Next()) == KSqlAtRow) |
|
740 { |
|
741 } |
|
742 TEST2(err, KSqlAtEnd); |
|
743 stmt2.Close(); |
|
744 /////////////////////////////////////////////////////////////////////// |
|
745 _LIT(KSql3, "SELECT BO.* FROM Object1 AS BO WHERE(NOT Flags&?)AND (NOT Flags&?)AND ((BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE(Origin=?)))) ORDER BY CreationDate ASC,BO.ObjectId ASC ;"); |
|
746 RSqlStatement stmt3; |
|
747 err = stmt3.Prepare(TheDb, KSql3); |
|
748 TEST2(err, KErrNone); |
|
749 err = stmt3.BindInt(0, 8); |
|
750 TEST2(err, KErrNone); |
|
751 err = stmt3.BindInt(1, 16); |
|
752 TEST2(err, KErrNone); |
|
753 err = stmt3.BindInt(2, 1); |
|
754 TEST2(err, KErrNone); |
|
755 while((err = stmt3.Next()) == KSqlAtRow) |
|
756 { |
|
757 } |
|
758 TEST2(err, KSqlAtEnd); |
|
759 stmt3.Close(); |
|
760 ///////////////////////////////////////////////////////////////////////// |
|
761 _LIT(KSql4, "SELECT count(*) FROM Object1 AS BO ,Album1 AS O ON BO.ObjectId=O.ObjectId WHERE(NOT Flags&?)AND (NOT Flags&?) ORDER BY Title COLLATE NOCASE ASC ;"); |
|
762 RSqlStatement stmt4; |
|
763 err = stmt4.Prepare(TheDb, KSql4); |
|
764 TEST2(err, KErrNone); |
|
765 err = stmt4.BindInt(0, 8); |
|
766 TEST2(err, KErrNone); |
|
767 err = stmt4.BindInt(1, 16); |
|
768 TEST2(err, KErrNone); |
|
769 while((err = stmt4.Next()) == KSqlAtRow) |
|
770 { |
|
771 } |
|
772 TEST2(err, KSqlAtEnd); |
|
773 stmt4.Close(); |
|
774 /////////////////////////////////////////////////////////////////////////// |
|
775 _LIT(KSql5, "SELECT count(*) FROM Object1 AS BO WHERE(NOT Flags&?)AND (NOT Flags&?)AND (((BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE((NOT Flags&?)AND (NOT Flags&?)AND (NOT Flags&?)AND ObjectDefId=?))OR BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE((NOT Flags&?)AND (NOT Flags&?)AND (NOT Flags&?)AND ObjectDefId=?)))AND BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE(Origin!=?)))) ORDER BY CreationDate DESC,BO.ObjectId DESC ;"); |
|
776 RSqlStatement stmt5; |
|
777 err = stmt5.Prepare(TheDb, KSql5); |
|
778 TEST2(err, KErrNone); |
|
779 err = stmt5.BindInt(0, 8); |
|
780 TEST2(err, KErrNone); |
|
781 err = stmt5.BindInt(1, 16); |
|
782 TEST2(err, KErrNone); |
|
783 err = stmt5.BindInt(2, 32); |
|
784 TEST2(err, KErrNone); |
|
785 err = stmt5.BindInt(3, 64); |
|
786 TEST2(err, KErrNone); |
|
787 err = stmt5.BindInt(4, 128); |
|
788 TEST2(err, KErrNone); |
|
789 err = stmt5.BindInt(5, 1); |
|
790 TEST2(err, KErrNone); |
|
791 err = stmt5.BindInt(6, 8); |
|
792 TEST2(err, KErrNone); |
|
793 err = stmt5.BindInt(7, 16); |
|
794 TEST2(err, KErrNone); |
|
795 err = stmt5.BindInt(8, 32); |
|
796 TEST2(err, KErrNone); |
|
797 err = stmt5.BindInt(9, 1); |
|
798 TEST2(err, KErrNone); |
|
799 err = stmt5.BindInt(10, 1); |
|
800 TEST2(err, KErrNone); |
|
801 while((err = stmt5.Next()) == KSqlAtRow) |
|
802 { |
|
803 } |
|
804 TEST2(err, KSqlAtEnd); |
|
805 stmt5.Close(); |
|
806 //////////////////////////////////////////////////////////////////////////////////////////////////////////// |
|
807 _LIT(KSql6, "SELECT count(*) FROM Object1 AS BO ,Tag1 AS O ON BO.ObjectId=O.ObjectId WHERE(NOT Flags&?)AND (NOT Flags&?)AND ((O.ObjectId IN(SELECT ObjectId FROM Object1 WHERE((NOT Flags&?)AND (NOT Flags&?)AND (NOT Flags&?)AND UsageCount>=?)))) ORDER BY Title COLLATE NOCASE ASC ;"); |
|
808 RSqlStatement stmt6; |
|
809 err = stmt6.Prepare(TheDb, KSql6); |
|
810 TEST2(err, KErrNone); |
|
811 err = stmt6.BindInt(0, 8); |
|
812 TEST2(err, KErrNone); |
|
813 err = stmt6.BindInt(1, 16); |
|
814 TEST2(err, KErrNone); |
|
815 err = stmt6.BindInt(2, 32); |
|
816 TEST2(err, KErrNone); |
|
817 err = stmt6.BindInt(3, 64); |
|
818 TEST2(err, KErrNone); |
|
819 err = stmt6.BindInt(4, 128); |
|
820 TEST2(err, KErrNone); |
|
821 err = stmt6.BindInt(5, 1); |
|
822 TEST2(err, KErrNone); |
|
823 while((err = stmt6.Next()) == KSqlAtRow) |
|
824 { |
|
825 } |
|
826 TEST2(err, KSqlAtEnd); |
|
827 stmt6.Close(); |
|
828 |
|
829 TUint32 fc2 = User::FastCounter(); |
|
830 TheHarvestSelect2Time += TimeDiffUs(fc1, fc2); |
|
831 } |
|
832 |
|
833 void DoHarvestInsertEvent() |
|
834 { |
|
835 TUint32 fc1 = User::FastCounter(); |
|
836 |
|
837 _LIT(KSql, "INSERT INTO Event1(EventId,ObjectId,EventDefId,Timestamp,Source,Participant) VALUES(?,?,?,?,?,?);"); |
|
838 const TInt KEventCnt = KItemCnt; |
|
839 |
|
840 for(TInt i=0;i<KEventCnt;++i) |
|
841 { |
|
842 BeginTransaction(); |
|
843 RSqlStatement stmt; |
|
844 TInt err = stmt.Prepare(TheDb, KSql); |
|
845 TEST2(err, KErrNone); |
|
846 err = stmt.BindInt(0, i + 1); |
|
847 TEST2(err, KErrNone); |
|
848 err = stmt.BindInt(1, i + 1); |
|
849 TEST2(err, KErrNone); |
|
850 err = stmt.BindInt(2, i + 1); |
|
851 TEST2(err, KErrNone); |
|
852 err = stmt.BindInt64(3, 329231202230LL); |
|
853 TEST2(err, KErrNone); |
|
854 err = stmt.BindInt(4, 1); |
|
855 TEST2(err, KErrNone); |
|
856 err = stmt.BindInt(5, 367); |
|
857 TEST2(err, KErrNone); |
|
858 stmt.Close(); |
|
859 CommitTransaction(); |
|
860 } |
|
861 |
|
862 TUint32 fc2 = User::FastCounter(); |
|
863 TheHarvestInsertEventTime += TimeDiffUs(fc1, fc2); |
|
864 } |
|
865 |
|
866 void DoHarvestUpdateObjImage() |
|
867 { |
|
868 TUint32 fc1 = User::FastCounter(); |
|
869 |
|
870 _LIT(KSql1, "UPDATE Object1 SET Flags=? ,Origin=?,TimeOffset=?,ItemType=?,Title=? WHERE ObjectId=?;"); |
|
871 _LIT(KSql2, "UPDATE Image1 SET Width=?,Height=?,BitsPerSample=?,FrameCount=? WHERE ObjectId=?;"); |
|
872 |
|
873 for(TInt i=0;i<KItemCnt;++i) |
|
874 { |
|
875 BeginTransaction(); |
|
876 |
|
877 RSqlStatement stmt1; |
|
878 TInt err = stmt1.Prepare(TheDb, KSql1); |
|
879 TEST2(err, KErrNone); |
|
880 err = stmt1.BindInt(0, 32); |
|
881 TEST2(err, KErrNone); |
|
882 err = stmt1.BindInt(1, 1); |
|
883 TEST2(err, KErrNone); |
|
884 err = stmt1.BindInt(2, 2); |
|
885 TEST2(err, KErrNone); |
|
886 err = stmt1.BindInt(3, 3); |
|
887 TEST2(err, KErrNone); |
|
888 TBuf<32> title; |
|
889 title.Copy(_L("Title-")); |
|
890 title.AppendNum(i + 1); |
|
891 err = stmt1.BindText(4, title); |
|
892 TEST2(err, KErrNone); |
|
893 err = stmt1.BindInt(5, i + 1); |
|
894 TEST2(err, KErrNone); |
|
895 err = stmt1.Exec(); |
|
896 TEST2(err, 1); |
|
897 stmt1.Close(); |
|
898 |
|
899 RSqlStatement stmt2; |
|
900 err = stmt2.Prepare(TheDb, KSql2); |
|
901 TEST2(err, KErrNone); |
|
902 err = stmt2.BindInt(0, 1000); |
|
903 TEST2(err, KErrNone); |
|
904 err = stmt2.BindInt(1, 1200); |
|
905 TEST2(err, KErrNone); |
|
906 err = stmt2.BindInt(2, 24); |
|
907 TEST2(err, KErrNone); |
|
908 err = stmt2.BindInt(3, 3); |
|
909 TEST2(err, KErrNone); |
|
910 err = stmt2.BindInt(4, i + 1); |
|
911 TEST2(err, KErrNone); |
|
912 err = stmt2.Exec(); |
|
913 TEST2(err, 1); |
|
914 stmt2.Close(); |
|
915 |
|
916 CommitTransaction(); |
|
917 } |
|
918 |
|
919 TUint32 fc2 = User::FastCounter(); |
|
920 TheHarvestUpdateObjImageTime += TimeDiffUs(fc1, fc2); |
|
921 } |
|
922 |
|
923 void DoHarvestSelect3() |
|
924 { |
|
925 TUint32 fc1 = User::FastCounter(); |
|
926 |
|
927 _LIT(KSql1, "SELECT BO.* ,O.* FROM Object1 AS BO ,Image1 AS O ON BO.ObjectId=O.ObjectId WHERE(NOT Flags&?)AND (NOT Flags&?)AND ((O.ObjectId IN(SELECT ObjectId FROM Object1 WHERE((NOT Flags&?)AND (NOT Flags&?)AND ObjectId=?))))LIMIT 1;"); |
|
928 |
|
929 for(TInt i=0;i<KItemCnt;++i) |
|
930 { |
|
931 RSqlStatement stmt; |
|
932 TInt err = stmt.Prepare(TheDb, KSql1); |
|
933 TEST2(err, KErrNone); |
|
934 err = stmt.BindInt(0, 65536); |
|
935 TEST2(err, KErrNone); |
|
936 err = stmt.BindInt(1, 65536); |
|
937 TEST2(err, KErrNone); |
|
938 err = stmt.BindInt(2, 65536); |
|
939 TEST2(err, KErrNone); |
|
940 err = stmt.BindInt(3, 65536); |
|
941 TEST2(err, KErrNone); |
|
942 err = stmt.BindInt(4, i + 1); |
|
943 TEST2(err, KErrNone); |
|
944 TInt cnt = 0; |
|
945 while((err = stmt.Next()) == KSqlAtRow) |
|
946 { |
|
947 ++cnt; |
|
948 } |
|
949 TEST2(err, KSqlAtEnd); |
|
950 TEST2(cnt, 1); |
|
951 stmt.Close(); |
|
952 } |
|
953 |
|
954 TUint32 fc2 = User::FastCounter(); |
|
955 TheHarvestSelect3Time += TimeDiffUs(fc1, fc2); |
|
956 } |
|
957 |
|
958 void DoHarvestSelect4() |
|
959 { |
|
960 TUint32 fc1 = User::FastCounter(); |
|
961 |
|
962 _LIT(KSql2, "SELECT BO.* FROM Object1 AS BO WHERE(NOT Flags&?)AND (NOT Flags&?)AND ((BO.ObjectId IN(SELECT ObjectId FROM Object1 WHERE((NOT Flags&?)AND (NOT Flags&?)AND (NOT Flags&?)AND ObjectId=?))));"); |
|
963 |
|
964 for(TInt i=0;i<KItemCnt;++i) |
|
965 { |
|
966 RSqlStatement stmt; |
|
967 TInt err = stmt.Prepare(TheDb, KSql2); |
|
968 TEST2(err, KErrNone); |
|
969 err = stmt.BindInt(0, 65536); |
|
970 TEST2(err, KErrNone); |
|
971 err = stmt.BindInt(1, 65536); |
|
972 TEST2(err, KErrNone); |
|
973 err = stmt.BindInt(2, 65536); |
|
974 TEST2(err, KErrNone); |
|
975 err = stmt.BindInt(3, 65536); |
|
976 TEST2(err, KErrNone); |
|
977 err = stmt.BindInt(4, 65536); |
|
978 TEST2(err, KErrNone); |
|
979 err = stmt.BindInt(5, i + 1); |
|
980 TEST2(err, KErrNone); |
|
981 TInt cnt = 0; |
|
982 while((err = stmt.Next()) == KSqlAtRow) |
|
983 { |
|
984 ++cnt; |
|
985 } |
|
986 TEST2(err, KSqlAtEnd); |
|
987 TEST2(cnt, 1); |
|
988 stmt.Close(); |
|
989 } |
|
990 |
|
991 TUint32 fc2 = User::FastCounter(); |
|
992 TheHarvestSelect4Time += TimeDiffUs(fc1, fc2); |
|
993 } |
|
994 |
|
995 void DoHarvestUpdateObj() |
|
996 { |
|
997 TUint32 fc1 = User::FastCounter(); |
|
998 |
|
999 _LIT(KSql, "UPDATE Object1 SET Flags=Flags&? WHERE ObjectId IN(?);"); |
|
1000 |
|
1001 for(TInt i=0;i<KItemCnt;++i) |
|
1002 { |
|
1003 BeginTransaction(); |
|
1004 RSqlStatement stmt; |
|
1005 TInt err = stmt.Prepare(TheDb, KSql); |
|
1006 TEST2(err, KErrNone); |
|
1007 err = stmt.BindInt(0, 32768); |
|
1008 TEST2(err, KErrNone); |
|
1009 err = stmt.BindInt(1, i + 1); |
|
1010 TEST2(err, KErrNone); |
|
1011 err = stmt.Exec(); |
|
1012 TEST2(err, 1); |
|
1013 stmt.Close(); |
|
1014 CommitTransaction(); |
|
1015 } |
|
1016 |
|
1017 TUint32 fc2 = User::FastCounter(); |
|
1018 TheHarvestUpdateObjTime += TimeDiffUs(fc1, fc2); |
|
1019 } |
|
1020 |
|
1021 void Harvest() |
|
1022 { |
|
1023 TUint32 fc1 = User::FastCounter(); |
|
1024 DoHarvestSelect(); |
|
1025 DoHarvestInsertObjImage(); |
|
1026 DoHarvestSelect2(); |
|
1027 DoHarvestSelect2(); |
|
1028 DoHarvestSelect2(); |
|
1029 DoHarvestInsertEvent(); |
|
1030 DoHarvestUpdateObjImage(); |
|
1031 DoHarvestSelect3(); |
|
1032 DoHarvestSelect4(); |
|
1033 DoHarvestUpdateObj(); |
|
1034 TUint32 fc2 = User::FastCounter(); |
|
1035 TheHarvestTime += TimeDiffUs(fc1, fc2); |
|
1036 } |
|
1037 |
|
1038 /** |
|
1039 @SYMTestCaseID PDS-SQL-UT-4150 |
|
1040 @SYMTestCaseDesc MDS harvesting performance test. |
|
1041 The test reproduces the MDS harvesting actions with the SQL server, |
|
1042 performed for 1000 images. The time taken for the execution of SQL statements |
|
1043 is measured and printed out. |
|
1044 @SYMTestActions MDS harvesting performance test. |
|
1045 @SYMTestExpectedResults Test must not fail |
|
1046 @SYMTestPriority High |
|
1047 @SYMDEF DEF142327 |
|
1048 */ |
|
1049 void PrintResults() |
|
1050 { |
|
1051 TheTest.Printf(_L("==Fast counter frequency=%d Hz\r\n"), TheFastCounterFreq); |
|
1052 TheTest.Printf(_L("==Create database, time=%d us\r\n"), TheCreateDbCreateConnTime); |
|
1053 TheTest.Printf(_L("==Open database, time=%d us\r\n"), TheCreateDbOpenConnTime); |
|
1054 TheTest.Printf(_L("=='SELECT max(seq) FROM SQLITE_SEQUENCE', time=%d us\r\n"), TheLastItemIdTime); |
|
1055 TheTest.Printf(_L("=='SELECT COUNT(*) FROM MdE_Preferences', time=%d us\r\n"), TheTableExistsTime); |
|
1056 TheTest.Printf(_L("==MDE tables creation, time=%d us\r\n"), TheMdeTablesCreationTime); |
|
1057 TheTest.Printf(_L("=='SELECT last_insert_rowid()', time=%d us\r\n"), TheLastInsertedRowIdTime); |
|
1058 TheTest.Printf(_L("==1.Create tables, time=%d us\r\n"), TheCreateTables1Time); |
|
1059 TheTest.Printf(_L("==2.Create tables, time=%d us\r\n"), TheCreateTables2Time); |
|
1060 TheTest.Printf(_L("==3.Create tables, time=%d us\r\n"), TheCreateTables3Time); |
|
1061 TheTest.Printf(_L("==InsertEventRelation, time=%d us\r\n"), TheInsertEventRelationTime); |
|
1062 TheTest.Printf(_L("==Create triggers, time=%d us\r\n"), TheCreateTriggersTime); |
|
1063 TheTest.Printf(_L("==DeleteObjectProperty, time=%d us\r\n"), TheDeleteObjPropertyTime); |
|
1064 TheTest.Printf(_L("==InsertCol2Property, time=%d us\r\n"), TheInsertCol2PropTime); |
|
1065 TheTest.Printf(_L("==The create transaction, time=%d us\r\n"), TheTransactionTime); |
|
1066 TheTest.Printf(_L("==Database create schema, time=%d us\r\n"), TheDbCreateTime); |
|
1067 |
|
1068 TheTest.Printf(_L("==HarvestSelect, time=%d us\r\n"), TheHarvestSelectTime); |
|
1069 TheTest.Printf(_L("==HarvestInsertObjImage, time=%d us\r\n"), TheHarvestInsertObjImageTime); |
|
1070 TheTest.Printf(_L("==HarvestSelect2, time=%d us\r\n"), TheHarvestSelect2Time); |
|
1071 TheTest.Printf(_L("==HarvestSelect3, time=%d us\r\n"), TheHarvestSelect3Time); |
|
1072 TheTest.Printf(_L("==HarvestSelect4, time=%d us\r\n"), TheHarvestSelect4Time); |
|
1073 TheTest.Printf(_L("==HarvestInsertEvent, time=%d us\r\n"), TheHarvestInsertEventTime); |
|
1074 TheTest.Printf(_L("==HarvestUpdateObjImage, time=%d us\r\n"), TheHarvestUpdateObjImageTime); |
|
1075 TheTest.Printf(_L("==HarvestUpdateObj, time=%d us\r\n"), TheHarvestUpdateObjTime); |
|
1076 TheTest.Printf(_L("==Harvest, time=%d us\r\n"), TheHarvestTime); |
|
1077 } |
|
1078 |
|
1079 void DoTestsL() |
|
1080 { |
|
1081 CalcIterationsCount(); |
|
1082 |
|
1083 TheTest.Start(_L("@SYMTestCaseID:PDS-SQL-UT-4149 Create database")); |
|
1084 CreateDb(); |
|
1085 |
|
1086 TBuf<80> buf; |
|
1087 buf.Format(_L("@SYMTestCaseID:PDS-SQL-UT-4150 Harvest %d items"), KItemCnt); |
|
1088 TheTest.Next(buf); |
|
1089 Harvest(); |
|
1090 |
|
1091 PrintResults(); |
|
1092 } |
|
1093 |
|
1094 //Usage: "t_sqlperformance3 [<drive letter>:]" |
|
1095 |
|
1096 TInt E32Main() |
|
1097 { |
|
1098 TheTest.Title(); |
|
1099 |
|
1100 CTrapCleanup* tc = CTrapCleanup::New(); |
|
1101 TheTest(tc != NULL); |
|
1102 |
|
1103 __UHEAP_MARK; |
|
1104 |
|
1105 User::CommandLine(TheCmd); |
|
1106 TheCmd.TrimAll(); |
|
1107 if(TheCmd.Length() > 0) |
|
1108 { |
|
1109 TheDriveName.Copy(TheCmd); |
|
1110 } |
|
1111 TheParse.Set(TheDriveName, &KDbName, 0); |
|
1112 const TDesC& dbFilePath = TheParse.FullName(); |
|
1113 TheDbFileName.Copy(dbFilePath); |
|
1114 TheTest.Printf(_L("==Database file name: %S\r\n"), &TheDbFileName); |
|
1115 |
|
1116 TestEnvDestroy(); |
|
1117 TestEnvInit(); |
|
1118 TRAPD(err, DoTestsL()); |
|
1119 TestEnvDestroy(); |
|
1120 TEST2(err, KErrNone); |
|
1121 |
|
1122 __UHEAP_MARKEND; |
|
1123 |
|
1124 TheTest.End(); |
|
1125 TheTest.Close(); |
|
1126 |
|
1127 delete tc; |
|
1128 |
|
1129 User::Heap().Check(); |
|
1130 return KErrNone; |
|
1131 } |