Relationships for large databases

[rorymcdaniel]

I'm trying to reverse engineer a MySQL database that contains 262 tables. Generally speaking, schemaspy has been incredibly useful. However, the relationships page is almost unreadable at points because all the relationship lines are bunched together, making it impossible to see what is connected to what. The image it generated is approximately 3500x20000, which is at the same time awesome and insane. Is it possible to force schemaspy to make the schema wider instead of taller, and then hopefully make these relationship lines easier to read? I'm using version 6RC2. I've attached a partial screenshot as an example of the scenario I'm trying to improve.

[rafalkasa]

@rorymcdaniel relationship diagram for medium size and bigger databases is not looking quite readable. It will be good to improve this somehow but extra investigation will be nesesery to find the best solution.

Thank you very much for creating the issue and using SchemaSpy

[thebf]

I suggest looking at www.ryandesign.com/canviz/ it seems to handle wider layouts when displaying DOT files.

Client side filtering of tables might improve the experience when working with large tables.

Being able to have multiple filter sets defined as fragments in the relationships page could also enhance the experience but would require extra work during the creation of a document.

[danwatt]

~ 600 tables on the main relationship graph. I had thought about color coding at one point years ago, but that probably would not scale well past 16 or so colors

[thebf]

I was thinking along the lines of highlighting with color when mousing over a line/connector. How did you get such a wide graph?

[rafalkasa]

I'm investigating the JS library that we can use for create diagrams. Unfortunately I found few very interesting but most of them are commerce solutions and pricing is quite high.

Maybe do you have some proposition please share your ideas.

[danwatt]

This is a weird schema. There is one table that is linked to by 60 other tables. There are other, weirder relationships. Its unweildy, but even with all of the overlapping lines, Schemaspy has been a great tool to use, maybe just not when looking at the big image. Clicking through to a single table is more helpful.

width: 3509px;
height: 16204px;

If command-line graphviz was ditched and the graph was rendered on the fly, or something could be done with SVG, that seems like a cool option.

[danwatt]

It looks like some people have made progress porting Graphviz to JS (that is, its probably converting the executable into javascript code and emulating)

http://www.webgraphviz.com/ https://dreampuf.github.io/GraphvizOnline/ http://viz-js.com/

[npetzall]

I've done testing with one if them as mentioned on gitter, but the result was broken. graphviz-java. It was also very very slow. But that was to generate from dot to svg.

The canviz still rely on graphviz to generate xdot.

I've been googling for something using d3.js

Don't forget #120

[bitwisecook]

Because I wanted something quick and easy that helps at least highlight relations I hacked this up:

#!/bin/bash

set -e

for f in diagrams/*/*.svg
do
  echo "fixing $f"
  sed -i.bak -e 's@<g id="graph@<defs><style>g.edge.edgeOn, g.edge > path.edgeOn, g.edge > polygon.edgeOn, g.edge > ellipse.edgeOn { stroke-width: 3; stroke: #EEEE00; } g.edge:hover, g.edge > path:hover, g.edge > polygon:hover, g.edge > ellipse:hover { stroke-width: 3; stroke: #EE00EE; }</style></defs><g id="graph@' -e 's@<\/svg>@<script type="text/javascript"><![CDATA[function toggleClass(allStates, thisState) { let breakOut = false; if (thisState.classList.contains("edgeOn")) { breakOut = true; } allStates.forEach((el) => { el.classList.remove("edgeOn"); [].forEach.call(el.children, (child) => { child.classList.remove("edgeOn") }); }); if (breakOut) { return } thisState.classList.add("edgeOn"); [].forEach.call(thisState.children, (child) => { child.classList.add("edgeOn") });}let allStates = document.querySelectorAll("g.edge");document.addEventListener("click", function (e) { for (var target = e.target; target \&\& target !== this; target = target.parentNode) { if (target.matches("g.edge")) { toggleClass(allStates, target); } }}, false);]]></script></svg>@' $f
  rm $f.bak
done

It simply adds a bit of CSS to highlight on hover and JS to toggle an edgeOn class that will highlight the edge in yellow. It's enough for my immediate need but certainly there's better ways to do it.

One thing I would say is it may help a bit if instead of using <object> to embed the svg, inlining is used instead. It'd make manipulating the CSS for the diagrams much easier and less hacky.

[yassine-safraoui]

any progress on this topic? I'm facing the same issue with a Moodle database, I think the best solution would be to show a label containing information like the table names of the relationship and the attributes of the relationship when hovering on the edge, and change the color of the edge when selecting it(clicking it) would also help I think, 7 years ago you guys discussed that libraries wouldn't allow this kind of behavior, is it still the case? I did some quick search and found that vizjs supports what I'm suggesting( see chosen.label here), and from what I see you're using vizjs right?

[npetzall]

Sadly no, the post above yours is probably the best so far, I had missed it.

Devil is in the details you've linked to visjs and not vizjs. They are different vizjs is a port of graphviz to JavaScript and visjs is it's own framework.

If we were to take a direction I would think that the changes above are the way to go. Create svg and add svg libraries for pan/zoom, highlight and possibly hide/collapse.

But that would take some effort but we are always open to contributions.