AppleScript to dump MySQL tables to OmniGraffle Pro.

mysql_to_omnigraffle.applescript

(*
Copyright (c) 2008, Christian Mittendorf <[email protected]>
All rights reserved.

Redistribution and use in source and binary forms, with or without 
modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this 
list ofconditions and the following disclaimer. Redistributions in binary form 
must reproduce the above copyright notice, this list of conditions and the 
following disclaimer in the documentation and/or other materials provided with 
the distribution. Neither the name of the <ORGANIZATION> nor the names of its 
contributors may be used to endorse or promote products derived from this 
software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE 
LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
POSSIBILITY OF SUCH DAMAGE.

*)

property mysql_binary : "/opt/local/lib/mysql5/bin/mysql"
property mysql_user : "user"
property mysql_pass : "pass"

property mysql_host : "127.0.0.1"
property mysql_port : "3306"

property font_normal : "Helvetica"
property font_bold : "Helvetica-Bold"
property font_italic : "Helvetica-Oblique"

property font_size_title : 10.0
property font_size_regular : 9.0

property box_width : 160.0
property box_height : 10.0

property with_shadows : false


if mysql_binary is equal to null then
	try
		set mysql_binary to (do shell script "/usr/bin/which mysql")
	on error
		display dialog "Cannot find the mysql executable on the command line." buttons {"Ok"} default button 1 with title "An error occured…" with icon caution
		return
	end try
end if

set mysql_host to (text returned of (display dialog "MySQL DB Host:" default answer mysql_host buttons {"OK"} default button 1))
set mysql_port to (text returned of (display dialog "MySQL DB Port:" default answer mysql_port buttons {"OK"} default button 1))

set mysql_user to (text returned of (display dialog "MySQL DB User:" default answer mysql_user buttons {"OK"} default button 1))
set mysql_pass to (text returned of (display dialog "Password:" default answer mysql_pass buttons {"OK"} default button 1 with hidden answer))

set dbList to getMySQLDatabaseList(mysql_user, mysql_pass)
if (count of items of dbList) is 0 then
	return
end if
set theDatabase to (choose from list dbList with prompt "Select a Database:" OK button name "Select")

if theDatabase is not false then
	set the_db to item 1 of theDatabase
	set tableList to getMySQLTableList(mysql_user, mysql_pass, the_db)
	
	if (count of items in tableList) > 0 then
		set all_tables to (choose from list tableList with prompt "Select Tables:" OK button name "Select" with multiple selections allowed)
		
		if (count of items in all_tables) > 0 then
			
			tell application "OmniGraffle Professional 5"
				set window_list to every window where id > 0
				if number of items of window_list > 0 then
					set the_document to document of front window
				else
					set the_document to make new document at end of documents
				end if
			end tell
			
			set draw_table_list to {}
			set draw_connections_list to {}
			
			repeat with i from 1 to number of items in all_tables
				set the_table to item i of all_tables
				set table_desc to getMySQLTableDescription(mysql_user, mysql_pass, the_db, the_table)
				set draw_table_list to draw_table_list & {{table:the_table, desc:table_desc}}
			end repeat
			
			repeat with i from 1 to number of items in draw_table_list
				set table_to_draw to item i of draw_table_list
				drawTableDescription(table of table_to_draw, desc of table_to_draw)
			end repeat
			
			repeat with i from 1 to number of items in draw_connections_list
				set connection_to_draw to item i of draw_connections_list
				drawTableConnection(connection_to_draw)
			end repeat
			
			drawTableConnections()
			
			tell application "OmniGraffle Professional 5"
				tell front document
					tell first canvas
						activate
						layout (a reference to every graphic)
					end tell
				end tell
			end tell
		else
			display dialog "No tables selected." buttons {"Ok"} default button 1
		end if
	else
		display dialog "No tables found in Database \"" & theDatabase & "\"." buttons {"Ok"} default button 1
	end if
end if


on drawTableConnections()
	tell application "OmniGraffle Professional 5"
		tell front document
			tell first canvas
				set title_graphics to {}
				set entry_graphics to {}
				
				set all_groups to every group
				repeat with x from 1 to number of items in all_groups
					set graphics_list to graphics of item x of all_groups
					repeat with i from 1 to number of items in graphics_list
						set this_graphic to item i of graphics_list
						if i is 1 then
							set title_graphics to title_graphics & {this_graphic}
						else
							if text of this_graphic contains "_id" then
								set entry_graphics to entry_graphics & {this_graphic}
							end if
						end if
					end repeat
				end repeat
				
				repeat with i from 1 to number of items in title_graphics
					set this_title to item i of title_graphics
					
					set title_text to text of this_title
					if title_text ends with "ies" then
						set title_text to texts 1 thru ((number of characters of title_text) - 3) of title_text & "y"
					else if title_text ends with "s" then
						set title_text to texts 1 thru ((number of characters of title_text) - 1) of title_text
					end if
					
					repeat with i from 1 to number of items in entry_graphics
						set this_item to item i of entry_graphics
						
						if text of this_item begins with (title_text & "_id") then
							
							set my_line to make new line at end of graphics with properties {head type:"FilledArrow", tail type:"FilledBall", line type:orthogonal, point list:{{100, 100}, {200, 200}}}
							
							tell my_line
								set source to this_item
								set tail magnet to 1
								set destination to this_title
								set head magnet to 2
							end tell
							
						end if
					end repeat
					
				end repeat
				
			end tell
		end tell
	end tell
