/ Check-in [d64b14e3]
Login

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

Overview
Comment:Improved name quoting and escaping in the auxiliary column info section of the ".schema" output for views and virtual tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:d64b14e37d9624bf5d86059ddd091170d8e6d341a8043f84548b9b3dbb96a908
User & Date: drh 2018-01-01 21:49:43
Context
2018-01-02
00:04
The ".schema" command in the command-line shell now shows the structure of table-valued functions and eponymous virtual tables if they are named on the ".schema" command line. Example: ".schema sql%" shows the structure of the "sqlite_dbstat" and "sqlite_stmt" virtual tables. check-in: f80f6651 user: drh tags: trunk
2018-01-01
21:49
Improved name quoting and escaping in the auxiliary column info section of the ".schema" output for views and virtual tables. check-in: d64b14e3 user: drh tags: trunk
21:28
In the output of ".schema", show the column names of virtual tables and views in a separate comment. check-in: 2234a87f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

   737    737   static char *shellFakeCrTab(
   738    738     sqlite3 *db,            /* The database connection containing the vtab */
   739    739     const char *zSchema,    /* Schema of the database holding the vtab */
   740    740     const char *zName       /* The name of the virtual table */
   741    741   ){
   742    742     sqlite3_stmt *pStmt = 0;
   743    743     char *zSql;
   744         -  char *z = 0;
          744  +  ShellText s;
          745  +  char cQuote;
          746  +  char *zDiv = "(";
   745    747   
   746         -  zSql = sqlite3_mprintf("SELECT group_concat(name,',')"
   747         -                         " FROM pragma_table_info(%Q,%Q);",
   748         -                         zName, zSchema);
          748  +  zSql = sqlite3_mprintf("PRAGMA \"%w\".table_info=%Q;",
          749  +                         zSchema ? zSchema : "main", zName);
   749    750     sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
   750    751     sqlite3_free(zSql);
   751         -  if( sqlite3_step(pStmt)==SQLITE_ROW ){
   752         -    z = sqlite3_mprintf("/* %s.%s(%s) */",
   753         -           zSchema ? zSchema : "main", zName, sqlite3_column_text(pStmt, 0));
          752  +  initText(&s);
          753  +  if( zSchema ){
          754  +    cQuote = quoteChar(zSchema);
          755  +    if( cQuote && sqlite3_stricmp(zSchema,"temp")==0 ) cQuote = 0;
          756  +    appendText(&s, zSchema, cQuote);
          757  +    appendText(&s, ".", 0);
   754    758     }
          759  +  cQuote = quoteChar(zName);
          760  +  appendText(&s, zName, cQuote);
          761  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          762  +    const char *zCol = (const char*)sqlite3_column_text(pStmt, 1);
          763  +    appendText(&s, zDiv, 0);
          764  +    zDiv = ",";
          765  +    cQuote = quoteChar(zCol);
          766  +    appendText(&s, zCol, cQuote);
          767  +  }
          768  +  appendText(&s, ")", 0);
          769  +
   755    770     sqlite3_finalize(pStmt);
   756         -  return z;
          771  +  return s.z;
   757    772   }
   758    773   
   759    774   /*
   760    775   ** SQL function:  shell_add_schema(S,X)
   761    776   **
   762    777   ** Add the schema name X to the CREATE statement in S and return the result.
   763    778   ** Examples:
................................................................................
   804    819             }else{
   805    820               z = sqlite3_mprintf("%.*s %s.%s", n+7, zIn, zSchema, zIn+n+8);
   806    821             }
   807    822           }
   808    823           if( aPrefix[i][0]=='V' ){
   809    824             const char *zName = (const char*)sqlite3_value_text(apVal[2]);
   810    825             if( z==0 ) z = sqlite3_mprintf("%s", zIn);
   811         -          z = sqlite3_mprintf("%z\n%z", z,  shellFakeCrTab(db, zSchema, zName));
          826  +          z = sqlite3_mprintf("%z\n/* %z */", z,
          827  +                              shellFakeCrTab(db, zSchema, zName));
   812    828           }
   813    829           if( z ){
   814    830             sqlite3_result_text(pCtx, z, -1, sqlite3_free);
   815    831             return;
   816    832           }
   817    833         }
   818    834       }

Changes to test/shell1.test.

   578    578        CREATE TABLE t1(x);
   579    579        CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
   580    580        CREATE VIEW v1 AS SELECT y+1 FROM v2;
   581    581     }
   582    582     catchcmd "test.db" ".schema"
   583    583   } {0 {CREATE TABLE t1(x);
   584    584   CREATE VIEW v2 AS SELECT x+1 AS y FROM t1
   585         -/* main.v2(y) */;
          585  +/* v2(y) */;
   586    586   CREATE VIEW v1 AS SELECT y+1 FROM v2
   587         -/* main.v1(y+1) */;}}
          587  +/* v1("y+1") */;}}
   588    588   db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
   589    589   }
   590    590   
   591    591   # .separator STRING  Change column separator used by output and .import
   592    592   do_test shell1-3.22.1 {
   593    593     catchcmd "test.db" ".separator"
   594    594   } {1 {Usage: .separator COL ?ROW?}}