Data Dictionary
List of tables in Cassandra database used in LMS service
sunbird_courses.question [PRIMARY KEY: id] - (User Defined Type - used in sunbird_courses.assessment_aggregator)
Column Name | Data Type | Description | Sample Data |
---|---|---|---|
id | text | identifier of the question | do_2129173993575219201147 |
assess_ts | timestamp | Time at which the question was attempted by the user | 2020-02-12 05:02:18.172000+0000 |
max_score | double | Maximum score that can be secured on the Question | 1 |
score | double | Score secured by the user on the Question | 0 |
type | text | Type of the Question | mcq |
title | text | Title of the Question | Explore Question 1 |
resvalues | list<frozen<map<text, text>>> | Question Response Value | [{'2': '{"text":"Chlorophyll\n"}'}] |
params | list<frozen<map<text, text>>> | List of Question Parameters | [{'1': '{"text":"Stomata\n"}'}, {'2': '{"text":"Chlorophyll\n"}'}, {'3': '{"text":"Chloroplast\n"}'}, {'4': '{"text":"Guard cells (correct)\n"}'}, {'answer': '{"correct":["4"]}'}] |
description | text | Question Description | Sample Question |
duration | decimal | time in seconds | 2.0 |
sunbird_courses.user_content_consumption [PRIMARY KEY (userid, contentid, batchid, courseid)]
Table used to store user content consumption status as part of Course consumption.
Column Name | Data Type | Description | Sample Data |
---|---|---|---|
userid | text | UUID of the user | e44c93fb-ca7f-4d14-a8cb-c10144fc34c4 |
contentid | text | Content Identifier whose consumption details is recorded in the row | do_2129175755621335041147 |
batchid | text | Batch Identifier enrolled by the user | 01291957301247180833 |
courseid | text | Course Identifier to which Batch belongs to | do_2129195698820055041246 |
completedcount | text | 1 | |
| text | Not used | |
datetime | timestamp | ||
| text | Not used | |
lastaccesstime | text | Last Content access time | 2020-06-04 13:10:31:296+0000 |
lastcompletedtime | text | Last Content Completion time | 2020-06-04 12:52:23:964+0000 |
lastupdatedtime | text | Latest record updated time | 2020-06-04 13:10:31:307+0000 |
progress | int | Maximum Content Consumption Progress percentage irrespective of view | 70 |
result | text | ||
| text | Not used | |
status | int | Content consumption completion status | 2 |
viewcount | int | Number of times user has consumed the content | 7 |
sunbird_courses.user_activity_agg [PRIMARY KEY ((activity_type, activity_id, user_id), context_id)]
Table used to store User's Course consumption progress aggregates
Column Name | Data Type | Description | Sample Data |
---|---|---|---|
activity_type | text | Type of the activity - Course, CourseUnit, Quiz etc | Course |
activity_id | text | Id of the activity - course_id, content_id etc | do_21282054143234048014178 |
user_id | text | User Id | 9ac843e9-47ee-4019-80ad-4ab23bf96045 |
context_id | text | Context in which the activity happened. Combination of type:value. | cb:01282120178297241653 |
| map<text, int> | Number of contents consumed by the user in the course Not used now. Migrated to aggregates column. aggregates column is created to get the attempts counts. | {'completedCount': 2} |
agg_details | list<text> | Aggregate metrics for the user and activity combination | ['{"max_score":5.0,"score":4.0,"type":"attempt_metrics","attempt_id":"19ac65ea5ec2e9ed9d29aacdbd46fddd","content_id":"do_212959942036119552130","attempted_on":1582086185}'] |
agg_last_updated | map<text, timestamp> | When did the aggregate metrics were last updated? | {'completedCount': '2020-08-19 05:55:48.000000+0000', 'max_score:do_212959942036119552130': '2021-12-02 06:42:17.037000+0000', 'score:do_212959942036119552130': '2021-12-02 06:42:17.037000+0000'} |
aggregates | map<text, double> | consumption aggregate metrics of the assessments in the course | {'attempts_count:do_212959942036119552130': 1, 'completedCount': 2, 'max_score:do_212959942036119552130': 5, 'score:do_212959942036119552130': 4} |
sunbird_courses.assessment_aggregator [PRIMARY KEY ((user_id, course_id), batch_id, content_id, attempt_id)]
Table used to store User's Assessment Contents' consumption aggregates
Column Name | Data Type | Description | Sample Data |
---|---|---|---|
user_id | text | UUID of the user | 30b2571f-08f9-49ce-b97a-c643df0c82f7 |
course_id | text | Course Identifier | do_21295500947955712011099 |
batch_id | text | Batch Identifier | 01295501508689100844 |
content_id | text | Assessment Content Identifier | do_2129493126207324161154 |
attempt_id | text | Attempt Identifier | 638a8d6240f8df4b8cc5ef9b79fa0d67 |
created_on | timestamp | Record created on | 2020-02-12 03:52:51.687000+0000 |
grand_total | text | Assessment aggregate score | 1.0/8.0 |
last_attempted_on | timestamp | Last attempted timestamp of the assessment | 2020-02-12 03:52:19.666000+0000 |
question | list<frozen<question>> | Question Details | |
total_max_score | double | Total Maximum score of assessments of the course | 8 |
total_score | double | Total score achieved by the user | 1 |
updated_on | timestamp | record updated on | 2020-02-12 05:02:33.088000+0000 |
sunbird_courses.course_batch [PRIMARY KEY (courseid, batchid)]
Table used to store Batch information created against Courses.
Column Name | Data Type | Description | Sample Data |
---|---|---|---|
courseid | text | Course Identifier | do_2134405168915005441176 |
batchid | text | Batch Identifier | 013440524934594560116 |
cert_templates | map<text, frozen<map<text, text>>> | Certificate template details linked to the Batch | |
created_date | timestamp | Batch Creation Date | 2021-12-15 12:19:07.049000+0000 |
createdby | text | Batch Created By | fca2925f-1eee-4654-9177-fece3fd6afc9 |
| text | Not used | |
createdfor | list<text> | Batch creator user Organisations | ['01269878797503692810', '01275629407654707220300'] |
description | text | Batch Description | Sample Batch |
end_date | timestamp | Batch End Date | |
| text | Not used | |
enrollment_enddate | timestamp | Batch Enrolment End Date | 2022-06-20 |
| text | Not used | |
enrollmenttype | text | Batch Enrolment Type | open |
mentors | list<text> | Mentors of the batch | |
name | text | Name of the Batch | Sample Batch |
start_date | timestamp | Batch Enrolment Start Date | 2021-12-15 12:19:07.049000+0000 |
| text | Not used | |
status | int | status of the Batch (running or closed) | 1 |
| boolean | Not used | |
updated_date | timestamp | record last updated date | 2021-12-15 12:19:07.049000+0000 |
| text | Not used |
sunbird_courses.user_enrolments [PRIMARY KEY (userid, courseid, batchid)]
Table used to capture User's information who have enrolled to Course Batch
Column Name | Data Type | Description | Sample Data |
---|---|---|---|
userid | text | UUID of the user | 02b8bade-c8ed-44c7-87fd-ebabeaed98c2 |
courseid | text | Course Identifier | do_213432103242072064167 |
batchid | text | Batch Identifier | 0134321075692503049 |
active | boolean | User Batch Enrolment status | True |
addedby | text | abf44b3d-877d-7de8-1da3-1603a727cb10 | |
| list<frozen<map<text, text>>> | Not Used | |
| int | Not Used | |
completedon | timestamp | Time at which user has completed the course consumption | 2023-03-23 12:38:05.055000+0000 |
completionpercentage | int | Course Consumption percentage | |
contentstatus | map<text, int> | Course's Content consumption status | {'do_21337188080177971211': 2, 'do_21353023582218649615542': 2} |
datetime | timestamp | record last updated on | 2023-03-23 12:38:05.063000+0000 |
enrolled_date | timestamp | Time at which user enrolled to Course Batch | 2023-03-23 12:37:12.105000+0000 |
| text | Not Used | |
issued_certificates | list<frozen<map<text, text>>> | Certificate details issued to the user upon course completion | |
lastcontentaccesstime | timestamp | Latest Timestamp at which user accessed the course | 2023-03-23 12:37:57.451000+0000 |
lastreadcontentid | text | Latest content id consumed by the user from the course | do_21337188080177971211 |
lastreadcontentstatus | int | Consumption completion status of the recently consumed content by the user from the course | 2 |
progress | int | Count of contents part of the Course that are consumed completely | 3 |
status | int | User Course consumption completion status (1 -> in progress, 2 -> completed) | 2 |
sunbird_courses.report_user_enrolments [PRIMARY KEY (userid, courseid, batchid)]
Table same as that of 'sunbird_courses.user_enrolments' used for certificate migration
sunbird_courses.bulk_upload_process [PRIMARY KEY: id]
Table used to store information about the bulk user enrolments file upload process.
Column Name | Data Type | Description | Sample Data |
---|---|---|---|
id | text | Bulk Upload process Id | 0128261582844723205 |
data | text | Bulk Enrolment request Data | |
failureresult | text | Enrolment Failure Data | |
objecttype | text | Bulk Upload Process Type | batchLearnerEnrol |
processendtime | text | Time at which record processing was completed | 2019-08-13 10:40:32:476+0000 |
processstarttime | text | Time at which records processing was started | 2019-08-13 10:40:31:178+0000 |
status | int | Status of the Process | 3 |
successresult | text | Enrolment Success Data | |
uploadedby | text | UUID of the user who initiated Bulk upload process | 4707a109-edee-455b-9e6c-a9a45e7f331c |
uploadeddate | text | Time at which Bulk upload was done | 2019-08-13 10:40:31:163+0000 |
Last updated