Re: 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(at)gmail(dot)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(at)gmail(dot)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

On Sun, 23 Jan 2005 12:09:26 -0600, Jeffrey Melloy
<jmelloy(at)visualdistortion(dot)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