I’ve written (and will open-source it once it’s finished; answers under MIT licence will be preferred, please indicate so if yours is one) a template for
postgresql_autodoc that allows me to generate shell script-parsable output for the database structure which I can then convert into TeX, escaping special characters as necessary. I plan on outputting both a table (similar to
postgresql_autodoc’s default HTML output, but more concise and especially more legible), and a database diagram which I’m told is some kind of ERD. The database documentation would be both available to developers (along with plain text output) and part of the PDF operation manual handed out to the customer (although we don’t think they’ll look at the database). My current point in implementation is the question how the Teχ source to be generated by that script has to look like to get the output to be what I wish.
I’ve looked around and found, of course, several methods to implement it, but none has so far been exactly what I envision. I’ve hand-drawn (in xpaint…) an example of roughly how it could look like:
My input is a number of database tables (name, list of fields with their names) and, for each field, whether it’s a primary key and/or a foreign key, and, for foreign keys, the name of the table and field it refers to.
This shall be output as a diagram, of up to
\linewidth, which dynamically resizes (in height) and rearranged (placement of the tables) to accomodate all tables and links, and so that the links are drawn automatically and with as few overlaps as possible, and with no links going over any tables.
A table is a box which basically contains a number of lines denoting their fields, and the name (ideally in inverse) on top. Primary keys are underlined (this is a convention, I do not use underlining in regular text).
Links are drawn as lines with arrows, from the box with the start table to the box of the destination table (unlike UML which reverses the direction, and only a bit like crow’s feet); they are simple arrows (we don’t know if they’re 1:1 or 1:n, and m:n relations are split up with their intermediate table explicitly visible, at the detail level we get as input data from the PostgreSQL dump).
Now, the important thing is: the links between tables always start and end at a vertical box line of a table, never one of the horizontal lines, and they are vertically placed to start/end at the field, not anywhere on the table. (This does mean that lines can merge, for example when several tables refer
users.pk there is only one arrowhead at the
users table on the
p̲k̲ field, but several lines going towards it, which may join somewhere in the middle. This is optional; if they don’t join, invent something to make the output not suck ☺)
I’ve once hand-drawn this ERD as UTF-8 text file:
This one obviously lacks the arrows (but has m:n relationships shown, as it’s design-first), but it shows the concept of merged lines quite well. Furthermore it employs a visible “tunnel” to indicate that, where lines cross, they don’t merge (between
SOURCES.VDEPS). Something like that would be cool to have as well ☻
My input data is, as I said, raw table names, field names, which fields belong to which tables, which fields are PK, which fields are FK and what they link to. I can literally generate any Teχ code imaginable, as I have a shell script to act as in-between, mostly because
postgresql_autodoc can only escape for HTML and our table names contain underscores.
I’ve first thought of graphviz because it does the laying out of the “nodes” (tables) and lines extremely well, and to just embed its output (PNG if must be), but that doesn’t work because graphviz tends to expand the diagram both horizontally and vertically, while I’ll need this to be part of a page (or several pages — if needed, the diagram may span multiple pages) of the documentation (operation manual), so I ended up recherching a pure LaTeX or Tikz/PGF solution.
The operating environment is: pdflatex on Debian unstable (TeXlive 2016), with roughly these packages loaded (the actual project is internal and has a few more lines to this, but I’ll open-source the non-customer/project-specific part of this). The font’s non-free, but the 500 size can be found online (the pdflatex glue for it to use the font is part of the git repo, in the same directory), and it’s not necessary for the font to be the same in the diagram (although would be agreeable).