Check for pre existing sequences when using Oracle DB

When connecting to an Oracle DB we check for existing schemas and tables but do not check for pre existing sequences. We should check for these and handle them in the same way as existing tables etc.

EDITED:

Steps to reproduce:

• Install JIRA with Oracle;
• Install JIRA Agile (GreenHopper) on it as usual;
• Remove JIRA Agile (GreenHopper);
• Stop JIRA;
• Just to simulate, drop all the "AO_60DB71*" tables;
• Install JIRA Agile (GreenHopper) once again;

Expected behavior:

• JIRA should check if all the "AO_60DB71*" tables and sequences already exists. If not, drop or reset the invalid tables and sequences; Then, install JIRA Agile (GreenHopper).

Current behavior:

• JIRA does not check properly if all the "AO_60DB71*" tables and sequences already exists, but install JIRA Agile anyway. Then, browsing some JIRA issue, the following error will occur:

2012-10-12 10:42:00,156 http-8080-4 ERROR xxx 652x52361x3 9cd15br 192.168.132.65 /browse/BC-115 [net.java.ao.sql] Exception executing SQL update <CREATE SEQUENCE "AO_60DB71_KEY_ID_SEQ" INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1>
java.sql.SQLSyntaxErrorException: ORA-00955: Name is Already Used by an Existing Object

It will cause lots of problems in JIRA as well, by example, when creating and cloning issues. Always related to Oracle (ORA-00955).

How to fix it manually:

• Remove JIRA Agile;
• Stop JIRA;
• Manually drop the offending tables and sequences on the database;
• Start JIRA once again;
• Re-install JIRA Agile.
  This is also described in this JIRA KB.