Opinions, Expertise, Passion.

Yes, it may seem rather strange to the readers but MySQL has a booth at this week’s Oracle Open World 2007 and I’m here in San Francisco Wednesday and Thursday. If your in SF come in and say hi. Check out the Official Press Release and MySQL listed Oracle Resources for this conference.

An interesting recent report Oracle Users Indicate Increase in Use of Open Source sheds some light of the significance of MySQL within the Oracle Community.

The Independent Oracle Users Group (IOUG) has released its second major research study focusing on open source adoption trends, “Open Source in the Enterprise: New Software Disrupts the Technology Stack.” Conducted for the IOUG by Unisphere Research and sponsored by MySQL AB, the study builds on a similar report issued in mid-2006 covering open source adoption trends in the enterprise stack. The findings point to ongoing interest in open source software solutions for operating systems, databases and middleware with most organizations reporting that they intend to increase their use of open source in general over the coming year. However, open source applications remain less widely adopted.

For more information read the full report here.

Sheeri talked a little about MySQL Triggers in One TRIGGER Fact, Optimizer Rewriting Stuff. While it’s great that MySQL 5.0 has Triggers, there are some things that I feel could become features in future releases.

IF EXISTS

One of the beautiful features that MySQL has is IF EXISTS. This ternary operation that if the object exists performs the operation, of not it does nothing works wonders in reviewing logs for errors. One of the problems with Oracle for example, is the requirement to ignore the ORA errors for non-existent objects.

But this functionality doesn’t exist for Triggers? One must wonder why. I’d like to see this.

mysql> DROP TRIGGER IF EXISTS trigger_name;

MySQL Manual DROP TRIGGER

REPLACE

On feature that simplifies the lack of IF EXISTS functionality using Oracle is REPLACE. The syntax is:

oracle> CREATE OR REPLACE TRIGGER trigger_name...

In this case, this functionality effectively eliminates the need for a DROP IF EXISTS, however I’d not like to see this introduced into MySQL especially it’s an Oracle specific syntax and not ANSI SQL.

Multiple Triggers of same type per table

MySQL only allows one Trigger per type per table. That is, there can only be one BEFORE UPDATE for example. While you may ask the question why you would need this functionality. Here is a typical situation.

You use Triggers to perform some level of business functionality, determining values for optimised (denormalised) columns is a good example. So you need to write an appropiate trigger for that piece of functionality.
You also use Triggers to perform database auditing, that is for every insert/update/delete of data, you record a full copy of the change in an audit database. One way to ensure this is consistent across the entire database is to implement via triggers. So you leverage programming functionality to pre-create triggers for all tables to manage this.

The problem with MySQL occurs in that you have to now merge these triggers for tables that require both. If you want to deploy your application into a test environment, you may wish to not deploy your auditing triggers, but now you have this functionality mixed in with business logic.

Multi Type Triggers

Another cool Oracle feature is the capacity to define a trigger for multiple types. For example:

oracle> CREATE OR REPLACE TRIGGER trigger_name
oracle> BEFORE INSERT OR DELETE OR UPDATE ON table_name ...

In this example, with MySQL you would need to create three seperate triggers.

Other features

While not as important and one would need to consider if necessary in MySQL are some other Oracle provided trigger functionality. This includes:

• WHEN CLAUSE trigger restriction
• Triggers on DDL Statements such as CREATE, DROP, ALTER
• Triggers on Database events such as LOGON, LOGOFF, STARTUP, and SHUTDOWN
• INSTEAD OF Triggers (for Views)
• STATEMENT based trigger

For more information you can check out the Oracle Documentation on Introduction to Triggers, CREATE TRIGGER, Documentation Search on Triggers

A Wednesday afternoon presentation at the MySQL Conference was MySQL Migration Toolkit by Mike Zinner from MySQL AB.

In summary, the Migration Toolkit currently provides the following feature set.

