#18 Support for PostgresSQL inheritance

[Darwin_Baisa]

Hi all,
I am Darwin working as a Software Development Engineer
in Invensys Process Systems, India(head qurtered in
UK). We are developing and maintaining a
tool(confidential) which uses PostgreSQL as primary
database. It was not developed fully by us. It was
outsourced to us. Now we are analyzing its database. It
is very vast and unable to analyze it. We were
searching for a tool and we got schemaspy. And it is
really "very good" project.
But I want to suggest an enhancement. i.e
it(schemaspy) does not support PostgreSQL's inheritance
concept. Can we make shemaspy so that it can support.
Main idea is to generate diagrams, just like class
diagrams, so that we can easily understand which table
is inherited from which. I alredy had a discussion with
Jhon.

This is the link of our discussion(in help forum)
http://sourceforge.net/forum/forum.php?thread_id=1511889&forum_id=462850

Best regards,
Darwin Baisa

[John Currier]

Logged In: YES
user_id=1264584

Okay, I've attached a crude table metadata dumper. Here's
the command line that I used to run it against a MySQL db:

java -cp .;/mysql/mysql-connector-java-3.1.12-bin.jar
TableMetaDumper com.mysql.jdbc.Driver
jdbc:mysql://localhost/library myuser mypassword book

-cp is a java option to specify where to find the classes
(including the database driver). Use whatever you used when
running SchemaSpy and also point to the directory that
contains TableMetaDumper.class (the current directory, '.',
in my example).

Then comes TableMetaData, followed by the database driver,
connection string, user, password, table, and (optionally)
schema.

Your driver should be org.postgresql.Driver and your
connection string should be something like
jdbc:postgresql://<host>/<db>, where you substitute <host>
and <db> with appropriate values. Note that it does no
error checking, so if you get something wrong you get some
nasty stack traces...post them and I'll help you figure it
out if you need me to.

Let me know what it spits out when you run it against your
entity and object tables. I hope there's something in there
to help us figure out what's going on.

John

[John Currier]

TableMetaDumper

[John Currier]

Logged In: YES
user_id=1264584

Darwin, were you ever able to figure this out?

John

[Michael Krelin]

Logged In: YES
user_id=578710
Originator: NO

Just a passer by node. I do not think jdbc reports inheritance relationships from postgresql, but just in case I tried to create a couple of tables and run your dumper on it. No results but the columns of the table.

From the discussion thread I take it you're not quite familiar with inheritance concept, so I think it should be relief for you to learn that table inheritance doesn't introduce any referential constraints. Roughly speaking, it's a syntactical aid to create similar tables (roughly speaking! I know this isn't the whole truth, don't quote me on that:)) and to select stuff from all of them derived tables.

Just in case you wonder: http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html

Indeed, I would be disappointed if I used table inheritance and wouldn't be able to see it in schemaspy's reports. Luckily, I almost never used this feature ;-)

[John Currier]

Logged In: YES
user_id=1264584
Originator: NO

Thanks for the info Michael. Yes, you're correct...I had never heard of it before.

John