/ Check-in [e7d34ec6]
Login

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

Overview
Comment:In the shell tool, if an "EXPLAIN" command is executed in ".explain on" mode, attempt to automatically indent the bodies of loops in the output VDBE program.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e7d34ec6814ed4606a6d5d7f68c218ae4d25e666
User & Date: dan 2013-11-13 18:35:01
Context
2013-11-13
20:46
Merge the skip-scan enhancement into trunk. check-in: b0bb975c user: drh tags: trunk
19:01
Import the "PRAGMA vdbe_eqp" enhancement and the enhanced EXPLAIN formatting the shell from trunk. Fix a bug in skip-scan and add a test case to prevent a regression. Closed-Leaf check-in: f668616a user: drh tags: skip-scan
18:35
In the shell tool, if an "EXPLAIN" command is executed in ".explain on" mode, attempt to automatically indent the bodies of loops in the output VDBE program. check-in: e7d34ec6 user: dan tags: trunk
17:58
Add the "PRAGMA vdbe_eqp" command, only available with SQLITE_DEBUG. Simplify some of the other debugging logic. check-in: 8ce33f4c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

   460    460                            ** .explain ON */
   461    461     char outfile[FILENAME_MAX]; /* Filename for *out */
   462    462     const char *zDbFilename;    /* name of the database file */
   463    463     char *zFreeOnClose;         /* Filename to free when closing */
   464    464     const char *zVfs;           /* Name of VFS to use */
   465    465     sqlite3_stmt *pStmt;   /* Current statement if any. */
   466    466     FILE *pLog;            /* Write log output here */
          467  +  int *aiIndent;         /* Array of indents used in MODE_Explain */
          468  +  int nIndent;           /* Size of array aiIndent[] */
   467    469   };
   468    470   
   469    471   /*
   470    472   ** These are the allowed modes.
   471    473   */
   472    474   #define MODE_Line     0  /* One column per line.  Blank line between records */
   473    475   #define MODE_Column   1  /* One record per line in neat columns */
