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.

Jay Pipes, Michael Kruckenberg

Monday, December 12th, 2005

Share This:

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’;