Opinio database description


Table of Contents

Introduction
Tables
OPS_SystemInfo
OPS_SystemAttribute
OPS_User
OPS_UserAttribute
OPS_UserGroup
OPS_UserGroupMember
OPS_ResourcePermission
OPS_SurveyGroup
OPS_Survey
OPS_SurveyAttribute
OPS_SurveyPage
OPS_SurveyPageAttribute
OPS_SurveyComment
OPS_SurveyPiping
OPS_Quota
OPS_Question
OPS_QuestionAttribute
OPS_QuestionTagField
OPS_QuestionRating
OPS_QuestionMultiple
OPS_QuestionMultipleOption
OPS_QuestionNumeric
OPS_QuestionMatrix
OPS_QuestionMatrixGroup
OPS_QuestionMatrixCell
OPS_QuestionFreeText
OPS_QuestionDropdown
OPS_QuestionComment
OPS_Dropdown
OPS_DropdownItem
OPS_Validator
OPS_ValidatorAttribute
OPS_Branch
OPS_BranchRating
OPS_BranchRatingOption
OPS_BranchMultiple
OPS_BranchMultipleOption
OPS_BranchNumeric
OPS_BranchDropdown
OPS_BranchDropdownOption
OPS_BranchMatrix
OPS_BranchMatrixOption
OPS_Invitation
OPS_Invitee
OPS_InviteeAttribute
OPS_Respondent
OPS_RespondentAttribute
OPS_ResponseFreeText
OPS_ResponseNumeric
OPS_ResponseEssay
OPS_ResponseEssayText
OPS_ResponseEssayNum
OPS_ResponseRating
OPS_ResponseDropdown
OPS_ResponseMultipleOption
OPS_ResponseMultipleOptionText
OPS_ResponseMultipleOptionNum
OPS_ResponseMatrix
OPS_ResponseMatrixText
OPS_ResponseMatrixNum
OPS_HeaderFooter
OPS_InvitationList
OPS_InvitationListItem
OPS_InvitationListItemAttr
OPS_Report
OPS_ReportAttribute
OPS_ReportElement
OPS_ReportElementAttribute
OPS_ReportElementQuestionNo
OPS_ReportQuestionInterval
OPS_ReportQuestionLabel
OPS_QuestionCondition
OPS_QuestionConditionOption
OPS_ReportFilter
OPS_ReportFilter_QuestionCond
OPS_ReportDrilldown
OPS_ReportRespondent
OPS_ReportFile
OPS_ReportPortal
OPS_ReportPortalAttribute
OPS_ReportPortalItem
OPS_Panel
OPS_PanelAttribute
OPS_PanelAttributeItem
OPS_PanelCategory
OPS_PanelSignUpSurvey
OPS_Panelist
OPS_PanelistAttribute
OPS_PanelistHistory
OPS_PanelUnverified
OPS_SampleDefinition
OPS_SampeCondition
OPS_SampeConditionValue
OPS_ScheduledTask
OPS_FailedEmailMsg
OPS_FailedEmailRecipient
OPS_Host
OPS_UploadedFile
OPS_UploadedFileCheck
OPS_PluginProperty
OPS_SequenceID
OPS_ContextGuideItem
OPS_RecentItem

Introduction

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 103 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
  • Hypersonic SQL
  • MySQL
  • Microsoft SQL Server
  • Oracle
Opinio 4.1 - 5.2.11
  • Hypersonic SQL
  • MySQL
  • Microsoft SQL Server
  • Oracle
  • PostgreSQL
  • IBM DB2
Opinio 6.0
  • Derby
  • MySQL
  • Microsoft SQL Server
  • Oracle
  • PostgreSQL
  • IBM DB2

In this document we use MySQL data types to describe table columns. Column types may vary from database to database.

11. January 2017

Tables

OPS_SystemInfo

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.

Foreign keys

Foreign key Child Parent
OPS_FK78 OPS_SystemAttribute.SystemInfoId SystemInfoId
OPS_FK91 OPS_Host.SystemInfoId SystemInfoId

OPS_SystemAttribute

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

Foreign keys

Foreign key Child Parent
OPS_FK78 SystemInfoId OPS_SystemInfo.SystemInfoId

OPS_User

Defines a user.

Columns

Column Data type Allow NULLs Key Notes
UserId BIGINT Not null PK Unique id
Login VARCHAR(20) Not null   Login name
UserName VARCHAR(100) Null   User's personal name
Password VARCHAR(250) Not null   Password
EncryptionKey VARCHAR(100) Null   Encryption key

Indices

Index Columns
User_Login Login

Foreign keys

Foreign key Child Parent
OPS_FK37 OPS_ResourcePermission.UserId UserId
OPS_FK74 OPS_UserAttribute.UserId UserId
OPS_FK75 OPS_UserGroupMember.UserId UserId
OPS_FK103 OPS_Panel.UserId UserId
OPS_FK98 OPS_RecentItem.UserId UserId

OPS_UserAttribute

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

Indices

Index Columns
UserAttribute_Us1 UserId

Foreign keys

Foreign key Child Parent
OPS_FK74 UserId OPS_User.UserId

OPS_UserGroup

Defines a group of Opinio users.

Columns

Column Data type Allow NULLs Key Notes
UserGroupId BIGINT Not null PK Unique id
UserGroupName VARCHAR(100) Null   Name of the user group
Description VARCHAR(100) Null   Description of the user group

Foreign keys

Foreign key Child Parent
OPS_FK76 OPS_UserGroupMember.UserGroupId UserGroupId

OPS_UserGroupMember

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

Indices

Index Columns
UserGroupMember_1 UserId
UserGroupMember_2 UserGroupId
UserGroupMember_3 UserId, UserGroupId

Foreign keys

Foreign key Child Parent
OPS_FK75 UserId OPS_User.UserId
OPS_FK76 UserGroupId OPS_UserGroup.UserGroupId

