RFC: System catalog additions in ODS 12

[Dmitry Yemanov]

All,

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).


Dmitry

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dimitry Sibiryakov-3]

> 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)

   Wasn't getting rid of BLR one of goals for FB3?

   SY, SD.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Dimitry Sibiryakov wrote:
>
> Wasn't getting rid of BLR one of goals for FB3?

No, it wasn't.


Dmitry

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Rustam Gadjimuradov]

Dmitry Yemanov
> I'll start with my own proposals, expecting others to follow up with
> both comments/critique and their own suggestions.

May it's time to discuss improvements in constraint messages,
that we discussed recently ?

WBR, GR

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dmitry Yemanov escreveu:
> I'll start with my own proposals, expecting others to follow up with
> both comments/critique and their own suggestions.
>  
CORE-736 requires some additions. Maybe BLR and source text to
RDB$RELATION_CONSTRAINTS. I didn't thought very well about it yet.

> 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).
Yes, I would like, but never tried to see what issues it may cause. This
change requires the SQLVAR changes, which should also be discussed.


Adriano


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dimitry Sibiryakov-3]

>> 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).
> Yes, I would like, but never tried to see what issues it may cause. This
> change requires the SQLVAR changes

   Such as?..

   SY, SD.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Rustam Gadjimuradov]

Adriano dos Santos Fernandes
> CORE-736 requires some additions. Maybe BLR and source text to
> RDB$RELATION_CONSTRAINTS. I didn't thought very well about it yet.

Do not forget about CORE-1852, where you said that planning it for 3.0 :)
I think it's better to have generic solution for all type of constraints.

WBR, GR

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dimitry Sibiryakov escreveu:
>>> 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).
>>>      
>> Yes, I would like, but never tried to see what issues it may cause. This
>> change requires the SQLVAR changes
>>    
>
>    Such as?..
Length of names > 31 bytes.


Adriano


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dimitry Sibiryakov-3]

> Dimitry Sibiryakov escreveu:
>>>> 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).
>>>>      
>>> Yes, I would like, but never tried to see what issues it may cause. This
>>> change requires the SQLVAR changes
>>>    
>>    Such as?..
> Length of names > 31 bytes.

   I don't see how change from UNICODE_FSS to UTF8 can increase size of
names. Thay both have variable character length and for most of planes
gives the same sequence of bytes.
   Let alone that only completely crazy DB developers use non-ASCII
characters in object names.

   SY, SD.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Alexander Peshkoff]

On Monday 26 October 2009 13:36:25 Dimitry Sibiryakov wrote:
>    Let alone that only completely crazy DB developers use non-ASCII
> characters in object names.

Not agreed. I've seen end-user systems, written on MsAccess, that are very
happy to use russian fields and tables names.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dimitry Sibiryakov escreveu:
>> Length of names > 31 bytes.
>>    
>
>    I don't see how change from UNICODE_FSS to UTF8 can increase size of
> names. Thay both have variable character length and for most of planes
> gives the same sequence of bytes.
>    Let alone that only completely crazy DB developers use non-ASCII
> characters in object names.
We have no reason to switch a lot of hack to another.

If you not like this flexibility, go on and propose its removal.

It should be completely supported until that.


Adriano


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dimitry Sibiryakov-3]

>>    I don't see how change from UNICODE_FSS to UTF8 can increase size of
>> names. Thay both have variable character length and for most of planes
>> gives the same sequence of bytes.
> We have no reason to switch a lot of hack to another.

   Why a lot? To exactly only one hack: silent truncation to 32 bytes
during transformation to XSQLVAR.

   SY, SD.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Mark Rotteveel]

> >>    I don't see how change from UNICODE_FSS to UTF8 can increase size of
> >> names. Thay both have variable character length and for most of planes
> >> gives the same sequence of bytes.
> > We have no reason to switch a lot of hack to another.
>
>    Why a lot? To exactly only one hack: silent truncation to 32 bytes
> during transformation to XSQLVAR.

Which could result in invalid UTF-8 (or UNICODE_FSS) byte sequences: not a good idea.

--
Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3.5 -
sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Adriano dos Santos Fernandes wrote:
>
> CORE-736 requires some additions. Maybe BLR and source text to
> RDB$RELATION_CONSTRAINTS.

I see two approaches here.

An easy one would be to add RDB$EXCEPTION_NAME referencing
RDB$EXCEPTIONS. The DDL syntax would not support value expressions and,
if <xcp message> would also be allowed (instead of <xcp name>), then an
exception should be created automagically behind the scene.

