Specify 6.8.02 and Schema 2.10 Updates Release Notes

Specify 6

Specify 6.8.02 includes minor bug fixes along with a significant Specify Schema 2.10 update.

Specify 6.8.02 Release Notes

Released 10 November 2022

  • Made changes to Specify 6 splash screen to reflect the addition of SCC’s Founding Member, The Commonwealth Scientific and Industrial Research Organisation (CSIRO).
  • Made pick-list sorting case-insensitive - #1134 (Requested by CSIRO)
  • Corrected missing name attribute for geography code fields - #1097 (Requested by Barcelona)
  • Fixed various typographic errors in field descriptions

Specify Schema Update

[Notes for System Administrators]

The 2.10 schema update is a response to requests we have received since the last update from SCC members for additions and extensions to the Specify data model. These changes will affect the database schema shared by Specify 6 and 7, but they were primarily motivated by Specify 7 user requirements. If you are interested in a schema change for the next update, submit a request on the Specify Community Forum.

If you are using Specify 7, your server and database will need to be updated using the Specify 6.8.02 installer to update to Schema 2.10. Specify Cloud hosted databases will be updated automatically to Specify 6.8.02, Schema 2.10, and to Specify 7.8 when it is released soon.

Data forms can be modified in Specify 6 or 7 to provide user access to the new database components. The Schema update has been tested with Specify 6 and 7, and all of the schema changes listed below are available in both platforms.

Once Specify 6.8.02 and Schema 2.10 are applied to a Specify database from a Specify 6 workstation, older installed versions of Specify 6 (6.8.01 and earlier) on other workstations will not operate until they are also updated.

Schema 2.10 Release Notes

The Specify 2.10 schema update changes all data fields previously declared as a floating point values (Float) to the fixed-point Decimal data type. The Decimal data type accurately represents any number within the precision of the format, whereas Floats round and change decimal values. This is important when exact decimal values for fields like measurements need to be protected without changes due to rounding.

Locality names can now be longer than 256 characters, the limit is now 1,024 characters. Taxon names can now exceed 64 characters and extend, up to 256 characters in length. This is useful when Taxon names are are applied to specimens that are hybrids of hybrid parents. Query fields can now contain over 65,000 characters, allowing queries to be more oversized and powerful.

Additional fields were added to several tables, see details below. Exchange records now support associated attachments.

Agents can now be linked to a new AgentIdentifier table, enabling many unique identifiers from multiple ID schemes and authorities to be linked to an Agent record. Agent identifiers can be uploaded through the Specify WorkBench and associated with existing agents. Current databases may continue to use fields in the Agent table itself for an external ID if they choose.

Another significant change is a new “Determiners” (join) table. The existing “Determination” table has a relationship with the “Determiners” table, allowing multiple Agents to be selected as Determiners, in a parallel way to specifying Collectors as Agents in the Collectors relationship to Collecting Event.

Because the Determiners table is new, it will initially be empty in existing databases. To begin using it, one can add the Determiners table to a form and unhide it in the schema configuration tool. This can be used in addition or in the place of the Determiner field in the Determination table. Several SCC collections requested this change to be able to manage and query Determiner (Agent) records. Note: This schema update does not automatically migrate existing Determiner records to the new Determiners table.


New Tables

