/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

737
738
739
740
741
742
743


744
745
746
747
748

749
750









751
752
753




754


755
756
757
758
759
760
761
762
763
...
804
805
806
807
808
809
810

811
812
813
814
815
816
817
818
static char *shellFakeCrTab(
  sqlite3 *db,            /* The database connection containing the vtab */
  const char *zSchema,    /* Schema of the database holding the vtab */
  const char *zName       /* The name of the virtual table */
){
  sqlite3_stmt *pStmt = 0;
  char *zSql;


  char *z = 0;

  zSql = sqlite3_mprintf("SELECT group_concat(name,',')"
                         " FROM pragma_table_info(%Q,%Q);",
                         zName, zSchema);

  sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  sqlite3_free(zSql);









  if( sqlite3_step(pStmt)==SQLITE_ROW ){
    z = sqlite3_mprintf("/* %s.%s(%s) */",
           zSchema ? zSchema : "main", zName, sqlite3_column_text(pStmt, 0));




  }


  sqlite3_finalize(pStmt);
  return z;
}

/*
** SQL function:  shell_add_schema(S,X)
**
** Add the schema name X to the CREATE statement in S and return the result.
** Examples:
................................................................................
          }else{
            z = sqlite3_mprintf("%.*s %s.%s", n+7, zIn, zSchema, zIn+n+8);
          }
        }
        if( aPrefix[i][0]=='V' ){
          const char *zName = (const char*)sqlite3_value_text(apVal[2]);
          if( z==0 ) z = sqlite3_mprintf("%s", zIn);

          z = sqlite3_mprintf("%z\n%z", z,  shellFakeCrTab(db, zSchema, zName));
        }
        if( z ){
          sqlite3_result_text(pCtx, z, -1, sqlite3_free);
          return;
        }
      }
    }







>
>
|

|
<
<
>


>
>
>
>
>
>
>
>
>
|
<
|
>
>
>
>

>
>

|







 







>
|







737
738
739
740
741
742
743
744
745
746
747
748


749
750
751
752
753
754
755
756
757
758
759
760
761

762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
...
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
static char *shellFakeCrTab(
  sqlite3 *db,            /* The database connection containing the vtab */
  const char *zSchema,    /* Schema of the database holding the vtab */
  const char *zName       /* The name of the virtual table */
){
  sqlite3_stmt *pStmt = 0;
  char *zSql;
  ShellText s;
  char cQuote;
  char *zDiv = "(";

  zSql = sqlite3_mprintf("PRAGMA \"%w\".table_info=%Q;",


                         zSchema ? zSchema : "main", zName);
  sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
  sqlite3_free(zSql);
  initText(&s);
  if( zSchema ){
    cQuote = quoteChar(zSchema);
    if( cQuote && sqlite3_stricmp(zSchema,"temp")==0 ) cQuote = 0;
    appendText(&s, zSchema, cQuote);
    appendText(&s, ".", 0);
  }
  cQuote = quoteChar(zName);
  appendText(&s, zName, cQuote);
  while( sqlite3_step(pStmt)==SQLITE_ROW ){

    const char *zCol = (const char*)sqlite3_column_text(pStmt, 1);
    appendText(&s, zDiv, 0);
    zDiv = ",";
    cQuote = quoteChar(zCol);
    appendText(&s, zCol, cQuote);
  }
  appendText(&s, ")", 0);

  sqlite3_finalize(pStmt);
  return s.z;
}

/*
** SQL function:  shell_add_schema(S,X)
**
** Add the schema name X to the CREATE statement in S and return the result.
** Examples:
................................................................................
          }else{
            z = sqlite3_mprintf("%.*s %s.%s", n+7, zIn, zSchema, zIn+n+8);
          }
        }
        if( aPrefix[i][0]=='V' ){
          const char *zName = (const char*)sqlite3_value_text(apVal[2]);
          if( z==0 ) z = sqlite3_mprintf("%s", zIn);
          z = sqlite3_mprintf("%z\n/* %z */", z,
                              shellFakeCrTab(db, zSchema, zName));
        }
        if( z ){
          sqlite3_result_text(pCtx, z, -1, sqlite3_free);
          return;
        }
      }
    }

Changes to test/shell1.test.

578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
     CREATE TABLE t1(x);
     CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
     CREATE VIEW v1 AS SELECT y+1 FROM v2;
  }
  catchcmd "test.db" ".schema"
} {0 {CREATE TABLE t1(x);
CREATE VIEW v2 AS SELECT x+1 AS y FROM t1
/* main.v2(y) */;
CREATE VIEW v1 AS SELECT y+1 FROM v2
/* main.v1(y+1) */;}}
db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
}

# .separator STRING  Change column separator used by output and .import
do_test shell1-3.22.1 {
  catchcmd "test.db" ".separator"
} {1 {Usage: .separator COL ?ROW?}}







|

|







578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
     CREATE TABLE t1(x);
     CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
     CREATE VIEW v1 AS SELECT y+1 FROM v2;
  }
  catchcmd "test.db" ".schema"
} {0 {CREATE TABLE t1(x);
CREATE VIEW v2 AS SELECT x+1 AS y FROM t1
/* v2(y) */;
CREATE VIEW v1 AS SELECT y+1 FROM v2
/* v1("y+1") */;}}
db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
}

# .separator STRING  Change column separator used by output and .import
do_test shell1-3.22.1 {
  catchcmd "test.db" ".separator"
} {1 {Usage: .separator COL ?ROW?}}