cloudfoundry/cloud_controller_ng — pull request #44 by youngm (2013-05-02 00:41:32+00:00)
Title: Oracle support in Cloud Controller

This pull request adds support for Oracle to the Cloud Controller.

This is a single pull request that includes and invalidates all of the other pull requests I've submitted over the last could of weeks. This PR is based on Master from a few days ago. So, there may need to be some additional merge work once this PR is conceptually approved.

This pull request is a lot to digest. If you want you can review the single feature PR that lead to this PR for some more concise context into some of the decisions I made in creating this PR. Here is a summary of the more notable changes I had to make and some context for why I made those changes:

## Schema Changes
### 30 Character Identifier Limit
Oracle limits all identifiers to 30 characters. So, this required me to go through and give static names to all of the indexes and fk constraints that names larger than 30 characters.

In order to support Oracle with the existing migration scripts I had to modify the past migrations. I also added a new migration script that renames all of the fk constraints I had to shorted. This should mean that all the databases have the same fk names. However, I did not rename any of the indexes. So an existing database and a freshly created database may have different names for many of the indexes.

See: https://github.com/cloudfoundry/cloud_controller_ng/pull/38

### Timestamp a reserved word
`timestamp` is a reserved word in Oracle. So I had to rename 2 instances of timestamp. I also added a migration script that will optionally rename these columns if they have the old name. This will bring existing and fresh schemas into alignment.

I also added the needed `def_column_alias` in the models so the API didn't change.

See: https://github.com/cloudfoundry/cloud_controller_ng/pull/38

### No Boolean in Oracle
Oracle doesn't have a Boolean type. So, I had to change all of the `Boolean` column types to `TrueClass`. This allowed Sequel to use a char(1) for Boolean in Oracle.

However, Sequel doesn't appear to have yet fully implement support for Boolean=>char(1). To fully support char(1) as boolean I had to create a monkey patch that basically makes Sequel look at all char(1) columns as a boolean type similar to what `convert_tinyint_to_bool` does for mysql. This decision removes the future ability to use `char(1)` as a column type in the cloud controller. I'm open to other ideas if this is unacceptable.

## Oracle doesn't support case insensitive columns

### Support for using lower() function in where clause
Oracle doesn't have a way to support individual case insensitive columns. The standard approach in Oracle is to use the `lower()` or `upper()` function in queries. To support this I added a sequel plugin that essentially allows for declaring in the model what fields are case insensitive. Then I updated the `query.rb` rest api facilities to look for this identifier and modify the query to use `lower()` for those columns declared as case insensitive.

I then modified the existing case_insensitive Sequel monkey patch to allow the drivers to declare if they wish to use the "lower" function or rely upon a case insensitive column type.

Sqlite's case insensitive column type is buggy so I have configured it to use "lower()" for where queries but continue to use its case insensitive column type for db constraint purposes.

I set the default to use lower() for where clauses since it seems to be a rather cross compatible way to provide case insensitivity across databases.

I considered changed all databases in the Cloud Controller to use lower(). However, mysql and sqlite don't support function based indexes which would have caused performance and db constraint problems in mysql and sqlite. So I opted to just use `lower()` where queries in oracle and sqlite.

To test this I enhanced the provided API and model test examples to allow for the declaring of case insensitivity in those test examples as well. This significantly enhances the Cloud Controllers previous case insensitivity testing. It actually found a bug/typo in the `:stacks` model. I have included a fix for that in the migration scripts of this PR.

See: https://github.com/cloudfoundry/cloud_controller_ng/pull/41

### Case Insensitive Constraints and Indexes
To maintain case insensitivity in constraints and indexes I added support for enabling the `:case_insensitive` flag for indexes. This will change the index to use a `lower()` function based index in databases that support it/need it.

## Oracle Empty Sometimes Equals NULL
In an Oracle varchar if you insert `empty` it will translate `empty` to `null`. If you attempt to query `empty` it will not match `null`. This cased a problem for Route.host. Which uses `empty` to denote a non wildcard host and `null` is not allowed. This was actually the most difficult and frustrating of all the problems I came accross and I think the solution is the ugliest.

What I ended up doing was changing non wildcard Route.host to `*` and disallowed through validation and db constraints `empty`. I believe I did so while maintaining API backwards compatibility. I also added several tests to help ensure the functionality. I am totally open to other ways to solve this problem. For more context on how I got to this solution see: https://github.com/cloudfoundry/cloud_controller_ng/pull/40 and https://github.com/cloudfoundry/cloud_controller_ng/pull/42

## Summary
I believe I've detailed above the more significant changes and work that I put into this endeavor. I think I've provided a pretty good solution for Oracle in the Cloud Controller and I think I've backed up that solution by adding tests that not only support the Oracle changes but I think improve the overall test coverage of the Cloud Controller.

My team has a deadline for supporting Oracle in our CF deployment by the middle of the May. It would be great if someone from the team could invest some time in reviewing and applying this PR by then. I am also happy to help in whatever way I can to provide CI support for Oracle. AWS provides Oracle support in RDS perhaps that can be utilized?

Thanks,
Mike