• Wizard like interface
• Plug-in architecture
• Migration of Oracle/MS SQL Server/MS Access/Generic JDBC Data Sources. 2006 support for Sybase and DB2.
• Provides user support to expand to other databases
• Customisable the produced wizard scripts at any step
• Completely Scriptable (the wizard interface can produce command line replay scripts) -allows re-running, re-scheduling
• Binaries include an Eclipse Java Project to enable easy extension of the Migration Toolkit.

The product is built on the Generic Runtime Environment (GRT) which is used in other MySQL GUI Products. The GRT is a thin C Layer that allows you to easily add modules written in other languages (e.g. C/C++, Java, Lua, Python etc).

The following are the steps of the Migration process.

• Source/Target
• Object selection
• Object Mapping
• Manual Editing
• Schema Creation
• Data Mapping
• Bulk Transfer
• Summary

Some more relevent points on some of the individual steps.

Source/Target

• Screen caters for source specific requirements (e.g. SID for Oracle)

Object selection

• Choose the different Schema Objects, including tables, views. Can limit by type, or individual objects
• For Oracle additional objects include Schemas, Routines and Sequences

Object Mapping

• Define Methods for mapping of schema objects
• Includes Language types (e.g. latin1), multilanguage (utf8), or a user defined
• For Tables you can choose the purpose which includes data consistency, statistical data. These rules will include determining best storage engine, intepreting auto increment columns.

Manual Editing

• You Can drill down in great detail to define the object mapping, down to a column level
• Provides a migrated objects review, including warnings (for example views need to be manually checked, or sequence not migrated)

Schema Creation

• Can create objects in real-time or create a SQL script for later executation
• You can review the generate SQL code that will be used in real-time and perform further manual refinement (e.g. converting to specific SMALLINT,INT,MEDIUMINT refinement)
• Will show any warnings, and allow for manual correction

Data Mapping

• Can create data in a real-time to a MySQL database
• Create Insert Statements (normal data and blob data) (optimized multi-row insert with byte limits)
• No facility at present for flat file creation/loading via LOAD INFILE

Bulk Transfer
Summary

• A migration report which can be saved to disk.
• Create a Migration script for the complete process. (which is a generated lua script)

I can see the possibility for the following enhancements.

• Consider ENUM fields by analysing data (columns char < 20) and offer as data type
• Consider determining AUTO_INCREMENT columns

References
MySQL Migration Toolkit - Provides details, screenshots and Flash Tutorials for SQL Server, Oracle and Access Migrations.

As mentioned in my earlier post Emulating Oracle Output Functionality, I’ll be speaking at the MySQL Users Conference on the topic of MySQL for Oracle Developers. Here is the second in a series of points regarding current MySQL Stored Procedures and Functions functionality.

• 3. Named Parameters
• 4. Procedure Overloading

3. Named Parameters

Parameters passed to Procedures under normal operations can be considered positional parameters. For each parameter, a calling statement is required to pass the same number of parameters and in the same order. Named parameters allows the user to pass parmeters in the order of their choosing, and also not pass all parameters.

For Example, consider the following Oracle Stored Procedure definition.

  EXAMPLE_PROCEDURE(num IN NUMBER DEFAULT 0,
                    dte IN DATE DEFAULT SYSDATE,
                    str IN VARCHAR2 DEFAULT NULL,
                    str2 IN VARCHAR2 DEFAULT NULL);

This can be called in several ways, here are few examples.

  EXAMPLE_PROCEDURE();
  EXAMPLE_PROCEDURE(9,  '01-MAR-2006', 'Hello World', NULL);
  EXAMPLE_PROCEDURE(dte => '01-MAR-2006');
  EXAMPLE_PROCEDURE(dte => '01-MAR-2006', str2 => 'Hello World');

The key to being able to use named parameters, is the addition of the DEFAULT syntax in the variable definition.

Giuseppe Maxia has many MySQL stored procedures, and I applaud his initiate with The MySQL General Purpose Stored Routines Library. Included here is an implementation of handling named parameters. I can’t say I’ve used it personally, however I did browse the code (another Open Source benefit).

4. Procedure Overloading

Oracle provides the capability like Java, which allows for multiple procedures to have the same name, but have a different set of parameter arguments.

