How to make a good documentation of a database ?
Hi !
I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.
I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another column...).
The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?
The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).
Does somebody know the right way to do this ?
Best regards,
David Pradier
--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98
The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).
It's a while since I've used them (just coming back to postgres after
enforced exile in mysqlville) but last I checked:
http://gborg.postgresql.org/project/dbutils/
Could generate UML diagrams from your DB. You might still be stuck
diff-ing them when you change the db, but at least they're prettier than
pg-dump.
Matt
Autodoc might be useful: http://www.rbt.ca/autodoc/
On Nov 18, 2004, at 8:31 AM, David Pradier wrote:
Show quoted text
Hi !
I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another
column...).The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).Does somebody know the right way to do this ?
Best regards,
David Pradier--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend
Well, yes, that's what I currently use.
The dot output is very interesting, but I guess the complete database
image will be 16 meters x 16 meters large when I have finished adding
the constraints.
I have already stopped to print it.
That's why I'd like to make something more 'paper-y' like 'The book of
my database', with lots of comments.
Or, I could insert the comments in the database itself so that autodoc
auto-documents them :-)
In fact, I have already looked a little into the tables of PostgreSQL itself
to see if I could hack it by adding a column "Comments" in the "table of
columns".
But I guess it isn't wise nor feasible, is it ?
On Thu, Nov 18, 2004 at 09:10:19AM -0600, Timothy Perrigo wrote:
Autodoc might be useful: http://www.rbt.ca/autodoc/
On Nov 18, 2004, at 8:31 AM, David Pradier wrote:
Hi !
I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another
column...).The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).Does somebody know the right way to do this ?
Best regards,
David Pradier--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98
On Thu, Nov 18, 2004 at 03:02:59PM +0000, Matt wrote:
The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).It's a while since I've used them (just coming back to postgres after
enforced exile in mysqlville) but last I checked:
http://gborg.postgresql.org/project/dbutils/
Could generate UML diagrams from your DB. You might still be stuck
diff-ing them when you change the db, but at least they're prettier than
pg-dump.
Thanks for the link, it's interesting :-)
Not totaly perfect, still :
I'll have to modify the schema of the database a lot during the next
months, so I'd prefer not to have to do a lots of diffing between the
database description and the 'comments documentation'.
Maybe the perfect solution doesn't exist yet ?
--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98
David Pradier wrote:
In fact, I have already looked a little into the tables of PostgreSQL itself
to see if I could hack it by adding a column "Comments" in the "table of
columns".
But I guess it isn't wise nor feasible, is it ?
You are familiar with:
COMMENT ON TABLE t IS 'this is my table';
And \d+
The comments get dumped with their associated object too.
--
Richard Huxton
Archonet Ltd
What we are doing here is likely not applicable to you, but I'll still
tell it:
- keep the data definition in an XML document, which includes all the
comments about all the tables/fields in the schema;
- generate both the database schema and the (HTML) documentation out of
this XML using style sheets;
- keep the XML in CVS for version control;
The HTML docs are also diffable between versions, but we don't keep them
in CVS as they can be readily generated from the version controlled XML.
When we have to update our schema, we update the XML data definition,
and regenerate the docs. We also have an XML schema which makes
table/field descriptions mandatory in the XML, so the busy developer
will not forget them.
Now it's clear that this needs a quite elaborate framework, and it took
some time for us to make it work, and it doesn't work always without
problems, but it is an option. For us the main reason to do it this way
was that we could abstract the XML such that we generate the schema for
Oracle and Postgres from the same data. But this "multi-db" part of it
is quite hard to achieve/maintain, and if you target just postgres, it
is somewhat easier.
HTH,
Csaba.
Show quoted text
On Thu, 2004-11-18 at 17:11, David Pradier wrote:
On Thu, Nov 18, 2004 at 03:02:59PM +0000, Matt wrote:
The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).It's a while since I've used them (just coming back to postgres after
enforced exile in mysqlville) but last I checked:
http://gborg.postgresql.org/project/dbutils/
Could generate UML diagrams from your DB. You might still be stuck
diff-ing them when you change the db, but at least they're prettier than
pg-dump.Thanks for the link, it's interesting :-)
Not totaly perfect, still :
I'll have to modify the schema of the database a lot during the next
months, so I'd prefer not to have to do a lots of diffing between the
database description and the 'comments documentation'.Maybe the perfect solution doesn't exist yet ?
On Thu, Nov 18, 2004 at 04:22:28PM +0000, Richard Huxton wrote:
David Pradier wrote:
In fact, I have already looked a little into the tables of PostgreSQL
itself
to see if I could hack it by adding a column "Comments" in the "table of
columns".
But I guess it isn't wise nor feasible, is it ?You are familiar with:
COMMENT ON TABLE t IS 'this is my table';
And \d+The comments get dumped with their associated object too.
No, I was not :-)
Wow, thanks Richard !
It's exactly what i wanted.
"Prepare to revive foul ghosts of foreign keys,
Prepare to die horrible doubts of the database pit,
Light is to be unleashed on you soon
and great is the relief shining on me."
David Pradier
--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98
dpradier@apartia.fr (David Pradier) writes:
I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another column...).
Would Druid (http://druid.sourceforge.net/) help?
Bill
--
Bill Harris
Facilitated Systems
http://facilitatedsystems.com/
David Pradier wrote:
The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).
You could use the postgresql's comment statement together with postgresql_autodoc. See the
following links for documentation and examples:
http://www.postgresql.org/docs/7.4/interactive/sql-comment.html
http://www.rbt.ca/autodoc/
http://www.rbt.ca/autodoc/output.html
http://www.rbt.ca/autodoc/autodocexample.html
http://www.rbt.ca/autodoc/output-graphviz.html
George Essig
Import Notes
Resolved by subject fallback
dpradier@apartia.fr (David Pradier) writes:
I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another column...).The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).Does somebody know the right way to do this ?
We use Rod Taylor's "postgresql-autodoc", which is a Perl script that
rummages through a database schema and generates output in several
forms.
The form that we're finding most useful is the HTML form, where it
generates an HTML table for each table, annotated with any COMMENTs on
tables/columns, as well as indicating linkages between objects.
The capabilities for generating "pretty pictures" are a bit limited,
but it does reasonable cross-referencing in the HTML form...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.
dpradier@apartia.fr (David Pradier) writes:
Well, yes, that's what I currently use.
The dot output is very interesting, but I guess the complete database
image will be 16 meters x 16 meters large when I have finished adding
the constraints.
I have already stopped to print it.
That's why I'd like to make something more 'paper-y' like 'The book of
my database', with lots of comments.Or, I could insert the comments in the database itself so that autodoc
auto-documents them :-)
In fact, I have already looked a little into the tables of PostgreSQL itself
to see if I could hack it by adding a column "Comments" in the "table of
columns".
But I guess it isn't wise nor feasible, is it ?
Not only can you put comments on tables, but you can put comments on
columns.
From the Slony-I sources:
comment on table @NAMESPACE@.sl_setsync is 'SYNC information';
comment on column @NAMESPACE@.sl_setsync.ssy_setid is 'ID number of the replication set';
comment on column @NAMESPACE@.sl_setsync.ssy_origin is 'ID number of the node';
comment on column @NAMESPACE@.sl_setsync.ssy_seqno is 'Slony-I sequence number';
comment on column @NAMESPACE@.sl_setsync.ssy_minxid is 'Earliest XID in provider system affected by SYNC';
comment on column @NAMESPACE@.sl_setsync.ssy_maxxid is 'Latest XID in provider system affected by SYNC';
comment on column @NAMESPACE@.sl_setsync.ssy_xip is 'Contains the list of XIDs in progress at SYNC time';
comment on column @NAMESPACE@.sl_setsync.ssy_action_list is 'action list used during the subscription process. At the time a subscriber copies over data from the origin, it sees all tables in a state somewhere between two SYNC events. Therefore this list must contains all XIDs that are visible at that time, whose operations have therefore already been included in the data copied at the time the initial data copy is done. Those actions may therefore be filtered out of the first SYNC done after subscribing.';
(@NAMESPACE@ gets transformed into a namespace name via a sed script;
use your favorite namespace as needed...)
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.
Hi,
I have set of create table statements. I need to execute them at one go
instead of running individual statement to create a table.
Help !
Thx
-----Original Message-----
From: Timothy Perrigo [mailto:tperrigo@wernervas.com]
Sent: Thursday, November 18, 2004 8:40 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to make a good documentation of a database ?
Autodoc might be useful: http://www.rbt.ca/autodoc/
On Nov 18, 2004, at 8:31 AM, David Pradier wrote:
Hi !
I've just inherited the responsibility of a postgresql database
of roughly 480 tables and 6460 columns, mainly without constraints,
not even foreign keys.I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another
column...).The best idea I could come with to do that was to maintain an output of
pgdump --shema-only, versioned with cvs, annotated with a patch, itself
versioned with cvs.
Not that bright, isn't it ?The problem is, I don't want to use a lot of time to maintain this
documentation, and above all, I'd prefer not to insert the information
twice (read: a new constraint in the database should automagically
update the documentation).Does somebody know the right way to do this ?
Best regards,
David Pradier--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98---------------------------(end of
broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Import Notes
Resolved by subject fallback
Thanks to everybody, Matt, Timothy, Richard, Csaba, Chris and George for
your help :-)
Finally, I think the right way to go for me is to generate an html
output with postgresql-autodoc and make it accessible to every
developer.
Thanks a lot for your help !
--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98
Nageshwar Rao wrote:
Hi,
I have set of create table statements. I need to execute them at one go
instead of running individual statement to create a table.
Help !
Nageshwar - two tips for posting to the mailing lists
1. Don't reply to an existing message with a new question
2. Make sure the subject line describes your question.
Otherwise, many people won't notice your question and you'll get less
people able to help.
As to the best way of executing multiple CREATE TABLE statements, that
depends on whether you are using psql or some other client. With psql
I'd put the statements in a file, then use something like:
psql -f my_commands.sql
psql < my_commands.sql
Or, if you're logged into psql you could use:
\i my_commands.sql
All covered in the man page for psql.
I always wrap the whole lot in BEGIN ... COMMIT so that if (usually
when) I make a mistake it rolls back all the changes.
HTH
--
Richard Huxton
Archonet Ltd
Yes, it seems interesting. (Is import of sql database possible ?)
But for now, I'll stick to postgresql_autodoc.
Thanks all the same, Bill, I'll try to keep an eye on this project.
On Thu, Nov 18, 2004 at 04:55:06PM +0000, Bill Harris wrote:
dpradier@apartia.fr (David Pradier) writes:
I'd like to make it a little more orthodox (lots and lots of
constraints, yeah !!), but I need a tool to make a documentation about
every column, at least, as some column are really vicious (like, they
are a foreign key to a table which depends on the type of another column...).Would Druid (http://druid.sourceforge.net/) help?
Bill
--
Bill Harris
Facilitated Systems
http://facilitatedsystems.com/---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.45.20.17.98
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
David Pradier <dpradier@apartia.fr> writes:
Yes, it seems interesting. (Is import of sql database possible ?)
But for now, I'll stick to postgresql_autodoc.
I think it may be. I've (at least) once, as a test, used it to document
an existing PostgreSQL database. I've also designed more than one
database in Druid and then exported the result to PostgreSQL.
Thanks all the same, Bill, I'll try to keep an eye on this project.
You're welcome.
Bill
- --
Bill Harris
Facilitated Systems
http://facilitatedsystems.com/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: For more information, see http://www.gnupg.org
iD4DBQFBolDl3J3HaQTDvd8RAqLTAJUTUi5JVuFgEG83CUmfjCPkJ5viAJ9/c7Rb
YXIGIjhZLiI1/jU6ijlviA==
=X+ai
-----END PGP SIGNATURE-----