2/4/2017: ER Diagram hell

I do a lot of database design work. It’s not HARD database design work. I’m just involved in a whole lot of projects, both in and out of the office, that require me to write a bunch of DDL.

For all the explosion of open source development tools lately, I am absolutely SHOCKED that there aren’t reasonable tools available for taking either DDL or going to a database directly and reverse-engineering a normal database diagram. It’s amazing to me.

Sure, you can get yEd, Dia, or any other diagramming tool and do it yourself. But a hand-drawn ER diagram strikes me as being much the same as software documentation: Lies. They’re written once out of a management directive to have a pretty picture/piece of paper. Then they’re filed away someplace and the underlying structures and or code begins IMMEDIATELY to diverge from the documentation, making the documentation worse than no documentation.

I’ve been playing around with Graphviz/dot for an awfully long time now and finally decided that it was time for me to write something that would generate dot graph source from more or less normal looking ddl. Sadly the code is in python instead of something sensible like perl (which was literally born for this kind of task.) But we don’t use perl in the office and they’ll get all kinds of pissy if it starts showing up in the source control system.

About an hour ago I finally got to the point where it would draw pictures of tables with column layouts and associations between tables. It’s not using real referential integrity DDL to generate the connections though. I’ve just given it a syntax that will allow me to put “table1.column1 -> table2.column2” notation in SQL comments that it will pick up. This way I can take the DDL file and run it directly into the database AND generate a diagram at the same time.

I’m not sure how I’m going to reconcile it to the (currently completely nonexistent) build/release system we don’t yet have. But I’ll find a way.

The biggest issue with that, I think, is going to be managing the transactional DDL. (i.e. alter table statements.) I may just bail on that entirely.

So I’m going to show a couple people this “cool new toy” at the office tomorrow.

They’ll nod and say “neat”. A couple of them will ask if I can put the source up into our tools directory.

What I’m NOT going to tell them is that I’m taking THIS code, and modifying it from here so that it generates a fully-featured suite of ORM model classes.

What?

You’re not a real programmer unless you’re lazy enough to write code that’ll write your code for you.

Wait ’til they find out all the ETL is generated source.

w00t

(hmm…maybe I should generate the code for the python-based generator using a perl script. Now THAT might just be able to keep me interested.)

Leave a Reply

Your email address will not be published. Required fields are marked *