OPS_ResourcePermission

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:
  • 0 = all resources
  • 1 = user group
  • 2 = survey group (folder)
  • 3 = survey
  • 4 = custom report
  • 5 = dropdown list
  • 6 = header
  • 7 = footer
  • 8 = plugin
  • 9 = invitation list(reusable list of invitees)
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}
  • 0 - admin access
  • 1 = read access
  • 2 = modify access
  • 4 = create access
  • 5 = access to survey invitations (if ResourceType is survey)
  • 6 = access to survey reports (if ResourceType is survey)
  • 7 = permission to delete responses (if ResourceType is survey)
  • 8 = permission to add a new survey to the group (if ResourceType is survey group)
  • 9 = permission to add a new survey group to the group (if ResourceType is survey group)
  • 10 = permission to administrate users (if ResourceType is user group)
UserId BIGINT Null FK References OPS_User table

Indices

Index Columns
ResourcePermissi1 UserId
ResourcePermissi2 ResourceType, ResourceId, UserId

Foreign keys

Foreign key Child Parent
OPS_FK37 UserId OPS_User.UserId

OPS_SurveyGroup

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
  • 0 - name
  • 1 - date
  • 2 - date reverse
CreatedDate BIGINT Null   Created date
UpdatedDate BIGINT Null   Last updated date

Indices

Index Columns
SurveyGroup_Pare1 ParentGroupId

Foreign keys

Foreign key Child Parent
OPS_FK71 OPS_Survey.SurveyGroupId SurveyGroupId

OPS_Survey

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:
  • 0 - survey is a survey
  • 1 - survey is a question library
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

Indices

Index Columns
Survey_SurveyGro1 SurveyGroupId

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

OPS_SurveyAttribute

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

Indices

Index Columns
SurveyAttribute_1 SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK72 SurveyId OPS_Survey.SurveyId

OPS_SurveyPage

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

Indices

Index Columns
SurveyPage_Surve1 SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK73 SurveyId OPS_Survey.SurveyId
OPS_FK86 OPS_SurveyPageAttribute.SurveyPageId SurveyPageId

OPS_SurveyPageAttribute

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

Foreign keys

Foreign key Child Parent
OPS_FK86 SurveyPageId OPS_SurveyPage.SurveyPageId
OPS_FK87 SurveyId OPS_Survey.SurveyId

OPS_SurveyComment

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

Foreign keys

Foreign key Child Parent
OPS_FK95 OPS_Survey.SurveyId SurveyId

OPS_SurveyPiping

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
  • 1 - piping of type text (the only type currently supported)
PipingSourceType INTEGER Null   The piping source type
  • 1 - question response
  • 2 - survey URL parameter
  • 3 - invitee data
  • 4 - fixed value
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.
  • 1 - in-text element
  • 2 - response to question type (rating/numeric/multiple/matrix cell/dropdown)
  • 3 - multiple Other option
  • 4 - open-ended response
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:
  • 1 - invitee name
  • 2 - invitee email
  • 3 - invitee attribute
  • 4 - invitee id
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

Indices

Index Columns
SurveyPiping1 PipingName, SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK102 OPS_Survey.SurveyId SurveyId

OPS_Quota

Defines a response quota for survey.

Columns

Column Data type Allow NULLs Key Notes
QuotaId BIGINT Not null PK Unique id
QuotaIndex INTEGER Null   Index in the quota list
QuotaLimit INTEGER Null   Quota limit
GroupIndex INTEGER Null   The group index
GroupName VARCHAR(255) Null   The name of the group
QuestionNo INTEGER Null   Question number in the survey, 0 if leaf
QuotaType INTEGER Null   The quota type
QuotaTargetIndex1 INTEGER Null   The quota target index
QuotaTargetIndex2 INTEGER Null   The quota target index 2
CreatedDate BIGINT Not null   The creation date for the quota
UpdatedDate BIGINT Null   The updated date for the quota
QuotaParentId BIGINT Null   The parent id for the quota
QuotaAncestorId BIGINT Null   The ancestor id for the quota
SurveyId BIGINT Not null FK The survey id for the quota

Foreign keys

Foreign key Child Parent
OPS_FKXX OPS_Survey.SurveyId SurveyId

OPS_Question

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.
  • 0 - no type
  • 1 - rating
  • 2 - numeric
  • 3 - multiple
  • 4 - matrix
  • 5 - dropdown
TextBefore TEXT Null   Text before the question
TextAfter TEXT Null   Text after the question
QuestionLayout VARCHAR(10) Null   Layout of the question. Values:
  • vertical
  • horizontal
RelationKey VARCHAR(10) Null   Relation key
SurveyId BIGINT Not null FK References OPS_Survey table

Indices

Index Columns
Question1 SurveyId, QuestionNo, QuestionId

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

OPS_QuestionAttribute

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

Indices

Index Columns
QuestionAttribut1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK20 QuestionId OPS_Question.QuestionId

OPS_QuestionTagField

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
  • 1 - dropdown list
  • 2 - checkbox
  • 3 - text
  • 4 - numeric INTEGER
  • 5 - numeric decimal
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

Indices

Index Columns
QuestionTagField1 QuestionId
QuestionTagField2 TagId, QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK32 DropdownId OPS_Dropdown.DropdownId
OPS_FK33 QuestionId OPS_Question.QuestionId

OPS_QuestionRating

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:
  • true
  • false
NALabel VARCHAR(50) Null   Label for N/A option
RatingStartLevel INTEGER Null   Start value for the rating
RatingLabels TEXT Null   Label for the rating value

Indices

Index Columns
QuestionRating_Q1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK11 OPS_BranchRating.QuestionRatingId QuestionRatingId
OPS_FK13 OPS_BranchRatingOption.QuestionRatingId QuestionRatingId
OPS_FK31 QuestionId OPS_Question.QuestionId

