Thread: 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: > 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? > > http://archives.postgresql.org -- 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. 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'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 timea subscriber copies over data from the origin, it sees all tables in a state somewhere between two SYNC events. Thereforethis list must contains all XIDs that are visible at that time, whose operations have therefore already been includedin the data copied at the time the initial data copy is done. Those actions may therefore be filtered out of thefirst 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.
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
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? http://archives.postgresql.org
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
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/
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-----