................................................................................
   761    763         for(i=0; i<nArg; i++){
   762    764           int w;
   763    765           if( i<ArraySize(p->actualWidth) ){
   764    766              w = p->actualWidth[i];
   765    767           }else{
   766    768              w = 10;
   767    769           }
   768         -        if( p->mode==MODE_Explain && azArg[i] && 
   769         -           strlen30(azArg[i])>w ){
          770  +        if( p->mode==MODE_Explain && azArg[i] && strlen30(azArg[i])>w ){
   770    771             w = strlen30(azArg[i]);
          772  +        }
          773  +        if( i==1 && p->aiIndent && p->pStmt ){
          774  +          int iOp = sqlite3_column_int(p->pStmt, 0);
          775  +          if( iOp<p->nIndent ){
          776  +            fprintf(p->out, "%*.s", p->aiIndent[iOp], "");
          777  +          }
   771    778           }
   772    779           if( w<0 ){
   773    780             fprintf(p->out,"%*.*s%s",-w,-w,
   774    781                 azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");
   775    782           }else{
   776    783             fprintf(p->out,"%-*.*s%s",w,w,
   777    784                 azArg[i] ? azArg[i] : p->nullvalue, i==nArg-1 ? "\n": "  ");
................................................................................
  1136   1143       fprintf(pArg->out, "Autoindex Inserts:                   %d\n", iCur);
  1137   1144       iCur = sqlite3_stmt_status(pArg->pStmt, SQLITE_STMTSTATUS_VM_STEP, bReset);
  1138   1145       fprintf(pArg->out, "Virtual Machine Steps:               %d\n", iCur);
  1139   1146     }
  1140   1147   
  1141   1148     return 0;
  1142   1149   }
         1150  +
         1151  +/*
         1152  +** Parameter azArray points to a zero-terminated array of strings. zStr
         1153  +** points to a single nul-terminated string. Return non-zero if zStr
         1154  +** is equal, according to strcmp(), to any of the strings in the array.
         1155  +** Otherwise, return zero.
         1156  +*/
         1157  +static int str_in_array(const char *zStr, const char **azArray){
         1158  +  int i;
         1159  +  for(i=0; azArray[i]; i++){
         1160  +    if( 0==strcmp(zStr, azArray[i]) ) return 1;
         1161  +  }
         1162  +  return 0;
         1163  +}
         1164  +
         1165  +/*
         1166  +** If compiled statement pSql appears to be an EXPLAIN statement, allocate
         1167  +** and populate the callback_data.aiIndent[] array with the number of
         1168  +** spaces each opcode should be indented before it is output. 
         1169  +**
         1170  +** The indenting rules are:
         1171  +**
         1172  +**     * For each "Next", "Prev", "VNext" or "VPrev" instruction, indent
         1173  +**       all opcodes that occur between the p2 jump destination and the opcode
         1174  +**       itself by 2 spaces.
         1175  +**
         1176  +**     * For each "Goto", if the jump destination is a "Yield" instruction
         1177  +**       that occurs earlier in the program than the Goto itself, indent
         1178  +**       all opcodes between the "Yield" and "Goto" by 2 spaces.
         1179  +*/
         1180  +static void explain_data_prepare(struct callback_data *p, sqlite3_stmt *pSql){
         1181  +  const char *zSql;               /* The text of the SQL statement */
         1182  +  const char *z;                  /* Used to check if this is an EXPLAIN */
         1183  +  int *abYield = 0;               /* True if op is an OP_Yield */
         1184  +  int nAlloc = 0;                 /* Allocated size of p->aiIndent[], abYield */
         1185  +  int iOp;
         1186  +
         1187  +  const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", 0 };
         1188  +  const char *azYield[] = { "Yield", 0 };
         1189  +  const char *azGoto[] = { "Goto", 0 };
         1190  +
         1191  +  /* Try to figure out if this is really an EXPLAIN statement. If this
         1192  +  ** cannot be verified, return early.  */
         1193  +  zSql = sqlite3_sql(pSql);
         1194  +  if( zSql==0 ) return;
         1195  +  for(z=zSql; *z==' ' || *z=='\t' || *z=='\n' || *z=='\f' || *z=='\r'; z++);
         1196  +  if( sqlite3_strnicmp(z, "explain", 7) ) return;
         1197  +
         1198  +  for(iOp=0; SQLITE_ROW==sqlite3_step(pSql); iOp++){
         1199  +    int i;
         1200  +    const char *zOp = (const char*)sqlite3_column_text(pSql, 1);
         1201  +    int p2 = sqlite3_column_int(pSql, 3);
         1202  +
         1203  +    /* Grow the p->aiIndent array as required */
         1204  +    if( iOp>=nAlloc ){
         1205  +      nAlloc += 100;
         1206  +      p->aiIndent = (int*)sqlite3_realloc(p->aiIndent, nAlloc*sizeof(int));
         1207  +      abYield = (int*)sqlite3_realloc(abYield, nAlloc*sizeof(int));
         1208  +    }
         1209  +    abYield[iOp] = str_in_array(zOp, azYield);
         1210  +    p->aiIndent[iOp] = 0;
         1211  +    p->nIndent = iOp+1;
         1212  +
         1213  +    if( str_in_array(zOp, azNext) ){
         1214  +      for(i=p2; i<iOp; i++) p->aiIndent[i] += 2;
         1215  +    }
         1216  +    if( str_in_array(zOp, azGoto) && p2<p->nIndent && abYield[p2] ){
         1217  +      for(i=p2+1; i<iOp; i++) p->aiIndent[i] += 2;
         1218  +    }
         1219  +  }
         1220  +
         1221  +  sqlite3_free(abYield);
         1222  +  sqlite3_reset(pSql);
         1223  +}
         1224  +
         1225  +/*
         1226  +** Free the array allocated by explain_data_prepare().
         1227  +*/
         1228  +static void explain_data_delete(struct callback_data *p){
         1229  +  sqlite3_free(p->aiIndent);
         1230  +  p->aiIndent = 0;
         1231  +  p->nIndent = 0;
         1232  +}
  1143   1233   
  1144   1234   /*
  1145   1235   ** Execute a statement or set of statements.  Print 
  1146   1236   ** any result rows/columns depending on the current mode 
  1147   1237   ** set via the supplied callback.
  1148   1238   **
  1149   1239   ** This is very similar to SQLite's built-in sqlite3_exec() 
................................................................................
  1197   1287         if( pArg && pArg->mode==MODE_Explain ){
  1198   1288           const char *zExplain = 0;
  1199   1289           sqlite3_test_control(SQLITE_TESTCTRL_EXPLAIN_STMT, pStmt, &zExplain);
  1200   1290           if( zExplain && zExplain[0] ){
  1201   1291             fprintf(pArg->out, "%s", zExplain);
  1202   1292           }
  1203   1293         }
         1294  +
         1295  +      /* If the shell is currently in ".explain" mode, gather the extra
         1296  +      ** data required to add indents to the output.*/
         1297  +      if( pArg->mode==MODE_Explain ){
         1298  +        explain_data_prepare(pArg, pStmt);
         1299  +      }
  1204   1300   
  1205   1301         /* perform the first step.  this will tell us if we
  1206   1302         ** have a result set or not and how wide it is.
  1207   1303         */
  1208   1304         rc = sqlite3_step(pStmt);
  1209   1305         /* if we have a result set... */
  1210   1306         if( SQLITE_ROW == rc ){
................................................................................
  1254   1350             }
  1255   1351           }else{
  1256   1352             do{
  1257   1353               rc = sqlite3_step(pStmt);
  1258   1354             } while( rc == SQLITE_ROW );
  1259   1355           }
  1260   1356         }
         1357  +
         1358  +      explain_data_delete(pArg);
  1261   1359   
  1262   1360         /* print usage stats if stats on */
  1263   1361         if( pArg && pArg->statsOn ){
  1264   1362           display_stats(db, pArg, 0);
  1265   1363         }
  1266   1364   
  1267   1365         /* Finalize the statement just executed. If this fails, save a