|
Using InterBase System Tables - Felix John COLIBRI. |
- abstract : The InterBase / FireBird System Tables: description of the main Tables, with their relationship and examples about how to extract information from them
- key words : InterBase - FireBird - System Table
- software used : Windows XP, InterBase 6.5
- hardware used : Pentium 2.800Mhz, 512 M memory, 140 G hard disc
- scope : InterBase 6 to 7.5, FireBird 1 to 2
- level : Delphi developer - InterBase / FireBird developer
- plan :
1 - InterBase / Firebird System Tables
InterBase, like all other Sql Engines, uses system tables to store the database schema and otherwise manage the complete engine. Those tables are described in the InterBase Language
Reference Manual, in a very alphabetic, "reference manual" style. Which means that it is difficult to relate the tables to each other, and, more important, to see how this knowledge could be used for our own development purposes.
In this article, we are going to present a more conceptual view of those tables, and show some request which extract different schema informations pieces from those Tables. We have used the EMPLOYEE.GDB demo database to extract the examples presented
in this paper.
2 - InterBase System Tables Description 2.1 - Conceptual architecture Before looking at the detailed system tables, we can guess what should be involved:
- any relational database consists of Tables, which are lists of rows, each row containing the same number of fields.
- each field has a name, a type, some parameters, and can be specified
explicitely as a Domain or implicitely as the column of a Table.
- the Table can have additional elements: indexes, constraints, triggers
- some other concepts, not tightly related to the Table / field cluster, will
include generators, stored procedures, exceptions, roles, user defined functions
- and there are some database wide tables (the database name, transactions, log_files ...)
Using those general ideas, and looking at the _id suffix or the references from one table to another in the system table definitions, we could build the following general schema (rdb$ removed, not all links displayed, not all System Tables):
2.2 - Column specifications To create a Table, we have to describe each column (name, type, parameters). This can be done - either by directly specifying the Columns:
CREATE TABLE country
(country_name VARCHAR(15), currency VARCHAR(10));
|
- or by creating an explicit Domain, and using the Domain name when we create the Table
CREATE DOMAIN empno AS SMALLINT;
CREATE DOMAIN projno AS CHAR(5);
CREATE TABLE employee_project
(emp_no empno, proj_id projno);
|
The information about Domains are kept in the rdb$fields Table, whereas the columns directly defined in a CREATE TABLE request are in the rdb$relation_fields Table.
The details of the collation, character set and array dimensions are kept in 3 separate tables. The Tables defining the column types are linked in the following way (rdb$ removed)
The main information in the rdb$fields Table are (partial, reordered): and:
- rdb$name is set during the Domain creation ("empno" from CREATE DOMAIN empno) or generated for Table columns created directly ("rdb$1" for the currency field of the country table)
- rdb$system_flag is equal to 1 for System Tables
- rdb$description: an optional description
- the type is specified by those fields
- rdb$field_type and rdb$field_sub_type specify the data type
- rdb$field_length, rdb$field_scale and rdb$field_precision,
rdb$character_length are size parameters
- rdb$dimensions is used for ARRAY fields. This will also involve the rdb$dimension Table (see below)
- rdb$collation_id and rdb$character_set_id are linked to the character tables (see below)
Here is a summary of those fields: - the Domain can also have some constraints
- rdb$null_flag. For instance:
CREATE DOMAIN prodtype
AS VARCHAR(12)
NOT NULL
| - rdb$default_value and rdb$default_source specify default values:
CREATE DOMAIN budget
AS NUMERIC(15, 2)
DEFAULT 50000
| - rdb$validation_blr and rdb$validation_source specify constraints. Here are a couple of examples:
CREATE DOMAIN budget
AS NUMERIC(15, 2)
CHECK (VALUE > 10000 AND VALUE <= 2000000);
CREATE DOMAIN custno
AS INTEGER
CHECK (VALUE > 1000);
CREATE DOMAIN deptno AS CHAR(3)
CHECK
(VALUE = '000'
OR (VALUE > '0' AND VALUE <= '999')
OR VALUE IS NULL);
CREATE DOMAIN jobgrade AS SMALLINT
CHECK (VALUE BETWEEN 0 AND 6);
CREATE DOMAIN ponumber AS CHAR(8)
CHECK (VALUE STARTING WITH 'V');
CREATE DOMAIN prodtype AS VARCHAR(12)
CHECK (VALUE IN ('software', 'hardware', 'other')) NOT NULL;
CREATE DOMAIN projno AS CHAR(5)
CHECK (VALUE = UPPER (VALUE));
|
- rdb$computed_blr and rdb$computed_source specify calculated fields:
CREATE TABLE salary_history
(
emp_no empno,
old_salary salary,
percent_change DOUBLE PRECISION DEFAULT 0,
new_salary
COMPUTED BY
(old_salary + old_salary * percent_change / 100)
)
|
Here are the main columns of rdb$character_set: and the rdb$collation Table
and the ARRAY dimensions are specified by:
To get the information about the domains, we can use the following request:
SELECT *
FROM rdb$fields
ORDER BY rdb$field_name
| and:
- we can filter out the internal fields by testing the system flag as well as any number after the $ of the name
- type and sub-type are decoded using the description above
- we can display the different size parameters, and we can also get the character code and collation order
- rdb$null_flag can be used to display NULL
- rdb$default_value and rdb$default_source will display any default
- rdb$validation_blr and rdb$validation_source will display the checks
Here is an example of the EMPLOYEE domains using our ColIbExp explorer: 
2.3 - The Table Columns
As explained above, we can directly specify the Column attributes in the CREATE TABLE request. In this case - the information is stored in the rdb$relation_fields Table
- an entry is generated in the rdb$fields Table, with an internally generated rdb$field_name
On the other hand, when we specify a Column using a previously defined Domain,
the rdb$relation_fields simply references the rdb$fields Table. In addition, we may add additional constraints (NOT NULL, defaults), which will override the Domain parameters. Here is an example:
CREATE DOMAIN deptno
AS CHAR(3)
CHECK
(VALUE = '000'
OR (VALUE > '0' AND VALUE <= '999')
OR VALUE IS NULL);
CREATE DOMAIN empno
AS SMALLINT;
CREATE DOMAIN budget
AS NUMERIC(15, 2)
DEFAULT 50000
CHECK (VALUE > 10000 AND VALUE <= 2000000);
CREATE DOMAIN phonenumber
AS VARCHAR(20);
CREATE TABLE department
(
dept_no deptno NOT NULL,
department VARCHAR(25) NOT NULL,
head_dept deptno,
mngr_no empno,
budget budget,
location VARCHAR(15),
phone_no phonenumber DEFAULT '555-1234'
);
| and - dept_no adds NOT NULL to the deptno Domain
- phone_no adds a DEFAULT value to phonenumber
Here are the relations between those tables:
The rdb$relation_fields Table main fields are:
Note that - all the size parameters are missing from this Table. So those informations come from the rdb$fields Table
- there are no checks on the Columns, but we can add them at the Table level
If we want to get the complete definition of the columns of a Table, we can use the following request:
SELECT rdb$field_name, rel_field.rdb$field_name,
rdb$field_type, rdb$field_sub_type,
rel_field.rdb$null_flag, rdb$field_length, rdb$field_scale,
rdb$character_length, rdb$field_precision,
field.rdb$default_source, field.rdb$validation_source
FROM rdb$relations rel
JOIN rdb$relation_fields rel_field
ON rel_field.rdb$relation_name = rel.rdb$relation_name
JOIN rdb$fields field
ON rel_field.rdb$field_source = field.rdb$field_name
WHERE rel.rdb$relation_name = : RelationName
ORDER BY rel_field.rdb$field_position, rel_field.rdb$field_name
|
Using this kind of request, here is a snapshot of the columns of the DEPARTMENT Table from the EMPLOYEE database: 
2.4 - Tables Tables are managed by the rdb$relations System Table. Tables are linked to many other database objects: - the Columns
- the Views
- the Indexes
- the constraints and triggers
- some security checks
The following schema displays the direct links to the rdb$relations Table:
The main fields of the rdb$relation Table are: And - rdb$view_blr is NULL for Views
- rdb$owner_name tells who created the Table, and is used to manage Table access
To get the list of all non system Table names, you may use:
SELECT rdb$relation_name
FROM rdb$relations
WHERE
(
(rdb$system_flag = 0)
OR
(rdb$system_flag IS NULL)
)
AND
(rdb$view_source IS NULL)
ORDER BY rdb$relation_name
| Note that
- we have to test rdb$system_flag both for 1 and IS NULL, since InterBase does not always use the same criterion
- fetching the list of Tables is so frequent that this request has been
incorporated in our standard U_IBX.PAS helper UNIT.
More interestingly, to get the columns of a Table:
SELECT rdb$field_name, rel_fld.rdb$field_name,
rdb$field_type, rdb$field_sub_type,
rel_fld.rdb$null_flag, rdb$field_length, rdb$field_scale,
rdb$character_length, rdb$field_precision,
fld.rdb$default_source, fld.rdb$validation_source
FROM rdb$relations rel
JOIN rdb$relation_fields rel_fld
ON rel_fld.rdb$relation_name = rel.rdb$relation_name
JOIN rdb$fields fld
ON rel_fld.rdb$field_source = fld.rdb$field_name
WHERE rel.rdb$relation_name = : RelationName
ORDER BY rel_fld.rdb$field_position, rel_fld.rdb$field_name
| Note that - this request contains the raw information about the Table columns, but to
display the presence of UNIQUE, PRIMARY KEY or FOREIGN KEY constraints on some column, we have to query additional System
Tables, and this will be presented below.
2.4.1 - Indices Table Indices are linked: - to the rdb$relations Table
- to the rdb$segment Table where the columns used by the Index are defined
- to the rdb$relation_constraint Table to handle the foreign key constraints
Here is a picture of those relationships:
Indices are created - by explicit CREATE INDEX requests. Here are a couple of definitions:
CREATE INDEX custnamex
ON customer(customer);
CREATE INDEX custregion
ON customer(country, city);
CREATE DESCENDING INDEX budgetx
ON department(budget);
CREATE UNIQUE INDEX prodtypex
ON project(product, proj_name);
|
- implicitely by the system to handle
- UNIQUE constraints
- PRIMARY KEY constraints
- for FOREIGN KEY constraints
- ORDER BY clauses
For instance, here is the script which creates the DEPARTMENT Table of the EMPLOYEE database:
CREATE TABLE department
(
dept_no deptno NOT NULL,
department VARCHAR(25) NOT NULL,
head_dept deptno,
mngr_no empno,
budget budget,
location VARCHAR(15),
phone_no phonenumber DEFAULT '555-1234',
UNIQUE (department) ,
PRIMARY KEY (dept_no) ,
CONSTRAINT FOREIGN KEY (head_dept)
REFERENCES department (dept_no) ,
CONSTRAINT FOREIGN KEY (mngr_no)
REFERENCES employee (emp_no)
);
CREATE DESCENDING INDEX budgetx
ON department(budget);
|
and here is, from the rdb$indices Table, the rows about the DEPARTMENT Table:  and:
- the first row is the UNIQUE index on DEPARTMENT (unique flag)
- the second row is the PRIMARY KEY index (also unique)
- third and fifth are about FOREIGN KEY indices (with the fk constraint reference)
- the forth is our hand created index (with type=1 for descending)
The main columns of the rdb$indices Table are: Note that - this table contains also a rdb$statistics field, which is used to optimize the query access strategy
The main columns of the rdb$index_segments Table are:
To extract the Indices from a Table, you may use:
SELECT ix.rdb$relation_name, ix.rdb$index_name,
ix.rdb$unique_flag, ix.rdb$index_type
FROM rdb$indices ix
JOIN rdb$relations rel
ON ix.rdb$relation_name = rel.rdb$relation_name
WHERE
(rel.rdb$system_flag <> 1 OR rel.rdb$system_flag IS NULL)
AND
rel.rdb$relation_name = : RelationName
AND
NOT EXISTS
(SELECT *
FROM rdb$relation_constraints rel_con
WHERE rel_con.rdb$index_name = ix.rdb$index_name)
ORDER BY ix.rdb$relation_name, ix.rdb$index_name
| and to get the columns of some index:
SELECT *
FROM rdb$index_segments
WHERE rdb$index_name = : indexname
ORDER BY rdb$field_position
|
2.4.2 - Triggers The rdb$triggers manages the Triggers related to the Tables. Triggers are created - explicitely. For instance, to get a new key, we can use a Generator, and an associated Trigger which fires before insert:
CREATE TRIGGER set_cust_no
FOR customer
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.cust_no = gen_id(cust_no_gen, 1);
END
| - implicitely to handle check constraints. This will be examined below.
The main fields are:
and: - rdb$trigger_type encodes the before/after event as well as the insert/update/delete cause
To get the user Triggers (those not generated to handle check constraints), we
can use:
SELECT *
FROM rdb$triggers trig
JOIN rdb$relations rel
ON trig.rdb$relation_name = rel.rdb$relation_name
WHERE
rel.rdb$relation_name = : TableName
AND
(rel.rdb$system_flag <> 1 OR rel.rdb$system_flag IS NULL)
AND
NOT EXISTS
(SELECT *
FROM rdb$check_constraints chk_con
WHERE
trig.rdb$trigger_name = chk_con.rdb$trigger_name)
ORDER BY trig.rdb$relation_name, trig.rdb$trigger_type,
trig.rdb$trigger_sequence, trig.rdb$trigger_name
|
2.4.3 - Constraints We can impose 5 kind of constraints: - NOT NULL
- PRIMARY KEY
- UNIQUE
- FOREIGN KEY
- CHECK
The constraints can be set a the Domain level, while creating a Table, or
using specific constraint clauses or requests. The constraints are managed using three new System Tables: - rdb$relation_constraints
- rdb$check_constraints
- rdb$ref_constraints
To understand how this happens, lets look at tw examples.
First, here are snapshots for the DEPARTMENT Table: - the rdb$relation_constraints :
 - here are the entries in the rdb$check_constraints Table
 - the rdb$ref_constraints:
 - and here are the Indices:
