Re: Show commands are not orthogonal
Andrew McIntyre wrote: ... But, that decision in the code means if you 'set schema sys' and then 'show tables' you get no output. That's bad behavior, in my opinion. I agree. ... I've attached a patch which changes the behavior to adjust the behavior of show tables to what you describe, and which also fixes the problem I just found described above. Try it out, and if you're satisfied with the behavior, I'll open a JIRA for tracking for release purposes and then commit to trunk and 10.2. The code changes looks good. One question comes to mind when reading the javadoc for DataBaseMetaData.getTables(): Should we also list temporary tables? -- Øystein
Re: Show commands are not orthogonal
Andrew McIntyre wrote: On 10/17/06, Oystein Grovlen - Sun Norway [EMAIL PROTECTED] wrote: I find it strange that while SHOW INDEXES, SHOW PROCEDURES, and SHOW SYNONYMS by default list all corresponding objects in the database, SHOW TABLES and SHOW VIEWS by default only list the corresponding objects in the current schema. Would it not be better if all commands behaved the same way? Are there reasons for not changing this? Having looked at the ij code, but not having gone to look at the corresponding metadata code or the JDBC spec, I can say that this is the current behavior when calling DatabaseMetadata.getTables() with null as the argument for the schema parameter and TABLE as the table type. I don't see a reason why it couldn't be fixed, although the fix may be to the metadata method, and not necessarily to ij. I suspect that if the schema is null, Derby uses the current connection schema to restrict the set of tables that is returned. Seems like I was fooled by the documentation here. When I actually try it out, it works the way I would expect it to work, except that it does not display system tables: ij show tables; TABLE_SCHEM |TABLE_NAME|REMARKS APP |T | 1 row selected ij create schema user1; 0 rows inserted/updated/deleted ij set schema user1; 0 rows inserted/updated/deleted ij create table u (j integer, i integer); 0 rows inserted/updated/deleted ij show tables; TABLE_SCHEM |TABLE_NAME|REMARKS APP |T | USER1 |U | 2 rows selected I will file a bug report for the documentation tomorrow. The documentation should proabably also say that system tables are only displayed if a schema is specified. (I guess this is what fooled the documentor into thinking that only tables for the current schema is displayed. This is not a feature of the DatabaseMetaData implementation, but implemented by ij). -- Øystein
Re: Show commands are not orthogonal
Øystein Grøvlen wrote: I will file a bug report for the documentation tomorrow. The documentation should proabably also say that system tables are only displayed if a schema is specified. Thinking about this for another few minutes, this still means that the show commands are not orthogonal since by default system procedures and indexes on system tables are displayed, but not system tables. So I'll ask again: What do people think about this. Should we fix this, and, if yes, in what way? -- Øystein
Re: Show commands are not orthogonal
On 10/18/06, Øystein Grøvlen [EMAIL PROTECTED] wrote: Øystein Grøvlen wrote: I will file a bug report for the documentation tomorrow. The documentation should proabably also say that system tables are only displayed if a schema is specified. Thinking about this for another few minutes, this still means that the show commands are not orthogonal since by default system procedures and indexes on system tables are displayed, but not system tables. So I'll ask again: What do people think about this. So, I went and looked at the code after this came up yesterday and I believe that the original implementor may have thought it confusing to issue show tables and get a list of all the system tables that weren't in the current schema, especially if upon first connecting you don't know what the current schema is. But, because the table type for the system tables is SYSTEM TABLE, if one issued the command 'show tables in sys' you would get nothing if you just ask for the table type of TABLES. This led the original implementor to only request SYSTEM TABLES if a schema was provided. But, that decision in the code means if you 'set schema sys' and then 'show tables' you get no output. That's bad behavior, in my opinion. Should we fix this, and, if yes, in what way? I've attached a patch which changes the behavior to adjust the behavior of show tables to what you describe, and which also fixes the problem I just found described above. Try it out, and if you're satisfied with the behavior, I'll open a JIRA for tracking for release purposes and then commit to trunk and 10.2. andrew ij_showalltables.diff Description: Binary data
Show commands are not orthogonal
I find it strange that while SHOW INDEXES, SHOW PROCEDURES, and SHOW SYNONYMS by default list all corresponding objects in the database, SHOW TABLES and SHOW VIEWS by default only list the corresponding objects in the current schema. Would it not be better if all commands behaved the same way? Are there reasons for not changing this? I also find it a bit strange that SHOW VIEWS uses the heading TABLE_NAME and not VIEW_NAME. -- Øystein
Re: Show commands are not orthogonal
I also find it a bit strange that SHOW VIEWS uses the heading TABLE_NAME and not VIEW_NAME. In a way, this is similar to DERBY-1072: http://issues.apache.org/jira/browse/DERBY-1072 bryan
Re: Show commands are not orthogonal
Andrew McIntyre wrote: I also find it a bit strange that SHOW VIEWS uses the heading TABLE_NAME and not VIEW_NAME. These are the names of the columns returned from the ResultSet retrieved from DatabaseMetadata.getTables(), and I believe those column names are mandated by the JDBC spec. And, ij displays them faithfully. :-) We could override the column names in a similar way that we set the column widths but I don't think that is really worthwhile. Probably better to just document that little idiosyncrasy. I have seen many database that show tables and views together in the 'show tables' / 'list tables' command. So maybe we can do something similar and make the ij o/p to show something like: create table atab (i int, j int, k int); create view v_atab as select i,j from atab; TABLE_SCHEM |TABLE_NAME|TYPE (?) APP |ATAB |Table APP |V_ATAB |View My 0.02 -Rajesh
