Planned Improvements in MySQL 5.1

This article looks ahead to the improvements you can expect in MySQL 5.1. These include triggers, user variables, and other features. It is excerpted from chapter 8 of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress; ISBN: 1590593324).

MySQL 5.1

Looking even further ahead, we expect continued improvements in MySQL 5.1, with the possible addition of another major feature that so far hasn’t been implemented yet. This is the implementation of triggers. Triggers are almost certain to make an appearance in MySQL 5.1.

We’ll give you a conceptual overview of triggers in the following section, and discuss what form we think they’re likely to take when they’re actually implemented. While it’s true that no one can tell for sure what may happen in the future, we do know that MySQL AB say they intend to implement these according to recognized standards, so what we will do is to show you what ANSI SQL says that triggers should be like, and make some observations on how they’re implemented in other database management systems.

Triggers

A trigger is a form of stored procedure that executes automatically when a specified event takes place—that is, when an SQL statement modifies data. Triggers are bound to specific types of statements ( UPDATE , INSERT , or DELETE ) acting on specific tables in much the same way that event handlers or event listeners are bound to particular events occurring on particular interface elements in GUI programming. For instance, a trigger might be associated with an INSERT statement acting on the products table which we’ve been using in our examples, or with an UPDATE statement that affects only the price column of that table.

In addition, a trigger is declared in such a way that it acts either before or after the statement to which it’s bound takes effect. If the trigger acts before the data-modification statement does, then it cannot “see” any changes caused by that statement; it only “knows” that the statement will affect a given table in a certain manner but not the specifics of that action. A trigger that acts after the triggering statement does see any changes caused by the statement and can act upon them.

Some databases (including both SQL Server and Oracle) also support triggers that act in place of the triggering statement using INSTEAD OF rather than BEFORE or AFTER for this purpose. At this time, MySQL is not expected to support this nonstandard extension. However, as it certainly supports other non-ANSI additions to SQL, it may be that demand for this feature will be sufficient to prompt its addition in a future release. (As always, don’t count on this happening, but don’t rule it out, either; and do watch for further developments in this area.)

It’s also important to understand that triggers operate at the statement level by default. In other words, a trigger normally executes once when a statement of the proper type acts on the proper table, even if that statement affects 3,000 rows. However, the standard does allow for row-level triggers as well. If MySQL supports this in its trigger implementation, then it will be possible for a trigger to execute as many times as there are rows affected by the data-modification statement.

{mospagebreak title=Trigger Syntax}

Triggers are created using the CREATE TRIGGER command, which in standard SQL has the following syntax:

CREATE TRIGGER trigge r
{BEFORE | AFTER} {[DELETE] | [INSERT] | [UPDATE] [OF columns]} ON table [REFERENCING { OLD [ROW] [AS] oldname} | NEW [ROW] [AS] newname }]
FOR EACH {ROW | STATEMENT}
[WHEN conditions]
statement-bloc
k

Here, trigger is the name of the trigger, and we assume that you’ll be able to use any valid MySQL identifier, just as you can now for tables, stored procedures, and so on. The BEFORE or AFTER keyword tells MySQL whether the trigger will fire before or after the operation that modifies data has taken place, and the next clause determines what sort of operation will fire the trigger. Note that it is possible to define multiple operations for a single trigger. For instance, a trigger could be set to fire whenever a query either updates current records or inserts data into a given table, but ignore operations that delete records from that table. It may be possible in MySQL to define triggers on individual table columns as well, but it’s not certain at this time whether or not this will be supported, and if so, when.

The FOR EACH clause determines whether the trigger will act at the statement level or the row level, as discussed previously. The WHEN clause may be used to test one or more conditions as part of determining whether or not the trigger should fire. Using the REFERENCING clause, you can assign aliases to the “before” and “after” versions of the table (and columns) with respect to any changes wrought by the data modification statement to which the trigger is bound.

Finally, at the end of this definition comes a block of stored procedure code that is to be executed in the event that the trigger is fired. Here’s a brief example of what you might expect to see in the way of a trigger when triggers become available in MySQL. Bear in mind that this must be regarded for now as pseudocode and that the actual triggers implementation may require something slightly different than what you see here:

CREATE TRIGGER price_increase
AFTER UPDATE ON products
REFERENCING OLD TABLE AS oldproduct NEW TABLE AS newproduct
WHEN newproduct.price > oldproduct.price BEGIN
 
