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
Import Notes
Resolved by subject fallback
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