< Zurück

02.12.2012 19:27:00 • Categories: Applescript, MySql • Tags: Applescript, Mysql

AppleScript: MySQL via do shell script

-- 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

< Zurück | ^ nach oben