/ Check-in [96397263]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix the ".echo on" dot-command of the shell so that it echos comments in addition to SQL statements and dot-commands. Add the --explain option to speedtest1 so that the output can be piped into the command-line shell to show nicely-formated VDBE code for the entire test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 96397263f94256e284aae9506de1fc48fea89193
User & Date: drh 2013-12-21 15:46:06
Context
2013-12-21
16:06
Fix harmless compiler warnings in FTS4. This involved corrupting Martin Porter's beautifully written and elegant stemmer code, making it a little less beautiful and a little less elegant. Today is a sad day. But the warnings from GCC grow increasingly verbose and irksome with each new release and so something had to be done. check-in: df056798 user: drh tags: trunk
15:46
Fix the ".echo on" dot-command of the shell so that it echos comments in addition to SQL statements and dot-commands. Add the --explain option to speedtest1 so that the output can be piped into the command-line shell to show nicely-formated VDBE code for the entire test. check-in: 96397263 user: drh tags: trunk
00:04
Add a case to speedtest1.c that demonstrates the need to factor OP_Column operators out of inner loops. check-in: 69a17336 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.

3034
3035
3036
3037
3038
3039
3040
3041



3042
3043
3044
3045
3046
3047
3048
....
3096
3097
3098
3099
3100
3101
3102

3103
3104
3105
3106
3107
3108
3109
      break;
    }
    if( seenInterrupt ){
      if( in!=0 ) break;
      seenInterrupt = 0;
    }
    lineno++;
    if( nSql==0 && _all_whitespace(zLine) ) continue;



    if( zLine && zLine[0]=='.' && nSql==0 ){
      if( p->echoOn ) printf("%s\n", zLine);
      rc = do_meta_command(zLine, p);
      if( rc==2 ){ /* exit requested */
        break;
      }else if( rc ){
        errCnt++;
................................................................................
        }else{
          fprintf(stderr, "%s %s\n", zPrefix, sqlite3_errmsg(p->db));
        }
        errCnt++;
      }
      nSql = 0;
    }else if( nSql && _all_whitespace(zSql) ){

      nSql = 0;
    }
  }
  if( nSql ){
    if( !_all_whitespace(zSql) ){
      fprintf(stderr, "Error: incomplete SQL: %s\n", zSql);
    }







|
>
>
>







 







>







3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
....
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
      break;
    }
    if( seenInterrupt ){
      if( in!=0 ) break;
      seenInterrupt = 0;
    }
    lineno++;
    if( nSql==0 && _all_whitespace(zLine) ){
      if( p->echoOn ) printf("%s\n", zLine);
      continue;
    }
    if( zLine && zLine[0]=='.' && nSql==0 ){
      if( p->echoOn ) printf("%s\n", zLine);
      rc = do_meta_command(zLine, p);
      if( rc==2 ){ /* exit requested */
        break;
      }else if( rc ){
        errCnt++;
................................................................................
        }else{
          fprintf(stderr, "%s %s\n", zPrefix, sqlite3_errmsg(p->db));
        }
        errCnt++;
      }
      nSql = 0;
    }else if( nSql && _all_whitespace(zSql) ){
      if( p->echoOn ) printf("%s\n", zSql);
      nSql = 0;
    }
  }
  if( nSql ){
    if( !_all_whitespace(zSql) ){
      fprintf(stderr, "Error: incomplete SQL: %s\n", zSql);
    }

Changes to test/shell2.test.

151
152
153
154
155
156
157

158
159
160
161
162
163
164
165
...
188
189
190
191
192
193
194

195
196
197
INSERT INTO foo1(a) VALUES(2);
INSERT INTO foo2(b) VALUES(2);
SELECT * FROM foo1;
1
2
SELECT * FROM foo2;
1

2}}

# Test with echo on and headers on using dot command and 
# multiple commands per line.
# NB. whitespace is important
do_test shell2-1.4.6 {
  forcedelete foo.db
  catchcmd "foo.db" {.echo ON
................................................................................
SELECT * FROM foo1;
a
1
2
SELECT * FROM foo2;
b
1

2}}

finish_test







>
|







 







>
|


151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
...
189
190
191
192
193
194
195
196
197
198
199
INSERT INTO foo1(a) VALUES(2);
INSERT INTO foo2(b) VALUES(2);
SELECT * FROM foo1;
1
2
SELECT * FROM foo2;
1
2
}}

