Wie Metadaten zu Tabellen/Typen extrahieren?

Programmierung mit PostgreSQL (C#, PHP, Perl, Java, ...)
Antworten
apatwork
Neuer Benutzer
Beiträge: 5
Registriert: Mo 28. Aug 2017, 11:09

Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von apatwork » Mo 28. Aug 2017, 11:48

Hallo allerseits!

ich bin alles andere als ein erfahrener Datenbank-Programmierer, muss mich allerdings derzeit mit folgender Aufgabenstellung befassen:

Ich soll aus einer PostgreSQL-DB (PostgreSQL 9.5) bestimmte, fuer eine Anwendung wichtige Metadaten extrahieren. Konkret geht es um die von Benutzern angelegten Tabellen (deren Namen, Namen der Tabellenspalten sowie deren Datentypen) und um eventuell von Anwendern definierte und in ihren Tabellen benutzte Datentypen. Ich habe schon intensiv gegoogelt und immerhin einen Anfang geschafft.

1. Mittels

Code: Alles auswählen

SELECT table_name FROM information_schema.tables
  WHERE table_schema='public'
  AND table_type='BASE TABLE';
erhalte ich die fuer mich interessanten Tabellen.

2. So ermittle ich zu einer Tabelle (<tablename>) die enthaltenen Spalten sowie Informationen zu deren Datentypen:

Code: Alles auswählen

SELECT column_name, data_type, udt_name
  FROM information_schema.columns
  WHERE table_schema='public' AND table_name='<tablename>';
3. Und auch, wie ich an custom datatypes reankomme, weiss ich schon (vorausgesetzt, ich kenne den Namen eines solchen Datentyps bereits (<typename>); etwa wenn er fuer ein Attribut einer zuvor analysierten Tabelle verwendet wurde):

Code: Alles auswählen

SELECT attname, format_type(atttypid, atttypmod)
  FROM pg_type
  JOIN pg_class on pg_class.oid = pg_type.typrelid
  JOIN pg_attribute on pg_attribute.attrelid = pg_class.oid
  WHERE typname = '<typename>' 
  ORDER BY attnum;
Nun allerdings stecke ich fest. Wie wir oben in 2. sehen, kann ich zu einer Tabellenspalte den "data_type" und auch, sofern es sich um einen "USER-DEFINED" type handelt, den Namen dieses custom datatypes ermitteln. Wie ermittle ich die gleiche Information zu den Attributen eines custom datatypes?

Ich habe inzwischen rausbekommen, dass in PostgreSQL die erzeugten Tabellen auch implizit als Datentypen abgelegt sind. Also eigentlich muesste ich gar nicht zwischen Tabellen und custom datatypes unterscheiden und koennte stattdessen einfach alle user-defined custom datatypes auswerten. Im Prinzip muesste man nach meinem Verstaendnis eine Query definieren, mit der ich die betreffende Untermenge der Eintraege in dem Katalog pg_type erhalte.

Ausserdem muesste ich noch irgendwie ein paar zusaetzliche Informationen ermitteln:
  • Ist ein Typ ein enum-Type?
  • Bei custom datatypes: die Attribute und ihre Typen und zu denen die Information, ob es sich um arrays von Werten des jew. Typs handelt.
  • Ein custom datatype kann ja selbst wieder Attribute enthalten, deren Typen custom datatypes sind. Diese Eigenschaft muss ich also ebenfalls erkennen koennen.
  • Weiterhin sollte es moeglich sein, Datentypen, die numerische Werte speichern (also integer sowie die Fliesskommatypen) von anderen Typen, die Text, Datumsinformationen etc. speichern, zu unterscheiden.
Die Kommunikation mit der Datenbank implementiere ich in einem C++-Programm unter Linux (RHEL 6.x), welches die Qt-Bibliothek (Version 5.9.1) und die darin bereitgestellte Funktionalitaet zum Zugriff auf Datenbanken (QSqlDatabase, QSqlQuery, ...) benutzt.

Waere toll, wenn ich ein paar sachdienliche Hinweise bekommen koennte.

akretschmer
Erfahrener Benutzer
Beiträge: 7532
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von akretschmer » Mo 28. Aug 2017, 12:04

Pffff....


Erst einmal: Willkommen im Forum hier.

Auf die Schnelle kann ich das nicht beantworten, das ist schon recht tief drin und das braucht man eher niemals in der täglichen Arbeit. Dir ist bewußt, daß sich diese internen Katalog-Tabellen von Version zu Version auch ändern können?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

apatwork
Neuer Benutzer
Beiträge: 5
Registriert: Mo 28. Aug 2017, 11:09

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von apatwork » Di 29. Aug 2017, 14:18

Oh! Damit hatte ich nicht gerechnet. Wenn die Tabellenstruktur, in der die "Metadaten", also die in der DB enthaltenen Tabellen/-spalten/Datentypen usw. definiert sind, nicht einigermassen fix ist, dann habe ich natuerlich mit jedem Versionswechsel der DB potenziell ein Problem ...
Vielen Dank! Das ist schon mal eine wichtige Information!

akretschmer
Erfahrener Benutzer
Beiträge: 7532
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von akretschmer » Di 29. Aug 2017, 14:55

Ja, das passiert nicht oft, aber es passiert...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1086
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von castorp » Di 29. Aug 2017, 15:41

apatwork hat geschrieben:
Mo 28. Aug 2017, 11:48
nun allerdings stecke ich fest. Wie wir oben in 2. sehen, kann ich zu einer Tabellenspalte den "data_type" und auch, sofern es sich um einen "USER-DEFINED" type handelt, den Namen dieses custom datatypes ermitteln. Wie ermittle ich die gleiche Information zu den Attributen eines custom datatypes?
Sowas in der Art:

Code: Alles auswählen

SELECT a.attname as attribute_name, 
       a.attnum as position,  
       dsc.description as remarks, 
       pg_catalog.format_type(a.atttypid, null) as data_type 
FROM pg_catalog.pg_namespace n  
   JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid 
   JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid 
   LEFT JOIN pg_catalog.pg_description dsc ON c.oid=dsc.objoid AND a.attnum = dsc.objsubid 
   LEFT JOIN pg_catalog.pg_class dc ON dc.oid=dsc.classoid AND dc.relname='pg_class' 
   LEFT JOIN pg_catalog.pg_namespace dn ON dc.relnamespace=dn.oid AND dn.nspname='pg_catalog' 
WHERE a.attnum > 0 AND NOT a.attisdropped  
  AND c.relkind = 'c' 
  AND n.nspname = 'public'  
  AND c.relname = 'data_type_name'
ORDER BY column_position;
Es gibt aber auch Tools die sowas können.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

apatwork
Neuer Benutzer
Beiträge: 5
Registriert: Mo 28. Aug 2017, 11:09

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von apatwork » Mi 30. Aug 2017, 18:10

Vielen Dank! hatte schon geahnt, dass das nicht ganz einfach wird. Ich probiere das in den naechsten Tagen mal aus und melde mich dann noch mal.

OT: Hatte ein bisschen Probleme mit den Einstellungen meines Teilnehmeraccounts in diesem Forum, weshalb ich keine EMail-Nachricht erhielt, wenn jemand sich zu meinem Problem geaeussert hat. Hoffe, in Zukunft funktioniert das ...

akretschmer
Erfahrener Benutzer
Beiträge: 7532
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von akretschmer » Mi 30. Aug 2017, 21:02

bekanntes Problem ...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

apatwork
Neuer Benutzer
Beiträge: 5
Registriert: Mo 28. Aug 2017, 11:09

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von apatwork » Mo 4. Sep 2017, 18:59

Habe inzwischen die ganz famose Query von Forenmitglied "castorp" ausprobiert. Gab einen klitzekleinen Typo in der letzten Zeile. Statt

Code: Alles auswählen

ORDER BY column_position;
war ganz sicher gemeint

Code: Alles auswählen

ORDER BY position;
Dann aber kommt zu dem von mir angegebenen user defined type eine Liste mit den Attribut-Namen, deren jew. Position in dem Typ sowie ihr eigener Typ raus. Das ist schon mal ganz grossartig!!!

Habe, glaube ich, auch im Grossen und Ganzen verstanden, wie diese Query funktioniert. Beim Ausprobieren an meinen vorhandenen user defined types sind mir noch zwei Phaenomene aufgefallen:

1. Wenn ein Attribut einen Array-Type hat, dann erscheint hinter dem Type-Namen "[]". Gaebe es vielleicht eine Moeglichkeit, diese Information irgendwie in der Ausgabe explizit darzustellen, also etwa in einer weiteren Ausgabespalte "is_array", deren Wert true oder false ("t" oder "f" korrekt?) waere? Im Notfall koennte ich das natuerlich durch Testen des Typnamens auf "[]" am Ende des Namens rauskriegen. Das erscheint mir aber nicht besonders elegant.

2. Ich habe auch mit einem user defined type zu tun, bei dem es sich um einen enum type handelt. Wende ich die Query auf so einen Typ an, dann wird kein Output erzeugt. Offenbar wird diese Art von user defined types irgendwie anders in den Metadaten gespeichert. Wie koennte man diese Information gewinnen, also "Typ ist ein enum-Typ" und am besten gleich noch die definierten Enum-Werte desselben?

castorp
Erfahrener Benutzer
Beiträge: 1086
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von castorp » Mo 4. Sep 2017, 23:21

Enums sind anders abgelegt:

Code: Alles auswählen

select n.nspname as enum_schema,  
       t.typname as enum_name,  
       string_agg(e.enumlabel, ', ' order by e.enumsortorder) as enum_values
from pg_type t 
   join pg_enum e on t.oid = e.enumtypid  
   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by n.nspname, t.typname
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

apatwork
Neuer Benutzer
Beiträge: 5
Registriert: Mo 28. Aug 2017, 11:09

Re: Wie Metadaten zu Tabellen/Typen extrahieren?

Beitrag von apatwork » Mo 11. Sep 2017, 18:16

Sehr schoen! Das hat mir sehr geholfen. Vielen Dank!

Antworten