This discussion presumes a basic knowledge of MySQL in the Mac OS X environment. Specifically, this is directed toward so-called desktop application usage, not internet, or PHP applications.
As you probably know, MySQL is frequently touted as the final solution for managing large amounts of data, and in obtaining lightning fast results to complex data queries. Part of the reason for that is that mySQL runs without a Graphical User Interface (GUI) to maximize speed and efficiency of response to queries. On the Mac, the lack of a GUI means that the user must be comfortable working in the Terminal to use MySQL.
MySQL does come with command line control, however, so that one need not be logged into the MySQL server to access the data, and with that ability, one can use AppleScript’s do shell script command to do just about anything with MySQL.
For this article, I am using MySQL version 5.0.24a-standard, and OS X version 10.4.9. The latest version of MySQL is always available at mySQL.org. The binary file practically installs itself, but Marc Liyanage’s Entropy site provides useful details if you are having difficulties. I also found this textbook to be very helpful in understanding how MySQL works, and constructing complicated queries spanning multiple tables. I started with bits of code from a project entitled MySQL4AppleScript written by Kevin Meaney, and although no longer supported, it is still available for download from this site.
I initially became interested in using MySQL as a means to a future end. I had heard about its speed, reliability, and query power, and thought that it might be interesting to someday develop a full-blown application for my veterinary office. In the meantime, I have been playing around with the command line functions via AppleScript Studio, and have been amazed at the speed of data transfer, even with databases containing thousands of records.
Before we get started, however, there is just one more thing. After you have installed MySQL onto your Mac, you have the option of setting a root user password. Since I am just playing around with it, I have set a root user password on one of my Macs, but not the others. I have set passwords for all other users I have created, and this is an important point for accessing the data. Whenever you send a command instruction via do shell script, you have to identify the user, and the password if that user (root user included) has a password set. Since I have not done this on the iBook that I do most of my work with, I always access my data as root with no password, and that is how the majority of the scriptlets will be presented today. For instance, if you wanted to know what databases you had in your system, here are two examples of the command that will do it. The first uses a password, the second does not:
set all_databases to (do shell script "/usr/local/mysql/bin/mysql -u USERNAME -pPASSWORD -e 'SHOW DATABASES'")
The syntax to take note of is that there is a space after the -u before you insert the USERNAME, and there is no space after the -p when you supply the PASSWORD. If you are accessing as root, with no password set, here is what it would look like:
set all_databases to (do shell script "/usr/local/mysql/bin/mysql -u root -e 'SHOW DATABASES'")
As you can probably see, the -u is the user, and the -p is the password, but how about the -e? That is simply telling the MySQL server to execute the statement and quit. I don’t know if it is necessary, but I use it with all the commands I send, just in case. (There are some Help pages on Command Line Tools at the MySQL website.)
When you access information from MySQL via AppleScript, the raw data looks like this:
whereas in the Terminal, it would appear like this:
Figure 1: Screenshot of mySQL Response in Terminal.
This is the default response for any query sent to MySQL; first the title of the column(s), then the desired data. Each item is ended with a line end character, so it is simple to collect this information in the form of a list:
set all_databases to paragraphs of (do shell script ("/usr/local/mysql/bin/mysql -u root -e 'SHOW DATABASES'"))
-->{"Database", "information_schema", "DVD2SQL001", "daily_miles", "dvd2sql", "mysql", "photocat", "spanward", "test"}
You can easily get rid of the first item in the list (the column header) by using the -N modifier in your command line, in this fashion:
set all_databases to paragraphs of (do shell script ("/usr/local/mysql/bin/mysql -u root -N -e 'SHOW DATABASES'"))
-->{"information_schema", "DVD2SQL001", "daily_miles", "dvd2sql", "mysql", "photocat", "spanward", "test"}
This latest iteration is by far the most efficient (in my opinion) for extracting just the data that you need, as long as you only need a single column. It gets slightly more interesting if you want two or more columns of information returned. Consider this table, entitled cars as part of the database daily_miles:
Figure 2: Screenshot of mySQL Columns in Terminal.
If we wanted just the year and the color of all cars, here is the scriptlet and the raw data return:
set year_and_colors to paragraphs of (do shell script ("/usr/local/mysql/bin/mysql -u root -D daily_miles -N -e 'SELECT year,color FROM cars'"))
-->{"2006 yellow", "1994 Green", "1993 White", "2003 White"}
When you want to access a specific database, you use the -D modifier after the username/password and place the name of the database right after that. (Remember to always place single quotes around your queries, as shown.) The results are still in the form of a list, and indeed, each item is a year and a color. The space in between them is actually a tab, so creating a two item list from this raw data necessitates the invocation of everyone’s favorite text item delimiters:
set year_and_colors_RAW to paragraphs of (do shell script ("/usr/local/mysql/bin/mysql -u root -D daily_miles -N -e 'SELECT year,color FROM cars'"))
set year_and_colors_FINAL to {}
set {astid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, tab}
repeat with an_item in year_and_colors_RAW
set end of year_and_colors_FINAL to (an_item's every text item)
end repeat
set AppleScript's text item delimiters to astid
-->{{"2006", "yellow"}, {"1994", "Green"}, {"1993", "White"}, {"2003", "White"}}
If you have a lot of data that needs to be added to a table in your database, by far the fastest and most efficient is to write the data to a .txt file, and then use MySQL’s LOAD DATA INFILE command. This is very simple with AppleScript’s native file writing commands, and all you have to do is choose an unique delimiter to divide each field when you write your .txt file. You also need to end each line with return. A simple repeat will then build the file correctly for MySQL to recognize. In this example, we have a list of 3-item lists of words that will be written to a .txt file for importing to a table:
set word_List to {{"handlers", "window", "MySQL"}, {"then", "prayer", "of"}, {"UpDateWindow", "else", "handlers"}, {"See", "List", "names"}, {"Spoken", "UpDateWindow", ""}, {"to", "window", "See"}}
set home_Path to path to home folder as Unicode text
set a to open for access file (home_Path & "Empty010.txt") with write permission--File does NOT need to exist prior to running this
repeat with a_List in word_List
set build_Line to ""
repeat with aw in a_List
set build_Line to build_Line & aw & "$"--I am using the dollar sign as the field delimiter
end repeat
set build_Line to (characters 1 thru -2 of build_Line as string) & return--Before writing the line to the .txt file, we need to replace the terminal dollar sign with a return
write build_Line to a starting at eof--Always writes a new line to the file
end repeat
close access a
Now that we have a properly formatted .txt file, we can send another shell command to load that data into a table that is ready to accept it.
Use this script to create a table entitled words inside of any MySQL database already present on your system
set table_Make to "DROP TABLE IF EXISTS `words`;
CREATE TABLE `words` (
`first` varchar(50) default NULL,
`second` varchar(50) default NULL,
`third` varchar(50) default NULL
)"
set all_databases to paragraphs of (do shell script ("/usr/local/mysql/bin/mysql -u root -e 'SHOW DATABASES'"))
set db_Name to item 1 of (choose from list all_databases with prompt "Select a MySQL database to create the 'words' table within:")
do shell script ("/usr/local/mysql/bin/mysql -u root -D " & db_Name & " -e '" & table_Make & "'")
For this to work correctly, you need to pay attention to how you build the .txt file in relation to the way you built the table in the database. In this example, we just created a .txt file from a list of 3-item word lists using the dollar sign ($) as the delimiter between each word. Here is what the resulting .txt file looks like:
Therefore, when we want to subsequently import those word lists into a table, we need to be sure that each record in the table will have three fields that can accept string values. It is also helpful to know the names of those fields as well. You can have plenty of other fields for each record even if you do not plan on importing that data at this time. MySQL will simply insert NULL values into the fields you do not identify as part of the LOAD DATA INFILE command. For this command, I have a table entitled words in the Test database, and the fields are labeled first, second, and third:
set home_Path to path to home folder as Unicode text
set SQL_import_file to (home_Path & "Empty010.txt")
do shell script ("/usr/local/mysql/bin/mysql -u root -D test -e \"LOAD DATA INFILE '" & (POSIX path of SQL_import_file) & "' INTO TABLE words FIELDS TERMINATED BY '$' LINES TERMINATED BY '\\r' (first,second,third)\"")
There are two specific things to note here. First, observe that the entire MySQL command is enclosed within double quotes after the -e. This is necessary due to the abundant (and necessary) usage of single quotes within the command itself to identify the file containing the data, the character of the delimiter, and the line termination character. Second, note that we have used a variable in the middle of the command (the POSIX path of the file we wrote to earlier). You can use variables however you desire here, since all we are doing is building strings to send to the shell.
One quirk with the entire LOAD DATA INFILE process is the location of the data file itself. I have had numerous problems getting this to function correctly if the file is located somewhere in either the Desktop or Documents trees. I now exclusively use a subfolder (mysqlload) in my home (user) folder and store the .txt files there. For more exciting details on this topic, please see the mySQL docs.
If you plan on putting together an application that does a lot of different things, but always accesses the same database, using variables to hold some the boring stuff can really simplify your coding:
property preamble : "/usr/local/mysql/bin/mysql " -- POSIX path to mysql
property username : "-u root " -- MySQL username
property passwd : "" -- MySQL password
property database : "-D test " -- MySQL database
property extraOpt : "-N -e " -- MySQL command line options
on doQuery(aQuery)
do shell script preamble & username & passwd & database & extraOpt & quoted form of aQuery
end doQuery
set the_words to every paragraph of (doQuery("SELECT * FROM words"))
set Final_Words to {}
set {astid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, tab}
repeat with an_item in the_words
set end of Final_Words to (an_item's every text item)
end repeat
set AppleScript's text item delimiters to astid
Final_Words
-->{{"handlers", "window", "MySQL"}, {"then", "prayer", "of"}, {"UpDateWindow", "else", "handlers"}, {"See", "List", "names"}, {"Spoken", "UpDateWindow", ""}, {"to", "window", "See"}}
This is now the exact same list we started off with when we talked about the LOAD DATA INFILE topic.
MySQL possesses some fairly powerful date and time capabilities, but these require a little conversion when working from AppleScript. The format in MySQL is YYYY-MM-DD (including the hyphens) and this little handler is what I use to convert an AppleScript date:
to MakeSQLdate(d)--A date property is expected
return (d's year & "-" & (d's month as number) & "-" & d's day) as string
end MakeSQLdate
MakeSQLdate(current date)
-->"2007-6-23"
You can see that using this form will often return a date with single digits in the month and day fields (YYYY-M-D). That is fine, so long as the hyphens are present, MySQL will fix it for you when it saves the date to a record. Going back the other way is just as simple. When you pull a date from a MySQL table, it comes in string format, and this handler will create a date property from that string:
on MakeASdate(d)--A string is expected
return date (d's word 2 & "-" & d's word 3 & "-" & d's word 1)
end MakeASdate
MakeASdate("1961-05-21")
-->date "Sunday, May 21, 1961 12:00:00 AM"
These handlers are only valid for dates, not dates with times, which is certainly not difficult, but requires a bit more manipulation, keeping in mind that MySQL expects times to be in 24-hour format:
on MakeSQLDateTime(dte) --A date property is expected
set tst to dte's time string
if tst contains "PM" then set tst to (((tst's word 1 as number) + 12) & (characters -9 thru -1 of tst) as string)
return ((dte's year) & "-" & (dte's month as number) & "-" & (dte's day) & space & (characters 1 thru -4 of tst as string) as string)
end MakeSQLDateTime
MakeSQLDateTime(current date)
-->"2007-6-23 21:54:46"
And going back the other way:
to MakeASdateTime(dse)--A string is expected
return date (dse's word 2 & "-" & dse's word 3 & "-" & dse's word 1 & space & dse's word 4 & ":" & dse's word 5 & ":" & dse's word 6)
end MakeASdateTime
MakeASdateTime("1979-06-01 19:34:07")
-->date "Friday, June 1, 1979 7:34:07 PM"
Using AppleScript to manage data in the MySQL framework is not difficult, and if you enjoy AppleScript Studio and Interface Builder, it is a viable option for any application that could benefit from a fast and powerful database server.