Thread: 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
Chris <pglist@gmail.com> 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
> > 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
On Sat, 22 Jan 2005 11:25:39 -0800, Chris <pglist@gmail.com> 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
> 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
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
If you explicitly name your schemas (including public) in all of your SQL
it will port easily to Oracle.
Chris
<pglist@gmail.com> To: Tom Lane <tgl@sss.pgh.pa.us>
Sent by: cc: pgsql-general@postgresql.org
pgsql-general-owner@pos 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
On Sun, 23 Jan 2005 12:09:26 -0600, Jeffrey Melloy <jmelloy@visualdistortion.org> 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