Diversity Gazetteer
Internals
For users that need more detailed information about the software including database design etc.
Subsections of Internals
Diversity Gazetteer
Database
DiversityGazetteer - Database
The diagram of for the main tables of the database is shown below.
The description is available in the databaseobjects and enumeration table chapters .
Subsections of Database
Diversity Gazetteer
Data Access
Access to the data
To get access to the data, you have to take several hurdles. In DiversityGazetteers you must be a member of one of the usergroups. You have only access to those data, that are listed in the projects you have access to.
Diversity Gazetteer
User groups
A user can be in 3 groups with diverse rights in the database where certain higher groups have all rights of lower groups in addition to special rights for this group, e.g. the group DiversityWorkbenchUser can only read the data of certain tables while DiversityWorkbenchEditor has the rights of DiversityWorkbenchUser and additionally can edit the data in certain tables - see overview below.
Summarized overview of the permissions of the groups
| Role | Permissions in addition to lower role resp. user group | Inculded rights |
| DiversityWorkbenchAdministrator | Delete data, edit user permissions | DiversityWorkbenchEditor |
| DiversityWorkbenchEditor | Create new entries and delete details (not whole datasets) | DiversityWorkbenchUser |
| DiversityWorkbenchUser | See the data of the data tables, add annotations |
If you are a DiversityWorkbenchAdministrator you can add a user to one of these Logins.
Any user may have access to several Projects.
Diversity Gazetteer
The following objects are not included:
- Logging tables
- Enumeration tables
- System objects
- Objects marked as obsolete
- Previous versions of objects
Tables, …
TABLES
Table ExternalDatabase
External datasource of the data
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ExternalDatabaseID | int | - | NO | - |
| ExternalDatabaseName | nvarchar (60) | - | NO | - |
| ExternalDatabaseVersion | nvarchar (255) | - | NO | - |
| ExternalAttribute_NameID | nvarchar (255) | Attribute of the extarnal source for mapping the data in table GeoName | YES | - |
| ExternalAttribute_PlaceID | nvarchar (255) | Attribute of the extarnal source for mapping the data in table GeoPlace | YES | - |
| ExternalCoordinatePrecision | nvarchar (255) | Description of the coordinate precision in the external source | YES | - |
| InternalNotes | ntext | Notes concerning the import of the data | YES | - |
Table GeoCache
Cached values for the hierarchy of places
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlaceID | int | Primary key as defined in table GeoPlace | NO | - |
| Country | nvarchar (100) | The cached country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID | YES | - |
| HierarchyPlaceToCountry | nvarchar (2000) | The cached hierarchy from the place to the country derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID | YES | - |
| HierarchyCountryToPlace | nvarchar (2000) | The cached hierarchy from the country to the place derived from GeoName via the Hierarchy defined in GeoPlace via the SuperiorPlaceID | YES | - |
| SuperiorPlaceID | int | Temporary used value for the superior place ID - empty after successful update of the cache values | YES | - |
Table GeoName
The names of the geographical objects
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| NameID | int | The ID of the name, PK | NO | - |
| Name | nvarchar (400) | The name of the place | NO | - |
| PlaceID | int | The ID of the place, refers to table GeoPlace | YES | - |
| LanguageCode | nvarchar (50) | The code of the language | YES | - |
| ExternalDatabaseID | int | The ID of the external database as stored in the table ExternalDatabase (foreign key) | YES | - |
| ExternalNameID | nvarchar (50) | The ID in the place in the foreign (source) database | YES | - |
| Notes | nvarchar (MAX) | Notes about the geographic name | YES | - |
| HierarchyCache | nvarchar (500) | The hierarchy of the place, cached value. | YES | - |
| HierarchyList | nvarchar (255) | A text indicating the hierarchy of the place underneath the place as defined in GeoPlace.SuperiorPlaceID | YES | - |
| LogInsertedBy | nvarchar (50) | Name of user who first entered (typed or imported) the data. | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database. | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of user who last updated the data. | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated. | YES | - |
Table GeoName_log
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| NameID | int | - | YES | - |
| Name | nvarchar (400) | - | YES | - |
| PlaceID | int | - | YES | - |
| LanguageCode | nvarchar (50) | - | YES | - |
| ExternalDatabaseID | int | - | YES | - |
| ExternalNameID | nvarchar (50) | - | YES | - |
| LogInsertedBy | nvarchar (50) | - | YES | - |
| LogInsertedWhen | smalldatetime | - | YES | - |
| LogUpdatedBy | nvarchar (50) | - | YES | - |
| LogUpdatedWhen | smalldatetime | - | YES | - |
| LogState | char (1) | - | YES | - |
| LogDate | datetime | - | NO | - |
| LogUser | nvarchar (50) | - | YES | - |
| LogID | int | - | NO | - |
| Notes | nvarchar (MAX) | - | YES | - |
Table GeoNameLanguage
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| NameID | int | - | NO | - |
| LanguageCode | nvarchar (50) | - | NO | - |
| LogInsertedBy | nvarchar (50) | - | YES | - |
| LogInsertedWhen | smalldatetime | - | YES | - |
| LogUpdatedBy | nvarchar (50) | - | YES | - |
| LogUpdatedWhen | smalldatetime | - | YES | - |
Table GeoPlace
The geographical places
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlaceID | int | ID of the place, PK | NO | - |
| Geography | geography | The geography of the place as WGS84 Koordinates and if available the altitude and the area | YES | - |
| SuperiorPlaceID | int | The ID of the superior place | YES | - |
| CountryPlaceID_Cache | int | The ID of the country a place belongs to | YES | - |
| RegionPlaceID_Cache | int | The ID of a region a place belongs to, e.g. a continent for a country | YES | - |
| ExternalDatabaseID | int | The ID of the external database as stored in the table ExternalDatabase (foreign key) | YES | - |
| ExternalNameID | nvarchar (50) | The ID in the place in the foreign (source) database | YES | - |
| PlaceType | nvarchar (50) | The type of the place, Foreign key, refers to table PlaceType_Enum | YES | - |
| PreferredNameID | int | The NameID of the name preferred for this place. Foreign key refers to table GeoName | YES | - |
| Notes | nvarchar (MAX) | Notes about the geographic place | YES | - |
| LogInsertedBy | nvarchar (50) | Name of user who first entered (typed or imported) the data. | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database. | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of user who last updated the data. | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated. | YES | - |
Table GeoPlace_log
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlaceID | int | - | YES | - |
| Geography | geography | - | YES | - |
| SuperiorPlaceID | int | - | YES | - |
| CountryPlaceID_Cache | int | - | YES | - |
| RegionPlaceID_Cache | int | - | YES | - |
| ExternalDatabaseID | int | - | YES | - |
| ExternalNameID | nvarchar (50) | - | YES | - |
| PlaceType | nvarchar (50) | - | YES | - |
| LogInsertedBy | nvarchar (50) | - | YES | - |
| LogInsertedWhen | smalldatetime | - | YES | - |
| LogUpdatedBy | nvarchar (50) | - | YES | - |
| LogUpdatedWhen | smalldatetime | - | YES | - |
| LogState | char (1) | - | YES | - |
| LogDate | datetime | - | NO | - |
| LogUser | nvarchar (50) | - | YES | - |
| LogID | int | - | NO | - |
| Notes | nvarchar (MAX) | - | YES | - |
Table GeoPlaceType
The types of the geographical places
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| PlaceID | int | ID of the place, part of PK | NO | - |
| PlaceType | nvarchar (50) | Type of the place, part of PK | NO | - |
| LogInsertedBy | nvarchar (50) | Name of user who first entered (typed or imported) the data. | YES | - |
| LogInsertedWhen | smalldatetime | Date and time when the data were first entered (typed or imported) into this database. | YES | - |
| LogUpdatedBy | nvarchar (50) | Name of user who last updated the data. | YES | - |
| LogUpdatedWhen | smalldatetime | Date and time when the data were last updated. | YES | - |
Table GeoProject
The projects the names are linked to
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProjectID | int | The ID of the project, part of PK | NO | - |
| NameID | int | The ID of the name, part of PK | NO | - |
Table LanguageCode_Enum
The codes for the languages
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. | NO | - |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface | YES | - |
| DisplayText | nvarchar (50) | Short abbreviated description of the object, displayed in the user interface | YES | - |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. | YES | - |
| DisplayEnable | bit | Enumerated objects can be hidden from the user interface, if this attribute is set to false (= unchecked check box) | YES | - |
| ParentCode | nvarchar (50) | The code of the superior entry, if a hierarchy within the entries is necessary | YES | Refers to table LanguageCode_Enum |
| InternalNotes | nvarchar (500) | Internal development notes on usage, definition, etc. of an enumerated object | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Table PlaceType_Enum
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| Code | nvarchar (50) | - | NO | - |
| Description | nvarchar (500) | - | YES | - |
| DisplayText | nvarchar (50) | - | YES | - |
| DisplayOrder | smallint | - | YES | - |
| DisplayEnable | bit | - | YES | - |
| ParentCode | nvarchar (50) | - | YES | - |
| InternalNotes | nvarchar (500) | - | YES | - |
| HierarchyPrefixUp | nvarchar (50) | - | YES | - |
| HierarchyPrefixDown | nvarchar (50) | - | YES | - |
| HierarchyPostfixUp | nvarchar (50) | - | YES | - |
| HierarchyPostfixDown | nvarchar (50) | - | YES | - |
Table ProjectProxy
The projects as stored in the module DiversityProjects
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO | - |
| Project | nvarchar (50) | The name or title of the project as shown in a user interface (Projects are defined in DiversityProjects) | YES | - |
| ImageDescriptionTemplate | xml (MAX) | Template for the description of images | YES | - |
| ProjectURI | varchar (255) | The URI of the project, e.g. as provided by the module DiversityProjects. | YES | - |
| CreateArchive | bit | If an archive e.g. by a task schedule should be created | YES | - |
| ArchiveProtocol | nvarchar (MAX) | The protocol created during the last archive | YES | - |
| StableIdentifierBase | varchar (500) | The initial string of the stable identifier for data of this project | YES | - |
| StableIdentifierTypeID | int | The type of the stable identifier for data of this project - evaluated in function StableIdentifier for the creation of stable identifiers | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
| LastChanges | datetime | The recent date when data within the project had been changed | YES | - |
| IsLocked | bit | If the data within the project should not be changeed and the access for all users is restricted to read onlyDefault value: (0) | YES | - |
Table ProjectUser
The projects which a user can access
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LoginName | nvarchar (50) | A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc.. | NO | Refers to table UserProxy |
| ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) | NO | Refers to table ProjectProxy |
| ReadOnly | bit | If the user has only read access to data of this projectDefault value: (0) | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
Depending on:
- ProjectProxy
- UserProxy
trgInsProjectUser
Setting ReadOnly in dependence of locked projects
Table UserProxy
The user as stored in the module DiversityAgents
| Column | Data type | Description | Nullable | Relation |
|---|---|---|---|---|
| LoginName | nvarchar (50) | A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc. | NO | - |
| CombinedNameCache | nvarchar (255) | The short name of the user, e.g. P. SmithDefault value: NULL | NO | - |
| AgentURI | varchar (255) | URI of a agent in the module DiversityAgents | YES | - |
| Queries | xml (MAX) | Queries created by the user | YES | - |
| RowGUID | uniqueidentifier | -Default value: newsequentialid() | NO | - |
| Settings | xml (MAX) | The settings for the user | YES | - |
| CurrentProjectID | int | The current project selected by the user | YES | - |
| ID | int | ID of the user | NO | - |
| PrivacyConsent | bit | If the user consents the storage of his user name in the database | YES | - |
| PrivacyConsentDate | datetime | The time and date when the user consented or refused the storage of his user name in the database | YES | - |
Diversity Gazetteer
Hierarchy
The hierarchy of the places is stored in table GeoPlace via the columns PlaceID and SuperiorPlaceID referring the PlaceID. As the calculation of the hierarchy is slow, the results are cached in the table GeoCache.
To include the continent in the hierarchy string that will be added to the links from other modules choose → 
Hierarchy: include continent from the menu. As next step choose → 
Update DB Hierarchy Cache from the menu to calculate the hierarchy and store it in the cache table. A short video 
shows details.