|
| 1 | +From ronz@ravensfield.com Tue May 22 17:35:37 2001 |
| 2 | +Return-path: <ronz@ravensfield.com> |
| 3 | +Received: from carp.ravensfield.com ([209.41.227.126]) |
| 4 | +by candle.pha.pa.us (8.10.1/8.10.1) with ESMTP id f4MLZaQ17913 |
| 5 | +for <pgman@candle.pha.pa.us>; Tue, 22 May 2001 17:35:37 -0400 (EDT) |
| 6 | +Received: from coho.ravensfield.com (coho [209.41.227.117]) |
| 7 | +by carp.ravensfield.com (Postfix) with SMTP |
| 8 | +id 5C2A9800D; Tue, 22 May 2001 16:46:38 -0500 (EST) |
| 9 | +Content-Type: text/plain; |
| 10 | + charset="iso-8859-1" |
| 11 | +From: Andrew Rawnsley <ronz@ravensfield.com> |
| 12 | +Organization: Ravensfield Geographic |
| 13 | +To: Bruce Momjian <pgman@candle.pha.pa.us> |
| 14 | +Subject: Re: [GENERAL] Queries across multiple databases (was: SELECT from a table in another database). |
| 15 | +Date: Tue, 22 May 2001 17:37:25 -0400 |
| 16 | +X-Mailer: KMail [version 1.2] |
| 17 | +cc: Tom Lane <tgl@sss.pgh.pa.us> |
| 18 | +References: <200105220437.f4M4bUA00539@candle.pha.pa.us> |
| 19 | +In-Reply-To: <200105220437.f4M4bUA00539@candle.pha.pa.us> |
| 20 | +MIME-Version: 1.0 |
| 21 | +Message-ID: <01052217372504.01367@coho.ravensfield.com> |
| 22 | +Content-Transfer-Encoding: 8bit |
| 23 | +Status: ORr |
| 24 | + |
| 25 | +On Tuesday 22 May 2001 12:37am, Bruce Momjian wrote: |
| 26 | +> Can you send me a little sample of SCHEMA use? |
| 27 | + |
| 28 | +Pardon if this is more long-winded or tangental than you are looking for... |
| 29 | + |
| 30 | +What may beconfusing many people (not excluding myself from time to time) is |
| 31 | +that cross-schema queries may have nothing to do with cross-database queries, |
| 32 | +which is an entirely different kettle of trout.... SCHEMAs as used by at |
| 33 | +least by Oracle and Sybase are nothing more than users/object owners (I have |
| 34 | +no experience with DB2 or Informix, or anything more exotic than that). |
| 35 | + |
| 36 | +Just off the top of my head, what would satisfy most people would be to be |
| 37 | +able to refer to objects as OWNER.OBJECT, with owner being 'within' the |
| 38 | +database (i.e. DATABASE.OWNER.OBJECT, which is how Sybase does it. Oracle has |
| 39 | +no 'database' parallel like that). Whether you do it Oracle-fashion and use |
| 40 | +the term SCHEMA for owner pretty universally or Sybase fashion and just pay |
| 41 | +lip service to the word doesn't really matter (unless there is a standards |
| 42 | +compliance issue). |
| 43 | + |
| 44 | +As to creating schemas...In Oracle you have to execute the CREATE SCHEMA |
| 45 | +AUTHORIZATION <user> while logged in as that user before you can add objects |
| 46 | +under that user's ownership. While it seems trivial, if you have a situation |
| 47 | +where you do not want to grant a user session rights, you have to grant them |
| 48 | +session rights, log in as them, execute CREATE SCHEMA..., then revoke the |
| 49 | +session rights. Bah. A table created by user X in schema Y is also owned by |
| 50 | +user Y, and its user Y that has to have many of the object rights to create |
| 51 | +that table. |
| 52 | + |
| 53 | +In Sybase, its essentially the same except the only real use for the CREATE |
| 54 | +SCHEMA command is for compliance and to group some DDL commands together. |
| 55 | +Other than that, Sybase always refers to schemas as owners. You don't have to |
| 56 | +execute CREATE SCHEMA... to create objects - you just need the rights. I've |
| 57 | +never used it at least - the only thing I see in it is eliminating the need |
| 58 | +to type 'go' after every DDL command. |
| 59 | + |
| 60 | +As for examples from Oracle space - |
| 61 | + |
| 62 | +Here is a foreign key reference with delete triggers from a table in |
| 63 | +schema/user PROJECT to tables in schemas/users SERVICES and WEBCAL: |
| 64 | + |
| 65 | +CREATE TABLE PROJECT.tasks_users ( |
| 66 | +� �event_id INTEGER REFERENCES WEBCAL.tasks(event_id) ON DELETE CASCADE, |
| 67 | +� �user_id VARCHAR2(25) REFERENCES SERVICES.users(user_id) ON DELETE CASCADE, |
| 68 | +� �confirmed CHAR(1), |
| 69 | +� �PRIMARY KEY (event_id,user_id) |
| 70 | +); |
| 71 | + |
| 72 | +A join between tables in would be |
| 73 | +SELECT � A.SAMPLE_ID, |
| 74 | +�������� ������� A.CONCENTRATION, |
| 75 | +�������� ������� A.CASNO, |
| 76 | +�������� ������� B.PARAMETER, |
| 77 | +�������� ������� C.DESCRIPTION AS STYPE |
| 78 | +�������� FROM HAI.RESULTS A, SAMPLETRACK.PARAMETERS B, |
| 79 | +SAMPLETRACK.SAMPLE_TYPE C |
| 80 | +�������� ������� WHERE A.CASNO = B.CASNO AND A.SAMPLE_TYPE = B.SAMPLE_TYPE |
| 81 | + |
| 82 | +In both Oracle and Sybase, all the objects are in the same 'database' |
| 83 | +(instance in Oracle), as I assume they would be in Postgres. There is I |
| 84 | +assume a name space issue - one should be able to create a FOO.BAR and a |
| 85 | +BAR.BAR in the same database. |
| 86 | + |
| 87 | +> I may be adding it to |
| 88 | +> 7.2 inside the same code that maps temp table names to real tables. |
| 89 | +> |
| 90 | + |
| 91 | +Excellent! I see light at the end of the tunnel (I will say the Postgres |
| 92 | +maintainers are among the most solidly competent around - one never has any |
| 93 | +real doubts about the system's progress). |
| 94 | + |
| 95 | +-- |
| 96 | +Regards, |
| 97 | + |
| 98 | +Andrew Rawnsley |
| 99 | +Ravensfield Digital Resource Group, Ltd. |
| 100 | +(740) 587-0114 |
| 101 | +www.ravensfield.com |
| 102 | + |