?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Snapshot of upstream SQLite 3.37.2
[sqlcipher.git] / test / speedtest1.c
blob83c503c8c789a7b8290cc13dc09219a0a6a2c1ae
1 /*
2 ** A program for performance testing.
3 **
4 ** The available command-line options are described below:
5 */
6 static const char zHelp[] =
7 "Usage: %s [--options] DATABASE\n"
8 "Options:\n"
9 " --autovacuum Enable AUTOVACUUM mode\n"
10 " --cachesize N Set the cache size to N\n"
11 " --checkpoint Run PRAGMA wal_checkpoint after each test case\n"
12 " --exclusive Enable locking_mode=EXCLUSIVE\n"
13 " --explain Like --sqlonly but with added EXPLAIN keywords\n"
14 " --heap SZ MIN Memory allocator uses SZ bytes & min allocation MIN\n"
15 " --incrvacuum Enable incremenatal vacuum mode\n"
16 " --journal M Set the journal_mode to M\n"
17 " --key KEY Set the encryption key to KEY\n"
18 " --lookaside N SZ Configure lookaside for N slots of SZ bytes each\n"
19 " --memdb Use an in-memory database\n"
20 " --mmap SZ MMAP the first SZ bytes of the database file\n"
21 " --multithread Set multithreaded mode\n"
22 " --nomemstat Disable memory statistics\n"
23 " --nosync Set PRAGMA synchronous=OFF\n"
24 " --notnull Add NOT NULL constraints to table columns\n"
25 " --output FILE Store SQL output in FILE\n"
26 " --pagesize N Set the page size to N\n"
27 " --pcache N SZ Configure N pages of pagecache each of size SZ bytes\n"
28 " --primarykey Use PRIMARY KEY instead of UNIQUE where appropriate\n"
29 " --repeat N Repeat each SELECT N times (default: 1)\n"
30 " --reprepare Reprepare each statement upon every invocation\n"
31 " --reserve N Reserve N bytes on each database page\n"
32 " --serialized Set serialized threading mode\n"
33 " --singlethread Set single-threaded mode - disables all mutexing\n"
34 " --sqlonly No-op. Only show the SQL that would have been run.\n"
35 " --shrink-memory Invoke sqlite3_db_release_memory() frequently.\n"
36 " --size N Relative test size. Default=100\n"
37 " --strict Use STRICT table where appropriate\n"
38 " --stats Show statistics at the end\n"
39 " --temp N N from 0 to 9. 0: no temp table. 9: all temp tables\n"
40 " --testset T Run test-set T (main, cte, rtree, orm, fp, debug)\n"
41 " --trace Turn on SQL tracing\n"
42 " --threads N Use up to N threads for sorting\n"
43 " --utf16be Set text encoding to UTF-16BE\n"
44 " --utf16le Set text encoding to UTF-16LE\n"
45 " --verify Run additional verification steps.\n"
46 " --without-rowid Use WITHOUT ROWID where appropriate\n"
47 ;
48
49 #include "sqlite3.h"
50 #include <assert.h>
51 #include <stdio.h>
52 #include <stdlib.h>
53 #include <stdarg.h>
54 #include <string.h>
55 #include <ctype.h>
56 #ifndef _WIN32
57 # include <unistd.h>
58 #else
59 # include <io.h>
60 #endif
61 #define ISSPACE(X) isspace((unsigned char)(X))
62 #define ISDIGIT(X) isdigit((unsigned char)(X))
63
64 #if SQLITE_VERSION_NUMBER<3005000
65 # define sqlite3_int64 sqlite_int64
66 #endif
67
68 typedef sqlite3_uint64 u64;
69
70 /*
71 ** State structure for a Hash hash in progress
72 */
73 typedef struct HashContext HashContext;
74 struct HashContext {
75 unsigned char isInit; /* True if initialized */
76 unsigned char i, j; /* State variables */
77 unsigned char s[256]; /* State variables */
78 unsigned char r[32]; /* Result */
79 };
80
81
82 /* All global state is held in this structure */
83 static struct Global {
84 sqlite3 *db; /* The open database connection */
85 sqlite3_stmt *pStmt; /* Current SQL statement */
86 sqlite3_int64 iStart; /* Start-time for the current test */
87 sqlite3_int64 iTotal; /* Total time */
88 int bWithoutRowid; /* True for --without-rowid */
89 int bReprepare; /* True to reprepare the SQL on each rerun */
90 int bSqlOnly; /* True to print the SQL once only */
91 int bExplain; /* Print SQL with EXPLAIN prefix */
92 int bVerify; /* Try to verify that results are correct */
93 int bMemShrink; /* Call sqlite3_db_release_memory() often */
94 int eTemp; /* 0: no TEMP. 9: always TEMP. */
95 int szTest; /* Scale factor for test iterations */
96 int nRepeat; /* Repeat selects this many times */
97 int doCheckpoint; /* Run PRAGMA wal_checkpoint after each trans */
98 int nReserve; /* Reserve bytes */
99 const char *zWR; /* Might be WITHOUT ROWID */
100 const char *zNN; /* Might be NOT NULL */
101 const char *zPK; /* Might be UNIQUE or PRIMARY KEY */
102 unsigned int x, y; /* Pseudo-random number generator state */
103 u64 nResByte; /* Total number of result bytes */
104 int nResult; /* Size of the current result */
105 char zResult[3000]; /* Text of the current result */
106 #ifndef SPEEDTEST_OMIT_HASH
107 FILE *hashFile; /* Store all hash results in this file */
108 HashContext hash; /* Hash of all output */
109 #endif
110 } g;
111
112 /* Return " TEMP" or "", as appropriate for creating a table.
113 */
114 static const char *isTemp(int N){
115 return g.eTemp>=N ? " TEMP" : "";
116 }
117
118 /* Print an error message and exit */
119 static void fatal_error(const char *zMsg, ...){
120 va_list ap;
121 va_start(ap, zMsg);
122 vfprintf(stderr, zMsg, ap);
123 va_end(ap);
124 exit(1);
125 }
126
127 #ifndef SPEEDTEST_OMIT_HASH
128 /****************************************************************************
129 ** Hash algorithm used to verify that compilation is not miscompiled
130 ** in such a was as to generate an incorrect result.
131 */
132
133 /*
134 ** Initialize a new hash. iSize determines the size of the hash
135 ** in bits and should be one of 224, 256, 384, or 512. Or iSize
136 ** can be zero to use the default hash size of 256 bits.
137 */
138 static void HashInit(void){
139 unsigned int k;
140 g.hash.i = 0;
141 g.hash.j = 0;
142 for(k=0; k<256; k++) g.hash.s[k] = k;
143 }
144
145 /*
146 ** Make consecutive calls to the HashUpdate function to add new content
147 ** to the hash
148 */
149 static void HashUpdate(
150 const unsigned char *aData,
151 unsigned int nData
152 ){
153 unsigned char t;
154 unsigned char i = g.hash.i;
155 unsigned char j = g.hash.j;
156 unsigned int k;
157 if( g.hashFile ) fwrite(aData, 1, nData, g.hashFile);
158 for(k=0; k<nData; k++){
159 j += g.hash.s[i] + aData[k];
160 t = g.hash.s[j];
161 g.hash.s[j] = g.hash.s[i];
162 g.hash.s[i] = t;
163 i++;
164 }
165 g.hash.i = i;
166 g.hash.j = j;
167 }
168
169 /*
170 ** After all content has been added, invoke HashFinal() to compute
171 ** the final hash. The hash result is stored in g.hash.r[].
172 */
173 static void HashFinal(void){
174 unsigned int k;
175 unsigned char t, i, j;
176 i = g.hash.i;
177 j = g.hash.j;
178 for(k=0; k<32; k++){
179 i++;
180 t = g.hash.s[i];
181 j += t;
182 g.hash.s[i] = g.hash.s[j];
183 g.hash.s[j] = t;
184 t += g.hash.s[i];
185 g.hash.r[k] = g.hash.s[t];
186 }
187 }
188
189 /* End of the Hash hashing logic
190 *****************************************************************************/
191 #endif /* SPEEDTEST_OMIT_HASH */
192
193 /*
194 ** Return the value of a hexadecimal digit. Return -1 if the input
195 ** is not a hex digit.
196 */
197 static int hexDigitValue(char c){
198 if( c>='0' && c<='9' ) return c - '0';
199 if( c>='a' && c<='f' ) return c - 'a' + 10;
200 if( c>='A' && c<='F' ) return c - 'A' + 10;
201 return -1;
202 }
203
204 /* Provide an alternative to sqlite3_stricmp() in older versions of
205 ** SQLite */
206 #if SQLITE_VERSION_NUMBER<3007011
207 # define sqlite3_stricmp strcmp
208 #endif
209
210 /*
211 ** Interpret zArg as an integer value, possibly with suffixes.
212 */
213 static int integerValue(const char *zArg){
214 sqlite3_int64 v = 0;
215 static const struct { char *zSuffix; int iMult; } aMult[] = {
216 { "KiB", 1024 },
217 { "MiB", 1024*1024 },
218 { "GiB", 1024*1024*1024 },
219 { "KB", 1000 },
220 { "MB", 1000000 },
221 { "GB", 1000000000 },
222 { "K", 1000 },
223 { "M", 1000000 },
224 { "G", 1000000000 },
225 };
226 int i;
227 int isNeg = 0;
228 if( zArg[0]=='-' ){
229 isNeg = 1;
230 zArg++;
231 }else if( zArg[0]=='+' ){
232 zArg++;
233 }
234 if( zArg[0]=='0' && zArg[1]=='x' ){
235 int x;
236 zArg += 2;
237 while( (x = hexDigitValue(zArg[0]))>=0 ){
238 v = (v<<4) + x;
239 zArg++;
240 }
241 }else{
242 while( isdigit(zArg[0]) ){
243 v = v*10 + zArg[0] - '0';
244 zArg++;
245 }
246 }
247 for(i=0; i<sizeof(aMult)/sizeof(aMult[0]); i++){
248 if( sqlite3_stricmp(aMult[i].zSuffix, zArg)==0 ){
249 v *= aMult[i].iMult;
250 break;
251 }
252 }
253 if( v>0x7fffffff ) fatal_error("parameter too large - max 2147483648");
254 return (int)(isNeg? -v : v);
255 }
256
257 /* Return the current wall-clock time, in milliseconds */
258 sqlite3_int64 speedtest1_timestamp(void){
259 #if SQLITE_VERSION_NUMBER<3005000
260 return 0;
261 #else
262 static sqlite3_vfs *clockVfs = 0;
263 sqlite3_int64 t;
264 if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
265 #if SQLITE_VERSION_NUMBER>=3007000
266 if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
267 clockVfs->xCurrentTimeInt64(clockVfs, &t);
268 }else
269 #endif
270 {
271 double r;
272 clockVfs->xCurrentTime(clockVfs, &r);
273 t = (sqlite3_int64)(r*86400000.0);
274 }
275 return t;
276 #endif
277 }
278
279 /* Return a pseudo-random unsigned integer */
280 unsigned int speedtest1_random(void){
281 g.x = (g.x>>1) ^ ((1+~(g.x&1)) & 0xd0000001);
282 g.y = g.y*1103515245 + 12345;
283 return g.x ^ g.y;
284 }
285
286 /* Map the value in within the range of 1...limit into another
287 ** number in a way that is chatic and invertable.
288 */
289 unsigned swizzle(unsigned in, unsigned limit){
290 unsigned out = 0;
291 while( limit ){
292 out = (out<<1) | (in&1);
293 in >>= 1;
294 limit >>= 1;
295 }
296 return out;
297 }
298
299 /* Round up a number so that it is a power of two minus one
300 */
301 unsigned roundup_allones(unsigned limit){
302 unsigned m = 1;
303 while( m<limit ) m = (m<<1)+1;
304 return m;
305 }
306
307 /* The speedtest1_numbername procedure below converts its argment (an integer)
308 ** into a string which is the English-language name for that number.
309 ** The returned string should be freed with sqlite3_free().
310 **
311 ** Example:
312 **
313 ** speedtest1_numbername(123) -> "one hundred twenty three"
314 */
315 int speedtest1_numbername(unsigned int n, char *zOut, int nOut){
316 static const char *ones[] = { "zero", "one", "two", "three", "four", "five",
317 "six", "seven", "eight", "nine", "ten", "eleven", "twelve",
318 "thirteen", "fourteen", "fifteen", "sixteen", "seventeen",
319 "eighteen", "nineteen" };
320 static const char *tens[] = { "", "ten", "twenty", "thirty", "forty",
321 "fifty", "sixty", "seventy", "eighty", "ninety" };
322 int i = 0;
323
324 if( n>=1000000000 ){
325 i += speedtest1_numbername(n/1000000000, zOut+i, nOut-i);
326 sqlite3_snprintf(nOut-i, zOut+i, " billion");
327 i += (int)strlen(zOut+i);
328 n = n % 1000000000;
329 }
330 if( n>=1000000 ){
331 if( i && i<nOut-1 ) zOut[i++] = ' ';
332 i += speedtest1_numbername(n/1000000, zOut+i, nOut-i);
333 sqlite3_snprintf(nOut-i, zOut+i, " million");
334 i += (int)strlen(zOut+i);
335 n = n % 1000000;
336 }
337 if( n>=1000 ){
338 if( i && i<nOut-1 ) zOut[i++] = ' ';
339 i += speedtest1_numbername(n/1000, zOut+i, nOut-i);
340 sqlite3_snprintf(nOut-i, zOut+i, " thousand");
341 i += (int)strlen(zOut+i);
342 n = n % 1000;
343 }
344 if( n>=100 ){
345 if( i && i<nOut-1 ) zOut[i++] = ' ';
346 sqlite3_snprintf(nOut-i, zOut+i, "%s hundred", ones[n/100]);
347 i += (int)strlen(zOut+i);
348 n = n % 100;
349 }
350 if( n>=20 ){
351 if( i && i<nOut-1 ) zOut[i++] = ' ';
352 sqlite3_snprintf(nOut-i, zOut+i, "%s", tens[n/10]);
353 i += (int)strlen(zOut+i);
354 n = n % 10;
355 }
356 if( n>0 ){
357 if( i && i<nOut-1 ) zOut[i++] = ' ';
358 sqlite3_snprintf(nOut-i, zOut+i, "%s", ones[n]);
359 i += (int)strlen(zOut+i);
360 }
361 if( i==0 ){
362 sqlite3_snprintf(nOut-i, zOut+i, "zero");
363 i += (int)strlen(zOut+i);
364 }
365 return i;
366 }
367
368
369 /* Start a new test case */
370 #define NAMEWIDTH 60
371 static const char zDots[] =
372 ".......................................................................";
373 void speedtest1_begin_test(int iTestNum, const char *zTestName, ...){
374 int n = (int)strlen(zTestName);
375 char *zName;
376 va_list ap;
377 va_start(ap, zTestName);
378 zName = sqlite3_vmprintf(zTestName, ap);
379 va_end(ap);
380 n = (int)strlen(zName);
381 if( n>NAMEWIDTH ){
382 zName[NAMEWIDTH] = 0;
383 n = NAMEWIDTH;
384 }
385 if( g.bSqlOnly ){
386 printf("/* %4d - %s%.*s */\n", iTestNum, zName, NAMEWIDTH-n, zDots);
387 }else{
388 printf("%4d - %s%.*s ", iTestNum, zName, NAMEWIDTH-n, zDots);
389 fflush(stdout);
390 }
391 sqlite3_free(zName);
392 g.nResult = 0;
393 g.iStart = speedtest1_timestamp();
394 g.x = 0xad131d0b;
395 g.y = 0x44f9eac8;
396 }
397
398 /* Forward reference */
399 void speedtest1_exec(const char*,...);
400
401 /* Complete a test case */
402 void speedtest1_end_test(void){
403 sqlite3_int64 iElapseTime = speedtest1_timestamp() - g.iStart;
404 if( g.doCheckpoint ) speedtest1_exec("PRAGMA wal_checkpoint;");
405 if( !g.bSqlOnly ){
406 g.iTotal += iElapseTime;
407 printf("%4d.%03ds\n", (int)(iElapseTime/1000), (int)(iElapseTime%1000));
408 }
409 if( g.pStmt ){
410 sqlite3_finalize(g.pStmt);
411 g.pStmt = 0;
412 }
413 }
414
415 /* Report end of testing */
416 void speedtest1_final(void){
417 if( !g.bSqlOnly ){
418 printf(" TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
419 (int)(g.iTotal/1000), (int)(g.iTotal%1000));
420 }
421 if( g.bVerify ){
422 #ifndef SPEEDTEST_OMIT_HASH
423 int i;
424 #endif
425 printf("Verification Hash: %llu ", g.nResByte);
426 #ifndef SPEEDTEST_OMIT_HASH
427 HashUpdate((const unsigned char*)"\n", 1);
428 HashFinal();
429 for(i=0; i<24; i++){
430 printf("%02x", g.hash.r[i]);
431 }
432 if( g.hashFile && g.hashFile!=stdout ) fclose(g.hashFile);
433 #endif
434 printf("\n");
435 }
436 }
437
438 /* Print an SQL statement to standard output */
439 static void printSql(const char *zSql){
440 int n = (int)strlen(zSql);
441 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ){ n--; }
442 if( g.bExplain ) printf("EXPLAIN ");
443 printf("%.*s;\n", n, zSql);
444 if( g.bExplain
445 #if SQLITE_VERSION_NUMBER>=3007017
446 && ( sqlite3_strglob("CREATE *", zSql)==0
447 || sqlite3_strglob("DROP *", zSql)==0
448 || sqlite3_strglob("ALTER *", zSql)==0
449 )
450 #endif
451 ){
452 printf("%.*s;\n", n, zSql);
453 }
454 }
455
456 /* Shrink memory used, if appropriate and if the SQLite version is capable
457 ** of doing so.
458 */
459 void speedtest1_shrink_memory(void){
460 #if SQLITE_VERSION_NUMBER>=3007010
461 if( g.bMemShrink ) sqlite3_db_release_memory(g.db);
462 #endif
463 }
464
465 /* Run SQL */
466 void speedtest1_exec(const char *zFormat, ...){
467 va_list ap;
468 char *zSql;
469 va_start(ap, zFormat);
470 zSql = sqlite3_vmprintf(zFormat, ap);
471 va_end(ap);
472 if( g.bSqlOnly ){
473 printSql(zSql);
474 }else{
475 char *zErrMsg = 0;
476 int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
477 if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
478 if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
479 }
480 sqlite3_free(zSql);
481 speedtest1_shrink_memory();
482 }
483
484 /* Run SQL and return the first column of the first row as a string. The
485 ** returned string is obtained from sqlite_malloc() and must be freed by
486 ** the caller.
487 */
488 char *speedtest1_once(const char *zFormat, ...){
489 va_list ap;
490 char *zSql;
491 sqlite3_stmt *pStmt;
492 char *zResult = 0;
493 va_start(ap, zFormat);
494 zSql = sqlite3_vmprintf(zFormat, ap);
495 va_end(ap);
496 if( g.bSqlOnly ){
497 printSql(zSql);
498 }else{
499 int rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0);
500 if( rc ){
501 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
502 }
503 if( sqlite3_step(pStmt)==SQLITE_ROW ){
504 const char *z = (const char*)sqlite3_column_text(pStmt, 0);
505 if( z ) zResult = sqlite3_mprintf("%s", z);
506 }
507 sqlite3_finalize(pStmt);
508 }
509 sqlite3_free(zSql);
510 speedtest1_shrink_memory();
511 return zResult;
512 }
513
514 /* Prepare an SQL statement */
515 void speedtest1_prepare(const char *zFormat, ...){
516 va_list ap;
517 char *zSql;
518 va_start(ap, zFormat);
519 zSql = sqlite3_vmprintf(zFormat, ap);
520 va_end(ap);
521 if( g.bSqlOnly ){
522 printSql(zSql);
523 }else{
524 int rc;
525 if( g.pStmt ) sqlite3_finalize(g.pStmt);
526 rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
527 if( rc ){
528 fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
529 }
530 }
531 sqlite3_free(zSql);
532 }
533
534 /* Run an SQL statement previously prepared */
535 void speedtest1_run(void){
536 int i, n, len;
537 if( g.bSqlOnly ) return;
538 assert( g.pStmt );
539 g.nResult = 0;
540 while( sqlite3_step(g.pStmt)==SQLITE_ROW ){
541 n = sqlite3_column_count(g.pStmt);
542 for(i=0; i<n; i++){
543 const char *z = (const char*)sqlite3_column_text(g.pStmt, i);
544 if( z==0 ) z = "nil";
545 len = (int)strlen(z);
546 #ifndef SPEEDTEST_OMIT_HASH
547 if( g.bVerify ){
548 int eType = sqlite3_column_type(g.pStmt, i);
549 unsigned char zPrefix[2];
550 zPrefix[0] = '\n';
551 zPrefix[1] = "-IFTBN"[eType];
552 if( g.nResByte ){
553 HashUpdate(zPrefix, 2);
554 }else{
555 HashUpdate(zPrefix+1, 1);
556 }
557 if( eType==SQLITE_FLOAT ){
558 /* Omit the value of floating-point results from the verification
559 ** hash. The only thing we record is the fact that the result was
560 ** a floating-point value. */
561 g.nResByte += 2;
562 }else if( eType==SQLITE_BLOB ){
563 int nBlob = sqlite3_column_bytes(g.pStmt, i);
564 int iBlob;
565 unsigned char zChar[2];
566 const unsigned char *aBlob = sqlite3_column_blob(g.pStmt, i);
567 for(iBlob=0; iBlob<nBlob; iBlob++){
568 zChar[0] = "0123456789abcdef"[aBlob[iBlob]>>4];
569 zChar[1] = "0123456789abcdef"[aBlob[iBlob]&15];
570 HashUpdate(zChar,2);
571 }
572 g.nResByte += nBlob*2 + 2;
573 }else{
574 HashUpdate((unsigned char*)z, len);
575 g.nResByte += len + 2;
576 }
577 }
578 #endif
579 if( g.nResult+len<sizeof(g.zResult)-2 ){
580 if( g.nResult>0 ) g.zResult[g.nResult++] = ' ';
581 memcpy(g.zResult + g.nResult, z, len+1);
582 g.nResult += len;
583 }
584 }
585 }
586 #if SQLITE_VERSION_NUMBER>=3006001
587 if( g.bReprepare ){
588 sqlite3_stmt *pNew;
589 sqlite3_prepare_v2(g.db, sqlite3_sql(g.pStmt), -1, &pNew, 0);
590 sqlite3_finalize(g.pStmt);
591 g.pStmt = pNew;
592 }else
593 #endif
594 {
595 sqlite3_reset(g.pStmt);
596 }
597 speedtest1_shrink_memory();
598 }
599
600 #ifndef SQLITE_OMIT_DEPRECATED
601 /* The sqlite3_trace() callback function */
602 static void traceCallback(void *NotUsed, const char *zSql){
603 int n = (int)strlen(zSql);
604 while( n>0 && (zSql[n-1]==';' || ISSPACE(zSql[n-1])) ) n--;
605 fprintf(stderr,"%.*s;\n", n, zSql);
606 }
607 #endif /* SQLITE_OMIT_DEPRECATED */
608
609 /* Substitute random() function that gives the same random
610 ** sequence on each run, for repeatability. */
611 static void randomFunc(
612 sqlite3_context *context,
613 int NotUsed,
614 sqlite3_value **NotUsed2
615 ){
616 sqlite3_result_int64(context, (sqlite3_int64)speedtest1_random());
617 }
618
619 /* Estimate the square root of an integer */
620 static int est_square_root(int x){
621 int y0 = x/2;
622 int y1;
623 int n;
624 for(n=0; y0>0 && n<10; n++){
625 y1 = (y0 + x/y0)/2;
626 if( y1==y0 ) break;
627 y0 = y1;
628 }
629 return y0;
630 }
631
632
633 #if SQLITE_VERSION_NUMBER<3005004
634 /*
635 ** An implementation of group_concat(). Used only when testing older
636 ** versions of SQLite that lack the built-in group_concat().
637 */
638 struct groupConcat {
639 char *z;
640 int nAlloc;
641 int nUsed;
642 };
643 static void groupAppend(struct groupConcat *p, const char *z, int n){
644 if( p->nUsed+n >= p->nAlloc ){
645 int n2 = (p->nAlloc+n+1)*2;
646 char *z2 = sqlite3_realloc(p->z, n2);
647 if( z2==0 ) return;
648 p->z = z2;
649 p->nAlloc = n2;
650 }
651 memcpy(p->z+p->nUsed, z, n);
652 p->nUsed += n;
653 }
654 static void groupStep(
655 sqlite3_context *context,
656 int argc,
657 sqlite3_value **argv
658 ){
659 const char *zVal;
660 struct groupConcat *p;
661 const char *zSep;
662 int nVal, nSep;
663 assert( argc==1 || argc==2 );
664 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
665 p= (struct groupConcat*)sqlite3_aggregate_context(context, sizeof(*p));
666
667 if( p ){
668 int firstTerm = p->nUsed==0;
669 if( !firstTerm ){
670 if( argc==2 ){
671 zSep = (char*)sqlite3_value_text(argv[1]);
672 nSep = sqlite3_value_bytes(argv[1]);
673 }else{
674 zSep = ",";
675 nSep = 1;
676 }
677 if( nSep ) groupAppend(p, zSep, nSep);
678 }
679 zVal = (char*)sqlite3_value_text(argv[0]);
680 nVal = sqlite3_value_bytes(argv[0]);
681 if( zVal ) groupAppend(p, zVal, nVal);
682 }
683 }
684 static void groupFinal(sqlite3_context *context){
685 struct groupConcat *p;
686 p = sqlite3_aggregate_context(context, 0);
687 if( p && p->z ){
688 p->z[p->nUsed] = 0;
689 sqlite3_result_text(context, p->z, p->nUsed, sqlite3_free);
690 }
691 }
692 #endif
693
694 /*
695 ** The main and default testset
696 */
697 void testset_main(void){
698 int i; /* Loop counter */
699 int n; /* iteration count */
700 int sz; /* Size of the tables */
701 int maxb; /* Maximum swizzled value */
702 unsigned x1 = 0, x2 = 0; /* Parameters */
703 int len = 0; /* Length of the zNum[] string */
704 char zNum[2000]; /* A number name */
705
706 sz = n = g.szTest*500;
707 zNum[0] = 0;
708 maxb = roundup_allones(sz);
709 speedtest1_begin_test(100, "%d INSERTs into table with no index", n);
710 speedtest1_exec("BEGIN");
711 speedtest1_exec("CREATE%s TABLE z1(a INTEGER %s, b INTEGER %s, c TEXT %s);",
712 isTemp(9), g.zNN, g.zNN, g.zNN);
713 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2,?3); -- %d times", n);
714 for(i=1; i<=n; i++){
715 x1 = swizzle(i,maxb);
716 speedtest1_numbername(x1, zNum, sizeof(zNum));
717 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
718 sqlite3_bind_int(g.pStmt, 2, i);
719 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
720 speedtest1_run();
721 }
722 speedtest1_exec("COMMIT");
723 speedtest1_end_test();
724
725
726 n = sz;
727 speedtest1_begin_test(110, "%d ordered INSERTS with one index/PK", n);
728 speedtest1_exec("BEGIN");
729 speedtest1_exec(
730 "CREATE%s TABLE z2(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
731 isTemp(5), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
732 speedtest1_prepare("INSERT INTO z2 VALUES(?1,?2,?3); -- %d times", n);
733 for(i=1; i<=n; i++){
734 x1 = swizzle(i,maxb);
735 speedtest1_numbername(x1, zNum, sizeof(zNum));
736 sqlite3_bind_int(g.pStmt, 1, i);
737 sqlite3_bind_int64(g.pStmt, 2, (sqlite3_int64)x1);
738 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
739 speedtest1_run();
740 }
741 speedtest1_exec("COMMIT");
742 speedtest1_end_test();
743
744
745 n = sz;
746 speedtest1_begin_test(120, "%d unordered INSERTS with one index/PK", n);
747 speedtest1_exec("BEGIN");
748 speedtest1_exec(
749 "CREATE%s TABLE t3(a INTEGER %s %s, b INTEGER %s, c TEXT %s) %s",
750 isTemp(3), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
751 speedtest1_prepare("INSERT INTO t3 VALUES(?1,?2,?3); -- %d times", n);
752 for(i=1; i<=n; i++){
753 x1 = swizzle(i,maxb);
754 speedtest1_numbername(x1, zNum, sizeof(zNum));
755 sqlite3_bind_int(g.pStmt, 2, i);
756 sqlite3_bind_int64(g.pStmt, 1, (sqlite3_int64)x1);
757 sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
758 speedtest1_run();
759 }
760 speedtest1_exec("COMMIT");
761 speedtest1_end_test();
762
763 #if SQLITE_VERSION_NUMBER<3005004
764 sqlite3_create_function(g.db, "group_concat", 1, SQLITE_UTF8, 0,
765 0, groupStep, groupFinal);
766 #endif
767
768 n = 25;
769 speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
770 speedtest1_exec("BEGIN");
771 speedtest1_prepare(
772 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
773 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
774 );
775 for(i=1; i<=n; i++){
776 if( (i-1)%g.nRepeat==0 ){
777 x1 = speedtest1_random()%maxb;
778 x2 = speedtest1_random()%10 + sz/5000 + x1;
779 }
780 sqlite3_bind_int(g.pStmt, 1, x1);
781 sqlite3_bind_int(g.pStmt, 2, x2);
782 speedtest1_run();
783 }
784 speedtest1_exec("COMMIT");
785 speedtest1_end_test();
786
787
788 n = 10;
789 speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
790 speedtest1_exec("BEGIN");
791 speedtest1_prepare(
792 "SELECT count(*), avg(b), sum(length(c)), group_concat(c) FROM z1\n"
793 " WHERE c LIKE ?1; -- %d times", n
794 );
795 for(i=1; i<=n; i++){
796 if( (i-1)%g.nRepeat==0 ){
797 x1 = speedtest1_random()%maxb;
798 zNum[0] = '%';
799 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
800 zNum[len] = '%';
801 zNum[len+1] = 0;
802 }
803 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
804 speedtest1_run();
805 }
806 speedtest1_exec("COMMIT");
807 speedtest1_end_test();
808
809
810 n = 10;
811 speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
812 speedtest1_exec("BEGIN");
813 speedtest1_prepare(
814 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
815 " ORDER BY a; -- %d times", n
816 );
817 for(i=1; i<=n; i++){
818 if( (i-1)%g.nRepeat==0 ){
819 x1 = speedtest1_random()%maxb;
820 zNum[0] = '%';
821 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
822 zNum[len] = '%';
823 zNum[len+1] = 0;
824 }
825 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
826 speedtest1_run();
827 }
828 speedtest1_exec("COMMIT");
829 speedtest1_end_test();
830
831 n = 10; /* g.szTest/5; */
832 speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
833 speedtest1_exec("BEGIN");
834 speedtest1_prepare(
835 "SELECT a, b, c FROM z1 WHERE c LIKE ?1\n"
836 " ORDER BY a LIMIT 10; -- %d times", n
837 );
838 for(i=1; i<=n; i++){
839 if( (i-1)%g.nRepeat==0 ){
840 x1 = speedtest1_random()%maxb;
841 zNum[0] = '%';
842 len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
843 zNum[len] = '%';
844 zNum[len+1] = 0;
845 }
846 sqlite3_bind_text(g.pStmt, 1, zNum, len+1, SQLITE_STATIC);
847 speedtest1_run();
848 }
849 speedtest1_exec("COMMIT");
850 speedtest1_end_test();
851
852
853 speedtest1_begin_test(150, "CREATE INDEX five times");
854 speedtest1_exec("BEGIN;");
855 speedtest1_exec("CREATE UNIQUE INDEX t1b ON z1(b);");
856 speedtest1_exec("CREATE INDEX t1c ON z1(c);");
857 speedtest1_exec("CREATE UNIQUE INDEX t2b ON z2(b);");
858 speedtest1_exec("CREATE INDEX t2c ON z2(c DESC);");
859 speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
860 speedtest1_exec("COMMIT;");
861 speedtest1_end_test();
862
863
864 n = sz/5;
865 speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
866 speedtest1_exec("BEGIN");
867 speedtest1_prepare(
868 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
869 " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
870 );
871 for(i=1; i<=n; i++){
872 if( (i-1)%g.nRepeat==0 ){
873 x1 = speedtest1_random()%maxb;
874 x2 = speedtest1_random()%10 + sz/5000 + x1;
875 }
876 sqlite3_bind_int(g.pStmt, 1, x1);
877 sqlite3_bind_int(g.pStmt, 2, x2);
878 speedtest1_run();
879 }
880 speedtest1_exec("COMMIT");
881 speedtest1_end_test();
882
883
884 n = sz/5;
885 speedtest1_begin_test(161, "%d SELECTS, numeric BETWEEN, PK", n);
886 speedtest1_exec("BEGIN");
887 speedtest1_prepare(
888 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z2\n"
889 " WHERE a BETWEEN ?1 AND ?2; -- %d times", n
890 );
891 for(i=1; i<=n; i++){
892 if( (i-1)%g.nRepeat==0 ){
893 x1 = speedtest1_random()%maxb;
894 x2 = speedtest1_random()%10 + sz/5000 + x1;
895 }
896 sqlite3_bind_int(g.pStmt, 1, x1);
897 sqlite3_bind_int(g.pStmt, 2, x2);
898 speedtest1_run();
899 }
900 speedtest1_exec("COMMIT");
901 speedtest1_end_test();
902
903
904 n = sz/5;
905 speedtest1_begin_test(170, "%d SELECTS, text BETWEEN, indexed", n);
906 speedtest1_exec("BEGIN");
907 speedtest1_prepare(
908 "SELECT count(*), avg(b), sum(length(c)), group_concat(a) FROM z1\n"
909 " WHERE c BETWEEN ?1 AND (?1||'~'); -- %d times", n
910 );
911 for(i=1; i<=n; i++){
912 if( (i-1)%g.nRepeat==0 ){
913 x1 = swizzle(i, maxb);
914 len = speedtest1_numbername(x1, zNum, sizeof(zNum)-1);
915 }
916 sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
917 speedtest1_run();
918 }
919 speedtest1_exec("COMMIT");
920 speedtest1_end_test();
921
922 n = sz;
923 speedtest1_begin_test(180, "%d INSERTS with three indexes", n);
924 speedtest1_exec("BEGIN");
925 speedtest1_exec(
926 "CREATE%s TABLE t4(\n"
927 " a INTEGER %s %s,\n"
928 " b INTEGER %s,\n"
929 " c TEXT %s\n"
930 ") %s",
931 isTemp(1), g.zNN, g.zPK, g.zNN, g.zNN, g.zWR);
932 speedtest1_exec("CREATE INDEX t4b ON t4(b)");
933 speedtest1_exec("CREATE INDEX t4c ON t4(c)");
934 speedtest1_exec("INSERT INTO t4 SELECT * FROM z1");
935 speedtest1_exec("COMMIT");
936 speedtest1_end_test();
937
938 n = sz;
939 speedtest1_begin_test(190, "DELETE and REFILL one table", n);
940 speedtest1_exec("DELETE FROM z2;");
941 speedtest1_exec("INSERT INTO z2 SELECT * FROM z1;");
942 speedtest1_end_test();
943
944
945 speedtest1_begin_test(200, "VACUUM");
946 speedtest1_exec("VACUUM");
947 speedtest1_end_test();
948
949
950 speedtest1_begin_test(210, "ALTER TABLE ADD COLUMN, and query");
951 speedtest1_exec("ALTER TABLE z2 ADD COLUMN d INT DEFAULT 123");
952 speedtest1_exec("SELECT sum(d) FROM z2");
953 speedtest1_end_test();
954
955
956 n = sz/5;
957 speedtest1_begin_test(230, "%d UPDATES, numeric BETWEEN, indexed", n);
958 speedtest1_exec("BEGIN");
959 speedtest1_prepare(
960 "UPDATE z2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
961 );
962 for(i=1; i<=n; i++){
963 x1 = speedtest1_random()%maxb;
964 x2 = speedtest1_random()%10 + sz/5000 + x1;
965 sqlite3_bind_int(g.pStmt, 1, x1);
966 sqlite3_bind_int(g.pStmt, 2, x2);
967 speedtest1_run();
968 }
969 speedtest1_exec("COMMIT");
970 speedtest1_end_test();
971
972
973 n = sz;
974 speedtest1_begin_test(240, "%d UPDATES of individual rows", n);
975 speedtest1_exec("BEGIN");
976 speedtest1_prepare(
977 "UPDATE z2 SET d=b*3 WHERE a=?1; -- %d times", n
978 );
979 for(i=1; i<=n; i++){
980 x1 = speedtest1_random()%sz + 1;
981 sqlite3_bind_int(g.pStmt, 1, x1);
982 speedtest1_run();
983 }
984 speedtest1_exec("COMMIT");
985 speedtest1_end_test();
986
987 speedtest1_begin_test(250, "One big UPDATE of the whole %d-row table", sz);
988 speedtest1_exec("UPDATE z2 SET d=b*4");
989 speedtest1_end_test();
990
991
992 speedtest1_begin_test(260, "Query added column after filling");
993 speedtest1_exec("SELECT sum(d) FROM z2");
994 speedtest1_end_test();
995
996
997
998 n = sz/5;
999 speedtest1_begin_test(270, "%d DELETEs, numeric BETWEEN, indexed", n);
1000 speedtest1_exec("BEGIN");
1001 speedtest1_prepare(
1002 "DELETE FROM z2 WHERE b BETWEEN ?1 AND ?2; -- %d times", n
1003 );
1004 for(i=1; i<=n; i++){
1005 x1 = speedtest1_random()%maxb + 1;
1006 x2 = speedtest1_random()%10 + sz/5000 + x1;
1007 sqlite3_bind_int(g.pStmt, 1, x1);
1008 sqlite3_bind_int(g.pStmt, 2, x2);
1009 speedtest1_run();
1010 }
1011 speedtest1_exec("COMMIT");
1012 speedtest1_end_test();
1013
1014
1015 n = sz;
1016 speedtest1_begin_test(280, "%d DELETEs of individual rows", n);
1017 speedtest1_exec("BEGIN");
1018 speedtest1_prepare(
1019 "DELETE FROM t3 WHERE a=?1; -- %d times", n
1020 );
1021 for(i=1; i<=n; i++){
1022 x1 = speedtest1_random()%sz + 1;
1023 sqlite3_bind_int(g.pStmt, 1, x1);
1024 speedtest1_run();
1025 }
1026 speedtest1_exec("COMMIT");
1027 speedtest1_end_test();
1028
1029
1030 speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
1031 speedtest1_exec("REPLACE INTO z2(a,b,c) SELECT a,b,c FROM z1");
1032 speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM z1");
1033 speedtest1_end_test();
1034
1035 speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
1036 speedtest1_exec("DELETE FROM z2;");
1037 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1038 " SELECT a,b,c FROM z1 WHERE (b&1)==(a&1);");
1039 speedtest1_exec("INSERT INTO z2(a,b,c)\n"
1040 " SELECT a,b,c FROM z1 WHERE (b&1)<>(a&1);");
1041 speedtest1_end_test();
1042
1043
1044 n = sz/5;
1045 speedtest1_begin_test(310, "%d four-ways joins", n);
1046 speedtest1_exec("BEGIN");
1047 speedtest1_prepare(
1048 "SELECT z1.c FROM z1, z2, t3, t4\n"
1049 " WHERE t4.a BETWEEN ?1 AND ?2\n"
1050 " AND t3.a=t4.b\n"
1051 " AND z2.a=t3.b\n"
1052 " AND z1.c=z2.c"
1053 );
1054 for(i=1; i<=n; i++){
1055 x1 = speedtest1_random()%sz + 1;
1056 x2 = speedtest1_random()%10 + x1 + 4;
1057 sqlite3_bind_int(g.pStmt, 1, x1);
1058 sqlite3_bind_int(g.pStmt, 2, x2);
1059 speedtest1_run();
1060 }
1061 speedtest1_exec("COMMIT");
1062 speedtest1_end_test();
1063
1064 speedtest1_begin_test(320, "subquery in result set", n);
1065 speedtest1_prepare(
1066 "SELECT sum(a), max(c),\n"
1067 " avg((SELECT a FROM z2 WHERE 5+z2.b=z1.b) AND rowid<?1), max(c)\n"
1068 " FROM z1 WHERE rowid<?1;"
1069 );
1070 sqlite3_bind_int(g.pStmt, 1, est_square_root(g.szTest)*50);
1071 speedtest1_run();
1072 speedtest1_end_test();
1073
1074 sz = n = g.szTest*700;
1075 zNum[0] = 0;
1076 maxb = roundup_allones(sz/3);
1077 speedtest1_begin_test(400, "%d REPLACE ops on an IPK", n);
1078 speedtest1_exec("BEGIN");
1079 speedtest1_exec("CREATE%s TABLE t5(a INTEGER PRIMARY KEY, b %s);",
1080 isTemp(9), g.zNN);
1081 speedtest1_prepare("REPLACE INTO t5 VALUES(?1,?2); -- %d times",n);
1082 for(i=1; i<=n; i++){
1083 x1 = swizzle(i,maxb);
1084 speedtest1_numbername(i, zNum, sizeof(zNum));
1085 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1086 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1087 speedtest1_run();
1088 }
1089 speedtest1_exec("COMMIT");
1090 speedtest1_end_test();
1091 speedtest1_begin_test(410, "%d SELECTS on an IPK", n);
1092 speedtest1_prepare("SELECT b FROM t5 WHERE a=?1; -- %d times",n);
1093 for(i=1; i<=n; i++){
1094 x1 = swizzle(i,maxb);
1095 sqlite3_bind_int(g.pStmt, 1, (sqlite3_int64)x1);
1096 speedtest1_run();
1097 }
1098 speedtest1_end_test();
1099
1100 sz = n = g.szTest*700;
1101 zNum[0] = 0;
1102 maxb = roundup_allones(sz/3);
1103 speedtest1_begin_test(500, "%d REPLACE on TEXT PK", n);
1104 speedtest1_exec("BEGIN");
1105 speedtest1_exec("CREATE%s TABLE t6(a TEXT PRIMARY KEY, b %s)%s;",
1106 isTemp(9), g.zNN,
1107 sqlite3_libversion_number()>=3008002 ? "WITHOUT ROWID" : "");
1108 speedtest1_prepare("REPLACE INTO t6 VALUES(?1,?2); -- %d times",n);
1109 for(i=1; i<=n; i++){
1110 x1 = swizzle(i,maxb);
1111 speedtest1_numbername(x1, zNum, sizeof(zNum));
1112 sqlite3_bind_int(g.pStmt, 2, i);
1113 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1114 speedtest1_run();
1115 }
1116 speedtest1_exec("COMMIT");
1117 speedtest1_end_test();
1118 speedtest1_begin_test(510, "%d SELECTS on a TEXT PK", n);
1119 speedtest1_prepare("SELECT b FROM t6 WHERE a=?1; -- %d times",n);
1120 for(i=1; i<=n; i++){
1121 x1 = swizzle(i,maxb);
1122 speedtest1_numbername(x1, zNum, sizeof(zNum));
1123 sqlite3_bind_text(g.pStmt, 1, zNum, -1, SQLITE_STATIC);
1124 speedtest1_run();
1125 }
1126 speedtest1_end_test();
1127 speedtest1_begin_test(520, "%d SELECT DISTINCT", n);
1128 speedtest1_exec("SELECT DISTINCT b FROM t5;");
1129 speedtest1_exec("SELECT DISTINCT b FROM t6;");
1130 speedtest1_end_test();
1131
1132
1133 speedtest1_begin_test(980, "PRAGMA integrity_check");
1134 speedtest1_exec("PRAGMA integrity_check");
1135 speedtest1_end_test();
1136
1137
1138 speedtest1_begin_test(990, "ANALYZE");
1139 speedtest1_exec("ANALYZE");
1140 speedtest1_end_test();
1141 }
1142
1143 /*
1144 ** A testset for common table expressions. This exercises code
1145 ** for views, subqueries, co-routines, etc.
1146 */
1147 void testset_cte(void){
1148 static const char *azPuzzle[] = {
1149 /* Easy */
1150 "534...9.."
1151 "67.195..."
1152 ".98....6."
1153 "8...6...3"
1154 "4..8.3..1"
1155 "....2...6"
1156 ".6....28."
1157 "...419..5"
1158 "...28..79",
1159
1160 /* Medium */
1161 "53....9.."
1162 "6..195..."
1163 ".98....6."
1164 "8...6...3"
1165 "4..8.3..1"
1166 "....2...6"
1167 ".6....28."
1168 "...419..5"
1169 "....8..79",
1170
1171 /* Hard */
1172 "53......."
1173 "6..195..."
1174 ".98....6."
1175 "8...6...3"
1176 "4..8.3..1"
1177 "....2...6"
1178 ".6....28."
1179 "...419..5"
1180 "....8..79",
1181 };
1182 const char *zPuz;
1183 double rSpacing;
1184 int nElem;
1185
1186 if( g.szTest<25 ){
1187 zPuz = azPuzzle[0];
1188 }else if( g.szTest<70 ){
1189 zPuz = azPuzzle[1];
1190 }else{
1191 zPuz = azPuzzle[2];
1192 }
1193 speedtest1_begin_test(100, "Sudoku with recursive 'digits'");
1194 speedtest1_prepare(
1195 "WITH RECURSIVE\n"
1196 " input(sud) AS (VALUES(?1)),\n"
1197 " digits(z,lp) AS (\n"
1198 " VALUES('1', 1)\n"
1199 " UNION ALL\n"
1200 " SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9\n"
1201 " ),\n"
1202 " x(s, ind) AS (\n"
1203 " SELECT sud, instr(sud, '.') FROM input\n"
1204 " UNION ALL\n"
1205 " SELECT\n"
1206 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1207 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1208 " FROM x, digits AS z\n"
1209 " WHERE ind>0\n"
1210 " AND NOT EXISTS (\n"
1211 " SELECT 1\n"
1212 " FROM digits AS lp\n"
1213 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1214 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1215 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1216 " + ((ind-1)/27) * 27 + lp\n"
1217 " + ((lp-1) / 3) * 6, 1)\n"
1218 " )\n"
1219 " )\n"
1220 "SELECT s FROM x WHERE ind=0;"
1221 );
1222 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1223 speedtest1_run();
1224 speedtest1_end_test();
1225
1226 speedtest1_begin_test(200, "Sudoku with VALUES 'digits'");
1227 speedtest1_prepare(
1228 "WITH RECURSIVE\n"
1229 " input(sud) AS (VALUES(?1)),\n"
1230 " digits(z,lp) AS (VALUES('1',1),('2',2),('3',3),('4',4),('5',5),\n"
1231 " ('6',6),('7',7),('8',8),('9',9)),\n"
1232 " x(s, ind) AS (\n"
1233 " SELECT sud, instr(sud, '.') FROM input\n"
1234 " UNION ALL\n"
1235 " SELECT\n"
1236 " substr(s, 1, ind-1) || z || substr(s, ind+1),\n"
1237 " instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )\n"
1238 " FROM x, digits AS z\n"
1239 " WHERE ind>0\n"
1240 " AND NOT EXISTS (\n"
1241 " SELECT 1\n"
1242 " FROM digits AS lp\n"
1243 " WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)\n"
1244 " OR z.z = substr(s, ((ind-1)%%9) + (lp-1)*9 + 1, 1)\n"
1245 " OR z.z = substr(s, (((ind-1)/3) %% 3) * 3\n"
1246 " + ((ind-1)/27) * 27 + lp\n"
1247 " + ((lp-1) / 3) * 6, 1)\n"
1248 " )\n"
1249 " )\n"
1250 "SELECT s FROM x WHERE ind=0;"
1251 );
1252 sqlite3_bind_text(g.pStmt, 1, zPuz, -1, SQLITE_STATIC);
1253 speedtest1_run();
1254 speedtest1_end_test();
1255
1256 rSpacing = 5.0/g.szTest;
1257 speedtest1_begin_test(300, "Mandelbrot Set with spacing=%f", rSpacing);
1258 speedtest1_prepare(
1259 "WITH RECURSIVE \n"
1260 " xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+?1 FROM xaxis WHERE x<1.2),\n"
1261 " yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+?2 FROM yaxis WHERE y<1.0),\n"
1262 " m(iter, cx, cy, x, y) AS (\n"
1263 " SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis\n"
1264 " UNION ALL\n"
1265 " SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \n"
1266 " WHERE (x*x + y*y) < 4.0 AND iter<28\n"
1267 " ),\n"
1268 " m2(iter, cx, cy) AS (\n"
1269 " SELECT max(iter), cx, cy FROM m GROUP BY cx, cy\n"
1270 " ),\n"
1271 " a(t) AS (\n"
1272 " SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') \n"
1273 " FROM m2 GROUP BY cy\n"
1274 " )\n"
1275 "SELECT group_concat(rtrim(t),x'0a') FROM a;"
1276 );
1277 sqlite3_bind_double(g.pStmt, 1, rSpacing*.05);
1278 sqlite3_bind_double(g.pStmt, 2, rSpacing);
1279 speedtest1_run();
1280 speedtest1_end_test();
1281
1282 nElem = 10000*g.szTest;
1283 speedtest1_begin_test(400, "EXCEPT operator on %d-element tables", nElem);
1284 speedtest1_prepare(
1285 "WITH RECURSIVE \n"
1286 " z1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM z1 WHERE x<%d),\n"
1287 " z2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM z2 WHERE y<%d)\n"
1288 "SELECT count(x), avg(x) FROM (\n"
1289 " SELECT x FROM z1 EXCEPT SELECT y FROM z2 ORDER BY 1\n"
1290 ");",
1291 nElem, nElem
1292 );
1293 speedtest1_run();
1294 speedtest1_end_test();
1295 }
1296
1297 /*
1298 ** Compute a pseudo-random floating point ascii number.
1299 */
1300 void speedtest1_random_ascii_fp(char *zFP){
1301 int x = speedtest1_random();
1302 int y = speedtest1_random();
1303 int z;
1304 z = y%10;
1305 if( z<0 ) z = -z;
1306 y /= 10;
1307 sqlite3_snprintf(100,zFP,"%d.%de%d",y,z,x%200);
1308 }
1309
1310 /*
1311 ** A testset for floating-point numbers.
1312 */
1313 void testset_fp(void){
1314 int n;
1315 int i;
1316 char zFP1[100];
1317 char zFP2[100];
1318
1319 n = g.szTest*5000;
1320 speedtest1_begin_test(100, "Fill a table with %d FP values", n*2);
1321 speedtest1_exec("BEGIN");
1322 speedtest1_exec("CREATE%s TABLE z1(a REAL %s, b REAL %s);",
1323 isTemp(1), g.zNN, g.zNN);
1324 speedtest1_prepare("INSERT INTO z1 VALUES(?1,?2); -- %d times", n);
1325 for(i=1; i<=n; i++){
1326 speedtest1_random_ascii_fp(zFP1);
1327 speedtest1_random_ascii_fp(zFP2);
1328 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1329 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1330 speedtest1_run();
1331 }
1332 speedtest1_exec("COMMIT");
1333 speedtest1_end_test();
1334
1335 n = g.szTest/25 + 2;
1336 speedtest1_begin_test(110, "%d range queries", n);
1337 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1338 for(i=1; i<=n; i++){
1339 speedtest1_random_ascii_fp(zFP1);
1340 speedtest1_random_ascii_fp(zFP2);
1341 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1342 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1343 speedtest1_run();
1344 }
1345 speedtest1_end_test();
1346
1347 speedtest1_begin_test(120, "CREATE INDEX three times");
1348 speedtest1_exec("BEGIN;");
1349 speedtest1_exec("CREATE INDEX t1a ON z1(a);");
1350 speedtest1_exec("CREATE INDEX t1b ON z1(b);");
1351 speedtest1_exec("CREATE INDEX t1ab ON z1(a,b);");
1352 speedtest1_exec("COMMIT;");
1353 speedtest1_end_test();
1354
1355 n = g.szTest/3 + 2;
1356 speedtest1_begin_test(130, "%d indexed range queries", n);
1357 speedtest1_prepare("SELECT sum(b) FROM z1 WHERE a BETWEEN ?1 AND ?2");
1358 for(i=1; i<=n; i++){
1359 speedtest1_random_ascii_fp(zFP1);
1360 speedtest1_random_ascii_fp(zFP2);
1361 sqlite3_bind_text(g.pStmt, 1, zFP1, -1, SQLITE_STATIC);
1362 sqlite3_bind_text(g.pStmt, 2, zFP2, -1, SQLITE_STATIC);
1363 speedtest1_run();
1364 }
1365 speedtest1_end_test();
1366
1367 n = g.szTest*5000;
1368 speedtest1_begin_test(140, "%d calls to round()", n);
1369 speedtest1_exec("SELECT sum(round(a,2)+round(b,4)) FROM z1;");
1370 speedtest1_end_test();
1371
1372
1373 speedtest1_begin_test(150, "%d printf() calls", n*4);
1374 speedtest1_exec(
1375 "WITH c(fmt) AS (VALUES('%%g'),('%%e'),('%%!g'),('%%.20f'))"
1376 "SELECT sum(printf(fmt,a)) FROM z1, c"
1377 );
1378 speedtest1_end_test();
1379 }
1380
1381 #ifdef SQLITE_ENABLE_RTREE
1382 /* Generate two numbers between 1 and mx. The first number is less than
1383 ** the second. Usually the numbers are near each other but can sometimes
1384 ** be far apart.
1385 */
1386 static void twoCoords(
1387 int p1, int p2, /* Parameters adjusting sizes */
1388 unsigned mx, /* Range of 1..mx */
1389 unsigned *pX0, unsigned *pX1 /* OUT: write results here */
1390 ){
1391 unsigned d, x0, x1, span;
1392
1393 span = mx/100 + 1;
1394 if( speedtest1_random()%3==0 ) span *= p1;
1395 if( speedtest1_random()%p2==0 ) span = mx/2;
1396 d = speedtest1_random()%span + 1;
1397 x0 = speedtest1_random()%(mx-d) + 1;
1398 x1 = x0 + d;
1399 *pX0 = x0;
1400 *pX1 = x1;
1401 }
1402 #endif
1403
1404 #ifdef SQLITE_ENABLE_RTREE
1405 /* The following routine is an R-Tree geometry callback. It returns
1406 ** true if the object overlaps a slice on the Y coordinate between the
1407 ** two values given as arguments. In other words
1408 **
1409 ** SELECT count(*) FROM rt1 WHERE id MATCH xslice(10,20);
1410 **
1411 ** Is the same as saying:
1412 **
1413 ** SELECT count(*) FROM rt1 WHERE y1>=10 AND y0<=20;
1414 */
1415 static int xsliceGeometryCallback(
1416 sqlite3_rtree_geometry *p,
1417 int nCoord,
1418 double *aCoord,
1419 int *pRes
1420 ){
1421 *pRes = aCoord[3]>=p->aParam[0] && aCoord[2]<=p->aParam[1];
1422 return SQLITE_OK;
1423 }
1424 #endif /* SQLITE_ENABLE_RTREE */
1425
1426 #ifdef SQLITE_ENABLE_RTREE
1427 /*
1428 ** A testset for the R-Tree virtual table
1429 */
1430 void testset_rtree(int p1, int p2){
1431 unsigned i, n;
1432 unsigned mxCoord;
1433 unsigned x0, x1, y0, y1, z0, z1;
1434 unsigned iStep;
1435 unsigned mxRowid;
1436 int *aCheck = sqlite3_malloc( sizeof(int)*g.szTest*500 );
1437
1438 mxCoord = 15000;
1439 mxRowid = n = g.szTest*500;
1440 speedtest1_begin_test(100, "%d INSERTs into an r-tree", n);
1441 speedtest1_exec("BEGIN");
1442 speedtest1_exec("CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1,z0,z1)");
1443 speedtest1_prepare("INSERT INTO rt1(id,x0,x1,y0,y1,z0,z1)"
1444 "VALUES(?1,?2,?3,?4,?5,?6,?7)");
1445 for(i=1; i<=n; i++){
1446 twoCoords(p1, p2, mxCoord, &x0, &x1);
1447 twoCoords(p1, p2, mxCoord, &y0, &y1);
1448 twoCoords(p1, p2, mxCoord, &z0, &z1);
1449 sqlite3_bind_int(g.pStmt, 1, i);
1450 sqlite3_bind_int(g.pStmt, 2, x0);
1451 sqlite3_bind_int(g.pStmt, 3, x1);
1452 sqlite3_bind_int(g.pStmt, 4, y0);
1453 sqlite3_bind_int(g.pStmt, 5, y1);
1454 sqlite3_bind_int(g.pStmt, 6, z0);
1455 sqlite3_bind_int(g.pStmt, 7, z1);
1456 speedtest1_run();
1457 }
1458 speedtest1_exec("COMMIT");
1459 speedtest1_end_test();
1460
1461 speedtest1_begin_test(101, "Copy from rtree to a regular table");
1462 speedtest1_exec("CREATE TABLE z1(id INTEGER PRIMARY KEY,x0,x1,y0,y1,z0,z1)");
1463 speedtest1_exec("INSERT INTO z1 SELECT * FROM rt1");
1464 speedtest1_end_test();
1465
1466 n = g.szTest*200;
1467 speedtest1_begin_test(110, "%d one-dimensional intersect slice queries", n);
1468 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x0>=?1 AND x1<=?2");
1469 iStep = mxCoord/n;
1470 for(i=0; i<n; i++){
1471 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1472 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1473 speedtest1_run();
1474 aCheck[i] = atoi(g.zResult);
1475 }
1476 speedtest1_end_test();
1477
1478 if( g.bVerify ){
1479 n = g.szTest*200;
1480 speedtest1_begin_test(111, "Verify result from 1-D intersect slice queries");
1481 speedtest1_prepare("SELECT count(*) FROM z1 WHERE x0>=?1 AND x1<=?2");
1482 iStep = mxCoord/n;
1483 for(i=0; i<n; i++){
1484 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1485 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1486 speedtest1_run();
1487 if( aCheck[i]!=atoi(g.zResult) ){
1488 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1489 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1490 }
1491 }
1492 speedtest1_end_test();
1493 }
1494
1495 n = g.szTest*200;
1496 speedtest1_begin_test(120, "%d one-dimensional overlap slice queries", n);
1497 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE y1>=?1 AND y0<=?2");
1498 iStep = mxCoord/n;
1499 for(i=0; i<n; i++){
1500 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1501 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1502 speedtest1_run();
1503 aCheck[i] = atoi(g.zResult);
1504 }
1505 speedtest1_end_test();
1506
1507 if( g.bVerify ){
1508 n = g.szTest*200;
1509 speedtest1_begin_test(121, "Verify result from 1-D overlap slice queries");
1510 speedtest1_prepare("SELECT count(*) FROM z1 WHERE y1>=?1 AND y0<=?2");
1511 iStep = mxCoord/n;
1512 for(i=0; i<n; i++){
1513 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1514 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1515 speedtest1_run();
1516 if( aCheck[i]!=atoi(g.zResult) ){
1517 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1518 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1519 }
1520 }
1521 speedtest1_end_test();
1522 }
1523
1524
1525 n = g.szTest*200;
1526 speedtest1_begin_test(125, "%d custom geometry callback queries", n);
1527 sqlite3_rtree_geometry_callback(g.db, "xslice", xsliceGeometryCallback, 0);
1528 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE id MATCH xslice(?1,?2)");
1529 iStep = mxCoord/n;
1530 for(i=0; i<n; i++){
1531 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1532 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1533 speedtest1_run();
1534 if( aCheck[i]!=atoi(g.zResult) ){
1535 fatal_error("Count disagree step %d: %d..%d. %d vs %d",
1536 i, i*iStep, (i+1)*iStep, aCheck[i], atoi(g.zResult));
1537 }
1538 }
1539 speedtest1_end_test();
1540
1541 n = g.szTest*400;
1542 speedtest1_begin_test(130, "%d three-dimensional intersect box queries", n);
1543 speedtest1_prepare("SELECT count(*) FROM rt1 WHERE x1>=?1 AND x0<=?2"
1544 " AND y1>=?1 AND y0<=?2 AND z1>=?1 AND z0<=?2");
1545 iStep = mxCoord/n;
1546 for(i=0; i<n; i++){
1547 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1548 sqlite3_bind_int(g.pStmt, 2, (i+1)*iStep);
1549 speedtest1_run();
1550 aCheck[i] = atoi(g.zResult);
1551 }
1552 speedtest1_end_test();
1553
1554 n = g.szTest*500;
1555 speedtest1_begin_test(140, "%d rowid queries", n);
1556 speedtest1_prepare("SELECT * FROM rt1 WHERE id=?1");
1557 for(i=1; i<=n; i++){
1558 sqlite3_bind_int(g.pStmt, 1, i);
1559 speedtest1_run();
1560 }
1561 speedtest1_end_test();
1562
1563 n = g.szTest*50;
1564 speedtest1_begin_test(150, "%d UPDATEs using rowid", n);
1565 speedtest1_prepare("UPDATE rt1 SET x0=x0+100, x1=x1+100 WHERE id=?1");
1566 for(i=1; i<=n; i++){
1567 sqlite3_bind_int(g.pStmt, 1, (i*251)%mxRowid + 1);
1568 speedtest1_run();
1569 }
1570 speedtest1_end_test();
1571
1572 n = g.szTest*5;
1573 speedtest1_begin_test(155, "%d UPDATEs using one-dimensional overlap", n);
1574 speedtest1_prepare("UPDATE rt1 SET x0=x0-100, x1=x1-100"
1575 " WHERE y1>=?1 AND y0<=?1+5");
1576 iStep = mxCoord/n;
1577 for(i=0; i<n; i++){
1578 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1579 speedtest1_run();
1580 aCheck[i] = atoi(g.zResult);
1581 }
1582 speedtest1_end_test();
1583
1584 n = g.szTest*50;
1585 speedtest1_begin_test(160, "%d DELETEs using rowid", n);
1586 speedtest1_prepare("DELETE FROM rt1 WHERE id=?1");
1587 for(i=1; i<=n; i++){
1588 sqlite3_bind_int(g.pStmt, 1, (i*257)%mxRowid + 1);
1589 speedtest1_run();
1590 }
1591 speedtest1_end_test();
1592
1593
1594 n = g.szTest*5;
1595 speedtest1_begin_test(165, "%d DELETEs using one-dimensional overlap", n);
1596 speedtest1_prepare("DELETE FROM rt1 WHERE y1>=?1 AND y0<=?1+5");
1597 iStep = mxCoord/n;
1598 for(i=0; i<n; i++){
1599 sqlite3_bind_int(g.pStmt, 1, i*iStep);
1600 speedtest1_run();
1601 aCheck[i] = atoi(g.zResult);
1602 }
1603 speedtest1_end_test();
1604
1605 speedtest1_begin_test(170, "Restore deleted entries using INSERT OR IGNORE");
1606 speedtest1_exec("INSERT OR IGNORE INTO rt1 SELECT * FROM z1");
1607 speedtest1_end_test();
1608 }
1609 #endif /* SQLITE_ENABLE_RTREE */
1610
1611 /*
1612 ** A testset that does key/value storage on tables with many columns.
1613 ** This is the kind of workload generated by ORMs such as CoreData.
1614 */
1615 void testset_orm(void){
1616 unsigned i, j, n;
1617 unsigned nRow;
1618 unsigned x1, len;
1619 char zNum[2000]; /* A number name */
1620 static const char zType[] = /* Types for all non-PK columns, in order */
1621 "IBBIIITIVVITBTBFBFITTFBTBVBVIFTBBFITFFVBIFIVBVVVBTVTIBBFFIVIBTB"
1622 "TVTTFTVTVFFIITIFBITFTTFFFVBIIBTTITFTFFVVVFIIITVBBVFFTVVB";
1623
1624 nRow = n = g.szTest*250;
1625 speedtest1_begin_test(100, "Fill %d rows", n);
1626 speedtest1_exec(
1627 "BEGIN;"
1628 "CREATE TABLE ZLOOKSLIKECOREDATA ("
1629 " ZPK INTEGER PRIMARY KEY,"
1630 " ZTERMFITTINGHOUSINGCOMMAND INTEGER,"
1631 " ZBRIEFGOBYDODGERHEIGHT BLOB,"
1632 " ZCAPABLETRIPDOORALMOND BLOB,"
1633 " ZDEPOSITPAIRCOLLEGECOMET INTEGER,"
1634 " ZFRAMEENTERSIMPLEMOUTH INTEGER,"
1635 " ZHOPEFULGATEHOLECHALK INTEGER,"
1636 " ZSLEEPYUSERGRANDBOWL TIMESTAMP,"
1637 " ZDEWPEACHCAREERCELERY INTEGER,"
1638 " ZHANGERLITHIUMDINNERMEET VARCHAR,"
1639 " ZCLUBRELEASELIZARDADVICE VARCHAR,"
1640 " ZCHARGECLICKHUMANEHIRE INTEGER,"
1641 " ZFINGERDUEPIZZAOPTION TIMESTAMP,"
1642 " ZFLYINGDOCTORTABLEMELODY BLOB,"
1643 " ZLONGFINLEAVEIMAGEOIL TIMESTAMP,"
1644 " ZFAMILYVISUALOWNERMATTER BLOB,"
1645 " ZGOLDYOUNGINITIALNOSE FLOAT,"
1646 " ZCAUSESALAMITERMCYAN BLOB,"
1647 " ZSPREADMOTORBISCUITBACON FLOAT,"
1648 " ZGIFTICEFISHGLUEHAIR INTEGER,"
1649 " ZNOTICEPEARPOLICYJUICE TIMESTAMP,"
1650 " ZBANKBUFFALORECOVERORBIT TIMESTAMP,"
1651 " ZLONGDIETESSAYNATURE FLOAT,"
1652 " ZACTIONRANGEELEGANTNEUTRON BLOB,"
1653 " ZCADETBRIGHTPLANETBANK TIMESTAMP,"
1654 " ZAIRFORGIVEHEADFROG BLOB,"
1655 " ZSHARKJUSTFRUITMOVIE VARCHAR,"
1656 " ZFARMERMORNINGMIRRORCONCERN BLOB,"
1657 " ZWOODPOETRYCOBBLERBENCH VARCHAR,"
1658 " ZHAFNIUMSCRIPTSALADMOTOR INTEGER,"
1659 " ZPROBLEMCLUBPOPOVERJELLY FLOAT,"
1660 " ZEIGHTLEADERWORKERMOST TIMESTAMP,"
1661 " ZGLASSRESERVEBARIUMMEAL BLOB,"
1662 " ZCLAMBITARUGULAFAJITA BLOB,"
1663 " ZDECADEJOYOUSWAVEHABIT FLOAT,"
1664 " ZCOMPANYSUMMERFIBERELF INTEGER,"
1665 " ZTREATTESTQUILLCHARGE TIMESTAMP,"
1666 " ZBROWBALANCEKEYCHOWDER FLOAT,"
1667 " ZPEACHCOPPERDINNERLAKE FLOAT,"
1668 " ZDRYWALLBEYONDBROWNBOWL VARCHAR,"
1669 " ZBELLYCRASHITEMLACK BLOB,"
1670 " ZTENNISCYCLEBILLOFFICER INTEGER,"
1671 " ZMALLEQUIPTHANKSGLUE FLOAT,"
1672 " ZMISSREPLYHUMANLIVING INTEGER,"
1673 " ZKIWIVISUALPRIDEAPPLE VARCHAR,"
1674 " ZWISHHITSKINMOTOR BLOB,"
1675 " ZCALMRACCOONPROGRAMDEBIT VARCHAR,"
1676 " ZSHINYASSISTLIVINGCRAB VARCHAR,"
1677 " ZRESOLVEWRISTWRAPAPPLE VARCHAR,"
1678 " ZAPPEALSIMPLESECONDHOUSING BLOB,"
1679 " ZCORNERANCHORTAPEDIVER TIMESTAMP,"
1680 " ZMEMORYREQUESTSOURCEBIG VARCHAR,"
1681 " ZTRYFACTKEEPMILK TIMESTAMP,"
1682 " ZDIVERPAINTLEATHEREASY INTEGER,"
1683 " ZSORTMISTYQUOTECABBAGE BLOB,"
1684 " ZTUNEGASBUFFALOCAPITAL BLOB,"
1685 " ZFILLSTOPLAWJOYFUL FLOAT,"
1686 " ZSTEELCAREFULPLATENUMBER FLOAT,"
1687 " ZGIVEVIVIDDIVINEMEANING INTEGER,"
1688 " ZTREATPACKFUTURECONVERT VARCHAR,"
1689 " ZCALMLYGEMFINISHEFFECT INTEGER,"
1690 " ZCABBAGESOCKEASEMINUTE BLOB,"
1691 " ZPLANETFAMILYPUREMEMORY TIMESTAMP,"
1692 " ZMERRYCRACKTRAINLEADER BLOB,"
1693 " ZMINORWAYPAPERCLASSY TIMESTAMP,"
1694 " ZEAGLELINEMINEMAIL VARCHAR,"
1695 " ZRESORTYARDGREENLET TIMESTAMP,"
1696 " ZYARDOREGANOVIVIDJEWEL TIMESTAMP,"
1697 " ZPURECAKEVIVIDNEATLY FLOAT,"
1698 " ZASKCONTACTMONITORFUN TIMESTAMP,"
1699 " ZMOVEWHOGAMMAINCH VARCHAR,"
1700 " ZLETTUCEBIRDMEETDEBATE TIMESTAMP,"
1701 " ZGENENATURALHEARINGKITE VARCHAR,"
1702 " ZMUFFINDRYERDRAWFORTUNE FLOAT,"
1703 " ZGRAYSURVEYWIRELOVE FLOAT,"
1704 " ZPLIERSPRINTASKOREGANO INTEGER,"
1705 " ZTRAVELDRIVERCONTESTLILY INTEGER,"
1706 " ZHUMORSPICESANDKIDNEY TIMESTAMP,"
1707 " ZARSENICSAMPLEWAITMUON INTEGER,"
1708 " ZLACEADDRESSGROUNDCAREFUL FLOAT,"
1709 " ZBAMBOOMESSWASABIEVENING BLOB,"
1710 " ZONERELEASEAVERAGENURSE INTEGER,"
1711 " ZRADIANTWHENTRYCARD TIMESTAMP,"
1712 " ZREWARDINSIDEMANGOINTENSE FLOAT,"
1713 " ZNEATSTEWPARTIRON TIMESTAMP,"
1714 " ZOUTSIDEPEAHENCOUNTICE TIMESTAMP,"
1715 " ZCREAMEVENINGLIPBRANCH FLOAT,"
1716 " ZWHALEMATHAVOCADOCOPPER FLOAT,"
1717 " ZLIFEUSELEAFYBELL FLOAT,"
1718 " ZWEALTHLINENGLEEFULDAY VARCHAR,"
1719 " ZFACEINVITETALKGOLD BLOB,"
1720 " ZWESTAMOUNTAFFECTHEARING INTEGER,"
1721 " ZDELAYOUTCOMEHORNAGENCY INTEGER,"
1722 " ZBIGTHINKCONVERTECONOMY BLOB,"
1723 " ZBASEGOUDAREGULARFORGIVE TIMESTAMP,"
1724 " ZPATTERNCLORINEGRANDCOLBY TIMESTAMP,"
1725 " ZCYANBASEFEEDADROIT INTEGER,"
1726 " ZCARRYFLOORMINNOWDRAGON TIMESTAMP,"
1727 " ZIMAGEPENCILOTHERBOTTOM FLOAT,"
1728 " ZXENONFLIGHTPALEAPPLE TIMESTAMP,"
1729 " ZHERRINGJOKEFEATUREHOPEFUL FLOAT,"
1730 " ZCAPYEARLYRIVETBRUSH FLOAT,"
1731 " ZAGEREEDFROGBASKET VARCHAR,"
1732 " ZUSUALBODYHALIBUTDIAMOND VARCHAR,"
1733 " ZFOOTTAPWORDENTRY VARCHAR,"
1734 " ZDISHKEEPBLESTMONITOR FLOAT,"
1735 " ZBROADABLESOLIDCASUAL INTEGER,"
1736 " ZSQUAREGLEEFULCHILDLIGHT INTEGER,"
1737 " ZHOLIDAYHEADPONYDETAIL INTEGER,"
1738 " ZGENERALRESORTSKYOPEN TIMESTAMP,"
1739 " ZGLADSPRAYKIDNEYGUPPY VARCHAR,"
1740 " ZSWIMHEAVYMENTIONKIND BLOB,"
1741 " ZMESSYSULFURDREAMFESTIVE BLOB,"
1742 " ZSKYSKYCLASSICBRIEF VARCHAR,"
1743 " ZDILLASKHOKILEMON FLOAT,"
1744 " ZJUNIORSHOWPRESSNOVA FLOAT,"
1745 " ZSIZETOEAWARDFRESH TIMESTAMP,"
1746 " ZKEYFAILAPRICOTMETAL VARCHAR,"
1747 " ZHANDYREPAIRPROTONAIRPORT VARCHAR,"
1748 " ZPOSTPROTEINHANDLEACTOR BLOB"
1749 ");"
1750 );
1751 speedtest1_prepare(
1752 "INSERT INTO ZLOOKSLIKECOREDATA(ZPK,ZAIRFORGIVEHEADFROG,"
1753 "ZGIFTICEFISHGLUEHAIR,ZDELAYOUTCOMEHORNAGENCY,ZSLEEPYUSERGRANDBOWL,"
1754 "ZGLASSRESERVEBARIUMMEAL,ZBRIEFGOBYDODGERHEIGHT,"
1755 "ZBAMBOOMESSWASABIEVENING,ZFARMERMORNINGMIRRORCONCERN,"
1756 "ZTREATPACKFUTURECONVERT,ZCAUSESALAMITERMCYAN,ZCALMRACCOONPROGRAMDEBIT,"
1757 "ZHOLIDAYHEADPONYDETAIL,ZWOODPOETRYCOBBLERBENCH,ZHAFNIUMSCRIPTSALADMOTOR,"
1758 "ZUSUALBODYHALIBUTDIAMOND,ZOUTSIDEPEAHENCOUNTICE,ZDIVERPAINTLEATHEREASY,"
1759 "ZWESTAMOUNTAFFECTHEARING,ZSIZETOEAWARDFRESH,ZDEWPEACHCAREERCELERY,"
1760 "ZSTEELCAREFULPLATENUMBER,ZCYANBASEFEEDADROIT,ZCALMLYGEMFINISHEFFECT,"
1761 "ZHANDYREPAIRPROTONAIRPORT,ZGENENATURALHEARINGKITE,ZBROADABLESOLIDCASUAL,"
1762 "ZPOSTPROTEINHANDLEACTOR,ZLACEADDRESSGROUNDCAREFUL,ZIMAGEPENCILOTHERBOTTOM,"
1763 "ZPROBLEMCLUBPOPOVERJELLY,ZPATTERNCLORINEGRANDCOLBY,ZNEATSTEWPARTIRON,"
1764 "ZAPPEALSIMPLESECONDHOUSING,ZMOVEWHOGAMMAINCH,ZTENNISCYCLEBILLOFFICER,"
1765 "ZSHARKJUSTFRUITMOVIE,ZKEYFAILAPRICOTMETAL,ZCOMPANYSUMMERFIBERELF,"
1766 "ZTERMFITTINGHOUSINGCOMMAND,ZRESORTYARDGREENLET,ZCABBAGESOCKEASEMINUTE,"
1767 "ZSQUAREGLEEFULCHILDLIGHT,ZONERELEASEAVERAGENURSE,ZBIGTHINKCONVERTECONOMY,"
1768 "ZPLIERSPRINTASKOREGANO,ZDECADEJOYOUSWAVEHABIT,ZDRYWALLBEYONDBROWNBOWL,"
1769 "ZCLUBRELEASELIZARDADVICE,ZWHALEMATHAVOCADOCOPPER,ZBELLYCRASHITEMLACK,"
1770 "ZLETTUCEBIRDMEETDEBATE,ZCAPABLETRIPDOORALMOND,ZRADIANTWHENTRYCARD,"
1771 "ZCAPYEARLYRIVETBRUSH,ZAGEREEDFROGBASKET,ZSWIMHEAVYMENTIONKIND,"
1772 "ZTRAVELDRIVERCONTESTLILY,ZGLADSPRAYKIDNEYGUPPY,ZBANKBUFFALORECOVERORBIT,"
1773 "ZFINGERDUEPIZZAOPTION,ZCLAMBITARUGULAFAJITA,ZLONGFINLEAVEIMAGEOIL,"
1774 "ZLONGDIETESSAYNATURE,ZJUNIORSHOWPRESSNOVA,ZHOPEFULGATEHOLECHALK,"
1775 "ZDEPOSITPAIRCOLLEGECOMET,ZWEALTHLINENGLEEFULDAY,ZFILLSTOPLAWJOYFUL,"
1776 "ZTUNEGASBUFFALOCAPITAL,ZGRAYSURVEYWIRELOVE,ZCORNERANCHORTAPEDIVER,"
1777 "ZREWARDINSIDEMANGOINTENSE,ZCADETBRIGHTPLANETBANK,ZPLANETFAMILYPUREMEMORY,"
1778 "ZTREATTESTQUILLCHARGE,ZCREAMEVENINGLIPBRANCH,ZSKYSKYCLASSICBRIEF,"
1779 "ZARSENICSAMPLEWAITMUON,ZBROWBALANCEKEYCHOWDER,ZFLYINGDOCTORTABLEMELODY,"
1780 "ZHANGERLITHIUMDINNERMEET,ZNOTICEPEARPOLICYJUICE,ZSHINYASSISTLIVINGCRAB,"
1781 "ZLIFEUSELEAFYBELL,ZFACEINVITETALKGOLD,ZGENERALRESORTSKYOPEN,"
1782 "ZPURECAKEVIVIDNEATLY,ZKIWIVISUALPRIDEAPPLE,ZMESSYSULFURDREAMFESTIVE,"
1783 "ZCHARGECLICKHUMANEHIRE,ZHERRINGJOKEFEATUREHOPEFUL,ZYARDOREGANOVIVIDJEWEL,"
1784 "ZFOOTTAPWORDENTRY,ZWISHHITSKINMOTOR,ZBASEGOUDAREGULARFORGIVE,"
1785 "ZMUFFINDRYERDRAWFORTUNE,ZACTIONRANGEELEGANTNEUTRON,ZTRYFACTKEEPMILK,"
1786 "ZPEACHCOPPERDINNERLAKE,ZFRAMEENTERSIMPLEMOUTH,ZMERRYCRACKTRAINLEADER,"
1787 "ZMEMORYREQUESTSOURCEBIG,ZCARRYFLOORMINNOWDRAGON,ZMINORWAYPAPERCLASSY,"
1788 "ZDILLASKHOKILEMON,ZRESOLVEWRISTWRAPAPPLE,ZASKCONTACTMONITORFUN,"
1789 "ZGIVEVIVIDDIVINEMEANING,ZEIGHTLEADERWORKERMOST,ZMISSREPLYHUMANLIVING,"
1790 "ZXENONFLIGHTPALEAPPLE,ZSORTMISTYQUOTECABBAGE,ZEAGLELINEMINEMAIL,"
1791 "ZFAMILYVISUALOWNERMATTER,ZSPREADMOTORBISCUITBACON,ZDISHKEEPBLESTMONITOR,"
1792 "ZMALLEQUIPTHANKSGLUE,ZGOLDYOUNGINITIALNOSE,ZHUMORSPICESANDKIDNEY)"
1793 "VALUES(?1,?26,?20,?93,?8,?33,?3,?81,?28,?60,?18,?47,?109,?29,?30,?104,?86,"
1794 "?54,?92,?117,?9,?58,?97,?61,?119,?73,?107,?120,?80,?99,?31,?96,?85,?50,?71,"
1795 "?42,?27,?118,?36,?2,?67,?62,?108,?82,?94,?76,?35,?40,?11,?88,?41,?72,?4,"
1796 "?83,?102,?103,?112,?77,?111,?22,?13,?34,?15,?23,?116,?7,?5,?90,?57,?56,"
1797 "?75,?51,?84,?25,?63,?37,?87,?114,?79,?38,?14,?10,?21,?48,?89,?91,?110,"
1798 "?69,?45,?113,?12,?101,?68,?105,?46,?95,?74,?24,?53,?39,?6,?64,?52,?98,"
1799 "?65,?115,?49,?70,?59,?32,?44,?100,?55,?66,?16,?19,?106,?43,?17,?78);"
1800 );
1801 for(i=0; i<n; i++){
1802 x1 = speedtest1_random();
1803 speedtest1_numbername(x1%1000, zNum, sizeof(zNum));
1804 len = (int)strlen(zNum);
1805 sqlite3_bind_int(g.pStmt, 1, i^0xf);
1806 for(j=0; zType[j]; j++){
1807 switch( zType[j] ){
1808 case 'I':
1809 case 'T':
1810 sqlite3_bind_int64(g.pStmt, j+2, x1);
1811 break;
1812 case 'F':
1813 sqlite3_bind_double(g.pStmt, j+2, (double)x1);
1814 break;
1815 case 'V':
1816 case 'B':
1817 sqlite3_bind_text64(g.pStmt, j+2, zNum, len,
1818 SQLITE_STATIC, SQLITE_UTF8);
1819 break;
1820 }
1821 }
1822 speedtest1_run();
1823 }
1824 speedtest1_exec("COMMIT;");
1825 speedtest1_end_test();
1826
1827 n = g.szTest*250;
1828 speedtest1_begin_test(110, "Query %d rows by rowid", n);
1829 speedtest1_prepare(
1830 "SELECT ZCYANBASEFEEDADROIT,ZJUNIORSHOWPRESSNOVA,ZCAUSESALAMITERMCYAN,"
1831 "ZHOPEFULGATEHOLECHALK,ZHUMORSPICESANDKIDNEY,ZSWIMHEAVYMENTIONKIND,"
1832 "ZMOVEWHOGAMMAINCH,ZAPPEALSIMPLESECONDHOUSING,ZHAFNIUMSCRIPTSALADMOTOR,"
1833 "ZNEATSTEWPARTIRON,ZLONGFINLEAVEIMAGEOIL,ZDEWPEACHCAREERCELERY,"
1834 "ZXENONFLIGHTPALEAPPLE,ZCALMRACCOONPROGRAMDEBIT,ZUSUALBODYHALIBUTDIAMOND,"
1835 "ZTRYFACTKEEPMILK,ZWEALTHLINENGLEEFULDAY,ZLONGDIETESSAYNATURE,"
1836 "ZLIFEUSELEAFYBELL,ZTREATPACKFUTURECONVERT,ZMEMORYREQUESTSOURCEBIG,"
1837 "ZYARDOREGANOVIVIDJEWEL,ZDEPOSITPAIRCOLLEGECOMET,ZSLEEPYUSERGRANDBOWL,"
1838 "ZBRIEFGOBYDODGERHEIGHT,ZCLUBRELEASELIZARDADVICE,ZCAPABLETRIPDOORALMOND,"
1839 "ZDRYWALLBEYONDBROWNBOWL,ZASKCONTACTMONITORFUN,ZKIWIVISUALPRIDEAPPLE,"
1840 "ZNOTICEPEARPOLICYJUICE,ZPEACHCOPPERDINNERLAKE,ZSTEELCAREFULPLATENUMBER,"
1841 "ZGLADSPRAYKIDNEYGUPPY,ZCOMPANYSUMMERFIBERELF,ZTENNISCYCLEBILLOFFICER,"
1842 "ZIMAGEPENCILOTHERBOTTOM,ZWESTAMOUNTAFFECTHEARING,ZDIVERPAINTLEATHEREASY,"
1843 "ZSKYSKYCLASSICBRIEF,ZMESSYSULFURDREAMFESTIVE,ZMERRYCRACKTRAINLEADER,"
1844 "ZBROADABLESOLIDCASUAL,ZGLASSRESERVEBARIUMMEAL,ZTUNEGASBUFFALOCAPITAL,"
1845 "ZBANKBUFFALORECOVERORBIT,ZTREATTESTQUILLCHARGE,ZBAMBOOMESSWASABIEVENING,"
1846 "ZREWARDINSIDEMANGOINTENSE,ZEAGLELINEMINEMAIL,ZCALMLYGEMFINISHEFFECT,"
1847 "ZKEYFAILAPRICOTMETAL,ZFINGERDUEPIZZAOPTION,ZCADETBRIGHTPLANETBANK,"
1848 "ZGOLDYOUNGINITIALNOSE,ZMISSREPLYHUMANLIVING,ZEIGHTLEADERWORKERMOST,"
1849 "ZFRAMEENTERSIMPLEMOUTH,ZBIGTHINKCONVERTECONOMY,ZFACEINVITETALKGOLD,"
1850 "ZPOSTPROTEINHANDLEACTOR,ZHERRINGJOKEFEATUREHOPEFUL,ZCABBAGESOCKEASEMINUTE,"
1851 "ZMUFFINDRYERDRAWFORTUNE,ZPROBLEMCLUBPOPOVERJELLY,ZGIVEVIVIDDIVINEMEANING,"
1852 "ZGENENATURALHEARINGKITE,ZGENERALRESORTSKYOPEN,ZLETTUCEBIRDMEETDEBATE,"
1853 "ZBASEGOUDAREGULARFORGIVE,ZCHARGECLICKHUMANEHIRE,ZPLANETFAMILYPUREMEMORY,"
1854 "ZMINORWAYPAPERCLASSY,ZCAPYEARLYRIVETBRUSH,ZSIZETOEAWARDFRESH,"
1855 "ZARSENICSAMPLEWAITMUON,ZSQUAREGLEEFULCHILDLIGHT,ZSHINYASSISTLIVINGCRAB,"
1856 "ZCORNERANCHORTAPEDIVER,ZDECADEJOYOUSWAVEHABIT,ZTRAVELDRIVERCONTESTLILY,"
1857 "ZFLYINGDOCTORTABLEMELODY,ZSHARKJUSTFRUITMOVIE,ZFAMILYVISUALOWNERMATTER,"
1858 "ZFARMERMORNINGMIRRORCONCERN,ZGIFTICEFISHGLUEHAIR,ZOUTSIDEPEAHENCOUNTICE,"
1859 "ZSPREADMOTORBISCUITBACON,ZWISHHITSKINMOTOR,ZHOLIDAYHEADPONYDETAIL,"
1860 "ZWOODPOETRYCOBBLERBENCH,ZAIRFORGIVEHEADFROG,ZBROWBALANCEKEYCHOWDER,"
1861 "ZDISHKEEPBLESTMONITOR,ZCLAMBITARUGULAFAJITA,ZPLIERSPRINTASKOREGANO,"
1862 "ZRADIANTWHENTRYCARD,ZDELAYOUTCOMEHORNAGENCY,ZPURECAKEVIVIDNEATLY,"
1863 "ZPATTERNCLORINEGRANDCOLBY,ZHANDYREPAIRPROTONAIRPORT,ZAGEREEDFROGBASKET,"
1864 "ZSORTMISTYQUOTECABBAGE,ZFOOTTAPWORDENTRY,ZRESOLVEWRISTWRAPAPPLE,"
1865 "ZDILLASKHOKILEMON,ZFILLSTOPLAWJOYFUL,ZACTIONRANGEELEGANTNEUTRON,"
1866 "ZRESORTYARDGREENLET,ZCREAMEVENINGLIPBRANCH,ZWHALEMATHAVOCADOCOPPER,"
1867 "ZGRAYSURVEYWIRELOVE,ZBELLYCRASHITEMLACK,ZHANGERLITHIUMDINNERMEET,"
1868 "ZCARRYFLOORMINNOWDRAGON,ZMALLEQUIPTHANKSGLUE,ZTERMFITTINGHOUSINGCOMMAND,"
1869 "ZONERELEASEAVERAGENURSE,ZLACEADDRESSGROUNDCAREFUL"
1870 " FROM ZLOOKSLIKECOREDATA WHERE ZPK=?1;"
1871 );
1872 for(i=0; i<n; i++){
1873 x1 = speedtest1_random()%nRow;
1874 sqlite3_bind_int(g.pStmt, 1, x1);
1875 speedtest1_run();
1876 }
1877 speedtest1_end_test();
1878 }
1879
1880 /*
1881 */
1882 void testset_trigger(void){
1883 int jj, ii;
1884 char zNum[2000]; /* A number name */
1885
1886 const int NROW = 500*g.szTest;
1887 const int NROW2 = 100*g.szTest;
1888
1889 speedtest1_exec(
1890 "BEGIN;"
1891 "CREATE TABLE z1(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1892 "CREATE TABLE z2(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1893 "CREATE TABLE t3(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1894 "CREATE VIEW v1 AS SELECT rowid, i, t FROM z1;"
1895 "CREATE VIEW v2 AS SELECT rowid, i, t FROM z2;"
1896 "CREATE VIEW v3 AS SELECT rowid, i, t FROM t3;"
1897 );
1898 for(jj=1; jj<=3; jj++){
1899 speedtest1_prepare("INSERT INTO t%d VALUES(NULL,?1,?2)", jj);
1900 for(ii=0; ii<NROW; ii++){
1901 int x1 = speedtest1_random() % NROW;
1902 speedtest1_numbername(x1, zNum, sizeof(zNum));
1903 sqlite3_bind_int(g.pStmt, 1, x1);
1904 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
1905 speedtest1_run();
1906 }
1907 }
1908 speedtest1_exec(
1909 "CREATE INDEX i1 ON z1(t);"
1910 "CREATE INDEX i2 ON z2(t);"
1911 "CREATE INDEX i3 ON t3(t);"
1912 "COMMIT;"
1913 );
1914
1915 speedtest1_begin_test(100, "speed4p-join1");
1916 speedtest1_prepare(
1917 "SELECT * FROM z1, z2, t3 WHERE z1.oid = z2.oid AND z2.oid = t3.oid"
1918 );
1919 speedtest1_run();
1920 speedtest1_end_test();
1921
1922 speedtest1_begin_test(110, "speed4p-join2");
1923 speedtest1_prepare(
1924 "SELECT * FROM z1, z2, t3 WHERE z1.t = z2.t AND z2.t = t3.t"
1925 );
1926 speedtest1_run();
1927 speedtest1_end_test();
1928
1929 speedtest1_begin_test(120, "speed4p-view1");
1930 for(jj=1; jj<=3; jj++){
1931 speedtest1_prepare("SELECT * FROM v%d WHERE rowid = ?", jj);
1932 for(ii=0; ii<NROW2; ii+=3){
1933 sqlite3_bind_int(g.pStmt, 1, ii*3);
1934 speedtest1_run();
1935 }
1936 }
1937 speedtest1_end_test();
1938
1939 speedtest1_begin_test(130, "speed4p-table1");
1940 for(jj=1; jj<=3; jj++){
1941 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
1942 for(ii=0; ii<NROW2; ii+=3){
1943 sqlite3_bind_int(g.pStmt, 1, ii*3);
1944 speedtest1_run();
1945 }
1946 }
1947 speedtest1_end_test();
1948
1949 speedtest1_begin_test(140, "speed4p-table1");
1950 for(jj=1; jj<=3; jj++){
1951 speedtest1_prepare("SELECT * FROM t%d WHERE rowid = ?", jj);
1952 for(ii=0; ii<NROW2; ii+=3){
1953 sqlite3_bind_int(g.pStmt, 1, ii*3);
1954 speedtest1_run();
1955 }
1956 }
1957 speedtest1_end_test();
1958
1959 speedtest1_begin_test(150, "speed4p-subselect1");
1960 speedtest1_prepare("SELECT "
1961 "(SELECT t FROM z1 WHERE rowid = ?1),"
1962 "(SELECT t FROM z2 WHERE rowid = ?1),"
1963 "(SELECT t FROM t3 WHERE rowid = ?1)"
1964 );
1965 for(jj=0; jj<NROW2; jj++){
1966 sqlite3_bind_int(g.pStmt, 1, jj*3);
1967 speedtest1_run();
1968 }
1969 speedtest1_end_test();
1970
1971 speedtest1_begin_test(160, "speed4p-rowid-update");
1972 speedtest1_exec("BEGIN");
1973 speedtest1_prepare("UPDATE z1 SET i=i+1 WHERE rowid=?1");
1974 for(jj=0; jj<NROW2; jj++){
1975 sqlite3_bind_int(g.pStmt, 1, jj);
1976 speedtest1_run();
1977 }
1978 speedtest1_exec("COMMIT");
1979 speedtest1_end_test();
1980
1981 speedtest1_exec("CREATE TABLE t5(t TEXT PRIMARY KEY, i INTEGER);");
1982 speedtest1_begin_test(170, "speed4p-insert-ignore");
1983 speedtest1_exec("INSERT OR IGNORE INTO t5 SELECT t, i FROM z1");
1984 speedtest1_end_test();
1985
1986 speedtest1_exec(
1987 "CREATE TABLE log(op TEXT, r INTEGER, i INTEGER, t TEXT);"
1988 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
1989 "CREATE TRIGGER t4_trigger1 AFTER INSERT ON t4 BEGIN"
1990 " INSERT INTO log VALUES('INSERT INTO t4', new.rowid, new.i, new.t);"
1991 "END;"
1992 "CREATE TRIGGER t4_trigger2 AFTER UPDATE ON t4 BEGIN"
1993 " INSERT INTO log VALUES('UPDATE OF t4', new.rowid, new.i, new.t);"
1994 "END;"
1995 "CREATE TRIGGER t4_trigger3 AFTER DELETE ON t4 BEGIN"
1996 " INSERT INTO log VALUES('DELETE OF t4', old.rowid, old.i, old.t);"
1997 "END;"
1998 "BEGIN;"
1999 );
2000
2001 speedtest1_begin_test(180, "speed4p-trigger1");
2002 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2003 for(jj=0; jj<NROW2; jj++){
2004 speedtest1_numbername(jj, zNum, sizeof(zNum));
2005 sqlite3_bind_int(g.pStmt, 1, jj);
2006 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2007 speedtest1_run();
2008 }
2009 speedtest1_end_test();
2010
2011 /*
2012 ** Note: Of the queries, only half actually update a row. This property
2013 ** was copied over from speed4p.test, where it was probably introduced
2014 ** inadvertantly.
2015 */
2016 speedtest1_begin_test(190, "speed4p-trigger2");
2017 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2018 for(jj=1; jj<=NROW2*2; jj+=2){
2019 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2020 sqlite3_bind_int(g.pStmt, 1, jj*2);
2021 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2022 sqlite3_bind_int(g.pStmt, 3, jj);
2023 speedtest1_run();
2024 }
2025 speedtest1_end_test();
2026
2027 /*
2028 ** Note: Same again.
2029 */
2030 speedtest1_begin_test(200, "speed4p-trigger3");
2031 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2032 for(jj=1; jj<=NROW2*2; jj+=2){
2033 sqlite3_bind_int(g.pStmt, 1, jj*2);
2034 speedtest1_run();
2035 }
2036 speedtest1_end_test();
2037 speedtest1_exec("COMMIT");
2038
2039 /*
2040 ** The following block contains the same tests as the above block that
2041 ** tests triggers, with one crucial difference: no triggers are defined.
2042 ** So the difference in speed between these tests and the preceding ones
2043 ** is the amount of time taken to compile and execute the trigger programs.
2044 */
2045 speedtest1_exec(
2046 "DROP TABLE t4;"
2047 "DROP TABLE log;"
2048 "VACUUM;"
2049 "CREATE TABLE t4(rowid INTEGER PRIMARY KEY, i INTEGER, t TEXT);"
2050 "BEGIN;"
2051 );
2052 speedtest1_begin_test(210, "speed4p-notrigger1");
2053 speedtest1_prepare("INSERT INTO t4 VALUES(NULL, ?1, ?2)");
2054 for(jj=0; jj<NROW2; jj++){
2055 speedtest1_numbername(jj, zNum, sizeof(zNum));
2056 sqlite3_bind_int(g.pStmt, 1, jj);
2057 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2058 speedtest1_run();
2059 }
2060 speedtest1_end_test();
2061 speedtest1_begin_test(210, "speed4p-notrigger2");
2062 speedtest1_prepare("UPDATE t4 SET i = ?1, t = ?2 WHERE rowid = ?3");
2063 for(jj=1; jj<=NROW2*2; jj+=2){
2064 speedtest1_numbername(jj*2, zNum, sizeof(zNum));
2065 sqlite3_bind_int(g.pStmt, 1, jj*2);
2066 sqlite3_bind_text(g.pStmt, 2, zNum, -1, SQLITE_STATIC);
2067 sqlite3_bind_int(g.pStmt, 3, jj);
2068 speedtest1_run();
2069 }
2070 speedtest1_end_test();
2071 speedtest1_begin_test(220, "speed4p-notrigger3");
2072 speedtest1_prepare("DELETE FROM t4 WHERE rowid = ?1");
2073 for(jj=1; jj<=NROW2*2; jj+=2){
2074 sqlite3_bind_int(g.pStmt, 1, jj*2);
2075 speedtest1_run();
2076 }
2077 speedtest1_end_test();
2078 speedtest1_exec("COMMIT");
2079 }
2080
2081 /*
2082 ** A testset used for debugging speedtest1 itself.
2083 */
2084 void testset_debug1(void){
2085 unsigned i, n;
2086 unsigned x1, x2;
2087 char zNum[2000]; /* A number name */
2088
2089 n = g.szTest;
2090 for(i=1; i<=n; i++){
2091 x1 = swizzle(i, n);
2092 x2 = swizzle(x1, n);
2093 speedtest1_numbername(x1, zNum, sizeof(zNum));
2094 printf("%5d %5d %5d %s\n", i, x1, x2, zNum);
2095 }
2096 }
2097
2098 #ifdef __linux__
2099 #include <sys/types.h>
2100 #include <unistd.h>
2101
2102 /*
2103 ** Attempt to display I/O stats on Linux using /proc/PID/io
2104 */
2105 static void displayLinuxIoStats(FILE *out){
2106 FILE *in;
2107 char z[200];
2108 sqlite3_snprintf(sizeof(z), z, "/proc/%d/io", getpid());
2109 in = fopen(z, "rb");
2110 if( in==0 ) return;
2111 while( fgets(z, sizeof(z), in)!=0 ){
2112 static const struct {
2113 const char *zPattern;
2114 const char *zDesc;
2115 } aTrans[] = {
2116 { "rchar: ", "Bytes received by read():" },
2117 { "wchar: ", "Bytes sent to write():" },
2118 { "syscr: ", "Read() system calls:" },
2119 { "syscw: ", "Write() system calls:" },
2120 { "read_bytes: ", "Bytes rcvd from storage:" },
2121 { "write_bytes: ", "Bytes sent to storage:" },
2122 { "cancelled_write_bytes: ", "Cancelled write bytes:" },
2123 };
2124 int i;
2125 for(i=0; i<sizeof(aTrans)/sizeof(aTrans[0]); i++){
2126 int n = (int)strlen(aTrans[i].zPattern);
2127 if( strncmp(aTrans[i].zPattern, z, n)==0 ){
2128 fprintf(out, "-- %-28s %s", aTrans[i].zDesc, &z[n]);
2129 break;
2130 }
2131 }
2132 }
2133 fclose(in);
2134 }
2135 #endif
2136
2137 #if SQLITE_VERSION_NUMBER<3006018
2138 # define sqlite3_sourceid(X) "(before 3.6.18)"
2139 #endif
2140
2141 #if SQLITE_CKSUMVFS_STATIC
2142 int sqlite3_register_cksumvfs(const char*);
2143 #endif
2144
2145 static int xCompileOptions(void *pCtx, int nVal, char **azVal, char **azCol){
2146 printf("-- Compile option: %s\n", azVal[0]);
2147 return SQLITE_OK;
2148 }
2149
2150 int main(int argc, char **argv){
2151 int doAutovac = 0; /* True for --autovacuum */
2152 int cacheSize = 0; /* Desired cache size. 0 means default */
2153 int doExclusive = 0; /* True for --exclusive */
2154 int nHeap = 0, mnHeap = 0; /* Heap size from --heap */
2155 int doIncrvac = 0; /* True for --incrvacuum */
2156 const char *zJMode = 0; /* Journal mode */
2157 const char *zKey = 0; /* Encryption key */
2158 int nLook = -1, szLook = 0; /* --lookaside configuration */
2159 int noSync = 0; /* True for --nosync */
2160 int pageSize = 0; /* Desired page size. 0 means default */
2161 int nPCache = 0, szPCache = 0;/* --pcache configuration */
2162 int doPCache = 0; /* True if --pcache is seen */
2163 int showStats = 0; /* True for --stats */
2164 int nThread = 0; /* --threads value */
2165 int mmapSize = 0; /* How big of a memory map to use */
2166 int memDb = 0; /* --memdb. Use an in-memory database */
2167 char *zTSet = "main"; /* Which --testset torun */
2168 int doTrace = 0; /* True for --trace */
2169 const char *zEncoding = 0; /* --utf16be or --utf16le */
2170 const char *zDbName = 0; /* Name of the test database */
2171
2172 void *pHeap = 0; /* Allocated heap space */
2173 void *pLook = 0; /* Allocated lookaside space */
2174 void *pPCache = 0; /* Allocated storage for pcache */
2175 int iCur, iHi; /* Stats values, current and "highwater" */
2176 int i; /* Loop counter */
2177 int rc; /* API return code */
2178
2179 #ifdef SQLITE_CKSUMVFS_STATIC
2180 sqlite3_register_cksumvfs(0);
2181 #endif
2182
2183 /* Display the version of SQLite being tested */
2184 printf("-- Speedtest1 for SQLite %s %.48s\n",
2185 sqlite3_libversion(), sqlite3_sourceid());
2186
2187 /* Process command-line arguments */
2188 g.zWR = "";
2189 g.zNN = "";
2190 g.zPK = "UNIQUE";
2191 g.szTest = 100;
2192 g.nRepeat = 1;
2193 for(i=1; i<argc; i++){
2194 const char *z = argv[i];
2195 if( z[0]=='-' ){
2196 do{ z++; }while( z[0]=='-' );
2197 if( strcmp(z,"autovacuum")==0 ){
2198 doAutovac = 1;
2199 }else if( strcmp(z,"cachesize")==0 ){
2200 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2201 i++;
2202 cacheSize = integerValue(argv[i]);
2203 }else if( strcmp(z,"exclusive")==0 ){
2204 doExclusive = 1;
2205 }else if( strcmp(z,"checkpoint")==0 ){
2206 g.doCheckpoint = 1;
2207 }else if( strcmp(z,"explain")==0 ){
2208 g.bSqlOnly = 1;
2209 g.bExplain = 1;
2210 }else if( strcmp(z,"heap")==0 ){
2211 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
2212 nHeap = integerValue(argv[i+1]);
2213 mnHeap = integerValue(argv[i+2]);
2214 i += 2;
2215 }else if( strcmp(z,"incrvacuum")==0 ){
2216 doIncrvac = 1;
2217 }else if( strcmp(z,"journal")==0 ){
2218 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2219 zJMode = argv[++i];
2220 }else if( strcmp(z,"key")==0 ){
2221 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2222 zKey = argv[++i];
2223 }else if( strcmp(z,"lookaside")==0 ){
2224 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
2225 nLook = integerValue(argv[i+1]);
2226 szLook = integerValue(argv[i+2]);
2227 i += 2;
2228 }else if( strcmp(z,"memdb")==0 ){
2229 memDb = 1;
2230 #if SQLITE_VERSION_NUMBER>=3006000
2231 }else if( strcmp(z,"multithread")==0 ){
2232 sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
2233 }else if( strcmp(z,"nomemstat")==0 ){
2234 sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 0);
2235 #endif
2236 #if SQLITE_VERSION_NUMBER>=3007017
2237 }else if( strcmp(z, "mmap")==0 ){
2238 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2239 mmapSize = integerValue(argv[++i]);
2240 #endif
2241 }else if( strcmp(z,"nosync")==0 ){
2242 noSync = 1;
2243 }else if( strcmp(z,"notnull")==0 ){
2244 g.zNN = "NOT NULL";
2245 }else if( strcmp(z,"output")==0 ){
2246 #ifdef SPEEDTEST_OMIT_HASH
2247 fatal_error("The --output option is not supported with"
2248 " -DSPEEDTEST_OMIT_HASH\n");
2249 #else
2250 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2251 i++;
2252 if( strcmp(argv[i],"-")==0 ){
2253 g.hashFile = stdout;
2254 }else{
2255 g.hashFile = fopen(argv[i], "wb");
2256 if( g.hashFile==0 ){
2257 fatal_error("cannot open \"%s\" for writing\n", argv[i]);
2258 }
2259 }
2260 #endif
2261 }else if( strcmp(z,"pagesize")==0 ){
2262 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2263 pageSize = integerValue(argv[++i]);
2264 }else if( strcmp(z,"pcache")==0 ){
2265 if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
2266 nPCache = integerValue(argv[i+1]);
2267 szPCache = integerValue(argv[i+2]);
2268 doPCache = 1;
2269 i += 2;
2270 }else if( strcmp(z,"primarykey")==0 ){
2271 g.zPK = "PRIMARY KEY";
2272 }else if( strcmp(z,"repeat")==0 ){
2273 if( i>=argc-1 ) fatal_error("missing arguments on %s\n", argv[i]);
2274 g.nRepeat = integerValue(argv[i+1]);
2275 i += 1;
2276 }else if( strcmp(z,"reprepare")==0 ){
2277 g.bReprepare = 1;
2278 #if SQLITE_VERSION_NUMBER>=3006000
2279 }else if( strcmp(z,"serialized")==0 ){
2280 sqlite3_config(SQLITE_CONFIG_SERIALIZED);
2281 }else if( strcmp(z,"singlethread")==0 ){
2282 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
2283 #endif
2284 }else if( strcmp(z,"sqlonly")==0 ){
2285 g.bSqlOnly = 1;
2286 }else if( strcmp(z,"shrink-memory")==0 ){
2287 g.bMemShrink = 1;
2288 }else if( strcmp(z,"size")==0 ){
2289 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2290 g.szTest = integerValue(argv[++i]);
2291 }else if( strcmp(z,"stats")==0 ){
2292 showStats = 1;
2293 }else if( strcmp(z,"temp")==0 ){
2294 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2295 i++;
2296 if( argv[i][0]<'0' || argv[i][0]>'9' || argv[i][1]!=0 ){
2297 fatal_error("argument to --temp should be integer between 0 and 9");
2298 }
2299 g.eTemp = argv[i][0] - '0';
2300 }else if( strcmp(z,"testset")==0 ){
2301 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2302 zTSet = argv[++i];
2303 }else if( strcmp(z,"trace")==0 ){
2304 doTrace = 1;
2305 }else if( strcmp(z,"threads")==0 ){
2306 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2307 nThread = integerValue(argv[++i]);
2308 }else if( strcmp(z,"utf16le")==0 ){
2309 zEncoding = "utf16le";
2310 }else if( strcmp(z,"utf16be")==0 ){
2311 zEncoding = "utf16be";
2312 }else if( strcmp(z,"verify")==0 ){
2313 g.bVerify = 1;
2314 #ifndef SPEEDTEST_OMIT_HASH
2315 HashInit();
2316 #endif
2317 }else if( strcmp(z,"reserve")==0 ){
2318 if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
2319 g.nReserve = atoi(argv[++i]);
2320 }else if( strcmp(z,"without-rowid")==0 ){
2321 if( strstr(g.zWR,"WITHOUT")!=0 ){
2322 /* no-op */
2323 }else if( strstr(g.zWR,"STRICT")!=0 ){
2324 g.zWR = "WITHOUT ROWID,STRICT";
2325 }else{
2326 g.zWR = "WITHOUT ROWID";
2327 }
2328 g.zPK = "PRIMARY KEY";
2329 }else if( strcmp(z,"strict")==0 ){
2330 if( strstr(g.zWR,"STRICT")!=0 ){
2331 /* no-op */
2332 }else if( strstr(g.zWR,"WITHOUT")!=0 ){
2333 g.zWR = "WITHOUT ROWID,STRICT";
2334 }else{
2335 g.zWR = "STRICT";
2336 }
2337 }else if( strcmp(z, "help")==0 || strcmp(z,"?")==0 ){
2338 printf(zHelp, argv[0]);
2339 exit(0);
2340 }else{
2341 fatal_error("unknown option: %s\nUse \"%s -?\" for help\n",
2342 argv[i], argv[0]);
2343 }
2344 }else if( zDbName==0 ){
2345 zDbName = argv[i];
2346 }else{
2347 fatal_error("surplus argument: %s\nUse \"%s -?\" for help\n",
2348 argv[i], argv[0]);
2349 }
2350 }
2351 if( zDbName!=0 ) unlink(zDbName);
2352 #if SQLITE_VERSION_NUMBER>=3006001
2353 if( nHeap>0 ){
2354 pHeap = malloc( nHeap );
2355 if( pHeap==0 ) fatal_error("cannot allocate %d-byte heap\n", nHeap);
2356 rc = sqlite3_config(SQLITE_CONFIG_HEAP, pHeap, nHeap, mnHeap);
2357 if( rc ) fatal_error("heap configuration failed: %d\n", rc);
2358 }
2359 if( doPCache ){
2360 if( nPCache>0 && szPCache>0 ){
2361 pPCache = malloc( nPCache*(sqlite3_int64)szPCache );
2362 if( pPCache==0 ) fatal_error("cannot allocate %lld-byte pcache\n",
2363 nPCache*(sqlite3_int64)szPCache);
2364 }
2365 rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, pPCache, szPCache, nPCache);
2366 if( rc ) fatal_error("pcache configuration failed: %d\n", rc);
2367 }
2368 if( nLook>=0 ){
2369 sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 0, 0);
2370 }
2371 #endif
2372 sqlite3_initialize();
2373
2374 /* Open the database and the input file */
2375 if( sqlite3_open(memDb ? ":memory:" : zDbName, &g.db) ){
2376 fatal_error("Cannot open database file: %s\n", zDbName);
2377 }
2378 #if SQLITE_VERSION_NUMBER>=3006001
2379 if( nLook>0 && szLook>0 ){
2380 pLook = malloc( nLook*szLook );
2381 rc = sqlite3_db_config(g.db, SQLITE_DBCONFIG_LOOKASIDE,pLook,szLook,nLook);
2382 if( rc ) fatal_error("lookaside configuration failed: %d\n", rc);
2383 }
2384 #endif
2385 if( g.nReserve>0 ){
2386 sqlite3_file_control(g.db, 0, SQLITE_FCNTL_RESERVE_BYTES, &g.nReserve);
2387 }
2388
2389 /* Set database connection options */
2390 sqlite3_create_function(g.db, "random", 0, SQLITE_UTF8, 0, randomFunc, 0, 0);
2391 #ifndef SQLITE_OMIT_DEPRECATED
2392 if( doTrace ) sqlite3_trace(g.db, traceCallback, 0);
2393 #endif
2394 if( memDb>0 ){
2395 speedtest1_exec("PRAGMA temp_store=memory");
2396 }
2397 if( mmapSize>0 ){
2398 speedtest1_exec("PRAGMA mmap_size=%d", mmapSize);
2399 }
2400 speedtest1_exec("PRAGMA threads=%d", nThread);
2401 if( zKey ){
2402 speedtest1_exec("PRAGMA key('%s')", zKey);
2403 }
2404 if( zEncoding ){
2405 speedtest1_exec("PRAGMA encoding=%s", zEncoding);
2406 }
2407 if( doAutovac ){
2408 speedtest1_exec("PRAGMA auto_vacuum=FULL");
2409 }else if( doIncrvac ){
2410 speedtest1_exec("PRAGMA auto_vacuum=INCREMENTAL");
2411 }
2412 if( pageSize ){
2413 speedtest1_exec("PRAGMA page_size=%d", pageSize);
2414 }
2415 if( cacheSize ){
2416 speedtest1_exec("PRAGMA cache_size=%d", cacheSize);
2417 }
2418 if( noSync ) speedtest1_exec("PRAGMA synchronous=OFF");
2419 if( doExclusive ){
2420 speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
2421 }
2422 if( zJMode ){
2423 speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
2424 }
2425
2426 if( g.bExplain ) printf(".explain\n.echo on\n");
2427 do{
2428 char *zThisTest = zTSet;
2429 char *zComma = strchr(zThisTest,',');
2430 if( zComma ){
2431 *zComma = 0;
2432 zTSet = zComma+1;
2433 }else{
2434 zTSet = "";
2435 }
2436 if( g.iTotal>0 || zComma!=0 ){
2437 printf(" Begin testset \"%s\"\n", zThisTest);
2438 }
2439 if( strcmp(zThisTest,"main")==0 ){
2440 testset_main();
2441 }else if( strcmp(zThisTest,"debug1")==0 ){
2442 testset_debug1();
2443 }else if( strcmp(zThisTest,"orm")==0 ){
2444 testset_orm();
2445 }else if( strcmp(zThisTest,"cte")==0 ){
2446 testset_cte();
2447 }else if( strcmp(zThisTest,"fp")==0 ){
2448 testset_fp();
2449 }else if( strcmp(zThisTest,"trigger")==0 ){
2450 testset_trigger();
2451 }else if( strcmp(zThisTest,"rtree")==0 ){
2452 #ifdef SQLITE_ENABLE_RTREE
2453 testset_rtree(6, 147);
2454 #else
2455 fatal_error("compile with -DSQLITE_ENABLE_RTREE to enable "
2456 "the R-Tree tests\n");
2457 #endif
2458 }else{
2459 fatal_error("unknown testset: \"%s\"\n"
2460 "Choices: cte debug1 fp main orm rtree trigger\n",
2461 zThisTest);
2462 }
2463 if( zTSet[0] ){
2464 char *zSql, *zObj;
2465 speedtest1_begin_test(999, "Reset the database");
2466 while( 1 ){
2467 zObj = speedtest1_once(
2468 "SELECT name FROM main.sqlite_master"
2469 " WHERE sql LIKE 'CREATE %%TABLE%%'");
2470 if( zObj==0 ) break;
2471 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
2472 speedtest1_exec(zSql);
2473 sqlite3_free(zSql);
2474 sqlite3_free(zObj);
2475 }
2476 while( 1 ){
2477 zObj = speedtest1_once(
2478 "SELECT name FROM temp.sqlite_master"
2479 " WHERE sql LIKE 'CREATE %%TABLE%%'");
2480 if( zObj==0 ) break;
2481 zSql = sqlite3_mprintf("DROP TABLE main.\"%w\"", zObj);
2482 speedtest1_exec(zSql);
2483 sqlite3_free(zSql);
2484 sqlite3_free(zObj);
2485 }
2486 speedtest1_end_test();
2487 }
2488 }while( zTSet[0] );
2489 speedtest1_final();
2490
2491 if( showStats ){
2492 sqlite3_exec(g.db, "PRAGMA compile_options", xCompileOptions, 0, 0);
2493 }
2494
2495 /* Database connection statistics printed after both prepared statements
2496 ** have been finalized */
2497 #if SQLITE_VERSION_NUMBER>=3007009
2498 if( showStats ){
2499 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_USED, &iCur, &iHi, 0);
2500 printf("-- Lookaside Slots Used: %d (max %d)\n", iCur,iHi);
2501 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_HIT, &iCur, &iHi, 0);
2502 printf("-- Successful lookasides: %d\n", iHi);
2503 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE, &iCur,&iHi,0);
2504 printf("-- Lookaside size faults: %d\n", iHi);
2505 sqlite3_db_status(g.db, SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL, &iCur,&iHi,0);
2506 printf("-- Lookaside OOM faults: %d\n", iHi);
2507 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_USED, &iCur, &iHi, 0);
2508 printf("-- Pager Heap Usage: %d bytes\n", iCur);
2509 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_HIT, &iCur, &iHi, 1);
2510 printf("-- Page cache hits: %d\n", iCur);
2511 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHi, 1);
2512 printf("-- Page cache misses: %d\n", iCur);
2513 #if SQLITE_VERSION_NUMBER>=3007012
2514 sqlite3_db_status(g.db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHi, 1);
2515 printf("-- Page cache writes: %d\n", iCur);
2516 #endif
2517 sqlite3_db_status(g.db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHi, 0);
2518 printf("-- Schema Heap Usage: %d bytes\n", iCur);
2519 sqlite3_db_status(g.db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHi, 0);
2520 printf("-- Statement Heap Usage: %d bytes\n", iCur);
2521 }
2522 #endif
2523
2524 sqlite3_close(g.db);
2525
2526 #if SQLITE_VERSION_NUMBER>=3006001
2527 /* Global memory usage statistics printed after the database connection
2528 ** has closed. Memory usage should be zero at this point. */
2529 if( showStats ){
2530 sqlite3_status(SQLITE_STATUS_MEMORY_USED, &iCur, &iHi, 0);
2531 printf("-- Memory Used (bytes): %d (max %d)\n", iCur,iHi);
2532 #if SQLITE_VERSION_NUMBER>=3007000
2533 sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHi, 0);
2534 printf("-- Outstanding Allocations: %d (max %d)\n", iCur,iHi);
2535 #endif
2536 sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHi, 0);
2537 printf("-- Pcache Overflow Bytes: %d (max %d)\n", iCur,iHi);
2538 sqlite3_status(SQLITE_STATUS_MALLOC_SIZE, &iCur, &iHi, 0);
2539 printf("-- Largest Allocation: %d bytes\n",iHi);
2540 sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &iCur, &iHi, 0);
2541 printf("-- Largest Pcache Allocation: %d bytes\n",iHi);
2542 }
2543 #endif
2544
2545 #ifdef __linux__
2546 if( showStats ){
2547 displayLinuxIoStats(stdout);
2548 }
2549 #endif
2550
2551 /* Release memory */
2552 free( pLook );
2553 free( pPCache );
2554 free( pHeap );
2555 return 0;
2556 }
SQLCipher is an SQLite extension that provides 256 bit AES encryption of database files.