Re: Views, views, views! (long)

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Josh Berkus
> Sent: Thursday, May 05, 2005 10:49 AM
> To: Andreas Pflug
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] Views, views, views! (long)
>
> Andreas,
>
> > There are only two choices: Creating a minimal subset tool, which
will
> > rely on INFORMATION_SCHEMA (or a schema API as in ODBC) as
standardized
> > by SQL specs, or making it specifically for every DBMS, whether
using
> > some fancy views or not.
>
> Thing is, INFORMATION_SCHEMA doesn't hold a lot of information that
people
> need to know. Like permissions,

COLUMN_PRIVILEGES Has one row for each column level permission granted
to or by the current user
TABLE_PRIVILEGES Has one row for each table level permission granted to
or by the current user

> comments,

These tables contain commentary information:
CREATE TABLE SQL_FEATURES
CREATE TABLE SQL_IMPLEMENTATION_INFO
CREATE TABLE SQL_LANGUAGES
CREATE TABLE SQL_SIZING_PROFILES
CREATE VIEW SQL_FEATURES
CREATE VIEW SQL_IMPLEMENTATION_INFO
CREATE VIEW SQL_IMPL_INFO
CREATE VIEW SQL_PACKAGES
CREATE VIEW SQL_SIZING
CREATE VIEW SQL_SIZING_PROFILES
CREATE VIEW SQL_SIZING_PROFS

And you can tack on more tables as needed.

>object owners,

Ownership stuff is contained in these:
CREATE TABLE SCHEMATA
CREATE VIEW ASSERTIONS
CREATE VIEW ATTRIBUTES
CREATE VIEW CHARACTER_SETS
CREATE VIEW CHECK_CONSTRAINTS
CREATE VIEW COLLATIONS
CREATE VIEW COLUMNS
CREATE VIEW COLUMN_DOMAIN_USAGE
CREATE VIEW COLUMN_UDT_USAGE
CREATE VIEW CONSTRAINT_COLUMN_USAGE
CREATE VIEW CONSTRAINT_TABLE_USAGE
CREATE VIEW DIRECT_SUPERTABLES
CREATE VIEW DIRECT_SUPERTYPES
CREATE VIEW DOMAINS
CREATE VIEW DOMAIN_CONSTRAINTS
CREATE VIEW DOMAIN_UDT_USAGE
CREATE VIEW KEY_COLUMN_USAGE
CREATE VIEW METHOD_SPECIFICATIONS
CREATE VIEW METHOD_SPECIFICATION_PARAMETERS
CREATE VIEW PARAMETERS
CREATE VIEW REFERENTIAL_CONSTRAINTS
CREATE VIEW ROUTINES
CREATE VIEW ROUTINE_COLUMN_USAGE
CREATE VIEW ROUTINE_TABLE_USAGE
CREATE VIEW SCHEMATA
CREATE VIEW SCHEMATA_S
CREATE VIEW TABLES
CREATE VIEW TABLE_CONSTRAINTS
CREATE VIEW TRANSFORMS
CREATE VIEW TRANSLATIONS
CREATE VIEW TRIGGERED_UPDATE_COLUMNS
CREATE VIEW TRIGGERS
CREATE VIEW TRIGGER_COLUMN_USAGE
CREATE VIEW TRIGGER_TABLE_USAGE
CREATE VIEW USER_DEFINED_TYPES
CREATE VIEW VIEWS
CREATE VIEW VIEW_COLUMN_USAGE
CREATE VIEW VIEW_TABLE_USAGE

If you need more than what is here, create an ownership table that is
connected to the others using key relationships.

>functions,
ROUTINES Lists one row for each stored procedure or user-defined
function
ROUTINE_COLUMNS Contains one row for each column returned by any
table-valued functions

> types,
DOMAIN_CONSTRAINTS Lists the user-defined datatypes that have rules
bound to them
DOMAINS Lists the user-defined datatypes

> etc. If adding columns and views to the Information schema ... and
> changing
> keys in a couple of places ... is OK, then we have somewhere to go.

Create a new relation that is tied to the table of interest with a key.

> Unfortunately, PostgreSQL does not have a seat on the ANSI committee,
so
> we're
> not going to get the standard changed. The standard lately belongs
to
> Oracle and DB2 and we have to suffer under it.
>
> > Doing it seriously, it probably needs the internal DBMS object
> > identifiers (oid in the case of pgsql), to uniquely identify objects
> > even after a rename. Hiding the OIDs in schema views will reduce
their
> > usability.
>
> Hmmm ... we argued about this. I was in favor of hiding the OIDs
because
> OIDs
> are not consistent after a database reload and names are. I can
see
> your
> point though; what do other people think?

Imagine (if you will) 100 different database systems, each of which has
a different way to access the system tables, and each of which changes
the tables whenever they want. If this picture is firm in mind, then
the absolute necessity of INFORMATION_SCHEMA will crystallize.

Whether or not OID values are published pales in comparison. Of course,
if they do become visible, they should not pollute the
INFORMATION_SCHEMA.

IMO-YMMV.

> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 8: explain analyze is your friend