INSERT INTO price_increases (id, change_date, prodid, oldprice, newprice)
  VALUES (”, NOW(), oldproduct.id, oldproduct.price, newproduct.price);
END

This trigger (named price_increase) is fired whenever the price of an existing product is increased. When this happens, it inserts the date or date/time when the increase took place, the former price, and the new, higher price into a table named price_increases.

The stored procedure code in a trigger should work just like that found in any other stored procedure. We expect that you’ll be able to use branching, loops, data definition statements (such as CREATE TABLE and ALTER TABLE ), reference other stored procedures and stored functions, and so on.


NOTE  
PostgreSQL doesn’t actually support procedural code within a trigger; instead, you must execute a stored procedure that’s defined elsewhere. It’s conceivable that MySQL could go this route as well, although what information we have available suggests that you’ll be able to write a procedure as part of a MySQL trigger when triggers become available, since this capability is part of the SQL standard.

To delete a trigger once it’s no longer needed, the SQL standard provides for a DROP TRIGGER command, whose syntax is simply

DROP TRIGGER trigger;

where trigger is simply the name of the trigger that you wish to drop.


NOTE  
Oracle and Microsoft’s SQL Server both support an ALTER TRIGGER command that can be used for changing existing triggers. This command is not currently part of the SQL standard, and PostgreSQL supports triggers quite happily without it. It may not be implemented in MySQL, at least initially, although if there’s enough demand for it, it could conceivably be added for compatibility reasons, just as MySQL 5.0.1 supports ALTER VIEW .

Benefits

Triggers can be used for number of purposes, all of which contribute at least indirectly to making database operations more efficient. These include

  • Auditing and logging: As shown in the price_increase example, we can use a trigger to track certain types of changes in a database by copying or moving data from the table upon which the trigger was set to other tables. It’s also conceivable that you could use SELECT INTO OUTFILE or SELECT INTO DUMPFILE in order to create files in delimited formats that are compatible with other applications.
  • Preserving data integrity: Although it’s preferable to use datatypes and foreign key constraints to keep users and applications from inserting invalid data or updating it to “bad” values, there are times when this isn’t easy to accomplish. For example, suppose you don’t want any products to be listed in the products table if their prices exceed one hundred dollars. You might use a trigger to “catch” these before they’re inserted and cause them to be inserted into an expensive_products table instead.
  • Addressing security concerns: Triggers can be used to facilitate the segregation of sensitive data into tables or databases for which access can be restricted. Recalling one of the example scenarios we outlined in the “Taking Derived Tables to the Next Level—Views” section earlier in the chapter: Suppose you have an application that obtains membership data for a corporate organization, and you’d like to make some data such as name and e-mail address available to developers working on a public “address book” interface where employees can look up contact information for co-workers. However, you don’t want the address book to display private data such as Social Security or taxpayer numbers. To keep this from happening, even by accident, you can use a trigger for INSERT and UPDATE operations on a restricted master employees table that copies “public” data such as name, e-mail address, and company telephone extension into an employees_public table, and allow the address book developers access to this table only. While we can accomplish the same task using a view, it’s good to have an additional choice in implementation, especially in a highly complex system such as the personnel database for a large corporation.

{mospagebreak title=Other Expected Improvements}

There are numerous other fixes, enhancements, and additions planned for future versions of MySQL. In this section, we’ll take a brief look at some of these, particularly those that are of interest with regard to optimizing database schemas and queries or speeding up general performance.

Full (Outer) Joins

Full joins may be supported in MySQL 5.0 or 5.1. Full joins, which return a NULL for any column in one table that isn’t matched in the other, are discussed in Chapter 5.

User Variables

User variables in MySQL 5.0 have already been changed in that the names are no longer case sensitive. Prior to this, @MYVAR and @myvar were treated as separate variables; beginning with version 5.0, they’ll be regarded as the same variable.

Another planned change is to allow user variables to be updated in UPDATE statements. For example, if this is done, the following would be possible:

UPDATE mytable SET @myvar := col1 + col2;

Currently, this can be done only in a SELECT query. It’s also likely that user variables will eventually be usable in statements having GROUP BY clauses, like so:

SELECT id, @count := COUNT(*) FROM products GROUP BY category_id;

SET Functions

Two new functions for working with SET columns are planned. These are ADD_TO_SET() and REMOVE_FROM_SET() . Suppose that we have a users table containing a SET column defined as

language SET(‘English,German,Spanish’)

