For database independence, we use JDBC and SQL standards compliance. We've taken a simple, yet efficient approach in which all of our objects know how to talk to the database directly, using a connection pool and DatabaseUtils Class for database-specific syntax. We don't use triggers, BLOBs, cursors or stored procedures.

In general, our Base and List Classes use:

• SELECT with LEFT JOINs
• SELECT with WHERE clause uses IN (SELECT)
• SELECT with OFFSET and LIMIT, or TOP to retrieve paged list of records (db dependent)
• SELECT with EXISTS
• INT, VARCHAR(300+), TEXT, FLOAT, TIMESTAMP or DATETIME types
• TIMESTAMPS with NULL values and without auto-timestamping behavior
• INSERT with retrieval of SEQUENCE or AUTONUMBER before or after insert (db dependent)
• TRANSACTIONS with COMMIT and ROLLBACK
• CURRENT_TIMESTAMP
• Cast functions as defined in DatabaseUtils (db dependent)

Changes that need to be made in ConcourseSuite CRM

1. Verify that the database meets the application's implemented capabilities; are there any limits on table names, field names, datatype lengths, indexes, or sequences? If so, an algorithm will be needed to truncate the default table, field, index, and sequence names
2. Copy the JDBC driver into ConcourseSuite CRM's lib directory, the driver name must include version information
3. Implement the database creation scripts based on the included scripts; PostgreSQL is the reference implementation, however the database may be closely related to another database; confirm the characteristics of the database first
4. Change ConcourseSuite CRM's master.properties file to include a connectivity example
5. Change DatabaseUtils.java to support the new database
6. Change PagedListInfo.java to support the new database
7. Change CustomFieldRecord.java to support the new database
8. Change Setup.java and configure_database.jsp for application installation

Testing

1. First configure ConcourseSuite CRM as usual, but specify the new database in build.properties during deployment
2. Manually create the database
3. Run "ant installdb" to create the database schema and install the base crm data
4. Repeat until installdb works
5. Try to login and test the application