Metadata manipulation.

    A Spaniard philosopher, Miguel de Unamuno, said once "it's a crazy person the one who is alone". Take the fashion, for example: if you decide to make your hair blue, several people may think you are crazy or simple an individual who doesn't fit in the normal society. However, if several people decide to mimic you and make their hair blue, too, then you are no longer crazy, because your style has became a fashion and it's widely accepted.

    This has happened to Interbase to some degree: it had features too much advanced for its time and they weren't measured in their real value. This fact, combined with a really poor marketing campaign, left people that could have took advantage of these diamonds without knowing anything about the product.

    When you modify a record by means of an SQL update statement, only that record becomes locked while you don't commit your transaction. This means locking granularity at the record level, not at the page level. Since Interbase 3.3 (near 1990), you were able to alter metadata of a table, for example, changing the data type of a field without having to recreate the field. And you can do the trick by brute force in the system tables if you want. Why doesn't the database get corrupted? Easy: because it has a multi version engine and each record version keeps only the difference with the prior. So, if you change metadata, new records and new versions of the old records take the new shape and keep track of the difference with the old versions. When a select is done, a transformation on the fly in the delivered information to the client takes place so you get the record version as it was when your current transaction was started. This is the typical recipe for enlarging a char or varchar field to 50 characters, from Aage Johansen:

update rdb$fields
set rdb$field_length = 50,
    rdb$character_length = 50
where rdb$field_name =
   (select rdb$field_source
   from rdb$relation_fields
   where rdb$relation_name = 'MYTABLE'
    and  rdb$field_name = 'MYFIELD'
   )

Usually, MYTABLE and MYFIELD must be uppercased unless you are playing with special features of dialect 3 under IB6. The example assumes you are using a character set where one visible character is represented by one byte. This is true for char set NONE but in UNICODE and other multi-byte char sets, one visible character may need two or three bytes to be stored, so in that case rdb$field_length would need to be twice or thrice rdb$character_length, respectively. Consult character sets on LangRef o DataDef guides. Improper synchronization of these values according to the character set used by the field being altered, will result in database corruption. Also, don't shrink a string field unless you are 100% sure the longest of the values stored in that column has enough space in the new length. Otherwise, you'll get "string truncation error" messages when selecting from this column.

If you want to effectively change all the records in the table to the new format so the engine don't have to incur in changes on the fly, you can perform a dummy update of the table, for example:

update mytable
set somefield = somefield;
commit;
select * from mytable;
commit;

and then all the fields in each record are converted to the new format for the columns in that table. The select statement is for garbage collecting old record versions. And now Bill "I know how to make money" Gates comes with SqlServer7 to tell you they have "never seen before" capabilities like metadata alteration on tables filled already with data and locking at the record granularity instead of complete page locks as in previous versions. And IT professionals around the world are evangelized by his words. Now, it's fashion and it's not more a crazy feature of Interbase but again, who remembers Interbase had the same capabilities 5 years in advance?

Of course, raw changes by means of altering the system tables are not limited to enlarging strings. You can change the type of a column, for example from smallint to integer or from a varchar to date. Beware that the engine won't check whether the conversion is possible because you are dealing with metadata directly, so don't attempt to change to an incompatible data type with the current stored information or you'll render that column unusable or will corrupt the table in the worst case. For example, it's possible to convert from varchar to integer in some cases (when the column has rdb$field_length <= 4 bytes), but make sure that all the column can be converted before doing the change.

There are several ways to make the same changes:

• Updating directly the system tables.
• Let a low level proprietary utility, QLI, do the job.
• Use a third party database editor or DBA tool make the changes, see here.
• Use the alter table and alter domain syntax.

To help in these tasks, IB6 introduced some extra facilities that are in the form of standard SQL commands and that deal safely with controlled metadata changes.

This page was last updated on 2000-06-02 20:22:49