Were this function to be added, it would then be possible to update the column definition with something like this:

ALTER TABLE users
MODIFY language ADD_TO_SET(‘Portuguese’, language);

and this:

ALTER TABLE users
MODIFY language REMOVE_FROM_SET(‘German’, language);

without the need to reiterate all the elements in the set.

Group Functions

The SQL standard provides for three functions that allow you to find out very quickly whether or not any one, some, or all of a set of values is true:

  • ANY() : This function returns TRUE if one and only one value in the set is true; if no values in the set are true or if more than one value is true, then the function returns FALSE.
  • SOME() : This function returns TRUE if at least one value in a set of values is true.
  • EVERY() : This function returns TRUE if and only if all values in a set are true.

Here are some examples, noting that the query SELECT price > 100 FROM products; will return a set of 1s and 0s, that is, TRUE and FALSE values:

# If this query returns 1 (TRUE) then we know only one product has a pric e
# greater than 100.00:
SELECT ANY(SELECT (price > 100) FROM products);
# If there are some products (possibly all of them, but at least one of
# them) having a price greater than 100.00, then this query will return 1
# (TRUE), otherwise it will return 0 (FALSE):
SELECT SOME(SELECT price > 100 FROM products);
# If *all* products in the table have prices greater than 100.00, then this
# query will return 1 (TRUE):
SELECT ALL(SELECT price > 100 FROM products);

According to the SQL standard, these functions should work only with sets of Boolean values; however, this could work in MySQL with other types of sets as well, since MySQL interprets 0 as FALSE and any other number or string value as TRUE.

{mospagebreak title=Summary}

We’ve looked in this chapter at several key new features that are becoming available or are very likely to be over the next few versions of MySQL. All of these features will add significant functionality to MySQL, increasing its power and flexibility in different ways. It seems logical to approach future attractions in a version-by-version fashion, so we started with subqueries and other features implemented in MySQL 4.1, which is almost certain to be available in a production release before the end of 2004. Subqueries, or queries within queries, can often be used to make queries shorter, simpler, and easier to read. In some cases they can even allow us to do in a single SELECT what would otherwise require multiple queries to accomplish without them.

We’d like to remind you at this point that we covered the other major new advance in MySQL 4.1—a new client API—in Chapter 7. These new programming APIs include prepared statements and multiple statements and will make MySQL applications programming much more flexible and efficient.

We also looked at the ability to assign table indexes to separate caches for purposes of fine-tuning key caching, which (beginning in MySQL 4.1.1) can be used to make indexes more efficient.

MySQL 5.0 will provide an implementation of another key SQL concept: that of what are sometimes known as SQL control statements. This is another way of expressing the idea of packaging collections of SQL statements so that they can be reused. Databases that do this implement what are known as stored procedures and stored functions, and both of these were already present in the first pre-release version of MySQL 5 (version 5.0.0-alpha) that became available at the end of 2003. While it will likely be sometime in 2005 or 2006 before these are included in a production release, you can download the pre-release from http://dev.mysql.com now, test out the examples we included, and try your hand at writing your own. We also looked at cursors, which provide a way to point at a particular row within a result set and work with its contents.

Beginning with version 5.0.1, MySQL also implements views, which provide a means of defining what are sometimes known as virtual tables, based on queries of existing tables (and sometimes even of other views). Views are extremely powerful, not only because they can provide simplified real-time access to normalized data that would otherwise require complicated joins, but also due to the fact that in many cases they can also be used to update the tables on which they’re based. In addition, views can help us ensure that read and update access to table data is properly secured.

Looking further ahead, it seems likely that MySQL 5.1 will implement another key SQL feature—triggers. These provide a way to call stored procedures automatically in response to changes made in tables or views. For example, if a query will delete a record from a table, you can (or will be able to) use a trigger to copy data from that record to another table before it’s actually deleted.

We saw how stored procedures and views (along with triggers when they’re implemented) can make life easier for database designers, developers, and users and can help enhance data security as well. So far as MySQL 5.0 and 5.1 are concerned, we’ve only scratched the surface here. Complete coverage of stored procedures and views each could easily take up entire chapters—and in future books about MySQL, they very likely will. And while we discussed some other expected future improvements, there will doubtless be new additions that we’ve either not had space to cover here or haven’t been able to foresee. So, as you use MySQL and upgrade to new versions, watch the MySQL web site, and check the documentation for news about new features and changes in existing ones.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye