bety: BETYdb 4.2 New Value Constraints

New value constraints for database tables and validations for corresponding models; new RSpec tests; several bug fixes.

Many new values constraints have been added to ensure database-integrity. Consistency checks have been added to the dbfiles table to ensure the referred-to containers exist. Restrictions on editing variables associated with traits or covariates that violate the range restriction for the variable value have been relaxed. Many bug fixes have been implemented.

Changes Pertinent to PEcAn Users

Administrators need to do a database migration.

See "Database Changes" below.

Summary of Changes Implemented values constraints, both at the database level and and the Web user-interface (Rails app) level

The addition of value constraints is a major step toward helping to ensure data-integrity. It should no longer be possible, for example, to enter a site with a longitude value of 2000 (which has happened!) or genuses that aren't capitalized. Not only will these constraints help prevent gross errors, they will also help standardize the set of allowable values. When used to constrain values in columns comprising a candiate key, this will make it less likely that duplicate rows for the same entity will occur. To give one example, a uniqueness constraint on species.scientificname will not by itself prevent the occurrence of two species rows that are identical except that the first has scientificname "Abies alba" and the second has scientificname "Abies alba ". Requiring values in this column to be "whitespace-normalized" (no leading or trailing space, no double-spaces) will prevent this sort of duplicate from occurring.

Implemented integrity constraints on references to inputs, models, and posteriors in dbfiles table

A row having container_type = 'Input' must have a value for container_id that matches the id column value of some row in the inputs table. Similarly for container types "Model" and "Posterior".

Allowing "lazier" approach to fixing variable range violations

It used to be a variable could not be edited if any trait or covariate row that referred to the variable had a variable value that violated the range restriction for the variable. One first had to fix all the range violations in the traits and/or covariates rows before the variable could be edited, even if the update only involved, say, changing the notes. Now the trigger that checks for range violations is only run when an attempt is made to change the value of the min or max attribute. Thus all other attributes are now freely editable, regardless of any range violations in associated rows.

Bug Fixes

Some of the fixes include:

• Corrected text of mis-labelled buttons

• Fix for issue #282: Can't create two new managements in a row for a given treatment.

• Eliminated duplicate fields from New Species form

• Fixed layout of Species and Covariate "Edit" pages (GH #280)

• Fixed trait-search action used on Covariate "Edit" page

• Eliminated vestigial obsolete code and routes

• Restored the change-password checkbox to the edit-user page to make this page clearer and more user-friendly

• Re-aligned and rearranged fields in species/show page (GH #135)

• Restored some routes that were eliminated when wildcard routes were eliminated.

• Corrected title for Machine "Show" page (GH #294)

• Allow Managers to delete Yield records; also allow Creators to delete Yield records they themselves created (part of Redmine issue #2334)

• Made treatments on Listing Treatments page sortable

• Restyled Listing Treatments page to make it more readable, and added delete buttons to rows that user is allowed to delete (part of Redmine issue #2334)

• Better error message when treatment is missing in bulk upload file (GH #287)

• Better error message for violation of foreign key constraint (GH #306)

• Fixed "CF Guidelines" link on Variables "Edit" page (GH #308)

• Fixed Variable update form so that variable type can be updated (GH #309)

• Fixed Management "Edit" page so that citation is not inadvertently changed (GH #313)

• Fixed "map" links on Site listing page that were broken in certain deployment configurations and removed "map" link from "Show" pages (GH #305)

• On "Edit" pages for traits and yields, preselect the current access level so that it isn't inadvertently changed (GH #302)

• Fixed display of incorrect QA/QC values on yields listing page (part of GH #303)

• Fixed bug in data access filter so that Views and Creators can once again see all records that they themselves created

• Changed criteria for displaying the edit control for QA/QC values on the yields list page (part of GH #303)

• Re-styled "New File" page (GH #304)

• Removed "Show" button from "New Site" page (GH #321)

• Changed "Back" buttons on all "New" pages to say "All Records" like all the edit pages do (GH #322)

Steps Needed for Upgrade Database Changes

Administrators need to do database migrations!

There are three database migrations included in this release. The first and most extensive of these involves the addition of several new value constraints. The following set of SQL commands may prove helpful in applying this migration, especially if a data synchronization has not been done recently:

update ensembles set notes = '', updated_at = now() at time zone 'utc' where notes is null; update runs set outprefix = '', updated_at = now() at time zone 'utc' where outprefix is null; update runs set setting = '', updated_at = now() at time zone 'utc' where setting is null; update users set state_prov = '', updated_at = now() at time zone 'utc' where state_prov is null; update users set postal_code = '', updated_at = now() at time zone 'utc' where postal_code is null; update workflows set params = '', updated_at = now() at time zone 'utc' where params is null;

If further trouble-shooting of migrations is required, the following may be helpful in finding the source of migration problems:

bundle exec rake db:migrate 2>&1 | grep ERROR

(If you run BETYdb in the production environment, and your production database differs from you development database, prefix this command with RAILS_ENV=production.)

The database version for this release is 20150521211114.

Gem Installation

No new Gems need be installed for this release.

Status of RSpec Tests

• All tests continue to pass when run in the default environment and can be run using the command

  bundle exec rspec

Complete details for running the rspec tests are on the updated Wiki page at https://github.com/PecanProject/bety/wiki/Automated-Tests