Re: [JDBC] Connection.setCatalog()
Patch applied. Thanks. > [[[ Original Message from Tom Lane <[EMAIL PROTECTED]> ]]] > > > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > > Tom Lane writes: > > >> Peter E. has previously commented that Postgres databases correspond > > >> most closely to the SQL concept of "catalog cluster", not "catalog". > > > > > I most certainly did not. According to my interpretation: > > > > I sit corrected. If you want to define catalog == database, okay with > > me. > > > > regards, tom lane > > Great, here is a context diff of CVS for implementing the get/setCatalog methods in >Connection - note: I've updated setCatalog(String catalog) from my previous diff so >it checks whether it is already connected to the specified catalog. > > Thanks, > > Jason Davies > > [EMAIL PROTECTED] [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [JDBC] Connection.setCatalog()
OK, seems like this is the final one to be applied. Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. > [[[ Original Message from Tom Lane <[EMAIL PROTECTED]> ]]] > > > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > > Tom Lane writes: > > >> Peter E. has previously commented that Postgres databases correspond > > >> most closely to the SQL concept of "catalog cluster", not "catalog". > > > > > I most certainly did not. According to my interpretation: > > > > I sit corrected. If you want to define catalog == database, okay with > > me. > > > > regards, tom lane > > Great, here is a context diff of CVS for implementing the get/setCatalog methods in >Connection - note: I've updated setCatalog(String catalog) from my previous diff so >it checks whether it is already connected to the specified catalog. > > Thanks, > > Jason Davies > > [EMAIL PROTECTED] [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Connection.setCatalog()
[[[ Original Message from Tom Lane <[EMAIL PROTECTED]> ]]] > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Tom Lane writes: > >> Peter E. has previously commented that Postgres databases correspond > >> most closely to the SQL concept of "catalog cluster", not "catalog". > > > I most certainly did not. According to my interpretation: > > I sit corrected. If you want to define catalog == database, okay with > me. > > regards, tom lane Great, here is a context diff of CVS for implementing the get/setCatalog methods in Connection - note: I've updated setCatalog(String catalog) from my previous diff so it checks whether it is already connected to the specified catalog. Thanks, Jason Davies [EMAIL PROTECTED] Connection.diff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Connection.setCatalog()
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane writes: >> Peter E. has previously commented that Postgres databases correspond >> most closely to the SQL concept of "catalog cluster", not "catalog". > I most certainly did not. According to my interpretation: I sit corrected. If you want to define catalog == database, okay with me. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [JDBC] Connection.setCatalog()
Tom Lane writes: > Peter E. has previously commented that Postgres databases correspond > most closely to the SQL concept of "catalog cluster", not "catalog". I most certainly did not. According to my interpretation: schema = schema catalog = database cluster = thing you get from initdb This is also how we currently document it and it tends to be the practice in other products as well. > This agrees with my reading of SQL92 4.13: > > A cluster is an implementation-defined collection of catalogs. > Exactly one cluster is associated with an SQL-session and it > defines the totality of the SQL-data that is available to that > SQL-session. Yes, the stuff served by a single postmaster is the totality of the SQL data available to that SQL session. But note: The method of creation and destruction of catalogs is implementation-defined. The set of catalogs that can be referenced in any SQL-statement, during any particular SQL-transaction, or during the course of an SQL-session is also implementation-defined. (just above your stuff) which serves us just fine. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] Connection.setCatalog()
[[[ Original Message from Tom Lane <[EMAIL PROTECTED]> ]]] > [EMAIL PROTECTED] writes: > > Do you know whether PostgreSQL will support the catalogname.tablename > > syntax in SQL queries in the future? > > Schema support is on the to-do list. It's probably too late to imagine > that it will get done for 7.2, but maybe for 7.3. > > Although the details haven't been argued out yet, I suspect that the > existing concept of independent databases within an installation will > remain in place (for backwards compatibility if nothing else). > SQL-style schemas and catalogs will exist as new naming levels > *within* what we now call a database. I see - I didn't realise this. As this is the case, there is no need for my database tool to list available databases since databases in postgresql are clusters. Thanks for clarifying that. > Peter E. has previously commented that Postgres databases correspond > most closely to the SQL concept of "catalog cluster", not "catalog". > This agrees with my reading of SQL92 4.13: > > A cluster is an implementation-defined collection of catalogs. > Exactly one cluster is associated with an SQL-session and it > defines the totality of the SQL-data that is available to that > SQL-session. > > Schemas and catalogs will exist within a database, and there will > still be no possibility of cross-database accesses (but, hopefully, > much less need for it either). Good. As long as everything conforms to some kind of standard we're okay :) > While I haven't followed this discussion closely, it appears to me that > you are trying to make setCatalog reconnect to a new database --- ie, a > new cluster in SQL terminology. I think this is a bad idea, as it will > create a backwards compatibility problem once we have actual catalogs > for setCatalog to work with. I'd suggest that the right short-term > thing is for setCatalog to either do nothing or throw an error. I agree. In which case the update Peter E did to DatabaseMetaData.getCatalogs() should be backed out. At least I've learnt how to use CVS and how to make context diffs :) > > regards, tom lane Jason Davies [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] Connection.setCatalog()
[EMAIL PROTECTED] writes: > Do you know whether PostgreSQL will support the catalogname.tablename > syntax in SQL queries in the future? Schema support is on the to-do list. It's probably too late to imagine that it will get done for 7.2, but maybe for 7.3. Although the details haven't been argued out yet, I suspect that the existing concept of independent databases within an installation will remain in place (for backwards compatibility if nothing else). SQL-style schemas and catalogs will exist as new naming levels *within* what we now call a database. Peter E. has previously commented that Postgres databases correspond most closely to the SQL concept of "catalog cluster", not "catalog". This agrees with my reading of SQL92 4.13: A cluster is an implementation-defined collection of catalogs. Exactly one cluster is associated with an SQL-session and it defines the totality of the SQL-data that is available to that SQL-session. Schemas and catalogs will exist within a database, and there will still be no possibility of cross-database accesses (but, hopefully, much less need for it either). While I haven't followed this discussion closely, it appears to me that you are trying to make setCatalog reconnect to a new database --- ie, a new cluster in SQL terminology. I think this is a bad idea, as it will create a backwards compatibility problem once we have actual catalogs for setCatalog to work with. I'd suggest that the right short-term thing is for setCatalog to either do nothing or throw an error. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [JDBC] Connection.setCatalog()
[EMAIL PROTECTED] writes: > What do they mean by database though? It is vague because the > definitions are not clear, but I interpret it like this: A "catalog" > equals "PostgreSQL database" which is a subset of the whole "database" > assuming "database" in this context to mean the whole server. I > realise that the "catalog" is fixed but this is also the case for > MySQL. Note it doesn't say "in current connection" so no reason why > you can't create a new one. The bottom line is that supporting them > would make life easier for people like me writing a database admin > tool. This seems like a reasonable interpretation. Given that the alternative would be to not implement it at all, I would agree if you'd implement it as suggested. > > > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. > > > > These methods are all implemented correctly. > > > > > I'm not sure about the stuff in DatabaseMetaData.getTables() for > > > example - at the moment specifying null gets all the tables in the > > > database which the driver is currently connected to. I think this is > > > fine - but different database name patterns might be specified and > > > they may have to be implemented? > > > > Yup. We'll just throw an SQLException in that case. > > Yes, that isn't really a problem. But _if_ you want to support catalogs maybe you >have to go the whole way? Do you mean that DatabaseMetaData.getTables() in a catalog other than the current one should reset the connection to the new catalog? That sounds a bit too revolutionary to me, but then again, as above, the alternative is to not allow it at all. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [JDBC] Connection.setCatalog()
[[[ Original Message from Peter Eisentraut <[EMAIL PROTECTED]> ]]] > [EMAIL PROTECTED] writes: > > > What do they mean by database though? It is vague because the > > definitions are not clear, but I interpret it like this: A "catalog" > > equals "PostgreSQL database" which is a subset of the whole "database" > > assuming "database" in this context to mean the whole server. I > > realise that the "catalog" is fixed but this is also the case for > > MySQL. Note it doesn't say "in current connection" so no reason why > > you can't create a new one. The bottom line is that supporting them > > would make life easier for people like me writing a database admin > > tool. > > This seems like a reasonable interpretation. Given that the alternative > would be to not implement it at all, I would agree if you'd implement it > as suggested. Great, thanks. Here is a context diff against current CVS for org.postgresql.Connection.get/setCatalog() - is this the correct place to implement the methods? - the no-op methods were replicated in both jdbc1 and jdbc2 subclasses. I've made the diff remove the no-op methods in the jdbc1 and jdbc2 subclasses. > > > > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. > > > > > > These methods are all implemented correctly. > > > > > > > I'm not sure about the stuff in DatabaseMetaData.getTables() for > > > > example - at the moment specifying null gets all the tables in the > > > > database which the driver is currently connected to. I think this is > > > > fine - but different database name patterns might be specified and > > > > they may have to be implemented? > > > > > > Yup. We'll just throw an SQLException in that case. > > > > Yes, that isn't really a problem. But _if_ you want to support catalogs maybe > you have to go the whole way? > > Do you mean that DatabaseMetaData.getTables() in a catalog other than the > current one should reset the connection to the new catalog? That sounds a > bit too revolutionary to me, but then again, as above, the alternative is > to not allow it at all. Yes. I agree that it's a bit pointless - it would make life easier for everyone if it was not allowed at all. Do you know whether PostgreSQL will support the catalogname.tablename syntax in SQL queries in the future? At the moment it seems as if catalogs are kinda half supported - you can list all catalogs on the system but you can't manipulate between them easily. For now at least I suggest we leave these methods alone. Thanks, Jason > > -- > Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter > Connection.diff ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [JDBC] Connection.setCatalog()
[[[ Original Message from Peter Eisentraut <[EMAIL PROTECTED]> ]]] > [EMAIL PROTECTED] writes: > > > Connection.setCatalog(String database) should connect to the specified database. > > The JDBC spec says: > > : Sets a catalog name in order to select a subspace of this Connection's > : database in which to work. If the driver does not support catalogs, it > : will silently ignore this request. > > Note the part about "subspace". In PostgreSQL, the database/catalog is > fixed when the connection is established. On other systems you can > probably change the database/catalog while keeping the connection. But > you cannot establish a new connection if the spec says that this method > should make a selection among the objects available in the current > connection. What do they mean by database though? It is vague because the definitions are not clear, but I interpret it like this: A "catalog" equals "PostgreSQL database" which is a subset of the whole "database" assuming "database" in this context to mean the whole server. I realise that the "catalog" is fixed but this is also the case for MySQL. Note it doesn't say "in current connection" so no reason why you can't create a new one. The bottom line is that supporting them would make life easier for people like me writing a database admin tool. By the way, you've already applied my suggested changes to DatabaseMetaData.getCatalogs() - supporting that would imply supporting Driver.set/getCatalog(). You may want to check what other similar drivers do, but as far as I can see the only reason for not implementing catalog support is if you have a database (e.g. Interbase) where you only connect to one "catalog" which is a file location - you can't actually list other available catalogs. I hope that clarifies the way I see it. > > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. > > These methods are all implemented correctly. > > > I'm not sure about the stuff in DatabaseMetaData.getTables() for > > example - at the moment specifying null gets all the tables in the > > database which the driver is currently connected to. I think this is > > fine - but different database name patterns might be specified and > > they may have to be implemented? > > Yup. We'll just throw an SQLException in that case. Yes, that isn't really a problem. But _if_ you want to support catalogs maybe you have to go the whole way? Thanks, Jason Davies [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [JDBC] Connection.setCatalog()
[EMAIL PROTECTED] writes: > Connection.setCatalog(String database) should connect to the specified database. The JDBC spec says: : Sets a catalog name in order to select a subspace of this Connection's : database in which to work. If the driver does not support catalogs, it : will silently ignore this request. Note the part about "subspace". In PostgreSQL, the database/catalog is fixed when the connection is established. On other systems you can probably change the database/catalog while keeping the connection. But you cannot establish a new connection if the spec says that this method should make a selection among the objects available in the current connection. > The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. These methods are all implemented correctly. > I'm not sure about the stuff in DatabaseMetaData.getTables() for > example - at the moment specifying null gets all the tables in the > database which the driver is currently connected to. I think this is > fine - but different database name patterns might be specified and > they may have to be implemented? Yup. We'll just throw an SQLException in that case. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [JDBC] Connection.setCatalog()
Hi Dave, "catalogs" are the same as "databases" I think. Basically, typing \l at the psql prompt will list them. The MySQL driver lists all the databases when getCatalogs() is called (by the way, Peter E has implemented it in DatabaseMetaData.getCatalogs() when I requested it - see CVS). I think maybe the name is just confusing? Methods like Connection.getCatalog() should return the database which the driver is currently connected to. Connection.setCatalog(String database) should connect to the specified database. The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. I'm not sure about the stuff in DatabaseMetaData.getTables() for example - at the moment specifying null gets all the tables in the database which the driver is currently connected to. I think this is fine - but different database name patterns might be specified and they may have to be implemented? The MySQL driver implements get/setCatalog() as well as getCatalogs(). Supporting catalogs is necessary for my database tool to list the databases available etc. Thanks, Jason Davies [EMAIL PROTECTED] [[[ Original Message from Dave Cramer <[EMAIL PROTECTED]> ]]] > Jason, > > Catalogs are foreign to postgres, can you briefly explain what they are, > and how you expect them to work > > Dave > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] On Behalf Of [EMAIL PROTECTED] > Sent: July 13, 2001 8:14 AM > Subject: [JDBC] Connection.setCatalog() > > > Why isn't setCatalog() implemented? This is similar to the > DatabaseMetaData.getCatalogs() which I asked about recently. The MySQL > driver supports it, no reason why we shouldn't. > > Jason Davies > [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]