OPS_QuestionMultiple

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:
  • true - single selection
  • false - multiple selection
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

Indices

Index Columns
QuestionMultiple2 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK7 OPS_BranchMultiple.QuestionMultipleId QuestionMultipleId
OPS_FK9 OPS_BranchMultipleOption.QuestionMultipleId QuestionMultipleId
OPS_FK28 QuestionId OPS_Question.QuestionId
OPS_FK29 OPS_QuestionMultipleOption.QuestionMultipleId QuestionMultipleId

OPS_QuestionMultipleOption

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
  • 0 - default layout (text to the right of the option)
  • 1 - option to the left of the text
  • 2 - option to the right of the text
  • 3 - option over the text
  • 4 - option under the text
ImageLayout INTEGER Null   Image layout
  • 0 - default layout (text to the right of the option)
  • 1 - image to the left of the text
  • 2 - image to the right of the text
  • 3 - image over the text
  • 4 - image under the text
QuestionMultipleId BIGINT Not null FK References OPS_QuestionMultiple table

Indices

Index Columns
QuestionMultiple1 QuestionMultipleId
QuestionMultiple3 OptionIndex, QuestionMultipleId

Foreign keys

Foreign key Child Parent
OPS_FK29 QuestionMultipleId OPS_QuestionMultiple.QuestionMultipleId

OPS_QuestionNumeric

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
  • 1 - INTEGER
  • 2 - decimal
PrefixLabel VARCHAR(255) Null   Label before the field
PostfixLabel VARCHAR(255) Null   Label after the field
FieldSize INTEGER Null   Size of the field

Indices

Index Columns
QuestionNumeric_1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK10 OPS_BranchNumeric.QuestionNumericId QuestionNumericId
OPS_FK30 QuestionId OPS_Question.QuestionId

OPS_QuestionMatrix

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
VisibleRow_Count BIGINT Null The default number of visible rows  
ShowHiddenRowsLabel VARCHAR(255) Null Label for the button to show hidden rows  

Indices

Index Columns
QuestionMatrix_Q1 QuestionId

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

OPS_QuestionMatrixGroup

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
  • 1 - rating type, contains cells of radio type, one cell per row can be selected
  • 2 - ranking type, contains cells of checkbox type, one cell per column x row can be selected
  • 3 - intersection type, contains cells of radio type, only one cell can be selected
  • 4 - any cell type, contains cells of checkbox type, any cell can be selected
  • 5 - custom type, contains cells of different types
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
RowRotationOn VARCHAR(5) Null Random row rotation on/off. Values:
  • true
  • false
 

Indices

Index Columns
QuestionMatrixGr1 QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK27 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId

OPS_QuestionMatrixCell

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.
  • 1 - label
  • 2 - dropdown list
  • 3 - checkbox
  • 4 - radio button
  • 5 - text
  • 6 - numeric INTEGER
  • 7 - numeric decimal
  • 8 - image
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

Indices

Index Columns
QuestionMatrixCe1 QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK25 DropdownId OPS_Dropdown.DropdownId
OPS_FK26 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId

OPS_QuestionFreeText

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)

Indices

Index Columns
QuestionFreeText1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK23 QuestionId OPS_Question.QuestionId

OPS_QuestionDropdown

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:
  • true
  • false
DropdownSize INTEGER Null   The size of the dropdown box

Indices

Index Columns
QuestionDropdown1 QuestionId
QuestionDropdown2 QuestionId, DropdownId

Foreign keys

Foreign key Child Parent
OPS_FK1 OPS_BranchDropdown.QuestionDropdownId QuestionDropdownId
OPS_FK3 OPS_BranchDropdownOption.QuestionDropdownId QuestionDropdownId
OPS_FK21 DropdownId OPS_Dropdown.DropdownId
OPS_FK22 QuestionId OPS_Question.QuestionId

OPS_QuestionComment

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

Foreign keys

Foreign key Child Parent
OPS_FK96 OPS_Question.QuestionId QuestionId

OPS_Dropdown

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:
  • true
  • false
DropdownSize INTEGER Null   Size of the dropdown box
SortOn VARCHAR(5) Null   Sorting of dropdown items. Values:
  • true
  • false
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:
  • 0 - dropdown belongs to reusable resources (dropdown bank)
  • 1 - dropdown belongs to a question essay field
  • 2 - dropdown belongs to a matrix cell
  • 3 - dropdown belongs to a question og type dropdown

Indices

Index Columns
Dropdown_Questio1 QuestionId

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

OPS_DropdownItem

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

Indices

Index Columns
DropdownItem_dro1 DropdownId

Foreign keys

Foreign key Child Parent
OPS_FK14 DropdownId OPS_Dropdown.DropdownId

