0

I am using Ruby on Rails. I want to store usernames into a database and when I extract them I want them to have the same capitalisation as it had when entered, but I want to add some validation that is not case sensitive to ensure the same username cannot be taken, no matter the capitalisation.

I say this because not all database adapters use case-sensitive indices so I would need to down-case the username before it was saved into the database.

So the validation in the model would be this:

uniqueness: { case_sensitive: false }

How would I go about doing this?

Tim
  • 3,191
  • 2
  • 16
  • 22

2 Answers2

1

A couple of approaches come to mind.

easy

You can have a case-sensitive model level validation, as you are doing and simply not validate uniqueness in the database.

ugly

Or, if you insist on a database level validation, a dirty database-agnostic way to do it could be to have another field, just used for validations.

add_column :users, :uppercase_username, :string, unique: true

class User
  def username=
    @username = value
    uppercase_username = value.upper
  end
end

awesome

You can define your own indexing function (may be database specific).

mkirk
  • 3,965
  • 1
  • 26
  • 37
  • The "easy" option has holes. The "ugly" option (with an in-database uniqueness constraint on `uppercase_username`) is the portable approach. The "awesome" one is database-specific and will confuse Rails unless you switch from `schema.rb` to `structure.sql` as `schema.rb` has no way to store database-specific indexing. – mu is too short Oct 29 '13 at 01:59
  • I think the 'ugly' option is the 'awesome' one – fengd Oct 29 '13 at 02:32
  • 1
    I guess I long ago gave up on "database portability" and am happily married to postgres and structure.sql – mkirk Oct 29 '13 at 22:34
-1

The validation that you mentioned (uniqueness: { case_sensitive: false }) will not change the case of the username when it is stored but will instead simply check that no emails exist that are the same. In essence, you do not have to worry about this problem given your current validation.

jvperrin
  • 3,368
  • 1
  • 23
  • 33
  • But is it not true that not all database adapters use case-sensitive indices, so I would need to do that validation then `before_save { username.downcase! }` – Tim Oct 28 '13 at 23:41
  • @Tim What database adapter are you using, or do you want this application to work across many types of database? – jvperrin Oct 28 '13 at 23:48
  • SQLite3 for development and tests & PostgreSQL for production. – Tim Oct 28 '13 at 23:55
  • The validation will work as you want without any changes, but if you really want to validate at a database level, look at the second to top answer to [this question](http://stackoverflow.com/questions/2435687/case-insensitive-find-or-create-by-whatever). – jvperrin Oct 28 '13 at 23:59
  • 1
    Leaving the uniqueness constraint outside the database leaves you open to race conditions and other problems. – mu is too short Oct 29 '13 at 01:56
  • @muistooshort Yes, although I imagine a race condition would be rare with inserting usernames, it could be an issue, so database uniqueness should be added. – jvperrin Oct 29 '13 at 02:01