only for RuBoard - do not distribute or recompile Previous Section Next Section

3.8 Modifying the Database

In this section, we consider simple examples of writing data to databases. Multiple users writing data, how to manage locking of databases, and more complex transactions with the MySQL DBMS are discussed in Chapter 6.

3.8.1 Adding a New Wine to the Winestore

To illustrate a write transaction with the winestore database, consider an example of inserting a new wine. This process can be performed with the MySQL command-line interpreter. Only one user is interacting with the DBMS in this example.

Let's suppose that 24 bottles of a new wine, a Curry Hill Cabernet Merlot 1996 made by De Morton Hill wineries, have arrived, and you wish to add a row to the database for the new wine.

The addition has several steps, the first of which is an INSERT INTO statement to create the basic row for the wine in the wine table:

INSERT INTO wine 
  SET wine_name='Curry Hill', 
  type='Red', 
  year=1996, 
  description='A beautiful mature wine. Smooth to taste
               Ideal with red meat.';

This creates a new row and sets the basic attributes. The wine_id is set to the next available value because of the auto_increment and DEFAULT modifiers. The remaining attributes to insert require further querying and then subsequent updates.

The second step is to set the winery_id for the new wine. We need to search for the De Morton Hill winery to identify the winery_id:

SELECT winery_id FROM winery 
  WHERE winery_name='De Morton Hill';

The result returned is:

+-----------+
| winery_id |
+-----------+
|       221 |
+-----------+
1 row in set (0.00 sec)

We can now update the new wine row to set the winery_id=221. However, which row to update? An easy way to find the wine_id of the new wine row is to use the built-in function last_insert_id( ). As discussed in the earlier section Section 3.5, this function returns the number created by the most recent auto_increment modifier:

 SELECT last_insert_id(  );

This returns the wine_id of the inserted row:

+------------------+
| last_insert_id(  ) |
+------------------+
|             1029 |
+------------------+
1 row in set (0.00 sec)

You can now issue the UPDATE statement:

UPDATE wine SET winery_id = 221 
  WHERE wine_id = 1029;

The third step is to set the variety information for the new wine. We need the variety_id values for Cabernet and Merlot. These can be found with a simple query:

SELECT * FROM grape_variety;

In part, the following results are produced:

+------------+------------+
| variety_id | variety    |
+------------+------------+
|          1 | Riesling   |
|          2 | Chardonnay |
|          3 | Sauvignon  |
|          4 | Blanc      |
|          5 | Semillon   |
|          6 | Pinot      |
|          7 | Gris       |
|          8 | Verdelho   |
|          9 | Grenache   |
|         10 | Noir       |
|         11 | Cabernet   |
|         12 | Shiraz     |
|         13 | Merlot     |

Cabernet has a variety_id=11 and Merlot a variety_id=13. We can now insert two rows into the wine_variety table. Because Cabernet is the first variety, set its ID=1, and ID=2 for Merlot:

INSERT INTO wine_variety 
  SET wine_id=1029, variety_id=11, id=1;
INSERT INTO wine_variety 
  SET wine_id=1029, variety_id=13, id=2;

The final step is to insert the first inventory row into the inventory table for this wine. There are 24 bottles, with a per-bottle price of $14.95 and per-case price of $171.99:

INSERT INTO inventory VALUES (1029, 1, 24, 14.95, 171.99);

We've now completed the process of inserting rows into other tables in the winestore is similar. Adding data to the winery, region, inventory, and orders tables follows the same approach. Insertion of rows into the customer and grape_variety tables is simpler because there are no attributes that require lookups in other tables.

3.8.2 Buying a Bottle of Wine from the Winestore

In this example, we consider the steps required to buy a bottle of wine. Again, assume that there is only one user reading or writing data with the DBMS. The complete process—implemented as part of the winestore web database application—is described in Chapter 12.

To motivate this example, consider a customer, Dimitria Marzalla, who has added two bottles of the new De Morton Wines Curry Hill Cabernet Merlot 1996 to her shopping cart and now wishes to purchase the wines.

Before showing you how the purchase is finalized, let's examine the information recorded in the user shopping cart and what we know about the user.

First, we know that cust_id=1 is the ID for this customer and that the wine being purchased has wine_id=1029. This associated information has been previously determined in the process of collecting data for the purchase in the online winestore.

Second, we need to consider how the shopping cart is managed in the winestore. We use the orders and items tables to manage the shopping cart for each user. When a user adds the first item to her shopping cart, a new row is created in the orders table with a dummy cust_id=-1 and the next available order_id for this dummy customer. We use a dummy customer number because customers don't need to log in to add wine to their shopping carts, and because finalized orders are distinguished by having the cust_id of a customer who is a member.

For this example, assume that the shopping cart has order_id=354, and the dummy customer is cust_id=-1. Also assume that the row in the items table that represents the wine in the shopping cart has a cust_id=-1, an order_id=354, an item_id=1, a wine_id=1029, a quantity qty=2, and the price information for the wine. The price is $14.95 per bottle.

