dcsimg

MySQL 5 Stored Procedures, Views, and Triggers

Take a hands-on tour of stored procedures, views, and triggers, just three of the new features found in MySQL 5.

As part of Linux Magazine’s coverage of the release of MySQL 5, developers Jay Pipes and Michael Kruckenberg have created an application that demonstrates stored procedures, views, and triggers, three of the release’s notable new features. In the coming months, additional articles will expand the application to highlight other advanced features of MySQL 5.

Let’s construct a simple stock trading application to track price changes for a variety of common securities. The application is written in Python and uses MySQL 5 to persist the data. (While the application is written in Python, you don’t need to be a Python expert to follow along.) To follow along, download and install MySQL 5 and Python (if need be), and grab the source code for this article from http://www.linux-mag.com/downloads/2005-12/mysql5/source.tgz.

To begin, start with the simple two-table schema detailed in Listing One.

Listing One: A schema to create the database and the requisite tables for tracking stock prices

DROP DATABASE IF EXISTS stock_app;
CREATE DATABASE stock_app;

USE stock_app;

CREATE TABLE IF NOT EXISTS Stock (
stock_symbol CHAR(5) NOT NULL,
name VARCHAR(30) NOT NULL,
PRIMARY KEY pk_stock (stock_symbol)
);

CREATE TABLE IF NOT EXISTS StockPriceHistory (
stock CHAR(5) NOT NULL,
time_taken DATETIME NOT NULL,
price DECIMAL(9,4) NOT NULL,
PRIMARY KEY pk_stock_price_history (stock, time_taken)
);

GRANT SELECT, INSERT, UPDATE, DELETE
ON stock_app.* TO ’stock_user’@’localhost’
IDENTIFIED BY ’stock_password’;

The Stock table contains some basic information on each of the securities of interest, and the StockPriceHistory table is used to maintain a history of the price of each security over time. The GRANT statement creates a user account and assigns SELECT, INSERT, UPDATE, and DELETE privileges to that account.

Listing Two: Populating the stock tracking tables shown in Listing One

USE stock_app;

INSERT INTO Stock (stock_symbol, name) VALUES
(’RHAT’,’Red Hat Inc.’),
(’DELL’,’Dell Inc.’),
(’AMD’,’Advanced Micro Devices’),
(’INTC’,’Intel Corporation’),
(’MSFT’,’Microsoft Corporation’);

INSERT INTO StockPriceHistory (stock, price, time_taken) VALUES
(’RHAT’, 34.05, NOW()),
(’DELL’, 56.65, NOW()),
(’AMD’, 23.75, NOW()),
(’INTC’, 45.63, NOW()),
(’MSFT’, 33.87, NOW());

Listing Two initializes the tables with some sample data, while Listing Three shows a short Python script that connects to a web service and to the stock database to fetch and persist additional data in near real-time, depending on the quote delay, if any. (See the sidebar “Why Python?” for more information on why Python was chosen for this example.)

Listing Three: Retrieving and storing stock data using Python

01#! /usr/bin/python
02from SOAPpy import SOAPProxy
03import MySQLdb as mysql
04
05url = ’http://64.124.140.30:9090/soap’
06namespace = ’urn:xmethods-delayed-quotes’
07server = SOAPProxy(url, namespace)
08
09query = “”"SELECT stock_symbol FROM Stock”"”
010db = mysql.connect(host=”localhost”,
011 user=”stock_user”,
012 passwd=”stock_password”,
013 db=”stock_app”)
014
015cursor = db.cursor()
016cursor.execute(query)
017
018stocks = cursor.fetchall()
019
020quotes = []
021
022for stock in stocks:
023 quotes.append((stock[0], server.getQuote(stock[0])))
024
025insert_stmt = “”"INSERT INTO StockPriceHistory (stock, time_taken, price)
026 VALUES (%s, NOW(), %s)”"”
027cursor.executemany(insert_stmt, quotes)
028cursor.close()

Listing Three calls the getQuote web service (provided by http://www.xmethods.org) for each security in the Stock table. The web service is configured in Lines 5-7; the list of securities is generated by the SELECT statement and returned in a list (an array) in Lines 9-18; Lines 22-23 iterate over the list, calling the web service to find each stock’s current price; and the last four lines save the values to the StockPriceHistory table with a timestamp. (The executemany() function of the MySQLdb.cursor object is used as a shortcut to execute the insert statement on Lines 25-26 for each stock symbol in the quotes list.