The Omni Group
These forums are now read-only. Please visit our new forums to participate in discussion. A new account will be required to post in the new forums. For more info on the switch, see this post. Thank you!

Go Back   The Omni Group Forums > OmniGraffle > OmniGraffle General
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
AppleScript to dump mySQL tables to OmniGraffle Pro Thread Tools Search this Thread Display Modes
All credit goes to the original author (James Spahr) who's script can be found here: http://www.designweenie.com/blog/ima...se.script.html
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
 
So you mention Omnigraffle 5 changes the XML, I've been trying for two days to get my mysql db into a org/design chart for management with no luck, this is the closest I've found to anything... I finally figured out how to get this to run, and I get this error
Code:
""<?xml version=\"1.0\"?>
  <row>
	<field name=\"Tables_in_dev\">word</field>
  </row>
</resultset>" doesn’t understand the parse XML message."
Any insight how to fix this? I'm looking through myself but I'm just now learning applescript just for this so its very slow going.

Thanks!
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Script to dump Omnifocus Context lists to Kindle? profdrhabeb OmniFocus Extras 2 2011-05-17 04:10 AM
'Workspace' to dump task list without entering in 'Tasks'? mymoon OmniPlan General 3 2009-10-05 08:57 PM
AppleScript-Omnigraffle Documentation? Daria OmniGraffle General 2 2009-07-22 11:18 AM
Importing Tables into OmniGraffle pword OmniGraffle General 1 2009-07-19 05:13 AM
Can I name an OmniGraffle window in AppleScript? ianpiper OmniGraffle General 0 2008-07-30 06:36 AM


All times are GMT -8. The time now is 01:56 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2024, vBulletin Solutions, Inc.