The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,680 other followers

InterBase/Firebird – query to show which fields in your database are not based on a domain

Posted by jpluimers on 2009/08/13

Our strategy is that in all of our InterBase and Firebird databases, the column definitions are based on domains.

But some of our databases do not have that for all columns (usually because we got involved later in the development cycle).

When you forget to define a column based on a domain, then both InterBase and Firebird will create a new system domain on the fly that starts with ‘RDB$’.
So if you forget, you get a new system domain for every column!

The below query will list those columns.

select
    rf.rdb$relation_name, rf.rdb$field_name,
    rf.rdb$field_source,
    f.rdb$field_type, t.rdb$type_name, f.rdb$field_sub_type,
    f.rdb$character_length, f.rdb$field_scale,
    f.rdb$field_length,
    st.rdb$type_name as rdb$sub_type_name,
    case f.rdb$field_type
        when 7 then 'smallint'
        when 8 then 'integer'
        when 16 then 'int64'
        when 9 then 'quad'
        when 10 then 'float'
        when 11 then 'd_float'
        when 17 then 'boolean'
        when 27 then 'double'
        when 12 then 'date'
        when 13 then 'time'
        when 35 then 'timestamp'
        when 261 then 'blob'
        when 37 then 'varchar'
        when 14 then 'char'
        when 40 then 'cstring'
        when 45 then 'blob_id'
    end as "ActualType",
    case f.rdb$field_type
        when 7 then
        case f.rdb$field_sub_type
            when 1 then 'numeric'
            when 2 then 'decimal'
        end
        when 8 then
        case f.rdb$field_sub_type
            when 1 then 'numeric'
            when 2 then 'decimal'
        end
        when 16 then
        case f.rdb$field_sub_type
            when 1 then 'numeric'
            when 2 then 'decimal'
            else 'bigint'
        end
        when 14 then
        case f.rdb$field_sub_type
            when 0 then 'unspecified'
            when 1 then 'binary'
            when 3 then 'acl'
            else
            case
                when f.rdb$field_sub_type is null then 'unspecified'
            end
        end
        when 37 then
        case f.rdb$field_sub_type
            when 0 then 'unspecified'
            when 1 then 'text'
            when 3 then 'acl'
            else
            case
                when f.rdb$field_sub_type is null then 'unspecified'
            end
        end
        when 261 then
        case f.rdb$field_sub_type
            when 0 then 'unspecified'
            when 1 then 'text'
            when 2 then 'blr'
            when 3 then 'acl'
            when 4 then 'reserved'
            when 5 then 'encoded-meta-data'
            when 6 then 'irregular-finished-multi-db-tx'
            when 7 then 'transactional_description'
            when 8 then 'external_file_description'
        end
    end as "ActualSubType"
from rdb$relation_fields rf
join rdb$fields f
    on f.rdb$field_name = rf.rdb$field_source
left join rdb$types t
    on t.rdb$type = f.rdb$field_type
    and t.rdb$field_name = 'RDB$FIELD_TYPE'
left join rdb$types st
    on st.rdb$type = f.rdb$field_sub_type
    and st.rdb$field_name = 'RDB$FIELD_SUB_TYPE'
where
    rf.rdb$system_flag = 0
    and rf.rdb$field_source like 'RDB$%'
order by
    rf.rdb$relation_name,
    rf.rdb$field_position

This query is based on some postings from Felix Colibri and Lorenzo Alberton and input from my colleague Edwin van der Kraan. Thanks guys!

The EMPLOYEE sample database that ships with both Firebird and InterBase, is partially based on domains, so it is a good DB to show what results this query will get you.
When running the query on the EMPLOYEE database, you will see something like the table below.

The RDB$FIELD_SOURCE column contains the system generated domains (that all start with ‘RDB$’).
The ActualType and ActualSubType columns will help you defining the correct domains.

If you change the ordering to be f.rdb$field_type, t.rdb$type_name, f.rdb$field_sub_type, f.rdb$field_length, then you get similar domains ordered together.

