Container Name Uniqueness

Turning Container Name Uniqueness ON

This constraint is set at the database level. Container Uniqueness can be turned ON by using the migrator tool, and running the optional migration step called "ContainerNameUniqueConstraints". This can be done by calling this command:

java -jar migrator.jar ContainerNameUniqueConstraints

Notes:

• Of course, edit the migrator.properties file to ensure that the mode is set to "migrate" not just "validate".

• If the migrator has carried out its work successfully, the database should now have a new index present called 'unique_cnt_name'. (In psql, do \di to see indexes)

• You do not need to restart JBoss in order for this change to take effect

Turning Container Name Uniqueness OFF

Container Uniqueness can be turned OFF by running this SQL command:

drop index  unique_cnt_name;

This statement will fail if there are already any non-unique container names in the database. If it fails, you can find all non-unique names with this statement:

SELECT name,  COUNT(name) AS namecount FROM container GROUP BY name HAVING COUNT(name) >  1;

For each non-unique name found, you can iterate through all instances of it, and rename them so that they will be unique.

SELECT name, luid FROM container WHERE where name = 'non-unique_name';

UPDATE container SET name='non-unique_name-1' WHERE luid=luid_of_container;

Resolving Non-Unique Container Names

When you run the above query and determine there are too many containers to hand-edit, what now?

1) Are any of the containers that have non unique names in a depleted or discarded state? If so, you can probably delete them once the customer gives the all clear:

select name, luid, stateid from container where name in (SELECT name FROM container GROUP BY name HAVING COUNT(name) >  1) order by name;

now, what do the values for the container stateid mean? You won't find them in the database, they are hard coded as follows

1, "Empty"

2, "Populated"

3, "Depleted"

4, "Discarded"

5, "Reagent-Only"

6, "New"

7, "Hybridized"

8, "Scanned"

9, "Discarded"

Armed with this knowledge, we can refine the query to highlight containers that may be deleted with little consequence:

SELECT name, luid, stateid FROM container

WHERE name IN (SELECT name FROM container GROUP BY name HAVING COUNT(name) >  1)

AND stateid IN (1,4,9) ORDER BY name;

Notes:

1. Containers can only be deleted if they are empty

2. SQL 'IN' statements normally have a limit of 255 records, so if the the sub-select - the one in parenthesis - returns more than 255 records, your mileage may vary