This chapter completes our outline of the online winestore. We present here the completed searching and browsing module, and two related topics in web database applications.
The searching and browsing module is briefly outlined in this chapter. A more comprehensive description is presented in Chapter 5. As in the previous three chapters, we suggest that the best method of understand the module is to load it into an editor and use and view the application locally while reading the chapter. We also reemphasize that the code presented here isn’t a production system and requires modifications to be used in a production environment.
The scripts we outline in this chapter cover the following topics:
Automated queries that empty unused carts.
How to separate HTML structure from the code functionality. We illustrate the benefits by showing how the shipping.2 order confirmation script can be rewritten to use a template.
We list the completed wine searching and browsing script that is
based on the browse( )
and
selectDistinct( )
functions discussed in Chapter 5.
Queries are run by users through the web interface and by administrators through either administrative web interfaces or from the MySQL command interpreter. However, sometimes automated querying is necessary to produce periodic reports, update data, or delete temporary data. We discuss how queries can be automated in this section.
To show how queries can be automated, consider an example from the
online winestore. The shopping cart in the online winestore is
implemented using the winestore database. As
discussed in Chapter 12, when an anonymous user
adds a wine to their shopping basket, an order row is added to the
orders table. The row is for a dummy customer
with a cust_id=-1
, and the next available
order_id
for this dummy customer. A related
items row is created for each item in the
shopping cart. The order_id
is maintained in the
session variable order_no
so that orders by
different anonymous customers aren’t confused.
Our system requirements in Chapter 1 specify that
if a customer doesn’t purchase the wines in their
shopping cart within one day, then the shopping cart should be
emptied. This is an example of a DELETE
operation
that should be automated. It is impractical to require the
administrator to run this query each day to remove junk data.
The following query can be run from the Linux shell to remove all orders rows that are more than one day old and are for the dummy customer:
% /usr/local/mysql/bin/mysql -uusername
-psecret
-e 'USE winestore; DELETE FROM orders WHERE unix_timestamp(date) < (unix_timestamp(date_add(now( ), interval -1 day))) AND cust_id = -1;'
The MySQL time and date function unix_timestamp( )
converts a timestamp
attribute to an integer that is accurate to the nearest second. In
this query, we compare the value of the entry in the
orders table with the value of exactly one day
earlier from the current date and time. If the row is older than one
day, then it is deleted. The same query works for the
items table, when orders
is
replaced with items
in the FROM
clause.
Having designed and tested the query, it can be inserted into a Unix cron table to automate the operation. The crond daemon is a process that runs by default in a Linux installation and continually checks the time. If any of the entries in user tables match the current time, then the commands in the entries are executed. Consider an example:
30 17 * * mon-fri echo 'Go home!'
This prints the string at 5:30 p.m. each working day. The two
asterisks mean every day of the month, and every month of the year
respectively. The string mon-fri
means the days
Monday to Friday inclusive. More details about
cron
can be found by running
man
crontab
in a Linux shell.
We can add our housekeeping query to our cron table by running:
% crontab -e
This edits the user’s cron table.
We have decided that the system should check for old shopping carts every 30 minutes. To do so, we add the following two lines to the file:
0 * * * * /usr/local/mysql/bin/mysql -uusername
-psecret
-e 'USE winestore; DELETE FROM orders WHERE unix_timestamp(date) < (unix_timestamp(date_add(now( ), interval -1 day))) AND cust_id = -1;' 30 * * * * /usr/local/mysql/bin/mysql -uusername
-psecret
-e 'USE winestore; DELETE FROM items WHERE unix_timestamp(date) < (unix_timestamp(date_add(now( ), interval -1 day))) AND cust_id = -1;'
The first line contains the complete query command for the
orders table from earlier in this section, and
the second line the items query. The shopping
cart orders
DELETE
query runs
exactly on each hour, while the items
DELETE
query runs at 30 minutes past each hour.
Different times are used to balance the DBMS load.
Reports, updates, delete operations, and other tasks can be added to the cron table in a similar way. For example, we can output a simple report of the number of bottles purchased yesterday and send this to our email address each morning:
0 8 * * * mon-fri /usr/local/mysql/bin/mysql -uusername
-psecret
-e 'USE winestore; SELECT sum(qty) FROM items WHERE unix_timestamp(date) > (unix_timestamp(date_add(now( ), interval -1 day))) AND cust_id != -1;' | mail [email protected]
We could also have automatically written the information to a log file or to a table in the database.
18.221.126.56