Determiner B C D E F
Column Name # Data Type Auto Increment Notes Requested By
DeterminerID 1 int(11) TRUE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
TimestampCreated 2 datetime FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
TimestampModified 3 datetime FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
Version 4 int(11) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
IsPrimary 5 bit(1) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
OrderNumber 6 int(11) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
Remarks 7 text FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
Text1 8 text FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
Text2 9 text FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
YesNo1 10 bit(1) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
YesNo2 11 bit(1) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
CreatedByAgentID 12 int(11) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
AgentID 13 int(11) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
DeterminationID 14 int(11) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
ModifiedByAgentID 15 int(11) FALSE CSIRO, New Mexico, West Virginia, Washington, and more (#1131)
ExchangeInAttachment
Column Name # Data Type Auto Increment Notes Requested By
ExchangeInAttachmentID 1 int(11) TRUE RBGE (#1333)
TimestampCreated 2 datetime FALSE RBGE (#1333)
TimestampModified 3 datetime FALSE RBGE (#1333)
Version 4 int(11) FALSE RBGE (#1333)
Ordinal 5 int(11) FALSE RBGE (#1333)
Remarks 6 text FALSE RBGE (#1333)
CreatedByAgentID 7 int(11) FALSE RBGE (#1333)
ModifiedByAgentID 8 int(11) FALSE RBGE (#1333)
ExchangeInID 9 int(11) FALSE RBGE (#1333)
AttachmentID 10 int(11) FALSE RBGE (#1333)
ExchangeOutAttachment
Column Name # Data Type Auto Increment Notes Requested By
ExchangeOutAttachmentID 1 int(11) TRUE RBGE (#1333)
TimestampCreated 2 datetime FALSE RBGE (#1333)
TimestampModified 3 datetime FALSE RBGE (#1333)
Version 4 int(11) FALSE RBGE (#1333)
Ordinal 5 int(11) FALSE RBGE (#1333)
Remarks 6 text FALSE RBGE (#1333)
CreatedByAgentID 7 int(11) FALSE RBGE (#1333)
ModifiedByAgentID 8 int(11) FALSE RBGE (#1333)
ExchangeOutID 9 int(11) FALSE RBGE (#1333)
AttachmentID 10 int(11) FALSE RBGE (#1333)
AgentIdentifier
Column Name # Data Type Auto Increment Notes Requested By
AgentIdentifierID 1 int(11) TRUE Many institutions (#1152)
TimestampCreated 2 datetime FALSE Many institutions (#1152)
TimestampModified 3 datetime FALSE Many institutions (#1152)
Version 4 int(11) FALSE Many institutions (#1152)
Date1 5 date FALSE Many institutions (#1152)
Date1Precision 6 tinyint(4) FALSE Many institutions (#1152)
Date2 7 date FALSE Many institutions (#1152)
Date2Precision 8 tinyint(4) FALSE Many institutions (#1152)
Identifier 9 varchar(2048) FALSE Many institutions (#1152)
IdentifierType 10 varchar(256) FALSE Many institutions (#1152)
Remarks 11 text FALSE Many institutions (#1152)
Text1 12 text FALSE Many institutions (#1152)
Text2 13 text FALSE Many institutions (#1152)
Text3 14 text FALSE Many institutions (#1152)
Text4 15 text FALSE Many institutions (#1152)
Text5 16 text FALSE Many institutions (#1152)
YesNo1 17 bit(1) FALSE Many institutions (#1152)
YesNo2 18 bit(1) FALSE Many institutions (#1152)
YesNo3 19 bit(1) FALSE Many institutions (#1152)
YesNo4 20 bit(1) FALSE Many institutions (#1152)
YesNo5 21 bit(1) FALSE Many institutions (#1152)
CreatedByAgentID 22 int(11) FALSE Many institutions (#1152)
ModifiedByAgentID 23 int(11) FALSE Many institutions (#1152)
AgentID 24 int(11) FALSE Many institutions (#1152)

New Fields Added to Existing Tables

GeoCoordDetail B C D E F
Column Name # Data Type Auto Increment Notes Requested By
Integer1 30 int(11) FALSE CSIRO (#1132)
Integer2 31 int(11) FALSE CSIRO (#1132)
Integer3 32 int(11) FALSE CSIRO (#1132)
Integer4 33 int(11) FALSE CSIRO (#1132)
Integer5 34 int(11) FALSE CSIRO (#1132)
Number1 35 decimal(20,10) FALSE CSIRO (#1132)
Number2 36 decimal(20,10) FALSE CSIRO (#1132)
Number3 37 decimal(20,10) FALSE CSIRO (#1132)
Number4 38 decimal(20,10) FALSE CSIRO (#1132)
Number5 39 decimal(20,10) FALSE CSIRO (#1132)
Text4 40 text FALSE CSIRO (#1132)
Text5 41 text FALSE CSIRO (#1132)
YesNo1 42 bit(1) FALSE CSIRO (#1132)
YesNo2 43 bit(1) FALSE CSIRO (#1132)
YesNo3 44 bit(1) FALSE CSIRO (#1132)
YesNo4 45 bit(1) FALSE CSIRO (#1132)
YesNo5 46 bit(1) FALSE CSIRO (#1132)
GiftPreparation
Column Name # Data Type Auto Increment Notes Requested By
Text1 15 text FALSE Bishop Museum (#808)
Text2 16 text FALSE Bishop Museum (#808)
Text3 17 text FALSE Bishop Museum (#808)
Text4 18 text FALSE Bishop Museum (#808)
Text5 19 text FALSE Bishop Museum (#808)
ConservDescription
Column Name # Data Type Auto Increment Notes Requested By
Date1 25 date FALSE Barcelona (#1154)
Date1Precision 26 tinyint(4) FALSE Barcelona (#1154)
Date2 27 date FALSE Barcelona (#1154)
Date2Precision 28 tinyint(4) FALSE Barcelona (#1154)
Date3 29 date FALSE Barcelona (#1154)
Date3Precision 30 tinyint(4) FALSE Barcelona (#1154)
Date4 31 date FALSE Barcelona (#1154)
Date4Precision 32 tinyint(4) FALSE Barcelona (#1154)
Date5 33 date FALSE Barcelona (#1154)
Date5Precision 34 tinyint(4) FALSE Barcelona (#1154)
Integer1 35 int(11) FALSE Barcelona (#1154)
Integer2 36 int(11) FALSE Barcelona (#1154)
Integer3 37 int(11) FALSE Barcelona (#1154)
Integer4 38 int(11) FALSE Barcelona (#1154)
Integer5 39 int(11) FALSE Barcelona (#1154)
Number1 40 decimal(20,10) FALSE Barcelona (#1154)
Number2 41 decimal(20,10) FALSE Barcelona (#1154)
Number3 42 decimal(20,10) FALSE Barcelona (#1154)
Number4 43 decimal(20,10) FALSE Barcelona (#1154)
Number5 44 decimal(20,10) FALSE Barcelona (#1154)
Text1 45 text FALSE Barcelona (#1154)
Text2 46 text FALSE Barcelona (#1154)
Text3 47 text FALSE Barcelona (#1154)
Text4 48 text FALSE Barcelona (#1154)
Text5 49 text FALSE Barcelona (#1154)
YesNo1 50 bit(1) FALSE Barcelona (#1154)
YesNo2 51 bit(1) FALSE Barcelona (#1154)
YesNo3 52 bit(1) FALSE Barcelona (#1154)
YesNo4 53 bit(1) FALSE Barcelona (#1154)
YesNo5 54 bit(1) FALSE Barcelona (#1154)
CollectionObjectAttribute
Column Name # Data Type Auto Increment Notes Requested By
Text31 119 text FALSE CSIRO (#2205)
Text32 120 text FALSE CSIRO (#2205)
Text33 121 text FALSE CSIRO (#2205)
Text34 122 text FALSE CSIRO (#2205)
Text35 123 text FALSE CSIRO (#2205)
Text36 124 text FALSE CSIRO (#2205)
Text37 125 text FALSE CSIRO (#2205)
Text38 126 text FALSE CSIRO (#2205)
Text39 127 text FALSE CSIRO (#2205)
Text40 128 text FALSE CSIRO (#2205)
Loan
Column Name # Data Type Auto Increment Notes Requested By
Text3 36 text FALSE Bishop Museum (#808)
Text4 37 text FALSE Bishop Museum (#808)
Text5 38 text FALSE Bishop Museum (#808)
LoanPreparation
Column Name # Data Type Auto Increment Notes Requested By
Text1 13 text FALSE Bishop Museum (#808)
Text2 14 text FALSE Bishop Museum (#808)
Text3 15 text FALSE Bishop Museum (#808)
Text4 16 text FALSE Bishop Museum (#808)
Text5 17 text FALSE Bishop Museum (#808)
OtherIdentifier
Column Name # Data Type Auto Increment Notes Requested By
Date1 6 date FALSE
Date1Precision 7 tinyint(4) FALSE
Date2 8 date FALSE
Date2Precision 9 tinyint(4) FALSE
Text1 13 text FALSE
Text2 14 text FALSE
Text3 15 text FALSE
Text4 16 text FALSE
Text5 17 text FALSE
YesNo1 18 bit(1) FALSE
YesNo2 19 bit(1) FALSE
YesNo3 20 bit(1) FALSE
YesNo4 21 bit(1) FALSE
YesNo5 22 bit(1) FALSE
Agent1ID 23 int(11) FALSE
Agent2ID 25 int(11) FALSE

Updated Fields

Locality B C D E F
Column Name # Data Type Auto Increment Notes Requested By
LocalityName 17 varchar(1024) FALSE Increased length from 256 to 1024 UTAustin (#1125)
Taxon
Column Name # Data Type Auto Increment Notes Requested By
Name 20 varchar(256) FALSE Increased length from 64 to 256
RecordSet
Column Name # Data Type Auto Increment Notes Requested By
Name 9 varchar(280) FALSE Increased length from 64 to 280 South Africa, Gotheburg (#1158)
SpQueryField
Column Name # Data Type Auto Increment Notes Requested By
StartValue 20 mediumtext FALSE Increased length from 1000 to 65535
EndValue 9 mediumtext FALSE Increased length from 1000 to 65535
PickListItem
Column Name # Data Type Auto Increment Notes Requested By
Title 6 varchar(1024) FALSE Increased length from 128 to 1024 CSIRO (#1157)
Value 7 varchar(1024) FALSE Increased length from 128 to 1024 CSIRO (#1157)
SpLocaleItemStr
Column Name # Data Type Auto Increment Notes Requested By
Text 7 varchar(2048) FALSE Increased length from 255 to 2048 RBGE (#1159)
WorkBench
Column Name # Data Type Auto Increment Notes Requested By
Name 12 varchar(256) FALSE Increased length from 64 to 256 South Africa, Gotheburg (#1158)
Container
Column Name # Data Type Auto Increment Notes Requested By
Name 7 varchar(1024) FALSE Increased length from 64 to 1024 Barcelona (#1123)
Description 6 mediumtext FALSE Increased length from 1024 to 65535
Attachment
Column Name # Data Type Auto Increment Notes
MimeType 12 varchar(1024) FALSE Increased length from 64 to 1024
CollectingEventAttribute
Column Name # Data Type Auto Increment Notes Requested By
Text10 20 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text11 21 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text12 22 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text13 23 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text14 24 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text15 25 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text16 26 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text17 27 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text4 30 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text5 31 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text6 32 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text7 33 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text8 34 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
Text9 35 mediumtext FALSE Increased length from 50 to 65535 Gothenburg (#1155)
VoucherRelationship
Column Name # Data Type Auto Increment Notes
Number1 11 decimal(20,10) FALSE Field type changed from float to decimal
Number2 12 decimal(20,10) FALSE Field type changed from float to decimal
CollectionObjectAttribute
Column Name # Data Type Auto Increment Notes
BottomDistance 73 decimal(20,10) FALSE Field type changed from float to decimal
TopDistance 77 decimal(20,10) FALSE Field type changed from float to decimal

All but a few float or double fields are now decimal fields in the Specify schema.