OO component for OG? per-line magnets? and ERDs.

Sun Aug 10 18:59:15 PDT 2003

Hi everyone. I'm a new subscriber to both lists, though I've read a lot of
them in the past via the web archives. Sorry for the crosspost, but I'm making
another one of those pleas for integration, so it seems to make sense. :)

I'm trying to automatically make ERDs of MySQL databases in OmniGraffle. I
have gotten a decent way so far, but I'm at a crossroads. I currently
have it making tables, but not magnets or connecting my tables' keys... yet.

I have read the OG mailing list very thoroughly and would like to ask a
few questions and share my progress regarding this (MySQL -> OG) issue. Sample
PHP scripts are available on request; I'll try to post them to a page when I
have things a little more cleaned up.

If you have downloaded the entire omnigraffle-users archive (mbox format!
kudos Omni! ;), you might care to reference the following item numbers:

5, 84, 95, 98, 255, 311, 324, 327, 328, 330, 382, 484, 620, 803, 1296, 1299,
1300, 1301, 1329, 1471, 1473, 1564, 1727

These are all the emails I thought were pertinent and in which people
mentioned mapping ERD's from databases, or some similar pursuit.
(I have also tacked on names and dates when referencing email from the list.)

* * *

There are three ways I can imagine going forward with my magnet issue, and of
course, the best one doesn't exist yet.  <grin>

So, I'd like to make the mother of all feature requests and suggest a
'mini-OmniOutliner' object for OmniGraffle that could:

1) Be a normal, scalable, rectangular OG graphic, but with its own floating
inspector palette (containing all the OO specific stuff), and maybe keyboard
shortcuts (not sure about overlap on those);

2) Allow magnets, one per row of the Outliner document, or one on each side
(per row), etc; and

3) Have multiple columns, just like OO files can.

and optionally:

4) Have disclosure triangles, which could (optionally) rescale the graphic it
lived inside of (when expanded / collapsed), OR maybe an optional scrollbar
(too space-consuming in some instances?) instead.

One ramification of #2 and #4 above: what happens if a child
node has a magnet attached, a line connected to it, and its disclosure
triangle is collapsed?

I would suggest drawing a dotted line ending (...) at the bottom of the
(still-visible) parent's bounding box.. signifying that the line connects to
something contained within the outline object, but not presently visible.

e.g.

Open
	|
	|V Parent Node
--------------->Child Node

Closed
	|
	|> Parent Node
__. . .	|  (signifying something's there but you can't see it)

If the parent was also not visible, kick it up the chain to IT's parent, etc.

OR, if both ends of a line were attached to hidden child nodes, perhaps the
line would fade / grey out / dash / disappear, etc. Ahh, Preferences.

If a line was attached to a specific row of an OmniOutliner component that
contained a scrollbar, I can think of a number of behaviors for when it
scrolled off the screen. You could have all of the lines 'stack up' at
the bottom / top of the component as their rows scrolled out of view, and/or
have lines that were a certain distance away automagically 'fade' in and out as you
got nearer to/farther from them.

Cool Example
------------
http://www.unithom.com/omnistuff/history_example.png

In my example above, there is a tall vertical bar on the left and a 'data
grid' (OmniOutliner document, embedded) on the right.

The tall bar on the left is a 'zoomed out' view of history, a 'big picture'
timeline. It has a series of magnets attached and is labeled at important
years and dates. The 'data grid' on the right is an OmniOutliner component
with specific rows and more detailed information about those dates. As the
user scrolls the OO component up/down, lines stack or fade in/out between the
two timelines.  Or, you could attach an applescript to the pieces of the
timeline so that when they are clicked / rolled over, they auto-scroll the OO
component to the date they're attached to.

(Yes, I realize that this presents ugly snafus like, "what if I drew lines
from one magnet on the normal object to multiple columns in the OO
component?" But this is just an example of some really cool functionality
that came to mind. Your Implementation May Vary. ;)

The lines above and below the visible range represent other items not visible
in the OO object (at present), while the one coming from about 1345 is a
hidden 'child node' of the 1295 Marco Polo node, and is therefore not visible
either... hence the black (it's in range) but dotted (it's not visible) line.
I've tried to also simulate the items farther up and down the timeline 'fading
out' (becoming more transparent or grey, etc) as one scrolls farther away from
them.

