MySQL Data Types and Rails Migrations

If you have ever tried googling for Rails migrations and MySQL data types in order to understand how they map, you may have been disappointed as I had been. There is very little documentation about that on the RoR Wiki. So, it takes a while to understand how they map, what you can do in your create_table functions and what you cannot do. There is a way to get all that information though. And it's right on your computer.

First, it is important to understand that Rails communicates with databases by using Adapters. What in Java are usually known as JDBC Database Connectors. Adapter classes for databases are usually found in classes of the following format:

ActiveRecord::ConnectionAdapters::DatabaseServerNameAdapter. For which DatabaseServerName could be MySQL, PostgreSQL and so on.

Assuming that you're running OS X or any other type of Unix system, you will be able to find the code for your particular adapter by running:

less /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/mysql_adapter.rb

In my case, my database adapter is MySQL. Once the file opens up, look for the function native_database_types. It shows the mapping between Rails names and your particular database. Again, in the case of MySQL the mapping is as follows:


def native_database_types #:nodoc:
{
:primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
:string => { :name => "varchar", :limit => 255 },
:text => { :name => "text" },
:integer => { :name => "int", :limit => 11 },
:float => { :name => "float" },
:decimal => { :name => "decimal" },
:datetime => { :name => "datetime" },
:timestamp => { :name => "datetime" },
:time => { :name => "time" },
:date => { :name => "date" },
:binary => { :name => "blob" },
:boolean => { :name => "tinyint", :limit => 1 }
}
end


It is also interesting to read through the full source code of the adapter. There is a bunch of valuable information in there that one can use to optimize the usage of ActiveRecord models as well as Migrations.

-- JPGeek.

Obtaning next MySQL auto-increment ID for J2EE CMP ejbCreate method

I have recently been working a lot with J2EE. Well working or studying (however you want to call it :-D). Anyways, I ran into a serious problem and for the first time, even Google wasn't much help.

Application Server: Glassfish / Sun App Server 9
Database Server: MySQL
EJB Version: 2.0 / 2.1

I assume that you all know how EJB CMPs (Container-Managed Persistence) work. The developer basically provides a set of cmp-fields which are the database attributes, provices a create/ejbCreate(parameters_here) method which is used by the container to generate SQL queries that insert data into the database: OK, before you scream at me, it is not this simple but I just want to get to the point.

This usually works without a problem if you can provide the primary key to the ejbCreate method. Let's assume your ejbCreate method looks like this:

public Long ejbCreate(Long id, String firstname, String lastname) { ... }.

To this assumption, let's add the fact that the primary key here (Long id) is auto-generated by your database management system, in this case MySQL.

The problem arises that there is no way you can provide an ID to your create method. So you would tend to do the following:

customerHome.create(null, "Mickey", "Mouse");

The problem with the code above is that your EJB container will scream at you and throw some aweful exceptions.

I also thought about trying something like this:

customerHome.create(new Long(-1), "Mickey", "Mouse");

My assumption was that the since ID was the primary key field with auto-increment in the MySQL table design, the RDBMS would intelligently decide that "-1" was not right and would assign the right value. I unfortunately found out that MySQL was not that smart.

I searched Google and asked a few developers friends to see whether MySQL had a way to determine the next primary key sequence like Oracle does (sequence.nextValue() ?) and I could not get much help either from Google or other developers.

After some serious digging in the functionning of MySQL and Schemas, I ran into the fact that one could find about the status of all tables in a database using the following query:

SHOW TABLE STATUS.

Such query will show the current values of the properties of all tables in the currently selected database. Interestingly enough, there is a property called "Auto_increment". Some search in the manuals told me that the "Auto_increment" property contains an integer value showing the next "auto-increment" value a inserted record would have for primary key. That's all that was needed.

Next, there was a need to narrow the results: instead of showing the status of all the tables in the database, I need to get the status of only one table: the one I was interested into. In this example, that would be the CUSTOMERBEAN table. The following query sorts it out:

SHOW TABLE STATUS LIKE "TABLE_NAME"

In our case, we would write is as
SHOW TABLE STATUS LIKE "CUSTOMERBEAN". For some reason, I think the MySQL guys need to redesign this. I've been reading a lot about API design and the above sentence does not convey much about what the query does (this was just a side note).

Once this can be done, the rest is just about obtaining a ResultSet object from the query above, get the value of the Auto_increment field and pass it to your cutomerHome.create(...) method call. In my particular case, I wrote a utility class to do that for me. I just get the result and up and running I am!

I hope this helps any of you developers out there dealing with the same problem. It took me about 2 hours to sort it out and that was quite a waste of time.

Also, if you think there are other ways (I know that there is a different way of handling this with the WebLogic server), please post a comment and let me know.

-- JPGeek