# Test with echo on and headers on using dot command and 
# multiple commands per line.
# NB. whitespace is important
do_test shell2-1.4.6 {
  forcedelete foo.db
  catchcmd "foo.db" {.echo ON
................................................................................
SELECT * FROM foo1;
a
1
2
SELECT * FROM foo2;
b
1
2
}}

finish_test

Changes to test/speedtest1.c.

5
6
7
8
9
10
11

12
13
14
15
16
17
18
..
45
46
47
48
49
50
51

52
53
54
55
56
57
58
...
285
286
287
288
289
290
291
















292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
...
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
...
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
...
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
...
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
...
782
783
784
785
786
787
788



789
790
791
792
793
794
795
...
921
922
923
924
925
926
927

928
929
930
931
932
933
934
*/
static const char zHelp[] =
  "Usage: %s [--options] DATABASE\n"
  "Options:\n"
  "  --autovacuum        Enable AUTOVACUUM mode\n"
  "  --cachesize N       Set the cache size to N\n" 
  "  --exclusive         Enable locking_mode=EXCLUSIVE\n"

  "  --heap SZ MIN       Memory allocator uses SZ bytes & min allocation MIN\n"
  "  --incrvacuum        Enable incremenatal vacuum mode\n"
  "  --journalmode M     Set the journal_mode to MODE\n"
  "  --key KEY           Set the encryption key to KEY\n"
  "  --lookaside N SZ    Configure lookaside for N slots of SZ bytes each\n"
  "  --nosync            Set PRAGMA synchronous=OFF\n"
  "  --notnull           Add NOT NULL constraints to table columns\n"
................................................................................
  sqlite3 *db;               /* The open database connection */
  sqlite3_stmt *pStmt;       /* Current SQL statement */
  sqlite3_int64 iStart;      /* Start-time for the current test */
  sqlite3_int64 iTotal;      /* Total time */
  int bWithoutRowid;         /* True for --without-rowid */
  int bReprepare;            /* True to reprepare the SQL on each rerun */
  int bSqlOnly;              /* True to print the SQL once only */

  int szTest;                /* Scale factor for test iterations */
  const char *zWR;           /* Might be WITHOUT ROWID */
  const char *zNN;           /* Might be NOT NULL */
  const char *zPK;           /* Might be UNIQUE or PRIMARY KEY */
  unsigned int x, y;         /* Pseudo-random number generator state */
  int nResult;               /* Size of the current result */
  char zResult[3000];        /* Text of the current result */
................................................................................
/* Report end of testing */
void speedtest1_final(void){
  if( !g.bSqlOnly ){
    printf("       TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
           (int)(g.iTotal/1000), (int)(g.iTotal%1000));
  }
}

















