Table of Contents
This documentation provides information about Opinio 6.0 database structure and description of tables, indexes and foreign keys.
Opinio database is a relational database that stores information about survey, respondent, reports and all other Opinio data. The database is total contains 101 tables.
We tried to keep Opinio databases as simple as possible to give the Opinio user a wide selection of supported databases. Opinio database doesn't rely on stored procedures, triggers, functions and other database programming features because a lot of databases don't support them.
Several sql scripts to create database are included in the Opinio distribution, one for each supported database. By supported database we mean a database that has been tested by Opinio developers and added to automatic upgrade inbuilt in Opinio.
History of database support
| Opinio version | Database supported |
|---|---|
| Opinio 4.0 |
|
| Opinio 4.1 - 5.2.11 |
|
| Opinio 6.0 |
|
In this document we use MySQL data types to describe table columns. Column types may vary from database to database.
18. December 2007
General information about the system.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SystemInfoId | BIGINT | NOT NULL | PK | Unique id |
| Language | VARCHAR(100) | NULL | Default language | |
| MainMailserver | VARCHAR(100) | NULL | Main mail server to use when sending email | |
| SecondMailserver | VARCHAR(100) | NULL | The SMTP email server to use if the main server is down | |
| Cache_size | INTEGER | NULL | Survey cache size | |
| LicenseCode | VARCHAR(100) | NULL | The license code controls whether Opinio runs in Demo or full mode. |
One record represents one system attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SystemAttributeId | BIGINT | NOT NULL | PK | Unique id |
| AttributeName | VARCHAR(30) | NOT NULL | Short name of the system attribute | |
| StringValue | VARCHAR(255) | NULL | String value of the attribute | |
| LongValue | BIGINT | NULL | Long value of the attribute | |
| BigTextValue | TEXT | NULL | Big text value of the attribute | |
| FloatValue | DOUBLE | NULL | Double value of the attribute | |
| SystemInfoId | BIGINT | NOT NULL | FK | References OPS_SystemInfo table |
Defines a user.
One record represents one user attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| UserAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the user attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| UserId | BIGINT | Not null | FK | References OPS_User table |
Defines a group of Opinio users.
Each record maps one user to one user group. One user can be a member of unlimited number of user groups.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| UserGroupMemberId | BIGINT | Not null | PK | Unique id |
| UserId | BIGINT | Not null | FK | References OPS_User table |
| UserGroupId | BIGINT | Not null | FK | References OPS_UserGroup table |
Represents a set of permissions for a resource. Resource are reusable items, such as drop down lists, headers/footers and survey objects, such as survey group(folder), survey, report and so on. One record exists for one resource.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ResourcePermissionId | BIGINT | Not null | PK | Unique id |
| ResourceType | INTEGER | Null | Type of the resource:
|
|
| ResourceId | BIGINT | Null | Id of the resource. Example: if resource type is survey, then ResourceId is survey id. | |
| PermissionList | VARCHAR(255) | Null |
Permissions to the resource. Stored as a string with access types separated by comma and enclosed in braces.
Example: {1, 2, 7}
|
|
| UserId | BIGINT | Null | FK | References OPS_User table |
Defines survey group.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyGroupId | BIGINT | Not null | PK | Unique id |
| GroupTitle | VARCHAR(100) | Null | Survey group name | |
| ParentGroupId | BIGINT | Null | Id of the parent survey group | |
| Description | VARCHAR(255) | Null | Survey group description | |
| SortBy | INTEGER | Null | Sort by value
|
|
| CreatedDate | BIGINT | Null | Created date | |
| UpdatedDate | BIGINT | Null | Last updated date |
Defines a survey. This table contains necessary information about a survey. All general information is defined in OPS_SurveyAttribute table.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyId | BIGINT | Not null | PK | Unique id |
| SurveyName | VARCHAR(100) | Not null | Name of the survey (used in the administration module) | |
| CreatedDate | BIGINT | Null | Date created | |
| CreatedBy | BIGINT | Null | User id who created the survey. | |
| SurveyGroupId | BIGINT | Null | FK | References OPS_SurveyGroup table |
| DeleteStatus | INTEGER | Null | Deleted status (not is use) | |
| ContentType | INTEGER | Null | Survey content type:
|
|
| ParentId | BIGINT | Null | References OPS_Survey.SurveyId. Is NULL if not a child survey. | |
| LastEditedBy | BIGINT | Null | Id of the last user who has updated the survey |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK16 | OPS_Invitation.SurveyId | SurveyId |
| OPS_FK19 | OPS_Question.SurveyId | SurveyId |
| OPS_FK34 | OPS_Report.SurveyId | SurveyId |
| OPS_FK38 | OPS_Respondent.SurveyId | SurveyId |
| OPS_FK71 | SurveyGroupId | OPS_SurveyGroup.SurveyGroupId |
| OPS_FK72 | OPS_SurveyAttribute.SurveyId | SurveyId |
| OPS_FK73 | OPS_SurveyPage.surveyId | SurveyId |
| OPS_FK87 | OPS_SurveyPageAttribute.SurveyId | SurveyId |
| OPS_FK105 | OPS_PanelSignUpSurvey.SurveyId | SurveyId |
| OPS_FK102 | OPS_SurveyPiping.SurveyId | SurveyId |
| OPS_FK94 | OPS_Survey.ParentId | SurveyId |
| OPS_FK95 | OPS_SurveyComment.SurveyId | SurveyId |
One record represents one survey attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
Represents a page with questions (survey section).
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyPageId | BIGINT | Not null | PK | Unique id |
| FromQuestion | INTEGER | Null | Number of the first question on the page | |
| ToQuestion | INTEGER | Null | Number of the last question on the page | |
| PageNo | INTEGER | Null | Sequential position of the page. Starts at 0. | |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
One record represents one survey page (section) attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyPageAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| SurveyPageId | BIGINT | Not null | FK | References OPS_SurveyPage table |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
Stores survey comments
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyCommentId | BIGINT | Not null | PK | Unique id |
| SurveyId | BIGINT | Not null | References OPS_SurveyId.SurveyId | |
| CommentText | TEXT | Null | The survey comment | |
| CommentBy | VARCHAR(50) | Null | Who added the comment | |
| EntryDate | BIGINT | Null | When the comment was added |
Stores survey piping elements info
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SurveyPipingId | BIGINT | Not null | PK | Unique id |
| PipingName | VARCHAR(255) | Not Null | The name of piping element | |
| PipingType | INTEGER | Null | The piping type
|
|
| PipingSourceType | INTEGER | Null | The piping source type
|
|
| QuestionId | BIGINT | Null | Question id (set if piping source type is Question response) | |
| QuestionSourceType | INTEGER | Null | Since one question can have several inputs fields, this columns specifies which of the input is used for piping.
|
|
| IntextName | VARCHAR(255) | Null | The name of in-text element used for piping. | |
| MatrixCellCol | INTEGER | Null | Column of the matrix cell used for piping | |
| MatrixCellRow | INTEGER | Null | Row of the matrix cell used for piping | |
| URLParamName | VARCHAR(255) | Null | The name of url parameter used for piping (parameter name without "opdata_"). | |
| InviteeDataType | INTEGER | Null | Type of the invitee data used for piping:
|
|
| InviteeDataName | VARCHAR(255) | Null | The name of invitee attribute used for piping. | |
| FixedValue | VARCHAR(255) | Null | Piping value for fixed value piping type | |
| MultipleValuesDelim | VARCHAR(255) | Null | Delimiter for multiple piping values | |
| SurveyId | BIGINT | Not null | References OPS_SurveyId.SurveyId |
One question in a survey.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionId | BIGINT | Not null | PK | Unique id |
| QuestionNo | INTEGER | Null | The question number | |
| DisplayNo | VARCHAR(20) | Null | User defined number to display instead of default question number | |
| QuestionText | TEXT | Null | Question text | |
| QuestionType | INTEGER | Null | Type of the question, defines what kind of the question it is.
|
|
| TextBefore | TEXT | Null | Text before the question | |
| TextAfter | TEXT | Null | Text after the question | |
| QuestionLayout | VARCHAR(10) | Null | Layout of the question. Values:
|
|
| RelationKey | VARCHAR(10) | Null | Relation key | |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK19 | SurveyId | OPS_Survey.SurveyId |
| OPS_FK20 | OPS_QuestionAttribute.QuestionId | QuestionId |
| OPS_FK22 | OPS_QuestionDropdown.QuestionId | QuestionId |
| OPS_FK23 | OPS_QuestionFreeText.QuestionId | QuestionId |
| OPS_FK24 | OPS_QuestionMatrix.QuestionId | QuestionId |
| OPS_FK28 | OPS_QuestionMultiple.QuestionId | QuestionId |
| OPS_FK30 | OPS_QuestionNumeric.QuestionId | QuestionId |
| OPS_FK31 | OPS_QuestionRating.QuestionId | QuestionId |
| OPS_FK33 | OPS_QuestionTagField.QuestionId | QuestionId |
| OPS_FK39 | OPS_ResponseDropdown.QuestionId | QuestionId |
| OPS_FK41 | OPS_ResponseEssay.QuestionId | QuestionId |
| OPS_FK43 | OPS_ResponseEssayNum.QuestionId | QuestionId |
| OPS_FK46 | OPS_ResponseEssayText.QuestionId | QuestionId |
| OPS_FK49 | OPS_ResponseFreeText.QuestionId | QuestionId |
| OPS_FK51 | OPS_ResponseMatrix.QuestionId | QuestionId |
| OPS_FK53 | OPS_ResponseMatrixNum.QuestionId | QuestionId |
| OPS_FK56 | OPS_ResponseMatrixText.QuestionId | QuestionId |
| OPS_FK59 | OPS_ResponseMultipleOption.QuestionId | QuestionId |
| OPS_FK61 | OPS_ResponseMultipleOptionNum.QuestionId | QuestionId |
| OPS_FK64 | OPS_ResponseMultipleOptionText.QuestionId | QuestionId |
| OPS_FK67 | OPS_ResponseNumeric.QuestionId | QuestionId |
| OPS_FK69 | OPS_ResponseRating.QuestionId | QuestionId |
| OPS_FK77 | OPS_Validator.QuestionId | QuestionId |
| OPS_FK79 | OPS_QuestionCondition.QuestionId | QuestionId |
One record for one question attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short attribute name | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
One record defines one essay field.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionTagFieldId | BIGINT | Not null | PK | Unique id |
| TagFieldType | INTEGER | Not null | Essay field type
|
|
| TagId | VARCHAR(255) | Not null | Identification of the essay field inside one question. | |
| FieldSize | INTEGER | Null | Size of the essay field. Applicable for text and numeric fields. | |
| DropdownId | BIGINT | Null | FK | References OPS_Dropdown. Applicable for dropdown field type. |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
Defines a rating/scale question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionRatingId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table. |
| MinText | VARCHAR(100) | Null | Label for minimum rating | |
| MaxText | VARCHAR(100) | Null | Label for maximum rating | |
| RatingLevel | INTEGER | Null | Number of levels in a rating question | |
| ShowNA | VARCHAR(5) | Null | True if N/A option will is on. Values:
|
|
| NALabel | VARCHAR(50) | Null | Label for N/A option |
Defines the type of a multiple-choice question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionMultipleId | BIGINT | Not null | PK | Unique id |
| MultipleOn | VARCHAR(5) | Not null | Flag determining the multiple choice type:
|
|
| ColumnCount | INTEGER | Not null | Number of display columns (to ease display if number of choices are numerous) | |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
One option in a multiple-choice question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| OptionText | VARCHAR(255) | Null | The text of the multiple choice option | |
| OptionIndex | INTEGER | Not null | Position of the option. 0 is the first multiple choice option, 1 is the second, etc. | |
| Image | VARCHAR(255) | Null | Defines an image in a multiple choice option | |
| Layout | INTEGER | Null | Layout for the option
|
|
| ImageLayout | INTEGER | Null | Image layout
|
|
| QuestionMultipleId | BIGINT | Not null | FK | References OPS_QuestionMultiple table |
Defines a numeric question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionNumericId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| NumericType | INTEGER | Null | Defines type of number
|
|
| PrefixLabel | VARCHAR(255) | Null | Label before the field | |
| PostfixLabel | VARCHAR(255) | Null | Label after the field | |
| FieldSize | INTEGER | Null | Size of the field |
Defines matrix question type.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionMatrixId | BIGINT | Not null | PK | Unique id |
| Column_Count | INTEGER | Not null | Number of columns in the matrix | |
| Row_Count | INTEGER | Not null | Number for rows in the matrix | |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK4 | OPS_BranchMatrix.QuestionMatrixId | QuestionMatrixId |
| OPS_FK6 | OPS_BranchMatrixOption.QuestionMatrixId | QuestionMatrixId |
| OPS_FK24 | QuestionId | OPS_Question.QuestionId |
| OPS_FK26 | OPS_QuestionMatrixCell.QuestionMatrixId | QuestionMatrixId |
| OPS_FK27 | OPS_QuestionMatrixGroup.QuestionMatrixId | QuestionMatrixId |
One record represents one matrix group (a group of matrix cells).
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionMatrixGroupId | BIGINT | Not null | PK | Unique id |
| GroupName | VARCHAR | Null | Name of the cell group | |
| Heading | VARCHAR(255) | Null | Group heading | |
| GroupType | INTEGER | Null | Group type tells what kind of cells the group contains
|
|
| BackgroundColour | VARCHAR(20) | Null | Background colour for this cell group | |
| FromColumn | INTEGER | Not null | Start column for the cell group | |
| ToColumn | INTEGER | Not null | End column for the cell group | |
| FromRow | INTEGER | Not null | Start row for the cell group | |
| ToRow | INTEGER | Not null | End row for the cell group | |
| QuestionMatrixId | BIGINT | Not null | FK | References OPS_QuestionMatrix table |
One record represents one cell in a matrix.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionMatrixCellId | BIGINT | Not null | PK | Unique id |
| MatrixCellType | INTEGER | Not null |
Defines type of the matrix cell.
|
|
| ColumnPosition | INTEGER | Not null | Column position of the cell | |
| RowPosition | INTEGER | Not null | Row position of the cell | |
| Label | VARCHAR(255) | Null | Label for a label cell | |
| DropdownId | BIGINT | Null | FK | References OPS_Dropdown. |
| FieldSize | INTEGER | Null | Field size for text and numeric cell types | |
| BackgroundColour | VARCHAR(20) | Null | Background colour for this cell | |
| TextColour | VARCHAR(20) | Null | Text colour for a label cell | |
| CellName | VARCHAR(255) | Null | Optional cell name, used in reports | |
| QuestionMatrixId | BIGINT | Not null | FK | References OPS_QuestionMatrix table |
Free-text input for one question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionFreeTextId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| Row_Count | INTEGER | Null | Number of rows for the input text box | |
| Column_Count | INTEGER | Null | Number of columns for the input text box | |
| Required | VARCHAR(5) | Null | True if required input (not in use) | |
| Label | VARCHAR(255) | Null | Text box label | |
| MaxLength | INTEGER | Null | Maximum length of user input (not in use) |
Defines a dropdown question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionDropdownId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| DropdownId | BIGINT | Not null | FK | References OPS_Dropdown table. Id of the dropdown to use in this question. |
| DropdownMultipleOn | VARCHAR(5) | Null | Multiple selection on/off. Values:
|
|
| DropdownSize | INTEGER | Null | The size of the dropdown box |
Stores question comments
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionCommentId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | References OPS_Question.QuestionId | |
| CommentText | TEXT | Null | The question comment | |
| CommentBy | VARCHAR(50) | Null | Who added the comment | |
| EntryDate | BIGINT | Null | When the comment was added |
One record represents one dropdown. Dropdowns stored in this table are dropdowns for dropdown question, essay field, matrix cell and reusable resources.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| DropdownId | BIGINT | Not null | PK | Unique id |
| DropdownName | VARCHAR(255) | Null | Name of dropdown list (applicable for reusable dropdowns) | |
| DropdownLabel | VARCHAR(255) | Null | Dropdown label. This will be the default display of the dropdown, but the dropdown will have no value if this is selected. | |
| MultipleOn | VARCHAR(5) | Null | Multiple selection. Values:
|
|
| DropdownSize | INTEGER | Null | Size of the dropdown box | |
| SortOn | VARCHAR(5) | Null | Sorting of dropdown items. Values:
|
|
| QuestionId | VARCHAR(5) | Null | If the dropdown belongs to a question, QuestionId is set. If the dropdown is in the reusable resources (dropdown bank), the QuestionId is NULL or 0. | |
| DropdownType | int | Null |
Type of the dropdown:
|
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK14 | OPS_DropdownItem.DropdownId | DropdownId |
| OPS_FK21 | OPS_QuestionDropdown.DropdownId | DropdownId |
| OPS_FK25 | OPS_QuestionMatrixCell.DropdownId | DropdownId |
| OPS_FK32 | OPS_QuestionTagField.DropdownId | DropdownId |
One entry in a dropdown list.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ItemName | VARCHAR(255) | Not null | Name of the dropdown list entry (display label) | |
| ItemValue | VARCHAR(255) | Null | Value of the dropdown list entry | |
| SortValue | INTEGER | Null | Contains sequential numbers used for sorting. When selecting, items are sorted by SortValue, then ItemName | |
| DropdownId | BIGINT | Not null | FK | References OPS_Dropdown table |
Validator for a question. A validator can validate a simple text or numeric field, or a whole question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ValidatorId | BIGINT | Not null | PK | Unique id |
| ValidatorType | INTEGER | Not null | Validator type defines what kind of validation will be done.
Varies for different kinds of target types:
|
|
| TargetType | INTEGER | Not null | Target type defines which field / question type this validator will validate.
|
|
| TargetIndex1 | INTEGER | Null | Index 1 for target. Can be index for multiple option in multiple question, or index for essay field in essay, or column for the first cell to validate in matrix question. | |
| TargetIndex2 | INTEGER | Null | Index 2 for target. Row for the first cell to validate (in matrix question). | |
| TargetIndex3 | INTEGER | Null | Index 3 for target. Column for the last cell to validate (in matrix question) | |
| TargetIndex4 | INTEGER | Null | Index 3 for target. Row for the last cell to validate (in matrix question) | |
| LongTargetIndex | BIGINT | Null | Long value for target. Used most for matrix group id. | |
| LongValue1 | BIGINT | Null | The comparing long value 1. Example: maximum value for numeric, or maximum text length, start value for valid range. | |
| LongValue2 | BIGINT | Null | The comparing long value 2. Example: end value for valid range. | |
| TextValue | VARCHAR(255) | Null | The comparing text value. Example: a word that response value must contain. | |
| BigTextValue | TEXT | Null | The comparing big text value. Like TextValue. | |
| DoubleValue1 | float | Null | The comparing double value 1. Example: maximum value for numeric, or start value for valid range | |
| DoubleValue2 | float | Null | The comparing double value 2. Example: End value for valid range. | |
| Description | VARCHAR(255) | Null | Validator description | |
| ErrorMessage | VARCHAR(255) | Null | Error message to show to the user if validation fails. | |
| TargetId | VARCHAR(255) | Null | Text target id. Used for in-text elements. | |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK77 | QuestionId | OPS_Question.QuestionId |
| OPS_FK92 | ValidatorId | OPS_ValidatorAttribute.ValidatorId |
One record represents one validator attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ValidatorAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeKey | VARCHAR(255) | Not null | Validator attribute key | |
| AttributeValue | VARCHAR(255) | Null | String value of the attribute | |
| ValidatorId | BIGINT | Not null | FK | References OPS_Validator table |
One record defines the branching information for a condition of type rating/scale. Rating branch allows to filter out some questions after response to a rating question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| BranchRatingId | BIGINT | Not null | PK | Unique id |
| BranchType | INTEGER | Null |
Branch types:
|
|
| BranchTitle | VARCHAR(255) | Null | Name of the branch condition. | |
| FilterArray | VARCHAR(255) | Not null | Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5. | |
| QuestionRatingId | BIGINT | Not null | FK | References OPS_QuestionRating table |
One record represents one option for a rating/scale branching condition. One or many options make up a complete rating condition, connected to the OPS_BranchRating table.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| Value | INTEGER | Null | Rating option position. Starts at 0. | |
| BranchRatingId | BIGINT | Not null | FK | References OPS_BranchRating table |
| QuestionRatingId | BIGINT | Not null | FK | References OPS_QuestionRating table |
Indices
| Index | Columns |
|---|---|
| BranchRatingOpti1 | BranchRatingId |
| BranchRatingOpti2 | QuestionRatingId |
| BranchRatingOpti3 | Value, BranchRatingId, QuestionRatingId |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK12 | BranchRatingId | OPS_BranchRating.BranchRatingId |
| OPS_FK13 | QuestionRatingId | OPS_QuestionRating.QuestionRatingId |
One record represents branching condition of type multiple choice. Multiple branch allows to filter out some questions based on the response to a multiple-choice question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| BranchMultipleId | BIGINT | Not null | PK | Unique id |
| BranchType | INTEGER | Null |
Branch types:
|
|
| BranchTitle | VARCHAR(255) | Null | Name of the branch condition. | |
| FilterArray | VARCHAR(255) | Not null | Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5. | |
| QuestionMultipleId | BIGINT | Not null | FK | References OPS_QuestionMultiple |
One record represents option of a branch condition of type multiple choice. One or many options make up a complete multiple condition, connected to the OPS_BranchMultiple table.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| Value | INTEGER | Null | Position of the multiple option. Starts at 0. | |
| BranchMultipleId | BIGINT | Not null | FK | References OPS_BranchMultiple table |
| QuestionMultipleId | BIGINT | Not null | FK | References OPS_QuestionMultiple table |
Indices
| Index | Columns |
|---|---|
| BranchMultipleOp1 | BranchMultipleId |
| BranchMultipleOp2 | QuestionMultipleId |
| BranchMultipleOp3 | Value, BranchMultipleId, QuestionMultipleId |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK8 | BranchMultipleId | OPS_BranchMultiple.BranchMultipleId |
| OPS_FK9 | QuestionMultipleId | OPS_QuestionMultiple.QuestionMultipleId |
One question represents a branching condition of type numeric. Numeric branch allows to filter out some questions if response to a numeric question is greater than minLongValue(minDoubleValue) or/and less then maxLongValue(maxDoubleValue) inclusive.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| BranchNumericId | BIGINT | Not null | PK | Unique id |
| BranchType | INTEGER | Null |
Branch types:
|
|
| BranchTitle | VARCHAR(255) | Null | Name of the branch condition. | |
| FilterArray | VARCHAR(255) | Not null | Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5. | |
| MinLongValue | BIGINT | Null | Minimum INTEGER value for the numeric condition | |
| MaxLongValue | BIGINT | Null | Maximum INTEGER value for the numeric condition | |
| MinDoubleValue | DOUBLE | Null | Minimum double value for the numeric condition | |
| MaxDoubleValue | DOUBLE | Null | Maximum double value for the numeric condition | |
| QuestionNumericId | BIGINT | Not null | FK | References OPS_QustionNumeric table |
One record represents branching condition of type dropdown. Dropdown branch allows to filter out some questions based on response to a dropdown question
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| BranchDropdownId | BIGINT | Not null | PK | Unique id |
| BranchType | INTEGER | Null | Branch types:
|
|
| BranchTitle | VARCHAR(255) | Null | Name of the branch condition. | |
| FilterArray | VARCHAR(255) | Not null | Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5. | |
| QuestionDropdownId | BIGINT | Not null | FK | References OPS_QuestionDropdown table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK1 | QuestionDropdownId | OPS_QuestionDropdown.QuestionDropdownId |
| OPS_FK2 | OPS_BranchDropdownOption.BranchDropdownId | BranchDropdownId |
One record represents option (item) of a branch condition of type dropdown. One or many options make up a complete dropdown condition, connected to the OPS_BranchDropdown table.
One record represents branching condition of matrix type. Matrix branch allows to filter out some questions based on the response to a matrix question (branching on radio buttons and checkboxes only).
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| BranchMatrixId | BIGINT | Not null | PK | Unique id |
| BranchType | INTEGER | Null | Branch types:
|
|
| BranchTitle | VARCHAR(255) | Null | Name of the branch condition. | |
| FilterArray | VARCHAR(255) | Not null | Information about which questions to filter out. Question numbers separated by comma and enclosed in braces. Index 0 in the filterArray corresponds to the first question, 1 to the second and so on. Example: {2, 3, 4} excludes question 3, 4 and 5. | |
| QuestionMatrixId | BIGINT | Not null | FK | References OPS_QuestionMatrix table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK4 | QuestionMatrixId | OPS_QuestionMatrix.QuestionMatrixId |
| OPS_FK5 | OPS_BranchMatrixOption.BranchMatrixId | BranchMatrixId |
One option in a matrix condition. One or many options make up a complete matrix condition, connected to the OPS_BranchMatrix table.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ColumnValue | INTEGER | Null | The column position of the option. | |
| RowValue | INTEGER | Null | The row position of the option. | |
| BranchMatrixId | BIGINT | Not null | FK | References OPS_BranchMatrix table |
| QuestionMatrixId | BIGINT | Not null | FK | References OPS_QuestionMatrix table |
Defines survey invitation.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| InvitationId | BIGINT | Not null | PK | Unique id |
| InvitationName | VARCHAR(255) | Not null | Invitation name | |
| FromName | VARCHAR(50) | Not null | Name of invitation author | |
| FromEmail | VARCHAR(100) | Not null | Email of invitation author | |
| InvitationDate | BIGINT | Not null | Date when the invitation will be sent, in milliseconds | |
| Reminder1Date | BIGINT | Null | Date when the first reminder should be sent, in milliseconds | |
| Reminder2Date | BIGINT | Null | Date when the second reminder should be sent, in milliseconds | |
| Reminder3Date | BIGINT | Null | Date when the third reminder should be sent, in milliseconds | |
| Reminder4Date | BIGINT | Null | Date when the fourth reminder should be sent, in milliseconds | |
| Reminder5Date | BIGINT | Null | Date when the fifth reminder should be sent, in milliseconds | |
| ReminderInterval | INTEGER | Null | DEPRECATED. Interval between each reminder, in milliseconds | |
| ReminderTime | BIGINT | Null | DEPRECATED | |
| ReminderCount | INTEGER | Not null | DEPRECATED. The number of times to send reminder to the invitees who have not responded. | |
| ContentType | VARCHAR(20) | Not null |
Email content type. Values:
|
|
| InvitationSubject | VARCHAR(255) | Not null | The subject in the email with invitation. | |
| InvitationMessage | TEXT | Not null | The content of the email with invitation. | |
| ReminderSubject | VARCHAR(255) | Null | The subject in the email with reminder. | |
| ReminderMessage | TEXT | Null | The content in the email with reminder. | |
| LastReminderSubject | VARCHAR(255) | Null | The subject in the email with last reminder. | |
| LastReminderMessage | TEXT | Null | The content in the email with last reminder. | |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
| TaskId | BIGINT | Not null | Id of the scheduled task | |
| InvitationSentDate | BIGINT | Null | Time in milliseconds when invitation was actually sent | |
| Reminder1SentDate | BIGINT | Null | Time in milliseconds when the first reminder was actually sent | |
| Reminder2SentDate | BIGINT | Null | Time in milliseconds when the second reminder was actually sent | |
| Reminder3SentDate | BIGINT | Null | Time in milliseconds when the third reminder was actually sent | |
| Reminder4SentDate | BIGINT | Null | Time in milliseconds when the fourth reminder was actually sent | |
| Reminder5SentDate | BIGINT | Null | Time in milliseconds when the fifth reminder was actually sent |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK16 | SurveyId | OPS_Survey.SurveyId |
| OPS_FK18 | OPS_Invitee.InvitationId | InvitationId |
One record for one invitee.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| InviteeId | BIGINT | Not null | PK | Unique id |
| InviteeName | VARCHAR(100) | Null | Name of the invitee | |
| InviteeEmail | VARCHAR(100) | Null | Email of the invitee | |
| SentDate | BIGINT | Null | Date when the invitation to this invitee was sent, in milliseconds | |
| IdKey | VARCHAR(100) | Null | Key to identify the invitee | |
| ReminderCount | INTEGER | Null | Number of reminders sent | |
| Status | INTEGER | Null | Status of the invitee:
|
|
| RespondentId | BIGINT | Null | Id of the respondent. Exists when the invitee has responded to the survey. Equals -1 if responded, but the invitation is anonym. | |
| InvitationId | BIGINT | Not null | FK | References OPS_Invitation table |
| AttributesExist | VARCHAR(5) | Null |
True if invitee attributes exist in the OPS_InviteeAttribute table. Values:
|
One record represents one invitee attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| InviteeAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short attribute name | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| InviteeId | BIGINT | Not null | FK | References OPS_Invitee table |
| InvitationId | BIGINT | Not null | FK | References OPS_Invitation table |
Each time a new respondent responds to a survey, one record is created.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| RespondentId | BIGINT | Not null | PK | Unique id |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey table |
| IPAddress | VARCHAR(50) | Null | Respondent's IP address | |
| Ticket | VARCHAR(20) | Null | Ticket to the survey | |
| EntryDate | BIGINT | Null | Timestamp of the first response | |
| CompletedDate | BIGINT | Null | Timestamp of the last response | |
| LastResponse | INTEGER | Null | Number of the last question responded | |
| DeleteStatus | INTEGER | Null | (not in use) | |
| LastUpdated | INTEGER | Null | Timestamp for last updated | |
| AttributesExist | VARCHAR(5) | Null |
True if respondent attributes exist in the OPS_RespondentAttribute table. Values:
|
|
| LanguageCode | VARCHAR(10) | Null | Language code for the last selected survey language. |
Indices
| Index | Columns |
|---|---|
| Respondent1 | SurveyId, RespondentId, EntryDate, CompletedDate |
| Respondent2 | CompletedDate, SurveyId |
| Respondent_Surve1 | SurveyId, IPAddress, CompletedDate |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK38 | SurveyId | OPS_Survey.SurveyId |
| OPS_FK40 | OPS_ResponseDropdown.RespondentId | RespondentId |
| OPS_FK42 | OPS_ResponseEssay.RespondentId | RespondentId |
| OPS_FK44 | OPS_ResponseEssayNum.RespondentId | RespondentId |
| OPS_FK47 | OPS_ResponseEssayText.RespondentId | RespondentId |
| OPS_FK50 | OPS_ResponseFreeText.RespondentId | RespondentId |
| OPS_FK52 | OPS_ResponseMatrix.RespondentId | RespondentId |
| OPS_FK54 | OPS_ResponseMatrixNum.RespondentId | RespondentId |
| OPS_FK57 | OPS_ResponseMatrixText.RespondentId | RespondentId |
| OPS_FK60 | OPS_ResponseMultipleOption.RespondentId | RespondentId |
| OPS_FK62 | OPS_ResponseMultipleOptionNum.RespondentId | RespondentId |
| OPS_FK65 | OPS_ResponseMultipleOptionText.RespondentId | RespondentId |
| OPS_FK68 | OPS_ResponseNumeric.RespondentId | RespondentId |
| OPS_FK70 | OPS_ResponseRating.RespondentId | RespondentId |
| OPS_FK85 | OPS_ReportRespondent.RespondentId | RespondentId |
| OPS_FK93 | OPS_RespondentAttribute.RespondentId | RespondentId |
One record represents one respondent attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| RespondentAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short attribute name | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
Answer to a free text.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
| TextValue | TEXT | Null | The free text answer |
Indices
| Index | Columns |
|---|---|
| ResponseFreeText1 | RespondentId |
| ResponseFreeText4 | QuestionId |
| *ResponseFreeText5 | QuestionId, RespondentId |
(* = Unique index)
Answer to a numeric question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
| LongValue | BIGINT | Null | The numeric answer, INTEGER/long | |
| DecValue | DOUBLE | Null | The numeric answer, decimal |
Indices
| Index | Columns |
|---|---|
| ResponseNumeric_1 | QuestionId |
| ResponseNumeric_2 | RespondentId |
| *ResponseNumeric_3 | QuestionId, RespondentId |
(* = Unique index)
Response to an essay field. One record exists for each essay field response.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ResponseEssayId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
| FieldIndex | smallint | Null | Index of the essay field, starts at 0 |
Indices
| Index | Columns |
|---|---|
| ResponseEssay_Qu1 | QuestionId, FieldIndex |
| ResponseEssay1 | RespondentId |
| *ResponseEssay2 | QuestionId, RespondentId, FieldIndex |
(* = Unique index)
Response to an essay field of type text and dropdown.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| TextValue | VARCHAR(255) | Null | The text answer or value of the selected dropdown item | |
| ResponseEssayId | BIGINT | Not null | FK | References OPS_ResponseEssay table |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
Indices
| Index | Columns |
|---|---|
| *ResponseEssayTex1 | ResponseEssayId |
| ResponseEssayTex2 | QuestionId |
| ResponseEssayTex3 | RespondentId |
(* = Unique index)
Response to a numeric field.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| LongValue | BIGINT | Null | Response of type INTEGER/long | |
| DecValue | DOUBLE | Null | Response of type decimal | |
| ResponseEssayId | BIGINT | Not null | FK | References OPS_ResponseEssay table |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
(* = Unique index)
Response to a rating question.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
| Value | INTEGER | Null | The selected option position. Starts at 1, 0 for N/A option. |
Indices
| Index | Columns |
|---|---|
| *ResponseRating1 | QuestionId, RespondentId |
| ResponseRating_Q1 | QuestionId |
| ResponseRating_R1 | RespondentId |
(* = Unique index)
Response to a dropdown question. Multiple records exist for one dropdown question if multiple selection is on.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
| TextValue | VARCHAR(100) | Not null | Value of the selected dropdown item |
Response to a multiple-choice question. Multiple records exist for one multiple-choice question if multiple selection is on.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ResponseMultipleOptionId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
| OptionIndex | smallint | Null | The index of the selected multiple option. Starts at 0. |
Indices
| Index | Columns |
|---|---|
| ResponseMultiple2 | QuestionId, OptionIndex |
| ResponseMultiple5 | RespondentId, QuestionId |
| *ResponseMultiple7 | QuestionId, RespondentId, OptionIndex |
(* = Unique index)
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK59 | QuestionId | OPS_Question.QuestionId |
| OPS_FK60 | RespondentId | OPS_Respondent.RespondentId |
| OPS_FK63 | OPS_ResponseMultipleOptionNum.ResponseMultipleOptionId | ResponseMultipleOptionId |
| OPS_FK66 | OPS_ResponseMultipleOptionText.ResponseMultipleOptionId | ResponseMultipleOptionId |
Response to a text field (or dropdown list) for a selected option. Currently used to store response to 'Other' field.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| TextValue | VARCHAR(255) | Null | Text response to a field or value of the selected dropdown item | |
| ResponseMultipleOptionId | BIGINT | Not null | FK | References OPS_ResponseMultipleOption table |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
Indices
| Index | Columns |
|---|---|
| *ResponseMultiple1 | ResponseMultipleOptionId |
| ResponseMultiple3 | QuestionId |
| ResponseMultiple4 | RespondentId |
(* = Unique index)
Response to an numeric field for a selected option. Not in use.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| LongValue | BIGINT | Null | Response to a numeric field in multiple question, type INTEGER/long | |
| DecValue | DOUBLE | Null | Response to a numeric field in multiple question, type decimal | |
| ResponseMultipleOptionId | BIGINT | Not null | FK | References OPS_ResponseMultipleOption table |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
(* = Unique index)
Response to a matrix question, one record for each cell response.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ResponseMatrixId | BIGINT | Not null | PK | Unique id |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
| RowPosition | INTEGER | Not null | Row position of the cell | |
| ColumnPosition | INTEGER | Not null | Column position of the cell |
Indices
| Index | Columns |
|---|---|
| *ResponseMatrix1 | QuestionId, RespondentId, ColumnPosition, RowPosition |
| ResponseMatrix2 | RespondentId, QuestionId |
(* = Unique index)
Response to a matrix cell of type text and dropdown.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| TextValue | VARCHAR(255) | Null | Text response or value of the selected dropdown item | |
| ResponseMatrixId | BIGINT | Not null | FK | References OPS_ResponseMatrix table |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
Indices
| Index | Columns |
|---|---|
| *ResponseMatrixTe1 | ResponseMatrixId |
| ResponseMatrixTe2 | ResponseMatrixId, TextValue |
| ResponseMatrixTe3 | QuestionId |
| ResponseMatrixTe4 | RespondentId |
(* = Unique index)
Response to a matrix cell with numeric field.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| LongValue | BIGINT | Null | Response of type INTEGER/long | |
| DecValue | DOUBLE | Null | Response of type decimal | |
| ResponseMatrixId | BIGINT | Not null | FK | References OPS_ResponseMatrix table |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
Indices
| Index | Columns |
|---|---|
| *ResponseMatrixNu1 | ResponseMatrixId |
| ResponseMatrixNu2 | QuestionId |
| ResponseMatrixNu3 | RespondentId |
(* = Unique index)
Stores reusable headers/footers (bank).
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| HeaderFooterId | BIGINT | Not null | PK | Unique id |
| HeaderFooterName | VARCHAR(20) | Null | Header/footer name | |
| HeaderFooterText | TEXT | Null | Header/footer content | |
| UserId | BIGINT | Null | Id of the user who created the resource | |
| Type | INTEGER | Not null | Resource type:
|
Stores reusable list of invitees.
One record for one invitee in reusable invitation list.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ItemId | BIGINT | Not Null | PK | Unique id |
| VARCHAR(100) | Not null | Email of invitee | ||
| InviteeName | VARCHAR(100) | Null | Invitee name | |
| SortValue | INTEGER | Null | Sort value, 0 if alphabetic sort is on. | |
| InvitationListId | BIGINT | Null | FK | References OPS_InvitationList table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK17 | InvitationListId | OPS_InvitationList.InvitationListId |
| OPS_FK118 | OPS_InvitationListItemAttr.ItemId | ItemId |
One record for one invitee attribute in the reusable invitee list.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ItemAttributeId | BIGINT | Not Null | PK | Unique id |
| AttributeName | VARCHAR(50) | Not null | Attribute name | |
| StringValue | VARCHAR(255) | Null | Attribute value | |
| ItemId | BIGINT | Not Null | FK | References OPS_InvitationListItem table |
| InvitationListId | BIGINT | Not Null | FK | References OPS_InvitationList table |
Defines survey report.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportId | BIGINT | Not null | PK | Unique id |
| SurveyId | BIGINT | Not null | FK | References OPS_Survey |
| ReportName | VARCHAR(100) | Null | Report name used in the administration module | |
| Heading | VARCHAR(255) | Null | Displayed report heading | |
| Description | TEXT | Null | Description of the report | |
| Header | TEXT | Null | Report header | |
| Footer | TEXT | Null | Report footer | |
| Font | VARCHAR(100) | Null | Report text font family | |
| HeadingFont | VARCHAR(100) | Null | Report heading font family | |
| FontSize | INTEGER | Null | Report text font size | |
| HeadingFontSize | INTEGER | Null | Report heading font size | |
| BgColour | VARCHAR(20) | Null | Report background colour | |
| FontColour | VARCHAR(20) | Null | Report text font colour | |
| HeadingFontColour | VARCHAR(20) | Null | Report heading font colour | |
| Css | VARCHAR(100) | Null | Report css | |
| RecalculatingPeriode | BIGINT | Not null | Period between report recalculation in minutes. Used if CacheOn is true. | |
| CacheOn | VARCHAR(5) | Null | Cache report:
|
|
| AvailableForResp | VARCHAR(5) | Null |
True if respondents can access the report. Values:
|
|
| IncludeIncompl | VARCHAR(5) | Null | True if the report will include incomplete responses. Values:
|
|
| CreatedBy | BIGINT | Null | Id of the user who created the report. | |
| CreatedDAte | BIGINT | Null | Date when the report was created. | |
| LastModifiedDate | BIGINT | Null | Date when the report was modified last time. | |
| LastFilledDate | BIGINT | Null | Date when the report was filled last time | |
| ContentType | INTEGER | Null | Report type:
|
|
| Status | INTEGER | Null | Report status
|
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK34 | SurveyId | OPS_Survey.SurveyId |
| OPS_FK35 | OPS_ReportElement.ReportId | ReportId |
| OPS_FK81 | OPS_ReportFilter.ReportId | ReportId |
| OPS_FK84 | OPS_ReportRespondent.ReportId | ReportId |
| OPS_FK88 | OPS_ReportAttribute.ReportId | ReportId |
| OPS_FK90 | OPS_ReportElementAttribute.ReportId | ReportId |
| OPS_FK99 | OPS_ReportDrilldown.ReportId | ReportId |
| OPS_FK100 | OPS_ReportQuestionLabel.ReportId | ReportId |
| OPS_FK101 | OPS_ReportQuestionInterval.ReportId | ReportId |
One record represents one report attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the user attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| ReportId | BIGINT | Not null | FK | References OPS_Report table |
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportElementId | BIGINT | Not null | PK | Unique id |
| ReportId | BIGINT | Not null | FK | References OPS_Report table |
| SortValue | INTEGER | Not null | Position of the report element. Starts at 0. | |
| ElementName | VARCHAR(255) | Not null | Report element name used in the administration module. | |
| Heading | VARCHAR(255) | Null | Report element heading, displayed in the report. | |
| Description | TEXT | Null | Report element description | |
| NumericPrecision | INTEGER | Null | Number of digits after comma in report values. | |
| ReportTypes | VARCHAR(255) | Null |
Report types, separated by comma and enclosed in braces. Example: {1, 2, 10}
|
|
| ShowNonSelectedItems | VARCHAR(5) | Null | True is non selected options should be displayed in the report element. True/false |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK35 | ReportId | OPS_Report.ReportId |
| OPS_FK36 | OPS_ReportElementQuestionNo.ReportElementId | ReportElementId |
| OPS_FK89 | OPS_ReportElementAttribute.ReportElementId | ReportElementId |
One record represents one report element attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportElementAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(30) | Not null | Short name of the user attribute | |
| StringValue | VARCHAR(255) | Null | String value of the attribute | |
| LongValue | BIGINT | Null | Long value of the attribute | |
| BigTextValue | TEXT | Null | Big text value of the attribute | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| ReportElementId | BIGINT | Not null | FK | References OPS_ReportElement table |
| ReportId | BIGINT | Not null | FK | References OPS_Report table |
Number of the questions included in a report element.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportId | BIGINT | Not null | Report id | |
| ReportElementId | BIGINT | Not null | FK | References OPS_ReportElement table |
| QuestionNumber | INTEGER | Not null | Question number |
One record represents intervals for one question in a report.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportQuestionIntervalId | BIGINT | Not null | PK | Unique id |
| Intervals | TEXT | Not null | Comma separated intervals, enclosed in [ ] | |
| NumericType | INTEGER | Not null | Numeric type of the intervals
|
|
| TargetType | INTEGER | Not null | Target id for numeric intervals:
|
|
| MappingId | VARCHAR(255) | Not null | Id used for mapping intervals to questions | |
| QuestionId | BIGINT | Not null | Id of the corresponding question | |
| ReportId | BIGINT | Not null | FK | References OPS_Report table |
One record represents intervals for one question in a report.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportQuestionLabelId | BIGINT | Not null | PK | Unique id |
| Label | VARCHAR(255) | Null | Question label | |
| TargetType | INTEGER | Not null | Target id for numeric intervals:
|
|
| MappingId | VARCHAR(255) | Not null | Id used for mapping intervals to questions | |
| QuestionId | BIGINT | Not null | Id of the corresponding question | |
| ReportId | BIGINT | Not null | FK | References OPS_Report table |
General condition for a question. Currently used only for report conditions.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionConditionId | BIGINT | Not null | PK | Unique id |
| ConditionName | VARCHAR(100) | Null | Name of the question condition | |
| ConditionType | INTEGER | Null | Condition type:
|
|
| FilterArray | VARCHAR | Null | Which question to filter out is condition is true. (not is use) | |
| QuestionId | BIGINT | Not null | FK | References OPS_Question table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK79 | QuestionId | OPS_Question.QuestionId |
| OPS_FK80 | OPS_QuestionConditionOption.QuestionConditionId | QuestionConditionId |
| OPS_FK82 | OPS_ReportFilter_QuestionCond.QuestionConditionId | QuestionConditionId |
One option in question condition.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| QuestionConditionOptionId | BIGINT | Not null | PK | Unique id |
| Position1 | INTEGER | Null | Option position 1. Can be option index in a multiple question, or essay field index in essay, or column position of the matrix cell. | |
| Position2 | INTEGER | Null | Option position 1. Row of the cell in matrix question. | |
| TagFieldPosition | INTEGER | Null | Position of the essay field. | |
| TextValue | VARCHAR | Null | The comparing text value. Example: a word which response value must contain. | |
| LongValue1 | BIGINT | Null | The comparing long value 1. Example: maximum value for numeric or start value for valid range. | |
| LongValue2 | BIGINT | Null | The comparing long value 2. Example: End value for valid range. | |
| DoubleValue1 | DOUBLE | Null | The comparing decimal value 1. Example: maximum value for numeric or start value for valid range. | |
| DoubleValue2 | DOUBLE | Null | The comparing decimal value 2. Example: End value for valid range. | |
| QuestionConditionId | BIGINT | Not null | FK | References OPS_QuestionCondition table |
| QuestionId | BIGINT | Not null | Question id |
Defines a report filter.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportFilterId | BIGINT | Not null | PK | Unique id |
| ReportFilterName | VARCHAR(100) | Not null | Report filter name, used in the administration module | |
| ReportFilterType | INTEGER | Not null | Report filter type:
|
|
| StartDate | BIGINT | Null | Date condition, applied to respondent entry date | |
| StopDate | BIGINT | Null | Date condition, applied to respondent complete date | |
| CompletedStatus | INTEGER | Null | Condition on respondent completed status (not in use) | |
| InvitationStatus | INTEGER | Null | Condition on respondent invitation status (not in use) | |
| ResponseLanguages | VARCHAR(255) | Null | Response languages filter - comma separated language codes | |
| LastUpdated | BIGINT | Null | Timestamp when the filter was last updated | |
| ReportId | BIGINT | Not null | FK | References OPS_Report table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK81 | ReportId | OPS_Report.ReportId |
| OPS_FK83 | OPS_ReportFilter_QuestionCond.ReportFilterId | ReportFilterId |
One record maps OPS_ReportFilter og OPS_QuestionCondition. One report filter can contains many question conditions.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportFilterId | BIGINT | Not null | FK | References OPS_ReportFilter table |
| QuestionConditionId | BIGINT | Not null | FK | References OPS_QuestionCondition table |
One record represents one report drilldown.
One record maps respondent to a report. One respondent can be included in many reports and one report can contain many respondents.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportId | BIGINT | Not null | FK | References OPS_Report table |
| RespondentId | BIGINT | Not null | FK | References OPS_Respondent table |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK84 | ReportId | OPS_Report.ReportId |
| OPS_FK85 | RespondentId | OPS_Respondent.RespondentId |
One record represents one generated report file.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportFileId | BIGINT | Not null | PK | Unique id |
| ReportFileName | VARCHAR(255) | Not null | Report file name | |
| Timestamp | BIGINT | Null | Time when the report file was generated (milliseconds) | |
| SystemVersion | VARCHAR(100) | Not null | System version for used for report generation | |
| ReportId | BIGINT | Not null | Report id | |
| IpAddress | VARCHAR(100) | Not null | Ip address of the system | |
| FileType | VARCHAR(30) | Not null | File type (zip/pdf/html) |
One record maps for every report portal.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportPortalId | BIGINT | Not null | PK | Unique id |
| Name | VARCHAR(255) | Not null | Report portal name | |
| CreatedDate | BIGINT | Not null | Create date in milliseconds | |
| CreatedBy | BIGINT | Not null | Id of the user who created the report portal | |
| ModifiedDate | BIGINT | Not null | Last modified date in milliseconds | |
| ModifiedBy | BIGINT | Not null | Id of the last user who modified the report portal |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK116 | ReportPortalItem.ReportPortalId | ReportPortalId |
| OPS_FK117 | OPS_ReportPortalAttribute.ReportPortalId | ReportPortalId |
One record represents one report portal attribute.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportPortalAttributeId | BIGINT | Not null | PK | Unique id |
| AttributeName | VARCHAR(255) | Not null | Name of the attribute | |
| StringValue | VARCHAR(255) | Null | String attribute value | |
| LongValue | BIGINT | Null | Long attribute value | |
| BigTextValue | TEXT | Null | Big text attribute value | |
| FloatValue | DOUBLE | Null | Double value of the attribute | |
| ReportPortalId | BIGINT | Not null | FK | References OPS_ReportPortal table |
One record represents one report portal item.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ReportPortalItemId | BIGINT | Not null | PK | Unique id |
| ItemType | INTEGER | Not null | Type of the report portal item:
|
|
| ItemIndex | INTEGER | Not null | Position of the item | |
| ZoneNo | INTEGER | Not null | Zone number where the item is located | |
| ItemText | TEXT | Null | Item text (text for items of type TEXT and link label for items of type LINK) | |
| LinkIconLocation | VARCHAR(255) | Null | Icon location for the item of type link | |
| LinkOnClick | VARCHAR(255) | Null | JavaScript to execute no link click. Not yet in use. | |
| MouseOverTitle | VARCHAR(255) | Null | Title to show when mouse is over the link | |
| ImageURL | VARCHAR(255) | Null | Url of the image | |
| LinkURL | VARCHAR(255) | Null | Link url | |
| ReportPortalId | VARCHAR(255) | Not null | FK | References OPS_ReportPortal table |
| ReportId | BIGINT | Null | Id of the report to show if any |
One record represents one panel (panel management)
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PanelId | BIGINT | Not null | PK | Unique id |
| PanelName | VARCHAR(255) | Not null | Name of the panel | |
| CreatedDate | BIGINT | Not null | Date the panel was created in milliseconds | |
| LastUpdatedDate | BIGINT | Not null | Date the panel was last updated | |
| SignUpValid | VARCHAR(5) | Not null | Indicated whether sign-up survey iv valid (true/false) | |
| PanelStatus | INTEGER | Not null | Status of the panel | |
| UserId | BIGINT | Not null | User id who created te panel. |
One record represents one panel attribute (panel question)
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PanelAttributeId | BIGINT | Not null | PK | Unique id |
| PanelAttributeName | VARCHAR(255) | Not null | Attribute name (used in admin module) | |
| PanelAttributeLabel | VARCHAR(255) | Not null | Attribute label (used in panel module, visible to the panelists) | |
| PanelAttributeType | INTEGER | Not null | Attribute type:
|
|
| PanelAttributePosition | INTEGER | Not null | Sequential position of the attribute | |
| SelectionMode | INTEGER | Null | Type of the selection, applies if PanelAttributeType is Selection:
|
|
| SelectionType | INTEGER | Null | Selection type
|
|
| DatePattern | VARCHAR(255) | Null | Date pattern for attribute of type Date | |
| PanelCategoryId | BIGINT | Not null | FK | References OPS_PanelCategory |
One record represents one panel attribute item (used for selection items)
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PanelAttributeItemId | BIGINT | Not null | PK | Unique id |
| PanelAttributeItemName | VARCHAR(255) | Not null | Panel attribute item name | |
| PanelAttributeItemValue | VARCHAR(255) | Not null | Attribute value | |
| PanelAttributeItemPosition | INTEGER | Null | Position of the item | |
| PanelAttributeId | BIGINT | Not null | FK | References OPS_PanelAttribute |
One record represents one panel category
One record represents mapping between panel and sign-up survey.
One record represents one panelist
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PanelistId | BIGINT | Not null | PK | Unique id |
| PanelistName | VARCHAR(255) | Not null | Panelist name | |
| PanelistEmail | VARCHAR(255) | Not null | Panelist email | |
| Password | VARCHAR(250) | Not null | Password (encrypted) | |
| CreatedDate | BIGINT | Not null | Date the panelist was created (signed up) in milliseconds | |
| LastLoginDate | BIGINT | Null | Date of the last login | |
| LastUpdatedDate | BIGINT | Not null | Date when panelist was updated last time (in milliseconds) | |
| LastRespondedDate | BIGINT | Null | Date when panelist last responded to a survey (in milliseconds) | |
| ProfileTicket | VARCHAR(100) | Null | Ticket to edit own profile | |
| RequestedEmail | VARCHAR(255) | Null | Email requested for change (stored here until verified) | |
| RequestedEmailKey | VARCHAR(255) | Null | Key for email change request | |
| PanelistStatus | INTEGER | Null | Status of the panelist
|
|
| PanelId | BIGINT | Not null | FK | References OPS_Panel |
One record represents one panelist attribute (response to panel attribute)
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PanelistAttributeId | BIGINT | Not null | PK | Unique id |
| PanelistAttributeValue | VARCHAR(255) | Null | String value of the attribute (response to the panel attribute) | |
| PanelistAttributeBigValue | text | Null | Text value of the attribute (response to the panel attribute) | |
| PanelistAttributeIntValue | BIGINT | Null | INTEGER value of the attribute (response to the panel attribute) | |
| PanelistAttributeDecValue | double | Null | Decimal value of the attribute (response to the panel attribute) | |
| PanelistId | BIGINT | Not null | FK | References OPS_Panelist table |
| PanelAttributeId | BIGINT | Not null | Id of the panel attribute |
One record represents one panelist history item.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PanelistHistoryId | BIGINT | Not null | PK | Unique id |
| PanelistId | BIGINT | Not null | FK | References OPS_Panelist table |
| EntryDate | BIGINT | Not null | History item date in milliseconds | |
| EntryType | INTEGER | Not null | Type of the history item:
|
|
| SurveyId | BIGINT | Null | Survey id that panelist is invited/responded to | |
| SurveyName | VARCHAR(255) | Not null | Survey name that panelist is invited/responded to | |
| InviteeId | BIGINT | Null | Invitee id used in invitation | |
| RespondentId | BIGINT | Null | Respondent id - if panelist started/completed/saved the survey | |
| PointsAwarded | INTEGER | Null | Points awarded. NOT YET IN USE. |
One record represents panelist who's email is not yet verified.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PanelistUnverifiedId | BIGINT | Not null | PK | Unique id |
| RegistrationKey | VARCHAR(100) | Not null | Registration key - ticket used in the verification email | |
| PanelistName | VARCHAR(255) | Not null | Panelist name | |
| PanelistEmail | VARCHAR(255) | Not null | Panelist email | |
| PanelistPassword | VARCHAR(255) | Not null | Panelist password, encrypted | |
| RegistrationDate | BIGINT | Not null | Date of the registration in milliseconds. | |
| PanelId | BIGINT | Not null | FK | References OPS_Panel |
One record represents sample definition
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SampleDefinitionId | BIGINT | Not null | PK | Unique id |
| SampleDefinitionName | VARCHAR(255) | Not null | Sample definition name | |
| MaxSampleSize | INTEGER | Not null | The max size of panelists in the sample (0 = unlimited). | |
| SampleType | INTEGER | Not null | Specifies how the sample is drawn from the finite population of panelists:
|
|
| IsValid | VARCHAR(5) | Not null | Indicates whether the sample definition is valid. True/false | |
| PanelistCount | INTEGER | Not null | Number of panelists in the sample definition | |
| CreatedDate | BIGINT | Not null | Date when sample definition was created | |
| CreatedBy | BIGINT | Not null | User who created the sample definition | |
| UpdatedDate | BIGINT | Null | Date when sample definition was last updated. | |
| UpdatedBy | BIGINT | Null | Id of the last user who updated the sample definition | |
| PanelId | BIGINT | Not null | FK | References OPS_Panel |
One record represents one sample condition
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| SampleConditionId | BIGINT | Not null | PK | unique id |
| SampleDefinitionId | BIGINT | Not null | FK | References OPS_SampleDefinition table |
| PanelAttributeId | BIGINT | Not null | Id of the panel attribute | |
| MultipleSelectionType | INTEGER | Not null | Type of multiple selection
|
|
| SearchType | INTEGER | Not null |
Search type for text attributes
|
One record represents one sample condition value (selection item value)
One record represents one scheduled task.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ScheduledTaskId | BIGINT | Not null | PK | Unique id |
| TaskName | VARCHAR(50) | Not null | Task name | |
| ClassName | VARCHAR(255) | Null | Name of the class that executes this task | |
| StartDate | BIGINT | Not null | Start date of the task, in milliseconds | |
| Period | BIGINT | Not null | Period between executions, in milliseconds | |
| TaskType | VARCHAR(10) | Null | Task type (not in uses) | |
| Parameters | VARCHAR(255) | Null | Parameters for the task, as string | |
| TotalRuns | INTEGER | Null | Number of executions | |
| RunCounter | INTEGER | Null | Number of current execution | |
| Status | VARCHAR(50) | Null | Status of the task:
|
|
| Executor | VARCHAR(50) | Null | The task executor |
One record represents one failed email.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| FailedEmailMsgId | BIGINT | Not null | PK | Unique id |
| FromName | VARCHAR(100) | Null | Name of the person who sends the email | |
| FromEmail | VARCHAR(100) | Null | Email of the person who sends the email | |
| Subject | VARCHAR(100) | Null | Email subject | |
| Message | TEXT | Null | Content of the email | |
| Type | VARCHAR(100) | Null | Email content type. Values:
|
|
| EntryDate | BIGINT | Null | Entry date |
Foreign keys
| Foreign key | Child | Parent |
|---|---|---|
| OPS_FK15 | OPS_FailedEmailRecipient.FailedEmailMsgId | FailedEmailMsgId |
One record represents one recipient who has not received an email.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| FailedEmailRecipientId | BIGINT | Not null | PK | Unique id |
| FailedEmailMsgId | BIGINT | Not null | FK | References OPS_FailedEmailMsg table |
| ToName | VARCHAR(100) | Null | Recipient's name | |
| ToEmail | VARCHAR(100) | Null | Recipient's email address | |
| Description | VARCHAR(100) | Null | Why the email failed to be sent |
Stores information about the different hosts in the Opinio installation.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| HostId | VARCHAR(10) | Not null | PK | Unique id |
| UniqueHostId | VARCHAR(10) | Null | Extra host id | |
| IPBasedURL | VARCHAR(255) | Null | IP-based system URL | |
| License | VARCHAR(255) | Null | Generated license key | |
| SystemInfoId | BIGINT | Not null | References OPS_SystemInfo.SystemInfoId |
One record represents an uploaded file, with the url to the cluster member where the file is located. Only in use if running Opinio in a clustered environment.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| FileURL | VARCHAR(255) | Not null | The file url | |
| UploadedDate | BIGINT | Not null | The date when file was uploaded | |
| UploadedCheckSum | BIGINT | Not null | Will decide if the file needs to be uploaded by another cluster member |
Stores when a cluster member last checked for new uploaded files.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| UniqueMemberId | VARCHAR(50) | Not null | Unique cluster member id | |
| CheckDate | BIGINT | Not null | Timestamp when last checked |
Stores plugin properties.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| PropertyId | BIGINT | Not null | PK | Unique id |
| PropertyName | VARCHAR(255) | Not null | The property name | |
| PropertyValue | TEXT | Not null | The property value | |
| ClassName | VARCHAR(255) | Not null | Name of the plugin which the property belongs to |
Stores the last unique ID's to each table.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| IdType | BIGINT | Not null | Id type | |
| Id | BIGINT | Not null | Last unique id |
One record represents one context guide item
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| ContextGuideItemId | BIGINT | Not null | PK | Unique id |
| GuideType | VARCHAR(100) | Not null | Type of the context guide | |
| ItemType | VARCHAR(100) | Not null | Type of the context guide item | |
| ChangeDate | BIGINT | Not null | Date when the item has been changed | |
| UserId | BIGINT | Not null | Id of the user who made the change | |
| ChangeType | VARCHAR(255) | Not null | Type of the change | |
| ChangeKey | VARCHAR(255) | Null | ||
| ChangeId | VARCHAR(255) | Null | ||
| ResourceId | BIGINT | Not null | Id of the resource |
One record represents one recent item.
Columns
| Column | Data type | Allow NULLs | Key | Notes |
|---|---|---|---|---|
| RecentItemId | BIGINT | Not null | PK | Unique id |
| UserId | BIGINT | Not null | FK | References OPS_User table |
| ItemIndex | INTEGER | Not null | Position of the item | |
| IconPath | VARCHAR(255) | Not null | Path to the item icon | |
| ItemText | TEXT | Not null | Item text (text for items of type TEXT and link label for items of type LINK) | |
| Label | VARCHAR(255) | Not null | Item label | |
| Url | VARCHAR(255) | Not null | Item url | |
| ResourceId | BIGINT | Not null | Id of the resource | |
| ResourceType | INTEGER | Not null | Type of the resource |