ALTER DESCRIPTION

Description

InterBase 2020 introduces support for modifying an entity's description in the database schema.

InterBase defines and stores each entity type in various database system tables. Many of the InterBase system tables tracking various database entities, have a column/field called RDB$DESCRIPTION. The ALTER DESCRIPTION syntax allows an authenticated user to modify the description comment stored in this field (as a text blob) for a specific database entity like a table, column, stored procedure etc. The description value/state can also be reset to NULL for any database entity.

In addition to the above, this feature also enables tracking in ODS version 18 databases, RDB$DESCRIPTION for Constraint, Role and Generator. Older ODS versions cannot track descriptions for these entity types.

This documentation describes support for modifying an entity's description in the database schema. Since each database entity type is defined and stored in various database system tables by InterBase, the DDL usage is not part of the SQL standard. Such data dictionary comments are often edited/stored along with the database schema for database designers and developers to convey some meaningful information about the entity to users of the database. Database tools can greatly benefit from a standard DDL syntax they can use to apply various descriptions to document the user's database schema.

Use

Users of InterBase prior to this new DDL syntax being available would execute a UPDATE <system_table> SET RDB$DESCRIPTION=<blob_text> WHERE <entity_name>=<user_entity>, for each entity type. This requires the user to know the database system schema in detail for each entity type. The new DDL syntax takes away this complexity, and provides a new ALTER DESCRIPTION FOR syntax that is standard across all database entities supported by InterBase.

syntax:

ALTER DESCRIPTION FOR <object> SET {'sometext' | NULL}
  
<object>      ::=  DATABASE
                   | <basic-type> object_name
                   | COLUMN relation_name.field_name
                   | PARAMETER procedure_name.parameter_name
  
<basic-type>  ::=  CHARACTER SET | COLLATION | CONSTRAINT | DOMAIN | ENCRYPTION
                   | EXCEPTION | EXTERNAL FUNCTION | FILTER
                   | GENERATOR | INDEX | PROCEDURE | ROLE
                   | SUBSCRIPTION | TABLE | TABLESPACE | TRIGGER
                   | USER | VIEW

Sample definitions with text:

/* Use ALTER DESCRIPTION command with description set to text */ 
alter description for database set 'Database description sample';
alter description for exception customer_check set 'Exception msg: Checking on customer sample';
alter description for filter desc_filter set 'Filter description sample';
alter description for index custnamex set 'Index description sample';
alter description for procedure add_emp_proj set 'Stored Procedure description sample';
alter description for table employee set 'Table description sample';
alter description for table "MyTableDelim" set 'Table delimited identifier description sample';
alter description for trigger set_cust_no set 'Trigger description sample';
alter description for external function abs set 'UDF description sample';
alter description for user sysdso set 'User description sample';
alter description for subscription sub_ceo_multidevice set 'Subscription description sample';
alter description for encryption backup_key set 'Encryption key description sample';
alter description for role role1 set 'Role description sample';
alter description for generator emp_no_gen set 'Generator description sample';
alter description for character set utf8 set 'character set description sample';
alter description for collation en_us set 'collation description sample';
alter description for domain lastname set 'domain description sample';
alter description for view phone_list set 'view description sample';
alter description for parameter add_emp_proj.emp_no set 'procedure parameter description sample';
alter description for column employee.last_name set 'column description sample';
alter description for column "MyTableDelim".f1 set 'delim table normal column description sample';
alter description for column "MyTableDelim"."MyFieldDelim1" set 'delim table delim column 1 description sample';
alter description for column "MyTableDelim"."My Field Delim 2" set 'delim table delim column 2 description sample';
alter description for column phone_list.phone_ext set 'view column description sample';
alter description for tablespace tspace_one set 'tablespace description sample';
alter description for constraint CC_PK set 'Primary Key Constraint description sample';
 
commit;

Sample definitions with NULL:

/* Use ALTER DESCRIPTION command with description set to NULL */
 
alter description for database set NULL;
alter description for exception customer_check set NULL;
alter description for filter desc_filter set NULL;
alter description for index custnamex set NULL;
alter description for procedure add_emp_proj set NULL;
alter description for table employee set NULL;
alter description for table "MyTableDelim" set NULL;
alter description for trigger set_cust_no set NULL;
alter description for external function abs set NULL;
alter description for user sysdso set NULL;
alter description for subscription sub_ceo_multidevice set NULL;
alter description for encryption backup_key set NULL;
alter description for role role1 set NULL;
alter description for generator emp_no_gen set NULL;
alter description for character set utf8 set NULL;
alter description for collation en_us set NULL;
alter description for domain lastname set NULL;
alter description for view phone_list set NULL;
alter description for parameter add_emp_proj.emp_no set NULL;
alter description for column employee.last_name set NULL;
alter description for column "MyTableDelim".f1 set NULL;
alter description for column "MyTableDelim"."MyFieldDelim1" set NULL;
alter description for column "MyTableDelim"."My Field Delim 2" set NULL;
alter description for column phone_list.phone_ext set NULL;
alter description for constraint CC_PK set NULL;
 
commit;

ISQL extract

ISQL command's "extract" function (-a) should generate ALTER DESCRIPTION lines for each basic-type entity's description.

Requirements and Constraints

The ALTER DESCRIPTION syntax only works with InterBase 2020 and later versions.
For pre-existing (historic) entity types such as Constraints, Roles and Generators, descriptions can only be tracked when using ODS version 18 or later databases.

Migration issues

The ANSI/SQL keyword COLLATION is now a reserved keyword in InterBase. Should you have the need to define any database entities with the name "COLLATION", please delimit the name with double-quotes by using a database with dialect version 3 or above. ALTER DESCRIPTION for Constraints, Roles and Generators can only be set if you are using ODS version 18 or later.