Aplicación para crear un archivo de equivalencias entre restricciones de una tabla Interbase/Firebird y mensajes, para mostrar en aplicaciones clientes cuando se violan las restricciones

En un artículo anterior creamos una serie de funciones para tratar los errores reportados por Interbase de manera especial en nuestras aplicaciones. Vimos ahí como hacer para determinar cuál fue la restricción violada (Check, Foreign Key, Primary Key, etc) y mostrar un mensaje propio al usuario. Estos mensajes se toman de un archivo INI.

El archivo INI de equivalencias (que llamaré diccionario) tiene el siguiente formato:

[Constraints]
Nombre_restriccion1=Mensaje_a_mostrar_en_el_cliente
Nombre_restriccion2=Mensaje_a_mostrar_en_el_cliente
…

es decir: una sola sección, llamada 'Constraints' con todas las restricciones y los mensajes asociados, si los hay. En el método de reporte de errores del artículo anterior, si no se encuentra el mensaje entonces se usa el estándar de Interbase acompañado por los códigos de error SQL y del servidor.

Si bien este archivo INI se puede escribir y mantener usando cualquier editor –una ventaja, ciertamente- es bastante engorroso recuperar los nombres de las restricciones de la Base de Datos, ya que esta información se encuentra en las tablas de sistema… y no es un lugar muy conocido ¿verdad?. Esta aplicación se encarga de buscar esta información, presentarla en una forma amena, y permitirnos asociar mensajes propios a cada una –incluso tiene una función para generarlos en forma automática. Como resultado obtenemos el diccionario actualizado, listo para usar.

La aplicación terminada tiene el siguiente aspecto en el IDE de Delphi:

La operatoria sería la siguiente:

• Se escribe la ruta del archivo de base de datos en el editor superior
• Se ingresan el nombre de usuario y la contraseña de administrador (un usuario que tenga permisos para leer las tablas de sistema)
• Se ingresa el nombre del archivo INI a crear/actualizar. El programa propone 'Diccio.ini', en la misma ruta que la Base de Datos, al conectar.
• Se presiona 'Conectar' para abrir la base de datos y leer la información de las restricciones en la lista.
• A partir de aquí, para cambiar un mensaje sólo hay que posicionarse sobre la restricción y escribir el mensaje en el memo inferior; al seleccionar otra línea se guardarán los cambios en la lista.
• Para guardar los mensajes en el archivo INI, presionamos el botón de Guardar.

Eventualmente se puede pedir al programa que genere mensajes tipo para todas las restricciones o solamente la seleccionada, con los botones inferiores.

¿Entendido? Entonces, comencemos la diversión.

Conexión a la Base de Datos

La conexión se hace a través del componente IBDatabase llamado 'db'. El nombre de la base de datos, el usuario y la contraseña se asignan en forma dinámica al presionar el botón 'Conectar':

procedure TForm1.actConectarExecute(Sender: TObject);
begin
  try
    Screen.Cursor := crSQLWait;
    if txn.InTransaction then txn.Rollback;
    with db do begin
      Close;
      DatabaseName := edBD.Text;
      Params.Clear;
      Params.Add('user_name=' + edUsuario.Text);
      Params.Add('password=' + edPassw.Text);
      Open;
    end;
    txn.StartTransaction;
    edDiccio.Text := ExtractFilePath(edBD.Text) + ExtractFileName(edDiccio.Text);
    LeerIni;
    LeerConstraints;
    lvConstraints.Enabled := True;
    GroupBox1.Enabled := True;
  finally
    Screen.Cursor := crDefault;
  end;
end;

Dado que este programa no modifica la base de datos, sólo la consulta, la transacción siempre se termina con Rollback. Además de la comprobación previa a la lectura, en el componente IBTransaction –llamado 'TXN'- he puesto la propiedad AutoStopAction a saRollback, para que automáticamente al final haga un Rollback antes de cerrar la transacción. La transacción es de tipo Snapshot para evitar problemas por utilización concurrente.

