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:

• #7850: [PATCH] Added missing backticks to mysql adapter


• #4905[PATCH] Rails should backtick table names automagically


• #3631: table names should be quoted


• #1633: [PATCH] quote_column_name in ActiveRecord::ConnectionAdapters ...

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?

Backup of Bloglines keep new items

If you are a Bloglines user, you probably save the interesting posts using "keep new" feature.

If you have use this reader for a time, the quantity of post saved could be large... Some day I thought that won't be funny to loose all this data, then I build a ruby script to backup bloglines "keep new" items into a xml file.

With this script I learned better Mechanize and Hpricot Ruby libraries.
Este script me ha servido para probar dos librerías muy útiles de Ruby: mechanize y hpricot.

You need these libraries in order to use the script:

gem install json
gem install activesupport
gem install hpricot
gem install mechanize

And here you have the script. I think is very understandable. I hope it helps you to backup your bloglines account (remember to change EMAIL and PASSWORD values with yours) or to understand better how mechanize and hpricot works.

require "rubygems"
require "hpricot"
require "json"
require "mechanize"
require "active_support"

# Reads a bloglines javascript tree structure that has all
# feeds data.
def read_tree( tree_base, label = "" )
 tree_base.each do |tree|
 if tree["kids"]
  read_tree tree["kids"], label + "/" + tree["n"]
  else
  @feeds << [tree["n"], label, tree["kn"], "http://www.bloglines.com/myblogs_display?sub=#{tree["id"]}&site=#{tree["sid"]}"]
 end
 end
end

# Add more memory to hpricot otherwise couldn't load some webs.
Hpricot.buffer_size = 262144

agent = WWW::Mechanize.new
page = agent.get 'http://www.bloglines.com/login'

form = page.forms[1]
form.email = 'EMAIL'
form.password = 'PASSWORD'

page = agent.submit form

# Get the bloglines sindicated feeds
menu_page = agent.get "http://www.bloglines.com/myblogs_subs"
start_text = "var initTreeData = "
end_text = "\n;\n"
js_feeds_tree_str = menu_page.content[menu_page.content.index(start_text)+start_text.size..menu_page.content.index(end_text)]
feeds_tree = JSON.parse js_feeds_tree_str.gsub("\\","")
@feeds = []
read_tree(feeds_tree["kids"])

puts "<bloglines_saves>"
@feeds.each do |feed|

 page = agent.get feed[3]
 doc= Hpricot(page.content)

 # get the content of all saved feed posts
 content = ((doc/"body")/"td.article")
 next if content.empty?
 puts "<feed name=\"#{feed[0].strip}\" folder=\"#{feed[1].strip}\">"
 
 # Iterate each saved feed post
 ((doc/"body")/"a.bl_itemtitle").each_with_index do |title, index|
  puts "<feed_save title=\"#{title.inner_html.strip}\" href=\"#{title.attributes["href"]}\">"
  puts content[index].inner_html.to_xs
  puts "</feed_save>"
 end
 puts "</feed>"

end
puts "</bloglines_saves>"

Más información:

• Hpricot: http://code.whytheluckystiff.net/hpricot/
• Mechanize: http://mechanize.rubyforge.org/mechanize/