Monday, October 26, 2009

System Catalog Additions for ODS 12

The following appeared in the Firebird Development List from Dimitry Yemanov:

Here I'd like to have the possible additions to the system tables
discussed and agreed on. Physical ODS changes will be discussed in a
separate thread. As you understand, the sooner we have it resolved, the
easier it would be to set up the time frame for the v3.0 Alpha release.

I'll start with my own proposals, expecting others to follow up with
both comments/critique and their own suggestions. The changes already
done by Adriano are not included here.

RDB$FIELDS
+ RDB$SECURITY_CLASS (standard USAGE privilege for domains)
+ RDB$OWNER_NAME (prohibits altering/dropping domains by non-owners)

RDB$RELATIONS
+ RDB$STATISTICS (blob with binary statistics for the optimizer)

RDB$FUNCTIONS
+ RDB$FUNCTION_ID (allows metadata versioning for PSQL functions)
+ RDB$FUNCTION_BLR (BLR of a PSQL function)
+ RDB$VALID_BLR (BLR validation flag)
+ RDB$DEBUG_INFO (debug information)
+ RDB$LEGACY_FLAG (flag indicating whether this function is legacy
UDF style or new style)
+ RDB$SECURITY_CLASS (standard EXECUTE permission for functions)
+ RDB$OWNER_NAME (prohibits altering/dropping functions by non-owners)

RDB$FUNCTION_ARGUMENTS
+ RDB$ARGUMENT_NAME (input argument name)
+ RDB$FIELD_SOURCE (reference to a domain describing argument)
+ RDB$DEFAULT_SOURCE (default value for argument)
+ RDB$DEFAULT_BLR (default value BLR for argument)
+ RDB$COLLATION_ID (collation ID override for argument)
+ RDB$NULL_FLAG (null flag override for argument)
+ RDB$ARGUMENT_MECHANISM (full domain or TYPE OF)
+ RDB$FIELD_NAME (field name for TYPE OF)
+ RDB$RELATION_NAME (relation name for TYPE OF)

RDB$FILTERS
+ RDB$SECURITY_CLASS (USAGE privilege -- non standard, but probably
useful?)
+ RDB$OWNER_NAME (prohibits altering/dropping filters by non-owners)

RDB$GENERATORS
+ RDB$START_VALUE (start value per SQL standard)
+ RDB$INCREMENT_VALUE (increment value per SQL standard)
+ RDB$MIN_VALUE (minimum value per SQL standard)
+ RDB$MAX_VALUE (maximum value per SQL standard)
+ RDB$CYCLE_FLAG (cycle option per SQL standard)
+ RDB$SECURITY_CLASS (allows to have the standard USAGE privilege for
sequences)
+ RDB$OWNER_NAME (prohibits altering/dropping sequences by non-owners)

RDB$EXCEPTIONS
+ RDB$SECURITY_CLASS (USAGE privilege for exceptions -- non standard,
but probably useful?)
+ RDB$OWNER_NAME (prohibits altering/dropping by non-owners)

RDB$CHARACTER_SETS
+ RDB$SECURITY_CLASS (standard USAGE privilege)
+ RDB$OWNER_NAME (prohibits altering/dropping by non-owners)

RDB$COLLATIONS
+ RDB$SECURITY_CLASS (standard USAGE privilege)
+ RDB$OWNER_NAME (prohibits altering/dropping by non-owners)

As you can see, almost everything shown above is related to either
enhanced metadata security or implementation of PSQL functions.

I don't pretend to add all the aforementioned functionality in v3.0,
these fields can be reserved until actually used later.

I have intentionally avoided the "schemas / longer identifiers" topic
here, as IMO it deserves a separate discussion. Is anybody willing to
start it? :-)

New monitoring tables/fields will also be discussed separately.

Opinions?

Adriano, if you intend to switch from UNICODE_FSS to UTF8 in ODS12
system tables, let's please discuss it as well (here or in a different
thread).

1 comment:

Pól Anaithnid said...

Hi Paul,

You mention

RDB$RELATIONS
+ RDB$STATISTICS (blob with binary statistics for the optimizer)


My question is why do these statistics have to be in blob form - why can they not be held as data in tables in the way much (most/all?) of this class of data is held in (dare I mention it (gasp)) Oracle?


Any views (pun intended!), ideas or opinions welcome.


Paul...