OPS_Validator

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:
  • Target: numeric field

    • 1 - Numeric response is a number
    • 2 - Essay response is a number
    • 3 - Matrix cell response is a number
    • 4 - Tag field response is a number
    • 5 - Numeric response required
    • 6 - Essay response required
    • 7 - Matrix cell response required
    • 8 - Tag field response required
    • 9 - Maximum value for numeric response
    • 10 - Maximum value for an essay response
    • 11 - Maximum value for a matrix cell response
    • 12 - Maximum value for a tag field response
    • 13 - Minimum value for numeric response
    • 14 - Minimum value for an essay response
    • 15 - Minimum value for a matrix cell response
    • 16 - Minimum value for a tag field response
    • 17 - Valid range for numeric response
    • 18 - Valid range for an essay response
    • 19 - Valid range for a matrix cell response
    • 20 - Valid range for a tag field response

  • Target: text field

    • 1 - Free text required
    • 2 - Essay response required
    • 3 - Tag field response required
    • 4 - Matrix cell response required
    • 5 - Maximum text length for free text
    • 6 - Maximum text length for an essay field
    • 7 - Maximum text length for tag field
    • 8 - Maximum text length for matrix cell
    • 9 - Minimum text length for free text
    • 10 - Minimum text length for an essay field
    • 12 - Minimum text length for tag field
    • 13 - Minimum text length for matrix cell
    • 14 - Email Validator type for free text
    • 15 - Email Validator type for an essay field
    • 16 - Email Validator type for tag field
    • 17 - Email Validator type for matrix cell
    • 18 - Date type for free text
    • 19 - Date type for an essay field
    • 20 - Date type for tag field
    • 21 - Date type for matrix cell
    • 22 - Contains a word/text for free text
    • 23 - Contains a word/text for an essay field
    • 24 - Contains a word/text for tag field
    • 25 - Contains a word/text for matrix cell
    • 26 - Regular expressions for free text
    • 27 - Regular expressions for an essay field
    • 28 - Regular expressions for tag field
    • 29 - Regular expressions for matrix cell

  • Target: multiple-choice question

    • 1 - Response required
    • 2 - Minimum number of choices
    • 3 - Maximum number of choices
    • 4 - Number of choices

  • Target: rating question

    • 1 - Rating response required

  • Target: matrix question

    • 1 - Group response required
    • 2 - Group response ranking required
    • 3 - Response ranking, not required
    • 4 - Minimum number of choices for group
    • 5 - Maximum number of choices for group
    • 6 - Required number of choices for group
    • 7 - Minimum number of choices for group row
    • 8 - Maximum number of choices for group row
    • 9 - Required number of choices for group row
    • 10 - Minimum number of choices for group column
    • 12 - Maximum number of choices for group column
    • 13 - Required number of choices for group column
    • 14 - Sum of cells equals
    • 15 - Sum of cells is less then
    • 16 - Sum of cells is greater then

  • Target: essay question

    • 1 - Sum of field inputs equals
    • 2 - Maximum value for the sum of fields
    • 3 - Minimum value for the sum of fields

  • Target: dropdown

    • 1 - Dropdown response required
    • 2 - Minimum number of choices
    • 3 - Maximum number of choices
    • 4 - Minimum number of choices
    • 5 - Essay field response required
    • 6 - Matrix cell response required
    • 7 - Multiple response required

TargetType INTEGER Not null   Target type defines which field / question type this validator will validate.
  • 1 - numeric question/field
  • 2 - text field
  • 3 - multiple question
  • 4 - rating question
  • 5 - matrix question
  • 6 - essay field
  • 7 - dropdown question/field
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

Indices

Index Columns
Validator1 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK77 QuestionId OPS_Question.QuestionId
OPS_FK92 ValidatorId OPS_ValidatorAttribute.ValidatorId

OPS_ValidatorAttribute

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

Indices

Index Columns
ValidatorAttribute1 ValidatorId

Foreign keys

Foreign key Child Parent
OPS_FK92 ValidatorId OPS_Validator.ValidatorId

OPS_Branch

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
BranchId BIGINT Not null PK Unique id
BranchTitle VARCHAR(255) Null   Name of the branch condition
FilterArray TEXT 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.
LastQuestionNo INTEGER Not null   The last question number in the condition
Formula VARCHAR(255) Null   A formula used as a condition
SurveyId BIGINT Not null FK The survey id for the condition

Foreign keys

Foreign key Child Parent
OPS_FKXX OPS_Survey.SurveyId SurveyId

OPS_BranchRating

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:
  • 0 - type 'one', respondent must select one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
QuestionRatingId BIGINT Not null FK References OPS_QuestionRating table
BranchId BIGINT Not null FK References OPS_Branch table
FormulaId VARCHAR(5) Not null   A formula id
QuestionNo INTEGER Not null   The question number

Indices

Index Columns
BranchRating_Que1 QuestionRatingId

Foreign keys

Foreign key Child Parent
OPS_FK11 QuestionRatingId OPS_QuestionRating.QuestionRatingId
OPS_FK12 OPS_BranchRatingOption.BranchRatingId BranchRatingId
OPS_FKXX OPS_Branch.BranchId BranchId

OPS_BranchRatingOption

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

OPS_BranchMultiple

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:
  • 0 - type 'at least one', respondent must select at least one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
  • 2 - type 'all', respondent must select all values in the condition
BranchId BIGINT Not null FK References OPS_Branch table
FormulaId VARCHAR(5) Not null   A formula id
QuestionNo INTEGER Not null   The question number

Indices

Index Columns
BranchMultiple_Q1 QuestionMultipleId

Foreign keys

Foreign key Child Parent
OPS_FK7 QuestionMultipleId OPS_QuestionMultiple.QuestionMultipleId
OPS_FK8 OPS_BranchMultipleOption.BranchMultipleId BranchMultipleId
OPS_FKXX OPS_Branch.BranchId BranchId

OPS_BranchMultipleOption

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

OPS_BranchNumeric

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:
  • 0 - type 'max', condition evaluates to true if response value is less or equals the maximum value.
  • 1 - type 'min', condition evaluates to true if response value is greater or equals the minimum value
  • 2 - type 'range', condition evaluates to true if response value is in the specified range, inclusive bounds.
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
BranchId BIGINT Not null FK References OPS_Branch table
FormulaId VARCHAR(5) Not null   A formula id
QuestionNo INTEGER Not null   The question number

Indices

Index Columns
BranchNumeric_Qu1 QuestionNumericId

Foreign keys

Foreign key Child Parent
OPS_FK10 QuestionNumericId OPS_QuestionNumeric.QuestionNumericId
OPS_FKXX OPS_Branch.BranchId BranchId

OPS_BranchDropdown

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:
  • 0 - type 'at least one', respondent must select at least one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
  • 2 - type 'all', respondent must select all values in the condition
QuestionDropdownId BIGINT Not null FK References OPS_QuestionDropdown table
BranchId BIGINT Not null FK References OPS_Branch table
FormulaId VARCHAR(5) Not null   A formula id
QuestionNo INTEGER Not null   The question number