Before finalizing an order, we need to determine if there are two bottles of the wine available. A wine can be added to the shopping cart if there is any stock available, but this doesn't necessarily mean that there is more than one bottle left or that another user has not purchased the wine in the meantime. If there is sufficient wine available to finalize an order, we reduce the on-hand stock by two bottles. Checking if there are two bottles available can be done with the following query:

SELECT SUM(on_hand)
  FROM inventory 
  WHERE wine_id=1029;

A GROUP BY wine_id is unnecessary in this case because only one wine is selected.

Assuming there are more than two bottles available, we need to reduce the on-hand stock, beginning with the oldest inventory; this was one of the system requirements defined in Chapter 1. There are several ways to find the oldest inventory and the wine per-bottle price. A simple technique is to inspect the inventories:

SELECT inventory_id,cost,on_hand
  FROM inventory
  WHERE wine_id=1029
  ORDER BY date_added;

The oldest (and only) inventory_id=1, and there is an on-hand stock of 24 bottles. We then reduce the on-hand stock by two:

UPDATE inventory 
  SET on_hand = on_hand - 2
  WHERE wine_id=1029 AND inventory_id=1;

If the on-hand stock in an inventory row is reduced to zero—which isn't so in this case—we then remove that row:

DELETE FROM inventory 
  WHERE wine_id = 1029 AND inventory_id=1;

Other possibilities may also occur, such as having to manipulate two inventories because the oldest inventory has only one bottle left. These possibilities are discussed in further detail in Chapter 12.

Having reserved two bottles of the wine for shipping, we can finalize the order for the customer. To do so, we need to store the details of the shopping cart entries in the orders and items tables. As discussed previously, by tracking the shopping cart of this user we know it has the order_id=354 for the dummy cust_id=-1. We also need to know how many previous orders this customer has made:

SELECT max(order_id) FROM orders WHERE customer_id=1;

If you find the customer previously made two orders, you update the shopping cart order row so that it is now the third order for this customer. Use this statement:

UPDATE orders SET cust_id = 1, 
                  order_id = 3, 
                  date = NULL,
                  delivery = 7.95,
                  discount = 0
  WHERE cust_id = -1 AND order_id = 354;

The shopping cart entry is now a customer order. date=NULL sets the date attribute to be the current system time and date. The delivery cost is $7.95, and there is no discount on the order.

To complete the order, we also update the related items row in the shopping cart, which contains the two bottles of wine. Use the following UPDATE statement:

UPDATE items SET cust_id = 1,
                 order_id = 3,
                 date = NULL
  WHERE cust_id = -1 AND 
        order_id = 354 AND
        item_id = 1;

There is no need to update the wine_id, price, or qty (quantity).

We can now confirm to the customer the purchase of two bottles of Curry Hill and ship the order.

This isn't quite the whole picture of purchasing wines or updating the database. In Chapter 6, we return to similar examples and discuss the implications and problems of many users interacting with the database at the same time.

3.8.3 INSERTing with a SELECT Statement

We'll now show how insertion and querying can be closely tied together with an INSERT INTO ... SELECT statement. This is useful for copying data and, if needed, modifying the data as it is copied.

Consider an example to create a permanent record of the total sales to each customer up to this month, let's say it's April. First, create a simple table to store the customer and sales details:

CREATE TABLE salesuntilapril 
(
 cust_id int(5) NOT NULL,
 surname varchar(50),
 firstname varchar(50),
 totalsales float(5,2),
 PRIMARY KEY (cust_id)
);

Now issue a combined INSERT INTO ... SELECT statement to populate the new table with the customer details and the total sales:

INSERT INTO salesuntilapril 
  (cust_id, surname, firstname, totalsales)
   SELECT customer.cust_id, surname, firstname, SUM(price)
     FROM customer, items
     WHERE customer.cust_id = items.cust_id
     GROUP BY items.cust_id;

The four attributes listed in the SELECT statement are mapped to the four attributes listed in the INSERT INTO statement. For example, the customer.cust_id in the SELECT line is mapped into cust_id in the salesuntilapril table.

A query on the new table shows part of the results:

SELECT * from salesuntilapril;
+---------+-------------+-----------+------------+
| cust_id | surname     | firstname | totalsales |
+---------+-------------+-----------+------------+
|       2 | LaTrobe     | Anthony   |     566.42 |
|       3 | Fong        | Nicholas  |     821.78 |
|       4 | Stribling   | James     |     181.69 |
|       5 | Choo        | Richard   |     534.99 |
|       6 | Eggelston   | Perry     |     657.37 |
|       7 | Mellaseca   | Kym       |    1216.88 |

There are two sensible limitations of the INSERT INTO ... SELECT statement: first, the query can't contain an ORDER BY, and second, the FROM clause can't contain the target table of the INSERT INTO.

only for RuBoard - do not distribute or recompile Previous Section Next Section