En el procedimiento de conexión, después de poner los parámetros y el nombre de la BD, proponemos el nombre para el archivo INI como 'DICCIO.INI' en el mismo directorio que la Base de Datos. A continuación leemos el archivo INI si es que existe, y ponemos la información de las restricciones y los mensajes existentes en el control ListView llamado lvConstraints.

El resto del procedimiento es cosmética: activar controles, cambiar el cursor, cosas así.

Lectura del archivo INI

Para disponer fácilmente de los mensajes, la sección completa del archivo INI es leída en una lista de strings (una instancia de la clase TStringList), usando el método ReadSectionValues de la clase TIniFile. Una vez leídos, podemos acceder a los mensajes con la propiedad Values de TStringList que devuelve la parte que sigue al signo igual de una línea.

La lista solamente se utiliza como un paso intermedio, porque además de almacenar la sección completa del INI es capaz de buscar un identificador (la cadena que está antes del '=' en las líneas del INI) y devolver el valor correspondiente (la parte que sigue al '=') simplemente con

Lista.Values[cadenaID]

La lectura propiamente dicha se efectúa en un procedimiento separado sólo por claridad; siempre será llamada como un paso previo a LeerConstraints, desde el procedimiento LeerTodo. Una vez colocados los datos en el ListView ya no se necesita la lista de strings por lo que es destruida.

procedure TForm1.LeerTodo;
var
  ts: TStringList;
begin
  ts := TStringList.Create;
  LeerIni(ts);
  LeerConstraints(ts);
  ts.Free;
end;

procedure TForm1.LeerIni(Lista: TStrings);
var
  i: TIniFile;
begin
  i := TIniFile.Create(edDiccio.text);
  i.ReadSectionValues(secConstraints, Lista);
  i.Free;
end;

Lectura de las restricciones

Y llegamos a la parte 'jugosa' de la aplicación: la lectura de las características de las restricciones desde las tablas de sistema de la Base de Datos.

Los tipos de restricción y los momentos en que se reportan los errores son los siguientes:

Las información sobre estas restricciones se encuentra en la tabla RDB$RELATION_CONSTRAINTS, que tiene la siguiente estructura:

Esta es la tabla 'maestra', con los datos principales. La información se completa con otras tablas, dependiendo del tipo de restricción:

Notemos que en el caso de una restricción de Integridad Referencial (FOREIGN KEY) tenemos dos tablas auxiliares, ya que la restricción relaciona dos tablas o, internamente, dos índices.

En principio no nos interesa tanto detalle; vamos a armar una lista con

• Nombre de la restricción
• Tipo
• Tabla en la que se define
• Campo sobre el que se define, en el caso de NOT NULL
• Expresión a verificar, en el caso de CHECK
• Nombre del índice, en los tipos PRIMARY KEY, UNIQUE y FOREIGN KEY
• Mensaje propio a mostrar

Los primeros seis puntos se pueden obtener con una sola sentencia SELECT, combinando las tablas mencionadas anteriormente. El mensaje a mostrar (si existe uno) se encuentra en el archivo INI; ya hemos visto como se lee éste.

La sentencia SQL principal tiene el siguiente aspecto:

SELECT RC.RDB$CONSTRAINT_NAME as Nombre,
  RC.RDB$CONSTRAINT_TYPE as Tipo,
  RC.RDB$RELATION_NAME as Tabla,
  CC.RDB$TRIGGER_NAME as Campo,
  RC.RDB$INDEX_NAME as Indice,
  T.RDB$TRIGGER_SOURCE as Definicion
FROM RDB$CHECK_CONSTRAINTS CC
  RIGHT JOIN RDB$RELATION_CONSTRAINTS RC
    ON CC.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME
  LEFT JOIN RDB$TRIGGERS T
    ON CC.RDB$TRIGGER_NAME = T.RDB$TRIGGER_NAME
ORDER BY RC.RDB$CONSTRAINT_TYPE, RC.RDB$CONSTRAINT_NAME

Es necesario el uso de combinaciones externas para lograr que aparezcan en el resultado todos los tipos de restricciones.

