Inconsistency with handling of unique constraints

[David Stanek]

We have many unit tests (simple example[1]) that check to see that indexes are case insensitive. The problem is that our current implementation doesn't explicitly make the index case insensitive. That's left up the the RDBMS. This is problematic because in my testing I have seen different results:
  - SQLite - is case sensitive
  - MySQL - is case insensitive[2]
  - PostgreSQL - is case sensitive

My goal was to get the unit tests working on MySQL/PostgreSQL, so I would like to figure out a direction. Should we drop those tests and leave it up to the environment? It's also possible to get SQLAlchemy create the proper index. I've never done it, but I'm sure it's possible.

[references]
1. http://git.openstack.org/cgit/openstack/keystone/tree/keystone/tests/test_backend.py#n1791
2. http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

[Juan Antonio Osorio Robles (juan-osorio-robles)]

In my opinion, I think we should either try to handle the case insensitivity or very explicitly say that we don't. Users will most likely stumble upon these things and it's better to have some sort of answer. A solution could be to then specify that case sensitivity is supported for some specified platforms.

In MySQL the following approach could be used: http://stackoverflow.com/a/11067627

[Dolph Mathews (dolph)]

I think this would be a great topic for the operator session at the summit!

[Juan Antonio Osorio Robles (juan-osorio-robles)]

Damn, we didn't talk about this at the summit :/ Any insights, either way?

[Dolph Mathews (dolph)]

Bah! The best approach now is probably to file a bp explaining what scenarios we expect conflicts to occur, those in which we don't, and discuss the viability of a migrating to more consistent enforcement.

[Swati Jaiswal (curioswati)]

Hello, I want to contribute to this bug. I am a newbie to openstack so please can you tell me where should I start?

[David Stanek (dstanek)]

I think doing what Dolph suggested in comment #4 is probably a good first step. That will show how big (or little) of a problem that this actually is...