Las tablas de sistema de Firebird contienen todas las informaciones sobre las tablas de usuario. Como estos datos son a veces un poco críptico, conviene reunir los datos de interés en vistas propias. Muchos comandos SQL útiles para extraer información de las tablas de sistemas se encuentran en el artículo “Extracting META information from Interbase/Firebird SQL […]

Obtener información de tablas

Con esta vista puedes obtener información sobre las columnas de las tablas y sus tipos. Esta información puede ser útil a la hora de comprobar si un dato a guardar en la base de datos está en un formato correcto – por ejemplo que un campo de texto no excede la longitud máxima.

CREATE VIEW V_TABLES (TABLE_NAME, FIELD_NAME, FIELD_DESCRIPTION, FIELD_DEFAULT_VALUE, FIELD_NOT_NULL_CONSTRAINT, FIELD_LENGTH, FIELD_PRECISION, FIELD_SCALE, FIELD_TYPE, FIELD_SUBTYPE, FIELD_COLLATION, FIELD_CHARSET)AS 
SELECT  r.RDB$RELATION_NAME as table_name, 
        r.RDB$FIELD_POSITION as field_position, 
        r.RDB$FIELD_NAME AS field_name, 
        r.RDB$DESCRIPTION AS field_description, 
        r.RDB$DEFAULT_VALUE AS field_default_value, 
        r.RDB$NULL_FLAG AS field_not_null_constraint, 
        f.RDB$FIELD_LENGTH AS field_length, 
        f.RDB$FIELD_PRECISION AS field_precision, 
        f.RDB$FIELD_SCALE AS field_scale, 
        CASE f.RDB$FIELD_TYPE 
          WHEN 261 THEN 'BLOB' 
          WHEN 14 THEN 'CHAR' 
          WHEN 40 THEN 'CSTRING' 
          WHEN 11 THEN 'D_FLOAT' 
          WHEN 27 THEN 'DOUBLE' 
          WHEN 10 THEN 'FLOAT' 
          WHEN 16 THEN 'INT64' 
          WHEN 8 THEN 'INTEGER' 
          WHEN 9 THEN 'QUAD' 
          WHEN 7 THEN 'SMALLINT' 
          WHEN 12 THEN 'DATE' 
          WHEN 13 THEN 'TIME' 
          WHEN 35 THEN 'TIMESTAMP' 
          WHEN 37 THEN 'VARCHAR' 
          ELSE 'UNKNOWN' 
        END AS field_type, 
        f.RDB$FIELD_SUB_TYPE AS field_subtype, 
        coll.RDB$COLLATION_NAME AS field_collation, 
        cset.RDB$CHARACTER_SET_NAME AS field_charset 
   FROM RDB$RELATION_FIELDS r 
   LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME 
   LEFT JOIN RDB$COLLATIONS coll ON r.RDB$COLLATION_ID = coll.RDB$COLLATION_ID 
    AND f.RDB$CHARACTER_SET_ID = coll.RDB$CHARACTER_SET_ID 
   LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID 
ORDER BY r.RDB$RELATION_NAME, r.RDB$FIELD_POSITION;

Información sobre claves externas

Con la vista siguiente puedes extraer información sobre claves externas (foreign keys).

CREATE VIEW V_FOREIGN_KEYS (TABLE_NAME, FIELD_NAME, REFERENCED_TABLE_NAME, REFERENCED_FIELD_NAME, ON_UPDATE, ON_DELETE)AS       
  SELECT DISTINCT  
--          rc.RDB$CONSTRAINT_NAME AS constraint_name,  
          rc.RDB$RELATION_NAME AS table_name,  
          d1.RDB$FIELD_NAME AS field_name,  
          d2.RDB$DEPENDED_ON_NAME AS referenced_table_name,  
          d2.RDB$FIELD_NAME AS referenced_field_name,  
          refc.RDB$UPDATE_RULE AS on_update,  
          refc.RDB$DELETE_RULE AS on_delete  
     FROM RDB$RELATION_CONSTRAINTS AS rc  
LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME  
LEFT JOIN RDB$DEPENDENCIES d1 ON d1.RDB$DEPENDED_ON_NAME = rc.RDB$RELATION_NAME  
LEFT JOIN RDB$DEPENDENCIES d2 ON d1.RDB$DEPENDENT_NAME = d2.RDB$DEPENDENT_NAME  
    WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'  
      AND d1.RDB$DEPENDED_ON_NAME <> d2.RDB$DEPENDED_ON_NAME  
      AND d1.RDB$FIELD_NAME <> d2.RDB$FIELD_NAME  
;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_FOREIGN_KEYS TO SYSDBA WITH GRANT OPTION;
UPDATE RDB$RELATION_FIELDS set
  RDB$DESCRIPTION = 'Table that uses the foreign key'
  where RDB$FIELD_NAME = 'TABLE_NAME' AND RDB$RELATION_NAME = 'V_FOREIGN_KEYS';
UPDATE RDB$RELATION_FIELDS set
  RDB$DESCRIPTION = 'Field whose values are restricted to the referenced field'
  where RDB$FIELD_NAME = 'FIELD_NAME' AND RDB$RELATION_NAME = 'V_FOREIGN_KEYS';
UPDATE RDB$RELATION_FIELDS set
  RDB$DESCRIPTION = 'The table which is referenced by the foreign key'
  where RDB$FIELD_NAME = 'REFERENCED_TABLE_NAME' AND RDB$RELATION_NAME = 'V_FOREIGN_KEYS';
UPDATE RDB$RELATION_FIELDS set
  RDB$DESCRIPTION = 'The field whose values are the permitted ones for the target field'
  where RDB$FIELD_NAME = 'REFERENCED_FIELD_NAME' AND RDB$RELATION_NAME = 'V_FOREIGN_KEYS';
UPDATE RDB$RELATION_FIELDS set
  RDB$DESCRIPTION = 'What to do when the referenced field value is updated'
  where RDB$FIELD_NAME = 'ON_UPDATE' AND RDB$RELATION_NAME = 'V_FOREIGN_KEYS';
UPDATE RDB$RELATION_FIELDS set
  RDB$DESCRIPTION = 'What to do when the referenced field value is deleted'
  where RDB$FIELD_NAME = 'ON_DELETE' AND RDB$RELATION_NAME = 'V_FOREIGN_KEYS';

Copiar comentarios de columnas de una tabla a una vista

Si creo una vista, entonces me gustaría tener los mismos comentarios en la vista que en las columnas correspondientes de las tablas a partir de las cuales creo la vista. Por supuesto, esto es un problema trivial para aquellos que nunca ponen comentarios en ningún sitio, pero para los demás, el siguiente comando podría ser de utilidad.

UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 
       (select RDB$DESCRIPTION from RDB$RELATION_FIELDS 
         where RDB$RELATION_NAME = 'SOURCE_TABLE'
           and RDB$FIELD_NAME = 'SOURCE_COLUMN')
  where RDB$FIELD_NAME = 'TARGET_FIELD' 
    AND RDB$RELATION_NAME = 'TARGET_NAME';

El select interior lee de la tabla de la cual queremos obtener los comentarios. Podemos sustituir el paréntesis por un string si queremos asignar un comentario a medida. SOURCE_TABLE y SOURCE_COLUMN se refieren a la tabla de donde quiero copiar los comentarios y TARGET_NAME y TARGET_FIELD a la vista a donde los quiero copiar. Los nombres de las tablas, vistas y columnas deben estar en mayúsculas. De otra forma la comparación = no funcionará.

Por supuesto, antes de usar este comando SQL hay que crear la tabla y la vista a que se refieren. La herramienta de extracción de DDL (Data Definition Language) no guarda referencias a comentarios de otras tablas. Sólo presenta el comentario actualmente asignado. Por eso, conviene guardarse un fichero SQL con los comandos de copia de comentarios junto con la creación de la vista, ya que se puede ejecutar cada vez se cambia la vista.

Referencias