Oracle provides the ability to overload a Procedure call with the same number of arguments and varying datatypes, for example, going with the earlier DBMS_OUTPUT.PUT_LINE analogy, Oracle has the following specification.

  DBMS_OUTPUT.PUT_LINE (item IN NUMBER);
  DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2);
  DBMS_OUTPUT.PUT_LINE (item IN DATE);

Likewise, it possible to have procedures with different numbers and types of arguments.

  EXAMPLE_PROCEDURE(num IN NUMBER);
  EXAMPLE_PROCEDURE(num IN NUMBER, str IN VARCHAR2);
  EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE);
  EXAMPLE_PROCEDURE(num IN NUMBER, dte IN DATE, str IN VARCHAR2, str2 IN VARCHAR2);
etc.

Notes

As MySQL growth continues and developers take up placing greater business logic in the database with Stored Procedures, there may be merit to consider these points. On the flip side, 10 years ago, business logic was placed in the database for many reasons including the extent of capable client programming languages, a Client/Server architecture and of course tight coupling with Oracles GUI products. One could argue a justification as to how much business logic is stored in the database, and how much is managed within an application. Given the advent of the Web, and multiple applications and clients assessing coporate data, it’s logical to place essential logic as close to the source of the data as possible, and the introduction of Stored Procedures in MySQL 5 released last year provided the capacity to consider this.

At this time, I would err on the conservative side with the use of Stored Procedures. The type of application, requirements and signficantly the age of the product (being a new product or existing legacy product) all affect the outcome. As mentioned, Giuseppe feels strongly regarding named parameters, and has provided a workaround. The benefit with Open Source is this FREEDOM clearly exists when the community contributes. This can only benefit MySQL in areas such as Stored Procedures.

Myself, only a few days ago over the weekend, I took an Java Open Source product, that lacked the capacity to support the calling of Stored Procedures via JDBC, developed it myself and then submitted my work back to the Apache community. I was even more happy when it was accepted unaltered, committed and in the nightly build for the next day. Read More. I’m now working on the next contribution to the same project, providing JDBC Transaction Support. While this may seem a sidetrack, I’m actually specifically using this product in testing and usability of MySQL, so ultimately everybody wins.

I stand corrected on my earlier post Emulating Oracle Output Functionality (which I’ve updated) when I made a reference to MySQL catching up. That was not what I was implying, that MySQL had to catchup to Oracle. I was indeed making reference that MySQL is a 10 year old product, Oracle almost 30, and with a far greater historical R&D budget, MySQL as an organisation has for lack of a better term, entered the race at a later time. One could also argue it’s not a race, I’m just using this analogy.

For those that might have thought this in reading my earlier article, I’d encourage you to re-read my updated introduction for my clarification and correction.

The comment was made to me, “Oracle is very good at being Oracle” which is totally correct, and the “MySQL’s market now overlaps that of Oracle, and so there is competition.” Indeed Oracle, as a leader in the RDBMS industry has directed development in functionality and features.

We (being the MySQL product and Open Source) are indeed in competition (with other RDBMS products, both commercial and open source) , and will always be, however those that know me would also know that differing products serve to provide differing features and functionality in enterprise solutions. Some have strengths over others, and also weaknesses.

I am a strong advocate of larger enterprises currently using Oracle to embrace both Oracle and MySQL within an organisation for the benefit of serving differing purposes. I do not believe they are mutually exclusive. I believe there needs to be a looser coupling in the emerging marketplace. Of course, if a new company was starting tomorrow, and wanted to use Oracle, I would question the requirements as MySQL will serve the needs for most practical enterprise solutions. Had that question been asked 5 years ago, it would depend signficantly on the requirements, 10 years ago it would not have been a consideration.

I was asked a simple question today by a collegue who is an experiened Java Developer and Oracle user and had just installed SUSE on his personal laptop, to align closer with his work environment. What MySQL should I install, the MySQL web site has this and this and this?

Simple question, but the answer isn’t as simple, especially when MySQL now has a number of different products encompassing client functionality in addition to the MySQL server.

