/ Check-in [2234a87f]
Login

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

Overview
Comment:In the output of ".schema", show the column names of virtual tables and views in a separate comment.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2234a87fa905312b23f46d52e06cff7cacbf23b187e16c4398a42e6bdae0ee9f
User & Date: drh 2018-01-01 21:28:25
Context
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
20:11
Minor simplification to the ".schema" logic in the command-line shell. check-in: add45c47 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

   724    724         lwr = mid+1;
   725    725       }else{
   726    726         upr = mid-1;
   727    727       }
   728    728     }
   729    729     return 0;
   730    730   }
          731  +
          732  +/*
          733  +** Construct a fake CREATE TABLE statement (or at least the part that comes
          734  +** after the "CREATE TABLE" keywords) that describes the columns of
          735  +** the view, virtual table, or table valued function zName in zSchema.
          736  +*/
          737  +static char *shellFakeCrTab(
          738  +  sqlite3 *db,            /* The database connection containing the vtab */
          739  +  const char *zSchema,    /* Schema of the database holding the vtab */
          740  +  const char *zName       /* The name of the virtual table */
          741  +){
          742  +  sqlite3_stmt *pStmt = 0;
          743  +  char *zSql;
          744  +  char *z = 0;
          745  +
          746  +  zSql = sqlite3_mprintf("SELECT group_concat(name,',')"
          747  +                         " FROM pragma_table_info(%Q,%Q);",
          748  +                         zName, zSchema);
          749  +  sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
          750  +  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));
          754  +  }
          755  +  sqlite3_finalize(pStmt);
          756  +  return z;
          757  +}
   731    758   
   732    759   /*
   733    760   ** SQL function:  shell_add_schema(S,X)
   734    761   **
   735    762   ** Add the schema name X to the CREATE statement in S and return the result.
   736    763   ** Examples:
   737    764   **
................................................................................
   760    787        "VIEW",
   761    788        "TRIGGER",
   762    789        "VIRTUAL TABLE"
   763    790     };
   764    791     int i = 0;
   765    792     const char *zIn = (const char*)sqlite3_value_text(apVal[0]);
   766    793     const char *zSchema = (const char*)sqlite3_value_text(apVal[1]);
   767         -  assert( nVal==2 );
          794  +  sqlite3 *db = sqlite3_context_db_handle(pCtx);
   768    795     if( zIn!=0 && strncmp(zIn, "CREATE ", 7)==0 ){
   769    796       for(i=0; i<(int)(sizeof(aPrefix)/sizeof(aPrefix[0])); i++){
   770    797         int n = strlen30(aPrefix[i]);
   771    798         if( strncmp(zIn+7, aPrefix[i], n)==0 && zIn[n+7]==' ' ){
   772         -        char cQuote = quoteChar(zSchema);
   773         -        char *z;
   774         -        if( cQuote ){
   775         -         z = sqlite3_mprintf("%.*s \"%w\".%s", n+7, zIn, zSchema, zIn+n+8);
   776         -        }else{
   777         -          z = sqlite3_mprintf("%.*s %s.%s", n+7, zIn, zSchema, zIn+n+8);
          799  +        char *z = 0;
          800  +        if( zSchema ){
          801  +          char cQuote = quoteChar(zSchema);
          802  +          if( cQuote && sqlite3_stricmp(zSchema,"temp")!=0 ){
          803  +            z = sqlite3_mprintf("%.*s \"%w\".%s", n+7, zIn, zSchema, zIn+n+8);
          804  +          }else{
          805  +            z = sqlite3_mprintf("%.*s %s.%s", n+7, zIn, zSchema, zIn+n+8);
          806  +          }
   778    807           }
   779         -        sqlite3_result_text(pCtx, z, -1, sqlite3_free);
   780         -        return;
          808  +        if( aPrefix[i][0]=='V' ){
          809  +          const char *zName = (const char*)sqlite3_value_text(apVal[2]);
          810  +          if( z==0 ) z = sqlite3_mprintf("%s", zIn);
          811  +          z = sqlite3_mprintf("%z\n%z", z,  shellFakeCrTab(db, zSchema, zName));
          812  +        }
          813  +        if( z ){
          814  +          sqlite3_result_text(pCtx, z, -1, sqlite3_free);
          815  +          return;
          816  +        }
   781    817         }
   782    818       }
   783    819     }
   784    820     sqlite3_result_value(pCtx, apVal[0]);
   785    821   }
   786    822   
   787    823   /*
................................................................................
  2994   3030       }
  2995   3031   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  2996   3032       sqlite3_enable_load_extension(p->db, 1);
  2997   3033   #endif
  2998   3034       sqlite3_fileio_init(p->db, 0, 0);
  2999   3035       sqlite3_shathree_init(p->db, 0, 0);
  3000   3036       sqlite3_completion_init(p->db, 0, 0);
  3001         -    sqlite3_create_function(p->db, "shell_add_schema", 2, SQLITE_UTF8, 0,
         3037  +    sqlite3_create_function(p->db, "shell_add_schema", 3, SQLITE_UTF8, 0,
  3002   3038                               shellAddSchemaName, 0, 0);
  3003   3039     }
  3004   3040   }
  3005   3041   
  3006   3042   #if HAVE_READLINE || HAVE_EDITLINE
  3007   3043   /*
  3008   3044   ** Readline completion callbacks
................................................................................
  5315   5351     }else
  5316   5352   
  5317   5353     if( c=='s' && strncmp(azArg[0], "schema", n)==0 ){
  5318   5354       ShellText sSelect;
  5319   5355       ShellState data;
  5320   5356       char *zErrMsg = 0;
  5321   5357       const char *zDiv = 0;
         5358  +    const char *zName = 0;
  5322   5359       int iSchema = 0;
         5360  +    int bDebug = 0;
         5361  +    int ii;
  5323   5362   
  5324   5363       open_db(p, 0);
  5325   5364       memcpy(&data, p, sizeof(data));
  5326   5365       data.showHeader = 0;
  5327   5366       data.cMode = data.mode = MODE_Semi;
  5328   5367       initText(&sSelect);
  5329         -    if( nArg>=2 && optionMatch(azArg[1], "indent") ){
  5330         -      data.cMode = data.mode = MODE_Pretty;
  5331         -      nArg--;
  5332         -      if( nArg==2 ) azArg[1] = azArg[2];
         5368  +    for(ii=1; ii<nArg; ii++){
         5369  +      if( optionMatch(azArg[ii],"indent") ){
         5370  +        data.cMode = data.mode = MODE_Pretty;
         5371  +      }else if( optionMatch(azArg[ii],"debug") ){
         5372  +        bDebug = 1;
         5373  +      }else if( zName==0 ){
         5374  +        zName = azArg[ii];
         5375  +      }else{
         5376  +        raw_printf(stderr, "Usage: .schema ?--indent? ?LIKE-PATTERN?\n");
         5377  +        rc = 1;
         5378  +        goto meta_command_exit;
         5379  +      }
  5333   5380       }
  5334         -    if( nArg==2 && azArg[1][0]!='-' ){
  5335         -      int i;
  5336         -      for(i=0; azArg[1][i]; i++) azArg[1][i] = ToLower(azArg[1][i]);
  5337         -      if( strcmp(azArg[1],"sqlite_master")==0
  5338         -       || strcmp(azArg[1],"sqlite_temp_master")==0
         5381  +    if( zName!=0 ){
         5382  +      if( sqlite3_stricmp(zName,"sqlite_master")==0
         5383  +       || sqlite3_stricmp(zName,"sqlite_temp_master")==0
  5339   5384         ){
  5340   5385           char *new_argv[2], *new_colv[2];
  5341   5386           new_argv[0] = sqlite3_mprintf(
  5342   5387                         "CREATE TABLE %s (\n"
  5343   5388                         "  type text,\n"
  5344   5389                         "  name text,\n"
  5345   5390                         "  tbl_name text,\n"
  5346   5391                         "  rootpage integer,\n"
  5347   5392                         "  sql text\n"
  5348         -                      ")", azArg[1]);
         5393  +                      ")", zName);
  5349   5394           new_argv[1] = 0;
  5350   5395           new_colv[0] = "sql";
  5351   5396           new_colv[1] = 0;
  5352   5397           callback(&data, 1, new_argv, new_colv);
  5353   5398           sqlite3_free(new_argv[0]);
  5354   5399           rc = SQLITE_OK;
  5355   5400         }else{
  5356   5401           zDiv = "(";
  5357   5402         }
  5358         -    }else if( nArg==1 ){
         5403  +    }else if( zName==0 ){
  5359   5404         zDiv = "(";
  5360         -    }else{
  5361         -      raw_printf(stderr, "Usage: .schema ?--indent? ?LIKE-PATTERN?\n");
  5362         -      rc = 1;
  5363         -      goto meta_command_exit;
  5364   5405       }
  5365   5406       if( zDiv ){
  5366   5407         sqlite3_stmt *pStmt = 0;
  5367   5408         rc = sqlite3_prepare_v2(p->db, "SELECT name FROM pragma_database_list",
  5368   5409                                 -1, &pStmt, 0);
  5369   5410         if( rc ){
  5370   5411           utf8_printf(stderr, "Error: %s\n", sqlite3_errmsg(p->db));
................................................................................
  5376   5417         iSchema = 0;
  5377   5418         while( sqlite3_step(pStmt)==SQLITE_ROW ){
  5378   5419           const char *zDb = (const char*)sqlite3_column_text(pStmt, 0);
  5379   5420           char zScNum[30];
  5380   5421           sqlite3_snprintf(sizeof(zScNum), zScNum, "%d", ++iSchema);
  5381   5422           appendText(&sSelect, zDiv, 0);
  5382   5423           zDiv = " UNION ALL ";
  5383         -        if( strcmp(zDb, "main")!=0 ){
  5384         -          appendText(&sSelect, "SELECT shell_add_schema(sql,", 0);
         5424  +        appendText(&sSelect, "SELECT shell_add_schema(sql,", 0);
         5425  +        if( sqlite3_stricmp(zDb, "main")!=0 ){
  5385   5426             appendText(&sSelect, zDb, '"');
  5386         -          appendText(&sSelect, ") AS sql, type, tbl_name, name, rowid,", 0);
  5387         -          appendText(&sSelect, zScNum, 0);
  5388         -          appendText(&sSelect, " AS snum, ", 0);
  5389         -          appendText(&sSelect, zDb, '\'');
  5390         -          appendText(&sSelect, " AS sname FROM ", 0);
  5391         -          appendText(&sSelect, zDb, '"');
  5392         -          appendText(&sSelect, ".sqlite_master", 0);
  5393   5427           }else{
  5394         -          appendText(&sSelect, "SELECT sql, type, tbl_name, name, rowid, ", 0);
  5395         -          appendText(&sSelect, zScNum, 0);
  5396         -          appendText(&sSelect, " AS snum, 'main' AS sname FROM sqlite_master",0);
         5428  +          appendText(&sSelect, "NULL", 0);
  5397   5429           }
         5430  +        appendText(&sSelect, ",name) AS sql, type, tbl_name, name, rowid,", 0);
         5431  +        appendText(&sSelect, zScNum, 0);
         5432  +        appendText(&sSelect, " AS snum, ", 0);
         5433  +        appendText(&sSelect, zDb, '\'');
         5434  +        appendText(&sSelect, " AS sname FROM ", 0);
         5435  +        appendText(&sSelect, zDb, '"');
         5436  +        appendText(&sSelect, ".sqlite_master", 0);
  5398   5437         }
  5399   5438         sqlite3_finalize(pStmt);
  5400   5439         appendText(&sSelect, ") WHERE ", 0);
  5401         -      if( nArg>1 ){
  5402         -        char *zQarg = sqlite3_mprintf("%Q", azArg[1]);
  5403         -        if( strchr(azArg[1], '.') ){
         5440  +      if( zName ){
         5441  +        char *zQarg = sqlite3_mprintf("%Q", zName);
         5442  +        if( strchr(zName, '.') ){
  5404   5443             appendText(&sSelect, "lower(printf('%s.%s',sname,tbl_name))", 0);
  5405   5444           }else{
  5406   5445             appendText(&sSelect, "lower(tbl_name)", 0);
  5407   5446           }
  5408         -        appendText(&sSelect, strchr(azArg[1], '*') ? " GLOB " : " LIKE ", 0);
         5447  +        appendText(&sSelect, strchr(zName, '*') ? " GLOB " : " LIKE ", 0);
  5409   5448           appendText(&sSelect, zQarg, 0);
  5410   5449           appendText(&sSelect, " AND ", 0);
  5411   5450           sqlite3_free(zQarg);
  5412   5451         }
  5413   5452         appendText(&sSelect, "type!='meta' AND sql IS NOT NULL"
  5414   5453                              " ORDER BY snum, rowid", 0);
  5415         -      rc = sqlite3_exec(p->db, sSelect.z, callback, &data, &zErrMsg);
         5454  +      if( bDebug ){
         5455  +        utf8_printf(p->out, "SQL: %s;\n", sSelect.z);
         5456  +      }else{
         5457  +        rc = sqlite3_exec(p->db, sSelect.z, callback, &data, &zErrMsg);
         5458  +      }
  5416   5459         freeText(&sSelect);
  5417   5460       }
  5418   5461       if( zErrMsg ){
  5419   5462         utf8_printf(stderr,"Error: %s\n", zErrMsg);
  5420   5463         sqlite3_free(zErrMsg);
  5421   5464         rc = 1;
  5422   5465       }else if( rc != SQLITE_OK ){

Changes to test/pragma5.test.

    30     30   do_execsql_test 1.0 {
    31     31     PRAGMA table_info(pragma_function_list)
    32     32   } {
    33     33     0 name {} 0 {} 0 
    34     34     1 builtin {} 0 {} 0
    35     35   }
    36     36   do_execsql_test 1.1 {
    37         -  SELECT * FROM pragma_function_list WHERE name='upper'
           37  +  SELECT * FROM pragma_function_list WHERE name='upper' AND builtin
    38     38   } {upper 1}
    39     39   do_execsql_test 1.2 {
    40     40     SELECT * FROM pragma_function_list WHERE name LIKE 'exter%';
    41     41   } {external 0}
    42     42   
    43     43   ifcapable fts5 {
    44     44     do_execsql_test 2.0 {

Changes to test/shell1.test.

   577    577     catchcmd "test.db" {
   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         -CREATE VIEW v2 AS SELECT x+1 AS y FROM t1;
   585         -CREATE VIEW v1 AS SELECT y+1 FROM v2;}}
          584  +CREATE VIEW v2 AS SELECT x+1 AS y FROM t1
          585  +/* main.v2(y) */;
          586  +CREATE VIEW v1 AS SELECT y+1 FROM v2
          587  +/* main.v1(y+1) */;}}
   586    588   db eval {DROP VIEW v1; DROP VIEW v2; DROP TABLE t1;}
   587    589   }
   588    590   
   589    591   # .separator STRING  Change column separator used by output and .import
   590    592   do_test shell1-3.22.1 {
   591    593     catchcmd "test.db" ".separator"
   592    594   } {1 {Usage: .separator COL ?ROW?}}