/ Check-in [3e217d62]
Login

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

Overview
Comment:CLI enhancement: Add the ".eqp full" option, that shows both the EXPLAIN QUERY PLAN and the EXPLAIN output for each command run. Also disable any ".wheretrace" and ".selecttrace" when showing EQP output.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3e217d6265ecd16db783bed7ce1d9d0f9c4828bb
User & Date: drh 2016-04-15 15:03:27
Context
2016-04-18
13:09
The last parameter to pread64() and pwrite64() should be off64_t, not off_t. check-in: 3a7d7298 user: drh tags: trunk
2016-04-15
20:46
Add the experimental sqlite3rbu_vacuum() API function. For opening an RBU handle that rebuilds a database from scratch. check-in: 0216b48f user: dan tags: rbu-vacuum
15:06
Merge enhancements from trunk, especially the ".eqp full" enhancement to the command-line shell. check-in: 491f0dbd user: drh tags: skip-ahead-distinct
15:03
CLI enhancement: Add the ".eqp full" option, that shows both the EXPLAIN QUERY PLAN and the EXPLAIN output for each command run. Also disable any ".wheretrace" and ".selecttrace" when showing EQP output. check-in: 3e217d62 user: drh tags: trunk
2016-04-14
17:29
In the ICU extension toupper() and tolower() SQL functions, avoid calling u_strToUpper() or u_strToLower() a second time if the buffer passed to the first invocation turns out to be large enough. check-in: d23e5813 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

  1729   1729   
  1730   1730       if( str_in_array(zOp, azNext) ){
  1731   1731         for(i=p2op; i<iOp; i++) p->aiIndent[i] += 2;
  1732   1732       }
  1733   1733       if( str_in_array(zOp, azGoto) && p2op<p->nIndent
  1734   1734        && (abYield[p2op] || sqlite3_column_int(pSql, 2))
  1735   1735       ){
  1736         -      for(i=p2op+1; i<iOp; i++) p->aiIndent[i] += 2;
         1736  +      for(i=p2op; i<iOp; i++) p->aiIndent[i] += 2;
  1737   1737       }
  1738   1738     }
  1739   1739   
  1740   1740     p->iIndent = 0;
  1741   1741     sqlite3_free(abYield);
  1742   1742     sqlite3_reset(pSql);
  1743   1743   }
