Обсуждение: DbVisualizer shows same tables for all DBs
I know Postgresql doesn't allow changing DBs with the same Connection, but it should let tools know about this limitation via DatabaseMetadata. I'm not particularly familiar with the JDBC spec, so I don't know if the driver doesn't implement the spec correctly or if the spec doesn't cover this sutiation. The fact is, when I connect to a Postgresql DB using DbVisualizer Free 3.1 (www.minq.se) and latest JDBC driver (build 106), I specify which DB I want to connect to in the connection URL (ie "jdbc:postgresql://localhost:5432/testdb"), and 3 DBs show up: testdb, template0 and template1. The weird thing is that template0 and template1 seem to be exact copies of testdb. AFAIK, what's really happening is that the driver can't "see" other DBs besides the one I connected to (testdb), so it shows all DBs as if they were the same. IMHO, this behaviour is quite confusing. -- Daniel Serodio <daniel@checkforte.com.br> CheckForte
On 28 Jan 2003, Daniel Serodio wrote: > I know Postgresql doesn't allow changing DBs with the same Connection, > but it should let tools know about this limitation via DatabaseMetadata. > I'm not particularly familiar with the JDBC spec, so I don't know if the > driver doesn't implement the spec correctly or if the spec doesn't cover > this sutiation. > > The fact is, when I connect to a Postgresql DB using DbVisualizer Free > 3.1 (www.minq.se) and latest JDBC driver (build 106), I specify which DB > I want to connect to in the connection URL (ie > "jdbc:postgresql://localhost:5432/testdb"), and 3 DBs show up: testdb, > template0 and template1. The weird thing is that template0 and template1 > seem to be exact copies of testdb. AFAIK, what's really happening is > that the driver can't "see" other DBs besides the one I connected to > (testdb), so it shows all DBs as if they were the same. IMHO, this > behaviour is quite confusing. The DatabaseMetaData.getCatalogs() method is called by DbVisualizer to get the list of available databases. When trying to display the tables in the database it passes the desired catalog name into the getTables method, but it is ignored. What could be done: 1) Make getCatalogs() only return the current catalog. 2) Make other DatabaseMetaData methods throw an SQLException if a catalog other than Connection.getCatalog() is used. The getCatalogs() method's javadoc says "Gets the catalog names available in this database." Does that mean are available to this connection or to the current user on another connection or another user on another connection? Kris Jurka
On Tue, 2003-01-28 at 20:11, Kris Jurka wrote: > On 28 Jan 2003, Daniel Serodio wrote: > > > I know Postgresql doesn't allow changing DBs with the same Connection, > > but it should let tools know about this limitation via DatabaseMetadata. > > I'm not particularly familiar with the JDBC spec, so I don't know if the > > driver doesn't implement the spec correctly or if the spec doesn't cover > > this sutiation. > > > > The fact is, when I connect to a Postgresql DB using DbVisualizer Free > > 3.1 (www.minq.se) and latest JDBC driver (build 106), I specify which DB > > I want to connect to in the connection URL (ie > > "jdbc:postgresql://localhost:5432/testdb"), and 3 DBs show up: testdb, > > template0 and template1. The weird thing is that template0 and template1 > > seem to be exact copies of testdb. AFAIK, what's really happening is > > that the driver can't "see" other DBs besides the one I connected to > > (testdb), so it shows all DBs as if they were the same. IMHO, this > > behaviour is quite confusing. > > The DatabaseMetaData.getCatalogs() method is called by DbVisualizer to get > the list of available databases. When trying to display the tables in the > database it passes the desired catalog name into the getTables method, but > it is ignored. > > What could be done: > > 1) Make getCatalogs() only return the current catalog. > > 2) Make other DatabaseMetaData methods throw an SQLException if a catalog > other than Connection.getCatalog() is used. I think the best thing to do would be to make getTables() only return tables from the current catalog, if the catalog param is null, or throw SQLException if the catalog param is different from the current catalog. What do you think? > The getCatalogs() method's javadoc says "Gets the catalog names available > in this database." Does that mean are available to this connection or to > the current user on another connection or another user on another > connection? > > Kris Jurka Yeah, that's not very specific... What does "this database" mean? BTW, what is the "conceptual" difference between a database, a catalog and a schema? Thanks a lot, Daniel Serodio -- Daniel Serodio <daniel@checkforte.com.br> CheckForte
Here is my take on what should be done: Kris Jurka wrote: > > On 28 Jan 2003, Daniel Serodio wrote: > > >>I know Postgresql doesn't allow changing DBs with the same Connection, >>but it should let tools know about this limitation via DatabaseMetadata. >>I'm not particularly familiar with the JDBC spec, so I don't know if the >>driver doesn't implement the spec correctly or if the spec doesn't cover >>this sutiation. >> >>The fact is, when I connect to a Postgresql DB using DbVisualizer Free >>3.1 (www.minq.se) and latest JDBC driver (build 106), I specify which DB >>I want to connect to in the connection URL (ie >>"jdbc:postgresql://localhost:5432/testdb"), and 3 DBs show up: testdb, >>template0 and template1. The weird thing is that template0 and template1 >>seem to be exact copies of testdb. AFAIK, what's really happening is >>that the driver can't "see" other DBs besides the one I connected to >>(testdb), so it shows all DBs as if they were the same. IMHO, this >>behaviour is quite confusing. > > > The DatabaseMetaData.getCatalogs() method is called by DbVisualizer to get > the list of available databases. When trying to display the tables in the > database it passes the desired catalog name into the getTables method, but > it is ignored. > > What could be done: > > 1) Make getCatalogs() only return the current catalog. I think the current functionality is fine, however I could be convinced otherwise. > > 2) Make other DatabaseMetaData methods throw an SQLException if a catalog > other than Connection.getCatalog() is used. I don't think an exception is necessary, as clients will not be expecting to see an exception. For interoperability across different clients, I think the best that can be done here is to return nothing. So the way things work today, if you are using a client app to browse the objects you would see: database1 - db1table1 - db1table2 database2 - db1table1 - db1table2 which is clearly wrong. So I am suggesting that we return the following information: database1 - db1table1 - db1table2 database2 <empty> or we could: database1 - db1table1 - db1table2 <only one db shown> > > The getCatalogs() method's javadoc says "Gets the catalog names available > in this database." Does that mean are available to this connection or to > the current user on another connection or another user on another > connection? > I don't know. > Kris Jurka > > Does anyone have any opinions on which way to go? thanks, --Barry
See comments below. On Tue, 2003-02-04 at 23:35, Barry Lind wrote: > Here is my take on what should be done: > > Kris Jurka wrote: > > > > On 28 Jan 2003, Daniel Serodio wrote: > > > > > >>I know Postgresql doesn't allow changing DBs with the same Connection, > >>but it should let tools know about this limitation via DatabaseMetadata. > >>I'm not particularly familiar with the JDBC spec, so I don't know if the > >>driver doesn't implement the spec correctly or if the spec doesn't cover > >>this sutiation. > >> > >>The fact is, when I connect to a Postgresql DB using DbVisualizer Free > >>3.1 (www.minq.se) and latest JDBC driver (build 106), I specify which DB > >>I want to connect to in the connection URL (ie > >>"jdbc:postgresql://localhost:5432/testdb"), and 3 DBs show up: testdb, > >>template0 and template1. The weird thing is that template0 and template1 > >>seem to be exact copies of testdb. AFAIK, what's really happening is > >>that the driver can't "see" other DBs besides the one I connected to > >>(testdb), so it shows all DBs as if they were the same. IMHO, this > >>behaviour is quite confusing. > > > > > > The DatabaseMetaData.getCatalogs() method is called by DbVisualizer to get > > the list of available databases. When trying to display the tables in the > > database it passes the desired catalog name into the getTables method, but > > it is ignored. > > > > What could be done: > > > > 1) Make getCatalogs() only return the current catalog. > > I think the current functionality is fine, however I could be convinced > otherwise. > > > > > 2) Make other DatabaseMetaData methods throw an SQLException if a catalog > > other than Connection.getCatalog() is used. > > I don't think an exception is necessary, as clients will not be > expecting to see an exception. For interoperability across different > clients, I think the best that can be done here is to return nothing. Agreed. > So the way things work today, if you are using a client app to browse > the objects you would see: > > database1 > - db1table1 > - db1table2 > database2 > - db1table1 > - db1table2 Exactly. > which is clearly wrong. So I am suggesting that we return the following > information: > > database1 > - db1table1 > - db1table2 > database2 > <empty> I think this is the best option. > or we could: > > database1 > - db1table1 > - db1table2 > <only one db shown> > > > > > The getCatalogs() method's javadoc says "Gets the catalog names available > > in this database." Does that mean are available to this connection or to > > the current user on another connection or another user on another > > connection? > > > > I don't know. > > > Kris Jurka > Does anyone have any opinions on which way to go? > > thanks, > --Barry -- Daniel Serodio <daniel@checkforte.com.br> CheckForte