Schlagwort-Archive: 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

 

AppleScript: MySQL via MySQLBridge

property mysql_pw : "scarlett"
property mysql_user : "root"
property mysql_host : "localhost"
property mysql_port : 3306
property mysql_socket : "/tmp/mysql.sock"

property mysql_db : "mysql"
property mysql_table : "user"

my make_connection(mysql_pw, mysql_user, mysql_host, mysql_port, mysql_socket, mysql_db)
set the_result1 to my sample_query("select * from user")
set the_result2 to my sample_query("show databases")
set the_result3 to my sample_query("show fields from user")
my close_connection()

return {the_result1, the_result2, the_result3}

on make_connection(mysql_pw, mysql_user, mysql_host, mysql_port, mysql_socket, mysql_db)
    tell application "MySQLBridge" to connect password mysql_pw user mysql_user host mysql_host port mysql_port socket mysql_socket database mysql_db
end make_connection

on sample_query(thisQuery)
    tell application "MySQLBridge" to return runSQL thisQuery
end sample_query

on close_connection()
    tell application "MySQLBridge" to close connection
end close_connection