Data dictionary for Workbench Schema - IntegratedBreedingPlatform/Documentation GitHub Wiki

Introduction

Database of the Workbench (portal) application. Key modules are: programs, user, menu system, migration and related configuration.

User Management

persons

Refer to Persons table description in ibdbv2 schema for extra information.

Column Name	Description	Type	Length (bytes)
personid	Unique Identifier for a person and the Primary Key for the table	INT	11
fname	First Name	VARCHAR	20
lname	Last Name	VARCHAR	50
ioname	Initials Or Other Names	VARCHAR	15
institid	Foreign Key to institut.id	INT	11
ptitle	Title of the Person	VARCHAR	25
poname	Person's name in another language	VARCHAR	50
plangu	Language of the other name - links to the udflds table	INT	11
pphone	Person's phone number in text	VARCHAR	20
pextent	Person's extension number	VARCHAR	20
pfax	Person's FAX number	VARCHAR	20
pemail	Person's email address	VARCHAR	40
prole	Person's role (coded)	INT	11
sperson	Person's start date	INT	11
eperson	Person's date of departure	INT	11
pstatus	Person's status - links to udflds	INT	11
pnotes	Notes	VARCHAR	255
contact	Contact information for the Person	VARCHAR	255

users

Please also refer to [users table ibdbv2] (https://github.com/IntegratedBreedingPlatform/Documentation/blob/master/db_docs/8a%20%20Administration%20-%20Users%20and%20Access%20-%20Overview%20and%20Schema%205-4.pdf).

Column Name	Description	Type	Length (bytes)
adate	Date of creation of the record YYYYMMDD	INT	11
cdate	Date the userID was closed YYYYMMDD	INT	11
instalid	Number of the ICIS installation to which the user belongs. Zero if the user has access to all local dbs	INT	11
personid	Foreign Key to the persons .id column - 1:1 mapping	INT	11
uaccess	User Access (coded, please see original docs)	INT	11
uname	username : used to login	VARCHAR	30
upswd	password : BCrypt encrypted	VARCHAR	60
userid	Primary Key and Unique identifier	INT	11
ustatus	User Status - Unassigned = 0	INT	11
utype	User Type (coded, please see original docs)	INT	11
multi_factor_authentication_enabled	Enable two-factor authentication for this user (Default value is 0 (False))	TINYINT	4

role_type

Stores configured role types with three basic levels: INSTANCE, CROP, PROGRAM

Column Name	Description	Type	Length (bytes)
role_type_id	Primary Key : internal use only	INT	11
name	Name of role	TEXT	100

role

Stores configured user roles in the system

Column Name	Description	Type	Length (bytes)
id	Primary Key : internal use only	INT	11
name	Name of role	TEXT	100
description	Description of role	TEXT	255
role_type_id	foreign key to role_type.id	INT	11
active	Flag whether role is active	TINYINT	1
editable	Flag whether role is editable	TINYINT	1
assignable	Flag whether role is assignable	TINYINT	1
created_by	User who created role	INT	11
created_date	Timestamp when role was created	TIMESTAMP
updated_by	User who last updated role	INT	11
updated_date	Timestamp when role was last updated	TIMESTAMP

users_roles

Mapping of role(s) assigned to users

Column Name	Description	Type	Length (bytes)
id	Primary Key : internal use only	INT	11
userid	foreign key to users.id to represent a user in the system	INT	11
role_id	foreign key to role.id	INT	11
crop_name	name of crop for which role applies to	TEXT	32
workbench_project_id	ID of program for which role applies to	INT	11
created_by	User who created user role configuration	INT	11
created_date	Timestamp when user role was created	TIMESTAMP

permission

Stores permissions (or user function) that a role or role type can be given access to

Column Name	Description	Type	Length (bytes)
permission_id	Primary Key : internal use only	INT	11
name	Name of permission	TEXT	255
description	Description of permission	TEXT	255
parent_id	Parent Permission record	INT	11
workbench_sidebar_category_link_id	Foreign key to workbench_sidebar_category_link_id	INT	11

role_permission

Stores permissions configured for a role

Column Name	Description	Type	Length (bytes)
role_id	foreign key to role table	INT	11
permission_id	foreign key to permission table	INT	11

role_type_permission

Stores permissions configured for a role

Column Name	Description	Type	Length (bytes)
role_type_id	foreign key to role_type table	INT	11
permission_id	foreign key to permission table	INT	11
selectable	flag whether permission is selectable for role type	TINYINT	1

Configuration

schema_version

Schema version, a timestamp string representation, used by the installer for upgrades. Only ever contains a single record

Column Name	Description	Type	Length (bytes)
version	A string representation of a date that represents the date at which the schema was stable. Currently set to '20150131' which is the date at which the local schemas were merged.	VARCHAR	32

standard_preset

Jasper report options for the Fieldbook

Column Name	Description	Type	Length (bytes)
standard_preset_id	primary key for the table - only used internally	INT	11
tool_id	The primary key for the workbench_tool record describing the BMS Application (Fieldbook, Crossing Manager, Study Browser etc.) that is configured with these presets (currently Fieldbook is the only application that is set)	INT	10
tool_section	A string representation of the constant in the code referred to to access the report e.g. FB_LBL_PRINT_CUSTOM_REPORT	VARCHAR	255
crop_name	A string representation of the crop stored in workbench_crop table	VARCHAR	255
name	A screen printable name for the report	VARCHAR	255
configuration	XML text that captures the report parameters	TEXT

template_setting

Storage house for all settings that we save during screen parameter generation in some parts of the BMS (Crossing Manager)

Column Name	Description	Type	Length (bytes)
template_setting_id	Primary Key : internal use only	INT	10
project_id	Link to primary key of the project table - project.id - which represents the Program	INT	11
tool_id	Link to workbench_tool.id : the tool for which settings are being saved	INT	10
name	The user specified name under which to serve and then reload the settings	VARCHAR	75
configuration	XML representation of the settings	TEXT
is_default	boolean option to select as default for this tool (0=false, 1=true)	TINYINT	1

workbench_crop

Catalogue for the Crop Databases available for this installation

Column Name	Description	Type	Length (bytes)
crop_name	Free text describing a crop - these names appear in the Create Program Crop Selection dropdown	VARCHAR	32
db_name	Database schema name storing the crop data	VARCHAR	64
schema_version	Same as the BMS product version	VARCHAR	32
plot_code_prefix	Contains prefix string that will be used for custom observation unit IDs	VARCHAR	4
use_uuid	Indicates if program studies should use UUID or custom observatiion unit IDs	TINYINT	1

Breeding Program

workbench_project

Program storage for the BMS. Each record links to program records in an individual Crop Database Schema. This table is misnamed and needs to be more correctly named 'workbench_program'.

Column Name	Description	Type	Length (bytes)
project_id	Primary Key : internal use only	INT	10
user_id	Foreign Key to the user.id table to denote creator and owner of the Program	INT	10
project_name	Name of the program, will appear on the dashboard	VARCHAR	255
start_date	The creation date of the program	DATE
project_uuid	The unique Business Key used to identify the Program in the crop database. This key is found in ibdbv2_crop_merged.project.program_uuid ibdbv2_crop_merged.listnms.program_uuid	VARCHAR	36
crop_type	the string representation of the crop, as outlined in workbench_crop.crop_name	VARCHAR	32
last_open_date	the last date that this program was opened by anyone (used for program activity)	TIMESTAMP

workbench_project_user_info

Links users as members of a Program and tracks last open date of a Program, per User.

Column Name	Description	Type	Length (bytes)
last_open_date	last time the program was opened (this is a duplicate of the workbench_project data)	TIMESTAMP
project_id	Foreign Key to the workbench_project.id table.column (stores Programs)	INT	10
user_id	Foriegn Key to the workbench user.id column	INT	11
user_info_id	Primary Key : internal use only	INT	10

workbench_user_info

Tracks user login count and token reset details

Column Name	Description	Type	Length (bytes)
user_id	Primary Key : internal use only	INT	11
login_count	Number of times unsuccessfully logged in	INT	11
reset_token	Token recorded to denote permission to change the password	VARCHAR	255
reset_expiry_date	Datetime at which he alarm switches off.	DATETIME

workbench_project_activity

Stores activity records for the Program

Column Name	Description	Type	Length (bytes)
project_activity_id	Primary Key : internal use only	INT	11
project_id	A Foreign Key to the Primary Key of the workbench_project table	INT	11
name	Name of the activity	TEXT	128
description	Description of program activity	TEXT
user_id	ID of user performing program activity	INT	11
date	Timestamp when record was created	TIMESTAMP

Menu System

workbench_sidebar_category

Stores the categories appearing in the BMS side menu.

Column Name	Description	Type	Length (bytes)
sidebar_category_id	Primary Key : internal use only	INT	11
sidebar_category_label	A Sentence Case representation of the label that appears on the screen. Currently transformed to all capitals in the menu bar	VARCHAR	255
sidebar_category_name	Internal string based key to represent the menu-group item	VARCHAR	255

workbench_sidebar_category_link

Stores links that reside in each category in the side menu for the BMS

Column Name	Description	Type	Length (bytes)
sidebar_category_link_id	Primary Key : internal use only	INT	11
tool_name	string based key to represent the tool displayed in the menu-group	VARCHAR	128
sidebar_category_id	FK to the Workbench_sidebar_category.id record, grouping this record under that major menu heading	INT	11
sidebar_link_name	A string based key for the name	VARCHAR	255
sidebar_link_title	String based term to be displayed on screen	VARCHAR	255

workbench_tool

Tools that operate within the workbench portal application view.

Column Name	Description	Type	Length (bytes)
tool_id	Primary Key : internal use only	INT	10
name	String based key representation of the tool	VARCHAR	128
group_name	string_underscore fomatted key for identification of this record	VARCHAR	128
title	Sentence case based representation that appears in the menu	VARCHAR	255
version	Maven based build/release version	VARCHAR	16
tool_type	Describes the type of technology the tool is built in	ENUM('WEB','WEB_WITH_LOGIN','NATIVE','WORKBENCH','ADMIN')
path	URL to the tool for opening	TEXT
parameter	parameters for the tool - currently unused by presents	VARCHAR	255
user_tool	Unsure, all set to zero at present	TINYINT	1

OpenCPU Integration

Stores R endpoints and parameters required for OpenCPU integration.

r_package

Contains the URL endpoint an R call can use to execute an R function

Column Name	Description	Type	Length (bytes)
package_id	Primary Key : internal use only	INT	11
description	Description of R package	VARCHAR	255
endpoint	URL of R Package endpoint	VARCHAR	500

r_call

Contains R call records that describe what the R call does.

Column Name	Description	Type	Length (bytes)
call_id	Primary Key : internal use only	INT	11
description	Description of R call	VARCHAR	255
package_id	foreign key to r_package table	INT	11

r_call_parameter

Contains parameters associated with an R call record.

Column Name	Description	Type	Length (bytes)
call_parameter_id	Primary Key : internal use only	INT	11
parameter_key	parameter of an R package	VARCHAR	13
value	value of the parameter	VARCHAR	500
call_id	foreign key to r_call table	INT	11

Release Notes

Tables for displaying release notes per user

release_note

Column Name	Description	Type	Length (bytes)
release_note_id	Primary key	Integer	4
version	Unique Key	Text	255
release_date	Release Date	Time Stamp	4
has_coming_soon		Tinyint	1
enabled		Tinyint	1
file_name	File Name	Text	255

release_note_user

Column Name	Description	Type	Length (bytes)
release_note_id	Foreign key to release_note table	Integer	4
user_id	Foreign key to user table	Integer	4
view_date		Time Stamp	4
show_again		Tinyint	1

Security

Tables for storing One-Time Password and user device meta data.

one_time_password

Column Name	Description	Type	Length (bytes)
id	Primary key	Integer	11
otp_code	The one-time password verification code	Integer	11
expires	The date and time the one-time password verification code expires	Time Stamp

user_device_meta_data

Column Name	Description	Type	Length (bytes)
id	Primary key	Integer	11
userid	Foreign key to the users table	Integer	11
device_details	The browser/client device meta data (the browser's User-Agent)	VARCHAR	500
location	The client's IP Address	VARCHAR	300
last_logged_in	The last date/time the user logged in to the device	Time Stamp