Documenting Database Tables

[gslindstrom]

Hello,

I am really enjoying learning Sphinx and want to use it to document a
system we are writing for a large client. We use the Postgres
database and I would like to have documentation included for each
table. Is there a way to pull in documentation similar to how we can
pull in docstings from Python modules (if not, I may look into writing
such an extension)?

Is there a standard way to document database schemas (including stored
procedures)? It's a large part of the application and I'd like
include it with the rest of the documentation.

I'd also like to thank Brandon Rhodes for his fine video tutorials on
Sphinx (in particular, from PyCon 2010). I watched it over the
weekend to learn how to do all sorts of new things.

Thanks for all your work!

Greg Lindstrom
Novasys Health
Little Rock, Arkansas

[jmu]

Hi,

I've started to write an extension that parses the SQL file into RST
tables.
However, the extension is not released yet as I had some problems with
the sqlparse (http://code.google.com/p/python-sqlparse/) limitations
at the time. The existing material:

Documentation: http://jmu.koodiorja.com/static/rusty/doc/sqltable.html
Code: http://bitbucket.org/jmu/rusty/

I'm hoping to release the extension some time in a future.

[werner]

On 07/04/2010 13:32, jmu wrote:
> Hi,
>
> I've started to write an extension that parses the SQL file into RST
> tables.
> However, the extension is not released yet as I had some problems with
> the sqlparse (http://code.google.com/p/python-sqlparse/) limitations
> at the time. The existing material:
>
> Documentation: http://jmu.koodiorja.com/static/rusty/doc/sqltable.html
> Code: http://bitbucket.org/jmu/rusty/
>
> I'm hoping to release the extension some time in a future.
>

Did you consider using SQLAlchemy (SA) instead of raw SQL?

Werner

[jmu]

> > However, the extension is not released yet as I had some problems with
> > the sqlparse (http://code.google.com/p/python-sqlparse/) limitations
> > at the time.
>

> Did you consider using SQLAlchemy (SA) instead of raw SQL?
>

Actually, I didn't.

My use case is about documenting the database from the raw DB creation
script file. But if the SQLAlchemy is able to parse the file as well
(with the nice addition of retrieving the info from actual DB as well)
then certainly I should consider it. Thanks for the tip.

[werner]

While you can "feed" raw SQL to SA that would not be how one normally
uses it.

Wouldn't it be better to document the database itself?

Here a very very very basic script to read the db (defined in dburl) and
extract the table definition.

engine = db.sa.create_engine(dburl, encoding='utf8', echo=False)

meta = db.sa.MetaData()
meta.reflect(bind=engine)
for table in reversed(meta.sorted_tables):
print table
print 'primary key'
for key in table.primary_key:
print key
print 'foreign keys'
for key in table.foreign_keys:
print key
print 'columns'
for col in table.columns:
print col, col.type, col.description

Showing the result of just one table:
conseventit
primary key
conseventit.conseventitid
foreign keys
ForeignKey(u'bottag.bottagid')
ForeignKey(u'consumption.consumptionid')
ForeignKey(u'rating.ratingid')
ForeignKey(u'cbbottle.cbbottleid')
ForeignKey(u'consevent.conseventid')
columns
conseventit.conseventitid FBInteger() conseventitid
conseventit.tagno FBString(length=32, convert_unicode=False,
assert_unicode=None) tagno
conseventit.fk_conseventid FBInteger() fk_conseventid
conseventit.fk_cbbottleid FBInteger() fk_cbbottleid
conseventit.fk_bottagid FBInteger() fk_bottagid
conseventit.fk_consumptionid FBInteger() fk_consumptionid
conseventit.created FBDate() created
conseventit.updated FBDate() updated
conseventit.fk_ratingid FBInteger() fk_ratingid

Werner

[jmu]

> While you can "feed" raw SQL to SA that would not be how one normally
> uses it.
>
> Wouldn't it be better to document the database itself?

Thanks for the handful example. Indeed, I can see two approaches here,
which both would be nice to have implemented. The benefits of using
raw SQL instead of live DB are not being dependent on running database
(document building server may not have access to it, for example) and
the possibility of using comments in the query file, like:

-- This is table is used by:
--
-- * blah
-- * blah
--
-- .. NOTE:: This is a note
--
CREATE TABLE foo (
id int(11) NOT NULL AUTO_INCREMENT,
bar_id int(11), -- Reference to table ``bar``
);

But then again, your example shows how easy it is to fetch data from
SA powered db connection. Makes one tempted to write a directive from
it :)

-Juha

P.S. I wonder how fast will the NoSQL solutions retire these efforts :)