fixed in 1.10 and up

I'm not sure how to deal with the following idea in liquibase, but I believe that this is what the migration should do when updating the DB before adding the unique constraint for the service name:

1. Find the duplicate services

2. For each of them, do the following:

   1. Pick one serviceId (probably the minimum value of the ID)

   2. Update the entries from the ifServices table to use the selected ID (step 2.1)

   3. Remove all the entries from the service table except for the selected ID (step 2.1)

3. Add the unique constraint to the DB

I'm not sure what should be done in case of a rollback, but I believe that all the steps from 2, must be done no matter what.

There is another thing to consider: something on the code (I blame Provisiond, but maybe I'm wrong) is creating these duplicates, so after fixing the DB, this might introduce a new problem because Hibernate is going to throw an exception when trying to add a new service with an existing name on the DB. When this happen, we should check the code, and add something like this:

An example of a well defined table is the categories table. Something like that we should have on the service table.

This table has a unique index constraint for categortname. So, an exception will be thrown when trying to save a new category with the same name.

But, as I said, the service table doesn't have that constraint:

Finally, as you can see, the service table doesn't have a sequence defined for the serviceid. Of course, Hibernate is going to use it because the ID is defined that way on the code, but the schema is saying something different, and I believe that the schema and the code must be consistent.

Another inconsistency:

This is related with OnmsApplication. There is a unique constraint for the name which is consistent, but there is no sequence for the ID, and the code is saying that a sequence is going to be used which is not consistent.

I'm not sure if there are more inconsistencies, as I've focused on the entities that have "unique=true" on the code.

From OnmsServiceType:

According with Hibernate's documentation, the "unique=true" is going to be used when generating the schema, but that is not going to be used to enforce a unique constraint for the serviceName column. In order to be sure that we have only one servicename on the DB, we must modify the service table.

Here is one of the methods from ServiceTypeDaoHibernate that is going to fail with duplicated services:

The implementation of findByCacheKey is going to generate an exception if there are more than one entry on the DB with the same name.

Of course this is going to brake any part of the code that is using NetworkElementFactory.getServiceIdFromName, like all the JSP from jetty-webapps/opennms/element/

This is a JUnit test that proves that the problem exist:

The idea is that if I try to save another OnmsServiceType entity with the same name, Hibernate must fail complaining about a unique constraint on the table.

Right now the test is failing when calling findByName, because the code is throwing the following exception:

The problem is:

How to do that without destroying references on the ifservices table that are referencing the duplicated services?