/ Check-in [26565b89]
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:The shell does not output the sqlite_stat1 table on .dump or .schema. The ANALYZE command now gathers statistics on tables that have only a single index because this sometimes helps when reordering tables in a join. (CVS 2686)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 26565b8931419031f9a8dd3947e1e2bd23ccbff2
User & Date: drh 2005-09-10 22:40:54
Context
2005-09-11
02:03
The ".dump" command from the shell correctly saves the state of the sqlite_sequence and sqlite_stat1 tables, if they exist. Ticket #1419. (CVS 2687) check-in: 3f191cf4 user: drh tags: trunk
2005-09-10
22:40
The shell does not output the sqlite_stat1 table on .dump or .schema. The ANALYZE command now gathers statistics on tables that have only a single index because this sometimes helps when reordering tables in a join. (CVS 2686) check-in: 26565b89 user: drh tags: trunk
16:46
Add the experimental EXPLAIN QUERY PLAN diagnostic capability. (CVS 2685) check-in: 986efb7b user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code associated with the ANALYZE command.
    13     13   **
    14         -** @(#) $Id: analyze.c,v 1.7 2005/09/06 10:26:47 drh Exp $
           14  +** @(#) $Id: analyze.c,v 1.8 2005/09/10 22:40:54 drh Exp $
    15     15   */
    16     16   #ifndef SQLITE_OMIT_ANALYZE
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** This routine generates code that opens the sqlite_stat1 table on cursor
    21     21   ** iStatCur.
................................................................................
    84     84     Vdbe *v;         /* The virtual machine being built up */
    85     85     int i;           /* Loop counter */
    86     86     int topOfLoop;   /* The top of the loop */
    87     87     int endOfLoop;   /* The end of the loop */
    88     88     int addr;        /* The address of an instruction */
    89     89   
    90     90     v = sqlite3GetVdbe(pParse);
    91         -  if( pTab==0 || pTab->pIndex==0 || pTab->pIndex->pNext==0 ){
    92         -    /* Do no analysis for tables with fewer than 2 indices */
           91  +  if( pTab==0 || pTab->pIndex==0 ){
           92  +    /* Do no analysis for tables that have no indices */
    93     93       return;
    94     94     }
    95     95   
    96     96   #ifndef SQLITE_OMIT_AUTHORIZATION
    97     97     if( sqlite3AuthCheck(pParse, SQLITE_ANALYZE, pTab->zName, 0,
    98     98         pParse->db->aDb[pTab->iDb].zName ) ){
    99     99       return;

Changes to src/shell.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code to implement the "sqlite" command line
    13     13   ** utility for accessing SQLite databases.
    14     14   **
    15         -** $Id: shell.c,v 1.126 2005/08/30 20:12:02 drh Exp $
           15  +** $Id: shell.c,v 1.127 2005/09/10 22:40:54 drh Exp $
    16     16   */
    17     17   #include <stdlib.h>
    18     18   #include <string.h>
    19     19   #include <stdio.h>
    20     20   #include <assert.h>
    21     21   #include "sqlite3.h"
    22     22   #include <ctype.h>
................................................................................
   901    901     if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
   902    902       char *zErrMsg = 0;
   903    903       open_db(p);
   904    904       fprintf(p->out, "BEGIN TRANSACTION;\n");
   905    905       if( nArg==1 ){
   906    906         run_schema_dump_query(p, 
   907    907           "SELECT name, type, sql FROM sqlite_master "
   908         -        "WHERE sql NOT NULL AND type=='table'", 0
          908  +        "WHERE sql NOT NULL AND type=='table' AND name NOT LIKE 'sqlite_%'", 0
   909    909         );
   910    910         run_schema_dump_query(p, 
   911    911           "SELECT name, type, sql FROM sqlite_master "
   912         -        "WHERE sql NOT NULL AND type!='table' AND type!='meta'", 0
          912  +        "WHERE sql NOT NULL AND type!='table' AND type!='meta' "
          913  +        "AND name NOT LIKE 'sqlite_%'", 0
   913    914         );
   914    915       }else{
   915    916         int i;
   916    917         for(i=1; i<nArg; i++){
   917    918           zShellStatic = azArg[i];
   918    919           run_schema_dump_query(p,
   919    920             "SELECT name, type, sql FROM sqlite_master "
................................................................................
  1286   1287           zShellStatic = 0;
  1287   1288         }
  1288   1289       }else{
  1289   1290         sqlite3_exec(p->db,
  1290   1291            "SELECT sql FROM "
  1291   1292            "  (SELECT * FROM sqlite_master UNION ALL"
  1292   1293            "   SELECT * FROM sqlite_temp_master) "
  1293         -         "WHERE type!='meta' AND sql NOTNULL "
         1294  +         "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'"
  1294   1295            "ORDER BY substr(type,2,1), name",
  1295   1296            callback, &data, &zErrMsg
  1296   1297         );
  1297   1298       }
  1298   1299       if( zErrMsg ){
  1299   1300         fprintf(stderr,"Error: %s\n", zErrMsg);
  1300   1301         sqlite3_free(zErrMsg);
................................................................................
  1330   1331       char **azResult;
  1331   1332       int nRow, rc;
  1332   1333       char *zErrMsg;
  1333   1334       open_db(p);
  1334   1335       if( nArg==1 ){
  1335   1336         rc = sqlite3_get_table(p->db,
  1336   1337           "SELECT name FROM sqlite_master "
  1337         -        "WHERE type IN ('table','view') "
         1338  +        "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'"
  1338   1339           "UNION ALL "
  1339   1340           "SELECT name FROM sqlite_temp_master "
  1340   1341           "WHERE type IN ('table','view') "
  1341   1342           "ORDER BY 1",
  1342   1343           &azResult, &nRow, 0, &zErrMsg
  1343   1344         );
  1344   1345       }else{

Changes to test/analyze.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   # This file implements tests for the ANALYZE command.
    13     13   #
    14         -# $Id: analyze.test,v 1.4 2005/09/06 10:26:47 drh Exp $
           14  +# $Id: analyze.test,v 1.5 2005/09/10 22:40:54 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # There is nothing to test if ANALYZE is disable for this build.
    20     20   #
    21     21   ifcapable {!analyze} {
................................................................................
   174    174   } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
   175    175   do_test analyze-3.7 {
   176    176     execsql {
   177    177       DROP INDEX t2i2;
   178    178       ANALYZE t2;
   179    179       SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
   180    180     }
   181         -} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}}
          181  +} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3}}
   182    182   do_test analyze-3.8 {
   183    183     execsql {
   184    184       CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;
   185    185       CREATE INDEX t3i1 ON t3(a);
   186    186       CREATE INDEX t3i2 ON t3(a,b,c,d);
   187    187       CREATE INDEX t3i3 ON t3(d,b,c,a);
   188    188       DROP TABLE t1;