Обсуждение: [GENERAL] Queries for Diagramming Schema Keys

Hi,

 

I'm trying to write an application (using libpqxx/C++) which creates graphical images of large and complex relations between tables (basically an SVG image with hot spots for drilling down on details). I need to essentially create icons of tables and their columns, and then draw interconnecting lines where foreign keys are involved, and to distinctly label primary keys, foreign keys, and non-key columns. Eventually this will have knowledge of an XML file loading scheme and be able to reverse engineer the required load order (imagine approximately 1,000 tables with many foreign keys and file loads which may take hours for each failure to load). I need some advice on using ANSI/information_schema queries to accomplish this.

 

Thus I have this query to list all tables:

SELECT table_name
  FROM information_schema.tables
WHERE table_schema='public'
   AND table_type='BASE TABLE';

 

...this seems to work ok. The next query is to find all foreign keys...this seems to sort of work, but may have issues:

SELECT
    tc.table_name AS local_table,
    kcu.column_name AS key_column,
    ccu.table_name AS fk_table,
    ccu.column_name AS fk_column 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
IN (SELECT table_name
  FROM information_schema.tables
WHERE table_schema='public'
   AND table_type='BASE TABLE');

 

This is my query to find all primary keys which are not foreign keys, and this definitely is not 100% correct:

SELECT DISTINCT
    tc.table_name AS local_table,
    kcu.column_name AS key_column
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name
IN (SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE')
AND (tc.table_name, kcu.column_name)
NOT IN (
SELECT
    tc.table_name,
    kcu.column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
IN (SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE')
)
ORDER BY local_table, key_column;

 

I am completely at a loss how I would query for all columns which are neither primary nor foreign keys. Would anyone have a suggestion for something like this:

SELECT table_name, non_key_column

FROM ...

WHERE ...

ORDER BY table_name, non_key_column

 

Any advice on any of the queries would be appreciated!

 

Thanks!

...

> Just to add that running psql with the -E switch is REALLY handy for seeing how psql executes queries to

> find how tables etc are put together.

 

I can't actually use that feature to gather the information I'm interested in since all I have are tables and data with no formal information on key relations and allowed load order. There is so much data in so many tables that testing even a single load iteration takes many hours and there are literally many thousands of load order combinations possible. Logs of hundreds of thousands (or millions) of XML loads would take a very long time to go through, and would then only give one possible load order.

 

Automating a diagram of key relations and visualizing it is the first step to methodically computing a correct load order, but I can't do that until I figure out how to use the system tables to describe (1) columns which are not keys, (2) columns which are primary keys not referring to another column, and (3) columns which are foreign keys and the table/column they are pointed at. My SQL knowledge is somewhat limited and I am struggling with the system tables.