Index: src/shell.c
==================================================================
--- src/shell.c
+++ src/shell.c
@@ -10,11 +10,11 @@
 **
 *************************************************************************
 ** This file contains code to implement the "sqlite" command line
 ** utility for accessing SQLite databases.
 **
-** $Id: shell.c,v 1.155 2006/11/08 12:25:43 drh Exp $
+** $Id: shell.c,v 1.156 2006/11/20 16:21:10 drh Exp $
 */
 #include <stdlib.h>
 #include <string.h>
 #include <stdio.h>
 #include <assert.h>
@@ -223,10 +223,11 @@
   int valid;        /* Is there legit data in here? */
   int mode;
   int showHeader;
   int colWidth[100];
 };
+
 /*
 ** An pointer to an instance of this structure is passed from
 ** the main program to the callback.  This is used to communicate
 ** state and mode information.
 */
@@ -234,10 +235,11 @@
   sqlite3 *db;            /* The database */
   int echoOn;            /* True to echo input commands */
   int cnt;               /* Number of records displayed so far */
   FILE *out;             /* Write results here */
   int mode;              /* An output mode setting */
+  int writableSchema;    /* True if PRAGMA writable_schema=ON */
   int showHeader;        /* True to show column names in List or Column mode */
   char *zDestTable;      /* Name of destination table when MODE_Insert */
   char separator[20];    /* Separator character for MODE_List */
   int colWidth[100];     /* Requested width of each column when in column mode*/
   int actualWidth[100];  /* Actual width of each column */
@@ -673,10 +675,13 @@
 
 
 /*
 ** Execute a query statement that has a single result column.  Print
 ** that result column on a line by itself with a semicolon terminator.
+**
+** This is used, for example, to show the schema of the database by
+** querying the SQLITE_MASTER table.
 */
 static int run_table_dump_query(FILE *out, sqlite3 *db, const char *zSelect){
   sqlite3_stmt *pSelect;
   int rc;
   rc = sqlite3_prepare(db, zSelect, -1, &pSelect, 0);
@@ -714,10 +719,23 @@
     fprintf(p->out, "DELETE FROM sqlite_sequence;\n");
   }else if( strcmp(zTable, "sqlite_stat1")==0 ){
     fprintf(p->out, "ANALYZE sqlite_master;\n");
   }else if( strncmp(zTable, "sqlite_", 7)==0 ){
     return 0;
+  }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
+    char *zIns;
+    if( !p->writableSchema ){
+      fprintf(p->out, "PRAGMA writable_schema=ON;\n");
+      p->writableSchema = 1;
+    }
+    zIns = sqlite3_mprintf(
+       "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
+       "VALUES('table','%q','%q',0,'%q');",
+       zTable, zTable, zSql);
+    fprintf(p->out, "%s\n", zIns);
+    sqlite3_free(zIns);
+    return 0;
   }else{
     fprintf(p->out, "%s;\n", zSql);
   }
 
   if( strcmp(zType, "table")==0 ){
@@ -747,11 +765,11 @@
       const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1);
       zSelect = appendText(zSelect, "quote(", 0);
       zSelect = appendText(zSelect, zText, '"');
       rc = sqlite3_step(pTableInfo);
       if( rc==SQLITE_ROW ){
-        zSelect = appendText(zSelect, ") || ', ' || ", 0);
+        zSelect = appendText(zSelect, ") || ',' || ", 0);
       }else{
         zSelect = appendText(zSelect, ") ", 0);
       }
     }
     rc = sqlite3_finalize(pTableInfo);
@@ -771,11 +789,13 @@
   }
   return 0;
 }
 
 /*
-** Run zQuery.  Update dump_callback() as the callback routine.
+** Run zQuery.  Use dump_callback() as the callback routine so that
+** the contents of the query are output as SQL statements.
+**
 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
 ** "ORDER BY rowid DESC" to the end.
 */
 static int run_schema_dump_query(
   struct callback_data *p, 
@@ -977,43 +997,40 @@
 
   if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
     char *zErrMsg = 0;
     open_db(p);
     fprintf(p->out, "BEGIN TRANSACTION;\n");
+    p->writableSchema = 0;
     if( nArg==1 ){
       run_schema_dump_query(p, 
         "SELECT name, type, sql FROM sqlite_master "
-        "WHERE sql NOT NULL AND type=='table' AND rootpage!=0", 0
-      );
-      run_schema_dump_query(p, 
-        "SELECT name, type, sql FROM sqlite_master "
-        "WHERE sql NOT NULL AND "
-        "  AND type!='table' AND type!='meta'", 0
+        "WHERE sql NOT NULL AND type=='table'", 0
       );
       run_table_dump_query(p->out, p->db,
         "SELECT sql FROM sqlite_master "
-        "WHERE sql NOT NULL AND rootpage==0"
+        "WHERE sql NOT NULL AND type IN ('index','trigger','view')"
       );
     }else{
       int i;
       for(i=1; i<nArg; i++){
         zShellStatic = azArg[i];
         run_schema_dump_query(p,
           "SELECT name, type, sql FROM sqlite_master "
           "WHERE tbl_name LIKE shellstatic() AND type=='table'"
-          "  AND rootpage!=0 AND sql NOT NULL", 0);
-        run_schema_dump_query(p,
-          "SELECT name, type, sql FROM sqlite_master "
-          "WHERE tbl_name LIKE shellstatic() AND type!='table'"
-          "  AND type!='meta' AND sql NOT NULL", 0);
+          "  AND sql NOT NULL", 0);
         run_table_dump_query(p->out, p->db,
           "SELECT sql FROM sqlite_master "
-          "WHERE sql NOT NULL AND rootpage==0"
+          "WHERE sql NOT NULL"
+          "  AND type IN ('index','trigger','view')"
           "  AND tbl_name LIKE shellstatic()"
         );
         zShellStatic = 0;
       }
+    }
+    if( p->writableSchema ){
+      fprintf(p->out, "PRAGMA writable_schema=OFF;\n");
+      p->writableSchema = 0;
     }
     if( zErrMsg ){
       fprintf(stderr,"Error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
     }else{