Indices

Index Columns
BranchDropdown_Q1 QuestionDropdownId
OPS_FKXX OPS_Branch.BranchId BranchId

Foreign keys

Foreign key Child Parent
OPS_FK1 QuestionDropdownId OPS_QuestionDropdown.QuestionDropdownId
OPS_FK2 OPS_BranchDropdownOption.BranchDropdownId BranchDropdownId

OPS_BranchDropdownOption

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.

Columns

Column Data type Allow NULLs Key Notes
TextValue VARCHAR(255) Null   The condition value (dropdown item value)
BranchDropdownId BIGINT Not null FK References OPS_BranchDropdown table
QuestionDropdownId BIGINT Not null FK References OPS_QuestionDropdown table

Indices

Index Columns
BranchDropdownOp1 BranchDropdownId
BranchDropdownOp2 QuestionDropdownId

Foreign keys

Foreign key Child Parent
OPS_FK2 BranchDropdownId OPS_BranchDropdown.BranchDropdownId
OPS_FK3 QuestionDropdownId OPS_QuestionDropdown.QuestionDropdownId

OPS_BranchMatrix

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:
  • 0 - type 'at least one', respondent must select at least one of the values in the condition
  • 1 - type '', respondent must not select any of the values in the condition
  • 2 - type 'all', respondent must select all values in the condition
QuestionMatrixId BIGINT Not null FK References OPS_QuestionMatrix table
BranchId BIGINT Not null FK References OPS_Branch table
FormulaId VARCHAR(5) Not null   A formula id
QuestionNo INTEGER Not null   The question number

Indices

Index Columns
BranchMatrix_Que1 QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK4 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId
OPS_FK5 OPS_BranchMatrixOption.BranchMatrixId BranchMatrixId
OPS_FKXX OPS_Branch.BranchId BranchId

OPS_BranchMatrixOption

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

Indices

Index Columns
BranchMatrixOpti1 QuestionMatrixId
BranchMatrixOpti2 BranchMatrixId
BranchMatrixOpti2 ColumnValue, RowValue, BranchMatrixId, QuestionMatrixId

Foreign keys

Foreign key Child Parent
OPS_FK5 BranchMatrixId OPS_BranchMatrix.BranchMatrixId
OPS_FK6 QuestionMatrixId OPS_QuestionMatrix.QuestionMatrixId

OPS_Invitation

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:
  • text/html
  • text/plain
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

Indices

Index Columns
Invitation_Surve1 SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK16 SurveyId OPS_Survey.SurveyId
OPS_FK18 OPS_Invitee.InvitationId InvitationId

OPS_Invitee

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:
  • 0 - invitation is pending
  • 1 - the invitation has been sent
  • 2 - reminder has been sent
  • 3 - error occurred while sending invitation
  • 4 - error occurred while sending reminder
  • 5 - email address is invalid
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:
  • true
  • false

Indices

Index Columns
Invitee_Responde1 RespondentId
Invitee_Invitati1 InvitationId

Foreign keys

Foreign key Child Parent
OPS_FK18 InvitationId OPS_Invitation.InvitationId
OPS_FK97 InviteeId OPS_InviteeAttribute.InviteeId

OPS_InviteeAttribute

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

Indices

Index Columns
InviteeAttribute_1 InviteeId

Foreign keys

Foreign key Child Parent
OPS_FK97 InviteeId OPS_Invitee.InviteeId

OPS_Respondent

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:
  • true
  • false
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

OPS_RespondentAttribute

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

Indices

Index Columns
RespondentAttribute_1 RespondentId

Foreign keys

Foreign key Child Parent
OPS_FK93 RespondentId OPS_Respondent.RespondentId

OPS_ResponseFreeText

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
ResponseDate BIGINT Null   The response timestamp

Indices

Index Columns
ResponseFreeText1 RespondentId
ResponseFreeText4 QuestionId
*ResponseFreeText5 QuestionId, RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK49 QuestionId OPS_Question.QuestionId
OPS_FK50 RespondentId OPS_Respondent.RespondentId

OPS_ResponseNumeric

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
ResponseDate BIGINT Null   The response timestamp

Indices

Index Columns
ResponseNumeric_1 QuestionId
ResponseNumeric_2 RespondentId
*ResponseNumeric_3 QuestionId, RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK67 QuestionId OPS_Question.QuestionId
OPS_FK68 RespondentId OPS_Respondent.RespondentId

OPS_ResponseEssay

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
ResponseDate BIGINT Null   The response timestamp

Indices

Index Columns
ResponseEssay_Qu1 QuestionId, FieldIndex
ResponseEssay1 RespondentId
*ResponseEssay2 QuestionId, RespondentId, FieldIndex

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK41 QuestionId OPS_Question.QuestionId
OPS_FK42 RespondentId OPS_Respondent.RespondentId
OPS_FK45 OPS_ResponseEssayNum.ResponseEssayId ResponseEssayId
OPS_FK48 OPS_ResponseEssayText.ResponseEssayId ResponseEssayId

OPS_ResponseEssayText

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)

Foreign keys

Foreign key Child Parent
OPS_FK46 QuestionId OPS_Question.QuestionId
OPS_FK47 RespondentId OPS_Respondent.RespondentId
OPS_FK48 ResponseEssayId OPS_ResponseEssay.ResponseEssayId

OPS_ResponseEssayNum

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

Indices

Index Columns
*ResponseEssayNum1 ResponseEssayId
ResponseEssayNum2 QuestionId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK43 QuestionId OPS_Question.QuestionId
OPS_FK44 RespondentId OPS_Respondent.RespondentId
OPS_FK45 ResponseEssayId OPS_ResponseEssay.ResponseEssayId

OPS_ResponseRating

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.
ResponseDate BIGINT Null   The response timestamp

Indices

