3

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:

ERD mockup

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:

ERD in UTF-8

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 CSYMS.CSYM and 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).

closed as too broad by CarLaTeX, Stefan Pinnow, Phelype Oleinik, JouleV, Schweinebacke Apr 22 at 7:18

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please provide an MWE and explain what particular problem you hope to get help with. I didn't read all of your question as it mostly seemed irrelevant and meant little to me. I couldn't get any sense of what you were having trouble with specifically in PGF/TikZ, which I guess (though I'm not sure) is what your question is meant to be about. – cfr Aug 7 '16 at 0:35
  • @cfr I have a problem getting started at all, i.e. get any idea how this can be achieved. I have the raw input data, and a means to make it look like Teχ, but I have no idea how the Teχ code should look like to achieve what I want. – mirabilos Aug 7 '16 at 21:37
  • 1
    As it stands, I think it very unlikely you are going to get a useful answer. You might, if somebody has a really top-notch crystal ball, but many of us can only afford second-rate models, I'm afraid. I really have no idea what you want to know from us. What exactly do you want to know? To get started with TikZ, look at the tutorials at the beginning of the manual. If that's not the question, what is? People are not likely to follow half a dozen links to reconstruct your entire project. What's the bit you think we can help with? The rest is just irrelevant. – cfr Aug 7 '16 at 21:54