Note that you do not always get a rdb$type_name value, you can see it in the below table for RDB$31 domain (table SALES, column AGED) which is a calculated field.
You could filter away those by joining on the rdb$relation_fields table, and check for the rdb$update_flag to be non zero.

RDB$RELATION_NAME RDB$FIELD_NAME  RDB$FIELD_SOURCE RDB$FIELD_TYPE RDB$TYPE_NAME RDB$FIELD_SUB_TYPE RDB$CHARACTER_LENGTH RDB$FIELD_SCALE RDB$FIELD_LENGTH RDB$SUB_TYPE_NAME ActualType ActualSubType
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COUNTRY           CURRENCY        RDB$1            37             VARYING       0                  10                   0               10                                 varchar    unspecified
CUSTOMER          CUSTOMER        RDB$18           37             VARYING       0                  25                   0               25                                 varchar    unspecified
CUSTOMER          CITY            RDB$19           37             VARYING       0                  25                   0               25                                 varchar    unspecified
CUSTOMER          STATE_PROVINCE  RDB$20           37             VARYING       0                  15                   0               15                                 varchar    unspecified
CUSTOMER          POSTAL_CODE     RDB$21           37             VARYING       0                  12                   0               12                                 varchar    unspecified
CUSTOMER          ON_HOLD         RDB$22           14             TEXT          0                  1                    0               1                                  char       unspecified
DEPARTMENT        DEPARTMENT      RDB$5            37             VARYING       0                  25                   0               25                                 varchar    unspecified
DEPARTMENT        LOCATION        RDB$6            37             VARYING       0                  15                   0               15                                 varchar    unspecified
EMPLOYEE          PHONE_EXT       RDB$7            37             VARYING       0                  4                    0               4                                  varchar    unspecified
EMPLOYEE          HIRE_DATE       RDB$8            35             TIMESTAMP     0                                       0               8                                  timestamp
EMPLOYEE          FULL_NAME       RDB$9            37             VARYING       0                  0                    0               37                                 varchar    unspecified
JOB               JOB_TITLE       RDB$2            37             VARYING       0                  25                   0               25                                 varchar    unspecified
JOB               JOB_REQUIREMENT RDB$3            261            BLOB          1                                       0               8                TEXT              blob       text
JOB               LANGUAGE_REQ    RDB$4            37             VARYING       0                  15                   0               15                                 varchar    unspecified
PHONE_LIST        PHONE_EXT       RDB$7            37             VARYING       0                  4                    0               4                                  varchar    unspecified
PHONE_LIST        LOCATION        RDB$6            37             VARYING       0                  15                   0               15                                 varchar    unspecified
PROJECT           PROJ_NAME       RDB$10           37             VARYING       0                  20                   0               20                                 varchar    unspecified
PROJECT           PROJ_DESC       RDB$11           261            BLOB          1                                       0               8                TEXT              blob       text
PROJ_DEPT_BUDGET  FISCAL_YEAR     RDB$12           8              LONG          0                                       0               4                                  integer
PROJ_DEPT_BUDGET  QUART_HEAD_CNT  RDB$13           8              LONG          0                                       0               4                                  integer
SALARY_HISTORY    CHANGE_DATE     RDB$14           35             TIMESTAMP     0                                       0               8                                  timestamp
SALARY_HISTORY    UPDATER_ID      RDB$15           37             VARYING       0                  20                   0               20                                 varchar    unspecified
SALARY_HISTORY    PERCENT_CHANGE  RDB$16           27             DOUBLE        0                                       0               8                                  double
SALARY_HISTORY    NEW_SALARY      RDB$17           27             DOUBLE        0                                       0               8                                  double
SALES             ORDER_STATUS    RDB$23           37             VARYING       0                  7                    0               7                                  varchar    unspecified
SALES             ORDER_DATE      RDB$24           35             TIMESTAMP     0                                       0               8                                  timestamp
SALES             SHIP_DATE       RDB$25           35             TIMESTAMP     0                                       0               8                                  timestamp
SALES             DATE_NEEDED     RDB$26           35             TIMESTAMP     0                                       0               8                                  timestamp
SALES             PAID            RDB$27           14             TEXT          0                  1                    0               1                                  char       unspecified
SALES             QTY_ORDERED     RDB$28           8              LONG          0                                       0               4                                  integer
SALES             TOTAL_VALUE     RDB$29           8              LONG          2                                       -2              4                BLR               integer    decimal
SALES             DISCOUNT        RDB$30           10             FLOAT         0                                       0               4                                  float
SALES             AGED            RDB$31           16                           0                                       -9              8                                  int64      bigint

