MySQL 5: information_schema and Strict Mode

Understand and control your data with information_schema and strict mode in MySQL 5.

Mike Kruckenberg, Jay Pipes

Thursday, June 15th, 2006

Share This:

December 2005’s feature story on MySQL 5 established the foundation of a stock tracking application using stored procedures, views, and triggers (available online at http://www.linux-mag.com/2005-12/mysql.html.) The February 2006 issue of Linux Magazine presented MySQL 5’s stored functions and cursors (see http://www.linux-mag.com/2006-02/mysql.html.) If you didn’t retain the database constructed in those two articles, use Listing One to (re) create the database and then follow along.[ You can download the SQL scripts and code used throughout this article from http://www.linux-mag.com/downloads/2006-06/source.tgz.]

LISTING ONE: A database schema and data for stock tracking

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

CREATE TABLE IF NOT EXISTS Watchlist (
watchlist_id SMALLINT UNSIGNED NOT NULL
, name VARCHAR(30) NOT NULL
, PRIMARY KEY pk_watchlist (watchlist_id)
);

CREATE TABLE IF NOT EXISTS WatchlistStock (
watchlist SMALLINT UNSIGNED NOT NULL
, stock CHAR(5) NOT NULL
, quantity_owned INT UNSIGNED NOT NULL
, PRIMARY KEY pk_watchlist_stock (watchlist, stock)
);

DELIMITER //

CREATE PROCEDURE stock_app.InsertStockPrice (
IN symbol CHAR(5)
, IN current_price DECIMAL(9,4)
) DETERMINISTIC
BEGIN
INSERT INTO StockPriceHistory (stock, price, time_taken)
VALUES (symbol, current_price, NOW());
END //

DELIMITER ;

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

CALL InsertStockPrice(’RHAT’,34.05);
CALL InsertStockPrice(’DELL’,56.65);
CALL InsertStockPrice(’AMD’,23.75);
CALL InsertStockPrice(’INTC’,45.63);
CALL InsertStockPrice(’MSFT’,33.87);

INSERT INTO WatchlistStock (watchlist, stock, quantity_owned)
SELECT 1, stock_symbol, 50.00
FROM Stock;
INSERT INTO WatchlistStock…