-- Copyleft Yarra Valley Software Ltd, Oxford, UK 2003
-- AppleScript mysql interface. 7 October 2003
-- Version Beta 0.3
--
-- This interface is freeware. It can be copied, duplicated used/modified.
-- This header must remain in place. Modified versions must acknowledge
-- the original indicate where modifications have been made. I would
-- really appreciate if improvements, additions could be passed through me
-- so that I can make them available within one version.
-- http://www.yvs.eu.com
-- ktam@yvs.eu.com
-- If you wish to include this script in your own AppleScript file, copy
-- everything to your script and then uncomment the last line and
-- the line that follows this text.
-- script mysql
property gPathToMySQL : missing value
property gmySQLCommand : missing value
property gTheUser : ""
property gTheUsersPassword : ""
property gTheHost : ""
property gTheDataBase : ""
property gUsePasswordInMyCnfFile : false
-- Determines where mysql is located. Returns true if successful else false.
on SetLocationOfmySQL()
if gmySQLCommand is not equal to missing value then
-- try
-- The command which is likely to cause least problems is get version.
-- set theResult to do shell script gmySQLCommand & " -V"
-- return true
-- end try
return true
end if
try
-- The command which is likely to cause least problems is get version.
set theResult to do shell script "mysql -V"
set gPathToMySQL to ""
on error
set mySQLList to {"/usr/local/mysql/bin/", "/Library/MySQL/bin/"}
set haveFound to false
repeat with i from 1 to the count of mySQLList
try
set theResult to do shell script (item i of mySQLList) & "mysql -V"
set gPathToMySQL to (item i of mySQLList)
set haveFound to true
exit repeat
end try
end repeat
if haveFound is equal to false then
try
try
set searchResults to do shell script "find /usr/local -name mysql -type f 2>/dev/null"
on error errorString
set searchResults to errorString
end try
-- display dialog searchResults
set tids to AppleScript's text item delimiters
set AppleScript's text item delimiters to return
set theResults to text items of searchResults
set AppleScript's text item delimiters to tids
set theCommand to "mysql"
repeat with i from 1 to (the count of theResults)
set theCommand to item i of theResults
if theCommand contains "mysql" then
try
-- The following line will throw an error if theCommand is not a valid mysql.
set theResult to do shell script theCommand & " -V"
-- display dialog "Command found: " & theCommand
exit repeat
end try
end if
end repeat
set theResult to do shell script theCommand & " -V"
set AppleScript's text item delimiters to "/"
set gPathToMySQL to (text items 1 thru -2 of theCommand) as string
set AppleScript's text item delimiters to tids
on error
try
set dialogResult to display dialog "Could not find your installation of mysql, could you please specify the directory that contains mysql" default answer "/Library/MySQL/bin/"
on error
set dialogResult to {button returned:"Cancel"}
end try
if the button returned of dialogResult is equal to "OK" then
set thePath to the text returned of dialogResult
try
-- The following line will throw an error if theCommand is not a valid mysql.
set theResult to do shell script thePath & "mysql -V"
set gPathToMySQL to thePath
on error
set gPathToMySQL to missing value
set gmySQLCommand to missing value
return false
end try
end if
end try
end if
end try
set gmySQLCommand to gPathToMySQL & "mysql"
return true
end SetLocationOfmySQL
on SetMySQLPath(thePath)
if thePath is equal to missing value then
set gPathToMySQL to missing value
set gmySQLCommand to missing value
else
set gPathToMySQL to thePath
set gmySQLCommand to thePath & "mysql"
end if
end SetMySQLPath
on GetMySQLPath()
return gPathToMySQL
end GetMySQLPath
on GetMySQLCommand()
return gmySQLCommand
end GetMySQLCommand
-- GetmySQLVersion will clear the mysql command and path variables if
-- an error occured when trying to get the version number.
on GetmySQLVersion()
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
try
set finalResult to do shell script gmySQLCommand & " -V"
on error
set gPathToMySQL to missing value
set gmySQLCommand to missing value
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
try
set finalResult to do shell script gmySQLCommand & " -V"
on error
set gPathToMySQL to missing value
set gmySQLCommand to missing value
end try
end try
return finalResult
end GetmySQLVersion
-- Returns false if there is not a valid mysql to run.
on ClearMySQLCommandIfNotValid()
if GetmySQLVersion() begins with "Error:" then
return false
else
return true
end if
end ClearMySQLCommandIfNotValid
-- Attempts to redo the command after seeing if we have a valid mysql executable
on RedoMySQLCommand(theCommand)
set theResult to GetmySQLVersion()
if theResult begins with "Error" then
return theResult
end if
try
set theResult to do shell script theCommand
on error theError
set theResult to "Error: " & theError
end try
return theResult
end RedoMySQLCommand
-- If originalPassword is an empty string then assume that the password
-- has not previously been set.
on SetUserPassword(userName, originalPassword, newPassword)
set finalResult to "Error: mysql not found, have you installed it?"
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
set mySQLAdminCommand to gPathToMySQL & "mysqladmin -u " & userName
if gUsePasswordInMyCnfFile is false then
set passwordComponent to " --password='" & originalPassword & "'"
end if
try
set theResult to do shell script mySQLAdminCommand & passwordComponent & " password " & quoted form of newPassword
on error theErrorString
set theResult to theErrorString
end try
return "" & theResult
end SetUserPassword
on SetRootPassword(originalPassword, newPassword)
return SetUserPassword("root", originalPassword, newPassword)
end SetRootPassword
on SetCurrentUser(userName)
set gTheUser to userName
end SetCurrentUser
on SetCurrentPassword(usersPassword)
set gTheUsersPassword to usersPassword
end SetCurrentPassword
on SetCurrentUserAndPassword(userName, usersPassword)
set gTheUser to userName
set gTheUsersPassword to usersPassword
end SetCurrentUserAndPassword
on SetTheHost(theHost)
set gTheHost to theHost
end SetTheHost
-- usePasswordInCnfFile is either true or false
on SetUsePasswordInMyCnfFile(usePasswordInCnfFile)
set gUsePasswordInMyCnfFile to usePasswordInCnfFile
end SetUsePasswordInMyCnfFile
-- If you are using the .my.cnf file to configure your login for mysql
-- then do not use this command as it will remove all entries in your
-- .my.cnf file and replace it with the password entry only.
on SetPasswordInMyCnfFile(thePassword)
try
set theCommand to "echo " & quoted form of ("[client]" & (ASCII character 10) & "password=" & thePassword) & " > ~/.my.cnf"
do shell script theCommand & ";chmod 600 ~/.my.cnf"
end try
end SetPasswordInMyCnfFile
--private function, should only be called by the handlers in mysql script
on CreateLogInString(userName, usersPassword, theHost)
set logInString to ""
if theHost is not equal to "" then
set logInString to logInString & " -h " & theHost
end if
if userName is not equal to "" then
set logInString to logInString & " -u " & userName
end if
if gUsePasswordInMyCnfFile is false then
set logInString to logInString & " --password='" & usersPassword & "'"
end if
return logInString
end CreateLogInString
on SetTheDataBase(theDataBase)
set gTheDataBase to theDataBase
end SetTheDataBase
on GetTheDataBase()
return gTheDataBase
end GetTheDataBase
on CreateDataBaseRaw(userName, usersPassword, theHost, dataBaseName)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set theLoginString to CreateLogInString(userName, usersPassword, theHost)
set theCommand to gmySQLCommand & theLoginString & " -e \"" & "CREATE DATABASE IF NOT EXISTS " & "\\`" & dataBaseName & "\\`" & "\""
-- display dialog theCommand
set theResult to ""
try
set theResult to "" & (do shell script theCommand)
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end CreateDataBaseRaw
on CreateDataBase(dataBaseName)
return CreateDataBaseRaw(gTheUser, gTheUsersPassword, gTheHost, dataBaseName)
end CreateDataBase
on DropDataBaseRaw(userName, usersPassword, theHost, dataBaseName)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set theLoginString to CreateLogInString(userName, usersPassword, theHost)
set theCommand to gmySQLCommand & theLoginString & " -e \"" & "DROP DATABASE IF EXISTS " & "\\`" & dataBaseName & "\\`" & "\""
-- display dialog theCommand
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end DropDataBaseRaw
on DropDataBase(dataBaseName)
return DropDataBaseRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase)
end DropDataBase
on ShowDataBasesRaw(userName, usersPassword, theHost)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set theLoginString to CreateLogInString(userName, usersPassword, theHost)
set theCommand to gmySQLCommand & theLoginString & " -e \"" & "SHOW DATABASES" & "\""
-- display dialog theCommand
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end ShowDataBasesRaw
on ShowDataBases()
return ShowDataBasesRaw(gTheUser, gTheUsersPassword, gTheHost)
end ShowDataBases
on ShowTablesRaw(userName, usersPassword, theHost, theDataBase)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set theLoginString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & theLoginString & dataBaseString & " -e \"" & "SHOW TABLES" & "\""
-- display dialog theCommand
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end ShowTablesRaw
on ShowTables()
return ShowTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase)
end ShowTables
on CreateTableRaw(userName, usersPassword, theHost, theDataBase, tableName, tableStructure)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "CREATE TABLE IF NOT EXISTS " & "\\`" & tableName & "\\`" & " (" & tableStructure & ")\""
-- display dialog theCommand
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end CreateTableRaw
on CreateTable(tableName, tableStructrue)
return CreateTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName, tableStructrue)
end CreateTable
on DeleteTablesRaw(userName, usersPassword, theHost, theDataBase, tableName)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "DROP TABLE IF EXISTS " & "\\`" & tableName & "\\`" & "\""
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end DeleteTablesRaw
on DeleteTables(tableName)
return DeleteTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName)
end DeleteTables
on DescribeTablesRaw(userName, usersPassword, theHost, theDataBase, tableName)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & "DESCRIBE " & "\\`" & tableName & "\\`" & "\""
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end DescribeTablesRaw
on DescribeTables(tableName)
return DescribeTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableName)
end DescribeTables
on InsertDataIntoTableRaw(userName, usersPassword, theHost, theDataBase, insertOptions, tableName, theInsertCommand)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"INSERT"
if insertOptions is not equal to "" then
set theCommand to theCommand & " " & insertOptions
end if
set theCommand to theCommand & " INTO " & "\\`" & tableName & "\\`" & " " & theInsertCommand & "\""
-- display dialog theCommand
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end InsertDataIntoTableRaw
on InsertDataIntoTable(insertOptions, tableName, theInsertCommand)
return InsertDataIntoTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, insertOptions, tableName, theInsertCommand)
end InsertDataIntoTable
on DeleteDataFromTableRaw(userName, usersPassword, theHost, theDataBase, deleteOptions, tableName, theDeleteCommand)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"DELETE"
if deleteOptions is not equal to "" then
set theCommand to theCommand & " " & deleteOptions
end if
set theCommand to theCommand & " FROM " & "\\`" & tableName & "\\`" & " " & theDeleteCommand & "\""
-- display dialog theCommand
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end DeleteDataFromTableRaw
on DeleteDataFromTable(tableName, deleteOptions, theDeleteCommand)
return DeleteDataFromTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, deleteOptions, tableName, theDeleteCommand)
end DeleteDataFromTable
on SelectDataFromTableRaw(userName, usersPassword, theHost, theDataBase, selectOptions, selectExpression, theTables, selectCommand)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \""
set theCommand to theCommand & "SELECT"
if selectOptions is not equal to "" then
set theCommand to theCommand & " " & selectOptions
end if
set theCommand to theCommand & " " & selectExpression
if theTables is not equal to "" then
set theCommand to theCommand & " FROM " & theTables
end if
if selectCommand is not equal to "" then
set theCommand to theCommand & " " & selectCommand
end if
set theCommand to theCommand & "\""
-- display dialog theCommand
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
-- display dialog theResult
return theResult
end SelectDataFromTableRaw
on SelectDataFromTable(selectOptions, selectExpression, theTables, selectCommand)
return SelectDataFromTableRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, selectOptions, selectExpression, theTables, selectCommand)
end SelectDataFromTable
-- If gUsePasswordInMyCnfFile is true then this function will ignore the
-- password passed in.
on ExecuteMySQLCommandRaw(userName, usersPassword, theHost, theDataBase, mySQLCommand)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \"" & mySQLCommand & "\""
set theResult to ""
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end ExecuteMySQLCommandRaw
on ExecuteMySQLCommand(mySQLCommand)
return ExecuteMySQLCommandRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, mySQLCommand)
end ExecuteMySQLCommand
on UpdateTablesRaw(userName, usersPassword, theHost, theDataBase, updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)
set finalResult to "Error: mysql not found, have you installed it?"
if gmySQLCommand is equal to missing value then
if SetLocationOfmySQL() is equal to false then
return finalResult
end if
end if
set logInString to CreateLogInString(userName, usersPassword, theHost)
set dataBaseString to ""
if theDataBase is not equal to "" then
set dataBaseString to " -D " & "'" & theDataBase & "'"
end if
set theCommand to gmySQLCommand & logInString & dataBaseString & " -e \""
set theCommand to theCommand & "UPDATE"
if updateOptions is not equal to "" then
set theCommand to " " & theCommand & updateOptions
end if
set theCommand to theCommand & " " & theTables & " " & "SET " & setExpression
if whereDefinition is not equal to "" then
set theCommand to theCommand & " WHERE " & whereDefinition
end if
if orderBy is not equal to "" then
set theCommand to theCommand & " ORDER BY " & orderBy
end if
if limitTo is not equal to "" then
set theCommand to theCommand & " LIMIT " & limitTo
end if
set theCommand to theCommand & "\""
set theResult to ""
-- display dialog theCommand
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end UpdateTablesRaw
on UpdateTables(updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)
return UpdateTablesRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, updateOptions, theTables, setExpression, whereDefinition, orderBy, limitTo)
end UpdateTables
on DumpMySQLRaw(userName, usersPassword, theHost, theDataBase, theTables, pathToFile)
set logInString to CreateLogInString(userName, usersPassword, theHost)
if theDataBase is equal to "" then
set theCommand to gPathToMySQL & "mysqldump --quick -Q" & logInString & " --all-databases"
else
set theCommand to gPathToMySQL & "mysqldump --quick -Q" & logInString & " '" & theDataBase & "'"
if theTables is not equal to "" then
-- set theCommand to theCommand & " '" & theTables & "' > " & quoted form of pathToFile
set theCommand to theCommand & " " & theTables & " > " & quoted form of pathToFile
else
set theCommand to theCommand & " > " & quoted form of pathToFile
end if
end if
-- display dialog theCommand
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end DumpMySQLRaw
on DumpMySQL(tableNames, pathToFile)
return DumpMySQLRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, tableNames, pathToFile)
end DumpMySQL
on ImportMySQLRaw(userName, usersPassword, theHost, theDataBase, pathToFile)
set logInString to CreateLogInString(userName, usersPassword, theHost)
set theCommand to gmySQLCommand & logInString & " '" & theDataBase & "' < " & quoted form of pathToFile
-- display dialog theCommand
try
set theResult to do shell script theCommand
on error
set theResult to RedoMySQLCommand(theCommand)
end try
return theResult
end ImportMySQLRaw
on ImportMySQL(pathToFile)
return ImportMySQLRaw(gTheUser, gTheUsersPassword, gTheHost, gTheDataBase, pathToFile)
end ImportMySQL
--end script