Oracle got it right with the latest edition Oracle 10g Express Edition (XE). A simple one rpm install that includes the Oracle Server, Oracle client, a Web Based Administrator, Query Tool and Application Development tool. I guess as we are all experienced, the simple question isn’t something we have a some notes on handy, hence the purpose of my entry A Basic MySQL Developer Installation. NOTE: This is for a developer environment, installation would be simplier using RPM’s for a compatible Distro.

On that note, It would be good for MySQL to provide a more complete installation of it’s suite of products rather then letting 3^rd parties package it all together. Problem is there are so many possible combinations, what do you do.

I’m a data modeller. I specialise in this, and for a number of years on large projects I’ve been able to focus on this single task within the System Development Life Cycle of software development for several months at a time. Unfortunately what depresses me the most, is I can’t get a full time position in what I’m an expert in. It’s not a specialised skill that an organisation can use on a full-time basis, unless it’s a large organisation, and quite frankly, Brisbane isn’t a market that can support the diversity of large organisations. (caveat, large organisations that are proactive in software development, not just large organisations that have significant IT requirements, but do not work proactively). This is why I can also do Software Development, Database Administration, and even System Administration. Again, I’m not good enough to fill one of these positions in a larger organisation as an expert, but I can generally hold my own, usually even with surpising results. (Side note, even this week, I was providing a possible solution and tool for system adminstration across a large organisation, and it was 5 mins work. Something the paid full-time system administrators were not providing????)

I only started looking at Domas Mituzas wordpress: friendly look at query log. I didn’t have to read far to see where it was going, and well I quite quickly turned off, sorry Domas, I’m sure your concluding points were valid. This is my point, and it has been echoed in our local MySQL users group as well, the lack of appropiate database design in open source projects. There are several contributors, but one I put down to the “Hobbist and the Professional Syndrome”. A topic for further discussion, but in summary here are the bullet points from a slide in a presentation I prepared.

Hobbist

• Downloadable software and examples
• Online tutorials
• Books like Learn in 24 hours/For Dummies

Professional

• Formal Qualifications
• Grounding in sound programming practices
• Understanding of SDLC principles
• Worked in team environment

Middle Ground Developer

• Time to skill verses output productivity
• Depends on environment and requirements

And on a final note, I guess why doing some raving. I find it criminal that organisations encourage at times a level of incompetence by promoting people that develop bad code into positions where they can continue to ensure that bad code stays, and further business decisions only engrain an organisation down the continued wrong path. There is already enough poor software developers out there that give the industry a bad name, but the good ones are few and far between.

What do we do, how can we solve this problem? I don’t think it can be solved now in the Open Source community. Adopting an Agile Development methodology such as Extreme Programming (XP) for example, it a very good start in organisations, something I’ve been working with now, or working with the principles for 6 years.

PS: Modelling is actually spelt both Modeling and Modelling (2 l’s) across the various English derivitives. Just incase somebody wanted to make comment.

Updated 28-mar-2006
There really is no way to do a comparision by numbers in features and functionality when it comes to Oracle and MySQL in the area of Stored Procedures and Triggers. Oracle does provide a far greater and extensive features list, however having more only means it has more.

We all know this, MySQL is a growing evolving database product, having just celebrated it’s tenth anniversary, Oracle on the other hand, will next year be 30, and has had significant funding in R&D being up until recently the second largest software company world wide. However, being open source, MySQL has the advantages of being lean and mean, providing only functionality users actually want and use, and has the community and organisation that is focussed clearly on providing new and improved functionality that users need.

I’ll have a lot more to speak about on this topic at my MySQL User Conference presentation MySQL for Oracle Developers next month, however in the preceeding weeks I thought I’d open some discussion and debate on various features, particularly in the area of Stored Procedures.

Here are some initial points, I’d like to talk about today. The purpose of this discussion is to highlight the difference between these two products, and possibilly other RDBMS products. While a RDBMS offers a feature, I am not stating that MySQL should necessarily also have this feature, however as part of comparing products in these articles, I am comparing existing Oracle Functionality to MySQL, so it will generally involve the description of functionality that is not presently comparable.

