Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
On Sun, 23 Jan 2005 12:09:26 -0600, Jeffrey Melloy [EMAIL PROTECTED] wrote: Although Oracle doesn't have a search path, it is possible to make functions publicly available by doing grant blah to public. After that they can be used without a schema identifier. There is also CREATE PUBLIC SINONYM thing in Oracle. Jeff Nicolai ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
If you explicitly name your schemas (including public) in all of your SQL it will port easily to Oracle. Chris [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Sent by: cc: pgsql-general@postgresql.org [EMAIL PROTECTED]Subject: Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird tgresql.org 01/22/2005 05:05 PM Please respond to Chris AFAIK the idea of a schema search path is specific to PG. I'm not sure how you will handle your public functions in other DBMSes. regards, tom lane I'll probably have to go research this for each database. I have no plans on immediately supporting other databases, but I don't want to use a structure that will be extremely difficult to port down the road. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
You'll probably be best off explicitly providing schema names for your common functions, e.g. SELECT * FROM common.mytable . Depending on your app, that could be better from a security point of view in PostgreSQL as well, if you want to prevent your users from sneakily replacing the common database objects. Ian Barwick In our case the schema's and users are a way to separate data as much as possible. There aren't any physical users who can do their own queries. Still a good idea though, that much less of a chance for bad things to happen in case of a bug in the code. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
Chris wrote: I know this isn't entirely postgresql specific, but it wouldn't be on another list either so here goes... I am writing an open source application where I would like to support at least oracle, and possibly firebird or DB2, in addition to postgresql which will be the default. I'm not going to try to support mysql. The application has many users, and in postgresql what works well is to create a schema for each user instead of a separate database. The main reason for schema's instead of databases is that the app runs under mod perl, and there are too many users to have a pool of open connections to each database. There are also a set of common functions that I usually store in the public schema. That way when working with the data of a particular user I can do a SET search_path TO user,public, and have access to all the functions without having to duplicate them in every schema. My question is how easily would this work with other databases? I know Oracle supports schema's, but I dont' know about the others. I also don't know if other databases have the concept of a search path, but I would think that they do. Although Oracle doesn't have a search path, it is possible to make functions publicly available by doing grant blah to public. After that they can be used without a schema identifier. Jeff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
I know this isn't entirely postgresql specific, but it wouldn't be on another list either so here goes... I am writing an open source application where I would like to support at least oracle, and possibly firebird or DB2, in addition to postgresql which will be the default. I'm not going to try to support mysql. The application has many users, and in postgresql what works well is to create a schema for each user instead of a separate database. The main reason for schema's instead of databases is that the app runs under mod perl, and there are too many users to have a pool of open connections to each database. There are also a set of common functions that I usually store in the public schema. That way when working with the data of a particular user I can do a SET search_path TO user,public, and have access to all the functions without having to duplicate them in every schema. My question is how easily would this work with other databases? I know Oracle supports schema's, but I dont' know about the others. I also don't know if other databases have the concept of a search path, but I would think that they do. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
Chris [EMAIL PROTECTED] writes: ... My question is how easily would this work with other databases? I know Oracle supports schema's, but I dont' know about the others. I also don't know if other databases have the concept of a search path, but I would think that they do. AFAIK the idea of a schema search path is specific to PG. I'm not sure how you will handle your public functions in other DBMSes. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
AFAIK the idea of a schema search path is specific to PG. I'm not sure how you will handle your public functions in other DBMSes. regards, tom lane I'll probably have to go research this for each database. I have no plans on immediately supporting other databases, but I don't want to use a structure that will be extremely difficult to port down the road. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
On Sat, 22 Jan 2005 11:25:39 -0800, Chris [EMAIL PROTECTED] wrote: I know this isn't entirely postgresql specific, but it wouldn't be on another list either so here goes... I am writing an open source application where I would like to support at least oracle, and possibly firebird or DB2, in addition to postgresql which will be the default. I'm not going to try to support mysql. FWIW, Firebird doesn't have any form of schemas or cross-database query support (although I think commercial third-party extensions might exist for the latter). You'll probably be best off explicitly providing schema names for your common functions, e.g. SELECT * FROM common.mytable . Depending on your app, that could be better from a security point of view in PostgreSQL as well, if you want to prevent your users from sneakily replacing the common database objects. Ian Barwick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