A more complex one would allow value expressions that may reference
fields etc, but this would require SOURCE / BLR columns to be added as
well, and we'll have to parse/validate them, etc.

Personally, I'd prefer the easy way, but this is just me.

Anyway, I don't require the new ODS to be formalized today, so we have
some time to think about it ;-)


Dmitry

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Alexander Peshkoff]

On Monday 26 October 2009 14:48:21 Mark Rotteveel wrote:

> > >>    I don't see how change from UNICODE_FSS to UTF8 can increase size
> > >> of names. Thay both have variable character length and for most of
> > >> planes gives the same sequence of bytes.
> > >
> > > We have no reason to switch a lot of hack to another.
> >
> >    Why a lot? To exactly only one hack: silent truncation to 32 bytes
> > during transformation to XSQLVAR.
>
> Which could result in invalid UTF-8 (or UNICODE_FSS) byte sequences: not a
> good idea.

Agreed - very bad idea. We should not go this way.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dmitry Yemanov escreveu:

> Adriano dos Santos Fernandes wrote:
>  
>> CORE-736 requires some additions. Maybe BLR and source text to
>> RDB$RELATION_CONSTRAINTS.
>>    
>
> I see two approaches here.
>
> An easy one would be to add RDB$EXCEPTION_NAME referencing
> RDB$EXCEPTIONS. The DDL syntax would not support value expressions and,
> if <xcp message> would also be allowed (instead of <xcp name>), then an
> exception should be created automagically behind the scene.

Problem with this is that we have "parameterized exceptions" in the
tracker, and I think we should support it.

Hence, it should be allowed here, so it's why I would choose BLR.


Adriano


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Adriano dos Santos Fernandes wrote:
>
> Problem with this is that we have "parameterized exceptions" in the
> tracker, and I think we should support it.
>
> Hence, it should be allowed here, so it's why I would choose BLR.

I'm not going to argue in general but please think in terms of
priorities. We have many tracker tickets and nobody says they all should
be scheduled for v3.0 :-) As for me, parameterized exceptions could wait
for better times, but I'm not the one going to make my hands dirty with
this feature, so my opinion may be ignored.


Dmitry

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dimitry Sibiryakov-3]

>>>>    I don't see how change from UNICODE_FSS to UTF8 can increase size of
>>>> names. Thay both have variable character length and for most of planes
>>>> gives the same sequence of bytes.
>>> We have no reason to switch a lot of hack to another.
>>    Why a lot? To exactly only one hack: silent truncation to 32 bytes
>> during transformation to XSQLVAR.
>
> Which could result in invalid UTF-8 (or UNICODE_FSS) byte sequences: not a good idea.

   Ok, truncation to number of chars that fits 32 bytes.

   SY, SD.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Adriano dos Santos Fernandes-3]

Dmitry Yemanov escreveu:

> Adriano dos Santos Fernandes wrote:
>  
>> Problem with this is that we have "parameterized exceptions" in the
>> tracker, and I think we should support it.
>>
>> Hence, it should be allowed here, so it's why I would choose BLR.
>>    
>
> I'm not going to argue in general but please think in terms of
> priorities. We have many tracker tickets and nobody says they all should
> be scheduled for v3.0 :-) As for me, parameterized exceptions could wait
> for better times, but I'm not the one going to make my hands dirty with
> this feature, so my opinion may be ignored.

I'm not saying it's a priority. I'm just saying about CORE-736 is marked
for 3.0 alpha1, so I mentioned its ODS changes here.

But later when we talk about parameterized exceptions, and if it have
exception name in relation_constrains, we'll have a problem with the
integration of both features.


Adriano


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Vlad Khorsun-2]

    I offer to add timestamp's of creation and last change for every object.

    I.e. something like RDB$CREATE_TS and RDB$CHANGE_TS to the
RDB$RELATIONS, RDB$PROCEDURES, RDB$FUNCTIONS, RDB$TRIGGERS,
RDB$INDICES, RDB$FIELDS etc.

    Also it seems important to add at least some of run-time counters into RDB$RELATIONS
and RDB$INDICES. I.e. to add at least RDB$INSERTS, RDB$DELETES, RDB$UPDATES.
This allows to automatically recalculate index\column statistics after some volume of
changes. Also it seems useful to add RDB$RECORD_COUNT, RDB$PAGE_COUNT into
RDB$RELATIONS to help optimizer to evaluate cardinality. Of course this numbers will be
not exact and updated in lazy mode.

    Also i plan to add support for named EXTERNAL DATA SOURSEs and corresponding
system tables for it.

Regards,
Vlad

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel