/ Check-in [96397263]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/shell.c.

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

Changes to test/shell2.test.

   151    151   INSERT INTO foo1(a) VALUES(2);
   152    152   INSERT INTO foo2(b) VALUES(2);
   153    153   SELECT * FROM foo1;
   154    154   1
   155    155   2
   156    156   SELECT * FROM foo2;
   157    157   1
   158         -2}}
          158  +2
          159  +}}
   159    160   
   160    161   # Test with echo on and headers on using dot command and 
   161    162   # multiple commands per line.
   162    163   # NB. whitespace is important
   163    164   do_test shell2-1.4.6 {
   164    165     forcedelete foo.db
   165    166     catchcmd "foo.db" {.echo ON
................................................................................
   188    189   SELECT * FROM foo1;
   189    190   a
   190    191   1
   191    192   2
   192    193   SELECT * FROM foo2;
   193    194   b
   194    195   1
   195         -2}}
          196  +2
          197  +}}
   196    198   
   197    199   finish_test

Changes to test/speedtest1.c.

     5      5   */
     6      6   static const char zHelp[] =
     7      7     "Usage: %s [--options] DATABASE\n"
     8      8     "Options:\n"
     9      9     "  --autovacuum        Enable AUTOVACUUM mode\n"
    10     10     "  --cachesize N       Set the cache size to N\n" 
    11     11     "  --exclusive         Enable locking_mode=EXCLUSIVE\n"
           12  +  "  --explain           Like --sqlonly but with added EXPLAIN keywords\n"
    12     13     "  --heap SZ MIN       Memory allocator uses SZ bytes & min allocation MIN\n"
    13     14     "  --incrvacuum        Enable incremenatal vacuum mode\n"
    14     15     "  --journalmode M     Set the journal_mode to MODE\n"
    15     16     "  --key KEY           Set the encryption key to KEY\n"
    16     17     "  --lookaside N SZ    Configure lookaside for N slots of SZ bytes each\n"
    17     18     "  --nosync            Set PRAGMA synchronous=OFF\n"
    18     19     "  --notnull           Add NOT NULL constraints to table columns\n"
................................................................................
    45     46     sqlite3 *db;               /* The open database connection */
    46     47     sqlite3_stmt *pStmt;       /* Current SQL statement */
    47     48     sqlite3_int64 iStart;      /* Start-time for the current test */
    48     49     sqlite3_int64 iTotal;      /* Total time */
    49     50     int bWithoutRowid;         /* True for --without-rowid */
    50     51     int bReprepare;            /* True to reprepare the SQL on each rerun */
    51     52     int bSqlOnly;              /* True to print the SQL once only */
           53  +  int bExplain;              /* Print SQL with EXPLAIN prefix */
    52     54     int szTest;                /* Scale factor for test iterations */
    53     55     const char *zWR;           /* Might be WITHOUT ROWID */
    54     56     const char *zNN;           /* Might be NOT NULL */
    55     57     const char *zPK;           /* Might be UNIQUE or PRIMARY KEY */
    56     58     unsigned int x, y;         /* Pseudo-random number generator state */
    57     59     int nResult;               /* Size of the current result */
    58     60     char zResult[3000];        /* Text of the current result */