end drawTableConnections


on drawTableDescription(table_name, table_description)
	tell application "OmniGraffle Professional 5"
		activate
		tell front document
			
			set canvas_size to canvasSize of first canvas
			
			set page_height to item 1 of canvas_size
			set page_width to item 2 of canvas_size
			
			set page_y to page_height * 0.7
			set page_x to page_height * 0.3
			
			set max_width to 0
			set max_height to 0
			
			tell first canvas
				
				repeat with i from number of items in table_description to 1 by -1
					set the_row to item i of table_description
					
					if |Key| of the_row is equal to "PRI" then
						set theFont to font_bold
					else
						set theFont to font_normal
					end if
					
					if |Key| of the_row is equal to "MUL" then
						set the_text to {{font:font_italic, size:font_size_regular, text:|Field| of the_row}, {font:theFont, size:font_size_regular, text:" : " & |Type| of the_row}}
					else
						set field_name to get |Field| of the_row & " : " & |Type| of the_row
						set the_text to {font:theFont, size:font_size_regular, text:field_name}
					end if
					
					set new_shape to make new shape at beginning of graphics with properties {magnets:{{1, 0}, {-1, 0}}, text:the_text, draws shadow:with_shadows, origin:{page_x, page_y}, thickness:0.5, size:{box_width, box_height}, autosizing:full}
					
					set tmp_size to size of new_shape
					set tmp_width to item 1 of tmp_size
					if tmp_width > max_width then
						set max_width to tmp_width
					end if
					
					if max_height is 0 then
						set max_height to item 2 of tmp_size
						set box_height to max_height
					end if
					
					set page_y to page_y - box_height
				end repeat
				
				set new_shape to make new shape at beginning of graphics with properties {magnets:{{0, 1}, {0, -1}}, text:{font:font_bold, size:font_size_title, alignment:center, text:table_name}, fill color:{0.8, 0.8, 0.8}, draws shadow:with_shadows, origin:{page_x, page_y}, thickness:0.5, size:{box_width, box_height}, autosizing:full}
				
				set tmp_size to size of new_shape
				set tmp_width to item 1 of tmp_size
				if tmp_width > max_width then
					set max_width to tmp_width
				end if
				
				set page_y to page_y - box_height
				
				set theshapes to a reference to shapes 1 thru ((count of items in table_description) + 1)
				
				repeat with tmp_shape in theshapes
					set tmp_size to size of tmp_shape
					set item 1 of tmp_size to max_width
					set size of tmp_shape to tmp_size
				end repeat
				
				assemble theshapes
			end tell
		end tell
	end tell
end drawTableDescription

(* do a "show databases" and return a list of available databases *)
on getMySQLDatabaseList(user, pass)
	set dbList to {}
	
	set p to " "
	if pass is not "" then
		set p to " --password='" & pass & "' "
	end if
	
	try
		set mysql_result to do shell script mysql_binary & " -h " & mysql_host & " --silent -u " & user & p & "  -e 'show databases'" with altering line endings
		repeat with i from 1 to number of paragraphs in mysql_result
			set mysql_row to paragraph i of mysql_result
			set dbList to dbList & mysql_row
		end repeat
	on error e
		display dialog e buttons {"Ok"} default button 1 with title "An error occured…" with icon caution
	end try
	
	return dbList
end getMySQLDatabaseList

(* do a "show tables" and return a list of available tables*)
on getMySQLTableList(user, pass, dbname)
	set tableList to {}
	
	set p to " "
	if pass is not "" then
		set p to " --password='" & pass & "' "
	end if
	
	set mysql_result to do shell script mysql_binary & " -h " & mysql_host & " --silent -u " & user & p & dbname & " -e 'show tables'" with altering line endings
	
	repeat with i from 1 to number of paragraphs in mysql_result
		set mysql_row to paragraph i of mysql_result
		set tableList to tableList & mysql_row
	end repeat
	
	return tableList
end getMySQLTableList

(* do a "describe <table>" and return a hashmap with the table description *)
on getMySQLTableDescription(user, pass, dbname, table)
	set tableDescription to {}
	
	set p to " "
	if pass is not "" then
		set p to " --password='" & pass & "' "
	end if
	
	set mysql_result to do shell script mysql_binary & " -h " & mysql_host & " --silent -u " & user & p & dbname & " -e 'describe " & table & "'" with altering line endings
	
	set defaultTextDelimiters to text item delimiters
	set text item delimiters to tab
	
	set item_count to number of paragraphs in mysql_result
	repeat with i from 1 to item_count
		set rowDescription to {|Field|:"", |Type|:"", |NULL|:"", |Key|:"", |DefaultExtra|:""}
		
		set mysql_row to paragraph i of mysql_result
		
		set |Field| of rowDescription to text item 1 of mysql_row
		set |Type| of rowDescription to text item 2 of mysql_row
		set |NULL| of rowDescription to text item 3 of mysql_row
		set |Key| of rowDescription to text item 4 of mysql_row
		set |DefaultExtra| of rowDescription to text item 5 of mysql_row
		
		set tableDescription to tableDescription & {rowDescription}
	end repeat
	
	set text item delimiters to defaultTextDelimiters
	
	return tableDescription
end getMySQLTableDescription