................................................................................
  1747   1747   */
  1748   1748   static void explain_data_delete(ShellState *p){
  1749   1749     sqlite3_free(p->aiIndent);
  1750   1750     p->aiIndent = 0;
  1751   1751     p->nIndent = 0;
  1752   1752     p->iIndent = 0;
  1753   1753   }
         1754  +
         1755  +/*
         1756  +** Disable and restore .wheretrace and .selecttrace settings.
         1757  +*/
         1758  +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
         1759  +extern int sqlite3SelectTrace;
         1760  +static int savedSelectTrace;
         1761  +#endif
         1762  +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
         1763  +extern int sqlite3WhereTrace;
         1764  +static int savedWhereTrace;
         1765  +#endif
         1766  +static void disable_debug_trace_modes(void){
         1767  +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
         1768  +  savedSelectTrace = sqlite3SelectTrace;
         1769  +  sqlite3SelectTrace = 0;
         1770  +#endif
         1771  +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
         1772  +  savedWhereTrace = sqlite3WhereTrace;
         1773  +  sqlite3WhereTrace = 0;
         1774  +#endif
         1775  +}
         1776  +static void restore_debug_trace_modes(void){
         1777  +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
         1778  +  sqlite3SelectTrace = savedSelectTrace;
         1779  +#endif
         1780  +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
         1781  +  sqlite3WhereTrace = savedWhereTrace;
         1782  +#endif
         1783  +}
         1784  +
         1785  +/*
         1786  +** Run a prepared statement
         1787  +*/
         1788  +static void exec_prepared_stmt(
         1789  +  ShellState *pArg,                                /* Pointer to ShellState */
         1790  +  sqlite3_stmt *pStmt,                             /* Statment to run */
         1791  +  int (*xCallback)(void*,int,char**,char**,int*)   /* Callback function */
         1792  +){
         1793  +  int rc;
         1794  +
         1795  +  /* perform the first step.  this will tell us if we
         1796  +  ** have a result set or not and how wide it is.
         1797  +  */
         1798  +  rc = sqlite3_step(pStmt);
         1799  +  /* if we have a result set... */
         1800  +  if( SQLITE_ROW == rc ){
         1801  +    /* if we have a callback... */
         1802  +    if( xCallback ){
         1803  +      /* allocate space for col name ptr, value ptr, and type */
         1804  +      int nCol = sqlite3_column_count(pStmt);
         1805  +      void *pData = sqlite3_malloc64(3*nCol*sizeof(const char*) + 1);
         1806  +      if( !pData ){
         1807  +        rc = SQLITE_NOMEM;
         1808  +      }else{
         1809  +        char **azCols = (char **)pData;      /* Names of result columns */
         1810  +        char **azVals = &azCols[nCol];       /* Results */
         1811  +        int *aiTypes = (int *)&azVals[nCol]; /* Result types */
         1812  +        int i, x;
         1813  +        assert(sizeof(int) <= sizeof(char *));
         1814  +        /* save off ptrs to column names */
         1815  +        for(i=0; i<nCol; i++){
         1816  +          azCols[i] = (char *)sqlite3_column_name(pStmt, i);
         1817  +        }
         1818  +        do{
         1819  +          /* extract the data and data types */
         1820  +          for(i=0; i<nCol; i++){
         1821  +            aiTypes[i] = x = sqlite3_column_type(pStmt, i);
         1822  +            if( x==SQLITE_BLOB && pArg && pArg->cMode==MODE_Insert ){
         1823  +              azVals[i] = "";
         1824  +            }else{
         1825  +              azVals[i] = (char*)sqlite3_column_text(pStmt, i);
         1826  +            }
         1827  +            if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){
         1828  +              rc = SQLITE_NOMEM;
         1829  +              break; /* from for */
         1830  +            }
         1831  +          } /* end for */
         1832  +
         1833  +          /* if data and types extracted successfully... */
         1834  +          if( SQLITE_ROW == rc ){
         1835  +            /* call the supplied callback with the result row data */
         1836  +            if( xCallback(pArg, nCol, azVals, azCols, aiTypes) ){
         1837  +              rc = SQLITE_ABORT;
         1838  +            }else{
         1839  +              rc = sqlite3_step(pStmt);
         1840  +            }
         1841  +          }
         1842  +        } while( SQLITE_ROW == rc );
         1843  +        sqlite3_free(pData);
         1844  +      }
         1845  +    }else{
         1846  +      do{
         1847  +        rc = sqlite3_step(pStmt);
         1848  +      } while( rc == SQLITE_ROW );
         1849  +    }
         1850  +  }
         1851  +}
  1754   1852   
  1755   1853   /*
  1756   1854   ** Execute a statement or set of statements.  Print
  1757   1855   ** any result rows/columns depending on the current mode
  1758   1856   ** set via the supplied callback.
  1759   1857   **
  1760   1858   ** This is very similar to SQLite's built-in sqlite3_exec()
................................................................................
  1775   1873     const char *zLeftover;          /* Tail of unprocessed SQL */
  1776   1874   
  1777   1875     if( pzErrMsg ){
  1778   1876       *pzErrMsg = NULL;
  1779   1877     }
  1780   1878   
  1781   1879     while( zSql[0] && (SQLITE_OK == rc) ){
         1880  +    static const char *zStmtSql;
  1782   1881       rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
  1783   1882       if( SQLITE_OK != rc ){
  1784   1883         if( pzErrMsg ){
  1785   1884           *pzErrMsg = save_err_msg(db);
  1786   1885         }
  1787   1886       }else{
  1788   1887         if( !pStmt ){
  1789   1888           /* this happens for a comment or white-space */
  1790   1889           zSql = zLeftover;
  1791   1890           while( IsSpace(zSql[0]) ) zSql++;
  1792   1891           continue;
  1793   1892         }
         1893  +      zStmtSql = sqlite3_sql(pStmt);
         1894  +      while( IsSpace(zStmtSql[0]) ) zStmtSql++;
  1794   1895   
  1795   1896         /* save off the prepared statment handle and reset row count */
  1796   1897         if( pArg ){
  1797   1898           pArg->pStmt = pStmt;
  1798   1899           pArg->cnt = 0;
  1799   1900         }
  1800   1901   
  1801   1902         /* echo the sql statement if echo on */
  1802   1903         if( pArg && pArg->echoOn ){
  1803         -        const char *zStmtSql = sqlite3_sql(pStmt);
  1804   1904           utf8_printf(pArg->out, "%s\n", zStmtSql ? zStmtSql : zSql);
  1805   1905         }
  1806   1906   
  1807   1907         /* Show the EXPLAIN QUERY PLAN if .eqp is on */
  1808         -      if( pArg && pArg->autoEQP ){
         1908  +      if( pArg && pArg->autoEQP && sqlite3_strlike("EXPLAIN%",zStmtSql,0)!=0 ){
  1809   1909           sqlite3_stmt *pExplain;
  1810         -        char *zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s",
  1811         -                                     sqlite3_sql(pStmt));
         1910  +        char *zEQP;
         1911  +        disable_debug_trace_modes();
         1912  +        zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql);
  1812   1913           rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
  1813   1914           if( rc==SQLITE_OK ){
  1814   1915             while( sqlite3_step(pExplain)==SQLITE_ROW ){
  1815   1916               raw_printf(pArg->out,"--EQP-- %d,",sqlite3_column_int(pExplain, 0));
  1816   1917               raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 1));
  1817   1918               raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 2));
  1818   1919               utf8_printf(pArg->out,"%s\n", sqlite3_column_text(pExplain, 3));
  1819   1920             }
  1820   1921           }
  1821   1922           sqlite3_finalize(pExplain);
  1822   1923           sqlite3_free(zEQP);
         1924  +        if( pArg->autoEQP>=2 ){
         1925  +          /* Also do an EXPLAIN for ".eqp full" mode */
         1926  +          zEQP = sqlite3_mprintf("EXPLAIN %s", zStmtSql);
         1927  +          rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
         1928  +          if( rc==SQLITE_OK ){
         1929  +            pArg->cMode = MODE_Explain;
         1930  +            explain_data_prepare(pArg, pExplain);
         1931  +            exec_prepared_stmt(pArg, pExplain, xCallback);
         1932  +            explain_data_delete(pArg);
         1933  +          }
         1934  +          sqlite3_finalize(pExplain);
         1935  +          sqlite3_free(zEQP);
         1936  +        }
         1937  +        restore_debug_trace_modes();
  1823   1938         }
  1824   1939   
  1825   1940         if( pArg ){
  1826   1941           pArg->cMode = pArg->mode;
  1827   1942           if( pArg->autoExplain
  1828   1943            && sqlite3_column_count(pStmt)==8
  1829         -         && sqlite3_strlike("%EXPLAIN%", sqlite3_sql(pStmt),0)==0
         1944  +         && sqlite3_strlike("EXPLAIN%", zStmtSql,0)==0
  1830   1945           ){
  1831   1946             pArg->cMode = MODE_Explain;
  1832   1947           }
  1833   1948   
  1834   1949           /* If the shell is currently in ".explain" mode, gather the extra
  1835   1950           ** data required to add indents to the output.*/
  1836   1951           if( pArg->cMode==MODE_Explain ){
  1837   1952             explain_data_prepare(pArg, pStmt);
  1838   1953           }
  1839   1954         }
  1840   1955   
  1841         -      /* perform the first step.  this will tell us if we
  1842         -      ** have a result set or not and how wide it is.
  1843         -      */
  1844         -      rc = sqlite3_step(pStmt);
  1845         -      /* if we have a result set... */
  1846         -      if( SQLITE_ROW == rc ){
  1847         -        /* if we have a callback... */
  1848         -        if( xCallback ){
  1849         -          /* allocate space for col name ptr, value ptr, and type */
  1850         -          int nCol = sqlite3_column_count(pStmt);
  1851         -          void *pData = sqlite3_malloc64(3*nCol*sizeof(const char*) + 1);
  1852         -          if( !pData ){
  1853         -            rc = SQLITE_NOMEM;
  1854         -          }else{
  1855         -            char **azCols = (char **)pData;      /* Names of result columns */
  1856         -            char **azVals = &azCols[nCol];       /* Results */
  1857         -            int *aiTypes = (int *)&azVals[nCol]; /* Result types */
  1858         -            int i, x;
  1859         -            assert(sizeof(int) <= sizeof(char *));
  1860         -            /* save off ptrs to column names */
  1861         -            for(i=0; i<nCol; i++){
  1862         -              azCols[i] = (char *)sqlite3_column_name(pStmt, i);
  1863         -            }
  1864         -            do{
  1865         -              /* extract the data and data types */
  1866         -              for(i=0; i<nCol; i++){
  1867         -                aiTypes[i] = x = sqlite3_column_type(pStmt, i);
  1868         -                if( x==SQLITE_BLOB && pArg && pArg->cMode==MODE_Insert ){
  1869         -                  azVals[i] = "";
  1870         -                }else{
  1871         -                  azVals[i] = (char*)sqlite3_column_text(pStmt, i);
  1872         -                }
  1873         -                if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){
  1874         -                  rc = SQLITE_NOMEM;
  1875         -                  break; /* from for */
  1876         -                }
  1877         -              } /* end for */
  1878         -
  1879         -              /* if data and types extracted successfully... */
  1880         -              if( SQLITE_ROW == rc ){
  1881         -                /* call the supplied callback with the result row data */
  1882         -                if( xCallback(pArg, nCol, azVals, azCols, aiTypes) ){
  1883         -                  rc = SQLITE_ABORT;
  1884         -                }else{
  1885         -                  rc = sqlite3_step(pStmt);
  1886         -                }
  1887         -              }
  1888         -            } while( SQLITE_ROW == rc );
  1889         -            sqlite3_free(pData);
  1890         -          }
  1891         -        }else{
  1892         -          do{
  1893         -            rc = sqlite3_step(pStmt);
  1894         -          } while( rc == SQLITE_ROW );
  1895         -        }
  1896         -      }
  1897         -
         1956  +      exec_prepared_stmt(pArg, pStmt, xCallback);
  1898   1957         explain_data_delete(pArg);
  1899   1958   
  1900   1959         /* print usage stats if stats on */
  1901   1960         if( pArg && pArg->statsOn ){
  1902   1961           display_stats(db, pArg, 0);
  1903   1962         }
  1904   1963   