1. Package Space
2. Provider Supplied Procedures

1. Package Space

MySQL provides for Stored Procedures and Functions only the definition of individual procedures and functions. There is no way to group them into logical groupings.

Oracle provides the functionality of Package Space. Groups of procedures and functions can be defined within a package. Consider it like a wrapper of a group of procedures and functions. The package definition consists of 2 parts, the header section, which defines the scope of the procedures and functions, and the body which defines the actual procedures and functions. An added advantage of this that within Oracle you can protect your package body code (making it unavailable for view). I’ll discuss this more at a later time.

I’d like to see MySQL consider a package syntax particularly as businesses start to work more extensively with stored procedures and functions in larger enterprise applications. UPDATE: As stated by Roland in the comments, you can register your support for this functionality at MySQL under Bug #11696.

Within MySQL you can emulate package name, at least in syntax. You can use a dot ‘.’ within a procedure name, unlike this restriction for tables. The down side is the call must be quoted. I’ll discuss this more next.

2. Provider Supplied Procedures

Oracle provides an extensive list of supplied procedures and functions, defined in many packages. One of the earliest you learn in Oracle development is DBMS_OUTPUT.PUT_LINE. As the name indicates, this allows your to output a line of information. This is a common means of debugging output. Andrew Gilfrin post on Debugging Stored Procedures in MySQL raises this point, and an interesting solution using a debugging table. While this functionality works, one of my goals is to provide compability functions so that any migration of code could be simplified. Here is a much simplier solution.

$ more dbms_output.put_line.mysql
DELIMITER //
DROP PROCEDURE `dbms_output.put_line`
//
CREATE PROCEDURE `dbms_output.put_line` (output  VARCHAR(255))
BEGIN
  SELECT output;
END;
//
DELIMITER ;

A MySQL Usage:

mysql> CALL `dbms_output.put_line` ('hello world');


A comparision Oracle Usage:

SQL> SET SERVER OUTPUT ON
SQL> BEGIN
SQL> DBMS_OUTPUT.PUT_LINE ('hello world');
SQL> END;


