Oracle’s hidden documentation – Commenting the Data Model in Oracle

The Football (or Soccer, if you prefer) World Cup is almost upon us.
England have absolutely no chance so even the false hope that traditionally accompanies major tournaments won’t be around to spoil the enjoyment.
What makes this World Cup extra special is the fact that it’s taking place in Brazil – the spiritual home of the Beautiful Game.
The only previous occasion that Brazil hosted the tournament was in 1950, and it’s worth a brief look at what went on then, if only to provide the basis of the examples that follow.
Back in 1950, as now, money was a bit scarce.
Brazil agreed to host the tournament on condition that the format would be designed to maiximize the number of games played and therefore the gate revenue generated.
It is for this reason that the 1950 tournament is unique in World Cup history as the only tournament to be decided, not by a final, but by a round-robin “Final Pool”.
Then, as now, England travelled to Brazil. Unlike now there was a fair degree of confidence, not to say arrogance, about the prospects of the national team showing these foreigners how the game should really be played.
The Empire may have been slipping away, but it was still a widely held belief – in England at least – that God was an Englishman.
In the event, England managed to lose to an amatuer USA team 1-0 and then get sent packing by Spain.

OMG, WTF does GD Mean ?

Whilst the English may have begun to wonder whether God wasn’t actually Scottish ( or even Welsh for that matter), the Brazilians swept all before them in the final round robin.
In the last match of the tournament, at the new footballing cathederal of the Maracana, over 200,000 people turned up to watch what the whole nation assumed would be their coronation of the new World Champions.
Apparently, no-one bothered to mention this to Uruguay.
Coming from a goal down, Uruguay won the match 2-1 and with it, the World Cup.
In Brazil this game is still known as Maracana├žo – The Maracana Blow.
All of which brings me to the matter at hand.
Abbreviations in both Oracle table names and column names are fairly common. After all, you only get 30 characters to play with for each ( unless you want to go down the long and winding road of quoted identifiers). An example of this might be :

create table final_pool_standings
(
    team varchar2(30) constraint fps_pk primary key,
    pld number(1),
    w number(1),
    d number(1),
    l number(1),
    gf number(2),
    ga number(2),
    pts number(1),
    gd as (gf - ga)
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'URUGUAY', 3, 2, 1, 
    0, 7, 5, 5
)
/
   
insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'BRAZIL', 3, 2, 0,
    1, 14, 4, 4
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'SWEDEN', 3, 1, 0,
    2, 6, 11, 2
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'SPAIN', 3, 0, 1,
    2, 4, 11, 1
)
/

commit;

Now, whilst for a footie fan( well, an English-speaking footie fan), the column names here are pretty much self explainatory, someone who is not quite so familiar with common abbreviations and acronyms associated with football may do a bit of head-scratching when presented with this table.

To be fair, it’s the same when looking at any data structure using abbreviations and acronyms.
In baseball you have GB (Games Back). In cricket, even more confusingly, you have Strike Rate, which means different things in the context of batting averages and bowling averages.
Fortunately, Oracle provides a mechanism to overcome this apparent failure in the QA processes for this particular data model.


Comment on Table

I think the first thing we need to do is to explain what the FINAL_POOL_STANDINGS table is all about :

comment on table final_pool_standings is
    'The 1950 World Cup round-robin group to decide the title. Table Short Name : FPS'
/

Note that the comment itself is a string-literal so it’s not possible to build a concatenated string. On the plus side however, you do get up to 4000 characters to begin to de-mistify your data model.

The _TAB_COMMENTS views provide a means of interrogating table comments :

select comments
from user_tab_comments
where table_name = 'FINAL_POOL_STANDINGS'
/

COMMENTS
------------------------------------------------------------------------------------------
The 1950 World Cup round-robin group to decide the title. Table Short Name : FPS

Unravelling the GD mystery- Column Comments

Just as useful, if not more so, is the ability to comment on individual columns :

comment on column final_pool_standings.team is
    'The national team. Primary Key'
/

comment on column final_pool_standings.pld is
    'Games played in the final pool stage'
/

comment on column final_pool_standings.w is
    'Games won'
/

comment on column final_pool_standings.d is
    'Games drawn'