I think that Cyprien Lomas (#1564, 2003-06-09) had a very similar idea that
could be very well handled by such a component... dynamically. Woot!

5) Have the capability to link to a live OO file's data. Of course.. if you're
going to ask for the moon, etc.  ;) I believe that Robin, a.k.a. Erithacus
rubecula (#1033, 2003-03-19) said it pretty well already (and every few
months. :)

6) As Ziya Listera (sp?) wrote on 2002-07-02 (#620), "You mean like the note
functionality in OmniOutliner, hint hint :-)" -- the note portion of a line
could be set to pop up like a tooltip, perhaps, if you rolled over or clicked
the row it was associated with. Or, it could drop down below just like it
presently does in OO. Or even an optional layer, though that doesn't seem
suited to the integration I'm proposing here.

* * *

I realize I probably sound like a total nut just coming out of the blue and
saying, "Hi! I just joined the mailing list! Can you please roll one of
your products into another for me, please?" but I'll try to justify below why
I have arrived at this being the best solution that I can see.

I'm also aware that others have asked for OO functionality inside of OG, and
have seen responses such as Greg Titus' (#5, 2001-12-17) which stress keeping
the two programs separate. Well, has anyone seen Photoshop and Illustrator
lately? Does Director allow you to import Flash objects? etc. They didn't
start that way, but eventually got to that point. Are we getting close to that
point at Omni? OG 4.0 Pro perhaps?  ;)

