rlucas.net: The Next Generation Rotating Header Image

July 21st, 2006:

Broken Quoting of Spaces in Table Names in Ruby’s ActiveRecord

Two-part posting.

1. The hot-shit developer boys at http://dev.rubyonrails.org apparently use Python (Trac) for their bug-tracking system, and for extra chuckles, it’s broken.

From http://dev.rubyonrails.org/newticket#preview

(removed a Python stack trace that came down to a NOT NULL constraint violation in the underlying database — the error message was messing up the blog formatting software.)

Not the most helpful when I’m trying to post a bug report!

2. So, I’m posting by bug report below. Essentially, having spaces in your table names throws a major monkey wrench in the “convention over configuration” mantra of Ruby on Rails.

I might fix this, if I can find the time (the lack thereof being why, in the first place, Rails seemed so appealing). If so, I will post a patch.

Legacy apps being built to SQL Server databases may find spaces in table names. These can be addressed superficially by a:

set_table_name '"Spacey Table"'  


set_table_name '[Spacey Table]'  

This approach makes parent classes behave properly when directly interpolating table_name into a string, avoiding such errors as:

Invalid object name 'Company'.: SELECT count(*) AS count_all FROM Spacey Table   

HOWEVER, pre-escaping the table names in this way breaks the SQLServer ConnectionAdapter’s ability to get info out of SQL Server, as in sqlserver_adapter.rb line 246 (line breaks added):

sql = "SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue,  DATA_TYPE as ColType, IS_NULLABLE As IsNullable,  COL_LENGTH('#{table_name}', COLUMN_NAME) as Length,  COLUMNPROPERTY(OBJECT_ID('#{table_name}'), COLUMN_NAME, 'IsIdentity')  as IsIdentity, NUMERIC_SCALE as Scale  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '#{table_name}'"  

As you can see, here it will try to match ‘Spacey Table’ = ‘[Spacey Table]’ for the brackets case (or ‘”Spacey Table”‘ for double quotes).

Also, get_table_name(sql) will have trouble with this.

To make this work without breaking encapsulation will probably require using an escaped table_name and then selectively unescaping it for the SQL Server-specific uses.

All in all, MSFT’s behavior is fairly satanic on this; see below for a link describing syntax and escaping. Note that the only backwards-compatible solution is to SET QUOTED_IDENTIFIER ON and then use “This Table”.”This Column” notation, in order not to run up against problems with SQL Server < 6.5.