Index Columns
*ResponseRating1 QuestionId, RespondentId
ResponseRating_Q1 QuestionId
ResponseRating_R1 RespondentId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK69 QuestionId OPS_Question.QuestionId
OPS_FK70 RespondentId OPS_Respondent.RespondentId

OPS_ResponseDropdown

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
ResponseDate BIGINT Null   The response timestamp

Indices

Index Columns
ResponseDropdown1 QuestionId, RespondentId
ResponseDropdown2 QuestionId, TextValue

Foreign keys

Foreign key Child Parent
OPS_FK39 QuestionId OPS_Question.QuestionId
OPS_FK40 RespondentId OPS_Respondent.RespondentId

OPS_ResponseMultipleOption

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.
ResponseDate BIGINT Null   The response timestamp

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

OPS_ResponseMultipleOptionText

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)

Foreign keys

Foreign key Child Parent
OPS_FK64 QuestionId OPS_Question.QuestionId
OPS_FK65 RespondentId OPS_Respondent.RespondentId
OPS_FK66 ResponseMultipleOptionId OPS_ResponseMultipleOption.ResponseMultipleOptionId

OPS_ResponseMultipleOptionNum

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

Indices

Index Columns
*ResponseMultiple6 ResponseMultipleOptionId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK61 QuestionId OPS_Question.QuestionId
OPS_FK62 RespondentId OPS_Respondent.RespondentId
OPS_FK63 ResponseMultipleOptionId OPS_ResponseMultipleOption.ResponseMultipleOptionId

OPS_ResponseMatrix

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
ResponseDate BIGINT Null   The response timestamp

Indices

Index Columns
*ResponseMatrix1 QuestionId, RespondentId, ColumnPosition, RowPosition
ResponseMatrix2 RespondentId, QuestionId

(* = Unique index)

Foreign keys

Foreign key Child Parent
OPS_FK51 QuestionId OPS_Question.QuestionId
OPS_FK52 RespondentId OPS_Respondent.RespondentId
OPS_FK55 OPS_ResponseMatrixNum.ResponseMatrixId ResponseMatrixId
OPS_FK58 OPS_ResponseMatrixText.ResponseMatrixId ResponseMatrixId

OPS_ResponseMatrixText

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)

Foreign keys

Foreign key Child Parent
OPS_FK56 QuestionId OPS_Question.QuestionId
OPS_FK57 RespondentId OPS_Respondent.RespondentId
OPS_FK58 ResponseMatrixId OPS_ResponseMatrix.ResponseMatrixId

OPS_ResponseMatrixNum

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)

Foreign keys

Foreign key Child Parent
OPS_FK53 QuestionId OPS_Question.QuestionId
OPS_FK54 RespondentId OPS_Respondent.RespondentId
OPS_FK55 ResponseMatrixId OPS_ResponseMatrix.ResponseMatrixId

OPS_HeaderFooter

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:
  • 1 - header
  • 2 - footer

OPS_InvitationList

Stores reusable list of invitees.

Columns

Column Data type Allow NULLs Key Notes
InvitationListId BIGINT Not null PK Unique id
InvitationListName VARCHAR(255) Not null   Name of the invitation list
SortOn INTEGER Null   Alphabetic sort value:
  • 0 - off
  • 1 - on

Foreign keys

Foreign key Child Parent
OPS_FK17 OPS_InvitationListItem.InvitationListId InvitationListId

OPS_InvitationListItem

One record for one invitee in reusable invitation list.

Columns

Column Data type Allow NULLs Key Notes
ItemId BIGINT Not Null PK Unique id
Email 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

Indices

Index Columns
InvitationListIt1 InvitationListId

Foreign keys

Foreign key Child Parent
OPS_FK17 InvitationListId OPS_InvitationList.InvitationListId
OPS_FK118 OPS_InvitationListItemAttr.ItemId ItemId

OPS_InvitationListItemAttr

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

Indices

Index Columns
InvListItemAttr ItemId

Foreign keys

Foreign key Child Parent
OPS_FK118 ItemId OPS_InvitationListItemAttr.ItemId

OPS_Report

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:
  • true
  • false
AvailableForResp VARCHAR(5) Null   True if respondents can access the report. Values:
  • true
  • false
IncludeIncompl VARCHAR(5) Null   True if the report will include incomplete responses. Values:
  • true
  • false
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:
  • 0 - main report
  • 1 - drilldown report
Status INTEGER Null   Report status
  • 0 - active report, applies both for main and drilldowns
  • 1 - scheduled for deletion. For now applies only to drilldowns

Indices

Index Columns
Report_SurveyId1 SurveyId

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

OPS_ReportAttribute

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

Indices

Index Columns
ReportAttr1 ReportId

Foreign keys

Foreign key Child Parent
OPS_FK88 ReportId OPS_Report.ReportId

OPS_ReportElement

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}
  • 1 - summary report
  • 2 - detailed statistics
  • 3 - cross tabulation
  • 4 - free text comments
  • 5 - comments in multiple choice 'other'-field
  • 6 - comments in matrix cells (cells of type text)
  • 7 - comments in essay fields of type text
  • 8 - horizontal bar chart
  • 9 - vertical bar chart
  • 10 - pie chart
ShowNonSelectedItems VARCHAR(5) Null   True is non selected options should be displayed in the report element. True/false

Indices

Index Columns
ReportElement_Re1 ReportId

Foreign keys

Foreign key Child Parent
OPS_FK35 ReportId OPS_Report.ReportId
OPS_FK36 OPS_ReportElementQuestionNo.ReportElementId ReportElementId
OPS_FK89 OPS_ReportElementAttribute.ReportElementId ReportElementId

OPS_ReportElementAttribute

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

Indices

Index Columns
ReportElementAttr1 ReportId
ReportElementAttr2 ReportElementId

Foreign keys

Foreign key Child Parent
OPS_FK89 ReportElementId OPS_ReportElement.ReportElementId
OPS_FK90 ReportId OPS_Report.ReportId

