Extrayendo metadatos

[cesarsoftware]

Hola compis, tengo una duda no existencial, uso un codigo que circula por la red para extraer los campos de una tabla (para tener integrado un visor de bbdd en las aplicaciones).

Es el siguiente

Código Delphi [-]
    sql := 'SELECT r.RDB$RELATION_NAME as table_name,';
    sql := sql + ' r.RDB$FIELD_POSITION as field_position,';
    sql := sql + ' r.RDB$FIELD_NAME AS field_name,';
    sql := sql + ' r.RDB$DESCRIPTION AS field_description,';
    sql := sql + ' r.RDB$DEFAULT_VALUE AS field_default_value,';
    sql := sql + ' r.RDB$NULL_FLAG AS field_not_null_constraint,';
    sql := sql + ' f.RDB$FIELD_LENGTH AS field_length,';
    sql := sql + ' f.RDB$FIELD_PRECISION AS field_precision,';
    sql := sql + ' f.RDB$FIELD_SCALE AS field_scale,';
    sql := sql + ' CASE f.RDB$FIELD_TYPE';
    sql := sql + '   WHEN 7 THEN ''' + 'SMALLINT''';
    sql := sql + '   WHEN 8 THEN ''' + 'INTEGER''';
    sql := sql + '   WHEN 9 THEN ''' + 'QUAD''';
    sql := sql + '   WHEN 10 THEN ''' + 'FLOAT''';
    sql := sql + '   WHEN 12 THEN ''' + 'DATE''';
    sql := sql + '   WHEN 13 THEN ''' + 'TIME''';
    sql := sql + '   WHEN 14 THEN ''' + 'CHAR''';
    sql := sql + '   WHEN 16 THEN ''' + 'BIGINT''';
    sql := sql + '   WHEN 27 THEN ''' + 'DOUBLE PRECISION''';
    sql := sql + '   WHEN 35 THEN ''' + 'TIMESTAMP''';
    sql := sql + '   WHEN 37 THEN ''' + 'VARCHAR''';
    sql := sql + '   WHEN 40 THEN ''' + 'CSTRING''';
    sql := sql + '   WHEN 45 THEN ''' + 'BLOB_ID''';
    sql := sql + '   WHEN 261 THEN ''' + 'BLOB''';
    sql := sql + ' ELSE ''' + 'UNKNOWN''';
    sql := sql + ' END AS field_type,';
    sql := sql + ' f.RDB$FIELD_SUB_TYPE AS field_subtype,';
    sql := sql + ' coll.RDB$COLLATION_NAME AS field_collation,';
    sql := sql + ' cset.RDB$CHARACTER_SET_NAME AS field_charset';
    sql := sql + ' FROM RDB$RELATION_FIELDS r';
    sql := sql + ' LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME';
    sql := sql + ' LEFT JOIN RDB$COLLATIONS coll ON r.RDB$COLLATION_ID = coll.RDB$COLLATION_ID';
    sql := sql + ' AND f.RDB$CHARACTER_SET_ID = coll.RDB$CHARACTER_SET_ID';
    sql := sql + ' LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID';
    sql := sql + ' WHERE RDB$RELATION_NAME = ''' + tabla + '''';
    sql := sql + ' ORDER BY r.RDB$RELATION_NAME, r.RDB$FIELD_POSITION;';
  try
    FormBBDD.SQLConnection.Execute(sql, nil, @DataSetCampos);
  except
    on E: Exception do
    begin
      msg := 'No se ha podido leer la tabla ' + tabla + #13 + #10 + E.Message;
      AvisoMsg(msg, True);
      DataSetCampos.Free;
      Exit;
    end;
  end;
  cuantos := DataSetCampos.RecordCount;
  STnumCampos.Caption := IntToStr(cuantos);

En primer lugar deciros que no entiendo las funciones "coll" y "cset" ni poque ha veces usa "f" y otras "r" pero funciona, si me lo podeis explicar me haceis un favor.
Y lo segundo es que no entiendo porque hace un case para obtener el tipo de datos si estos estan en la tabla RDB$TYPES, ¿no se podria hacer un left join o algo para sacar directamente el tipo de datos, es decir si el tipo es 14 entonces "TEXT" ya que ese datos esta en la tabla de tipos?

Gracias de antemano.

[Casimiro Notevi]

Son alias, se usan cuando se hace referencia a más de una tabla, para que no haya problemas de saber a qué campo de qué tabla nos referimos, de esa manera puedes tener campos con el mismo nombre pero de tablas diferentes y estaría claro, ejemplo:

tabla empresas (id, nombre, telefono)
tabla sucursales (id, id_empresa, nombre, telefono)

Ejemplos:

Código SQL [-]
select e.telefono, s.telefono
from empresas e
inner join sucursales s on (s.id_empresa=e.id)
order by e.nombre, s.nombre

Código SQL [-]
select emp.telefono, suc.telefono
from empresas emp
inner join sucursales suc on (suc.id_empresa=emp.id)
order by emp.nombre, suc.nombre

En el primer caso, se ha usado el alias e para empresas y s para sucursales
En el segundo caso, se ha usado el alias emp para empresas y suc para sucursales.
Puedes usar el alias que quieras

[cesarsoftware]

Gracias "casimiro" entendido el tema de alias, entoces coll y cset tambien son alias de COLLATION Y CHARSET.

Por tanto r se refiere a RELATIONS y f a FIELDS, 't'a claro.

¿Y la segunda cuestion? ¿Como meter en el campo field_type el valor del campo RDB$TYPES_NAME de la tabla RDB$TYPES,
es decir que en vez de tener un 8 (INTEGER) y hacerle el "case" que coja el nombre de la tabla RDB$TYPES?

Sin haber estudiado, lo haria asi, mal, pero es para aclararme
"busca en la tabla TYPES y mete en el campo field_type el valor del campo TYPE_NAME de la tabla TYPES segun el valor del campo FIELD_TYPE de la tabla FIELD"
LEFT JOIN RDB$TYPES ON field_type = RDB$TYPES.RDB$TYPE_NAME = RDB$FIELDS.RDB$FIELD_TYPE

No quiero (o si) que me resuelvas el codigo sino aprender a hacerlo.

Gracias,

[Casimiro Notevi]

Si existe ese relación, entonces debe funcionar.
Aunque en varios sitios he visto que no se usa, no he investigado el motivo, mira este mensaje:

[cesarsoftware]

Gracias casimiro, ya habia visto codigos parecidos.

No soy experto en SQL pero igual que a ti me extraña que los codigos que circulan no usen esa relacion.

¿Un buen curso de SQL?

Thanks

[Casimiro Notevi]

No sé, ya digo, no lo he mirado, aunque supongo que debe existir algún motivo.

[cesarsoftware]

Dentro de mi breve experiencia en SQL, entiendo que un left join "añade" registros concatenando dos campos (uno de cada tabla), por ejemplo
select * from editoriales left join libros on editoriales.codigo=libros.codigoeditorial

sacara los libros de la tabla editoriales cuyo editoriales.codigo sea igual a la libros.codigoeditorial
Pero aqui lo que se busca es "sustituir" un campo de un tabla por el contenido de otro campo de otra tabla, se me ocurre hacerlo en dos pasos.
Primero sacar los campos de RDB$FIELDS y despues recorrer el DataSet asignando el RDB$TYPES correspondiente.
Digo esto porque si en un futuro o version de firebird cambia el tipo, al haberlo asignado mediante un case podemos tener un problema ¿no?

[Casimiro Notevi]

Ya te digo, no he mirado esa tabla, no tengo ni idea, tendría que investigar un poco y echarle un vistazo.

[cesarsoftware]

Gracias casimiro por el tiempo dedicado, creo que tienes cosas mejores que hacer que mirar este tema, yo lo investigare y de paso aprendere mas sql, en cuanto encuentro una solucion lo publico.

Una vez mas agradezco infinitamente el tiempo que nos dedicas, deberiamos compensarte, a ti y al resto de los moderadores no se como, pero deberiamos.

Thanks.

[Casimiro Notevi]

Gracias a ti, y cuéntanos lo que descubras

[cesarsoftware]

Bueno, creo que se lo que pasa, tema resuelto.

Segun se ve en la tabla de tipos, digamos, que no esta todo lo precisa que se necesita. He realizado una segunda pasada para incluir los nombres de los tipos y no queda como quisieramos, hay que hacer el CASE "en linea" como hace todo el mundo (o el primero que empezo, jejeje)
En esta imagen se ve la aplicacion que muestra los campos de las tablas usando la funcion que nos ocupa (osea, funciona)

En esta imagen se ven los tipos de datos en la tabla RDB$TYPES


He "Adecentado" o mejor dicho simplificado y adecuado el codigo para tener todos los campos de una tabla y sus caracteristicas.
Dejo toda la funcion completa ya que despues hay que sacar los indices y los campos que corresponden a los indices.
Este codigo es totalmente funcional, evidentemente el formulario y las grillas las pone cada usuario
Casimiro, puedes usarlo cuando quieras, jejeje

Código Delphi [-]
procedure TFormConfTablas.LeerCampos(tabla: string);
var
  sql, msg, nombreindice, campo, tipo: string;
  DataSetCampos, DataSetIndices, DataSetSegment: TSQLDataset;
  cuantos, i, j, indices, campos: word;
  longitud, precision, escala: integer;
begin
  SGcampos.OnClick := nil;
  SGindices.OnClick := nil;
  IniciaGrillas();
  sql := 'SELECT rela.RDB$RELATION_NAME as table_name,';
  sql := sql + ' rela.RDB$FIELD_POSITION as field_position,';
  sql := sql + ' rela.RDB$FIELD_NAME AS field_name,';
  sql := sql + ' fiel.RDB$FIELD_LENGTH AS field_length,';
  sql := sql + ' fiel.RDB$FIELD_PRECISION AS field_precision,';
  sql := sql + ' fiel.RDB$FIELD_SCALE AS field_scale,';
  sql := sql + ' CASE fiel.RDB$FIELD_TYPE';
  sql := sql + '   WHEN 7 THEN';// ''' + 'SHORT''';
  sql := sql + '   CASE fiel.RDB$FIELD_SUB_TYPE';
  sql := sql + '     WHEN 1 THEN ''' + 'NUMERIC''';
  sql := sql + '     WHEN 2 THEN ''' + 'DECIMAL''';
  sql := sql + '     ELSE ''' + 'SMALLINT''';
  sql := sql + '   END';
  sql := sql + '   WHEN 8 THEN';// ''' + 'LONG''';
  sql := sql + '   CASE fiel.RDB$FIELD_SUB_TYPE';
  sql := sql + '     WHEN 1 THEN ''' + 'NUMERIC''';
  sql := sql + '     WHEN 2 THEN ''' + 'DECIMAL''';
  sql := sql + '     ELSE ''' + 'INTEGER''';
  sql := sql + '   END';
  sql := sql + '   WHEN 9 THEN ''' + 'QUAD''';
  sql := sql + '   WHEN 10 THEN ''' + 'FLOAT''';
  sql := sql + '   WHEN 12 THEN ''' + 'DATE''';
  sql := sql + '   WHEN 13 THEN ''' + 'TIME''';
  sql := sql + '   WHEN 14 THEN ''' + 'TEXT''';
  sql := sql + '   WHEN 16 THEN';// ''' + 'INT64''';
  sql := sql + '   CASE fiel.RDB$FIELD_SUB_TYPE';
  sql := sql + '     WHEN 1 THEN ''' + 'NUMERIC''';
  sql := sql + '     WHEN 2 THEN ''' + 'DECIMAL''';
  sql := sql + '     ELSE ''' + 'BIGINT''';
  sql := sql + '   END';
  sql := sql + '   WHEN 27 THEN ''' + 'DOUBLE''';
  sql := sql + '   WHEN 35 THEN ''' + 'TIMESTAMP''';
  sql := sql + '   WHEN 37 THEN ''' + 'VARCHAR''';
  sql := sql + '   WHEN 40 THEN ''' + 'CSTRING''';
  sql := sql + '   WHEN 45 THEN ''' + 'BLOB_ID''';
  sql := sql + '   WHEN 261 THEN';// ''' + 'BLOB''';
  sql := sql + '   CASE fiel.RDB$FIELD_SUB_TYPE';
  sql := sql + '     WHEN 0 THEN ''' + 'UNSPECIFIED''';
  sql := sql + '     WHEN 1 THEN ''' + 'TEXT''';
  sql := sql + '     WHEN 2 THEN ''' + 'BLT''';
  sql := sql + '     WHEN 3 THEN ''' + 'ACL''';
  sql := sql + '     WHEN 4 THEN ''' + 'RANGES''';
  sql := sql + '     WHEN 5 THEN ''' + 'SUMMARY''';
  sql := sql + '     WHEN 6 THEN ''' + 'FORMAT''';
  sql := sql + '     WHEN 7 THEN ''' + 'TRANSACTION_DESCRIPTION''';
  sql := sql + '     WHEN 8 THEN ''' + 'EXTERNAL_FILE_DESCRIPCTION''';
  sql := sql + '   END';
  sql := sql + ' ELSE ''' + 'UNKNOWN''';
  sql := sql + ' END AS field_type';
  sql := sql + ' FROM RDB$RELATION_FIELDS rela';
  sql := sql + ' LEFT JOIN RDB$FIELDS fiel ON rela.RDB$FIELD_SOURCE = fiel.RDB$FIELD_NAME';
  sql := sql + ' WHERE RDB$RELATION_NAME = ''' + tabla + '''';
  sql := sql + ' ORDER BY rela.RDB$RELATION_NAME, rela.RDB$FIELD_POSITION;';
  try
    FormBBDD.SQLConnection.Execute(sql, nil, @DataSetCampos);
  except
    on E: Exception do
    begin
      msg := 'No se ha podido leer la tabla ' + tabla + #13 + #10 + E.Message;
      AvisoMsg(msg, True);
      DataSetCampos.Free;
      SGcampos.OnClick := SGcamposClick;
      SGindices.OnClick := SGindicesClick;
      Exit;
    end;
  end;
  cuantos := DataSetCampos.RecordCount;
  STnumCampos.Caption := IntToStr(cuantos);
  if cuantos = 0 then
  begin
    DataSetCampos.Free;
    SGcampos.OnClick := SGcamposClick;
    SGindices.OnClick := SGindicesClick;
    Exit;
  end;
  for i := 0 to cuantos - 1 do
  begin
    tipo := Trim(DataSetCampos.FieldByName('field_type').AsString);
    longitud := DataSetCampos.FieldByName('field_length').AsInteger;
    precision := DataSetCampos.FieldByName('field_precision').AsInteger;
    escala := DataSetCampos.FieldByName('field_scale').AsInteger;
    if escala < 0 then
      escala := escala * -1;
    if precision > 0 then
    begin
      if (tipo = 'SMALLINT')
      or (tipo = 'INTEGER')
      or (tipo = 'BIGINT') then
      begin
        tipo := 'NUMERIC';
        longitud := precision;
      end;
    end;
    SGcampos.Cells[0, i + 1] := Trim(DataSetCampos.FieldByName('field_position').AsString);
    SGcampos.Cells[1, i + 1] := Trim(DataSetCampos.FieldByName('field_name').AsString);
    SGcampos.Cells[2, i + 1] := tipo;
    SGcampos.Cells[3, i + 1] := IntToStr(longitud);
    SGcampos.Cells[4, i + 1] := IntToStr(escala);
    SGcampos.RowCount := SGcampos.RowCount + 1;
    SGcampos.Row := SGcampos.RowCount - 1;
    DataSetCampos.Next;
  end;
  SGcampos.RowCount := SGcampos.RowCount - 1;
  DataSetCampos.Free;
  // Leer Indices
  sql := 'SELECT RDB$INDEX_NAME, RDB$INDEX_ID, RDB$SEGMENT_COUNT FROM RDB$INDICES';
  sql := sql + ' WHERE RDB$RELATION_NAME = ''' + tabla + '''';
  try
    FormBBDD.SQLConnection.Execute(sql, nil, @DataSetIndices);
  except
    on E: Exception do
    begin
      msg := 'No se ha podido leer los indices de la tabla ' + tabla + #13 + #10 + E.Message;
      AvisoMsg(msg, True);
      DataSetIndices.Free;
      SGcampos.OnClick := SGcamposClick;
      SGindices.OnClick := SGindicesClick;
      Exit;
    end;
  end;
  cuantos := DataSetIndices.RecordCount;
  STnumIndices.Caption := IntToStr(cuantos);
  if cuantos = 0 then
  begin
    DataSetIndices.Free;
    SGcampos.OnClick := SGcamposClick;
    SGindices.OnClick := SGindicesClick;
    Exit;
  end;
  for i := 0 to cuantos - 1 do
  begin
    nombreindice := Trim(DataSetIndices.FieldByName('RDB$INDEX_NAME').AsString);
    SGindices.Cells[0, i + 1] := Trim(DataSetIndices.FieldByName('RDB$INDEX_ID').AsString);
    SGindices.Cells[1, i + 1] := nombreindice;
    indices := DataSetIndices.FieldByName('RDB$SEGMENT_COUNT').AsInteger;
    if indices = 0 then
      Continue;
    // Leer campos de indices
    sql := 'SELECT RDB$FIELD_NAME, RDB$FIELD_POSITION FROM RDB$INDEX_SEGMENTS';
    sql := sql + ' WHERE RDB$INDEX_NAME = ''' + nombreindice + '''';
    sql := sql + ' ORDER BY RDB$FIELD_POSITION ASC';
    try
      FormBBDD.SQLConnection.Execute(sql, nil, @DataSetSegment);
    except
      on E: Exception do
      begin
        msg := 'No se ha podido leer los campos de los indices de la tabla ' + tabla  + #13 + #10 + E.Message;
        AvisoMsg(msg, True);
        DataSetSegment.Free;
        Continue;
      end;
    end;
    campos := DataSetSegment.RecordCount;
    if campos = 0 then
    begin
      DataSetSegment.Free;
      Continue;
    end;
    for j := 0 to campos - 1 do
    begin
      if (2 + j) <= (SGindices.ColCount - 1) then
      begin
        campo := Trim(DataSetSegment.FieldByName('RDB$FIELD_NAME').AsString);
        SGindices.Cells[2 + j, i + 1] := campo;
      end;
      DataSetSegment.Next;
    end;
    SGindices.RowCount := SGindices.RowCount + 1;
    SGindices.Row := SGindices.RowCount - 1;
    DataSetSegment.Free;
    DataSetIndices.Next;
  end;
  SGindices.RowCount := SGindices.RowCount - 1;
  DataSetIndices.Free;
  SGcampos.OnClick := SGcamposClick;
  SGindices.OnClick := SGindicesClick;
  Application.ProcessMessages;
end;

Tema cerrado.

[Casimiro Notevi]

Entonces no era tan complicado ni tenía misterios ocultos, sólo hacía falta ponerse un rato con ello para entender el motivo
Gracias por compartirlo.