Ruby, Ruby on Rails, Artificial Intelligence and something more ...

Ver y Cocinar, descubre la cocina y disfrĂștala a simple vista

RailsConf Europe 2007 Speaker

RailsConf Europe 2007 Speaker
http://www.railsconfeurope.com/

Spejman Thumblog!

Monday 28 May 2007

Using MySQL reserved words as model names

The generation of a model with a migration in a ruby on rails application lets to the creation of a database table with the pluralization form of the desired model name. In MySQL, a migration will generate a sql statement like:


CREATE TABLE model_name_pluralized (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
`created_on` date DEFAULT NULL, `name` varchar(255) DEFAULT NULL) ENGINE=InnoDB

As you can notice column names are quoted but table name doesn't. If you use as a model name a sigularized form of a MySQL reserved word, the migration that creates this model will generate a statement that will lead to an error like:

Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'databases (`id` int(11) DEFAULT NULL
auto_increment PRIMARY KEY, `created_on` da' at line 1: CREATE TABLE databases (`id` int(11)
DEFAULT NULL auto_increment PRIMARY KEY, `created_on` date DEFAULT NULL, `name` varchar(255)
DEFAULT NULL) ENGINE=InnoDB

Last week I read the Josh Susser Laying Tracks slides who
encourages me to write a patch for this issue.

Before writing anything I tried to find if someone has made something related and I found some tickets related in Rails trac:


The most interesting of this tickets is #4905 which fix all MySQL statements to prevent reserved words crash, but I don't know why isn't included in the code because it's last history is from 05/25/2006. #7850 is closed as duplicated because of #4905. And #3631 history finishes with "don't use reserved words" what in my opinion isn't the best solution.

In brief, the problem exists (I can't name my models with names like "database", "exist", ...) and the patch too (#4905). Then, what should we do to fix this problem?

No comments:

About Me

Barcelona, Barcelona, Spain