................................................................................
   285    287   /* Report end of testing */
   286    288   void speedtest1_final(void){
   287    289     if( !g.bSqlOnly ){
   288    290       printf("       TOTAL%.*s %4d.%03ds\n", NAMEWIDTH-5, zDots,
   289    291              (int)(g.iTotal/1000), (int)(g.iTotal%1000));
   290    292     }
   291    293   }
          294  +
          295  +/* Print an SQL statement to standard output */
          296  +static void printSql(const char *zSql){
          297  +  int n = (int)strlen(zSql);
          298  +  while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
          299  +  if( g.bExplain ) printf("EXPLAIN ");
          300  +  printf("%.*s;\n", n, zSql);
          301  +  if( g.bExplain
          302  +   && (sqlite3_strglob("CREATE *", zSql)
          303  +     || sqlite3_strglob("DROP *", zSql)
          304  +     || sqlite3_strglob("ALTER *", zSql)
          305  +      )
          306  +  ){
          307  +    printf("%.*s;\n", n, zSql);
          308  +  }
          309  +}
   292    310   
   293    311   /* Run SQL */
   294    312   void speedtest1_exec(const char *zFormat, ...){
   295    313     va_list ap;
   296    314     char *zSql;
   297    315     va_start(ap, zFormat);
   298    316     zSql = sqlite3_vmprintf(zFormat, ap);
   299    317     va_end(ap);
   300    318     if( g.bSqlOnly ){
   301         -    int n = (int)strlen(zSql);
   302         -    while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
   303         -    printf("%.*s;\n", n, zSql);
          319  +    printSql(zSql);
   304    320     }else{
   305    321       char *zErrMsg = 0;
   306    322       int rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
   307    323       if( zErrMsg ) fatal_error("SQL error: %s\n%s\n", zErrMsg, zSql);
   308    324       if( rc!=SQLITE_OK ) fatal_error("exec error: %s\n", sqlite3_errmsg(g.db));
   309    325     }
   310    326     sqlite3_free(zSql);
................................................................................
   314    330   void speedtest1_prepare(const char *zFormat, ...){
   315    331     va_list ap;
   316    332     char *zSql;
   317    333     va_start(ap, zFormat);
   318    334     zSql = sqlite3_vmprintf(zFormat, ap);
   319    335     va_end(ap);
   320    336     if( g.bSqlOnly ){
   321         -    int n = (int)strlen(zSql);
   322         -    while( n>0 && (zSql[n-1]==';' || isspace(zSql[n-1])) ){ n--; }
   323         -    printf("%.*s;\n", n, zSql);
          337  +    printSql(zSql);
   324    338     }else{
   325    339       int rc;
   326    340       if( g.pStmt ) sqlite3_finalize(g.pStmt);
   327    341       rc = sqlite3_prepare_v2(g.db, zSql, -1, &g.pStmt, 0);
   328    342       if( rc ){
   329    343         fatal_error("SQL error: %s\n", sqlite3_errmsg(g.db));
   330    344       }
................................................................................
   493    507       speedtest1_run();
   494    508     }
   495    509     speedtest1_exec("COMMIT");
   496    510     speedtest1_end_test();
   497    511   
   498    512   
   499    513     speedtest1_begin_test(150, "CREATE INDEX five times");
   500         -  speedtest1_exec(
   501         -      "BEGIN;\n"
   502         -      "CREATE UNIQUE INDEX t1b ON t1(b);\n"
   503         -      "CREATE INDEX t1c ON t1(c);\n"
   504         -      "CREATE UNIQUE INDEX t2b ON t2(b);\n"
   505         -      "CREATE INDEX t2c ON t2(c DESC);\n"
   506         -      "CREATE INDEX t3bc ON t3(b,c);\n"
   507         -      "COMMIT;\n"
   508         -  );
          514  +  speedtest1_exec("BEGIN;");
          515  +  speedtest1_exec("CREATE UNIQUE INDEX t1b ON t1(b);");
          516  +  speedtest1_exec("CREATE INDEX t1c ON t1(c);");
          517  +  speedtest1_exec("CREATE UNIQUE INDEX t2b ON t2(b);");
          518  +  speedtest1_exec("CREATE INDEX t2c ON t2(c DESC);");
          519  +  speedtest1_exec("CREATE INDEX t3bc ON t3(b,c);");
          520  +  speedtest1_exec("COMMIT;");
   509    521     speedtest1_end_test();
   510    522   
   511    523   
   512    524     n = sz/5;
   513    525     speedtest1_begin_test(160, "%d SELECTS, numeric BETWEEN, indexed", n);
   514    526     speedtest1_exec("BEGIN");
   515    527     speedtest1_prepare(
................................................................................
   575    587     speedtest1_exec("CREATE INDEX t4c ON t4(c)");
   576    588     speedtest1_exec("INSERT INTO t4 SELECT * FROM t1");
   577    589     speedtest1_exec("COMMIT");
   578    590     speedtest1_end_test();
   579    591   
   580    592     n = sz;
   581    593     speedtest1_begin_test(190, "DELETE and REFILL one table", n);
   582         -  speedtest1_exec(
   583         -    "DELETE FROM t2;"
   584         -    "INSERT INTO t2 SELECT * FROM t1;"
   585         -  );
          594  +  speedtest1_exec("DELETE FROM t2;");
          595  +  speedtest1_exec("INSERT INTO t2 SELECT * FROM t1;");
   586    596     speedtest1_end_test();
   587    597   
   588    598   
   589    599     speedtest1_begin_test(200, "VACUUM");
   590    600     speedtest1_exec("VACUUM");
   591    601     speedtest1_end_test();
   592    602   
................................................................................
   674    684     speedtest1_begin_test(290, "Refill two %d-row tables using REPLACE", sz);
   675    685     speedtest1_exec("REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1");
   676    686     speedtest1_exec("REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1");
   677    687     speedtest1_end_test();
   678    688   
   679    689     speedtest1_begin_test(300, "Refill a %d-row table using (b&1)==(a&1)", sz);
   680    690     speedtest1_exec("DELETE FROM t2;");
   681         -  speedtest1_exec(
   682         -     "INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)==(a&1);"
   683         -     "INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)<>(a&1);"
   684         -  );
          691  +  speedtest1_exec("INSERT INTO t2(a,b,c)\n"
          692  +                  " SELECT a,b,c FROM t1  WHERE (b&1)==(a&1);");
          693  +  speedtest1_exec("INSERT INTO t2(a,b,c)\n"
          694  +                  " SELECT a,b,c FROM t1  WHERE (b&1)<>(a&1);");
   685    695     speedtest1_end_test();
   686    696   
   687    697   
   688    698     n = sz/5;
   689    699     speedtest1_begin_test(310, "%d four-ways joins", n);
   690    700     speedtest1_exec("BEGIN");
   691    701     speedtest1_prepare(
................................................................................
   782    792           doAutovac = 1;
   783    793         }else if( strcmp(z,"cachesize")==0 ){
   784    794           if( i>=argc-1 ) fatal_error("missing argument on %s\n", argv[i]);
   785    795           i++;
   786    796           cacheSize = integerValue(argv[i]);
   787    797         }else if( strcmp(z,"exclusive")==0 ){
   788    798           doExclusive = 1;
          799  +      }else if( strcmp(z,"explain")==0 ){
          800  +        g.bSqlOnly = 1;
          801  +        g.bExplain = 1;
   789    802         }else if( strcmp(z,"heap")==0 ){
   790    803           if( i>=argc-2 ) fatal_error("missing arguments on %s\n", argv[i]);
   791    804           nHeap = integerValue(argv[i+1]);
   792    805           mnHeap = integerValue(argv[i+2]);
   793    806           i += 2;
   794    807         }else if( strcmp(z,"incrvacuum")==0 ){
   795    808           doIncrvac = 1;
................................................................................
   921    934     if( doExclusive ){
   922    935       speedtest1_exec("PRAGMA locking_mode=EXCLUSIVE");
   923    936     }
   924    937     if( zJMode ){
   925    938       speedtest1_exec("PRAGMA journal_mode=%s", zJMode);
   926    939     }
   927    940   
          941  +  if( g.bExplain ) printf(".explain\n.echo on\n");
   928    942     if( strcmp(zTSet,"main")==0 ){
   929    943       testset_main();
   930    944     }else if( strcmp(zTSet,"debug1")==0 ){
   931    945       testset_debug1();
   932    946     }else{
   933    947       fatal_error("unknown testset: \"%s\"\n", zTSet);
   934    948     }