unepwcmc/SAPI — pull request #116 by agnessa (2013-11-21 14:39:45+00:00)
Title: Handling synonyms

Numerous schema changes in this PR:
-> removed old reported_species_name and reported_appendix fields from shipments
-> added new reported_taxon_concept_id field to shipments
-> added not null and unique constraints to permits, shipment export permits and shipments

NOTE: Migrations will wipe out all the shipments related data.

Moreover, the submit shipments mechanism has been moved entirely into a stored procedure. The SQL statements have grown way beyond what a regular ruby class should endure. In addition, the stored proc returns the number of inserted rows or -1 if not all rows could be inserted - we can then easily rollback the transaction.

The synonym-related improvements to the SQL inserts are as follows:
-> match the uploaded scientific name with an accepted name in the first place. Whatever is matched here, will be stored as reported_taxon_concept_id
-> if what was matched above is not a synonym, store it as taxon_concept_id; otherwise resolve to accepted name

The search will only look at taxon_concept_id; if someone searches by synonym, it gets resolved to accepted name through the autocomplete mechanism.

Another improvement to the submitting shipments SQL is splitting the export permit field on [:;,].