Best free tool for relationship extraction

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 there an 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_constraints rc
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_constraints rc
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? Is there 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.

QED.

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? Is there 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