Auto mapper not working - bad data (and possible fixes)

[Brett]

I had an issue where the auto mapper stopped working for Corporate mask, but worked for personal mask.

After looking in to this, I found there were 2 rows in the wormholes table that referenced signatures that were no longer in the signatures table.

This caused problems in the client side java script code in automapper.js, line 34, which was appears to iterate over the wormholes looking up the initialID or secondaryID of for the signature. When the loop hit the wormholes without corresponding signatures, a null object reference is thrown.

I am not certain how the wormholes rows did not get removed when the signatures were removed.

I removed the offending rows from the wormholes table, and the auto mapping feature started working again for corporate mask users.

One potential fix could be to create a foreign dependency between wormholes table and signatures table.

Another potential fix would be to capture the exception in automapper.js tripwire.client.signatures[wormhole.initialID] (and secondaryID) return null objects.

[Brett]

I have come up with a potential solution.

This SQL code will create a foreign key constraint between signatures and wormholes. This constraint will ensure that both signatures exist for a wormhole. If either signature is missing, the constraint will not allow the wormhole to be inserted. If either of the signatures is deleted, the related wormhole is also deleted.

ALTER TABLE `tripwire`.`wormholes` 
ADD INDEX `fk_secondaryID_signature_idx` (`secondaryID` ASC);
ALTER TABLE `tripwire`.`wormholes` 
ADD CONSTRAINT `fk_initialID_signature`
  FOREIGN KEY (`initialID`)
  REFERENCES `tripwire`.`signatures` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_secondaryID_signature`
  FOREIGN KEY (`secondaryID`)
  REFERENCES `tripwire`.`signatures` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

This SQL should prevent the case where there is a wormhole with no matching signatures (initialdID and secondaryID).

If the wormhole is deleted, the related signatures are not changed (or deleted). This should not cause a problem because not all signatures are related to wormholes. So there will be signatures that do not have corresponding wormhole entry.

The current "clean up" process will remove any expired signatures, and if the wormhole exists, it will be removed automatically as well. If the wormhole has already been removed, just the signatures would get removed during the clean up.

One additional change to the code would be to have the UI delete the wormhole signatures instead of the wormhole. By deleting the signatures, the wormhole entry would be deleted automatically. I suspect the query should look like this:

delete from signatures where id in (select initialID, secondaryID from wormholes where id = {1})

{1} = the id of the wormhole to be removed

With the above SQL constraint, and the change to wormhole delete SQL, there should be no instance of wormholes without signature ids.

[Astriania]

system should be resilient to bad wormholes these days