The Omni Group Forums - AppleScript to dump mySQL tables to OmniGraffle Pro

All credit goes to the original author (James Spahr) who's script can be found here: [url]http://www.designweenie.com/blog/images/mapdatabase.script.html[/url]
All I did was beat it into submission for the current OmniGraffle AppleScript dictionary.:D

You'll have to make the connections yourself, haven't gotten that far yet. It does put magnets on for you though.

Oh anyone know how to make tables instead of groups through AppleScript? Everytime I messed with tables I beachballed OmniGraffle to death.:(

And I really hope Omni changes the XML for OmniGraffle 5 as someone alluded to in a post. I originally had the thought to write an application to write the XLM rather then use AppleScript. One look at an OmniGraffle file in plist editor or text mate dissuaded me from that idea.:p

Here's the AppleScript code:
[CODE]-- EDIT THESE VALUES --
property dbName : "test"
property dbHost : "localhost"
property dbUser : "root"
property dbPassword : ""
property commandPath : "/usr/local/mysql/bin/mysql"
property dbType : "mySQL"
-- END EDITING --

(*
All credit goes to the original author (James Spahr) who's script can be found here:
http://www.designweenie.com/blog/images/mapdatabase.script.html

This script uses Latenight sowftware's XML tools:
http://www.latenightsw.com/freeware/XMLTools2/index.html

OmniGraffle:
http://www.omnigroup.com/applications/omnigraffle/

MySQL:
http://dev.mysql.com/downloads/
*)

on executeSQL(sql)

(*
This function takes a quotes escaped sql statement and returns it's results
as an applescript array

there is no error handling
*)

-- build the shell command
if dbType = "mySQL" then
set shellCommand to "echo \"" & sql & "\" | " as string
set shellCommand to shellCommand & commandPath & " -u '" & dbUser & "' --password='" & dbPassword & "' " as string
set shellCommand to shellCommand & "-h '" & dbHost & "' --xml " & dbName as string
end if

-- execute shell command
set xmlCommand to do shell script shellCommand

-- parse the resulting xml
set xmlArray to parse XML xmlCommand

-- prep the as array
set resultSet to {}

-- loop through XML DOM and build the array we are about to return
repeat with r in XML contents of xmlArray
set rr to XML contents of r
set thisRow to {}

repeat with rrr in rr
set theName to XML tag of rrr
set theValue to first item of the XML contents of rrr
set thisRow to thisRow & {{columnName:theName, columnValue:theValue}}
end repeat
set resultSet to resultSet & {thisRow}
end repeat

return resultSet
end executeSQL

on run
-- define some global variables
global max_y
global currentCanvasSize
global ct
global orig_y

set orig_y to 10
set ct to 0
set max_y to 0
set documentName to "Entity-Relationship Diagram of " & dbName

tell application "OmniGraffle Professional"
make new document with properties¬
{name:documentName}
end tell

-- get the size of the current document's canvas and makes it automatically adjust the pages for the # of items
tell document documentName of application "OmniGraffle Professional"
set currentCanvasSize to canvasSize of first canvas
set adjusts pages of first canvas to true
end tell

-- get all the table names
if dbType="mySQL" then
set tableList to executeSQL("SHOW TABLES")
end if

-- loop through the table names and create the OG document
repeat with table in tableList
set tableName to columnValue of first item of table as string
-- get column names
if dbType = "mySQL" then
set columnList to executeSQL("SHOW COLUMNS FROM " & tableName as string)
end if
-- make the shape
makeTableShapes(tablename, columnList, documentName)
set ct to ct + 1
end repeat
end run

on makeTableShapes(tableName, columnList, documentName)

-- define globals
global max_y
global ct
global currentCanvasSize
global orig_y

-- define display settings
set y_offset to 15
set fullWidth to 260
set nameWidth to 180
set typeWidth to 80
set fontSize to 8
set titleColor to {45535, 45535, 65535}
set columnColor to {65535, 65535, 65535}
set headerColor to {45000,28000,42000}

-- calculate where this table will be drawn
set orig_x to 5 + ((fullWidth + 10) * ct)

if (orig_x + fullWidth) > first item of currentCanvasSize then
set orig_y to max_y + (y_offset * 3)
set ct to 0
set orig_x to 5 + ((fullWidth + 10) * ct)
end if

set orig_y2 to orig_y

-- create an empty array so we can group objects later
set tableShapes to {}

tell document documentName of application "OmniGraffle Professional"

-- create the table name block
set tableShape to (make new shape at end of graphics of first canvas with properties ¬
{origin:{orig_x, orig_y2}, size:{fullWidth, y_offset}, draws shadow:true, name:"Rectangle", fill color:titleColor, text:{size:fontSize, text:tableName}} ¬
)
set tableShapes to tableShapes & {tableShape}

-- calculate where the next row will go
set orig_y2 to orig_y2 + y_offset
if (orig_y2 > max_y) then
set max_y to orig_y2
end if

-- draw the header box for the column's name
set tableShape to (make new shape at end of graphics of first canvas with properties ¬
{origin:{orig_x, orig_y2}, size:{nameWidth, y_offset}, draws shadow:true, name:"Rectangle", fill color:headerColor, text:{size:fontSize, text:"Column Name"}} ¬
)
set tableShapes to tableShapes & {tableShape}

-- draw the header box for the column's data type
set tableShape to (make new shape at end of graphics of first canvas with properties ¬
{origin:{orig_x + nameWidth, orig_y2}, size:{typeWidth,y_offset}, draws shadow:true, name:"Rectangle", fill color:headerColor, text:{size:fontSize, text:"Data Type"}} ¬
)
set tableShapes to tableShapes & {tableShape}

-- loop through column names
repeat with col in columnList
set columnName to columnValue of item 1 of col as string
set columnType to columnValue of item 2 of col as string
set indexType to columnValue of item 4 of col as string

-- If there is an index on a column, add it to the column's name
if indexType is not "" then
set columnName to columnName & " (" & indexType & ")"
end if

-- calculate where the next row will go
set orig_y2 to orig_y2 + y_offset
if (orig_y2 > max_y) then
set max_y to orig_y2
end if

-- draw the column boxes for the column's name
set tableShape to (make new shape at end of graphics of first canvas with properties ¬
{origin:{orig_x, orig_y2}, size:{nameWidth, y_offset}, draws shadow:true, name:"Rectangle", fill color:columnColor, text:{size:fontSize, text:columnName}, magnets:{{-1, 0}}} ¬
)
set tableShapes to tableShapes & {tableShape}

-- draw the column boxes for the column's data type
set tableShape to (make new shape at end of graphics of first canvas with properties ¬
{origin:{orig_x + nameWidth, orig_y2}, size:{typeWidth, y_offset}, draws shadow:true, name:"Rectangle", fill color:columnColor, text:{size:fontSize, text:columnType}, magnets:{{1, 0}}} ¬
)
set tableShapes to tableShapes & {tableShape}
end repeat
assemble tableShapes
end tell
end makeTableShapes[/CODE]