Vortex Student Database Schema
- DEVOPS
Introduction
The Vortex Student Database is a Mongo database that is used to store student information and their training sessions.The database contains all users, students and instructors, and their organization in classes. Each training session is recorded, results and metrics, in addition to scoring rules and the results. Some additional tables might be present for optional or prototype features.
In this document, we assume the reader is familiar with Mongo database. The document describes the current state of the Vortex Student Database.
Collections
The basic collections contain information about the users, classes and training sessions
- users : contains the information about all users
- classes : contains all the classes and the users in each class
- sessionsV2 : contains each training session information
- metrics : contains all the metrics gathered during the training sessions
Scoring collections are optional and contain the scoring rules for each scenario
- scoring_factory : contains the default scoring rules for specific training scenarios, provided by CM Labs.
- scoring_custom : contains the instructor-created scoring rules for specific training scenarios
Locales collection contains the translated strings to be used during report generation
- locale_xx_YY : contains the translated text for language xx (international language code in lowercase) and for country YY (international country code in uppercase)
Some collections are used only for demo or experimental features and should be ignored.
- bestcontainermove
- learningpath
There is a deprecated collection (session) that can be present in some database, depending what version of simulators is running.
Users and classes
| users collection | ||
|---|---|---|
| Field | Type | Description |
| _id | oid | Unique ID for a user |
| name | string | Login name of the user. With this name, the user will be able to log in the Console. Must be a non-empty string with no whitespace. |
| version | integer | Current version is 620140801. Used for backward compatibility. |
| type | string | Indicates the type of user. The type can be "STUDENT", "INSTRUCTOR" or "ADMIN". No other values are allowed. |
| fullname | string | The full name of the user, that will be shown in the reports. It is usually in the form FirstName LastName. It should not be empty, but there is no error if it is. |
| active | boolean | Indicates if the user is active or not. An inactive user cannot log in, and cannot be added to a training session by the instructor. |
| company | string | An optional string that can be used for the company for this user. |
| customid | string | An optional string that can be used if the school has its own id system. |
| password_hash | string | 256 bits cryptographic key |
| password_salt | string | 256 bits cryptographic key |
| Indexes | Description | |
| _id | Standard Mongo databse index on object ids | |
| company | Index by company | |
| type | Index by user type | |
| name | Index by user name | |
| classes collection | ||
|---|---|---|
| Field | Type | Description |
| _id | oid | Unique ID for a class |
| users_id | array of oid | List of users _id that are in this class |
| version | integer | Current version is 620141210. Used for backward compatibility. |
| name | string | The name of the class. |
| Indexes | Description | |
| _id | Standard Mongo database index on object ids | |
| users_id | Index by the users who are in the class | |
Sessions and Metrics
The sessionV2 collection contains all the informations about the student, the exercise, when, and any event that happened during a single training session.
| sessionsV2 collection | ||||||||
|---|---|---|---|---|---|---|---|---|
| Field | Type | Description | ||||||
| _id | oid | Unique ID for a session | ||||||
| name | string | (unused) Name of the session. Usually empty. | ||||||
| description | string | (unused) Description of the session. Usually empty | ||||||
| version | integer | Current version is 620200107. Used for backward compatibility. | ||||||
| start_time | date | The start date and time of the training session | ||||||
| users_id | array of oid | Users involved in this training session. It usually is 1 student and 1 instructor, it can be only 1 student on some simulators. Can be more students for tandem scenario | ||||||
| elapsed_time | double | The duration of the training session (in seconds) | ||||||
| equipment | array of strings | The training equipment as an array of 2 strings : ["Top Equipment", equipment name] | ||||||
| sub_equipment | array of array of strings | The sub-equipment describes a variation on the main equipment. It is an array of items [ [sub-equipment type, sub-equipment name] ... ] | ||||||
| scene | array of strings | The scene is the name of the training exercise : [exercise list name, exercise name] | ||||||
| bookmarks | array of bookmark documents | Bookmarks are tags that the instructor can enter during the training session. See bookmark document definition | ||||||
| environmental_conditions | array of environmental_condition documents | Records all the initial weather conditions and any changes. See environmental_condition document definition | ||||||
| malfunctions | array of malfunction documents | Malfunctions that the instructor has triggered during the simulation session See malfunction document definition | ||||||
| comments | string | Instructor comment entered at the end of the training session | ||||||
| result | string | Either "Pass", "Fail" or "None" | ||||||
| scoring_rule_set | scoring_rule_set document | Copy of the scoring rules used during the training session. See the scoring_rule_set document definition | ||||||
| video_recording | string | Path to the video recording of the training session. Not available on all simulators. | ||||||
| report_info | report_info document | Contains only one field
| ||||||
| vortex_version | string | The Vortex Toolkit version when the session was run. Used for backward compatibility. | ||||||
| Indexes | Description | |||||||
| _id | Standard Mongo database index on object ids | |||||||
| users_id | Index by users in the session | |||||||
| bookmark document | ||
|---|---|---|
| Field | Type | Description |
| text | string | Annotation entered by the instructor |
| type | integer | Instructor coded type : 0 = Blue flag, 1 = Green flag, 2 = Orange flag, 3 = Red flag |
| id | integer | Sequential id of the bookmark in the session |
| time | double | Session time the bookmark is entered, in seconds |
| environmental_condition document | ||
|---|---|---|
| Field | Type | Description |
| time | double | Session time this environmental condition has been set , in seconds |
| text | string | Description of the condition that changed |
| value | string | The new value |
| malfunction document | ||
|---|---|---|
| Field | Type | Description |
| time | double | Session time this malfunction been triggered, in seconds |
| name | string | Malfunction name |
| action | string | Either of "FAULT_ON", "FAULT_OFF" or "FAULT_VALUE_CHANGED" |
| value | string | Either of "FAULT_ON", "FAULT_OFF" or the actual value as a string |
| dimension | string | Either empty, or a string indicating the dimension. See References |
| scoring_rule_set document | ||
|---|---|---|
| Field | Type | Description |
| version | integer | Current version is 0. Used for backward compatibility. |
| scenario_id | string | Id of the scenario used during this simulation session |
| scoring_rules | array of scoring_rule document | List of rules that defines how the score is calculated See scoring_rule document definition |
score | double | Final score of the trainee in this session |
| scoring_rule document | ||
|---|---|---|
| Field | Type | Description |
| version | double | Current version is 0. Used for backward compatibility. |
| minimum | double | Minimum value for this rule. When the metric's value goes below the minimum, the deduction is applied. |
| maximum | double | Maximum value for this rule. When the metric's value goes above the maximum, the deduction is applied. |
| deduction | double | Number of points deducted per trigger |
| metric_unique_name | string | A unique name identifying the metric associated to this Scoring Rule |
| trigger_count | integer | Number of times this rule was triggered |
| trigger_timestamp | double | Session time when this rule was triggered the last time |
The metrics collection contains all the metrics for all training sessions in the database. Each metric contains the oid of the session it belongs to. Those metrics are used for report generation.
| metrics collection | ||
|---|---|---|
| Field | Type | Description |
| _id | oid | Unique ID for this metric |
| name | string | Name of the metric |
| value | double | For non-vector3 type metric : Value of the metric |
| valueX | double | For vector3 type metric : the value is (valueX, valueY, valueZ) |
| valueY | double | |
| valueZ | double | |
| session | oid | Unique id of the training session when the metric was recorded |
| unit | string | (obsolete) Contained the unit symbol for the metric. Replaced by dimension and hint. |
| dimension | integer | Value indicating the dimension of the value (time, speed, etc..). See the list of Dimension values in the References |
| hint | integer | Value indicating how the dimension should be displayed See the list of Hint values in the References |
| version | integer | Current version is 620200107. Used for backward compatibility. |
| Indexes | Description | |
| _id | Standard Mongo database index on object ids | |
| session | Index by the session from which they come | |
| name | Index by the metric name | |
| session and time | Index by the session and the time, so they are retrieved in the order they were saved | |
Scoring rules : factory and custom
Scoring_factory collection contains the default scoring rules for each scenario, as designed by CM-Labs. Those are hard coded but will be updated automatically when a new version of the Training product is installed. The scoring_custom collection contains the rules that an instructor has created or modified on their own system. Those are local to a system. Both collections have exactly the same structure and data, they only differ in the way they are used.
The structure of those collections is very similar to the scoring rule set document in the session.
| scoring_factory and scoring_custom collections | ||
|---|---|---|
| Field | Type | Description |
| _id | oid | Unique id for this scoring_factory |
| version | integer | Current version is 0. Used for backward compatibility. |
| scenario_id | string | id of the scenario used during this simulation session |
| scoring_rules | array of scoring_rule document | List of rules that defines how the score is calculated See scoring_rule document definition |
asset_version | string | The Vortex Toolkit version when those scoring rules were saved. Used for backward compatibility. |
| Indexes | Description | |
| _id | Standard Mongo database index on object ids | |
| scenario_id | Index by the scenario for which they are used | |
| scoring_rule document | ||
|---|---|---|
| Field | Type | Description |
| version | double | Current version is 0. Used for backward compatibility. |
| minimum | double | Minimum value for this rule. When the metric's value goes below the minimum, the deduction is applied. |
| maximum | double | Maximum value for this rule. When the metric's value goes above the maximum, the deduction is applied. |
| deduction | double | Number of points deducted per trigger |
| metric | string | Base-64 serialization of the metrics information |
Locales collections
The various locales collections that may or may not be present contain the translated strings that are used when generating reports. Each locale table has the same structure and contains the strings for one particular language. The specific language can be retrieved in the name of the collection : locale_xx_YY : contains the translated text for language xx (international language code in lowercase) and for country YY (international country code in uppercase).
| locale_xx_YY collection | ||
|---|---|---|
| Field | Type | Description |
| _id | oid | Unique id for this translation |
| context | string | Context for this string (allows disambiguation between same string in different contexts) |
| source | string | Source in the original language (usually English), but can also be a translation key. |
| translation | string | Translated text in the target language |
| Indexes | Description | |
| _id | Standard Mongo database index on object ids | |
| context and source | Index by the the context and the source string | |
| source and translation | Index by the the source and translation | |
Obsolete sessions collection
The sessions collection is obsolete and new sessions are always added to the sessionsV2 collection. However, this collection may be present in the database of a client that has older simulators. It may even be active, as older simulators will write their sessions to this collection. All the sessions this this obsolete collection are shown in the Console and reports can be generated from them.
The structure of the sessions collection is very similar to the sessionsV2 collection
References
| Dimension : describe the type of value | |
|---|---|
| value | Description |
| 0 | None (dimensionless) |
| 1 | Length |
| 2 | Volume |
| 3 | Mass |
| 4 | Time |
| 5 | Angle |
| 6 | Linear speed |
| 7 | Linear acceleration |
| 8 | Angular speed |
| 9 | Angular acceleration |
| 10 | Force |
| 11 | Torque |
| 12 | Energy |
| 13 | Work |
| 14 | Power |
| 15 | Moment inertia |
| 16 | Spring constant linear |
| 17 | Spring damping linear |
| 18 | Unit Length Spring constant linear |
| 19 | Unit Length Spring damping linear |
| 20 | Kinetic loss |
| 21 | Percentage % |
| 22 | Normalized |
| 23 | Spring constant angular |
| 24 | Spring damping angular |
| 25 | Unit Length Spring constant angular |
| 26 | Unit Length Spring damping angular |
| 27 | Damping linear velocity |
| 28 | Damping angular velocity |
| 29 | Density |
| 30 | Linear Density |
| 31 | Momentum |
| 32 | Count |
| 33 | Transformation matrix without scale |
| 34 | Transformation matrix |
| 35 | Pressure |
| 36 | Scale |
| 37 | Reciprocal of a linear spring constant |
| 38 | Reciprocal of a angular spring constant |
| 39 | Frequency |
| 40 | Surface density |
| 41 | Angular kinetic loss |
| 42 | Area |
| 43 | Volume flow rate |
| 44 | Liquid volume |
| 45 | Liquid volume rate |
| 46 | Rotation rate |
| 47 | Bucket efficiency with respect to fuel consumption |
| 48 | Bucket efficiency over time |
| 49 | Blade efficiency |
| 50 | Temperature |
| Hint : modify how the dimension will be displayed | |
|---|---|
| Value | Description |
| 0 | Standard |
| 1 | Speed km/h (instead of m/s) |
| 2 | Mass tonne (instead of kg) |
| 3 | Power kW (instead of W) |
| 4 | Pressure kPa (instead of Pa) |
| 5 | Bucket efficiency tonne/L (instead of kg/L) |
| 6 | Bucket efficiency tonne/h (instead of kg/s) |
| 7 | Speed m/min (instead of m/s) |
| 8 | Blade efficiency kg*km/L (instead of kg*m/L) |
| 9 | Blade efficiency tonne*km/L (instead of kg*m/L) |
| 10 | Length km (instead of m) |
| 11 | Length mm (instead of m) |
| 12 | Pressure bar (instead of Pa) |