mapdatabase.script (AppleScript)

Wednesday, September 29, 2004


-- EDIT THESE VALUES --
property dbname : "test"
property dbhost : "localhost"
property dbuser : "root"
property dbpass : ""
property mysqlpath : "/usr/local/mysql/bin/mysql"
-- END EDITING --

(*
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/mysql/4.0.html
*)



on execsql(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
   set cmd to "echo \"" & sql & "\" | " as string
   set cmd to cmd & mysqlpath & " -u '" & dbuser & "' --password='" & dbpass & "' " as string
   set cmd to cmd & "-h '" & dbhost & "' --xml " & dbname as string
   
   -- execute shell command
   set x to do shell script cmd
   
   -- parse the resulting xml
   set xmlarray to parse XML x
   
   -- prep the as array
   set resultset to {}
   
   -- loop through XML DOM and buil 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 item 1 of the XML contents of rrr
         set thisrow to thisrow & {{col_name:thename, col_value:thevalue}}
      end repeat
      set resultset to resultset & {thisrow}
   end repeat
   
   return resultset
end execsql


on run
   
   -- define some global variables   
   global max_y
   global csz
   global windowRef
   global ct
   global orig_y
   
   set orig_y to 50
   set ct to 0
   set max_y to 0
   set windowRef to 1
   
   -- get the size of the current document's canvas
   tell application "OmniGraffle Professional"
      set csz to canvas size of page 1 of document windowRef
   end tell
   
   -- get all the table names
   set tablelist to execsql("SHOW TABLES")
   
   -- loop through the table names and create the OG document
   repeat with t in tablelist
      
      set tablename to col_value of item 1 of t as string
      
      -- get column names
      set collist to execsql("SHOW COLUMNS FROM " & tablename as string)
      
      
      -- make the shape
      makedbshape(tablename, collist)
      
      set ct to ct + 1
      
   end repeat
   
end run

on makedbshape(tablename, collist)
   
   -- define globals
   global max_y
   global ct
   global csz
   global windowRef
   global orig_y
   
   -- define display settings
   set y_offset to 15
   set width to 160
   set colwidth to 60
   set typesz to 8
   set titlecolor to {45535, 45535, 65535}
   set colcolor to {65535, 65535, 65535}
   
   
   -- calculate where this db table will be drawn
   set orig_x to 50 + ((width + 10) * ct)
   
   if ((orig_x + width) > item 1 of csz) then
      
      set orig_y to max_y + (y_offset * 3)
      set ct to 0
      set orig_x to 50 + ((width + 10) * ct)
   end if
   
   set orig_y2 to orig_y
   
   -- create an empty array so we can group objects later
   set shps to {}
   
   tell document 1 of application "OmniGraffle Professional"
      
      -- create the title block
      
      set shp to (make new shape at end of graphics of page 1 with properties ¬
         {origin:{orig_x, orig_y2}, size:{width, y_offset}, draws shadow:false, name:"Rectangle", fill color:titlecolor, text:{size:typesz, text:tablename}} ¬
            )
      
      set shps to shps & {shp}
      
      -- loop through column names
      repeat with c in collist
         
         -- if the col has an index, add it to the name
         if (col_value of item 4 of c is "") then
            set colname to col_value of item 1 of c
         else
            set colname to col_value of item 1 of c & " (" & col_value of item 4 of c & ")" as string
         end if
         set coltype to col_value of item 2 of c as string
         
         -- 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
         set shp to (make new shape at end of graphics of page 1 with properties ¬
            {origin:{orig_x + colwidth, orig_y2}, size:{width - colwidth, y_offset}, draws shadow:false, name:"Rectangle", fill color:colcolor, text:{size:typesz, text:coltype}, magnets:{{1, 0}}} ¬
               )
         
         set shps to shps & {shp}
         
         set shp to (make new shape at end of graphics of page 1 with properties ¬
            {origin:{orig_x, orig_y2}, size:{width, y_offset}, draws shadow:false, name:"Rectangle", fill color:colcolor, text:{size:typesz, text:colname}, magnets:{{-1, 0}}} ¬
               )
         
         set shps to shps & {shp}
         
         
         
      end repeat
      
      assemble shps
      
   end tell
   
   
end makedbshape
Top | Made with Script Debugger 3.0