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
The Wiert Corner - irregular stream of stuff 
Sergio Dario said
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.
Sergio Dario said
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.
Firebird News » Firebird – query to show which fields in your database are not based on a domain said
[…] below query will list those columns. Permalink Leave your comment […]