2.0 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 historically course-specific tables, but this is loosing meaning in C2 as the structure allows for resources to be shared between courses. They generally use the iid identifier, unless they are relatively new tables (in this case they use the id field).
Note 2: Sessions are really special, but they are used only about half of the time, so they will (more than not) be set to null in the tables where the session_id field appears. In previous versions, this field could equal 0. Not anymore. Queries can thus be simplified.
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 |
| parent_id | |
| tree_root | |
| resource_node_id | A 1 to 1 reference to resource_node |
| lft | |
| lvl | |
| rgt | |
| 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) |
| limit_courses | |
| limit_active_courses | |
| limit_sessions | |
| limit_users | |
| limit_teachers | |
| limit_disk_space | |
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 |
| session_id | See "Common fields" |
| access_url_id | See "Common fields" |
Link between access_url and user for users registered in a specific URL
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| access_url_id |
access_url id (FK) |
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" |
| Field | Definition |
|---|---|
| id | |
| creator_id | |
| created_at | |
| updated_at | |
| type | |
| max_attendees |
| Field | Definition |
|---|---|
| id | |
| invitation_id | |
| user_id | |
| created_at | |
| updated_at | |
| type |
| Field | Definition |
|---|---|
| id | |
| type | |
| event_id | |
| date_interval | |
| sent | |
| created_at | |
| updated_at |
Link between sys_announcement and usergroup
| Field | Definition |
|---|---|
| group_id | |
| announcement_id | id from sys_announcement
|
| Field | Definition |
|---|---|
| id | |
| title | |
| category | |
| compressed | |
| mime_type | |
| original_name | |
| dimensions | |
| size | |
| crop | |
| metadata | |
| description | |
| updated_at | |
| created_at |
| Field | Definition |
|---|---|
| id | |
| attempt_id |
(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