Thread: Best free tool for relationship extraction

I need to determine relationships between several tables. Is there a free tool to extract these from catalog tables? Is there an SQL that also does this?

Thanks
Alex

Sent from my smartphone

    AG> I need to determine relationships between several tables. Is there a
    AG> free tool to extract these from catalog tables? Is there an SQL that
    AG> also does this?

https://metacpan.org/module/DBIx::Class

It can't figure out m2m's though, for obvious reasons.

--
<Polytope> tetris is so unrealistic

From: Alexander Gataric [mailto:gataric@usa.net] 
Sent: Thursday, September 13, 2012 12:52 PM
To: pgsql-general@postgresql.org
Subject: Best free tool for relationship extraction

I need to determine relationships between several tables. Is there a free tool to extract these from catalog tables? Is
therean SQL that also does this?
 

Thanks
Alex


Try this SQL:

WITH RECURSIVE FK_recursive(distance, child_table, parent_table, FK_constraint_name, unique_constraint_name,
                                      ON_DELETE, ON_UPDATE, is_deferrable, FK_path) AS (
    SELECT 1, tc.table_name, ctu.table_name, ctu.constraint_name, rc.unique_constraint_name,
           rc.delete_rule, rc.update_rule, tc.is_deferrable, quote_ident(ctu.table_name)
    FROM information_schema.constraint_table_usage ctu, information_schema.table_constraints tc,
information_schema.referential_constraintsrc
 
    WHERE ctu.table_name = 'gp_part_space' and ctu.table_catalog = 'vector'
      and tc.constraint_name = ctu.constraint_name and tc.constraint_type = 'FOREIGN KEY' and tc.constraint_catalog =
'vector'
      and ctu.constraint_name = rc.constraint_name
  UNION ALL
    SELECT er.distance + 1, tc.table_name, ctu.table_name, ctu.constraint_name, rc.unique_constraint_name, 
           rc.delete_rule, rc.update_rule, tc.is_deferrable, er.FK_path || ' <- ' || quote_ident(ctu.table_name)
    FROM FK_recursive er, information_schema.constraint_table_usage ctu, information_schema.table_constraints tc,
information_schema.referential_constraintsrc
 
    WHERE er.child_table = ctu.table_name and ctu.table_catalog = 'vector'
      and tc.constraint_name = ctu.constraint_name and tc.constraint_type = 'FOREIGN KEY' and tc.constraint_catalog =
'vector'
      and ctu.constraint_name = rc.constraint_name
  )
SELECT distance, child_table, parent_table, FK_constraint_name, unique_constraint_name, 
       ON_DELETE, ON_UPDATE, is_deferrable, FK_path || ' <- ' || quote_ident(child_table) AS FK_path
  FROM FK_recursive ORDER BY distance, parent_table;

If you get an error like this (possible on 8.4.5):

ERROR:  operator is not unique: smallint[] <@ smallint[]
LINE 1: select $1 <@ $2 and $2 <@ $1
                  ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
QUERY:  select $1 <@ $2 and $2 <@ $1
CONTEXT:  SQL function "_pg_keysequal" during inlining

Then recompile the function:

SET search_path TO information_schema;
CREATE OR REPLACE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';
SET search_path TO public;


And then re-run original recursive query.

Regards,
Igor Neyman

On Thu, 13 Sep 2012 11:51:42 -0500 "Alexander Gataric" <gataric@usa.net> wrote:

> I need to determine relationships between several tables. Is there a free tool to extract these from catalog tables?
Isthere an SQL that also does this? 

I've gotten good results with schemaspy.

--
Bill Moran <wmoran@potentialtech.com>

I usually just forget her birthday, eye color, or name.  Then she extracts herself from the relationship.

On Thursday, 13 September 2012 18:51:48 UTC+2, "Alexander Gataric"  wrote:
> I need to determine relationships between several tables. Is there a free tool to extract these from catalog tables?
Isthere an SQL that also does this? 
>
> Thanks
> Alex
>
> Sent from my smartphone

You can try DbVisualizer at www.dbvis.com, even the Freeware Version is good.

Johan Nel
Durban, South Africa.

On 09/13/2012 06:51 PM, Alexander Gataric wrote:
> I need to determine relationships between several tables. Is there a
> free tool to extract these from catalog tables? Is there an SQL that
> also does this?
>
> Thanks
> Alex
>
> Sent from my smartphone
>
I'm using http://squirrel-sql.sourceforge.net for those kind of things.

Peter

--
Peter Hopfgartner
web  : www.r3-gis.com