update vcell database access code to support Oracle and Postgres

[jcschaff]

Background

The database layer in VCell consists of the following 4 layers (simplified):

1. Our Data Definition Layer
   • hand coded Java classes which are one-to-one with tables in the VCell database (see cbit.sql.Table subclasses)
   • cbit.sql.CompareDatabaseSchema: tooling to query a database for its table definitions (DDL) and compare our Java classes and suggest needed changes).
   • cbit.vcell.modeldb.SQLCreateAllTables.java: tooling to create a new database schema within a database server, populating all needed VCell tables with Constraints and Sequences.
2. Our Database Driver Layer (like low level ORM - Object/Relational Mapping).
   • subclasses of cbit.vcell.modeldb.DbDriver (and cbit.sql.Table) generate and invoke SQL to create, retrieve, update, and delete records in our database.
3. Our Transaction Layer which manages database connections, retries, commits and rollbacks.
   • subclasses of cbit.vcell.modeldb.AbstractDbTopLevel driven by cbit.vcell.modeldb.DatabaseServerImpl and incremental saves of complex documents via the cbit.vcell.modeldb.ServerDocumentManager.
4. Database Service/Communication Layer
   • implementations of cbit.vcell.server.UserMetaDbServer and cbit.vcell.server.AdminDatabaseServer accessed by the client via a VCellConnection or more directly by our backend servers.

Support either Oracle and Postgres in database layers

• Layer 1: (Data Definition Layer) - not much to do here - translate a few SQL data types - and make sure we can generate all of the VCell tables in a new Postgres database.
• Layer 2: (Database Driver Layer) - first pass was done 6 years ago, but considerable work was needed to modify and upgrade our SQL statements to a subset which is understood by both Oracle 11g and latest Postgres.
  • primary work is removing the Oracle-only syntax '(+)' for outer table joins and replace and test both Oracle and Postgres with the modern SQL syntax for outer joins.
  • update other SQL syntax as needed.