OPS_ReportElementQuestionNo

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

Indices

Index Columns
ReportElementQue1 ReportId
ReportElementQue2 ReportElementId
ReportElementQue3 ReportId, ReportElementId, QuestionNumber

Foreign keys

Foreign key Child Parent
OPS_FK36 ReportElementId OPS_ReportElement.ReportElementId

OPS_ReportQuestionInterval

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
  • 0 - integer
  • 1 - decimal
TargetType INTEGER Not null   Target id for numeric intervals:
  • 1 - numeric question
  • 2 - numeric in-text element
  • 3 - numeric matrix cell
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

Indices

Index Columns
QuestionInterval1 QuestionId, ReportId
QuestionInterval2 TargetType, MappingId, QuestionId, ReportId

Foreign keys

Foreign key Child Parent
OPS_FK101 ReportId OPS_Report.ReportId

OPS_ReportQuestionLabel

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:
  • 1 - question
  • 2 - in-text element
  • 3 - matrix cell
  • 4 - matrix group
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

Indices

Index Columns
QuestionLabel1 QuestionId, ReportId
QuestionLabel2 TargetType, MappingId, QuestionId, ReportId

Foreign keys

Foreign key Child Parent
OPS_FK100 ReportId OPS_Report.ReportId

OPS_QuestionCondition

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:
  • 0 - type 'at least one', some of the values selected
  • 1 - type '', of the values selected
  • 2 - type 'all', all values are selected
FilterArray VARCHAR Null   Which question to filter out is condition is true. (not is use)
QuestionId BIGINT Not null FK References OPS_Question table

Indices

Index Columns
QuestionCond1 QuestionId

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

OPS_QuestionConditionOption

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

Indices

Index Columns
QuestionCondOp1 QuestionConditionId
QuestionCondOp2 QuestionId

Foreign keys

Foreign key Child Parent
OPS_FK80 QuestionConditionId OPS_QuestionCondition.QuestionConditionId

OPS_ReportFilter

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:
  • 0 - include respondents meeting conditions
  • 1 - exclude respondents meeting conditions
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
LastNDays INTEGER Null   Filter on last number of days

Indices

Index Columns
ReportFilter1 ReportId

Foreign keys

Foreign key Child Parent
OPS_FK81 ReportId OPS_Report.ReportId
OPS_FK83 OPS_ReportFilter_QuestionCond.ReportFilterId ReportFilterId

OPS_ReportFilter_QuestionCond

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

Indices

Index Columns
ReportFilQCond1 ReportFilterId
ReportFilQCond2 ReportFilterId, QuestionConditionId

Foreign keys

Foreign key Child Parent
OPS_FK82 QuestionConditionId OPS_QuestionCondition.QuestionConditionId
OPS_FK83 ReportFilterId OPS_ReportFilter.ReportFilterId

OPS_ReportDrilldown

One record represents one report drilldown.

Columns

Column Data type Allow NULLs Key Notes
ReportDrillDownId BIGINT Not null PK Unique id
ReportDrillDownKey VARCHAR(255) Not null   Key with drilldown info
ReportId BIGINT Not null FK References OPS_Report table

Indices

Index Columns
Drilldown1 ReportDrillDownKey, ReportId

Foreign keys

Foreign key Child Parent
OPS_FK99 ReportId OPS_Report.ReportId

OPS_ReportRespondent

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

Indices

Index Columns
ReportRespondent1 ReportId
ReportRespondent2 RespondentId

Foreign keys

Foreign key Child Parent
OPS_FK84 ReportId OPS_Report.ReportId
OPS_FK85 RespondentId OPS_Respondent.RespondentId

OPS_ReportFile

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)

Indices

Index Columns
ReportFile1 ReportId
ReportFile2 ReportId, FileType

OPS_ReportPortal

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

OPS_ReportPortalAttribute

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

Indices

Index Columns
ReportPortalAtt1 AttributeName, ReportPortalId

Foreign keys

Foreign key Child Parent
OPS_FK117 ReportPortalId OPS_ReportPortal.ReportPortalId

OPS_ReportPortalItem

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:
  • 0 - text item
  • 1 - link 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

Indices

Index Columns
ReportPortalItem1 ItemIndex, ReportPortalId

Foreign keys

Foreign key Child Parent
OPS_FK116 ReportPortalId OPS_ReportPortal.ReportPortalId

OPS_Panel

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.

Indices

Index Columns
Panel1 UserId

Foreign keys

Foreign key Child Parent
OPS_FK104 OPS_PanelSignUpSurvey.PanelId PanelId
OPS_FK106 OPS_PanelCategory .PanelId PanelId
OPS_FK109 OPS_Panelist.PanelId PanelId
OPS_FK112 OPS_PanelistUnverified.PanelId PanelId
OPS_FK113 OPS_SampleDefinition.PanelId PanelId

OPS_PanelAttribute

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:
  • 0 - short text
  • 1 - long text
  • 2 - selection
  • 3 - checkbox
  • 4 - date
  • 5 - INTEGER
  • 6 - decimal
PanelAttributePosition INTEGER Not null   Sequential position of the attribute
SelectionMode INTEGER Null   Type of the selection, applies if PanelAttributeType is Selection:
  • 0 - single selection
  • 1 - multiple selection
SelectionType INTEGER Null   Selection type
  • 0 - dropdown list
  • 1 - radio buttons/checkboxes
DatePattern VARCHAR(255) Null   Date pattern for attribute of type Date
PanelCategoryId BIGINT Not null FK References OPS_PanelCategory

Indices

Index Columns
PanelAttribute1 PanelAttributePosition, PanelCategoryId

Foreign keys

Foreign key Child Parent
OPS_FK108 OPS_PanelAttributeItem.PanelAttributeId PanelAttributeId
OPS_FK107 PanelCategoryId OPS_PanelCategory.PanelCategoryId

