You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Oystein Grovlen - Sun Norway <Oy...@Sun.COM> on 2006/10/17 15:14:30 UTC
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
Posted by Oystein Grovlen - Sun Norway <Oy...@Sun.COM>.
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
Posted by Andrew McIntyre <mc...@gmail.com>.
On 10/18/06, Øystein Grøvlen <Oy...@sun.com> 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
Re: Show commands are not orthogonal
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Ø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
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Andrew McIntyre wrote:
> On 10/17/06, Oystein Grovlen - Sun Norway <Oy...@sun.com> 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
Posted by Bryan Pendleton <bp...@amberpoint.com>.
>> 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
Posted by Rajesh Kartha <ka...@gmail.com>.
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
Re: Show commands are not orthogonal
Posted by Andrew McIntyre <mc...@gmail.com>.
On 10/17/06, Oystein Grovlen - Sun Norway <Oy...@sun.com> 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.
> 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.
andrew