Diversity Collection
No-SQL Interface
JSON Cache
Databases of the modules provid a No-SQL interface where the data of the main table and the depending tables are merged as JSON.
Table JsonCache
Content of table JsonCache restricted to public available data
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ID | int | Unique ID for the Dataset, Primary key | NO | ID of the source |
| URI | varchar (500) | The URL as combination of BaseURL and ID | NO | BaseURL and ID of the source |
| DisplayText | nvarchar (500) | Representation in the interface | NO | Main table of the source |
| LogUpdatedWhen | datetime | Date and time when the data were last updated | NO | - |
| Data | json | Data related to the current dataset | NO | - |
procFillJsonCache is started by an update trigger trgUpd… of the main table in the database
Interface in clients
All modules provide data via a cache table. In the header of the clients you can inspect the content of the JsonCache with a click on the button. For modules with a difference between local and public data, you can inspect the content of the public data with a click of the right mouse button.
Update
Apart of the update via the Trigger (see below) you can update the JsonCache via the 
update button underneath the button.
To update the JsonCache for the whole database select Administration - JsonCache… from the menu. a window as shown below will open where you can update the JsonCache for single datasets or the whole database.
Summary
graph TD;
TaxonName[Main table in database]
trgUpdTaxonName[trgUpd.. of main table in database]
TaxonName --> |Update in table| trgUpdTaxonName
proc[Procedure procFillJsonCache setting the content in table JsonCache]
trgUpdTaxonName --> proc
graph TD;
Mainform[Main form]
ButtonShow[Button show JsonCache of current dataset]
Mainform --> ButtonShow
Left[Show Data]
ButtonShow --> |Left click| Left
graph TD;
Mainform[Main form]
Admin[Administration menu]
Mainform --> Admin
Cache[JsonCache...]
Admin --> Cache
Adminform[Administration form]
Cache --> Adminform
AdminUpdateSingle[Update single dataset]
Adminform --> AdminUpdateSingle
AdminUpdateDB[Update for whole database]
Adminform --> AdminUpdateDB
Example
The JsonCache of the current dataset will be shown as in the example below:
[
{
"Server": "http://zsm.diversityworkbench.de/",
"Database": "DiversityCollection_ZSM_Training",
"URL": "http://zsm.diversityworkbench.de/Collection_ZSM_Training/399050",
"Type": "CollectionSpecimen",
"ID": 399050,
"DisplayText": "JsonCacheDemo",
"Project": [
{
"Project": "HYMIScoll",
"ProjectID": 927
}
],
"CollectionEvent": [
{
"LocalityDescription": "Germany, Bavaria",
"CollectionYear": 2024,
"CollectionMonth": 7,
"CollectionDay": 9,
"CollectionEventLocalisation": [
{
"DisplayText": "Altitude (mNN)",
"Content Location 1": "Altitude from",
"Location1": "679",
"Content Location 2": "Altitude to"
},
{
"DisplayText": "Named area (DiversityGazetteers)",
"Content Location 1": "Location",
"Location1": "Holzleithen, Bavaria, Germany",
"Content Location 2": "Thesaurus code"
},
{
"DisplayText": "Coord. WGS84",
"Content Location 1": "Longitude (East-West)",
"Location1": "10.959893227",
"Content Location 2": "Latitude (North-South)",
"Location2": "47.753982544",
"Geography": "POINT (10.959893227 47.753982544)"
}
],
"CollectionEventProperty": [
{
"PropertyName": "Geographic regions",
"PropertyType": "Vegetation",
"Property": "Eschen-Ahorn-Schluchtwald bzw. -Hangwald (feucht-kühle Standorte)",
"Hierarchy": "Eschen-Ahorn-Schluchtwald bzw. -Hangwald (feucht-kühle Standorte) | Schlucht-, Blockhalden- und Hangschuttwälder | Laub(misch)wälder und -forste (Laubbaumanteil > 50 %)"
}
],
"CollectionEventMethod": [
{
"Method": "Fallen",
"Marker": "1",
"CCollectionEventParameterValue": [
{
"PropertyType": "Alkohol",
"Property": "70%"
},
{
"PropertyType": "Pheromon",
"Property": "-"
},
{
"PropertyType": "Licht",
"Property": "400 nm"
}
]
}
],
"CollectionEventRegulation": [
{
"Regulation": "DE_PER_20210801",
"TransactionTitle": "DE_PER_20210801"
}
],
"CollectionEventSeries": [
{
"SeriesID": -1,
"Description": "Voralpen",
"SeriesCode": "B2024_02",
"DateStart": "2024-07-01T13:29:22",
"DateEnd": "2024-07-27T13:29:22",
"CollectionEventSeriesDescriptor": [
{
"Descriptor": "Schluchtwälder",
"DescriptorType": "Biotoperfassung"
}
]
},
{
"SeriesID": -4,
"Description": "Ammerschlucht",
"Geography": "POINT (10.9668204567046 47.7016669695774)",
"DateStart": "2024-07-09T13:29:22",
"DateEnd": "2024-07-10T13:29:22",
"CollectionEventSeriesDescriptor": [
{
"Descriptor": "Schluchtwälder",
"DescriptorType": "Biotoperfassung"
}
]
}
],
"ExternalIdentifier": [
{
"Type": "LSID",
"Identifier": "Event01"
}
]
}
],
"Annotation": [
{
"Annotation": "Annotation for specimen"
}
],
"CollectorsName": [
{
"CollectorsName": "Huber, F.",
"CollectorsNumber": "FH-654"
},
{
"CollectorsName": "Pfeiffer, R."
},
{
"CollectorsName": "Schmidt, H."
}
],
"CollectionSpecimenReference": [
{
"ReferenceTitle": "Reference for a part",
"SpecimenPartID_Reference": 182805
}
],
"CollectionSpecimenRelation": [
{
"RelatedSpecimenDisplayText": "ext. Relation of specimen to something else",
"RelationType": "Specimen part",
"RelatedSpecimenURI": "ext. Relation of specimen to something else"
}
],
"CollectionSpecimenPart": [
{
"SpecimenPartID": 182807,
"MaterialCategory": "DNA sample",
"CollectionHierarchy": [
{
"CollectionName": "Entomologie",
"CollectionAcronym": "Ento",
"Type": "department"
},
{
"CollectionName": "Insecta varia",
"CollectionAcronym": "InVa",
"CollectionOwner": "SNSB - Zoologische Staatssammlung München",
"Type": "department"
},
{
"CollectionName": "Odonata",
"Type": "department"
},
{
"CollectionName": "SNSB - Zoologische Staatssammlung München",
"CollectionAcronym": "ZSM",
"Type": "institution"
}
],
"CollectionSpecimenPartProcessing": [
{
"DisplayText": "DNA",
"ProcessingDate": "2024-07-10T10:47:53.050",
"ResponsibleName": ""
}
],
"CollectionSpecimenTransaction": [
{
"TransactionType": "loan",
"TransactionTitle": "Ausleihe nach berlin",
"CollectionName": "SNSB - Zoologische Staatssammlung München",
"ActualEndDate": "2024-07-04T00:00:00"
}
],
"CollectionSpecimenTask": [
{
"Task": "Exhibition ⁞ Part",
"Type": "Part",
"CollectionName": "Ebene 2",
"DisplayText": "JsonCacheDemo: Calopteryx virgo(Linnaeus, 1758) - DNA sample"
}
]
},
{
"SpecimenPartID": 182805,
"MaterialCategory": "pinned specimen",
"CollectionHierarchy": [
{
"CollectionName": "ZSM-InVa-D1482",
"CollectionAcronym": "10",
"Type": "drawer",
"CollectionImage": [
{
"URI": "https://www.zsm.mwn.de/drawers/InVa/Odonata/ZSM-InVa-D1482-2020"
}
]
}
],
"CollectionSpecimenTask": [
{
"Task": "Exhibition ⁞ Part",
"Type": "Part",
"CollectionName": "Ebene 2",
"DisplayText": "JsonCacheDemo: Calopteryx virgo(Linnaeus, 1758) - pinned specimen"
}
]
},
{
"SpecimenPartID": 182806,
"MaterialCategory": "tissue sample",
"CollectionHierarchy": [
{
"CollectionName": "ZSM-InVa-D1482",
"CollectionAcronym": "10",
"Type": "drawer",
"CollectionImage": [
{
"URI": "https://www.zsm.mwn.de/drawers/InVa/Odonata/ZSM-InVa-D1482-2020"
}
]
}
],
"CollectionSpecimenTransaction": [
{
"TransactionType": "regulation",
"TransactionTitle": "DE_PER_20210801",
"CollectionName": "SNSB - Zoologische Staatssammlung München"
}
],
"CollectionSpecimenTask": [
{
"Task": "Exhibition ⁞ Part",
"Type": "Part",
"CollectionName": "Ebene 2",
"DisplayText": "JsonCacheDemo: Calopteryx virgo(Linnaeus, 1758) - tissue sample"
}
]
}
],
"IdentificationUnit": [
{
"IdentificationUnitID": 437340,
"TaxonomicGroup": "fungus",
"Identification": [
{
"TaxonomicName": "Metarhizium anisopliae",
"IdentificationSequence": 0
}
]
},
{
"IdentificationUnitID": 437339,
"HierarchyCache": "Odonata - Calopterygidae - Calopteryginae",
"OrderCache": "Odonata",
"FamilyCache": "Calopterygidae",
"TaxonomicGroup": "insect",
"NumberOfUnits": 22,
"Identification": [
{
"TaxonomicName": "Calopteryx virgo(Linnaeus, 1758)",
"IdentificationSequence": 1
}
],
"IdentificationUnitAnalysis": [
{
"DisplayText": "16S",
"AnalysisNumber": "1",
"AnalysisResult": "TGC",
"SpecimenPartID": 182805
},
{
"DisplayText": "DNA Analysis",
"AnalysisDate": "2024-06-24",
"AnalysisNumber": "1",
"AnalysisResult": "ATTGCAGC",
"ResponsibleName": "Meier, R.",
"IdentificationUnitAnalysisMethod": [
{
"DisplayText": "Ultraschall",
"MethodMarker": "1"
}
]
}
],
"IdentificationUnitGeoAnalysis": [
{
"AnalysisDate": "2024-07-08T14:39:21.300",
"Geography": "POINT (11.50062829 48.16385096)"
},
{
"AnalysisDate": "2024-07-08T14:45:28.007",
"Geography": "POLYGON ((11.5003972353666 48.1634295515789, 11.5005823309247 48.1634194298878, 11.5005903785577 48.1634788947945, 11.5004012591831 48.1635041989893, 11.5003972353666 48.1634295515789))"
}
]
}
]
}
]Cache database
Table [Project].CacheJsonCache
Content of table JsonCache restricted to public available data in the Cache database
| Column | Data type | Description |
|---|---|---|
| ID | int | Unique ID for the Dataset, Primary key |
| URI | varchar (500) | The URL as combination of BaseURL and ID |
| DisplayText | nvarchar (500) | Representation in the interface |
| LogUpdatedWhen | datetime | Date and time when the data were last updated |
| Data | json | Public available data related to the current dataset |
The data are transferred via a stored procedure [Project].procPublishJsonCache where [Project] is the schema corresponding to the Project in the database. Restricted to public information e.g. not locked via DataWithholding, Internal…, etc. and further restrictions as defined in the cache database.
Postgres database
The table CacheJsonCache is a copy of the table in the SQL-Server database with the Data stored in JSONB Format (= binary).