This is as close the syntax can be. With MySQL you require the CALL and the backqoutes (`). Both things that can be easily added in an automated way.

References:
DBMS_OUTPUT Note: this is a public web version of the Version 8i manual online. Oracle provides online documentation, but you require a login (which is free) to the Oracle Technology Network.

Oracle8i Supplied Packages Reference. Again this is dated information, being an older version, but gives you an indication. For reference Oracle 7 released in 1993 first provided Package/Procedure/Function functionality.

Other

On a side note, I’d really like to see a IF EXISTS syntax for DROP PROCEDURE. IF EXISTS is an excellent MySQL extension. Oracle for stored procedures has the CREATE OR REPLACE syntax.

Have a look at InnoDB. Well, ok your lazy, so here’s a screen print below.
Plastered thoughout the top section is the word MySQL. It’s in the core banner blurb, there’s a logo, and even links to the MySQL Documentation. And right in the middle of all this is “Innobase OY is an Oracle Company”, with the standard Oracle Logo.

So my question would be, is this a good thing or a bad thing from a MySQL advertising perspective?
Does it help or hinder MySQL?
Does it show Oracle as being in partnership with MySQL? Is this good for MySQL to increase it’s exposure into the Oracle world?
Does inclusion of InnoDB now within Oracle traffic and links improve exposure to MySQL within search engines?

Frankly, I’m a little surprised that Oracle Legal hasn’t got onto this, I’m sure somewhere there would be a decree, that no competitor logos should be shown with the official Oracle logo.

I’d suspect that the existing MySQL community know the deal re Oracle, so perhaps this will expose more Oracle specific people to MySQL!

Some recent posts regarding Oracle (See Smart moves by MySQL AB and Larry Ellison still doesn’t understand open source) leads me to put in my 2 cents worth.

My background I’m sure like a lot of experienced MySQL people is in Oracle, and indeed in Ingres before that (starting in 1988). I have also worked for a number of years at Oracle Corporation. Ironically I started as their resident Ingres Specialist, in an international research project of DMS (Design & Migration Services) of re-engineering Ingres applications into an Oracle Designer Repository some 10 years ago in 1996. I of course moved into a number of other Oracle roles for clients following that. I still retain some contacts around the place.

It’s obvious Larry’s goal is to become more “Open Source”. This is of course a complex topic, and you can’t just say it like this. I’ll leave that for another day, but I’ve got some opinion on current trends.

It’s clear from recent purchases of JBoss, and SleepCat (Berkley DB) that Oracle has an intention of providing a suitable technology stack in a number of vertical marketspaces that Oracle presently cannot. For example, in the embedded space. Purchasing provides a much quicker solution, and also provides a different way to solve the problem instead of the legacy Oracle Product line. I haven’t seen any recent press, but I know that Xen was also in Oracle’s wallet space.

I would suspect that Oracle will move to providing a Stack solution (akin to SpikeSource for example) in a number of vertical spaces. These stacks will include non-traditional but now owner Oracle products, including Berkeley DB in lower end solutions. This along with the recent Oracle Express Edition (XE) free offering will enable Oracle to target market’s that were previously unavailable.

This is clearly not for a financial intention. There is no way to obtain a return on investment, however it’s all about upselling. By providing these offerings, Oracle now has a large marketplace to promote commercial product offerings.

Assuming, Oracle goes down this path, I’d assume they will also provide clear migration paths between stack offerings, as part of upselling. This will be more complex to achieve. I also suspect that Oracle will move towards a better Web 2.0 model, by providing products are no cost, but provide a service to support and maintain in these new marketspaces.

In closing, why did Oracle purchase InnoBase, the creators of the MySQL InnoDB Storage Engine? This question seems still unanswered. It doesn’t fit easily into the above ideas of differing technology stacks. Ultimately, money buys power, and I suspect it was just that, with no real future intentions or clear plan.

Update
Thanks for the comment about JBoss, yes, it’s not official, nor is Xen, but it took Oracle I think 15 months to get Peoplesoft.

I came across the Oracle Corporation Wikipedia entry in other work, the end of the history page reads a who’s who of acquisitions.

With Oracle Corporation purchasing InnoBase, the company providing the InnoDB Storage Engine, and now reliable rumors of the acquisition of SleepyCat, the BDB Storage Engine, both key transactional storage engines for MySQL are effectively owned by a competitor.

While the is a strange and probably unchartered territory for both organisations, I’m personally concerned. I use InnoDB extensively, however if there was a comparable alternative within MySQL I’d consider switching out of principle. Is Oracle purchasing these organisations a bad thing? We don’t know. That’s the problem. While MySQL will undoubtly continue to provide these storage engines as part of the MySQL Database I believe a call to arms is needed.

It’s true that Oracle helped more general adoption of Linux when it announced this as it’s primary platform some years back. I’m sure with a middleware suite such as JBoss this will benefit from Oracle’s exposure, but will it benefit in it’s development? Will funding for development skew the product torwards what Oracle wants, not the community? Even things such as BerkleyDB being embedded in Open Office, and Google wanting to spend into the refinement of Open Office for possible web services options just makes you wonder whats happening. I’m on holiday’s trying to relax, and not do any reading, developing or communicating, but the one thing that makes my mind turn is, why is Oracle doing this?

My concerns are: look what happened to the acquired big commercial competitors, the PeopleSoft, Siebel, JD Edwards. Now, it’s a little difficult with the Open Source software, but I’m no licence expert. While it will continue to be available in current licencing options, I’m sure there has to be some concerns. Even something trivial like, all MySQL downloads that include these engines must first be registered on the website, and all these statistics must go to Oracle. Can they do this? I have no idea, but what if they could.

I’m sure internally in MySQL AB there are plans afoot for alternative transactional storage engines. The pluggable nature of these within MySQL makes it easy to move in this direction. I think sometimes, some functionality is kept close to heart, and you only here about it when some actual work is released.

I’m interested to guage reaction to see if a public working group should be setup to specifically tackle the issue of an independent transactional storage engine. Are there others out there that feel the same way I do. Now let me be clear, I’m not anti Oracle, infact rather pro Oracle, but I’m very anti Microsoft. If it was Microsoft buying these companies how would people react?

In my opinion this should be a bold announcement from MySQL now. In stating the development and release of a new Transactional Storage Engine this year, and then not evening mentioning InnoDB and BDB, they are downplaying the Oracle buy in, and emphasising a true Open Source Company option. In no means say they are no longer supporting InnoDB and BDB, but if the media exposure from MySQL continues to mention them, then it’s going to bleed into some reference back to Oracle.

I can’t contribute to the actual development in C++, if it was Java that would be a different matter, but as I move more away from hardcore coding there are plenty of other areas in which I could contribute.

Last year saw a record number of acquisitions by Oracle Corporation. Of note was in October 2005 InnoBase (Read Press Release) which had a direct relationship with MySQL providing the InnoDB Storage Engine. It’s too early to tell what the impact to MySQL will be if any.

I’ve been in Singapore, and have not read any news in the past few days, but all information I’m receiving from those collegues in the know is that Sleepycat Software (the company behind Berkley DB, and the MySQL BDB Storage Engine) is now firmly in the sites of Oracle Acquisition. The rumors of JBoss is also definitely on the go. No official news from any Oracle Press Releases as yet.

So what is Oracle doing, and what impact will this have for MySQL? Both InnoDB and BDB are key transactional storage engines. Given that MySQL has the full capability for ease of plugin for new storage engines is this a prod to MySQL to consider another alternative?

If I wasn’t mistaken, it seems Larry Ellison is looking to be a big name in open source!

One piece of SQL functionality that doesn’t appear to have any consistency or an ANSI SQL Standard is the management of system generated sequential numbers, used for example in suggorate keys.

MySQL uses AUTO_INCREMENT which serves the purposes adequately, however in my documenting of differences with Oracle in my upcoming MySQL Conference presentation “MySQL for Oracle Developers” there a number of key differences with Oracle’s SEQUENCE usage.

MySQL AUTO_INCREMENT to Oracle SEQUENCE Differences

• AUTO_INCREMENT is limited to one column per table
• AUTO_INCREMENT must be assigned to a specific table.column (not allowing multi table use)
• AUTO_INCREMENT is INSERTed as a not specified column, or a value of NULL

The MaxDB Reserved Words list includes SEQUENCE for the CREATE SEQUENCE however I’ve never used MaxDB. Other popular open source products such as PostgreSQL and Ingres use sequences. Refer to the references section for more details.

Usage

The following provides an example sytax usage within MySQL and Oracle.

MySQL

CREATE TABLE Movie(
id           INT NOT NULL AUTO_INCREMENT,
name     VARCHAR(60) NOT NULL,
released YEAR NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;


INSERT INTO Movie (name,released) VALUES ('Gladiator',2000);
INSERT INTO Movie (id,name,released) VALUES (NULL,'The Bourne Identity',1998);


Oracle

CREATE TABLE Movie(
id          INT NOT NULL,
name     VARCHAR2(60) NOT NULL,
released INT NOT NULL,
PRIMARY KEY (id)
);
CREATE SEQUENCE MovieSeq;


INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);


You can within Oracle use a Before Insert trigger to simulate handling of the MySQL Insert syntax. Note: Within Oracle you will require a SEQUENCE per table and a TRIGGER per table. Oracle supports multiple triggers of the same type per table (not sure if MySQL supports this).

CREATE OR REPLACE TRIGGER BRI_MOVIE_TRG
BEFORE INSERT ON Movie
FOR EACH ROW
BEGIN
  SELECT MovieSeq.NEXTVAL INTO :new.id FROM DUAL;
END BRI_MOVIE_TRG;
.
RUN;


INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);


Oracle’s syntax uses the sequence name with .NEXTVAL or .CURVAL.

Future Directions

I would like to see a SEQUENCE implementation with MySQL (whether official or unofficial). I’m sure some enterprising person in the community already has one. Database abstraction layer systems would also most likely have implementations. I liked the PostgreSQL Syntax for ease of use with the following commands.

• NEXTVAL(’sequence’);
• CURRVAL(’sequence’);
• SETVAL(’sequence’,value);

Wanting something and doing something about it are two different things, so here is what I wiped together to demonstrate a possible implementation. It needs a lot more work in appropiate error handling. transaction management, testing and performance analysis, however it shows the options of one possible implementation.

currval

DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
name              VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment       INT NOT NULL DEFAULT 1,
PRIMARY KEY (name)
) ENGINE=InnoDB;
INSERT INTO sequence VALUES ('MovieSeq',3,5);
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
  DECLARE value INTEGER;
  SET value = 0;
  SELECT current_value INTO value
  FROM sequence
  WHERE name = seq_name;
  RETURN value;
END$
DELIMITER ;

Some Testing:

mysql> SELECT currval('MovieSeq');
+---------------------+
| currval('MovieSeq') |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT currval('x');
+--------------+
| currval('x') |
+--------------+
|            0 |
+--------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------+
| Level   | Code | Message          |
+---------+------+------------------+
| Warning | 1329 | No data to FETCH |
+---------+------+------------------+
1 row in set (0.00 sec)

What was interesting was I originally used a cursor, as below, but the results for passing an invalid argument (basic boundary testing), returned a SQL error while the above implementation returned a more manageable warning.

 DECLARE c CURSOR FOR
    SELECT current_value FROM sequence
    WHERE name = seq_name;
  OPEN c;
  FETCH c INTO value;


mysql> select currval('x');
ERROR 1329 (02000): No data to FETCH


Indeed the Apache Object Relational Bridge Sequence Manager section shows a very cool syntax for MSSQL.

UPDATE TABLE SET @MAX_KEY = MAX_KEY = MAX_KEY + 1

UPDATE table SET var = column = value which effectively allows you to eliminated the need for a seperate UPDATE and SELECT for this type of operation.

nextval

DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
   UPDATE sequence
   SET          current_value = current_value + increment
   WHERE name = seq_name;
   RETURN currval(seq_name);
END$
DELIMITER ;

mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                  15 |
+---------------------+
1 row in set (0.09 sec)

mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                  20 |
+---------------------+
1 row in set (0.01 sec)

mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                  25 |
+---------------------+
1 row in set (0.00 sec)

setval

DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
RETURNS INTEGER
CONTAINS SQL
BEGIN
   UPDATE sequence
   SET          current_value = value
   WHERE name = seq_name;
   RETURN currval(seq_name);
END$
DELIMITER ;

mysql> select setval('MovieSeq',150);
+------------------------+
| setval('MovieSeq',150) |
+------------------------+
|                    150 |
+------------------------+
1 row in set (0.06 sec)

mysql> select curval('MovieSeq');
+---------------------+
| currval('MovieSeq') |
+---------------------+
|                 150 |
+---------------------+
1 row in set (0.00 sec)

mysql> select nextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
|                 155 |
+---------------------+
1 row in set (0.00 sec)

Other References

Ingres CREATE SEQUENCE Page 105.
PostgreSQL CREATE SEQUENCE
Apache Object/Relational Bridge - Sequence Manager - Subproject of The Apache DB Project
MySQL CREATE PROCEDURE

In the past week I’ve added some more reading to my list, extended the library and hammered the credit card. Some of this has been in part to keep abreast of things as a result of being quizzed in job interviews.

Analysis Patterns by Martin Fowler and Oracle Database 10g Linux Administration

I’ve been accepted to present at the MySQL Users Conference 2006 held in Santa Clara, California next year. My paper will be particularly targeting Oracle Developers, and will be combined with another presentation targeting Oracle DBA’s.

I’ll also be developing demonstrations with the upcoming MySQL Sakila Test Database by Mike Hillyer from MySQL AB.