Casi todos los datos que deseamos mostrar en la lista están en la tabla RDB$RELATION_CONSTRAINTS; la excepción son los tipos CHECK y NOT NULL que necesitan datos de tablas auxiliares.

Las restricciones tipo CHECK y NOT NULL utilizan la tabla RDB$CHECK_CONSTRAINTS:

Vemos que el segundo campo tiene dos significados distintos. En el caso de la restricción NOT NULL, simplemente es el nombre del campo a verificar. En el caso de CHECK, este campo contiene el nombre de un trigger. Este trigger es generado internamente para implementar la verificación de la expresión.

En realidad se generan dos triggers por cada restricción CHECK, uno para verificar los datos al insertar (before insert) y el otro al modificar (before update). En la tabla RDB$CHECK_CONSTRAINTS tendremos entonces dos registros por cada CHECK, con el mismo valor en RDB$CONSTRAINT_NAME y distinto valor en RDB$TRIGGER_NAME.

Por ejemplo, en la Base de Datos Employee.gdb de los programas de demostración que trae Delphi se define una restricción CHECK llamada INTEG_12, de la siguiente manera:

CHECK (min_salary < max_salary)

En la tabla RDB$CHECK_CONSTRAINTS veremos entonces dos registros

Para obtener la definición de la expresión debemos investigar los triggers, en la tabla RDB$TRIGGERS:

Lo único que nos interesa mostrar es la definición de la expresión del CHECK: la columna RDB$TRIGGER_SOURCE, un memo.

Combinando estas tres tablas obtenemos todos los datos que queremos mostrar en el ListView. El procedimiento LeerConstraints se encarga de eso:

procedure TForm1.LeerConstraints(Lista: TStrings);
var
  s: string;
begin
  try
    Screen.Cursor := crHourGlass;
    lvConstraints.items.clear;
    Q1.Open;
    s := '';
    while not Q1.Eof do
    begin
      if s <> Q1.FieldByName('Nombre').AsString then
      begin
        s := Q1.FieldByName('Nombre').AsString;
        with lvConstraints.Items.Add do
        begin
          Caption := Trim(s);
          SubItems.Add(Trim(Q1.FieldByName('Tipo').AsString));
          SubItems.Add(Trim(Q1.FieldByName('Tabla').AsString));
          if SubItems[iTipo] = cCheck then
            SubItems.Add('')
          else
            SubItems.Add(Trim(Q1.FieldByName('Campo').AsString));
          SubItems.Add(Trim(Lista.Values[Caption]));
          SubItems.Add(Trim(Q1.FieldByName('Definicion').AsString));
          SubItems.Add(Trim(Q1.FieldByName('Indice').AsString));
        end; //with
      end; //if
      Q1.Next;
    end; //while
    Q1.Close;
  finally
    Screen.Cursor := crDefault;
  end;
end;

Note que se agrega aquí la columna de Mensaje, leyendo directamente de la lista de strings pasada como parámetro.

El orden en que se agregan los subitems tiene importancia; debe coincidir con el orden de las columnas en el ListView, y también con las constantes que definimos para identificarlas:

const
  iTipo       = 0;
  iTabla      = 1;
  iCampo      = 2;
  iMensaje    = 3;
  iDefinicion = 4;
  iIndice     = 5;

Mostrar el detalle de la restricción y el mensaje asociado; almacenar el nuevo mensaje si corresponde

Cuando seleccionamos una restricción en la lista superior, se muestra en el componente memo intermedio un detalle de la misma. Veamos cada caso por separado.

• NOT NULL: se muestra el nombre de la tabla y el nombre del campo. Ambos datos están en el ListView.
• CHECK: se muestra el nombre de la tabla y la expresión; ambos datos están en el ListView.
• PRIMARY KEY: se muestra el nombre de la tabla, el nombre del índice (en el ListView) y los campos que componen el índice (hay que buscarlos en RDB$INDEX_SEGMENTS)
• UNIQUE: igual que para PRIMARY KEY
• FOREIGN KEY: la primera parte igual que PRIMARY KEY; agrega datos sobre el índice referenciado (RDB$REF_CONSTRAINTS, RDB$RELATION_CONSTRAINTS) y sus campos (RDB$INDEX_SEGMENTS)