And the snapshot for the CUSTOMER Table which has a CHECK constraint: - the creation requests are:
CREATE DOMAIN custno AS INTEGER
CHECK (VALUE > 1000);
CREATE DOMAIN firstname AS VARCHAR(15);
CREATE DOMAIN lastname AS VARCHAR(20);
CREATE DOMAIN phonenumber AS VARCHAR(20);
CREATE DOMAIN addressline AS VARCHAR(30);
CREATE DOMAIN countryname AS VARCHAR(15);
CREATE TABLE customer
(
cust_no custno NOT NULL,
customer VARCHAR(25) NOT NULL,
contact_first firstname,
contact_last lastname,
phone_no phonenumber,
ADDRESS_LINE1 addressline,
ADDRESS_LINE2 addressline,
city VARCHAR(25),
state_province VARCHAR(15),
country countryname,
postal_code VARCHAR(12),
on_hold CHAR(1) DEFAULT NULL,
PRIMARY KEY (cust_no)
);
CREATE INDEX custnamex ON customer(customer);
CREATE INDEX custregion ON customer(country, city);
ALTER TABLE customer
ADD FOREIGN KEY (country) REFERENCES country (country);
ALTER TABLE customer ADD
CHECK (on_hold IS NULL OR on_hold = '*');
| - the rdb$relation_constraints:
 - the rdb$check_constraints:
 - the rdb$ref_constraints:
 - the Indices:
 - the rdb$triggers:
