CMS DB Structure
ERD Diagram
Tables
- admins
- announcements
- attachments
- contests
- datasets
- evaluations
- executables
- files
- fsobjects
- managers
- messages
- participations
- printjobs
- questions
- statements
- submission_results
- submissions
- tasks
- teams
- testcases
- tokens
- users
- user test executables
- user test files
- user test managers
- user test results
- user tests
admins
(This table stores the names and tokens of administrators)
| Idx | Field Name | Data Type | Description |
|---|---|---|---|
| PK | id | int4 | Autogenerated unique ID value |
| name | varchar | Full name of administrator, free-type field | |
| username | varchar | Unique username of administrator, free-type field | |
| authentication | varchar | Authentification token | |
| enabled | bool | Account state: enabled or disabled, binary field | |
| permission_all | bool | Superuser permissions: true or false, binary field | |
| permission_messaging | bool | Messaging privilege: true or false, binary field |
| Name | On field |
|---|---|
| admins_pkey | id |
| admins_username_key | username |
announcements
(This table stores the announcements made during the contests)
| Idx | Field Name | Data Type | Description |
|---|---|---|---|
| PK | id | int4 | Autogenerated unique ID value |
| timestamp | timestamp | Timestamp of the announcement | |
| subject | varchar | Subject of the announcement | |
| text | varchar | Text of the body of the announcement | |
| FK | contest_id | int4 | Unique ID of the contest announcement pretains to, child of contests table |
| FK | admin_id | int4 | Unique ID of the administrator publishing the announcement, child of admins table |
| Name | On field |
|---|---|
| announcement_pkey | id |
attachments
(This table stores the attachements for the tasks)
| Idx | Field Name | Data Type | Description |
|---|---|---|---|
| PK | id | int4 | Autogenerated unique ID value |
| FK | task_id | int4 | Unique indentifier of the task this attachment belongs to, child of tasks |
| filename | varchar | Name of the attachement, free-type field | |
| digest | varchar | Stores the hash (digest of hash function) of the attachement |
| Name | On field |
|---|---|
| attachments_pkey | id |
| attachments_task_id_filename_key | task_id + filename |
contests
(This table stores the contests)
| Idx | Field Name | Data Type | Description |
|---|---|---|---|
| PK | id | int4 | Autogenerated unique ID value |
| name | varchar | Name of the contest | |
| description | varchar | Description of the contest | |
| allowed_localizations | varchar | Lists localization available within this contest | |
| languages | varchar | Lists programming languages allowed within this contest | |
| submissions_download_allowed | bool | Can submission be downloaded: true or false, binary field | |
| allow_questions | bool | Are questions allowed: true or false, binary field | |
| allow_user_tests | bool | Are user tests allowed: true or false, binary field | |
| block_hidden_participations | bool | Are hidden participations allowed: true or false, binary field | |
| allow_password_authentication | bool | Are authentication via password allowed: true or false, binary field | |
| ip_restriction | bool | Is IP restriction enforced: true or false, binary field | |
| ip_autologin | bool | Is autologin by IP allowed: true or false, binary field | |
| token_mode | token_mode | This field is of special datatype and regulates whether this contest uses tokens. Can be "disabled", "infinite" or "finite" | |
| token_max_number | int4 | If token mode is set to "finite", this field sets the maximum number of tokens available in this contest, otherwise left empty | |
| token_min_interval | interval | If token mode is used and the token has been used, this field defines the minimum amount of time contestant has to wait before another token can be used | |
| token_gen_initial | int4 | If token mode is set to "finite", this field defines the amount of tokens available when contest begins. Counter decrements by 1 each time contestant uses a token | |
| token_gen_number | int4 | If token mode is set to "finite", this field defines the amount of tokens added every selected interval to the contestants. | |
| token_gen_interval | interval | If token mode is set to "finite", this field defines the interval between new tokens generation. | |
| token_gen_max | int4 | If token mode is set to "finite", this field defines the maximum amount of tokens that can be generated in this contest | |
| start | timestamp | Timestamp of the contest start | |
| stop | timestamp | Timestamp of the contest finish | |
| analysis_enabled | bool | Is contest analysis enabled: true or false, binary field | |
| analysis_start | timestamp | Timestamp of the analysis start | |
| analysis_stop | timestamp | Timestamp of the analysis finish | |
| timezone | varchar | Timezone of the contest | |
| per_user_time | interval | If set to non-null value, this fields regulate the amount of time contestant can participate in the timeframe of the contest | |
| max_submission_number | int4 | This field sets the maximum number of submission each participant can make per contest | |
| max_user_test_number | int4 | This field sets the maximum number of tests each participant can make | |
| min_submission_interval | interval | The minimum amount of time, in seconds, the user is required to wait after a submission has been submitted before next one can be submitted | |
| min_user_test_interval | interval | The minimum amount of time, in seconds, the user is required to wait after a test has been submitted before next one can be submitted | |
| score_precision | int4 | This field sets the precision of the score showed to participants in amount of decimals in float number |
| Name | On field |
|---|---|
| contests_pkey | id |
| contests_name_key | name |
Table datasets
(This table stores the datasets) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK task_id int4 Unique indentifier of the task this dataset belongs to, child of tasks description varchar Description of the dataset autojudge bool Is auto judging enabled for this dataset: true or false, binary field time_limit float8 If used, sets the time limit on dataset availability memory_limit int8 Limits the size of the dataset task_type varchar Stores the type of task this dataset is associated with task_type_parameters jsonb Stores a json of task parameters score_type varchar Type of scoring associated with this dataset score_type_parameters jsonb Stores a json of scoring parameters Indexes datasets_id_task_id_key ON id + task_id datasets_pkey ON id datasets_task_id_description_key ON task_id + description
Table evaluations
(This table stores the evaluations) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK submission_id int4 Unique ID of the submission, child of submissions FK dataset_id int4 Unique ID of the dataset, child of datasets FK testcase_id int4 Unique ID of the testcase, child of testcases outcome varchar Stores the outcome of the evaluation text varchar The text of the evaluation, expanding on outcome execution_time float8 The amount of CPU time it took to execute the subject of evaluation execution_wall_clock_time float8 The total amount of time it took to execute the subject of evaluation execution_memory int8 The amount of memory used during evaluation evaluation_shard int4 The amount of shards used evaluation_sandbox varchar Sandbox used in evaluation Indexes evaluations_pkey ON id evaluations_submission_id_dataset_id_testcase_id_key ON submission_id + dataset_id + testcase_id
Table executables
(This table stores the executables submitted by contestants) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK submission_id int4 Unique ID of the submission, child of submissions FK dataset_id int4 Unique ID of the dataset, child of datasets filename varchar Filename of the executable digest varchar Stores the hash (digest of hash function) of the executable Indexes executables_pkey ON id executables_submission_id_dataset_id_filename_key ON submission_id + dataset_id + filename
Table files
(This table stores the files submitted by contestants) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK submission_id int4 Unique ID of the submission, child of submissions filename varchar Name of the file digest varchar Stores the hash (digest of hash function) of the file Indexes files_pkey ON id files_submission_id_filename_key ON submission_id + filename
Table fsobjects
(This table stores the log of object-related events) Idx
Field Name Data Type Description PK digest varchar Stores the hash (digest of hash function) of the event loid oid Unique object ID description varchar Decription of the event Indexes fsobjects_pkey ON digest
Table managers
(This table stores the so-called managers, grading scripts for allowed languages per dataset) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK dataset_id int4 Unique ID of the dataset, child of datasets filename varchar The filename of the manager digest varchar Stores the hash (digest of hash function) of the manager Indexes managers_pkey ON id managers_dataset_id_filename_key ON dataset_id + filename
Table messages
(This table stores direct messages sent to contestants) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value timestamp timestamp Timestamp of when the message was sent subject varchar Subject of the message text varchar Text of the message FK participation_id int4 Unique ID of the participant to whom the message was sent, child of participants FK admin_id int4 Unique ID of the administrator who sent the message, child of administrators. Can be null Indexes messages_pkey ON id
Table participations
(This table stores the access of users to the contests) Idx
Field Name Data Type Description PK id int4 A unique ID of each participation ip varchar If access is login based, then the IP or mask is stored here starting_time varchar the start time of the access delay_time varchar If participation of contestant was delayed, the delay time is stored here extra_time varchar an extension time for the particular user password varchar a password (plan or encrypted) for accessing a contests hidden bool if '1', then the scores of the user will not be shown in Ranking unrestricted bool If '1', then participation is not restricted FK contest_id int4 Unique ID of the contest, child of contests FK user_id int4 Unique ID of the user, child of users FK team_id int4 Unique ID of the user's team, child of teams Indexes participations_pkey ON id participations_contest_id_user_id_key ON contest_id + user_id
Table print jobs
(This table stores the print jobs log) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK participation_id int4 Unique participation ID, child of participations timestamp timestamp Timestamp of the print job filename varchar Printed filename digest varchar Stores the hash (digest of hash function) of the printjob done bool Is the print job done: true or false, binary field status varchar Status of the job Indexes printjobs_pkey ON id
Table questions
(This table stores requests and questions from the participants during the contest) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value question_timestamp timestamp Precise timestamp of the request subject varchar Subject of the request or question text varchar The body of the request or question reply_timestamp timestamp Timestamp of the reply by staff ignored bool Is question ignored: true or false, binary field reply_subject varchar The subject of the reply to the question or request reply_text varchar The body of the reply FK participation_id int4 Unique ID of participant, child of participations FK admin_id int4 Unique ID of administrator, child of admins. Can be null Indexes questions_pkey ON id
Table statements
(This table stores task statements in different languages) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK task_id int4 Unique task ID, child of tasks language varchar Language code of the statement digest varchar Stores the hash (digest of hash function) of the statement Indexes statements_pkey ON id statements_task_id_language_key ON task_id + language
Table submission_results
(This table stores the result of each submission) Idx
Field Name Data Type Description FK | PK id int4 Unique ID of the submission, child of submissions, part of Primary Key FK | PK dataset_id int4 Unique ID of the dataset, child of datasets, part of Primary Key compilation_outcome compilation_outcome Stores the compilation outcome in a special datatype: can be ok or fail compilation_text varchar Stores the status of the compilation compilation_tries int4 Stores the compilation tries count compilation_stdout varchar Stores the output of the compilator compilation_stderr varchar Stores the error produced by the compilator compilation_time float8 The amount of CPU time spent on compilation compilation_wall_clock_time float8 Total amount of time spent on compilation compilation_memory int8 The amount of memory used by the compilator compilation_shard int4 Shards split by the compilator compilation_sandbox varchar Stores the location of the sandbox used in compilation evaluation_outcome evaluation_outcome Stores the outcome of the evaluation in a special datatype, can be either OK or null evaluation_tries int4 Amount of [additional] evaluation tries score float8 Score of the evaluation score_details jsonb Stores json object with scoring details public_score float8 Score of the evaluation, available to public public_score_details jsonb Stores json object with public scoring details ranking_score_details varchar Details and ranks Indexes submission_results_pkey ON submission_id + dataset_id
Table submissions
(This table stores submission details) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK participation_id int4 Unique ID of the participation, child of participations FK task_id int4 Unique ID of the task, child of tasks timestamp timestamp Precise timestamp of the submission language varchar Programming language of the submission comment varchar Free type field for comments official bool Is the submission official: true or false, binary field Indexes submissions_pkey ON participation_id
Table tasks
(This table stores tasks information) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value num int4 Order of this task in the contest FK contest_id int4 Unique ID of the contest this task belongs to, child of contests name varchar Name of the task title varchar Title of the task submission_format varchar Format, in which submissions will be accepted primary_statements varchar Associated primary statement, if available. Most likely to be referring to the language of the statement token_mode token_mode This field is of special datatype and regulates whether this task uses tokens. Can be "disabled", "infinite" or "finite" token_max_number int4 If token mode is set to "finite", this field sets the maximum number of tokens available for this task, otherwise left empty token_min_interval interval If token mode is used and the token has been used, this field defines the minimum amount of time contestant has to wait before another token can be used token_gen_initial int4 If token mode is set to "finite", this field defines the amount of tokens available when task begins. Counter decrements by 1 each time contestant uses a token token_gen_number int4 If token mode is set to "finite", this field defines the amount of tokens added every selected interval to the contestants. token_gen_interval interval If token mode is set to "finite", this field defines the interval between new tokens generation. token_gen_max int4 If token mode is set to "finite", this field defines the maximum amount of tokens that can be generated for this task max_submission_number int4 This field sets the maximum number of submission each participant can make per task max_user_test_number int4 This field sets the maximum number of tests each participant can make per task min_submission_interval interval The minimum amount of time, in seconds, the user is required to wait after a submission has been submitted before next one can be submitted min_user_test_interval interval The minimum amount of time, in seconds, the user is required to wait after a test has been submitted before next one can be submitted feedback_level varchar Level of feedback for this task score_precision int4 Scoring precision for this task score_mode score_mode This field is of special datatype and regulates how this task will be scored FK dataset_id int4 Unique ID of the dataset used for this task, child of datasets Indexes tasks_pkey ON id tasks_contest_id_name_key ON contest_id + name tasks_contest_id_num_key ON contest_id + num tasks_name_key ON name
Table teams
(Stores the list of teams, like 'AZE', 'FRA' and 'USA') Idx
Field Name Data Type Description PK id int4 The unique ID of the team code varchar A short description of the team. In IOI, it's a country code in ISO 3166-1 alpha-3 code format name varchar Full description of the team. In IOI, the name of the country. Indexes teams_pkey ON id teams_code_key ON code
Table testcases
(Stores the details of testcases) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK dataset_id int4 Unique ID of the dataset used for this testcase, child of datasets codename varchar Codename given to the testcase public bool Is this testcase public: true or false, binary field input varchar Input for the testcase output varchar Output of the testcase Indexes testcases_pkey ON id testcases_dataset_id_codename_key ON dataset_id + codename
Table tokens
(Stores tokens given or used if token_mode is enabled) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK submission_id int4 Unique value of the submission that used this token, child of submissions timestamp timestamp Timestamp of consumption Indexes tokens_pkey ON id tokens_submission_id_key ON submission_id
Table users
(This table stores the list of all entered users. Adding a user to CMS does not guarantee the access to the contests. To grant an access to the contests, user shall exists in a participation table) Idx
Field Name Data Type Description PK id int4 A unique ID of the user first_name varchar First name last_name varchar Last name username varchar Login name of the user. In IOI, it's usually associated with the country code. Example: 'AZE1', 'ITA3' and 'SWE2'. Caution: avoid using "-" sign in logins, like 'SGP-4'. password varchar A password of the user is stored here. It can be plain or encrypted text email varchar Email of the user timezone varchar Timezone of the user preferred_languages varchar Indexes users_pkey ON id users_username_key ON username
Table user test executables
(This table stores the test executables information ) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK user_test_id int4 Unique ID of the user test, child of user_tests FK dataset_id int4 Unique ID of the dataset used, child of datasets filename varchar Filename of the test executable digest varchar Stores the hash (digest of hash function) of the test executable Indexes user_test_executables_pkey ON id user_test_executables_user_test_id_dataset_id_filename_key ON user_test_id + dataset_id + filename
Table user test files
(This table stores the test files information ) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK user_test_id int4 Unique ID of the user test, child of user_tests filename varchar The name of the test file digest varchar Stores the hash (digest of hash function) of the test file Indexes user_test_files_pkey ON id user_test_files_user_test_id_filename_key ON user_test_id + filename
Table user test managers
(This table stores the so-called test managers, grading scripts for allowed languages per dataset ) Idx
Field Name Data Type Description PK id int4 Autogenerated unique ID value FK user_test_id int4 Unique ID of the user test, child of user_tests filename varchar Name of the file containing test manager digest varchar Stores the hash (digest of hash function) of the test manager file Indexes user_test_managers_pkey ON id user_test_managers_user_test_id_filename_key ON user_test_id + filename
Table user test results
(This table stores the results of test evaluations ) Idx
Field Name Data Type Description PK | FK user_test_id int4 Unique ID of the user test, child of user_tests, part of the primary key PK | FK dataset_id int4 Unique ID of the dataset, child of datasets, part of the primary key output varchar Output of the test compilation_outcome varchar Compilation outcome of the test compilation compilation_text varchar Text of the compilation compilation_tries int4 Amount of times compilation was attempted compilation_stdout varchar Output of the compiler compilation_stderr varchar Error output of the compiler compilation_time float8 Amount of CPU time used during compilation compilation_wall_clock_time float8 Total amount of time used during compilation compilation_memory int8 Amount of memory used during compilation compilation_shard int4 Shards used during compilation compilation_sandbox varchar Address of the sandbox used for compilation evaluation_outcome varchar The outcome of the test evaluation evaluation_text varchar Text of the evaluation evaluation_tries int4 Amount of tries evaluation was attempted execution_time float8 Amount of CPU time used during execution execution_wall_clock_time float8 Total amount of time used during execution execution_memory int8 Amount of memory used during execution evaluation_shard int4 Amount of shards evaluation_sandbox varchar Address of the sandbox used for evaluation Indexes user_test_results_pkey ON user_test_id + dataset_id
Table user tests
(This table stores the user tests details ) Idx
Field Name Data Type Description PK id int4 FK participation_id int4 Unique ID of the participation, child of participations FK task_id int4 Unique ID of the task, child of tasks timestamp timestamp Precise timestamp of the test language varchar Programming language used input varchar Input for the test Indexes ix_user_tests_participation_id ON participation_id user_tests_pkey ON id