................................................................................
  2080   2139     ".clone NEWDB           Clone data into NEWDB from the existing database\n"
  2081   2140     ".databases             List names and files of attached databases\n"
  2082   2141     ".dbinfo ?DB?           Show status information about the database\n"
  2083   2142     ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
  2084   2143     "                         If TABLE specified, only dump tables matching\n"
  2085   2144     "                         LIKE pattern TABLE.\n"
  2086   2145     ".echo on|off           Turn command echo on or off\n"
  2087         -  ".eqp on|off            Enable or disable automatic EXPLAIN QUERY PLAN\n"
         2146  +  ".eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN\n"
  2088   2147     ".exit                  Exit this program\n"
  2089   2148     ".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"
  2090   2149     ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n"
  2091   2150     ".headers on|off        Turn display of headers on or off\n"
  2092   2151     ".help                  Show this message\n"
  2093   2152     ".import FILE TABLE     Import data from FILE into TABLE\n"
  2094   2153     ".indexes ?TABLE?       Show names of all indexes\n"
................................................................................
  3250   3309         raw_printf(stderr, "Usage: .echo on|off\n");
  3251   3310         rc = 1;
  3252   3311       }
  3253   3312     }else
  3254   3313   
  3255   3314     if( c=='e' && strncmp(azArg[0], "eqp", n)==0 ){
  3256   3315       if( nArg==2 ){
  3257         -      p->autoEQP = booleanValue(azArg[1]);
         3316  +      if( strcmp(azArg[1],"full")==0 ){
         3317  +        p->autoEQP = 2;
         3318  +      }else{
         3319  +        p->autoEQP = booleanValue(azArg[1]);
         3320  +      }
  3258   3321       }else{
  3259         -      raw_printf(stderr, "Usage: .eqp on|off\n");
         3322  +      raw_printf(stderr, "Usage: .eqp on|off|full\n");
  3260   3323         rc = 1;
  3261   3324       }
  3262   3325     }else
  3263   3326   
  3264   3327     if( c=='e' && strncmp(azArg[0], "exit", n)==0 ){
  3265   3328       if( nArg>1 && (rc = (int)integerValue(azArg[1]))!=0 ) exit(rc);
  3266   3329       rc = 2;
................................................................................
  4013   4076       }else{
  4014   4077         rc = 0;
  4015   4078       }
  4016   4079     }else
  4017   4080   
  4018   4081   #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
  4019   4082     if( c=='s' && n==11 && strncmp(azArg[0], "selecttrace", n)==0 ){
  4020         -    extern int sqlite3SelectTrace;
  4021   4083       sqlite3SelectTrace = integerValue(azArg[1]);
  4022   4084     }else
  4023   4085   #endif
  4024   4086   
  4025   4087   #if defined(SQLITE_ENABLE_SESSION)
  4026   4088     if( c=='s' && strncmp(azArg[0],"session",n)==0 && n>=3 ){
  4027   4089       OpenSession *pSession = &p->aSession[0];
................................................................................
  4273   4335       }
  4274   4336       x = system(zCmd);
  4275   4337       sqlite3_free(zCmd);
  4276   4338       if( x ) raw_printf(stderr, "System command returns %d\n", x);
  4277   4339     }else
  4278   4340   
  4279   4341     if( c=='s' && strncmp(azArg[0], "show", n)==0 ){
         4342  +    static const char *azBool[] = { "off", "on", "full", "unk" };
  4280   4343       int i;
  4281   4344       if( nArg!=1 ){
  4282   4345         raw_printf(stderr, "Usage: .show\n");
  4283   4346         rc = 1;
  4284   4347         goto meta_command_exit;
  4285   4348       }
  4286         -    utf8_printf(p->out, "%12.12s: %s\n","echo", p->echoOn ? "on" : "off");
  4287         -    utf8_printf(p->out, "%12.12s: %s\n","eqp", p->autoEQP ? "on" : "off");
         4349  +    utf8_printf(p->out, "%12.12s: %s\n","echo", azBool[p->echoOn!=0]);
         4350  +    utf8_printf(p->out, "%12.12s: %s\n","eqp", azBool[p->autoEQP&3]);
  4288   4351       utf8_printf(p->out, "%12.12s: %s\n","explain",
  4289   4352            p->mode==MODE_Explain ? "on" : p->autoExplain ? "auto" : "off");
  4290         -    utf8_printf(p->out,"%12.12s: %s\n","headers", p->showHeader ? "on" : "off");
         4353  +    utf8_printf(p->out,"%12.12s: %s\n","headers", azBool[p->showHeader!=0]);
  4291   4354       utf8_printf(p->out, "%12.12s: %s\n","mode", modeDescr[p->mode]);
  4292   4355       utf8_printf(p->out, "%12.12s: ", "nullvalue");
  4293   4356         output_c_string(p->out, p->nullValue);
  4294   4357         raw_printf(p->out, "\n");
  4295   4358       utf8_printf(p->out,"%12.12s: %s\n","output",
  4296   4359               strlen30(p->outfile) ? p->outfile : "stdout");
  4297   4360       utf8_printf(p->out,"%12.12s: ", "colseparator");
  4298   4361         output_c_string(p->out, p->colSeparator);
  4299   4362         raw_printf(p->out, "\n");
  4300   4363       utf8_printf(p->out,"%12.12s: ", "rowseparator");
  4301   4364         output_c_string(p->out, p->rowSeparator);
  4302   4365         raw_printf(p->out, "\n");
  4303         -    utf8_printf(p->out, "%12.12s: %s\n","stats", p->statsOn ? "on" : "off");
         4366  +    utf8_printf(p->out, "%12.12s: %s\n","stats", azBool[p->statsOn!=0]);
  4304   4367       utf8_printf(p->out, "%12.12s: ", "width");
  4305   4368       for (i=0;i<(int)ArraySize(p->colWidth) && p->colWidth[i] != 0;i++) {
  4306   4369         raw_printf(p->out, "%d ", p->colWidth[i]);
  4307   4370       }
  4308   4371       raw_printf(p->out, "\n");
  4309   4372     }else
  4310   4373   
