From FileMaker Pro to MSSQL or MYSQL using Applescript
set tablestoMake to ""
set dbName to ""
set mysql to true
set mssql to false
set sqlInsert to false
set mssqlClasses to false
set truncate to false
set useDB to false
set namespace to "" ---USED TO OUTPUT A BASIC C SHARP CLASS
tell application "FileMaker Pro"
set output to ""
set myTables to the name of every table of database dbName
repeat with x from 1 to the count of every table of database dbName
--try
set myTable to item x of myTables
if (tablestoMake contains myTable or tablestoMake is "") then
set myTable to name of table myTable
set myDbTable to (dbName as string) & "_" & name of table myTable of database dbName
set myfields to the name of every field of table myTable of database dbName as list
set output to output & "CREATE TABLE " & myTable & return & tab & tab & "(" & return
if (mysql = true) then
repeat with i from 1 to the count of myfields
set myfield to item i of myfields as string
set myFieldType to default type of field (item i of myfields) of table myTable of database dbName as string
if myfield is equal to "id" and myFieldType is equal to "real" then
set output to output & tab & tab & tab & "`" & myfield & "`" & " MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY"
else if myFieldType = "real" then
set output to output & tab & tab & tab & "`" & myfield & "`" & " INT"
else if myFieldType = "date" then
set output to output & tab & tab & tab & "`" & myfield & "`" & " VARCHAR (256)"
else if myFieldType = "string" then
set output to output & " " & tab & tab & tab & "`" & myfield & "`" & " VARCHAR (2256)"
end if
if (i < the (count of myfields)) then
set output to output & "," & return
else if (i = the (count of myfields)) then
set output to output & return
end if
set i to i + 1
end repeat
set output to output & tab & tab & tab & ") " & return & tab & tab & tab & "ENGINE=MyISAM;" & return & return
end if
if (mssql = true) then
repeat with i from 1 to the count of myfields
set myfield to item i of myfields as string
set myFieldType to default type of field (item i of myfields) of table myTable of database dbName as string
if myfield is equal to "id" and myFieldType is equal to "real" then
set output to output & tab & tab & tab & "id INT NOT NULL PRIMARY KEY"
else if myFieldType = "real" then
set output to output & tab & tab & tab & "[" & myfield & "] INT"
else if myFieldType = "date" then
set output to output & tab & tab & tab & "[" & myfield & "] datetime"
else if myFieldType = "string" then
set output to output & " " & tab & tab & tab & "[" & myfield & "] NVARCHAR(2256)"
end if
if (i < the (count of myfields)) then
set output to output & ", " & return
else if (i = the (count of myfields)) then
set output to output & return
end if
set i to i + 1
end repeat
set output to output & return & tab & tab & ");" & return & return
end if
if (mssqlClasses = true) then
set output to "using System.Web;" & return & return
set output to output & "namespace " & namespace & ".NS" & myTable & return
set output to output & "{" & return
set output to output & tab & "public class " & myTable & return
set output to output & tab & "{" & return
repeat with i from 1 to the count of myfields
set myfield to item i of myfields as string
set myFieldType to default type of field (item i of myfields) of table myTable of database dbName as string
if myfield is equal to "id" and myFieldType is equal to "real" then
set output to output & " " & tab & tab & tab & "public int " & myfield & "{ get; set; }"
else if myFieldType = "real" then
set output to output & " " & tab & tab & tab & "public int " & myfield & "{ get; set; }"
else if myFieldType = "date" then
set output to output & " " & tab & tab & tab & "public date " & myfield & "{ get; set; }"
else if myFieldType = "string" then
set output to output & " " & tab & tab & tab & "public string " & myfield & "{ get; set; }"
end if
if (i < the (count of myfields)) then
set output to output & return
else if (i = the (count of myfields)) then
set output to output & return
end if
set i to i + 1
end repeat
set output to output & return & tab & "}" & return & return
set output to output & return & "}" & return & return
my saveDoc(output, dbName, myTable, mssqlClasses, sqlInsert)
end if
if (sqlInsert = true) then
set output to "INSERT INTO " & myTable & "("
repeat with i from 1 to the count of myfields
if (i < the (count of myfields)) then
set output to output & (item i of myfields as string) & ", "
else if (i = the (count of myfields)) then
set output to output & (item i of myfields as string) & ") values ("
end if
set i to i + 1
end repeat
set i to 1
repeat with i from 1 to the count of myfields
set myfield to item i of myfields as string
set myFieldType to default type of field (item i of myfields) of table myTable of database dbName as string
if myFieldType = "real" then
set output to output & " int "
else if myFieldType = "string" or myFieldType = "date" then
set output to output & "string "
end if
if (i < the (count of myfields)) then
set output to output & ", "
end if
if (i = the (count of myfields)) then
set output to output & ");" & return
end if
end repeat
my saveDoc(output, dbName, myTable, mssqlClasses, sqlInsert)
end if
end if
--end try
set x to x + 1
end repeat
if (mssql = true or mysql = true) then
my saveDoc(output, dbName, myTable, mssqlClasses, sqlInsert)
end if
display dialog "DONE"
end tell
on saveDoc(output, dbName, myTable, mssqlClasses, sqlInsert)
set the clipboard to output
set d to do shell script "date '+%Y_%m_%d'"
tell application "TextWrangler"
make new document
paste of document of window 1
if (mssqlClasses = false and sqlInsert = false) then
save document of window 1 to dbName & "_sections_headers_" & d & "_big.sql"
else if (mssqlClasses = true) then
save document of window 1 to myTable & ".cs"
else if (sqlInsert = true) then
save document of window 1 to myTable & ".sql"
end if
--close document of window 1
end tell
end saveDoc