/ Check-in [9fdc2496]
Login

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

Overview
Comment:Fix the ".dump" command in the shell. Ticket #2072. Also ticket #2065. (CVS 3515)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9fdc249609a4745715a2bf49bbf1376ea243a20a
User & Date: drh 2006-11-20 16:21:10
Context
2006-11-23
09:39
Add performance tests to the test suite. (CVS 3516) check-in: 270c745d user: drh tags: trunk
2006-11-20
16:21
Fix the ".dump" command in the shell. Ticket #2072. Also ticket #2065. (CVS 3515) check-in: 9fdc2496 user: drh tags: trunk
2006-11-18
20:20
Make sure VACUUM cleans up after itself. Ticket #2071. (CVS 3514) check-in: 2fdc147d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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.155 2006/11/08 12:25:43 drh Exp $
           15  +** $Id: shell.c,v 1.156 2006/11/20 16:21:10 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>
................................................................................
   221    221   
   222    222   struct previous_mode_data {
   223    223     int valid;        /* Is there legit data in here? */
   224    224     int mode;
   225    225     int showHeader;
   226    226     int colWidth[100];
   227    227   };
          228  +
   228    229   /*
   229    230   ** An pointer to an instance of this structure is passed from
   230    231   ** the main program to the callback.  This is used to communicate
   231    232   ** state and mode information.
   232    233   */
   233    234   struct callback_data {
   234    235     sqlite3 *db;            /* The database */
   235    236     int echoOn;            /* True to echo input commands */
   236    237     int cnt;               /* Number of records displayed so far */
   237    238     FILE *out;             /* Write results here */
   238    239     int mode;              /* An output mode setting */
          240  +  int writableSchema;    /* True if PRAGMA writable_schema=ON */
   239    241     int showHeader;        /* True to show column names in List or Column mode */
   240    242     char *zDestTable;      /* Name of destination table when MODE_Insert */
   241    243     char separator[20];    /* Separator character for MODE_List */
   242    244     int colWidth[100];     /* Requested width of each column when in column mode*/
   243    245     int actualWidth[100];  /* Actual width of each column */
   244    246     char nullvalue[20];    /* The text to print when a NULL comes back from
   245    247                            ** the database */
................................................................................
   671    673     return zIn;
   672    674   }
   673    675   
   674    676   
   675    677   /*
   676    678   ** Execute a query statement that has a single result column.  Print
   677    679   ** that result column on a line by itself with a semicolon terminator.
          680  +**
          681  +** This is used, for example, to show the schema of the database by
          682  +** querying the SQLITE_MASTER table.
   678    683   */
   679    684   static int run_table_dump_query(FILE *out, sqlite3 *db, const char *zSelect){
   680    685     sqlite3_stmt *pSelect;
   681    686     int rc;
   682    687     rc = sqlite3_prepare(db, zSelect, -1, &pSelect, 0);
   683    688     if( rc!=SQLITE_OK || !pSelect ){
   684    689       return rc;
................................................................................
   712    717     
   713    718     if( strcmp(zTable, "sqlite_sequence")==0 ){
   714    719       fprintf(p->out, "DELETE FROM sqlite_sequence;\n");
   715    720     }else if( strcmp(zTable, "sqlite_stat1")==0 ){
   716    721       fprintf(p->out, "ANALYZE sqlite_master;\n");
   717    722     }else if( strncmp(zTable, "sqlite_", 7)==0 ){
   718    723       return 0;
          724  +  }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
          725  +    char *zIns;
          726  +    if( !p->writableSchema ){
          727  +      fprintf(p->out, "PRAGMA writable_schema=ON;\n");
          728  +      p->writableSchema = 1;
          729  +    }
          730  +    zIns = sqlite3_mprintf(
          731  +       "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
          732  +       "VALUES('table','%q','%q',0,'%q');",
          733  +       zTable, zTable, zSql);
          734  +    fprintf(p->out, "%s\n", zIns);
          735  +    sqlite3_free(zIns);
          736  +    return 0;
   719    737     }else{
   720    738       fprintf(p->out, "%s;\n", zSql);
   721    739     }
   722    740   
   723    741     if( strcmp(zType, "table")==0 ){
   724    742       sqlite3_stmt *pTableInfo = 0;
   725    743       char *zSelect = 0;
................................................................................
   745    763       rc = sqlite3_step(pTableInfo);
   746    764       while( rc==SQLITE_ROW ){
   747    765         const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1);
   748    766         zSelect = appendText(zSelect, "quote(", 0);
   749    767         zSelect = appendText(zSelect, zText, '"');
   750    768         rc = sqlite3_step(pTableInfo);
   751    769         if( rc==SQLITE_ROW ){
   752         -        zSelect = appendText(zSelect, ") || ', ' || ", 0);
          770  +        zSelect = appendText(zSelect, ") || ',' || ", 0);
   753    771         }else{
   754    772           zSelect = appendText(zSelect, ") ", 0);
   755    773         }
   756    774       }
   757    775       rc = sqlite3_finalize(pTableInfo);
   758    776       if( rc!=SQLITE_OK ){
   759    777         if( zSelect ) free(zSelect);
................................................................................
   769    787       }
   770    788       if( zSelect ) free(zSelect);
   771    789     }
   772    790     return 0;
   773    791   }
   774    792   
   775    793   /*
   776         -** Run zQuery.  Update dump_callback() as the callback routine.
          794  +** Run zQuery.  Use dump_callback() as the callback routine so that
          795  +** the contents of the query are output as SQL statements.
          796  +**
   777    797   ** If we get a SQLITE_CORRUPT error, rerun the query after appending
   778    798   ** "ORDER BY rowid DESC" to the end.
   779    799   */
   780    800   static int run_schema_dump_query(
   781    801     struct callback_data *p, 
   782    802     const char *zQuery,
   783    803     char **pzErrMsg
................................................................................
   975    995       }
   976    996     }else
   977    997   
   978    998     if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
   979    999       char *zErrMsg = 0;
   980   1000       open_db(p);
   981   1001       fprintf(p->out, "BEGIN TRANSACTION;\n");
         1002  +    p->writableSchema = 0;
   982   1003       if( nArg==1 ){
   983   1004         run_schema_dump_query(p, 
   984   1005           "SELECT name, type, sql FROM sqlite_master "
   985         -        "WHERE sql NOT NULL AND type=='table' AND rootpage!=0", 0
   986         -      );
   987         -      run_schema_dump_query(p, 
   988         -        "SELECT name, type, sql FROM sqlite_master "
   989         -        "WHERE sql NOT NULL AND "
   990         -        "  AND type!='table' AND type!='meta'", 0
         1006  +        "WHERE sql NOT NULL AND type=='table'", 0
   991   1007         );
   992   1008         run_table_dump_query(p->out, p->db,
   993   1009           "SELECT sql FROM sqlite_master "
   994         -        "WHERE sql NOT NULL AND rootpage==0"
         1010  +        "WHERE sql NOT NULL AND type IN ('index','trigger','view')"
   995   1011         );
   996   1012       }else{
   997   1013         int i;
   998   1014         for(i=1; i<nArg; i++){
   999   1015           zShellStatic = azArg[i];
  1000   1016           run_schema_dump_query(p,
  1001   1017             "SELECT name, type, sql FROM sqlite_master "
  1002   1018             "WHERE tbl_name LIKE shellstatic() AND type=='table'"
  1003         -          "  AND rootpage!=0 AND sql NOT NULL", 0);
  1004         -        run_schema_dump_query(p,
  1005         -          "SELECT name, type, sql FROM sqlite_master "
  1006         -          "WHERE tbl_name LIKE shellstatic() AND type!='table'"
  1007         -          "  AND type!='meta' AND sql NOT NULL", 0);
         1019  +          "  AND sql NOT NULL", 0);
  1008   1020           run_table_dump_query(p->out, p->db,
  1009   1021             "SELECT sql FROM sqlite_master "
  1010         -          "WHERE sql NOT NULL AND rootpage==0"
         1022  +          "WHERE sql NOT NULL"
         1023  +          "  AND type IN ('index','trigger','view')"
  1011   1024             "  AND tbl_name LIKE shellstatic()"
  1012   1025           );
  1013   1026           zShellStatic = 0;
  1014   1027         }
         1028  +    }
         1029  +    if( p->writableSchema ){
         1030  +      fprintf(p->out, "PRAGMA writable_schema=OFF;\n");
         1031  +      p->writableSchema = 0;
  1015   1032       }
  1016   1033       if( zErrMsg ){
  1017   1034         fprintf(stderr,"Error: %s\n", zErrMsg);
  1018   1035         sqlite3_free(zErrMsg);
  1019   1036       }else{
  1020   1037         fprintf(p->out, "COMMIT;\n");
  1021   1038       }