Data Dictionary generators for PostgreSQL to Confluence Wiki

Question

I'm looking for a tool that takes PostgreSQL tables and outputs a Data Dictionary in a wiki format (preferably Confluence). It seems like most tools out there require a lot of manual work/multiple tools to accomplish this task (IE> SchemaSpy, DB Visual Architect, Confluence plugins to take outputted HTML DD and convert to Confluence). I'm looking for ONE tool that will scan my Postgres tables and output a wiki friendly Data Dictionary that will allow seamless maintenance as the DB changes, without having to update my database and the DB schema in the other tool.

Smithers

It seems there's no easy answer. But you can get YAML output of your DB structure with Pyrseas, or XML output with SchemaSpy. All the rest is "yaml to wiki" or "xml to wiki" converter... Not a rocket science. – Amazonas

Actually I can write such yaml-to-wiki converter if you hire me :-) – Amazonas

What you mean by wiki format? Do you need a single page with a Header1 per-table, Header2 per-type? Or do you need a list of tables, each table pointing to a dedicated page? – Legpull

Answer 1

There is the Bob Swift's Confluence SQL Plugin that allows you to display data derived from a SQL query in a Confluence Page .. e.g. as a table .. perhaps it is worth a look for you?

Confluence versions 3.1.x - 4.9.x are currently supported...

The plugin is free and can be downloaded from Atlassian's Plugin Exchange: https://plugins.atlassian.com/plugins/org.swift.confluence.sql

Additional information about the plugin can be found here: https://studio.plugins.atlassian.com/wiki/display/SQL/Confluence+SQL+Plugin

Ollayos

Answer 2

I think you'll have to script this yourself, but it's pretty easy and fun. I'll assume Python here.

I like the Confluence XML-RPC interface. For that, see http://goo.gl/KCt3z. The remote methods you care about are likely login, getPage, setPage and/or updatePage. This skeleton will look like:

import xmlrpclib

server = xmlrpclib.Server(opts.url)
conn = server.confluence1
token = conn.login(opts.username, opts.password)
page = conn.getPage(token,'PageSpace',page_title)
page = page + table
page = conn.updatePage(token,page,update_options)

table here is the data from PG tables. We'll build that below.

For pulling simple data from PostgreSQL, I use psycopg2 most often (also consider SQLSoup). Regardless of how you fetch data, you'll end up with a list of rows as a dictionary. The database part will probably look like:

import psycopg2, psycopg2.extras

conn = psycopg2.connect("dbname=reece")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute('SELECT * FROM sometable')
rows = cur.fetchall()

Now you need to format the data. For simple stuff, print= statements will work. For more complicated formatting, consider a templating engine like jinja2 (http://jinja.pocoo.org/). The rendering code might look like this:

from jinja2 import Template
template = Template(open(template_path).read())
table = template.render( rows = rows )

The file template_path will contain the formatting template, which might look like:

<table>
<tr>
  <th>col header 1</th>
  <th>col header 2</th>
</tr>

{% for row in rows|sort -%}
<tr>
  <td>{{row.col1}}</td>
  <td>{{row.col2}}</td>
</tr>
{% endfor %}

</table>

Note: Confluence no longer uses the wiki markup by default. You should be writing HTML.

Finally, if you want to make a page for all tables, you can look at information_schema, which contains information about the database as tables. For example:

select table_name from information_schema.tables where table_schema = current_schema();

Alarmist