Besides, I think that John Oram (#330, 2002-03-07) was right on the money
about his improvement suggestions to both OO and OG. He's got some wild ideas,
but they (and the graphics backing them up) are pretty amazing. I mean, tell
me honestly that you, a red-blooded Omnipatriot, can look at
http://oram.com/omni/grid.png without tearing up just a little bit...?

I would propose *integrating* the component, rather than requiring an
*import* as so many others have proposed... an 'import' suggests a frozen
state, a snapshot. Live exchange is dynamic. (Ever watch an open file in
BBEdit when you change it somewhere else? Booyeah.)

* * *

Here is what I have already got working so far on my MySQL to ERD stuff:

1. PHP asks MySQL for table metadata (schema) and creates an array of
pertinent information. (Based heavily on PHP PEAR DB DataObject class'
'Generator.php' or 'createTables.php' files. See
http://pear.php.net/manual/en/package.database.db-dataobject.intro-autobuilding.php
for more on this.)

2. I use a skeleton xml template made from an Omnigraffle document, but with
placeholders (e.g.  "{LOCATION}" gets replaced by the Bounds tag's string)
and blocks (e.g. "") in it.

I'm using a multitext object (UML 'Class Name / Operations / Attributes') to
hold my table info (title / primary keys, one per line / other keys, one per
line). I believe Alex Rice (email #84, dated 2002-01-19) was using a
multitext object from this palette too to make ERDs... it's handy, but sadly,
multitext objects do not presently export properly to Visio XML. (This is bug
8740 in Omni's database.) Don't even ask me how I'd propose exporting an
OO component's contents to Visio, as I know next to nothing about it. (Does
Visio have the ability to embed some M$ outliner COM object in it or whatever?
:)

3. Each of the three sections of each UML graphic in my template has a
corresponding placeholder, {TITLE}, {PRIMARY_KEYS}, and {OTHER_KEYS}. The
entire UML graphic itself is stored in the repeatable 'table' block so I can
make as many of it as I need, one per DB table.

I then use the PHP PEAR project's HTML IntegratedTemplate class to swap in
info about my tables. For more information see
http://pear.php.net/manual/en/package.html.html-template-it.intro.php

4. Finally, I run the PHP script and redirect its output to dbname.graffle. I
open the newly created file in OmniGraffle. Voila, instant tables, populated
with their names, primary keys, and other keys.

That isn't bad for a start, but as a 'lazy' programmer, I am sworn to waste
10x as much time trying to perfect this thing as I would have simply making
the ERDs by hand! Besides, it's cool. ;) That, and I have probably spent about
five hours just researching and composing this whopper of an email (and
preparing that schmancy example) today!

Now, I'm trying to add two magnets per (relevant) line of text -- one on each
side of my shape -- for each primary key or foreign key in my DB. After that,
I will try to automatically connect foreign keys in tables to the primary keys
of the tables they are linked to. Finally, I plan to experiment with
hierarchical or maybe force-directed graph layout to see if I couldn't at
least start the layout process. But that's a ways off yet. I also might try to
implement Andrew Shugg's suggestion (#1735, from today, 2003-08-10 ;)  of
performing auto-layout automatically on open.

Side note: Bob Russell mentioned in his email (#327, 2002-03-06) about using
naming conventions to identify links between tables. I can't speak to
FileMaker, as I don't use it much, but a very similar solution that comes to
mind is the PEAR DB DataObject class I'm using -- it can *either* try to
determine foreign key links by a table's column names (e.g.
thistable.foreigntablename_foreignkeyname) or you can set up a separate file
that describes all of those linkages for it.) See the link I pasted to it
above for more information. Also, newer versions of MySQL support some foreign
key constraints when creating tables, but I have not gone into that much yet
either.

So anyway, I'm on the 'slapping magnets on my primary/foreign keys' step.
However, having added some magnets by hand in OG and opened the file in
BBEdit, it looks like magnets are still only stored per-shape, not per-line.
(Note: not to be confused with Kenneth Prager's (#802, 2002-09-18) suggestion
of 'line magnets' ;) I'm referring more to Tom Wiebe's email (#311,
2002-03-02).

I concluded this because in the .graffle XML file, I saw that any returns
between lines are just backslash-escaped, RTF-style newlines. This suggests to
me that per-line magnets are probably not supported yet. (Am I right? Please
say no! ;) The magnets are also stored in a section of the shape's main
definition in the XML file, not interspersed between subsections of the text
items it contains, etc.

My only other thought on how to do this (at present) would be to create my own
groups of shapes, by making a box and a number of individual text objects (a
title, and then one per mysql table column ...and some divider lines), then
grouping them all together. I see that Greg Titus at Omni mentioned this
very same solution too. (#328, 2002-03-06.)

However, that solution would make many more objects per diagram and make
editing the ERD much more difficult, as one would have to ungroup, edit,
regroup, etc... and so forth. I'd essentially have to re-run the mysql -> OG
php script every time I changed my schema (which can happen a lot in the
discovery and planning phases of a database), and then I'd lose any manual
layout work that I'd done.

(I guess it's not such a big deal if my DB is authoritative and OG is being
used primarily as an ERD *browser*, but it'd still be nice to see a cleaner
solution.)

While I admire Puneet Kishor's (#1300, 2003-04-23) and Erik Price's
(#1471, 2003-05-17) ideas about going the other direction, OG -> SQL Database
creation, I would add that there are a lot more aspects to creating a DB than
OG is capable or should be aware of, and that if you didn't want to write your
CREATE TABLE statements by hand in BBEdit, you could always use a tool like
Advenio's SQL Grinder to help you. (For more information see
http://www.advenio.com/sqlgrinder/ ;)

One idea that came to mind at this point was the hierarchical-ness (sic) of
OmniOutliner, and hence my huge feature request above.

What if an OmniGraffle graphic object could be a 'list' (one layer deep) or
'outline' (multiple layers deep) container that could hold individual lines
like omnioutliner does? Each line of text could be its own object in the XML
file format, and it would make it much easier to associate magnets with
specific lines. It would also help keep much better track of specific lines
being linked by magnets, even if you added or deleted lines before or after
them (again, Tom Weibe, #311, 2002-03-02). Giving name attributes to graphical
objects for anchoring purposes would probably be the next logical step.

This would eliminate the hassle of the other solution -- grouping and
ungrouping all the time, and having to create new text objects for every
single (separate) line I wanted in my graphic.

Plus, an OmniOutliner container object might also address Weston
Houghton's request (#1296, 2003-04-23) for more 'tab stops' if you could
create multiple columns just like you can in OmniOutliner. I suspect that
request was also intentioned towards some use as a Data Grid.

And to get really nutty, I can even imagine being able to click column headers
and dynamically re-order an omnioutliner component, while all of the lines
dynamically redrew themselves.

Just to be fair, I can think of a few benefits that using anchors, magnets and
arrows would have for OO -- I've seen plenty of examples of using OO to map
out code subroutines. This would let you add some 'JMP' instructions between
your subroutines -- probably scriptable, too.