................................................................................
  4711   4774           sqlite3_free(zVfsName);
  4712   4775         }
  4713   4776       }
  4714   4777     }else
  4715   4778   
  4716   4779   #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
  4717   4780     if( c=='w' && strncmp(azArg[0], "wheretrace", n)==0 ){
  4718         -    extern int sqlite3WhereTrace;
  4719   4781       sqlite3WhereTrace = nArg>=2 ? booleanValue(azArg[1]) : 0xff;
  4720   4782     }else
  4721   4783   #endif
  4722   4784   
  4723   4785     if( c=='w' && strncmp(azArg[0], "width", n)==0 ){
  4724   4786       int j;
  4725   4787       assert( nArg<=ArraySize(azArg) );
................................................................................
  5379   5441         data.showHeader = 1;
  5380   5442       }else if( strcmp(z,"-noheader")==0 ){
  5381   5443         data.showHeader = 0;
  5382   5444       }else if( strcmp(z,"-echo")==0 ){
  5383   5445         data.echoOn = 1;
  5384   5446       }else if( strcmp(z,"-eqp")==0 ){
  5385   5447         data.autoEQP = 1;
         5448  +    }else if( strcmp(z,"-eqpfull")==0 ){
         5449  +      data.autoEQP = 2;
  5386   5450       }else if( strcmp(z,"-stats")==0 ){
  5387   5451         data.statsOn = 1;
  5388   5452       }else if( strcmp(z,"-scanstats")==0 ){
  5389   5453         data.scanstatsOn = 1;
  5390   5454       }else if( strcmp(z,"-backslash")==0 ){
  5391   5455         /* Undocumented command-line option: -backslash
  5392   5456         ** Causes C-style backslash escapes to be evaluated in SQL statements