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’);
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 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.
Why Python?
If you haven’t tried Python yet, you need to. Because of its terse but coherent syntax, Python is ideal for prototyping applications that, if necessary, can then be ported to another programming language. Python’s structure and flow is also easy to understand, even if you’ve never used the language before.
Moreover, interacting with web services and the MySQL server is a trivial matter in Python, as the SOAPpy and MySQLdbmodules do all of the grunt work, allowing you to focus on business rules and application logic. (To use web services in Python, make sure you’ve installed the PyXML, fpconst, and SOAPpy modules. You can either Google each of these packages and follow the installation instructions after unpacking the tarballs, or you can visit Mark Pilgrim’s stellar “Dive Into Python” web site to get additional help. Pilgrim’s site can be found at http://diveintopython.org/soap_web_services/install.html.)
If you’re not familiar with Python, the only semantic that may need clarification is the use of the term list — what other languages typically call an array. In Python, a list is a general type of array object that can contain other specialized arrays of other objects. For the purposes of this article, however, you can simply treat each list as an array of items.
Introduction to Stored Procedures
A stored procedure, or routine, is simply a container for a set of SQL statements. Stored procedures can contain conditional statements and processing logic that normally would be unavailable in a traditional dynamically generated SQL statement.
For instance, a stored procedure can contain looping constructs and conditionals (do / while, if / then / else, and so on), permitting more complex operations.
Having SQL code in stored procedures allows applications to minimize the amount of SQL code that appears in the application and push that code into the tier of the database. This enforces cleaner application design and frees application code pages of unnecessary dynamic SQL code clutter.
An easy improvement to the stock tracking application is to encapsulate the insertion of a new stock price record into a callable stored procedure. Listing Four shows the SQL code to create a stored procedure, called InsertStockPrice(). The code contains a simple INSERT statement copied from LIsting Three’s dynamic SQL code. In MySQL, stored procedures are declared with the CREATE PROCEDURE keywords, and are followed by parameters enclosed in parentheses, and the procedure’s SQL statements enclosed with BEGIN and END contruct.
Listing Four: A stored procedure for inserting stock prices
DROP PROCEDURE IF EXISTS stock_app.InsertStockPrice;
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 ;
GRANT EXECUTE ON stock_app.* TO ’stock_user’@’localhost’;
The line DELIMITER // is not a comment. Because the MySQL command line client views the semicolon (;) as the statement delimiter by default, Listing Four must change the delimiter to something other than the semicolon, since the semicolon is needed within the CREATE PROCEDURE statement to define which SQL statements the procedure should run when called. The final DELIMITER; statement restores the delimiter back to the semicolon.
The GRANT statement in Listing Four permits the database user to EXECUTE the newly-created InsertStockPrice() stored procedure.
To add the stored procedure in Listing Four to the database, simply run:
$ mysql –u root –p < listing4.sql
(While the MySQL root user is used here, you can modify the database using any MySQL user account that has privileges to create databases and tables on the server.)
Power Tip
Keep stored procedures into text-based files instead of defining the procedures at the mysql command prompt. Text-based SQL files can be maintained under revision control just like any other source code file.
Once the stored procedure is defined in the database, you can replace the dynamic INSERT statements in Listing Three with the stored procedure. Listing Five shows lines 25-26 of Listing 3 converted to the call to the stored procedure.
Listing Five: Using the stored procedure in Listing Four from Python 01… 02 03insert_stmt = “”"CALL InsertStockPrice (%s, %s)”"” 04cursor.executemany(insert_stmt, quotes) 05cursor.close()
Notice that instead of the (ugly) dynamic SQL statement, the code simply issues a CALL followed by the name of the routine and the parameters enclosed by parentheses. In this way, stored procedures are executed in a similar fashion to regular functions.
This example stored procedure is only the tip of the iceberg when it comes to the power and flexibility that stored procedures bring to MySQL 5. For instance, stored procedures can encapsulate the transaction processing power of the InnoDB storage engine in a handy container, freeing application code of that messy (and annoying) BEGIN TRANSACTION… ROLLBACK / COMMIT code.
Encapsulation with Views
The next feature to add to the application is the ability to” watch” certain stock symbols to compare how particular stock portfolios have done over time and to compare the performance of watched stocks to the average of all the stocks in our database.
Listing Six introduces two more tables to the stock tracking application: Watchlist and WatchlistStock.
Listing Six: Tables for” watchlists”
USE stock_app;
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) );
The Watchlist table contains an identifier and a name for arbitrary lists of stocks contained in the WatchlistStock table. These watchlists can be used by the application to group stocks into lists for purposes of comparing sample “portfolios.”
To show a list of the stocks in a specific watchlist, along with the percentage of the total watchlist’s value that the stock represents within the watchlist portfolio, use a SELECT statement like the one in Figure One.
Figure One: SELECT- ing the watchlist data
mysql> SELECT -> w.name as “List”, -> ws.stock as “Stock”, -> ws.quantity_owned as “Qty”, -> sph.price as “Cur Price”, -> (ws.quantity_owned * sph.price) as “Cur Value”, -> ROUND((ws.quantity_owned -> / TotalWatchlist.total_shares) * 100, 2) as “% List” -> FROM Watchlist w -> INNER JOIN WatchlistStock ws -> ON w.watchlist_id = ws.watchlist -> INNER JOIN ( -> SELECT stock, MAX(time_taken) as last_time -> FROM StockPriceHistory -> GROUP BY stock -> ) as CurrentStockTime -> ON ws.stock = CurrentStockTime.stock -> INNER JOIN StockPriceHistory sph -> ON sph.stock = CurrentStockTime.stock -> AND sph.time_taken = CurrentStockTime.last_time -> INNER JOIN ( -> SELECT watchlist, SUM(quantity_owned) as total_shares -> FROM WatchlistStock -> GROUP BY watchlist -> ) as TotalWatchlist -> ON w.watchlist_id = TotalWatchlist.watchlist -> ORDER BY “List”, “Stock”, “% List” DESC; +——————–+——-+—–+———–+————+——–+ | List | Stock | Qty | Cur Price | Cur Value | % List | +——————–+——-+—–+———–+————+——–+ | List #1 (Mix) | AMD | 50 | 24.0000 | 1200.0000 | 20.00 | | List #1 (Mix) | DELL | 50 | 32.5900 | 1629.5000 | 20.00 | | List #1 (Mix) | INTC | 50 | 24.0700 | 1203.5000 | 20.00 | | List #1 (Mix) | MSFT | 50 | 24.6700 | 1233.5000 | 20.00 | | List #1 (Mix) | RHAT | 50 | 20.4800 | 1024.0000 | 20.00 | | List #2 (Hvy Chip) | AMD | 300 | 24.0000 | 7200.0000 | 37.50 | | List #2 (Hvy Chip) | INTC | 450 | 24.0700 | 10831.5000 | 56.25 | | List #2 (Hvy Chip) | RHAT | 50 | 20.4800 | 1024.0000 | 6.25 | +——————–+——-+—–+———–+————+——–+ 8 rows in set (0.00 sec)
The SELECT statement in Figure One uses a series of JOIN ed regular and derived tables (a special kind of subquery useful in aggregation) to produce the output.
Clearly, executing this SQL statement from the command line (or even cluttering the application code with this size of statement) is cumbersome. Until MySQL 5, however, there was little a developer could do to encapsulate this kind of complex statement. But, with MySQL 5, you have two options for encapsulating this statement into a cleaner container. The first method, using a stored procedure, has already been explained, and would be a fine option for separating this code into a database-level routine.
A second option would be to use a new MySQL 5 view. A view is simply a SELECT statement that can be used like a regular table, but that need not be defined as a separate table.
A major disadvantage of stored procedures is that they cannot be “embedded” in other SELECT statements, meaning code such as the following is illegal:
SELECT * FROM MyStoredProcedure();
A view, on the other hand, can be defined to represent a selection of various fields from various tables, and then used as a “virtual table” in other SELECT statements in a format such as:
SELECT * FROM MyView;
The basics of creating a view in MySQL 5 is simple: prepend CREATE VIEW before the SELECT statement that should populate the view. A simple view might be created with:
CREATE VIEW MyView AS SELECT field1, field2 FROM MyTable;
(Some caveats apply, as you’ll see in a moment.) Once created, you can SELECT from the view as normal:
SELECT * FROM MyView
(Internally, MySQL replaces the MyView with the SELECT statement defined by CREATE VIEW.)
To return to the stock application, you want to encapsulate the complex SQL statement in Listing Seven into a simple, easy to call, view. However, simply adding CREATE VIEW causes an error, because MySQL has some restrictions on what kind of SELECT statements can be defined as a view.
The most important restriction is that views cannot contain SELECT statements that involve derived tables. While that may seem odd, this restriction actually encourages componentization of your SQL statements. To define a view containing all the SQL code in Listing Seven, you must first define separate views representing the derived tables. In this way, MySQL actually forces you to create smaller, more re-usable pieces of code.
Listing Seven shows the three views created from the original SQL code in Figure One.
Listing Seven: Views for stock tracking
CREATE OR REPLACE VIEW stock_app.StockLastTime AS SELECT stock, MAX(time_taken) as last_time FROM stock_app.StockPriceHistory GROUP BY stock;
CREATE OR REPLACE VIEW stock_app.WatchlistTotals AS SELECT watchlist, SUM(quantity_owned) as total_shares FROM stock_app.WatchlistStock GROUP BY watchlist;
CREATE OR REPLACE VIEW stock_app.WatchlistSummary AS SELECT w.name as “List”, ws.stock as “Stock”, ws.quantity_owned as “Qty”, sph.price as “Cur Price”, (ws.quantity_owned * sph.price) as “Cur Value”, ROUND((ws.quantity_owned / wlt.total_shares) * 100, 2) as “% List” FROM stock_app.Watchlist w INNER JOIN stock_app.WatchlistStock ws ON w.watchlist_id = ws.watchlist INNER JOIN stock_app.StockLastTime slt ON ws.stock = slt.stock INNER JOIN stock_app.StockPriceHistory sph ON sph.stock = slt.stock AND sph.time_taken = slt.last_time INNER JOIN stock_app.WatchlistTotals wlt ON w.watchlist_id = wlt.watchlist;
There are now three separate views — StockLastTime, WatchlistTotals, and WatchlistSummary — that can be used.
For example, selecting just from the StockLastTime view yields:
Likewise, selecting just from the WatchlistTotals yields:
mysql> SELECT * FROM WatchlistTotals; +———–+————–+ | watchlist | total_shares | +———–+————–+ | 1 | 250 | | 2 | 800 | +———–+————–+ 2 rows in set (0.00 sec)
The WatchlistSummary view selects from StockLastTime and WatchlistTotals as replacements for the previously used derived tables. Taken together, the views help mask the complexity of the underlying SQL and encourage a clean componentization of your SELECT statements.
Instead of some very messy SQL code, the correct code is a one-liner:
mysql> SELECT * FROM WatchlistSummary;
Did you notice that there is no ORDER BY in the views? While it’s possible to include a default ORDER BY clause in a view, an ORDER BY clause used in a SELECT statement that calls the view overrides the ORDER BY in the view. This makes it easy to re-order view results just like any other SELECT statement:
mysql> SELECT * FROM WatchlistSummary -> ORDER BY “Cur Price”;
Using Triggers to Automate Statistics
Assume now that you’ve been running the watchlist for a few days and realize that there are a few more pieces of information that would be helpful to have in the stock summary tables. Specifically, you’d like to be able to quickly see the minimum, maximum, and average price of each stock at each time a new price is inserted into the StockPriceHistory table.
Creating a stored procedure or a view that calculated this data on the fly would be near impossible. Instead, your best bet is to add columns to store the calculated values and have the database automatically generate and populate the additional fields with a trigger that’s fired when the data is inserted. A trigger allows you to associate a set of SQL statements with an INSERT, UPDATE or DELETE event in the database. You can execute the SQL code before or after an event.
Listing Eight shows an ALTER statement to add the new columns.
Listing Eight: Altering the StockPriceHistory table to include new fields
Listing Nine shows a trigger that calculates the minimum, maximum, and average price for a given stock each time the stock is updated.
Listing Ten: A stock price history trigger 01DELIMITER // 02CREATE TRIGGER before_StockPriceHistory_insert 03BEFORE INSERT on StockPriceHistory 04FOR EACH ROW 05BEGIN 06 07DECLARE min DECIMAL(9,4); 08DECLARE max DECIMAL(9,4); 09DECLARE records INTEGER; 010DECLARE sum DECIMAL(9,4); 011DECLARE avg DECIMAL(9,4); 012 013SELECT min(price) INTO min 014FROM StockPriceHistory 015WHERE stock = NEW.stock; 016 017IF min < NEW.price THEN 018set NEW.min_price = min; 019ELSE 020set NEW.min_price = NEW.price; 021END IF; 022 023SELECT max(price) INTO max 024FROM StockPriceHistory 025WHERE stock = NEW.stock; 026 027IF max > NEW.price THEN 028set NEW.max_price = max; 029ELSE 030set NEW.max_price = NEW.price; 031END IF; 032 033SELECT count(*),sum(price) INTO records,sum 034FROM StockPriceHistory 035WHERE stock = NEW.stock; 036 037set NEW.avg_price = (sum + NEW.price)/(records + 1); 038 039END // 040DELIMITER ;
Lines 2-3 contain the initial CREATE TRIGGER statement, which specifies a name and whether to execute the trigger before or after the event — in this case, an INSERT. The timing of the trigger is important: if you want to change values that are going into a table you need to run the trigger before the data is inserted. Or, use AFTER INSERT to perform logic after the data has already been inserted.
The FOR EACH ROW on Line 4 is always required in a trigger: If the event involves multiple rows, the statements in the trigger execute once for each row. The BEGIN block on Line 5 indicates the start of the SQL statements. Lines 7-11 declare variables used to store values during the necessary calculations for the new columns.
Lines 13-21 perform the necessary lookups and logic to determine the current minimum stock price for the security. There are two notable differences about the query in Lines 13-15: In Line 13, the trigger does a lookup to find the stock’s current lowest price in the StockPriceHistory table. The SELECT… INTO statement puts the value of the SELECT into the variable min. Within a trigger, you must always SELECT values into variables (or use a cursor for SELECT statements that return multiple records).
The second difference in the SELECT in Lines 13-21 is the NEW keyword in Line 15. When working inside a trigger, you refer to table fields by one of two keywords, OLD or NEW. NEW refers to the new record to be put in the table; OLD refers to the existing data in the table that will be replaced (in an UPDATE event) or removed (during a DELETE event).
INSERT statements only have NEW records, because there is no existing record that is being replaced. For that reason, a trigger that executes on INSERT does not permit the use of OLD, as there is no existing data to access. Triggers that execute on an UPDATE will have access to both NEW and OLD records. With a DELETE trigger, you are only allowed to reference the OLD record, as there is no incoming, or NEW, record. Within a trigger you are required to specify the NEW or OLD keyword when referencing field names.
Lines 17-21 look at the existing minimum stock price and determine if the new price is lower than the previous minimum. The SET statement changes the values of the record being inserted. Next the trigger checks the maximum price, performing the same data check and comparison.
The last piece of the trigger is to determine the average price of the existing StockPriceHistory records at the time of a new entry to the table. Lines 33-35SELECT the sum of all prices and the number of records; Line 37 performs the necessary calculation, including the newly added price in the calculation.
With the new columns and trigger in place, you can use the InsertStockPrice() procedure and expect to see the added columns filled with the calculated values, as shown in Figure Two.
FIGURE TWO: Records can be manipulated based on events using a trigger
If you run the Python script in Listing Five for an hour at 10-minute intervals, the table has a larger set of data that demonstrates the trigger’s calculations in action. The new table is shown in Figure Three.
FIGURE THREE: Additional data has accumulated via the trigger
The trigger could obviously be expanded to do even more sophisticated calculations. The syntax for programming logic into triggers (and all routines for that matter) allows you to perform a great deal of logic inside the database.
Loads of Power
The examples shown here are overviews of just three new features available in MySQL 5. Additional syntax, not covered here, is available when creating stored procedures, views and triggers.
As with all substantial software upgrades — and MySQL 5 is a substantial upgrade, if you need the added power — it’s important to think carefully about how new technology fits into your needs before jumping in with both feet. Stored procedures, views and triggers shouldn’t be used just because the features are” there”. The features may be necessary or beneficial for some, but not all.
That being said, stored procedures, views, and triggers have been long-awaited features. In addition, MySQL 5 also includes a number of other improvements, including stored functions, server-side cursors, and the INFORMATION_SCHEMA meta-database. Stay tuned to Linux Magazine for more coverage.
Jay Pipes and Michael Kruckenberg are co-authors of the recently published book Pro MySQL (ISBN 159059505X) from Apress.
Loading ...
No Comments
No comments yet.