Auslesen der System-Tabellen

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

Hamburgo
Beiträge: 125
Registriert: Di 28. Mai 2019, 17:28

Hallo zusammen,

so vor ca. 10 Jahren habe ich im Web mal einen Select gefunden,
der mir ganz nützliche Informationen aus den System-Tabellen
liefert:

Code: Alles auswählen

   SELECT r.RDB\$FIELD_NAME AS Field,
          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 Type,
          f.RDB\$FIELD_LENGTH AS Length,
          f.RDB\$FIELD_PRECISION AS Precision_X,
          f.RDB\$FIELD_SCALE AS Scale,

          f.RDB\$FIELD_SUB_TYPE AS SubType,
          coll.RDB\$COLLATION_NAME AS Collation,
          cset.RDB\$CHARACTER_SET_NAME AS CharSet,

          MIN(rc.RDB\$CONSTRAINT_TYPE) AS Constraint_X,
          MIN(i.RDB\$INDEX_NAME) AS Idx,
          CASE WHEN r.RDB\$NULL_FLAG = 1 THEN 'NO' ELSE 'YES' END AS Null_X,
          r.RDB\$DEFAULT_VALUE AS Default_X,
          r.RDB\$FIELD_POSITION AS Pos 

     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 f.RDB\$COLLATION_ID = coll.RDB\$COLLATION_ID

LEFT JOIN RDB\$CHARACTER_SETS cset ON f.RDB\$CHARACTER_SET_ID = cset.RDB\$CHARACTER_SET_ID

LEFT JOIN RDB\$INDEX_SEGMENTS s ON s.RDB\$FIELD_NAME=r.RDB\$FIELD_NAME

LEFT JOIN RDB\$INDICES i ON i.RDB\$INDEX_NAME = s.RDB\$INDEX_NAME 
      AND i.RDB\$RELATION_NAME=r.RDB\$RELATION_NAME

LEFT JOIN RDB\$RELATION_CONSTRAINTS rc ON rc.RDB\$INDEX_NAME = s.RDB\$INDEX_NAME
      AND rc.RDB\$INDEX_NAME = i.RDB\$INDEX_NAME
      AND rc.RDB\$RELATION_NAME = i.RDB\$RELATION_NAME

LEFT JOIN RDB\$REF_CONSTRAINTS refc ON rc.RDB\$CONSTRAINT_NAME = refc.RDB\$CONSTRAINT_NAME

    WHERE r.RDB\$RELATION_NAME='".$_POST['_DieseTabelle']."' 
 GROUP BY Field,
          Type, 
          Length,
          Precision_X,
          Scale,
          SubType,
          Collation,
          CharSet,
          Null_X,
          Default_X,
          Pos
 ORDER BY Pos
Ich muss zugeben, dass ich nur bedingt durch das Ding durchblicke. In der Liga kicke ich einfach nicht.

Einziger Nachteil dieses Select ist, dass für die Default-Werte nicht der tatsächliche Wert ausgegeben wird,
z.B. "0", sondern ein s.g. BLR (Binary Language Representation), z.B. "0x0000194c00000005".

Hat jemand eine Idee wie man dem Select beibringen kann den tatsächlich eingetragenen Default-Wert
z.B. "0" oder "NOW" usw. auszuspucken ?

Ein weiterer Schwachpunkt ist (für mich aber von geringer Bedeutung / bitte keine große Energie reinstecken), dass gefühlt alle verfügbaren Collation pro Feld/Spalte ausgeliefert werden und nicht nur die tatsächlich eingetragene Sortierung. Auch liefert der Output keine Info darüber welche Collation von allen die eingetragene ist.

Danke und viele Grüße
Hamburgo
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Normalerweise bekommt man alle Informationen über sog. Schema-Abfragen.
In ODBC/.Net/JDBC gibt es die
Connection.GetSchema()
die ein Resultset aller verfügbaren Schemata liefert.
Über das Schema "Restrictions" erhält man ein resultset zur Verwednung von Filtern des jeweiligen Schemas.
Da findest du dann auch das Scheme "Columns", dem du per Restriction den Tabellennamen gezielt mitgibst.

Diese Funktionalität hat mit FB nichts zu tun sondern gehört zur Mindestanforderung an gute DB-Treiber.
Hamburgo
Beiträge: 125
Registriert: Di 28. Mai 2019, 17:28

Das gilt für FireBird scheinbar nicht.

https://stackoverflow.com/questions/109 ... definition

1. Satz:
Firebird does not support schemas, so there is no way you can get that information.
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Hallo Hamurgo,

vielleicht hilft dir dieser Code-Schnipsel weiter:

Code: Alles auswählen