Los tres últimos tipos de restricciones necesitan datos sobre los campos de un índice; investiguemos entonces la tabla RDB$INDEX_SEGMENTS que mantiene los campos que conforman un índice:

Nuestra intención es mostrar los campos de un índice como una lista de nombres separados por comas. Para recuperar los campos en el orden adecuado usamos la siguiente sentencia SQL:

SELECT RC.RDB$RELATION_NAME as Tabla,
  S.RDB$FIELD_NAME as Campo
FROM RDB$RELATION_CONSTRAINTS RC
  INNER JOIN RDB$INDEX_SEGMENTS S ON RC.RDB$INDEX_NAME = S.RDB$INDEX_NAME
WHERE RC.RDB$INDEX_NAME = :Indice
ORDER BY S.RDB$FIELD_POSITION

En el parámetro 'Indice' pondremos el nombre del índice a investigar. Los campos se recuperan en el mismo orden en que fueron declarados.

El siguiente fragmento (parte del procedimiento lvConstraintsSelectItem) arma la lista separada por comas:

    { Busco los campos del indice }
      q2.close;
      q2.ParamByName('Indice').AsString := Item.SubItems[iIndice];
      q2.Open;
      s := '';
      while not q2.Eof do
      begin
        s := s + Trim(q2.FieldByName('Campo').AsString) + ', ';
        q2.Next;
      end;
      q2.Close;

Al final de este segmento tenemos la lista con un par de caracteres extra (la última coma y un espacio) que serán extraídos antes de agregarlos al memo de detalles.

El caso más complejo es el de las restricciones de Integridad Referencial (FOREIGN KEY). En este caso,

1. Obtenemos el nombre del índice de la tabla detalle (donde se define la restricción) de la lista –fue leído en el primer paso.
2. Buscamos y mostramos los campos de ese índice.
3. Buscamos el nombre de la restricción PRIMARY KEY o UNIQUE referenciada por la clave externa, en la tabla RDB$REF_CONSTRAINTS.
4. Obtenemos el índice correspondiente a la nueva restricción de la tabla RDB$RELATION_CONSTRAINTS.
5. Finalmente, obtenemos y mostramos los campos de este índice.

Necesitamos acceder a otra tabla: RDB$REF_CONSTRAINTS, que almacena datos sobre las restricciones de integridad referencial

El campo que nos interesa en este momento es RDB$CONST_NAME_UQ. Con este nombre podemos ir a la tabla RDB$RELATION_CONSTRAINTS y obtener el índice de esta nueva relación. Con este nombre, vamos a la tabla RDB$INDEX_SEGMENTS a buscar los campos. Todo esto se puede hacer en una sola operación, con la siguiente sentencia SQL:

SELECT RC.RDB$CONSTRAINT_NAME as NombreFK,
  RC.RDB$CONST_NAME_UQ as ConstrRef,
  RELC.RDB$RELATION_NAME as TablaRef,
  RELC.RDB$INDEX_NAME as IndiceRef,
  IND.RDB$FIELD_NAME as CampoRef
FROM RDB$REF_CONSTRAINTS RC
  LEFT JOIN RDB$RELATION_CONSTRAINTS RELC
    ON RC.RDB$CONST_NAME_UQ = RELC.RDB$CONSTRAINT_NAME
  LEFT JOIN RDB$INDEX_SEGMENTS IND
    ON RELC.RDB$INDEX_NAME = IND.RDB$INDEX_NAME
WHERE RC.RDB$CONSTRAINT_NAME = :FK
ORDER BY IND.RDB$FIELD_POSITION

Necesitamos sólo un parámetro, el nombre de la restricción FOREIGN KEY inicial. En la tabla resultado tendremos repetidos los valores de los campos NombreFK (nombre de la restricción FOREIGN KEY), ConstrRef (nombre de la restricción referenciada), TablaRef (nombre de la tabla referenciada) e IndiceRef (nombre del índice referenciado) por cada campo incluido en el índice referenciado. Dado que la cantidad de campos de un índice no es muy elevada, no justifico la ejecución de dos consultas separadas.