/* Run SQL */
void speedtest1_exec(const char *zFormat, ...){
  va_list ap;
  char *zSql;
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  if( g.bSqlOnly ){
    int n = (int)strlen(zSql);
    while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
    printf("%.*s;\n", n, zSql);
  }else{
    char *zErrMsg = 0;
    int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
    if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
    if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
  }
  sqlite3_free(zSql);
................................................................................
void speedtest1_prepare(const char *zFormat, ...){
  va_list ap;
  char *zSql;
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  if( g.bSqlOnly ){
    int n = (int)strlen(zSql);
    while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
    printf("%.*s;\n", n, zSql);
  }else{
    int rc;
    if( g.pStmt ) sqlite3_finalize(g.pStmt);
    rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
    if( rc ){
      fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
    }
................................................................................
    speedtest1_run();
  }
  speedtest1_exec("COMMIT");
  speedtest1_end_test();


  speedtest1_begin_test(150, "CREATE INDEX five times");
  speedtest1_exec(
      "BEGIN;\n"
      "CREATE UNIQUE INDEX t1b ON t1(b);\n"
      "CREATE INDEX t1c ON t1(c);\n"
      "CREATE UNIQUE INDEX t2b ON t2(b);\n"
      "CREATE INDEX t2c ON t2(c DESC);\n"
      "CREATE INDEX t3bc ON t3(b,c);\n"
      "COMMIT;\n"
  );
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
................................................................................
  speedtest1_exec("CREATE INDEX t4c ON t4(c)");
  speedtest1_exec("INSERT INTO t4 SELECT * FROM t1");
  speedtest1_exec("COMMIT");
  speedtest1_end_test();

  n = sz;
  speedtest1_begin_test(190, "DELETE and REFILL one table", n);
  speedtest1_exec(
    "DELETE FROM t2;"
    "INSERT INTO t2 SELECT * FROM t1;"
  );
  speedtest1_end_test();


  speedtest1_begin_test(200, "VACUUM");
  speedtest1_exec("VACUUM");
  speedtest1_end_test();

................................................................................
  speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
  speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1");
  speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1");
  speedtest1_end_test();

  speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
  speedtest1_exec("DELETE FROM t2;");
  speedtest1_exec(
     "INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)==(a&1);"
     "INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)<>(a&1);"
  );
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(310, "%d four-ways joins", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
................................................................................
        doAutovac = 1;
      }else if( strcmp(z,"cachesize")==0 ){
        if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
        i++;
        cacheSize = integerValue(argv[i]);
      }else if( strcmp(z,"exclusive")==0 ){
        doExclusive = 1;



      }else if( strcmp(z,"heap")==0 ){
        if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
        nHeap = integerValue(argv[i+1]);
        mnHeap = integerValue(argv[i+2]);
        i += 2;
      }else if( strcmp(z,"incrvacuum")==0 ){
        doIncrvac = 1;
................................................................................
  if( doExclusive ){
    speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
  }
  if( zJMode ){
    speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
  }


  if( strcmp(zTSet,"main")==0 ){
    testset_main();
  }else if( strcmp(zTSet,"debug1")==0 ){
    testset_debug1();
  }else{
    fatal_error("unknown testset: \"%s\"\n", zTSet);
  }







>







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









|
<
<







 







|
<
<







 







|
<
|
|
|
|
|
|
<







 







<
|
|
<







 







|
|
|
|







 







>
>
>







 







>







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
..
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
...
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319


320
321
322
323
324
325
326
...
330
331
332
333
334
335
336
337


338
339
340
341
342
343
344
...
507
508
509
510
511
512
513
514

515
516
517
518
519
520

521
522
523
524
525
526
527
...
587
588
589
590
591
592
593

594
595

596
597
598
599
600
601
602
...
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
...
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
...
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
*/
static const char zHelp[] =
  "Usage: %s [--options] DATABASE\n"
  "Options:\n"
  "  --autovacuum        Enable AUTOVACUUM mode\n"
  "  --cachesize N       Set the cache size to N\n" 
  "  --exclusive         Enable locking_mode=EXCLUSIVE\n"
  "  --explain           Like --sqlonly but with added EXPLAIN keywords\n"
  "  --heap SZ MIN       Memory allocator uses SZ bytes & min allocation MIN\n"
  "  --incrvacuum        Enable incremenatal vacuum mode\n"
  "  --journalmode M     Set the journal_mode to MODE\n"
  "  --key KEY           Set the encryption key to KEY\n"
  "  --lookaside N SZ    Configure lookaside for N slots of SZ bytes each\n"
  "  --nosync            Set PRAGMA synchronous=OFF\n"
  "  --notnull           Add NOT NULL constraints to table columns\n"
................................................................................
  sqlite3 *db;               /* The open database connection */
  sqlite3_stmt *pStmt;       /* Current SQL statement */
  sqlite3_int64 iStart;      /* Start-time for the current test */
  sqlite3_int64 iTotal;      /* Total time */
  int bWithoutRowid;         /* True for --without-rowid */
  int bReprepare;            /* True to reprepare the SQL on each rerun */
  int bSqlOnly;              /* True to print the SQL once only */
  int bExplain;              /* Print SQL with EXPLAIN prefix */
  int szTest;                /* Scale factor for test iterations */
  const char *zWR;           /* Might be WITHOUT ROWID */
  const char *zNN;           /* Might be NOT NULL */
  const char *zPK;           /* Might be UNIQUE or PRIMARY KEY */
  unsigned int x, y;         /* Pseudo-random number generator state */
  int nResult;               /* Size of the current result */
  char zResult[3000];        /* Text of the current result */
................................................................................
/* Report end of testing */
void speedtest1_final(void){
  if( !g.bSqlOnly ){
    printf("       TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
           (int)(g.iTotal/1000), (int)(g.iTotal%1000));
  }
}

/* Print an SQL statement to standard output */
static void printSql(const char *zSql){
  int n = (int)strlen(zSql);
  while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
  if( g.bExplain ) printf("EXPLAIN ");
  printf("%.*s;\n", n, zSql);
  if( g.bExplain
   && (sqlite3_strglob("CREATE *", zSql)
     || sqlite3_strglob("DROP *", zSql)
     || sqlite3_strglob("ALTER *", zSql)
      )
  ){
    printf("%.*s;\n", n, zSql);
  }
}

/* Run SQL */
void speedtest1_exec(const char *zFormat, ...){
  va_list ap;
  char *zSql;
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  if( g.bSqlOnly ){
    printSql(zSql);


  }else{
    char *zErrMsg = 0;
    int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
    if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
    if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
  }
  sqlite3_free(zSql);
................................................................................
void speedtest1_prepare(const char *zFormat, ...){
  va_list ap;
  char *zSql;
  va_start(ap, zFormat);
  zSql = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
  if( g.bSqlOnly ){
    printSql(zSql);


  }else{
    int rc;
    if( g.pStmt ) sqlite3_finalize(g.pStmt);
    rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
    if( rc ){
      fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
    }
................................................................................
    speedtest1_run();
  }
  speedtest1_exec("COMMIT");
  speedtest1_end_test();


  speedtest1_begin_test(150, "CREATE INDEX five times");
  speedtest1_exec("BEGIN;");

  speedtest1_exec("CREATE UNIQUE INDEX t1b ON t1(b);");
  speedtest1_exec("CREATE INDEX t1c ON t1(c);");
  speedtest1_exec("CREATE UNIQUE INDEX t2b ON t2(b);");
  speedtest1_exec("CREATE INDEX t2c ON t2(c DESC);");
  speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
  speedtest1_exec("COMMIT;");

  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
................................................................................
  speedtest1_exec("CREATE INDEX t4c ON t4(c)");
  speedtest1_exec("INSERT INTO t4 SELECT * FROM t1");
  speedtest1_exec("COMMIT");
  speedtest1_end_test();

  n = sz;
  speedtest1_begin_test(190, "DELETE and REFILL one table", n);

  speedtest1_exec("DELETE FROM t2;");
  speedtest1_exec("INSERT INTO t2 SELECT * FROM t1;");

  speedtest1_end_test();


  speedtest1_begin_test(200, "VACUUM");
  speedtest1_exec("VACUUM");
  speedtest1_end_test();

................................................................................
  speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
  speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1");
  speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1");
  speedtest1_end_test();

  speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
  speedtest1_exec("DELETE FROM t2;");
  speedtest1_exec("INSERT INTO t2(a,b,c)\n"
                  " SELECT a,b,c FROM t1  WHERE (b&1)==(a&1);");
  speedtest1_exec("INSERT INTO t2(a,b,c)\n"
                  " SELECT a,b,c FROM t1  WHERE (b&1)<>(a&1);");
  speedtest1_end_test();


  n = sz/5;
  speedtest1_begin_test(310, "%d four-ways joins", n);
  speedtest1_exec("BEGIN");
  speedtest1_prepare(
................................................................................
        doAutovac = 1;
      }else if( strcmp(z,"cachesize")==0 ){
        if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
        i++;
        cacheSize = integerValue(argv[i]);
      }else if( strcmp(z,"exclusive")==0 ){
        doExclusive = 1;
      }else if( strcmp(z,"explain")==0 ){
        g.bSqlOnly = 1;
        g.bExplain = 1;
      }else if( strcmp(z,"heap")==0 ){
        if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
        nHeap = integerValue(argv[i+1]);
        mnHeap = integerValue(argv[i+2]);
        i += 2;
      }else if( strcmp(z,"incrvacuum")==0 ){
        doIncrvac = 1;
................................................................................
  if( doExclusive ){
    speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
  }
  if( zJMode ){
    speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
  }

  if( g.bExplain ) printf(".explain\n.echo on\n");
  if( strcmp(zTSet,"main")==0 ){
    testset_main();
  }else if( strcmp(zTSet,"debug1")==0 ){
    testset_debug1();
  }else{
    fatal_error("unknown testset: \"%s\"\n", zTSet);
  }