1.11 data dictionary
This page is an (incomplete) documentation of the data structure in Chamilo 1.11.
- All fields called
c_id(or in certain casescourse_id) always are references to thecourse.idfield. - All fields called
user_id(or any field ending withuser_id) always are references to theuser.idfield. - All fields called
session_id(or any field ending withsession_id) always are references to thesession.idfield. - All fields called
access_url_idorurl_idare references to theaccess_url.idfield. - When fields called
group_idappear in a table with ac_prefix, they are usually referring to a course group (c_group_info.iid)
As such, these fields are not detailed below.
Note 1: All tables starting with a c_ are course-specific tables. They always include a reference to the course as the c_id field, and generally still hold a double identifier: iid is the new, platform-unique identifier, while the combination of id+c_id is the "old" unique identifier. Queries should use the iid field, although some have been left using id+c_id by lack of time during the transition.
Note 2: Sessions are really special, and most course resources tables include a session ID which can be either 0 or NULL (depending on the age of the table), or has a non-zero value. In case it has a (numeric) value, it means the resource is available inside the session-course and not inside the base course. If it is 0 or NULL, it means it is available inside the base course and for some resources, this means it is also visible in the session-course.
At the end of this page, we provide a few examples of inter-table queries that might be useful.
List of all URLs if using multiple URLs
| Field | Definition |
|---|---|
| id | Unique identifier |
| url | URL (including protocol) |
| description | |
| active | 1 if the URL can already be used |
| created_by | (FK) user.id |
| tms | timestamp |
| url_type | (not used yet) |
Link between the access_url table and the course table
| Field | Definition |
|---|---|
| id | Unique identifier |
| c_id | See "Common fields" |
| access_url_id | See "Common fields" |
Link between access_url and course_category
| Field | Definition |
|---|---|
| id | Unique identifier |
| access_url_id | See "Common fields" |
| course_category_id |
Link between access_url and session for sessions registered in a specific URL
| Field | Definition |
|---|---|
| id | Unique identifier |
| access_url_id | See "Common fields" |
| session_id | See "Common fields" |
Link between access_url and user for users registered in a specific URL
| Field | Definition |
|---|---|
| access_url_id |
access_url id (FK) |
| user_id | See "Common fields" |
Link between access_url and usergroup for groups registered in a specific URL
| Field | Definition |
|---|---|
| id | Unique identifier |
| access_url_id | See "Common fields" |
| usergroup_id |
usergroup id (FK) |
Contains a list of ID of user that are platform administrators. If a user ID is here, the user is a platform admin (access URLs change that behaviour slightly)
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_id | See "Common fields" |
Link between sys_announcement and usergroup
| Field | Definition |
|---|---|
| group_id | |
| announcement_id | id from sys_announcement
|
(not yet used)
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| path | |
| controller | |
| active |
(not in use yet) Allows for the synchronisation of Chamilo portals between themselves (not used yet)
| Field | Definition |
|---|---|
| id | Unique identifier |
| ... | ... |
(not in use yet) Allows for the registration of transactions (pending or executed) from another system, to be executed in Chamilo
| Field | Definition |
|---|---|
| ... | |
| ... |
(not Dictionary of status terms for branch_transaction
| Field | Definition |
|---|---|
| ... |
Contains the announcements in the corresponding course tool
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| title | |
| content | |
| end_date | |
| display_order | |
| email_sent | |
| session_id | See "Common fields" |
Contains the attachments (files) of c_announcement | iid | Unique identifier |
| Field | Definition |
|---|---|
| id | deprecated |
| c_id | See "Common fields" |
| path | |
| comment | |
| size | |
| announcement_id | id from c_announcement
|
| filename |
Contains one attendance "resource". Courses can contain several attendance resources, which can each be evaluated in the gradebook. Each contains dates (c_attendance_calendar), which contain attendance records (see c_attendance_sheet)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| name | |
| description | |
| active | |
| attendance_qualify_title | |
| attendance_qualify_max | |
| attendance_weight | |
| session_id | See "Common fields" |
| locked |
Each attendance datetime is registered here separately.
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| attendance_id | id from c_attendance
|
| date_time | |
| done_attendance |
If an attendance date is specific to a users group in a course, this links the date to the c_group
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| group_id | See "Common fields" |
| calendar_id |
The global attendance "rate" of a user in an attendance resource
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| user_id | See "Common fields" |
| attendance_id | |
| score |
Registers the attendance or lack thereof of a user to a specific calendar datetime
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| presence | |
| user_id | See "Common fields" |
| attendance_calendar_id |
Log table for changes in the c_attendance_sheet
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| attendance_id | |
| lastedit_date | |
| lastedit_type | |
| lastedit_user_id | See "Common fields" |
| calendar_date_value |
Entries for each blog created in a course
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| blog_id | |
| blog_name | |
| blog_subtitle | |
| date_creation | |
| visibility | |
| session_id | See "Common fields" |
File attachments to blog posts
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| path | |
| comment | |
| size | |
| post_id | |
| filename | |
| blog_id | |
| comment_id |
Comments on blog posts
| Field | Definition |
|---|---|
| iid | Unique identifier |
| comment_id | deprecated |
| c_id | See "Common fields" |
| title | |
| comment | |
| author_id | Reference to user.id. Represents the author of the blog comment. |
| date_creation | |
| blog_id | |
| post_id | |
| task_id | |
| parent_comment_id |
Messages posted in blogs
| Field | Definition |
|---|---|
| iid | Unique identifier |
| blog_id | |
| c_id | See "Common fields" |
| title | |
| full_text | |
| date_creation | |
| author_id | References user.id. Represents the author of the blog post. |
| post_id |
Ratings given to blog posts
| Field | Definition |
|---|---|
| iid | Unique identifier |
| rating_id | |
| c_id | See "Common fields" |
| blog_id | |
| rating_type | |
| item_id | |
| user_id | See "Common fields" |
| rating |
Link between c_blog and user
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| blog_id | |
| user_id | See "Common fields" |
Tasks that can be configured inside a blog
| Field | Definition |
|---|---|
| iid | Unique identifier |
| task_id | |
| c_id | See "Common fields" |
| blog_id | |
| title | |
| description | |
| color | |
| system_task |
Assignation of tasks (c_blog_task) to users (user)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| blog_id | |
| target_date | |
| user_id | See "Common fields" |
| task_id |
Events of the calendar
| Field | Definition |
|---|---|
| iid | Unique identifier |
| room_id | (not used yet) |
| id | deprecated |
| c_id | See "Common fields" |
| title | |
| content | |
| start_date | |
| end_date | |
| parent_event_id | |
| session_id | See "Common fields" |
| all_day | |
| comment | |
| color |
Attachment (files) to the c_calendar_event records
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| path | |
| comment | |
| size | |
| agenda_id | |
| filename |
Definition of repetitions for existing events
| Field | Definition |
|---|---|
| iid | Unique identifier |
| cal_id | |
| c_id | See "Common fields" |
| cal_type | |
| cal_end | |
| cal_frequency | |
| cal_days |
Definition of exclusions/exceptions in repetitions for existing events
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| cal_id | |
| cal_date |
Logs when a user is active in the course chat
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| session_id | See "Common fields" |
| to_group_id | |
| user_id | See "Common fields" |
| last_connection |
The sections available in the course description tool
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| title | |
| content | |
| session_id | See "Common fields" |
| description_type | |
| progress |
Settings of the course (whether to send e-mails, when, etc) that can be access through the Settings tool inside the course
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| variable | |
| subkey | |
| type | |
| category | |
| value | |
| title | |
| comment | |
| subkeytext |
The list of documents and folders of the documents tool
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| path | |
| comment | |
| title | |
| filetype | |
| size | |
| readonly | |
| session_id | See "Common fields" |
Folders inside the dropbox
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| cat_id | |
| cat_name | |
| received | |
| sent | |
| user_id | See "Common fields" |
| session_id | See "Common fields" |
Feedback sent by users on files in the dropbox
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| feedback_id | |
| file_id | |
| author_user_id | |
| feedback | |
| feedback_date |
A file dropped in the dropbox
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| uploader_id | References user.id. Represents the user who uploaded the file. |
| filename | |
| filesize | |
| title | |
| description | |
| author | |
| upload_date | |
| last_upload_date | |
| cat_id | |
| session_id | See "Common fields" |
The relationships between files (c_dropbox_file) and whom they are sent to (user)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| file_id | |
| user_id | See "Common fields" |
A message posted on a file (c_dropbox_file)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| feedback_date | |
| feedback | |
| cat_id | |
| session_id | See "Common fields" |
| c_id | See "Common fields" |
| file_id | |
| dest_user_id | See "Common fields". Represents the user this message is sent to. |
Optional table created by manual action documented in configuration.php
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| name | |
| description | |
| created_at | |
| updated_at | |
| position |
File attachments to c_forum_post records
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| path | |
| comment | |
| size | |
| post_id | |
| filename |
Forum categories (which include forums)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| cat_title | |
| cat_comment | |
| cat_order | |
| locked | |
| session_id | See "Common fields" |
| cat_id |
Forums (which include threads)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| forum_id | |
| forum_title | |
| forum_comment | |
| forum_threads | |
| forum_posts | |
| forum_last_post | |
| forum_category | |
| allow_anonymous | |
| allow_edit | |
| approval_direct_post | |
| allow_attachments | |
| allow_new_threads | |
| default_view | |
| forum_of_group | |
| forum_group_public_private | |
| forum_order | |
| locked | |
| session_id | See "Common fields" |
| forum_image | |
| start_time | |
| end_time | |
| lp_id | |
| moderated |
When a user marks a thread or post for update notifications, it is kept here
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | |
| thread_id | |
| user_id | See "Common fields" |
| post_id |
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | |
| user_id | See "Common fields" |
| forum_id | |
| thread_id | |
| post_id |
Forum posts (in response to a thread)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| post_id | |
| post_title | |
| post_text | |
| thread_id | |
| forum_id | |
| poster_id | |
| poster_name | |
| post_date | |
| post_notification | |
| post_parent_id | |
| visible | |
| status |
Forum threads (which give birth to posts and have more properties than posts)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| thread_id | |
| thread_title | |
| forum_id | |
| thread_replies | |
| thread_poster_id | References user.id. Represents the author of the thread. |
| thread_poster_name | |
| thread_views | |
| thread_last_post | |
| thread_date | |
| thread_sticky | |
| locked | |
| session_id | See "Common fields" |
| thread_title_qualify | |
| thread_qualify_max | |
| thread_close_date | |
| thread_weight | |
| thread_peer_qualify | |
| lp_item_id |
If a thread can be scored, the scores given by teachers or other users are stored here
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | |
| user_id | See "Common fields" |
| thread_id | |
| qualify | |
| qualify_user_id | |
| qualify_time | |
| session_id | See "Common fields" |
A log table of all the changes in the forum threads scores
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
Terms from the course glossary
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| glossary_id | |
| name | |
| description | |
| display_order | |
| session_id | See "Common fields" |
Categories of course groups (c_group_info)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | |
| title | |
| description | |
| doc_state | |
| calendar_state | |
| work_state | |
| announcements_state | |
| forum_state | |
| wiki_state | |
| chat_state | |
| max_student | |
| self_reg_allowed | |
| self_unreg_allowed | |
| groups_per_user | |
| display_order |
Course groups and their description
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | depecated |
| c_id | See "Common fields" |
| name | |
| status | |
| category_id | |
| description | |
| max_student | |
| doc_state | |
| calendar_state | |
| work_state | |
| announcements_state | |
| forum_state | |
| wiki_state | |
| chat_state | |
| secret_directory | |
| self_registration_allowed | |
| self_unregistration_allowed | |
| session_id | See "Common fields" |
Tutors assigned to course groups (c_group_info)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| user_id | See "Common fields" |
| group_id | See "Common fields" |
Users registered in course groups (c_group_info)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| user_id | See "Common fields" |
| group_id | See "Common fields" |
| status | |
| role |
This gathers the definition, visibility and changelog of most learning objects in Chamilo. It is a central piece of the database structure in 1.11
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| to_group_id | See "Common fields". Represents the user group (in a course group) that receives/can see this item, if any. |
| to_user_id | See "Common fields". Represents the user that receives/can see this item, if any. |
| insert_user_id | See "Common fields". Represents the user who created this item. |
| session_id | See "Common fields" |
| id | deprecated |
| tool | a string with the name of the tool (document, link, etc) |
| insert_date | |
| lastedit_date | |
| ref | the iid of the item in its own table |
| lastedit_type | An English string representing the last type of edition (delete, |
| lastedit_user_id | See "Common fields". Represents the user who last edited this file. |
| visibility | |
| start_visible | |
| end_visible |
Course links
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| url | |
| title | |
| description | |
| category_id | |
| display_order | |
| on_homepage | |
| target | |
| session_id | See "Common fields" |
Categories of course links (c_link)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| category_title | |
| description | |
| display_order | |
| session_id | See "Common fields" |
Course learning paths
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| lp_type | |
| name | |
| ref | |
| description | |
| path | |
| force_commit | |
| default_view_mod | |
| default_encoding | Mostly for very old imported content not in UTF-8 (usually in UTF-8 in all recent content) |
| display_order | |
| content_maker | just a label |
| content_local | |
| content_license | |
| prevent_reinit | |
| js_lib | |
| debug | feature only available to platform admins |
| theme | |
| preview_image | |
| author | |
| session_id | See "Common fields" |
| prerequisite | for dependencies on other learning paths |
| hide_toc_frame | |
| seriousgame_mode | |
| use_max_score | |
| autolaunch | |
| category_id | |
| max_attempts | |
| subscribe_users | |
| created_on | |
| modified_on | |
| publicated_on | |
| expired_on | |
| accumulate_scorm_time |
Categories for courses learning paths
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| name | |
| position |
Access specifications for specific users to specific learning paths categories
| Field | Definition |
|---|---|
| iid | Unique identifier |
| category_id | |
| user_id | See "Common fields" |
Documents inside a learning path
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated (almost) |
| lp_id | |
| item_type | tool used (document, link, etc) or in the case of SCORM: asset, sco, etc |
| ref | iid of the item if document, quiz, link, etc |
| title | |
| description | |
| path | |
| min_score | |
| max_score | |
| mastery_score | |
| parent_item_id | |
| previous_item_id | |
| next_item_id | |
| display_order | |
| prerequisite | |
| parameters | |
| launch_data | |
| max_time_allowed | |
| terms | |
| search_did | |
| audio | |
| prerequisite_min_score | |
| prerequisite_max_score |
Each visualization by a user (user) of one document (c_lp_item) inside the learning path (c_lp). Belongs to a c_lp_view entry
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| lp_item_id | |
| lp_view_id | |
| view_count | |
| start_time | |
| total_time | |
| score | |
| status | |
| suspend_data | |
| lesson_location | |
| core_exit | |
| max_score |
SCORM interactions for one specific c_lp_item_view
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| order_id | |
| lp_iv_id | |
| interaction_id | |
| interaction_type | |
| weighting | |
| completion_time | |
| correct_responses | |
| student_response | |
| result | |
| latency |
SCORM objectives for one specific c_lp_item_view
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| lp_iv_id | |
| order_id | |
| objective_id | |
| score_raw | |
| score_max | |
| score_min | |
| status |
View of a learning path (c_lp) by a user. Contains many c_lp_view_item
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| lp_id | |
| user_id | See "Common fields" |
| view_count | |
| last_item | |
| progress | the current progress of the user in this learning path's view, in percentage (0-100) |
| session_id | See "Common fields" |
Posts in the notebook tool in the course (personal notes)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| notebook_id | |
| user_id | See "Common fields" |
| course | deprecated |
| session_id | See "Common fields" |
| title | |
| description | |
| creation_date | datetime |
| update_date | datetime |
| status |
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| last_connection | |
| user_id | See "Common fields" |
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| name | |
| url |
Blog-related user permissions ...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
Blog-releated user permissions in tasks ...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
Blog-related user permissions ...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
List of exercises in courses
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| title | Title |
| description | Introduction block that shows at the beginning of a test |
| sound | |
| type | |
| random | Type of randomness |
| random_answers | Whether answers are randomized |
| active | 0 if disabled, 1 otherwise |
| results_disabled | Whether we show the results |
| access_condition | |
| max_attempt | Number of attempts allowed |
| start_time | Datetime of when the entrance to the quiz is first available |
| end_time | Datetime of when the entrance to the quiz stops being available |
| feedback_time | |
| expired_time | |
| session_id | See "Common fields" |
| propagate_neg | Whether to propagate negative scores from one question to the next |
| save_correct_answers | |
| review_answers | |
| random_by_category | |
| text_when_finished | |
| display_category_name | |
| pass_percentage | |
| question_selection_type | |
| hide_question_title | |
| autolaunch | |
| exercise_category_id | |
| prevent_backwards | Prevent users for going backwards between questions in one-question-per-page mode (optional field added manually) |
List of possible answers to a question (c_quiz_question) in a c_quiz
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id_auto | |
| c_id | See "Common fields" |
| id | deprecated |
| question_id | the c_quiz_question iid it relates to |
| answer | the text of this answer |
| correct | 0 if incorrect, 1 if the correct answer to the question |
| comment | feedback if this answer was picked |
| ponderation | Not the ponderation (bad naming) but the score obtained for selecting this answer |
| position | |
| hotspot_coordinates | |
| hotspot_type | |
| destination | |
| answer_code |
List of questions in the exercises tool. As questions can be shared between c_quiz, the real link between questions and quizzes is through the c_quiz_rel_question table
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| question | question title |
| description | question (longer) description, if any |
| ponderation | max score for this question (usually calculated from the sum of scores from the answers) |
| position | |
| type | one of ~20 question types. Defined by constants in main/inc/lib/api.lib.php (search for "Question types" constants) |
| picture | in the case of a hotspot question, path to the image |
| level | level of difficulty. Only a string. Does not imply any logic change. |
| extra | |
| question_code | special code that can be added to each question |
Categories of questions in quizzes
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| title | |
| description |
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| question_id | |
| name | |
| position |
Relationship between a c_quiz_question and a c_quiz_question_category
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| category_id | |
| question_id |
Relationship between a c_quiz and a quiz category (see c_exercise_category)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| category_id | |
| exercise_id | FK to c_quiz iid |
| count_question |
Relationship between a question (c_quiz_question) and a specific quiz (c_quiz)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| question_order | |
| question_id | |
| exercice_id |
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| source_type | |
| source_id | |
| resource_type | |
| resource_id |
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
An assignment handed over by a student
| Field | Definition |
|---|---|
| iid | Unique identifier |
| session_id | See "Common fields" |
| c_id | See "Common fields" |
| id | deprecated |
| url | |
| url_correction | |
| title | |
| title_correction | |
| description | |
| author | |
| active | |
| accepted | |
| post_group_id | |
| sent_date | |
| filetype | |
| has_properties | |
| view_properties | |
| qualification | |
| date_of_qualification | |
| parent_id | |
| qualificator_id | |
| weight | |
| user_id | See "Common fields" |
| allow_text_assignment | |
| contains_file | |
| document_id |
Assigments definition (tasks/works)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| expires_on | |
| ends_on | |
| add_to_calendar | |
| enable_qualification | |
| publication_id | FK to c_student_publication iid |
Comments on c_student_publication
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| work_id | FK to c_student_publication iid |
| comment | |
| file | |
| user_id | See "Common fields" |
| sent_at |
Link between a document (c_document) that serves as a template to the assignments
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| work_id | FK to c_student_publication iid |
| document_id | FK to c_document iid |
Link between users and assignments (to assign specific assignments to a reduced number of users)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| work_id | |
| user_id | See "Common fields" |
Surveys
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| survey_id | |
| code | |
| title | |
| subtitle | |
| author | |
| lang | |
| avail_from | |
| avail_till | |
| is_shared | |
| template | |
| intro | |
| surveythanks | |
| creation_date | |
| invited | |
| answered | |
| invite_mail | |
| reminder_mail | |
| mail_subject | |
| anonymous | |
| access_condition | |
| shuffle | |
| one_question_per_page | |
| survey_version | |
| parent_id | |
| survey_type | |
| show_form_profile | |
| form_fields | |
| session_id | See "Common fields" |
| visible_results |
Possible answers to survey questions (c_survey_question)
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| answer_id | |
| survey_id | |
| question_id | |
| option_id | |
| value | |
| user |
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| name | |
| description | |
| survey_id |
Invitations of users to surveys
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| survey_invitation_id | |
| survey_code | |
| user | |
| invitation_code | |
| invitation_date | |
| reminder_date | |
| answered | |
| session_id | See "Common fields" |
| group_id | FK to c_group_info iid |
Survey questions
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| question_id | |
| survey_id | |
| survey_question | |
| survey_question_comment | |
| type | |
| display | |
| sort | |
| shared_question_id | |
| max_value | |
| survey_group_pri | |
| survey_group_sec1 | |
| survey_group_sec2 | |
| is_required | Whether this is a mandatory survey (1) or not (0) |
Options on survey questions
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| question_option_id | |
| question_id | |
| survey_id | |
| option_text | |
| sort | |
| value |
Highest level of thematic progress categorization
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| title | |
| content | |
| display_order | |
| active | |
| session_id | See "Common fields" |
Dates related to the progress in a thematic plan
| Field | Definition |
|---|---|
| iid | Unique identifier |
| room_id | |
| c_id | See "Common fields" |
| id | deprecated |
| thematic_id | |
| attendance_id | |
| content | |
| start_date | |
| duration | |
| done_advance |
Second level of thematic progress
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| thematic_id | |
| title | |
| description | |
| description_type |
Entries of available tools on the course homepage
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | deprecated |
| c_id | See "Common fields" |
| name | internal name (translated through language files to show the name of the tool to the user) |
| link | URL to the tool's home |
| image | icon (_na.png is added when the tool is disabled) |
| visibility | |
| admin | |
| address | |
| added_tool | |
| target | |
| category | |
| session_id | See "Common fields" |
| description | |
| custom_icon | If a custom icon is uploaded by the teacher, where to find it |
Introduction sections. On the course homepage but also (if enabled) in each tool.
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | String representing the tool |
| intro_text | longtext with the HTML intro to the tool |
| session_id | See "Common fields" |
Deprecated: Values for user fields in courses
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| user_id | See "Common fields" |
| definition_id | |
| editor_ip | |
| edition_time | |
| content |
Deprecated: User fields in courses
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| title | |
| comment | |
| line_count | |
| rank |
Wiki pages in course
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| page_id | |
| reflink | |
| title | |
| content | |
| user_id | See "Common fields" |
| group_id | FK to c_group_info iid |
| dtime | |
| addlock | |
| editlock | |
| visibility | |
| addlock_disc | |
| visibility_disc | |
| ratinglock_disc | |
| assignment | |
| comment | |
| progress | |
| score | |
| version | |
| is_editing | |
| time_edit | |
| hits | |
| linksto | |
| tag | |
| user_ip | |
| session_id | See "Common fields" |
Wiki configuration in a course
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| page_id | |
| task | |
| feedback1 | |
| feedback2 | |
| feedback3 | |
| fprogress1 | |
| fprogress2 | |
| fprogress3 | |
| max_size | |
| max_text | |
| max_version | |
| startdate_assig | |
| enddate_assig | |
| delayedsubmit |
Conversations on each wiki page
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| publication_id | |
| userc_id | |
| comment | |
| p_score | |
| dtime |
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| type | |
| group_id | See "Common fields" |
| session_id | See "Common fields" |
| user_id | See "Common fields" |
Careers (admin panel)
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| status | |
| created_at | |
| updated_at |
Chat sessions
| Field | Definition |
|---|---|
| id | Unique identifier |
| from_user | References user.id (the user who wrote the message) |
| to_user | References user.id (the user who received the message) |
| message | |
| sent | |
| recd |
Videochat sessions (temporarily deprecated)
| Field | Definition |
|---|---|
| id | Unique identifier |
| from_user | |
| to_user | |
| room_name | |
| datetime |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| code | |
| name |
Link between classes and users
| Field | Definition |
|---|---|
| class_id | |
| user_id | See "Common fields" |
Courses
| Field | Definition |
|---|---|
| id | Unique identified (referenced by c_id everywhere else) |
| room_id | not yet used |
| title | |
| code | |
| directory | |
| course_language | |
| description | |
| category_code | |
| visibility | |
| show_score | |
| tutor_name | |
| visual_code | |
| department_name | |
| department_url | |
| disk_quota | |
| last_visit | |
| last_edit | |
| creation_date | |
| expiration_date | |
| subscribe | |
| unsubscribe | |
| registration_code | |
| legal | |
| activate_legal | 0 if no particular legal terms. 1 to enable legal terms confirmation |
| add_teachers_to_sessions_courses | |
| course_type_id |
Courses categories
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| code | |
| parent_id | |
| tree_pos | |
| children_count | |
| auth_course_child | |
| auth_cat_child |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| link | |
| image | |
| row | |
| column | |
| position |
Relationship between course and usergroup
| Field | Definition |
|---|---|
| course_code | FK to course.code! (to be changed to id in the future) |
| class_id |
Subscription of users to courses (if not using sessions)
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_id | See "Common fields" |
| c_id | See "Common fields" |
| relation_type | |
| status | |
| is_tutor | |
| sort | |
| user_course_cat | |
| legal_agreement |
Special accesses to catalogue by specific users
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_id | See "Common fields" |
| c_id | See "Common fields" |
| visible |
Requests for courses by users (if the course requests feature is enabled)
| Field | Definition |
|---|---|
| id | Unique identifier |
| code | |
| user_id | See "Common fields" |
| directory | |
| db_name | |
| course_language | |
| title | |
| description | |
| category_code | |
| tutor_name | |
| visual_code | |
| request_date | |
| objetives | (note the spelling mistake here) |
| target_audience | |
| status | |
| info | |
| exemplary_content |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| translation_var | |
| description | |
| props |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| message | |
| subject | |
| event_type_name | |
| activated | |
| language_id |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_from | References user.id. Represents the user who generated the event. |
| user_to | |
| event_type_name |
Extra fields definition. Also defines which type of resource it applies to (users, courses, quizzes, etc)
| Field | Definition |
|---|---|
| id | Unique identifier |
| extra_field_type | |
| field_type | |
| variable | |
| display_text | |
| default_value | |
| field_order | |
| visible_to_self | |
| visible_to_others | |
| changeable | |
| filter | |
| created_at |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_option_id | |
| related_field_option_id | |
| role_id | |
| field_id |
Possible values for extra_field_values
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_id | |
| option_value | |
| display_text | |
| priority | |
| priority_message | |
| option_order |
Tags on extra fields
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_id | |
| tag_id | |
| item_id |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_id | |
| user_id | See "Common fields" |
| value | |
| created_at | |
| updated_at |
Values stored for specific resources in specific extra fields
| Field | Definition |
|---|---|
| field_id | |
| value | |
| item_id | |
| created_at | |
| updated_at | |
| comment |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| percentage | |
| title | |
| acronym | |
| grade_model_id |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| default_lowest_eval_exclude | |
| default_external_eval | |
| default_external_eval_prefix |
Highest level of the gradebook structure (attributing certificates and skills). Despite the name, this is not a gradebook "category" but rather the list of all gradebooks, which can include sub-gradebooks, but they are still each a fully-functional gradebook.
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| user_id | See "Common fields" |
| course_code | This table still uses the course code to reference the course table. This is planned for change to c_id in future major versions. |
| parent_id | Recursive reference to gradebook_category.id. Sub-gradebooks reference the main gradebook through this field. |
| weight | |
| visible | |
| certif_min_score | |
| session_id | See "Common fields" |
| document_id | |
| locked | |
| default_lowest_eval_exclude | |
| generate_certificates | |
| grade_model_id | Reference to a grade_model.id
|
| is_requirement | Whether this gradebook's completion is a requirement to finish the course (1) or not (0) |
Generated certificates
| Field | Definition |
|---|---|
| id | Unique identifier |
| cat_id | |
| user_id | See "Common fields" |
| score_certificate | |
| created_at | |
| path_certificate |
External evaluation scored in the gradebook
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| user_id | See "Common fields" |
| course_code | Course code. Will be moved to c_id in the future |
| category_id | |
| created_at | |
| weight | |
| max | |
| visible | |
| type | |
| locked |
Internal evaluation (Chamilo resources) scored in the gradebook
| Field | Definition |
|---|---|
| id | Unique identifier |
| type | |
| ref_id | |
| user_id | See "Common fields" |
| course_code | Reference to course.code. Will be changed to course.id in future major versions. |
| category_id | |
| created_at | |
| weight | |
| visible | |
| locked |
...
| Field | Definition |
|---|---|
| id | Unique identified |
| id_linkeval_log | |
| name | |
| description | |
| created_at | |
| weight | |
| visible | |
| type | |
| user_id_log | See "Common fields" |
Results of users on gradebook_link or gradebook_evaluation
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_id | See "Common fields" |
| evaluation_id | |
| created_at | |
| score |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| id_result | |
| user_id | See "Common fields" |
| evaluation_id | |
| created_at | |
| score |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| score | |
| display | |
| category_id | |
| score_color_percent |
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| category_id | |
| user_id | See "Common fields" |
| score | |
| registered_at |
Internal hooks definition
| Field | Definition |
|---|---|
| id | Unique identifier |
| hook_event_id | |
| hook_observer_id | |
| type | |
| hook_order | |
| enabled |
Internal hooks definition
| Field | Definition |
|---|---|
| id | Unique identifier |
| class_name | |
| description |
Internal hooks definition
| Field | Definition |
|---|---|
| id | Unique identifier |
| class_name | |
| path | |
| plugin_name |
The language supported for the user interface, and sublanguages
| Field | Definition |
|---|---|
| id | Unique identifier |
| parent_id | |
| original_name | |
| english_name | |
| isocode | |
| dokeos_folder | |
| available |
Terms and conditions
| Field | Definition |
|---|---|
| id | Unique identifier |
| date | |
| content | |
| type | |
| changes | |
| version | |
| language_id | References language.id
|
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| template | |
| type | |
| created_at | |
| updated_at | |
| author_id | |
| url_id | See "Common fields" |
| default_template | |
| system |
User messages on the social network + all messages sent by the system by e-mail and saved for the local inbox
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_sender_id | References user.id as the sender of the message |
| user_receiver_id | References user.id as the receiver of the message |
| msg_status | |
| send_date | |
| title | |
| content | |
| group_id | |
| parent_id | |
| update_date | |
| votes |
File attachments for message. Note that at this point the
| Field | Definition |
|---|---|
| id | |
| path | |
| comment | |
| size | |
| message_id | |
| filename |
Likes given to messages on the social network
| Field | Definition |
|---|---|
| id | |
| message_id | |
| user_id | See "Common fields" |
| liked | |
| disliked | |
| updated_at |
...
| Field | Definition |
|---|---|
| id | |
| dest_user_id | See "Common fields" |
| dest_mail | |
| title | |
| content | |
| send_freq | |
| created_at | |
| sent_at |
...
| Field | Definition |
|---|---|
| id | |
| idp_endpoint_uri | |
| session_type | |
| assoc_handle | |
| assoc_type | |
| expires_in | |
| mac_key | |
| created |
Personal events stored in the agenda
| Field | Definition |
|---|---|
| id | |
| user | Reference to user.id
|
| title | |
| text | |
| date | |
| enddate | |
| course | Course code. Most often NULL as a "personal" event is not linked to a course. |
| parent_event_id | Circular reference to personal_agenda.id to designate the event from which this one was created as a repetition |
| all_day | |
| color |
Repetition on personal_agenda
| Field | Definition |
|---|---|
| cal_id | |
| cal_type | |
| cal_end | |
| cal_frequency | |
| cal_days |
Repetition exclusions on personal_agenda_repeat
| Field | Definition |
|---|---|
| cal_id | |
| cal_date |
All plugin tables (see plugin documentation)
Promotions are contained in careers and contain sessions.
| Field | Definition |
|---|---|
| id | |
| career_id | |
| name | |
| description | |
| status | |
| created_at | |
| updated_at |
Rooms are an item for a future feature in Chamilo allowing you to define a physical or virtual room for a synchronous lesson.
| Field | Definition |
|---|---|
| id | |
| branch_id | Reference to branch_sync.id
|
| title | |
| description | |
| geolocation | |
| ip | IP address of the room or virtual room, if any (can be IPv4 or IPv6) |
| ip_mask | IP mask if "ip" is a network address |
If the scheduled announcements for sessions are enabled, contains the messages scheduled to be sent at specific dates to session users
| Field | Definition |
|---|---|
| id | |
| subject | |
| message | |
| date | |
| sent | |
| session_id | See "Common fields" |
| c_id | See "Common fields" |
Internal data on indexer with Xapian
| Field | Definition |
|---|---|
| id | |
| course_code | Reference to course.code
|
| tool_id | |
| ref_id_high_level | |
| ref_id_second_level | |
| search_did |
Sequences for course/session access
| Field | Definition |
|---|---|
| id | |
| name | |
| graph | |
| created_at | |
| updated_at |
Conditions for sequence
| Field | Definition |
|---|---|
| id | |
| description | |
| mat_op | |
| param | |
| act_true | |
| act_false |
Formula for sequence
| Field | Definition |
|---|---|
| id | |
| sequence_method_id | |
| sequence_variable_id |
Method for sequence
| Field | Definition |
|---|---|
| id | |
| description | |
| formula | |
| assign | |
| met_type | |
| act_false |
Method for resource
| Field | Definition |
|---|---|
| id | |
| sequence_id | |
| type | |
| resource_id |
...
| Field | Definition |
|---|---|
| id | |
| sequence_type_entity_id | |
| c_id | See "Common fields" |
| session_id | See "Common fields" |
| row_id | |
| name |
Rules for sequence
| Field | Definition |
|---|---|
| id | |
| description |
...
| Field | Definition |
|---|---|
| id | |
| sequence_rule_id | |
| sequence_condition_id |
...
| Field | Definition |
|---|---|
| id | |
| sequence_rule_id | |
| sequence_method_id | |
| method_order |
...
| Field | Definition |
|---|---|
| id | |
| name | |
| description | |
| ent_table |
...
| Field | Definition |
|---|---|
| id | |
| sequence_variable_id | |
| sequence_condition_id |
...
| Field | Definition |
|---|---|
| id | |
| sequence_row_entity_id | |
| user_id | See "Common fields" |
| advance | |
| complete_items | |
| total_items | |
| success | |
| success_date | |
| available | |
| available_start_date | |
| available_end_date |
...
| Field | Definition |
|---|---|
| id | |
| name | |
| description | |
| default_val |
Course sessions definitions
| Field | Definition |
|---|---|
| id | Unique identifier |
| id_coach | Each session has only one general coach. This references the user.id field. |
| session_category_id | |
| name | |
| description | |
| show_description | |
| duration | |
| nbr_courses | |
| nbr_users | |
| nbr_classes | |
| session_admin_id | Which user (user.id) created this session |
| visibility | |
| promotion_id | |
| display_start_date | |
| display_end_date | |
| access_start_date | |
| access_end_date | |
| coach_access_start_date | |
| coach_access_end_date | |
| send_subscription_notification |
Categories of sessions
| Field | Definition |
|---|---|
| id | |
| access_url_id | See "Common fields" |
| name | |
| date_start | |
| date_end |
Relationship between sessions and courses (which courses are contained in which sessions)
| Field | Definition |
|---|---|
| id | |
| session_id | See "Common fields" |
| c_id | See "Common fields" |
| nbr_users | |
| position |
Subscription of users to a course in a session
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| session_id | See "Common fields" |
| c_id | See "Common fields" |
| visibility | |
| status | |
| legal_agreement |
Subscription of users in a session. This also contains (if this is the case) information about where the user has been moved to (to another session) if that was the case.
| Field | Definition |
|---|---|
| id | |
| session_id | See "Common fields" |
| user_id | See "Common fields" |
| relation_type | |
| duration | |
| moved_to | |
| moved_status | |
| moved_at | |
| registered_at |
Platform settings
| Field | Definition |
|---|---|
| id | |
| variable | |
| subkey | |
| type | |
| category | |
| selected_value | |
| title | |
| comment | |
| scope | |
| subkeytext | |
| access_url | Reference to access_url.id
|
| access_url_changeable | |
| access_url_locked |
Platform settings' possible option
| Field | Definition |
|---|---|
| id | |
| variable | |
| value | |
| display_text |
...
| Field | Definition |
|---|---|
| survey_id | Unique identifier |
| code | Survey code (literal) |
| title | |
| subtitle | |
| author | |
| lang | |
| template | |
| intro | |
| surveythanks | |
| creation_date | |
| course_code |
...
| Field | Definition |
|---|---|
| question_id | |
| survey_id | |
| survey_question | |
| survey_question_comment | |
| type | |
| display | |
| sort | |
| code | |
| max_value |
...
| Field | Definition |
|---|---|
| question_option_id | |
| question_id | |
| survey_id | |
| option_text | |
| sort |
Skills
| Field | Definition |
|---|---|
| id | |
| profile_id | |
| name | |
| short_code | |
| description | |
| access_url_id | See "Common fields" |
| icon | |
| criteria | |
| status | |
| updated_at |
Possible levels for a skill
| Field | Definition |
|---|---|
| id | |
| profile_id | |
| name | |
| position | |
| short_name |
Groups of skills levels (level profiles)
| Field | Definition |
|---|---|
| id | |
| name | |
| description |
Linking betweek gradebooks (gradebook_category)
| Field | Definition |
|---|---|
| id | |
| gradebook_id | |
| skill_id | Reference to skill.id
|
| type |
Linking of skill with skill_profile
| Field | Definition |
|---|---|
| id | |
| skill_id | Reference to skill.id
|
| profile_id |
Hierarchy of skills between themselves
| Field | Definition |
|---|---|
| id | |
| skill_id | Reference to skill.id
|
| parent_id | |
| relation_type | |
| level |
When a user gets a skill, this table registers it
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| skill_id | Reference to skill.id
|
| course_id | See "Common fields" (course.id) |
| session_id | See "Common fields" |
| acquired_level | |
| acquired_skill_at | |
| assigned_by | References user.id
|
| argumentation | |
| argumentation_author_id | References user.id
|
Users having obtained skills can get feedback from other users. They are stored here.
| Field | Definition |
|---|---|
| id | |
| skill_rel_user_id | References skill_rel_user.id. |
| feedback_giver_id | References user.id. Represents the person giving feedback. |
| feedback_text | |
| feedback_value | |
| feedback_datetime |
...
| Field | Definition |
|---|---|
| id | |
| code | |
| name |
...
| Field | Definition |
|---|---|
| id | |
| course_code | References course.code. |
| tool_id | |
| ref_id | |
| field_id | |
| value |
Global announcements definitions
| Field | Definition |
|---|---|
| id | |
| date_start | |
| date_end | |
| visible_teacher | Whether this announcement is visible to teachers (user.status = 1) |
| visible_student | Whether this announcement is visible to students (user.status = 5) |
| visible_guest | Whether this announcement is visible to guests (all users, even not identified) |
| title | |
| content | |
| lang | |
| access_url_id | See "Common fields" |
Global agenda events (not repetition possible)
| Field | Definition |
|---|---|
| id | |
| title | |
| content | |
| start_date | Datetime |
| end_date | Datetime |
| access_url_id | See "Common fields" |
| all_day | Whether this event happens all day long or just for the given time start and end. |
Global document templates
| Field | Definition |
|---|---|
| id | |
| title | |
| comment | |
| image | |
| content |
Tags can be assigned to extra fields.
| Field | Definition |
|---|---|
| id | |
| tag | |
| field_id | References extra_field.id. |
| count |
...
| Field | Definition |
|---|---|
| id | |
| title | |
| description | |
| course_code | References course.code (will be converted to course.id in future major versions) |
| user_id | See "Common fields" |
| ref_doc | |
| image |
...
| Field | Definition |
|---|---|
| id | |
| ticket_id | |
| user_id | See "Common fields" |
| sys_insert_user_id | See "Common fields" |
| assigned_date |
...
| Field | Definition |
|---|---|
| id | |
| project_id | References ticket_project.id
|
| name | |
| description | |
| total_tickets | |
| course_required | |
| sys_insert_user_id | See "Common fields" |
| sys_insert_datetime | |
| sys_lastedit_user_id | See "Common fields" |
| sys_lastedit_datetime |
...
| Field | Definition |
|---|---|
| id | |
| category_id | References ticket_category.id
|
| user_id | See "Common fields" |
...
| Field | Definition |
|---|---|
| id | |
| ticket_id | References ticket_ticket.id
|
| subject | |
| message | |
| status | |
| ip_address | |
| sys_insert_user_id | See "Common fields" |
| sys_insert_datetime | |
| sys_lastedit_user_id | See "Common fields" |
| sys_lastedit_datetime |
...
| Field | Definition |
|---|---|
| id | |
| ticket_id | References ticket_ticket.id
|
| message_id | References ticket_message.id
|
| path | |
| filename | |
| size | |
| sys_insert_user_id | |
| sys_insert_datetime | |
| sys_lastedit_user_id | |
| sys_lastedit_datetime |
...
| Field | Definition |
|---|---|
| id | |
| name | |
| code | |
| description | |
| color | |
| urgency | |
| sys_insert_user_id | See "Common fields" |
| sys_insert_datetime | |
| sys_lastedit_user_id | See "Common fields" |
| sys_lastedit_datetime |
...
| Field | Definition |
|---|---|
| id | |
| name | |
| description | |
| other_area | |
| sys_insert_user_id | See "Common fields" |
| sys_insert_datetime | |
| sys_lastedit_user_id | See "Common fields" |
| sys_lastedit_datetime |
...
| Field | Definition |
|---|---|
| id | |
| code | |
| name | |
| description |
...
| Field | Definition |
|---|---|
| id | |
| project_id | References ticket_project.id
|
| category_id | References ticket_category.id
|
| priority_id | References ticket_priority.id
|
| course_id | See "Common fields" (course.id) |
| session_id | See "Common fields" |
| status_id | References ticket_status.id
|
| code | |
| subject | |
| message | |
| personal_email | |
| assigned_last_user | References user.id
|
| total_messages | |
| keyword | |
| source | |
| start_date | |
| end_date | |
| sys_insert_user_id | See "Common fields" |
| sys_insert_datetime | |
| sys_lastedit_user_id | See "Common fields" |
| sys_lastedit_datetime |
Ranking based on score voted by users on courses
| Field | Definition |
|---|---|
| id | |
| c_id | See "Common fields" |
| session_id | See "Common fields" |
| url_id | See "Common fields" |
| accesses | |
| total_score | |
| users | |
| creation_date |
Log of all accesses to courses, tools and sessions in the platform. It does not track all actions, though. Only "entering" each course and tool
| Field | Definition |
|---|---|
| access_id | Unique identifier |
| access_user_id | See "Common fields" |
| access_date | |
| c_id | See "Common fields" |
| access_tool | NULL or any English tool name (user, learnpath, quiz, chat, document, forum, link, etc) |
| access_session_id | See "Common fields" |
| user_ip | IP address |
Log of all attempts (answers to questions) in quizzes (c_quiz). These are sub-elements of track_e_exercises.
| Field | Definition |
|---|---|
| id | Unique identifier of the attempt (of answer to a question) |
| exe_id | References track_e_exercises.exe_id
|
| user_id | See "Common fields" |
| question_id | References c_quiz_question.iid
|
| answer | |
| teacher_comment | |
| marks | Score attributed |
| c_id | See "Common fields" |
| position | Internal helper to get in which order the questions were answered, so as to present them faster and avoid issues with random question orders. |
| tms | |
| session_id | See "Common fields" |
| filename | |
| seconds_spent | The number of seconds spent on this answer |
...
| Field | Definition |
|---|---|
| id | |
| attempt_id | References track_e_attempt.id
|
| marks_coeff |
(experimental) Log of historical changes to answers. This is not working properly yet in 1.11.x
| Field | Definition |
|---|---|
| id | Unique identifier |
| exe_id | - |
| question_id | |
| answer | |
| marks | |
| insert_date | |
| author | |
| teacher_comment | |
| session_id | See "Common fields" |
Logs all login/logout actions to courses. This can be confusing when users open tabs to different courses at the same time.
| Field | Definition |
|---|---|
| course_access_id | unique ID |
| c_id | See "Common fields" |
| user_id | See "Common fields" |
| login_course_date | datetime field for when the user logged in |
| logout_course_date | datetime field for when the user logged out |
| counter | number of actions the user took in this course between login and logout |
| session_id | See "Common fields" |
| user_ip | IP address (v4 or v6) of the user when logged in |
Log of important actions in the system. This can be considered an audit table, as it records actions like user/course/session creations/deletions
| Field | Definition |
|---|---|
| default_id | |
| default_user_id | See "Common fields" |
| c_id | See "Common fields" |
| default_date | |
| default_event_type | |
| default_value_type | |
| default_value | |
| session_id | See "Common fields" |
Log of all documents downloads (only logs documents that exist in the c_document table)
| Field | Definition |
|---|---|
| down_id | Unique identifier |
| down_user_id | See "Common fields" |
| down_date | |
| c_id | See "Common fields" |
| down_doc_path | |
| down_session_id | See "Common fields" |
Log of all quiz (c_quiz) attempts by a user. For the detail of each answer, check track_e_attempt
| Field | Definition |
|---|---|
| exe_id | Unique identifier |
| exe_user_id | See "Common fields" |
| exe_date | Datetime (UTC) at which the attempt started |
| c_id | See "Common fields" |
| exe_exo_id | References c_quiz.iid
|
| exe_result | Score obtained by the user |
| exe_weighting | Max score based on the configuration of questions |
| user_ip | IP address |
| status | Current status of the exercise attempt. Can be 'incomplete' or '' if completed. |
| data_tracking | The sequence (separated by semi-columns) of questions to be answered in this attempt. |
| start_date | Datetime (UTC) at which the user entered the intro for the exercise. |
| steps_counter | |
| session_id | See "Common fields" |
| orig_lp_id | References c_lp.iid if this attempt was held inside a learning path |
| orig_lp_item_id | References c_lp_item.iid if this attempt was held inside a learning path. In this case, the iid is the item that links to c_quiz.iid. |
| exe_duration | Number of seconds the whole exercise attempt lasted |
| expired_time_control | A datetime (UTC) of the time when the exercise is over because the total time allowed has expired. |
| orig_lp_item_view_id | References c_lp_item_view.iid if this attempt was held inside a learning path |
| questions_to_check | If the user has marked some questions for review, this field will contain a semicolumn-separated list of c_quiz_question. iid marked for review |
Log of scores at quizzes of type HotPotatoes
| Field | Definition |
|---|---|
| id | |
| exe_name | |
| exe_user_id | See "Common fields" |
| exe_date | |
| c_id | See "Common fields" |
| exe_result | Score obtained |
| exe_weighting | Maximum score achievable |
Log of additional data regarding questions of HotSpot type (questions on images).
| Field | Definition |
|---|---|
| hotspot_id | unique id |
| hotspot_user_id | User ID |
| hotspot_course_code | Course code (not ID) |
| c_id | See "Common fields" |
| hotspot_exe_id | References c_quiz.iid(?) |
| hotspot_question_id | References c_quiz_question.iid
|
| hotspot_answer_id | References c_quiz_answer.iid
|
| hotspot_correct | |
| hotspot_coordinate |
Log of changes in the c_item_property table
| Field | Definition |
|---|---|
| id | Unique ID |
| course_id | See "Common fields" (course.id) |
| item_property_id | ID in the c_item_property table (FK) |
| title | |
| content | |
| progress | |
| lastedit_date | datetime |
| lastedit_user_id | See "Common fields" |
| session_id | See "Common fields" |
Log of accesses to tools in courses
| Field | Definition |
|---|---|
| access_id | unique ID |
| access_user_id | See "Common fields" |
| access_date | datetime (in UTC) |
| c_id | See "Common fields" |
| access_tool | name of the tool |
| access_session_id | See "Common fields" |
Log of accesses to links in the course's links tool (c_link)
| Field | Definition |
|---|---|
| links_id | unique id |
| links_user_id | See "Common fields" |
| links_date | datetime |
| c_id | See "Common fields" |
| links_link_id | iid from c_link (FK) |
| links_session_id | See "Common fields" |
Log all login and logout actions, with no differentiation of what the user did (see track_e_lastaccess for that)
| Field | Definition |
|---|---|
| login_id | unique ID |
| login_user_id | See "Common fields" |
| login_date | datetime |
| user_ip | IP address (v4 or v6) |
| logout_date | datetime |
Log online presence of users. Used to tell if the user is online by applying a difference to the current datetime
| Field | Definition |
|---|---|
| login_id | unique ID |
| login_user_id | See "Common fields" |
| login_date | datetime |
| user_ip | IP address (v4 or v6) |
| c_id | See "Common fields" |
| session_id | See "Common fields" |
| access_url_id | See "Common fields" |
deprecated
| Field | Definition |
|---|---|
| open_id | |
| open_remote_host | |
| open_agent | |
| open_referer | |
| open_date |
Log uploads
| Field | Definition |
|---|---|
| upload_id | unique ID |
| upload_user_id | See "Common fields" |
| upload_date | datetime |
| upload_cours_id | deprecated |
| c_id | See "Common fields" |
| upload_work_id | References c_student_publication.iid
|
| upload_session_id | See "Common fields" |
Deprecated
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| sco_id | |
| course_id | See "Common fields" |
| sv_key | |
| sv_value |
deprecated
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| sco_id | |
| stack_order | |
| course_id | See "Common fields" |
| sv_key | |
| sv_value |
Users of the system
| Field | Definition |
|---|---|
| id | Unique ID |
| user_id | deprecated |
| username | The username used to login |
| username_canonical | a cleaned version of the username |
| email_canonical | a cleaned version of the email |
| the user e-mail | |
| locked | 1 if the user account has been locked from the system |
| enabled | 1 if the user account has been enabled |
| expired | 1 if the user account has expired |
| credentials_expired | (not yet used) 1 if the user's credentials have expired |
| credentials_expire_at | (not yet used) expiry datetime for the credentials |
| expires_at | (not used yet) datetime at which the user account expires - see expiration_date field below |
| lastname | User's lastname |
| firstname | User's firstname |
| password | Password (hashed/encrypted following the method defined by $_configuration['password_encryption'] in app/config/configuration.php
|
| phone | Phone number |
| address | (not yet used) Address |
| salt | Value used to encrypt the password if the corresponding encryption method requires it |
| last_login | The datetime of the last login for this user |
| created_at | (not used yet) The datetime of creation - see registration_date field below |
| updated_at | The datetime of last update of the user account |
| confirmation_token | String used for the password change confirmation |
| password_requested_at | Datetime at which the password regen was requested |
| roles | an internal array with some information. Not really used at this point. |
| profile_completed | 1 if the profile has been completed |
| auth_source | 'platform' by default. If using LDAP or other authentication methods, this will vary based on the system used. |
| status | User default role. 1 if the user is a teacher, 5 if the user is a student. Check main/inc/lib/api.lib.php for more roles |
| official_code | Optional code |
| picture_uri | URL of the user picture |
| creator_id | The user ID of the person who created this user |
| competences | Part of portfolio fields |
| diplomas | Part of portfolio fields |
| openarea | Part of portfolio fields |
| teach | Part of portfolio fields |
| productions | Part of portfolio fields |
| language | Interface language for this user (this is a string but references a language in the language table) |
| registration_date | Registration datetime |
| expiration_date | Expiration datetime |
| active | 1 if the user account is active, 0 otherwise. An inactive user will not be able to connect to the platform. |
| openid | A token if using early versions of the OpenID implementation in Chamilo |
| theme | Default theme |
| hr_dept_id | deprecated |
API keys for users (for external systems)
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| api_key | |
| api_service | |
| api_end_point | |
| created_date | |
| validity_start_date | |
| validity_end_date | |
| description |
Courses categories defined by users in "sort my courses" feature
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| title | |
| sort |
Dictionary table for the types of social relation types
| Field | Definition |
|---|---|
| id | |
| title |
Link between users and courses to register user's vote on the course
| Field | Definition |
|---|---|
| id | unique ID |
| c_id | See "Common fields" |
| user_id | See "Common fields" |
| session_id | See "Common fields" |
| url_id | See "Common fields" |
| vote | int value (usually 0-5) |
...
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| event_type_name |
link between user and tag
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| tag_id |
links between users
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| friend_user_id | See "Common fields" |
| relation_type | |
| last_edit |
User groups (global groups) (see classes and social groups in the administration)
| Field | Definition |
|---|---|
| id | |
| name | |
| description | |
| group_type | |
| picture | Path to an image that represents the group. |
| url | A URL linked to this group (external website or so) |
| visibility | |
| allow_members_leave_group | |
| created_at | |
| updated_at |
Link between usergroup and course
| Field | Definition |
|---|---|
| id | |
| usergroup_id | References usergroup.id
|
| course_id | See "Common fields" |
Link between usergroup and c_quiz_question
| Field | Definition |
|---|---|
| id | |
| c_id | See "Common fields" |
| question_id | |
| usergroup_id | References usergroup.id
|
| coefficient |
Link between usergroup and session
| Field | Definition |
|---|---|
| id | |
| usergroup_id | References usergroup.id
|
| session_id | See "Common fields" |
Link between usergroup and user
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| usergroup_id | References usergroup.id
|
| relation_type |
Link between usergroup and itself
| Field | Definition |
|---|---|
| id | |
| group_id | References usergroup.id
|
| subgroup_id | References usergroup.id
|
| relation_type |
If you wanted to know how many errors have been made in a quiz over a certain period of time, you could proceed this way:
- query track_e_exercices.tms between two dates for the given exe_exo_id (referencing c_quiz.iid)
- link track_e_attempt to track_e_exercices on the exe_id field
- check how many track_e_attempt.marks are = 0 (this is not 100% reliable but will give you a good approximation)
In some cases, you might want to generate an intermediary table with progress results from your students. We did that in a specific case (generating a table called track_x_course_progress) which might be useful to others, so here are the details:
track_x_course_progress
| Field | Definition |
|---|---|
| user_id | Internal ID |
| firstname | |
| lastname | |
| course_id | Internal course/training ID |
| lp_id | Internal lesson (lp) ID |
| lesson_title | Lesson (lp) title |
| progress | Progress the user reached in this lesson (lp) in percentage points |
| first_access | Datetime (YYYY-MM-DD hh:mm:ss) of the first access to the course (not the lesson) |
| last_view | Datetime (YYYY-MM-DD hh:mm:ss) of the last access to the course (not the lesson) |
| seconds_in_lesson | The number of seconds spent by the user in this particular lesson (lp) |
| update_timestamp | A note of when this row was last updated |
Then some indexes might be useful:
- ALTER TABLE track_x_course_progress ADD INDEX idx_txcp_uid (user_id);
- ALTER TABLE track_x_course_progress ADD INDEX idx_txcp_cid (course_id);
- ALTER TABLE track_x_course_progress ADD INDEX idx_txcp_cname (course_name(100));
- ALTER TABLE track_x_course_progress ADD INDEX idx_txcp_lpid (lp_id);
Of course, this table needs to be generated from the raw data (probably once a day to avoid overload), but that is left as an exercise for the reader.
-
Home
- Tools and sessions
- Quiz: Importing
- Releases
- Community support strategy
- Translation management
- How to report issues
- Development
- Integration