Ahora si podemos ver el listado completo del método lvConstraintsSelectItem, llamado en respuesta al evento OnSelectItem del ListView:

procedure TForm1.lvConstraintsSelectItem(Sender: TObject; Item: TListItem;
  Selected: Boolean);
var
  s: string;
begin
  if Selected then
  begin
    if SameText(Item.SubItems[iTipo], cPK) or
       SameText(Item.SubItems[iTipo], cFK) or
       SameText(Item.SubItems[iTipo], cUnique) then
    begin
      { Busco los campos del indice }
      q2.close;
      q2.ParamByName('Indice').AsString := Item.SubItems[iIndice];
      q2.Open;
      s := '';
      while not q2.Eof do
      begin
        s := s + Trim(q2.FieldByName('Campo').AsString) + ', ';
        q2.Next;
      end;
      q2.Close;
      mDefinicion.Lines.Text := Format('Tabla: %s', [item.subitems[iTabla]]);
      mDefinicion.Lines.Add(Format('Indice: %s, con campos: %s',
        [trim(Item.SubItems[iIndice]), copy(s, 1, length(s) - 2)]));
      if SameText(Item.SubItems[iTipo], cFK) then
      begin
        { busco los datos de la tabla referenciada (master) }
        q3.Close;
        q3.ParamByName('FK').AsString := Item.Caption;
        q3.Open;
        s := '';
        while not q3.Eof do
        begin
          s := s + Trim(q3.FieldByName('CampoRef').AsString) + ', ';
          q3.Next;
        end;
        mDefinicion.Lines.Add(Format('Referencia al í %s, de la tabla: %s, campos: %s',
          [trim(q3.FieldByName('IndiceRef').AsString),
          trim(q3.FieldByName('TablaRef').AsString),
            copy(s, 1, length(s) - 2)]));
      end; //if FK...
    end
    else
    if SameText(Item.SubItems[iTipo], cCheck) then
    begin
      mDefinicion.lines.SetText(PChar(Item.SubItems[iDefinicion]));
      mDefinicion.Lines.Insert(0,'Tabla: '+Item.SubItems[iTabla]);
    end
    else
      mDefinicion.Lines.Text := Format('Tabla: %s, Campo: %s', [Item.Subitems[iTabla], Item.SubItems[iCampo]]);
    mMensaje.Lines.Text := item.SubItems[iMensaje];
  end //if selected
  else
    if not SameText(Item.SubItems[iMensaje], mMensaje.Lines.Text) then
    begin
      Item.SubItems[iMensaje] := mMensaje.Lines.Text;
      Modificado := True;
    end;
end;

He utilizado algunas constantes extras, definidas en la sección de constantes general de la unit:

{ Identificacion de los distintos tipos de constraints }
  cCheck = 'CHECK';
  cFK = 'FOREIGN KEY';
  cPK = 'PRIMARY KEY';
  cUnique = 'UNIQUE';
  cNotNull = 'NOT NULL';

Observe el uso de SetText para preservar los saltos de línea; también hay que notar que este evento se llama cada vez que cambia la selección de un item de la lista. Esto significa que se llamará una vez cuando se selecciona un ítem, y otra vez cuando se deselecciona. En el primer caso buscamos todos los detalles de la restricción y los mostramos en el memo correspondiente, mientras colocamos también el mensaje asociado (si había alguno) en el memo de escritura. Cuando se deselecciona un item, comprobamos si el usuario ha cambiado el mensaje; en caso afirmativo almacenamos el nuevo mensaje y activamos una señal interna (Modificado) para saber que se ha realizado un cambio y hay que grabarlo.

Guardar los cambios

Los datos que debemos guardar en el archivo INI (el nombre de las restricciones y sus mensajes asociados) están en la lista superior; por lo tanto, el proceso de guardado es solamente un bucle que recorre la lista y almacena una por una las líneas:

procedure TForm1.actGuardarExecute(Sender: TObject);
var
  i: TIniFile;
  j: Integer;