This helped us a lot when cleaning up a couple of databases.

–jeroen

4 Responses to “InterBase/Firebird – query to show which fields in your database are not based on a domain”

  1. Please help!!
    He tells me this error when I run the script in python:
    File ” firebirdpython.py ” , line 142, in < module>
    cursor.execute ( cadenaSQL2 )
    kinterbasdb.ProgrammingError : ( -104 , ‘ isc_dsql_prepare : \ n Dynamic SQL Error \ n
    SQL code = -104 \ n Error Token unknown – line 10 , column 7 \ n When ‘ )

    Script firebirdpython.py:

    – coding: cp1252 –

    import kinterbasdb
    import os
    kinterbasdb.init(type_conv=200)

    Fichero de creación de tablas y adición de campos.

    fichero1 = ‘crear_tablas.sql’
    fichero2 = ‘add_campos.sql’

    Ruta de conexión.

    rutaAcceso = ‘localhost:C:\file\dba.GDB’

    Intentamos conectar con FireBird.ISO8859_1

    conectado = True
    conexion = True

    try:
    conexion= kinterbasdb.connect(dsn=rutaAcceso,user=’SYSDBA’, password=’masterkey’,charset=’UNICODE_FSS’,dialect=1)

    print “Conectado!”
    except:
    print “No he podido conectar con “, rutaAcceso

    Estamos conectados, a trabajar!

    if conectado:
    # Obtenemos todas las tablas.
    cadenaSQL = ”’
    select rdb$relation_name
    from rdb$relations
    where rdb$view_blr is null
    and (rdb$system_flag is null or rdb$system_flag = 0);
    ”’
    # Inicializamos cursor.
    cursor = conexion.cursor()
    print “Recuperando nombres de tablas….!”
    # Ejecutamos cursor.
    cursor.execute(cadenaSQL)
    # Guardamos los nombres de las tablas en una lista.
    tablas = []
    for fila in cursor:
    tablas.append(fila[0])
    print “OK!”
    # Cadena que obtiene tablas con campos y tipos de datos.
    cadenaSQL2 = ”’
    select
    rf.rdb$relation_name, rf.rdb$field_name,
    rf.rdb$field_source,
    f.rdb$field_type, t.rdb$type_name, f.rdb$field_sub_type,
    f.rdb$character_length, f.rdb$field_scale,
    f.rdb$field_length,
    st.rdb$type_name as rdb$sub_type_name,
    case f.rdb$field_type
    when 7 then ‘smallint’
    when 8 then ‘integer’
    when 16 then ‘int64’
    when 9 then ‘quad’
    when 10 then ‘float’
    when 11 then ‘d_float’
    when 17 then ‘boolean’
    when 27 then ‘double’
    when 12 then ‘date’
    when 13 then ‘time’
    when 35 then ‘timestamp’
    when 261 then ‘blob’
    when 37 then ‘varchar’
    when 14 then ‘char’
    when 40 then ‘cstring’
    when 45 then ‘blob_id’
    end as “ActualType”,
    case f.rdb$field_type
    when 7 then
    case f.rdb$field_sub_type
    when 1 then ‘numeric’
    when 2 then ‘decimal’
    end
    when 8 then
    case f.rdb$field_sub_type
    when 1 then ‘numeric’
    when 2 then ‘decimal’
    end
    when 16 then
    case f.rdb$field_sub_type
    when 1 then ‘numeric’
    when 2 then ‘decimal’
    else ‘bigint’
    end
    when 14 then
    case f.rdb$field_sub_type
    when 0 then ‘unspecified’
    when 1 then ‘binary’
    when 3 then ‘acl’
    else
    case
    when f.rdb$field_sub_type is null then ‘unspecified’
    end
    end
    when 37 then
    case f.rdb$field_sub_type
    when 0 then ‘unspecified’
    when 1 then ‘text’
    when 3 then ‘acl’
    else
    case
    when f.rdb$field_sub_type is null then ‘unspecified’
    end
    end
    when 261 then
    case f.rdb$field_sub_type
    when 0 then ‘unspecified’
    when 1 then ‘text’
    when 2 then ‘blr’
    when 3 then ‘acl’
    when 4 then ‘reserved’
    when 5 then ‘encoded-meta-data’
    when 6 then ‘irregular-finished-multi-db-tx’
    when 7 then ‘transactional_description’
    when 8 then ‘external_file_description’
    end
    end as “ActualSubType”
    from rdb$relation_fields rf
    join rdb$fields f
    on f.rdb$field_name = rf.rdb$field_source
    left join rdb$types t
    on t.rdb$type = f.rdb$field_type
    and t.rdb$field_name = ‘RDB$FIELD_TYPE’
    left join rdb$types st
    on st.rdb$type = f.rdb$field_sub_type
    and st.rdb$field_name = ‘RDB$FIELD_SUB_TYPE’
    where
    rf.rdb$system_flag = 0
    and rf.rdb$field_source like ‘RDB$%’
    order by
    rf.rdb$relation_name,
    rf.rdb$field_position;
    ”’
    # Inicializamos cursor.
    cursor.close()
    cursor = conexion.cursor()
    # Info.
    print “Recuperando información de campos de tablas!”
    # Ejecutamos cursor.
    cursor.execute(cadenaSQL2)
    # Guardamos los nombres de las tablas en una lista.
    campos_tablas = []
    # Posiciones: 0 nombre tabla, 1 nombre campo, 2 long. campo, 10 tipo dato.
    for fila in cursor:
    campos_tablas.append((fila[0],fila[1],fila[8],fila[10]))
    # Info.
    print “OK!”
    print “Guardando información en ficheros SQL!”
    # Creamos fichero.
    f = open(os.path.realpath(fichero1), “w”)
    # Creamos los create table.
    for tabla in tablas:
    cadenaSQL = ‘CREATE TABLE ‘ + str(tabla).strip() +’ ( campo_testigo char(1) ) ;’
    f.write(cadenaSQL+”\n”)
    # Cerramos fichero.
    f.close()
    # Creamos fichero.
    f2 = open(os.path.realpath(fichero2), “w”)
    # Creamos alter table.
    for campo in campos_tablas:
    if str(campo[3]).strip() == ‘datetime’ or \
    str(campo[3]).strip() == ‘timestamp’ or \
    str(campo[3]).strip() == ‘double’ or \
    str(campo[3]).strip() == ‘time’:
    cadenaSQL = ‘ALTER TABLE ‘ + str(campo[0]).strip() + ‘ ADD ‘ + str(campo[1]).strip() + \
    ‘ ‘ + str(campo[3]).strip() + ‘ ;’
    else:
    cadenaSQL = ‘ALTER TABLE ‘ + str(campo[0]).strip() + ‘ ADD ‘ + str(campo[1]).strip() + \
    ‘ ‘ + str(campo[3]).strip() + ‘(‘ + str(campo[2]).strip()+ ‘) ;’
    f2.write(cadenaSQL+”\n”)
    # Cerramos fichero.
    f2.close()
    # Info.
    print “Terminada exportación de datos!”
    # Cerramos conexiones de cursor y base de datos.
    print “Cerrando conexiones!”
    cursor.close()
    conexion.close()

    • jpluimers said

      I don’t do Python yet. Please ask this on StackOverflow.

      • I realize that the problem is in the metadata “f.rdb$field_type” , but still could not know that the problem, I think I’ll reformulate a firebird / interbase query again to obtain the data from the ranks and their respective data.

  2. […] below query will list those columns. Permalink Leave your comment […]

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: