15.5 Automating Querying

Sometimes automated queries are useful for producing periodic reports, updating data, or deleting temporary data. As we show you later in "Backup and Recovery," they're also useful tools to produce database backups.

Consider an example from the winestore database where query automation is useful. The shopping cart in the online winestore is implemented using the database. As we discuss in detail later in Chapter 18, when an anonymous user adds a wine to their shopping basket, a row is added to the orders table. The row is for a dummy customer with a cust_id=-1. A related items row is then created for each item in the shopping cart. For the moment, the details of how this works and why we do it this way aren't important.

Our system requirements in Chapter 16 specify that if a customer doesn't purchase the wines in their shopping cart within one day, the shopping cart should be emptied. This is similar to most online stores, and it's necessary to prevent the database being filled with abandoned carts. In this case, it's a DELETE query that should be automated.

The following instructions assume you've followed our installation instructions in Appendix A through Appendix C.

If you're using a Unix environment, the following query can be run from the shell to remove all shopping cart rows from the orders and items tables that are more than one day old:

% /usr/local/mysql/bin/mysql -uusername -ppassword -e 'USE winestore;

  DELETE orders, items FROM orders INNER JOIN items 

  USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)

  AND orders.cust_id = -1;'

In a Microsoft Windows environment you can do the same thing using the Run dialog box that's accessible from the Start menu. Type the following and then click OK:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe" -uusername -ppassword 

  -D winestore -e "DELETE orders, items FROM orders INNER JOIN items 

  USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)

  AND orders.cust_id = -1;"

The MySQL time and date functions date_sub( ) and now( ) are described in Section 15.4. The next two sections show how to install a command so it runs regularly.

15.5.1 Unix

Having designed and tested a query, it can be inserted into a cron table (or crontab) to automate the operation. The crond daemon is a process that runs by default in a Unix installation and continually checks the time. If any of the entries in user tables match the current time, the commands in the entries are executed. Consider an example from a user cron table:

30 17 * * 1-5 echo 'Go home!'

This instructs crond to print the string at 5:30 p.m. each day from Monday (day 1) to Friday (day 5). The two asterisks mean every day of the month, and every month of the year respectively. The string 1-5 means the days Monday to Friday inclusive.

A cron entry has six parts: a time in minutes from 0 to 59, a time in hours using the 24-hour clock, a day of the month from 1 to 31, a month of the year from 1 to 12, a day of the week from 0 to 7 (Sunday is both 0 and 7), and the command to execute. For each of the first five parts, you can set an integer value (for example, 1), a comma-separated list of values (for example, 1,3,5), a range of values (for example, 1-3), a combination or a list and a range (for example, 1-3,5), or a stepped value (for example, 0-23/2 could be used to mean every second hour). You can also replace any value with an asterisk * meaning all values.

Under Linux, you can replace integer day numbers with the shortcut names mon to sun, and numeric months with the shortcuts jan to dec. More details about cron can be found by typing man crontab in a shell to read the manual page. Note that crontabs in some other Unix variants also have a slightly different format.

You can add the housekeeping query discussed in the previous section to the cron table by typing the following at a shell prompt:

% crontab -e

This edits your cron table. Let's decide that the system should check for old shopping carts every 30 minutes. To do so, add the following line to the file (it must be on one line):

0,30 * * * * /usr/local/mysql/bin/mysql -uusername -ppassword

  -e 'USE winestore; DELETE orders, items FROM orders INNER JOIN items 

  USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)

  AND orders.cust_id = -1;'

After you save the file, the shopping cart DELETE query runs every 30 minutes.

Reports and other tasks can be added to the cron table in a similar way. For example, you can output a simple report of the number of bottles purchased yesterday and send this to your email address each morning. Here's how you might do it:

0 8 * * * mon-fri /usr/local/mysql/bin/mysql -uusername -ppassword

  -e 'USE winestore; SELECT sum(qty) FROM orders INNER JOIN items 

  USING (cust_id, order_id) WHERE date > date_sub(now( ), interval 1 day)

  AND orders.cust_id != -1;' | /bin/mail hugh@hughwilliams.com

We could also have automatically written the information to a log file or to a table in the database.

There are other ways to automate queries or housekeeping in a Unix environment, including with the commands at and batch. We don't discuss these here, but you can find out more by typing man at or man batch at a shell prompt. A Mac OS X-focused article (that's also mostly relevant to other Unix users) can be found at http://www.macdevcenter.com/pub/a/mac/2002/07/02/terminal_5.html.

15.5.2 Microsoft Windows

Having designed and tested a query, it can be scheduled to run automatically by Microsoft Windows. The Windows task scheduler is a process that runs by default and continually checks the time. If any of the scheduled entries match the current date and time, the commands in the entries are executed.

Suppose you want to check for old shopping carts once every day. To do this, click on the Start Menu, then on Settings, and then on the Control Panel menu option. Now, double-click on the Scheduled Tasks icon. In the window, you'll see an icon labeled Add Scheduled Task. Double click the icon, and a wizard that guides you through setting up a task will start. We discuss the steps for Windows 2000 next and assume you've followed our installation instructions in Appendix A through Appendix C.

To use the wizard, click Next to begin. Then, click the Browse button and locate the mysql.exe program in the directory C:\Program Files\EasyPHP1-7\mysql\bin. Click Open to select the program. Now, because we want to run the task daily, click on the Daily radio button and click on Next. On the next screen, you can accept the default start time, interval (Every Day), and start Date, and click Next again. The following screen asks for your Windows username and password: enter these and press Next. You're now on the final screen of the wizard, but you still need to modify exactly what the task will do. So, click the Open Advanced Properties checkbox and click on Finish. A mysql task dialog box should appear containing three tabs.

You've now completed working with the wizard, but you need to add extra details to the command that will run. In the Run text box shown in the Task tab, alter the text so that it is as follows:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe"-uusername -ppassword

  -D winestore -e "DELETE orders, items FROM orders INNER JOIN items 

  USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)

  AND orders.cust_id = -1;"

Click on Apply, and then on OK. After the dialog window closes, the shopping cart DELETE query runs every day.

You can schedule tasks to run once or more frequently than daily by changing the settings in the Schedule tab for the task. You can access this by selecting Settings from the Start Menu, then Control Panel, double-clicking on Scheduled Tasks, and then on the task you want to edit. The Advanced button allows you to customize when a task repeats. For example, suppose you want a task to run every 10 minutes. To do this, you select the Repeat Task checkbox, change the Every option to 10 minutes, select the Duration radio button, and change the hour(s) setting to 24. Then, click on OK.

The Settings tab lets you adjust other conditions that determine if a task should be run, such as whether your notebook is running on batteries, whether the computer is sleeping, or if the computer is idle.

You can find out more about the Task Scheduler using the Microsoft Windows Help system. Click on Help in the Start Menu, and typing Scheduled Tasks into the Index tab. Double-click the Overview sub-entry.