Database Developer MySQL Ready for Prime Time
Trends Logic in DB Moving Past Java Drivers ODBC JDBC OLE DB .NET Podcast SQL:2003 MS SQL 2005 XML DBs XQuery Webcast XQuery SQL:2003 MS SQL 2005 OLTP benchmarks InnoDB XA Case Study Nine Sabre		<< 1 2 Storage Engines MySQL can operate with multiple storage engines. The original ISAM storage engine has been replaced by a MyISAM engine that supports larger operating system files. MySQL also offers engines for operating with comma-separated values and in-memory tables. Starting with version 3.23, MySQL users could choose the Berkeley DB (BDB) storage engine. MySQL 4.0 added the InnoDB transactional storage engine. MySQL is working with Sleepycat Software on a transactional interface for use with the Berkeley DB storage engine. MySQL 5.0 also includes storage engines that support archives and federated data. It also introduces a BIT data type and extends the maximum length of the VARCHAR type to 65K. OLTP SQL platforms today are far removed from the SQL servers of a decade ago. Despite entire new classes of applications for SQL databases, there is still tremendous interest in SQL-powered online transaction processing (OLTP) systems.   Early versions of MySQL worked with an ISAM engine and b-tree indexing, but lacked support for transactions having ACID properties. For many users, the lack of transactions was a barrier to MySQL adoption. Today users have a choice of using MySQL with a transactional data store. The InnoDB storage engine provides row-level locking, foreign keys, commit, rollback and recovery support. MySQL 5.0.3 and higher versions also support distributed transaction processing using the InnoDB engine. MySQL distributed transactions conform to the X/Open XA specification, using global transactions and two-phase commit. A MySQL server will act as a Resource Manager that handles XA transactions within a global transaction. Clients connected to the MySQL server can act as the Transaction Manager. Database Security To maintain the integrity of databases, a DBMS must provide features for restricting access. It must provide authentication, authorization and a means to define privileges to act upon tables, procedures and other database contents. SQL database managers have implemented several approaches to security, with user-based security being the most common. Using this model, the database administrator can define privileges for individual users. MySQL does not currently support groups or role-based security, as explained in that earlier article: "Some DBMS products let you use roles and role separation to manage authorities and responsibilities. This allows you to divide administrative responsibilities so different roles have different responsibilities and permissions. Whereas you can grant administrators the equivalent of an "all access" pass, you can "use restrict" the object access of other roles. Groups and roles are convenient for authorizing a collection of users." Extensible Server Architecture After the major SQL vendors gained success in positioning their products in the transaction processing space, they added features to support online analytical processing (OLAP) and data warehousing. They also embraced extensible server architectures that embedded the Java Virtual Machine (VM),  and more recently, the .NET Common Language Runtime (CLR). The former, also known as Java-enabled databases, opens the door to Java stored procedures and user-defined functions (UDFs). The latter does the same for .NET programming languages.  Some platforms, such as Informix Dynamic Server, also provide the capability of plugging in custom indexing schemes to complement the built-in b-tree indexing. Because the MySQL architecture is flexible, it will operate with multiple storage engines. It does not, however, embed the Java VM or .NET CLR. XML Prime time SQL platforms have evolved to support data processing with SQL and document processing with XML. MySQL is looking at adding support for XML to the database server. One path for doing so is to integrate with Sleepycat Software's Berkeley DB XML. Operating as a layer over the Berkeley DB  storage engine, it provides XQuery and native XML storage and retrieval. MySQL has committed to ISO SQL standards compliance, which implies it will eventually support the SQL/XML functions and XML data type of the SQL:2003 standard. Future Looks Bright The future looks promising for MySQL because version 5.0 makes it competitive for enterprise computing, with capabilities such as transactions, stored procedures, parallel processing and the capacity for very large databases. The free MySQL Community Edition is an excellent vehicle for expanding the MySQL developer community. That opens the door for license revenues, new products that complement MySQL, and consultants evangelizing MySQL to their clients. About the Author Ken North is an author and consultant who teaches Expert Series seminars. He wrote Database Magic with Ken North (Prentice Hall), Windows Multi-DBMS Programming (Wiley) and the Database Developer columns for Dr. Dobb's and Web Techniques. Online Resources MySQL Developer Zone http://dev.mysql.com Sabre Holdings http://www.sabre-holdings.com Sleepycat Software http://www.sleepycat.com/                              Database Server Watch  SQL Summit Home Page    Articles    © 2005, Ken North Computing LLC, All rights reserved.