Here is the relationship between those Tables: Note that - for the "customer custno NOT NULL" column we find
- in rdb$relations_constraints a row with "INTEG_57, NOT NULL" row
- in rdb$check_constraints a "INTEG_57, CUST_NO" row. However the "CUST_NO" appears in the rdb$trigger_name, but there is no such trigger
at all: this is the column name
- for the "PRIMARY KEY (cust_no)" constraint we find
- in rdb$relations_constraints a row with "INTEG_60, PRIMARY KEY, RDB$PRIMARY22" row
- in rdb$indices a "RDB$PRIMARY22, unique" row
- for the FOREIGN KEY constraint "FOREIGN KEY (country) REFERENCES country (country)" constraint
- in rdb$relations_constraints a row with "INTEG_61, FOREIGN KEY, RDB$FOREIGN23" row
- in rdb$ref_constraints an "INTEG_61, INTEG_2" row
- in rdb$indices a "RDB$FOREIGN23, RDB$PRIMARY1" entry, with a row on the COUNTRY Table
- for the CHECK constraint, "CHECK (on_hold IS NULL OR on_hold = '*')"
- in rdb$relations_constraints a row with "INTEG_59, CHECK" row
- in rdb$check_constraints two entries:
- "INTEG_59, CHECK_9"
- "INTEG_59, CHECK_10"
- in the rdb$triggers table, two triggers, one "BEFORE UPDATE" and the second "BEFORE INSERT"
- for the UNIQUE constraint (in the DEPARTMENT Table):
- in rdb$relations_constraints a row with "INTEG_15, UNIQUE, RDB$4"
- in the rdb$indices, a row "RDB4, unique" index
The 3 constraints tables main columns are:
- for rdb$relations_constraints:
- for the rdb$check_constraints:
- for the rdb$ref_constraints:
Here are some examples of querying the system tables about the different constraints:
2.5 - Views Here is a simple View from the EMPLOYEE database
CREATE view phone_list
(
emp_no, first_name, last_name, phone_ext,
location, phone_no
)
AS
SELECT emp_no, first_name, last_name, phone_ext,
location, phone_no
FROM employee, department
WHERE employee.dept_no = department.dept_no
| The schema of this View is specified in the rdb$relations System Table, with
rdb$view_blr<> NULL. Columns are specified in rdb$relation_fields. In order to list the Views, we can use:
SELECT rdb$relation_name
FROM rdb$relations
WHERE
(rdb$system_flag <> 1 OR rdb$system_flag IS NULL)
AND
NOT rdb$view_blr IS NULL
AND
rdb$flags = 1
|
The rdb$view_relations contains information for aliasing View column names
2.6 - Stored Procedure
Stored Procedures are managed by the rdb$procedures System Table, which contains - the rdb$procedure_name
- the rdb$procedure_source Blob contains the body of the Stored Procedure
Here are the main fields:
The input and output parameters are handled by the rdb$procedure_parameters.
This Table simply links the rdb$procedures Table and the rdb$Fields Table, with an additional rdb$parameter_type column to indicate whether the parameter is an input or output parameter
The Tables involved are:
As an example, the creation script of the GET_EMP_PROJ procedure is:
CREATE PROCEDURE get_emp_proj
(
emp_no SMALLINT
)
RETURNS
(
proj_id CHAR(5)
)
AS
BEGIN
FOR SELECT proj_id
FROM employee_project
WHERE emp_no = : emp_no
INTO : proj_id
DO
SUSPEND;
END
|
The parameter informations displayed by the ColIbExp utility are: 
Here is a request which lists all Stored Procedure names:
SELECT rdb$procedure_name
FROM rdb$procedures
| and to get information about the parameters we can use:
SELECT rdb$parameter_name, rdb$field_type, rdb$field_sub_type,
rdb$field_length, rdb$field_scale, rdb$field_precision,
rdb$character_length
FROM rdb$procedure_parameters sp_param
JOIN rdb$fields fld
ON sp_param.rdb$field_source = fld.rdb$field_name
WHERE
sp_param.rdb$procedure_name = : sp_name
|
2.7 - User Defined Functions (UDFs) UDF are managed by the rdb$functions and rdb$function_arguments Table, in a similar fashion as the Stored Procedures
2.8 - Generators
The rdb$generators table manages the Generators. The main columns are: Note that - the current value of the generator is not contained in this table, but
managed directly by the System
2.9 - Security The USERs are managed by the separate Server wide ISC4.GDB database. For each database, the rdb$role and rdb$user_privilege are used to handle
Roles and Grants. To define Roles, we use rdb$roles:
Grants are handled by rdb$user_privileges: and
- rdb$grantor: the User who gave the Grant
- rdb$user tells which User receives the Grant (User, Role, Procedure, Trigger, View)
- rdb$relation_name tells which object was involved in the Grant (Table,
View, Procedure, Role)
- rdb$privilege : the kind of Grant
Here is a Grant request
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES
ON country
TO public
WITH GRANT option;
|
and here are the corresponding rdb$user_privileges Table rows: 
3 - Comments As usual:
- please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will
be helpful for other readers
- we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
- or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button
- and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in
your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.
4 - References
- The System Tables are described in the InterBase Language Reference Manual, (LANGREF) Chapter 7 - System Tables and
Views. This link is an IBPhoenix link for the InterBase 6 manual, but if this link does not work, Google will easily find other links for this manual.
5 - The author
Felix John COLIBRI works at the Pascal Institute. Starting with Pascal in 1979, he then became involved with Object Oriented Programming, Delphi, Sql, Tcp/Ip, Html, UML. Currently, he is mainly
active in the area of custom software development (new projects, maintenance, audits, BDE migration, Delphi
Xe_n migrations, refactoring), Delphi Consulting and Delph
training. His web site features tutorials, technical papers about programming with full downloadable source code, and the description and calendar of forthcoming Delphi, FireBird, Tcp/IP, Web Services, OOP / UML, Design Patterns, Unit Testing training sessions.
|
