AO DatabaseUpdater causes failures on oracle

Description

Unlike other supported DBs (mysql, postgres, mssql, memory) oracle throws errors during AO development when trying to migrate objects. I believe its related to JRA-29155 in which GH encountered it for installs, but the underlying issue is in AO.

Error

1 2 net.java.ao.sql - Exception executing SQL update <CREATE SEQUENCE aefjuser."AO_000000_TEAM_ID_SEQ" INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1> java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object

This is particularly an issue for developers whom follow this guide - https://developer.atlassian.com/docs/atlassian-platform-common-components/active-objects/developing-your-plugin-with-active-objects/the-active-objects-library/testing

Specifying a DatabaseUpdater through the @Data annotation. It is implemented here as a static inner class (AoBlogServiceTestDatabaseUpdater). Its update method allows you to prepare the database for tests. It is called only once per class (or per set of classes with the same DatabaseUpdater).

Workaround (for developers)

If using the DatabaseUpdater class for your test suites, add a method that explicitly drops sequences. Hope it spares someone the hours i wasted trying to get oracle to play nice like all the other DBs.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 public static void dropEverything(EntityManager em) throws SQLException { DatabaseProvider provider = em.getProvider(); String schema = provider.getSchema(); Connection connection = provider.startTransaction(); log.warn("Dropping All Sequences and Tables!"); drop("SEQUENCE",connection); drop("TABLE",connection); provider.commitTransaction(connection); connection.close(); } private static void drop(String type, Connection connection) throws SQLException{ Statement stmt = null; try { stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("select * from user_" + type + "s"); while (rs.next()) { String name = rs.getString(type +"_name"); log.warn("Dropping "+ type +" " + name); Statement dropstmt = null; try { dropstmt = connection.createStatement(); int result = dropstmt.executeUpdate("DROP " + type + " " + name ); log.warn("Results: " + result); } catch (SQLException e) { e.printStackTrace(); } finally { if (dropstmt != null) { dropstmt.close(); } } } } catch (SQLException e) { e.printStackTrace(); } finally { if (stmt != null) { stmt.close(); } } log.warn(type + "s dropped"); }

This should be run just prior to any migrate tasks in DatabaseUpdater update() method.

1 2 3 4 5 6 7 8 @Override public void update(EntityManager em) throws Exception { if (findDatabaseType(em).equals(DatabaseType.ORACLE)){ dropEverything(em); } em.migrate(SupportTeam.class); em.migrate(SupportMember.class); em.migrate(TeamToUser.class);

Environment

oracle 12
jira 6.x
ao 0.28.0

Testing Notes

Add notes...

Status

Assignee

Unassigned

Reporter

Eddie Webb

Labels

None

Add-on Type

None

Team

None

CC

None

Risk factor

None

QA Kickoff Status

None

QA Demo Status

None

Affects versions

0.28.0

Priority

Minor
Configure