SELECT
  RF.RDB$FIELD_NAME FIELD_NAME,
  CASE F.RDB$FIELD_TYPE
    WHEN 7 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'SMALLINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 8 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'INTEGER'
        WHEN 1 THEN 'NUMERIC('  || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 9 THEN 'QUAD'
    WHEN 10 THEN 'FLOAT'
    WHEN 12 THEN 'DATE'
    WHEN 13 THEN 'TIME'
    WHEN 14 THEN 'CHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ') '
    WHEN 16 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'BIGINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 27 THEN 'DOUBLE'
    WHEN 35 THEN 'TIMESTAMP'
    WHEN 37 THEN 'VARCHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
    WHEN 40 THEN 'CSTRING' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
    WHEN 45 THEN 'BLOB_ID'
    WHEN 261 THEN 'BLOB SUB_TYPE ' || F.RDB$FIELD_SUB_TYPE
    ELSE 'RDB$FIELD_TYPE: ' || F.RDB$FIELD_TYPE || '?'
  END FIELD_TYPE,
  IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, NULL, 'NOT NULL') FIELD_NULL,
  CH.RDB$CHARACTER_SET_NAME FIELD_CHARSET,
  DCO.RDB$COLLATION_NAME FIELD_COLLATION,
  COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) FIELD_DEFAULT,
  F.RDB$VALIDATION_SOURCE FIELD_CHECK,
  RF.RDB$DESCRIPTION FIELD_DESCRIPTION
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
WHERE (RF.RDB$RELATION_NAME = 'TBL_USER') 
AND (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
ORDER BY RF.RDB$FIELD_POSITION;
Gruß
Martin
Martin Köditz
it & synergy GmbH
Hamburgo
Beiträge: 125
Registriert: Di 28. Mai 2019, 17:28

Nachtrag:

Wenn ich via PDO lese, dann kommen zu meiner Überraschung die korrekten Default-Werte und
KEINE BLR's !

Folglich liegt das Problem wohl im FB-Treiber unter PHP.
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Nun ja, ich habe ja die Treiber aufgezählt die das untestützen.
Da du bei PHP auf den ISC-Routinen aufsetzt, ist das nur verständlich.
Allerdings hindert diech niemand, die C# Quelle des FB-Clients anzusehn, um ebenso Schemas zu unterstützen.
Siehe ODBC-PHP wie ODBC_Columns, ODBC_Tables usw.
Native hätte ich auch keine Lust mir das zusammen zu suchen.
vr2
Beiträge: 214
Registriert: Fr 13. Apr 2018, 00:13

Hallo Hamburgo,

Martins SQL illustriert ganz gut, worum es geht, und liefert Dir auch die tatsächlichen Default -Werte. Es müsste natürlich für Firebird 4 erweitert werden, da es etliche der neuen Datentypen nicht auswertet. Was die Treiber angeht: Die kochen auch nur mit Wasser und am Ende fragen die für solche Informationen auch nur die Systemtabellen ab, die ISC-API-Funktionen liefern keine Schema-Informationen, bzw in einer Form ähnlich wie in den Systemtabellen, auch da muss auf SQL abgebildet werden.

Also entweder Du suchst Dir Treiber, die die Schema-Infos als Convenience-Funktionen bereitstellen, wobei Dir jedes brauchbare DBA-Tool das auch rausgibt (Kriterium für Treiberauswahl ist nicht, ob sie Schemadaten rausgeben, das ist eher unwichtig, sondern eher, ob sie aktuelle Serverversionen voll unterstützen), oder Du legst Dir einmalig eine Abfrage wie die von Torsten zurecht, erweitert/aktualisiert auf den neuesten Stand (dec_floats, timestamp with timezone, boolean, ...), dann bist Du davon unabhängig, ob Treiber Dir die Infos rüberreichen oder nicht oder wie aktuell sie sind, das ist nämlich auch ein Punkt, der gerne übersehen wird. Allerweltstreiber wie pdo haben den Nachteil, dass sie nur den kleinsten gemeinsamen Nenner verschiedener DB-Treiber umsetzen, besondere Firebird-Features kannst Du damit nicht nutzen, weil die Maintainer die gar nicht kennen. Und sowieso immer Jahrzehnte hinter der aktuellen Firebird-Entwicklung zurückhängen. Maßstäbe hingegen setzen die JDBC-Treiber von Mark Rotteveel und der Python-Treiber von Pavel Cisar. Neuerdings auch der Delphi-Treiber IBX von Tony Whyman, auch der unterstützt Firebird 4 und UDRs. Welcher Treiber kann das noch?

Grüße, Volker
bfuerchau
Beiträge: 485
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Stimmt, was die Treiber angeht so wird das z.T. eher schlechter als besser.
In .Net gibt es spezielle Klassen für ODBC, OLEDB, Oracle und SQL-Server.
Alle unterstützen Schemaabfragen in unterschiedlicher Ausprägung.
Benutzt man nun aber, auch in .Net, die ADODB-Bibliothek, die auf ODBC und registrierte OleDb-Treiber zugreift, muss man feststellen, dass erheblich bessere und detailiertere Schemaabfragen möglich sind.
Die SQLDbConnection liefert z.B. keine Indexinformationen, die per ADODB problemlos zu bekommen sind.
Dasselbe gilt für dei ODBC-Unterstützung. Der MSDASQL-OleDb-Treiber für ODBC ist da genauer und besser als der native ODBC-Zugriff auf dieselbe Datenbank.

Da ADODB ActiveX-basisert ist sollte es doch möglich sein, auch per PHP auf ActiveX zugreifen zu können.
https://www.example-code.com/phpAx/default.asp

Nur leider wird der ODBC-Treiber von Firebird inzwischen auch nicht mehr weiterentwickelt.

Jedoch habe ich hier eine Möglichkeit gefunden, auf .Net-Assemblies zuzugreifen um ggf. den FB-Treiber von .Net nutzen zu können:
https://www.drupalonwindows.com/en/blog ... mblies-php
Benutzeravatar
martin.koeditz
Beiträge: 443
Registriert: Sa 31. Mär 2018, 14:35

Um das Thema um den PHP-Treiber nochmals "rund" zu bekommen:

Bei Hamburgos Skript, kann der PHP-Treiber nichts vernünftiges ausgeben, da die Ausgabe ja BLR-Code erzeugt. Dieser ist halt nicht ohne weiteres übersetzbar.
Das von mir aufgezeigte SQL funktioniert hingegen im PHP-Treiber wurderbar:

Code: Alles auswählen

[0] => Array
        (
            [FIELD_NAME] => USER_ID                                                                                                                     
            [FIELD_TYPE] => BIGINT
            [FIELD_NULL] => NOT NULL                        
            [FIELD_CHARSET] => 
            [FIELD_COLLATION] => 
            [FIELD_DEFAULT] => 
            [FIELD_CHECK] => 
            [FIELD_DESCRIPTION] => Benutzer-ID
        )

    [1] => Array
        (
            [FIELD_NAME] => NAME                                                                                                                        
            [FIELD_TYPE] => VARCHAR(32)
            [FIELD_NULL] => NOT NULL                        
            [FIELD_CHARSET] => UTF8                                                                                                                        
            [FIELD_COLLATION] => 
            [FIELD_DEFAULT] => 
            [FIELD_CHECK] => 
            [FIELD_DESCRIPTION] => Name des Benutzers.
        )

    [2] => Array
        (
            [FIELD_NAME] => FIRSTNAME                                                                                                                   
            [FIELD_TYPE] => VARCHAR(32)
            [FIELD_NULL] => NOT NULL                        
            [FIELD_CHARSET] => UTF8                                                                                                                        
            [FIELD_COLLATION] => 
            [FIELD_DEFAULT] => 
            [FIELD_CHECK] => 
            [FIELD_DESCRIPTION] => Vorname des Benutzers.
        )
        ...
Natürlich muss man diese Daten dann selbst noch weiterverarbeiten.

Hilft das weiter oder habe ich grundsätzlich etwas falsch verstanden? Wenn es wirklich einen "Fehler" gibt, dann sollten wir dem auf den Grund gehen.

Gruß
Martin
Martin Köditz
it & synergy GmbH
Hamburgo
Beiträge: 125
Registriert: Di 28. Mai 2019, 17:28

Hallo Martin,

das Script, welches Du gepostet hast, ist dem welches ich vor Jahren mal "geklaut" habe
sehr verwandt und beide liefern ganz brauchbare Daten.

Die Ausgabe, die Du mit Deinem letzten Post eingestellt hast, liefert meines Erachtens keine
Klarheit, ob eingetragenen Default-Werte korrekt ausgegeben werden, da ja keine angezeigt werden.

Für meine Zwecke ist es völlig ausreichend und bin happy, dass ich gestern durch Zufall rausgefunden
habe, dass PDO die System-Tabellen (Default-Werte) korrekt lesen kann.

Ich brauche die Informationen bisher nur in meiner Datenbank-Update-Routine, um in den Bestands-Daten
meiner Kunden den korrekten Default-Wert zu setzen, wenn neue Felder/Spalten in einer Tabelle
hinzugekommen sind.

Bisher habe ich die Default-Werte aus den Feld-Typen abgeleitet. Jetzt habe ich jedoch einen Fall, wo das nicht mehr aussreicht.

Nur in einem Script vom Standard abzuweichen und PDO zu nutzen ist für mich ok.

Zwischenzeitlich habe ich sogar noch dazu gelernt, wo ich die Info's zu Indizes und Domain's her bekomme.

Wenn es auch unter PHP funktionieren würde, wäre natürlich fein, aber für mich nicht mehr erforderlich.

Danke und viele Grüße
Hamburgo
Antworten