cloudfoundry/uaa — pull request #39 by youngm (2013-04-08 23:48:12+00:00)
Title: Add Oracle DB support to UAA

This pull request adds code support for Oracle as a database to UAA. All unit and integration tests have been run and pass. Below are some notes about the changes made in this pull request:

* The oracle driver is proprietary. I did find some older drivers on maven central but I'm pretty sure they violate the terms of the driver. So, to be safe for now I just comment out a maven dependency for the driver that can be installed into their local repo by those wishing to test the Oracle support.
* Oracle doesn't support "limit" query syntax. So, I added a LimitQueryAdapter that modifies queries to provide limits around them. I then combined this adapter into a JdbcPagingListFactory to abstract the creation of a paging list regardless of the db underneath.
* Oracle has no "BOOLEAN" type. So I use a NUMBER(1) instead. This necessitated some changes to queries that hard code "true/false" in the query and required some changes to the schema.vpp file to handle boolean type defaults.
* Oracle "char()" type handles queries with prepared statements weird. So, for Oracle instead of using char(36) for some types I used a varchar(36) instead. Another option is just to have all the databases use VARCHAR instead of CHAR. But for now only Oracle is using the VARCHAR. For more see this document. http://www.rgagnon.com/javadetails/java-oracle-char-field-preparedstatement.html
* The most characters a VARCHAR can hold in Oracle is 4000. So, I created a "LARGEVARCHAR" property for the .vpp scripts to differentiate between Oracle's 4000 char varchar and the other dbs varchar(4096)
* select 1 is an invalid query in Oracle so I had to parameterize the pool validation query to support oracle's select 1 from dual validation query.
* Oracle's "alter column" syntax is different from other databases. Oracle instead of modifying the column just redefines the column. So I updated the .vpp macro's to support this syle of alter column. Even though the macro isn't used anywhere yet. But will come in handy when we start to add migrations to .vpp files.

To run integration tests you can install Oracle XE http://www.oracle.com/technetwork/products/express-edition/overview/index.html then update the username and password appropriatly in the .properties files.

Sorry this pull request is so big. Oracle Sucks!

Mike