SQLite

Check-in [d64b14e37d]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d64b14e37d9624bf5d86059ddd091170d8e6d341a8043f84548b9b3dbb96a908
User & Date: drh 2018-01-01 21:49:43.315
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: f80f6651df 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: d64b14e37d 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: 2234a87fa9 user: drh tags: trunk)
Changes
Unified Diff Show Whitespace Changes 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

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:








>
>
|

|
<
|


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

>
>

|








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

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:
︙︙
804
805
806
807
808
809
810
811

812
813
814
815
816
817
818

          }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;
        }
      }
    }








|
>








819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834

          }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?}}
︙︙