begin
  try
    Screen.Cursor := crHourGlass;
    if lvConstraints.Selected <> nil then
      lvConstraints.Selected.SubItems[iMensaje] := mMensaje.Lines.Text;
    i := TIniFile.Create(edDiccio.text);
    for j := 0 to lvConstraints.Items.Count - 1 do
      i.WriteString(secConstraints, lvConstraints.Items[j].Caption, lvConstraints.Items[j].SubItems[iMensaje]);
    i.Free;
    Modificado := False;
  finally
    Screen.Cursor := crDefault;
  end;
end;

Generar mensajes automáticos

Si llegamos hasta aquí ¿por qué no dar un paso más, y dotar a la aplicación con la capacidad de generar mensajes para las restricciones de forma automática? Estos mensajes sirven como modelo, y pueden ser escritos en el lenguaje del usuario.

Los mensajes que propongo (pueden ser modificados fácilmente) para cada tipo de restricción son

• NOT NULL: ‘El campo <nombre del campo> debe tener un valor’
• CHECK: ‘No se cumple la expresión <expresión>’
• PRIMARY KEY, UNIQUE: ‘El registro ya existe y no puede repetirse’
• FOREIGN KEY: ‘Hay registros relacionados, no se puede realizar la operación’

El código es bastante sencillo (comparado con lo que hemos visto…)

procedure TForm1.GenerarMensaje(indice: integer);
var
  li: TListItem;
begin
  li := lvConstraints.Items[indice];
  if li.SubItems[iMensaje] = '' then
  begin
    if SameText(li.SubItems[iTipo], cNotNull) then
      li.SubItems[iMensaje] := Format('El campo %s debe tener un valor', [li.subitems[iCampo]])
    else
    if SameText(li.SubItems[iTipo], cFK) then
      li.SubItems[iMensaje] := 'Hay registros relacionados, no se puede realizar la operació'
    else
    if SameText(li.SubItems[iTipo], cPK) or SameText(li.SubItems[iTipo], cUnique) then
      li.SubItems[iMensaje] := 'El registro ya existe y no puede repetirse'
    else
    if SameText(li.SubItems[iTipo], cCheck) then
      li.SubItems[iMensaje] := format('No se cumple la expresió %s',[copy(li.subitems[iDefinicion],
        6, length(li.SubItems[iDefinicion]))]);
    Modificado := True;
  end; //if
end;

Este código genera sólo un mensaje, para el ítem de la lista cuyo índice se pasa como parámetro. El código de los dos botones de generación se ve a continuación

Sólo el seleccionado:

procedure TForm1.actGenerarSelExecute(Sender: TObject);
begin
  GenerarMensaje(lvConstraints.Selected.Index);
  mMensaje.Lines.Text := lvConstraints.Selected.SubItems[iMensaje];
end;

Toda la lista:

procedure TForm1.actGenerarTodosExecute(Sender: TObject);
var
  i: Integer;
  b: Boolean;
begin
  b := MessageDlg('¿ los mensajes existentes?', mtWarning, [mbYes, mbNo], 0) = mrYes;
  try
    Screen.Cursor := crHourGlass;
    for i := 0 to lvConstraints.Items.Count - 1 do
    begin
      if b then lvConstraints.Items[i].SubItems[iMensaje] := '';
      GenerarMensaje(i);
    end;
  finally
    Screen.Cursor := crDefault;
  end;
end;

Mantenimiento de la interfaz

La aplicación ya está completa; el resto del código se encarga de crear y mantener una interfaz amigable, que responda como uno espera. En particular he usado una lista de acciones y he hecho un uso intensivo de la propiedad Anchors de los componentes para que se adapten al tamaño de la ventana.

Y eso es todo. El camino ha sido largo y desparejo, pero creo que valió la pena. Junto con la técnica para interceptar y cambiar los mensajes de error que desarrollamos en el último artículo, esta aplicación se ganará un lugar en la caja de herramientas para hacer ‘más humanos’ los programas que desarrollemos.

El código fuente completo de la aplicación se encuentra en CodeCentral.