/

comment on column final_pool_standings.l is
    'Games lost'
/

comment on column final_pool_standings.gf is
    'Goals For - number of goals scored in final pool matches'
/

comment on column final_pool_standings.ga is
    'Goals Against - number of goals conceded in final pool matches'
/

comment on column final_pool_standings.pts is
    'Points gained in final pool matches - two for a win, one for a draw, nothing for a loss'
/

comment on column final_pool_standings.gd is
    'Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column'
/

Most IDE’s actually display column comments in-line with the column definitions so they are easy to spot.
You can also retrieve them from the data dictionary directly :

select column_name, comments
from user_col_comments
where table_name = 'FINAL_POOL_STANDINGS'
/
COLUMN_NAME		       COMMENTS
------------------------------ ------------------------------------------------------------------------------------------
TEAM			       The national team. Primary Key
PLD			       Games played in the final pool stage
W			       Games won
D			       Games drawn
L			       Games lost
GF			       Goals For - number of goals scored in final pool matches
GA			       Goals Against - number of goals conceded in final pool matches
PTS			       Points gained in final pool matches - two for a win, one for a draw, nothing for a loss
GD			       Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column

This sort of information may well be held in whichever modelling tool you may or may not be using. However, there’s no substitute for having these sorts of comments in the database itself, especially if you happen to be the one trying to resolve an urgent support call on a job that’s accessing a data model with which you are unfamiliar.

Amending and deleting comments

If you want to edit a comment, you simply replace it with another string literal.
For example, if I wanted to expand on the comment for the GD column :

comment on column final_pool_standings.gd is
    'Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column. In truth this is slightly anachronistic as Goal Difference was not used to rank teams on the same number of points in this tournament.'
/

If you want to remove a comment altogether, you simply need to set it to a NULL string :

comment on column final_pool_standings.w is 
    ''
/

Even if a table or column has no comment associated with it, it does still have an entry in the _COMMENTS views.

Hidden documentation – Comments on Data Dictionary Views

Oracle is way ahead of you here. It’s been using the comments functionality for tables and views for years.
Not sure what USER_TAB_PRIVS is for (or even, whether or not it’s a table) ?…

select table_type, comments
from dba_tab_comments
where owner = 'SYS'
and table_name = 'USER_TAB_PRIVS'
/

TABLE_TYPE  COMMENTS
----------- --------------------------------------------------------------------------------
VIEW        Grants on objects for which the user is the owner, grantor or grantee

SQL> 

You can also find out more about the differences between the USER, ALL, and DBA versions of a view :

select table_name, comments
from dba_tab_comments
where table_name like '%\_TAB_PRIVS' escape '\'
/

TABLE_NAME	     COMMENTS
-------------------- ------------------------------------------------------------------------------------------
USER_TAB_PRIVS	     Grants on objects for which the user is the owner, grantor or grantee
ALL_TAB_PRIVS	     Grants on objects for which the user is the grantor, grantee, owner,
		      or an enabled role or PUBLIC is the grantee

DBA_TAB_PRIVS	     All grants on objects in the database
ROLE_TAB_PRIVS	     Table privileges granted to roles

Column comments are also included :

select column_name, comments
from dba_col_comments
where owner = 'SYS'
and table_name = 'USER_TAB_PRIVS'
/

COLUMN_NAME                    COMMENTS
------------------------------ --------------------------------------------------------------------------------
GRANTEE                        Name of the user to whom access was granted
OWNER                          Owner of the object
TABLE_NAME                     Name of the object
GRANTOR                        Name of the user who performed the grant
PRIVILEGE                      Table Privilege
GRANTABLE                      Privilege is grantable
HIERARCHY                      Privilege is with hierarchy option

7 rows selected.

SQL> 

There are hundreds of data dictionary objects that are documented in this way.
These do seem to be mainly the USER, ALL and DBA views – V$ views are noticable by their absence. However, the ones that are there do offer a significant amount of useful documentation.

OK. Time to review my World Cup Essentials checklist …

  • World Cup Wall Chart and TV Guide – check
  • Realistic Expectations – check
  • England Penalty shoot-out blind-fold ( just in case) – I’m not sure I’ll be needing that
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s