Add support for Oracle

[Nicklas Nordborg]

The current implementation doesn't work with Oracle databases for a number of reasons. I will add comments to this ticket describing each issue I found while testing and any possible solutions I came up with.

[Nicklas Nordborg]

1. Installation fails when installing web client.

The problem is that the web client installs empty strings for the 'server.links.help' and 'server.links.faq'. Oracle converts empty strings to null which isn't allowed by the any of the *Setting classes.

The only possible solution is to allow null as setting values. The installation should install null values instead of empty strings and the web client code (ie. JSP pages) should be checked that they can handle null values correctly.

[Nicklas Nordborg]

2. TestSample, TestExtract and TestLabeledExtract fails.

They fail with the same exception:

--List events for extract FAILED
net.sf.basedb.core.BaseException: could not execute query
   at net.sf.basedb.core.HibernateUtil.loadList(HibernateUtil.java:1348)
   at net.sf.basedb.core.ItemQuery.list(ItemQuery.java:92)
   at TestExtract.test_list_events(TestExtract.java:324)
   at TestExtract.test_all(TestExtract.java:56)
   at TestExtract.main(TestExtract.java:35)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
[snip]
Caused by: java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB

The problem is that Oracle doesn't support SELECT DISTINCT if one of the selected fields is a CLOB even if the primary key (id) is also among the selected field. In this case it is the description field of the item. A good optimizer should only have to check the ID for distinct values.

A solution is to use the org.hibernate.Query.iterate() method instead of the org.hibernate.Query.list() or .scroll() methods which are used today. It has worse performance since it first loads only the ID:s and then load each item by a separate query. So, we should only use .iterate() when there is a need for it.

We should add a public boolean supportsDistinctWithBlobs() method to the DbEngine class. We also need a way to tell HibernateUtil.loadList() and HibernateUtil.loadIterator() if the query uses DISTINCT and selects any BLOB/CLOB column. It shouldn't be too hard to implement given that Eclipse can help us refactor the methods. We may also have to change the ScrollIterator class to make it accept a regular Iterator and not only a org.hibernate.ScrollableResults.

[Nicklas Nordborg]

3. TestDbInfo fails

++Testing dbinfo
=================
Table   : AnnotationSets
Catalog : null
Schema  : null
Database information
--------------------
net.sf.basedb.core.BaseException: ORA-00942: table or view does not exist
   at net.sf.basedb.core.HibernateUtil.dbIndexes(HibernateUtil.java:1599)
   at TestDbInfo.test_all(TestDbInfo.java:44)
   at TestDbInfo.main(TestDbInfo.java:34)
Caused by: java.sql.SQLException: ORA-00942: table or view does not exist
[snip]
   at net.sf.basedb.core.dbengine.TableInfo.<init>(TableInfo.java:137)
   at net.sf.basedb.core.HibernateUtil.dbIndexes(HibernateUtil.java:1595)
   ... 2 more

It is very strange that this error occurs. The test first finds the columns for the table, which works, then the primary and foreign keys which also works. When loading the indexes it doesn't work. The parameters (ie. the table name to get the info for) are the same in all cases.

This might be a bug in the JDBC and/or Oracle database. I only have tested with Oracle 10g Release 2 Express edition which is a beta release and the bug may have been fixed since then.

[Nicklas Nordborg]

4. TestExperiment fails

--Create root bioassayset using plugin OK
20:52:40,468 ERROR ql:192 - SELECT COUNT(*) AS "spotCount", 
COUNT(DISTINCT "pos"."reporter_id") AS "reporterCount" 
FROM "D1986Spot" "spt" 
LEFT JOIN "D1986Pos" AS "pos" 
ON "pos"."cube" = "spt"."cube" AND "pos"."position" = "spt"."position"
WHERE "spt"."cube" = 3 AND "spt"."layer" = 1
java.sql.SQLException: ORA-00905: missing keyword

Oracle has no JOIN or LEFT JOIN keyword, but uses theta join style instead. Should be easy to fix since it is already supported by the core. We only need to create a OracleDbEngine class that returns true for the useThetaJoin() method.

[Nicklas Nordborg]

5. TestPlateFlatFileImporter, TestRawDataFlatFileImporter, TestPluginConfiguration,

TestReporterFlatFileImporter and TestReporterMapFlatFileImporter fails

++Testing plugin configuration
--Create plugin definition OK
--Create plugin configuration OK
--List plugin configurations OK (1)
--Load plugin configuration OK
--Configure plugin configuration FAILED
org.hibernate.exception.SQLGrammarException: could not initialize a collection:
[net.sf.basedb.core.data.PluginConfigurationData.configurationValues#2091]
[snip]
  at net.sf.basedb.core.PluginConfiguration.getParameterNames(PluginConfiguration.java:283)
  at net.sf.basedb.core.ParameterValuesImpl.<init>(ParameterValuesImpl.java:96)
  at net.sf.basedb.core.PluginConfiguration.configure(PluginConfiguration.java:496)
  at TestPluginConfiguration.test_configure(TestPluginConfiguration.java:189)
  at TestPluginConfiguration.test_all(TestPluginConfiguration.java:63)
  at TestPluginConfiguration.main(TestPluginConfiguration.java:48)
Caused by: java.sql.SQLException: ORA-00972: identifier is too long

All plugin configuration related exceptions seems to be related to that there is no VersionedPluginConfigurationValues table. If I try to create it by hand I get the error message: Table Name VERSIONEDPLUGINCONFIGURATIONVALUES exceeds allowed size.

Oracle seems to have a limit of 30 characters for table names. We can solve it by changing the name of the table, but it is likely to cause problems with other installation using MySQL or Postgres. Another solution is to let the DbEngine inspect and maybe change all mapping information before the final configuration. We could for example add a method DbEngine.fixTableAnomalies(Table table) which could inspect table and column names (and possible other not yet known things) and change the name. In this case the OracleDbEngine should truncate the table name to 30 characters. The method should be called from HibernateUtil.init() just before the call to final configuration (cfg.configure()).

[Nicklas Nordborg]

6. Subcontext names of current context settings is likely to cause problems.

This is related to item 1. Subcontext names are an empty string by default, which is converted to null by Oracle. This time, we can't allow null values since subcontext names must be unique and most databases allow multiple null values in unique columns. So, we must change the default subcontext name to something else, that is unlikely to appear in a real subcontext. Unfortunately, the design is a bit bad and the empty string is hardcoded in a lot of places which may take a subcontext.

[Nicklas Nordborg]

7. Analysing a table after a batch insert

Another problem is to determin the betst way to updated the index statistics for a table after a large batch insert. In other words what SQL command(s) the DbEngine.getAnalyzeTableSql() should return. It seems to work with ANALYZE <tablename> but Oracle documentation says that it is deprecated and may be removed in future releases. They recommend using the DBMS_STATS package instead but I can't get that to work.