Alter Column Definition - Design Specification

High-Level Design

The ability to alter metadata is one that InterBase supports via GDML. In order to support this functionality in SQL, the following components need to be modified

  • DSQL

    The DSQL parser (and subsequent execution paths) need to be modified to allow for the new syntax for altering column definitions. In addition to this, there will be two new keywords added; MODIFY and TYPE.

  • JRD

    New dyn parameters will be added to ibase.h. In addition the functions DYN_modify_local_field, DYN_modify_relation, and DYN_modify_global_field will be updated. These updates are described below.

In order to ensure a consistent state in the database, column definitions can only be changed if there are no dependencies on them. There are exceptions to this as the following dependencies are allowed

  • Primary / foreign key
  • Indices

High Level Algorithm

Once the parser accepts the syntax for altering a definition, a DYN string is created for the statement. The DYN string is created from the nodes generated by the parser. After the DYN string is created, it is passed on to dyn_mod.e to perform the actual work of updating the column definition. When performing the updates, the following information is checked (if needed)

  • Dependencies
  • Datatypes (is it a base type or a domain)
  • Field positions (GDML would not allow columns to 'swap' positions)

Detailed Design

Internal Data Structures


The initial grammar for the syntax is as follows (changes are bold):

For changes to field definitions:

alter_op : DROP simple_column_name drop_behaviour
{ $$ = make_node (nod_del_field, 2, $2, $3); }
{ $$ = make_node (nod_delete_rel_constraint, (int) 1, $3);}
| ADD column_def
{ $$ = $2; }
| ADD table_constraint_definition
{ $$ = $2; }

| MODIFY column_def
{ $$ = make_node (nod_mod_field_type, 1, $2); }
| MODIFY simple_column_name POSITION nonneg_short_integer
{ $$ = make_node (nod_mod_field_pos, 2, $2,
MAKE_constant ((STR) $4, CONSTANT_SLONG)); }
| MODIFY simple_column_name TO simple_column_name
{ $$ = make_node (nod_mod_field_name, 2, $2, $4); }

For changes to domain definitions:

alter_domain_op : SET begin_string default_opt end_trigger
{ $$ = make_node (nod_def_default, (int) e_dft_count,
$3, $4); }
| ADD CONSTRAINT domain_check_constraint
{ $$ = $3; }
| ADD domain_check_constraint
{ $$ = $2; }
{$$ = make_node (nod_del_default, (int) 0, NULL); }
{ $$ = make_node (nod_delete_rel_constraint, (int) 1, NULL); }
| TO simple_column_name
{ $$ = make_node (nod_mod_field_name, 1, $2); }
| TYPE data_type end_trigger
{ $$ = make_node (nod_mod_domain_type, 1, $2); };

Detailed Algorithm


In order to keep RDB$FIELDS 'clean', any time a field definition is changed from a base type to a domain, the existing entry in RDB$FIELDS should be removed. In order to do this, however, we need to know two things

  • is the existing field definition using a domain or a base type
  • is the existing field definition being used by more than one field

The answer to the first bullet is found when checking the second bullet. Since InterBase make no distinctions between field types and domains, there is no straightforward way to know if a field is using a base type or domain definition. To alleviate this problem, any RDB$FIELD_SOURCE which contains the prefix RDB$ is assumed to be a local field definition.

dyn_mod.e::modify_lfield_type & modify_gfield_type

These functions are very similar to their counterparts in dyn_def.e. (DYN_define_local_field and DYN_define_global_field) except that they alter the definitions in the system tables instead of adding them to the system tables.

Finding dependencies

In order to determine if a field definition can be altered, the existing field needs to be checked to see if any other database object is dependent on it. To determine this, the following queries will be issued:

Triggers, Stored Procedures, Computed Columns, Views:

FOR (REQUEST_HANDLE request TRANSACTION_HANDLE gbl->gbl_transaction)
RDB$FIELD_NAME EQ field_name
dependencies = TRUE;


If the field being altered is part of an index, the following procedure will be used:

  • If the field name is being altered, the change is made in RDB$INDEX_SEGMENTS.
  • The field RDB$INDEX_NAME in RDB$INDICES will be touched to force a recreate of the index

Updating the metadata cache

To ensure that any new requests use the latest version of the relation in the cache, a call to METD_drop_relation will be made once the field definition is updated.

New/Affected Modules

  • dsqlnode.h

    New node definitions to be added:

    • nod_mod_field_name
    • nod_mod_domain_type
    • nod_mod_field_pos
    • nod_mod_field_type
  • dsqlkeywords.h

    New keywords added. These keywords will be introduced in SQL 3

    • MODIFY
    • TYPE
  • dsqlddl.c

    Function modify_domain

    • Add cases for nod_mod_domain_type and nod_mod_field_name

    Function modify_relation

    • Add cases for nod_mod_field_name, nod_mod_field_type, nod_mod_field_pos
  • dsqlparse.y

    Add tokens for MODIFY and TYPE

    Add grammar to alter_op and alter_domain_op for the new syntax

  • jrdibase.h

    Add dyn verbs isc_dyn_new_fld_name, isc_dyn_new_dom_name Update isc_dyn_last_dyn_value

  • jrddyn_mod.e

    Allow DYN_modify_global_field to update a domain name

    Allow DYN_modify_local_field to update a field name

    Allow DYN_modify_local_field to update a field type

    Add functions modify_lfield_type, modify_gfield_type, and IsFieldADomain

  • jrddyn_util.e

    Add functions DYN_UTL_gen_unique_id and DYN_UTL_generate_field_name from dyn_def.e

  • jrddyn_def.e

    Move functions gen_unique_id and generate_field_name to dyn_util.e

Testing Considerations

  • It is crucial to ensure that all allowed dependencies are updated correctly
  • Altering definitions can not affect transactions in progress
  • Changing a field from a type based definition to a domain definition must properly update all occurences of that field and remove the field information from RDB$FIELDS