ActiveRecord::Base is inconsistent with quoting of primary key columns:
Member.find(1)
# => SELECT * FROM members WHERE (members.id = 1)
Member.find_by_id(1)
# => SELECT * FROM members WHERE (members."id" = 1) LIMIT 1
This has bad side-effects when using mixed-case primary keys on case-sensitive databases, such as PostgreSQL.
Ticket #4547 originally brought this up but failed to provide a decent patch, though it looked like bitsweat was waiting for one so he could commit the fix.
Attached is a patch which ensure all references to the primary key are column quoted by the connection adapater.
AR tests pass in MySQL and PostgreSQL.
No tests are included with the patch, because a) there's no tests for AR's orginal behaviour, 2) how on earth do I add a test for this?
The following test would work on PostgreSQL because it throws a SQL error that it can't find the column "primarykey", but is no good for other adapters, and doesn't directly test if AR::Base is generating SQL with the pk column quoted.
CREATE TABLE mixed_case_primary_keys ("primaryKey" INTEGER PRIMARY KEY);
INSERT INTO TABLE mixed_case_primary_key VALUES (1);
class MixedCasePrimaryKey < ActiveRecord::Base
set_primary_key "primaryKey"
end
class MixedCasePrimaryKeyTest < Test::Unit::Case
def test_should_search_primary_keys
assert_nothing_raised do
MixedCasePrimaryKey.find(1)
end
end
end
