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.