OPS_PanelAttributeItem

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

Indices

Index Columns
PanelAttrItem1 PanelAttributeItemPosition, PanelAttributeId

Foreign keys

Foreign key Child Parent
OPS_FK108 PanelAttributeId OPS_PanelAttribute.PanelAttributeId

OPS_PanelCategory

One record represents one panel category

Columns

Column Data type Allow NULLs Key Notes
PanelCategoryId BIGINT Not null PK Unique id
PanelCategoryName VARCHAR(255) Not null   Name of thep anel category
PanelCategoryPosition INTEGER Not null   Sequential position
PanelId BIGINT Not null FK References OPS_Panel

Indices

Index Columns
PanelCategory1 PanelCategoryPosition, PanelId

Foreign keys

Foreign key Child Parent
OPS_FK107 OPS_PanelAttribute.PanelCategoryId PanelCategoryId
OPS_FK106 PanelId OPS_Panel.PanelId

OPS_PanelSignUpSurvey

One record represents mapping between panel and sign-up survey.

Columns

Column Data type Allow NULLs Key Notes
PanelId BIGINT Not null FK References OPS_Panel
SurveyId BIGINT Not null FK References OPS_Survey

Indices

Index Columns
PanelSignUpSurvey1 PanelId, SurveyId

Foreign keys

Foreign key Child Parent
OPS_FK104 PanelId OPS_Panel.PanelId
OPS_FK105 SurveyId OPS_Survey.SurveyId

OPS_Panelist

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
  • 0 = subscribed
  • 1 = unsubscribed
PanelId BIGINT Not null FK References OPS_Panel

Indices

Index Columns
Panelist1 PanelistEmail, PanelistId

Foreign keys

Foreign key Child Parent
OPS_FK109 PanelId OPS_Panel.PanelId
OPS_FK110 OPS_PanelistAttribute.PanelistId PanelistId
OPS_FK111 OPS_PanelistHistory.PanelistId PanelistId

OPS_PanelistAttribute

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

Indices

Index Columns
TODO TODO

Foreign keys

Foreign key Child Parent
OPS_FK110 PanelistId OPS_Panelist.PanelistId

OPS_PanelistHistory

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:
  • 0 = invited to a survey
  • 1 = reminder sent
  • 1 = reminder sent
  • 2 = panelist has started a survey
  • 3 = panelist has completed a survey
  • 4 = panelist has saved a survey to come back later
  • 5 = panelist has left a survey without answering
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.

Foreign keys

Foreign key Child Parent
OPS_FK111 PanelistId OPS_Panelist.PanelistId

OPS_PanelUnverified

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

Foreign keys

Foreign key Child Parent
OPS_FK112 PanelId OPS_Panel.PanelId

OPS_SampleDefinition

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:
  • 1 = Randomly select the panelists
  • 2 = Select the panelists that least recently have been invited
  • 3 = Select the panelists that least recently have responded
  • 4 = Select the panelists that have been invited least
  • 5 = Select the panelists that have responded least
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

Foreign keys

Foreign key Child Parent
OPS_FK114 OPS_SampleCondition.SampleDefinitionId SampleDefinitionId
OPS_FK113 PanelId OPS_Panel.PanelId

OPS_SampeCondition

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
  • 0 = at least one
  • 1 = all
SearchType INTEGER Not null   Search type for text attributes
  • 0 = search for text
  • 1 = regular expression

Indices

Index Columns
SampleCondition1 SampleDefinitionId, PanelAttributeId

Foreign keys

Foreign key Child Parent
OPS_FK115 OPS_SampleConditionValue.SampleConditionId OPS_SampleConditionValue
OPS_FK114 SampleDefinitionId OPS_SampleDefinition.SampleDefinitionId

OPS_SampeConditionValue

One record represents one sample condition value (selection item value)

Columns

Column Data type Allow NULLs Key Notes
SampleConditionValueId BIGINT Not null PK Unique id
SampleConditionId BIGINT Not null FK References OPS_SampleCondition table
StringValue VARCHAR(255) Null   Condition value

Foreign keys

Foreign key Child Parent
OPS_FK115 SampleConditionId OPS_SampleCondition.SampleConditionId

OPS_ScheduledTask

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:
  • RUNNING - the task is running
  • PENDING - the task is pending
  • COMPLETED - the task is completed
  • ERROR - error in the task
  • STOPPED - the task is stopped
Executor VARCHAR(50) Null   The task executor

OPS_FailedEmailMsg

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:
  • text/html
  • text/plain
EntryDate BIGINT Null   Entry date

Foreign keys

Foreign key Child Parent
OPS_FK15 OPS_FailedEmailRecipient.FailedEmailMsgId FailedEmailMsgId

OPS_FailedEmailRecipient

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

Indices

Index Columns
FailedEmailRecip1 FailedEmailMsgId

Foreign keys

Foreign key Child Parent
OPS_FK15 FailedEmailMsgId OPS_FailedEmailMsg.FailedEmailMsgId

OPS_Host

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

Indices

Index Columns
Host_1 HostId

Foreign keys

Foreign key Child Parent
OPS_FK91 OPS_SystemInfo.SystemInfoId SystemInfoId

OPS_UploadedFile

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

Indices

Index Columns
UploadedFile1 UploadedDate

OPS_UploadedFileCheck

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

Indices

Index Columns
UploadedFileCheck1 UniqueMemberId

OPS_PluginProperty

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

Indices

Index Columns
PluginProperty1 PropertyId

OPS_SequenceID

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

OPS_ContextGuideItem

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

Indices

Index Columns
ContextGuideItem1 ResourceId

OPS_RecentItem

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

Indices

Index Columns
RecentItem1 UserId
RecentItem2 UserId, ItemIndex, ResourceId, ResourceType

Foreign keys

Foreign key Child Parent
OPS_FK98 UserId OPS_User.UserId