MySQL

I l @ ve RuBoard

Once again, along came the little guys to save the day. Many sites don't need the battleship strength (and price tag) of Oracle. MySQL is an open -source SQL database available for anyone to use, with many (although not all) of the features of its big brothers, such as Oracle.

MySQL is available for just about any computer that has decent power ”it is fairly lightweight on the processor and easy to install (10 minutes, as opposed to multiple hours for Oracle).

So, perhaps you are wondering, what's the catch? What are you not getting in MySQL that makes people turn to Oracle? Well, MySQL is a neat little package, but it is missing some things that would be nice to have in a perfect world.

A major feature that MySQL does not offer is database consistency checking. You can use foreign key tags in your schema, but MySQL cheerfully ignores them. A lot of DBAs I know would consider this a very bad thing.

A foreign key constraint prevents you from creating inconsistent data. For example, let's suppose that you had a scheme that looked like this:

 CREATE TABLE USER (        USERID INTEGER,        FIRST_NAME     VARCHAR(80),        LAST_NAME      VARCHAR(80)); CREATE TABLE PURCHASE (        USERID FOREIGN KEY USER(USERID),        ITEM INTEGER,        QUANTITY INTEGER); 

In a database such as Oracle's, if you created an entry in the PURCHASE table with a user ID of 3, there would have to already be a user ID of 3 in the USER table or an error would occur. Similarly, you couldn't delete user 3 from USER if it was referenced in PURCHASE.

The MySQL folks make a pretty impassioned argument in their documentation that depending on foreign keys for data integrity is a bad idea anyway, but convincing your DBA of this philosophy is likely to degrade into a religious debate.

In addition, some other features are missing, such as subselects and select into . But probably the other major piece that you will miss is the rollback/commit functionality. MySQL does implement rollback and commit for certain types of tables, but not all of them. Again, the MySQL folks offer their own spin on why this is okay, but being able to roll back transactions is (in my opinion) important enough to make sure that you have it available.

Rollback allows you to set a savepoint on the database before starting to do a series of transactions with it, and be able to either roll back to the original state or commit the changes at the end. For example, when recording a purchase, you need to record a debit against the user's account and enter a record into the shipping table so that you'll know later to ship the item. Let's say that the second part fails. You wouldn't want to charge the user but not ship the item. Thus, you'd want to roll back to the state before the transaction began .

So, MySQL isn't a full-blown production database ”at least, not yet. It's still good enough for probably 90% of the e-commerce sites in the world, however. And version 4.0, which is in alpha as of this writing, addresses a number of these concerns, including row-level locking and transaction control.

I l @ ve RuBoard


MySQL and JSP Web Applications. Data-Driven Programming Using Tomcat and MySQL
MySQL and JSP Web Applications: Data-Driven Programming Using Tomcat and MySQL
ISBN: 0672323095
EAN: 2147483647
Year: 2002
Pages: 203
Authors: James Turner

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net