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"'
or
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.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_89rn.asp