2.0 data dictionary - chamilo/chamilo-lms GitHub Wiki
This page is an (incomplete) documentation of the data structure in Chamilo 1.11.
Incomplete data at this point - do not use
Notes
General principles
Entities
All tables are now reflected by Doctrine entities defined in the code in either src/CoreBundle/Entity or src/CourseBundle/Entity.
All entities defined in src/CourseBundleEntity are related to learning tools and generally keep a table name prefix of c_ for historical reasons. The table where documents are stored is called c_document and the entity is CDocument and not just Document, but a user is not a resource of a learning tool, so its table is called user and its entity is User.
Course-related entities generally use the iid identifier, unless they are relatively new tables (in this case they use the id field).
Sessions
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.
Resources
A major difference in database structure between Chamilo 1 and Chamilo 2 is we now consider almost everything that is not static as a resource. This is reflected by the following trio of tables:
resource_node, which contains the essence of the resource,resource_link, which contains the allocation of a resource to a context like a course, a session, a group, etc) and its properties in this context. There can be many links for each resource.resource_filewhich contains the files attached to the resource, if any. There can be many files (different formats or variations) for one resource.
Common fields
- 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) - All tables that require it have a
titlefield. This is a significant difference with Chamilo 1.x as, previously, it could have beenname,titleor something else completely. Now you can trust the existence of atitlefield pretty much anywhere except where we don't need a title because the entity is not editable/viewable on its own. - Most tables have
created_atandupdated_atfields. This allows to very quickly know if something is recent.
As such, these fields are not detailed below.
Other notes
At the end of this page, we provide a few examples of inter-table queries that might be useful.
Tables and fields
access_url
List of all URLs if using multiple URLs
| Field | Definition |
|---|---|
| id | Unique identifier |
| resource_node_id | A 1 to 1 reference to resource_node |
| parent_id | |
| tree_root | |
| 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 | |
| is_login_only | In complex setups, one URL can be "sacrified" to be a URL to only login and dispatch users to other URLs |
access_url_rel_color_theme
Link between the access_url table and the color_theme table. Manages the relationship between URLs and the stylesheet that is applied to them.
| Field | Definition |
|---|---|
| id | Unique identifier |
| url_id | See "Common fields" |
| color_theme_id | ID of the row in color_theme |
| active | Only used actively if = 1 |
| created_at | See "Common fields" |
| updated_at | See "Common fields" |
access_url_rel_course
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" |
access_url_rel_course_category
Link between access_url and course_category
| Field | Definition |
|---|---|
| id | Unique identifier |
| access_url_id | See "Common fields" |
| course_category_id |
access_url_rel_session
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" |
access_url_rel_user
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) |
access_url_rel_usergroup
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) |
admin
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" |
agenda_event_invitation
| Field | Definition |
|---|---|
| id | |
| creator_id | |
| created_at | |
| updated_at | |
| type | |
| max_attendees |
agenda_event_invitee
| Field | Definition |
|---|---|
| id | |
| invitation_id | |
| user_id | |
| created_at | |
| updated_at | |
| type |
agenda_reminder
| Field | Definition |
|---|---|
| id | |
| type | |
| event_id | |
| date_interval | |
| sent | |
| created_at | |
| updated_at |
announcement_rel_group
Link between sys_announcement and usergroup
| Field | Definition |
|---|---|
| group_id | |
| announcement_id | id from sys_announcement |
asset
| Field | Definition |
|---|---|
| id | |
| title | |
| category | |
| compressed | |
| mime_type | |
| original_name | |
| dimensions | |
| size | |
| crop | |
| metadata | |
| description | |
| updated_at | |
| created_at |
attempt_feedback
| Field | Definition |
|---|---|
| id | |
| attempt_id |
block
(not yet used)
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| path | |
| controller | |
| active |
branch_sync
(not in use yet) Allows for the synchronisation of Chamilo portals between themselves (not used yet)
| Field | Definition |
|---|---|
| id | Unique identifier |
| ... | ... |
branch_transaction
(not in use yet) Allows for the registration of transactions (pending or executed) from another system, to be executed in Chamilo
| Field | Definition |
|---|---|
| ... | |
| ... |
branch_transaction_status
(not Dictionary of status terms for branch_transaction
| Field | Definition |
|---|---|
| ... |
c_announcement
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" |
c_announcement_attachment
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 |
c_attendance
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 |
c_attendance_calendar
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 |
c_attendance_calendar_rel_group
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 |
c_attendance_result
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 |
c_attendance_sheet
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 |
c_attendance_sheet_log
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 |
c_blog
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" |
c_blog_attachment
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 |
c_blog_comment
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 |
c_blog_post
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 |
c_blog_rating
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 |
c_blog_rel_user
Link between c_blog and user
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| blog_id | |
| user_id | See "Common fields" |
c_blog_task
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 |
c_blog_task_rel_user
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 |
c_calendar_event
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 |
c_calendar_event_attachment
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 |
c_calendar_event_repeat
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 |
c_calendar_event_repeat_not
Definition of exclusions/exceptions in repetitions for existing events
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| cal_id | |
| cal_date |
c_chat_connected
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 |
c_course_description
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 |
c_course_setting
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 |
c_document
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" |
c_dropbox_category
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" |
c_dropbox_feedback
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 |
c_dropbox_file
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" |
c_dropbox_person
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" |
c_dropbox_post
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. |
c_exercise_category
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 |
c_forum_attachment
File attachments to c_forum_post records
| Field | Definition |
|---|---|
| iid | Unique identifier |
| id | |
| c_id | See "Common fields" |
| path | |
| comment | |
| size | |
| post_id | |
| filename |
c_forum_category
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 |
c_forum_forum
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 |
c_forum_mailcue
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 |
c_forum_notification
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | |
| user_id | See "Common fields" |
| forum_id | |
| thread_id | |
| post_id |
c_forum_post
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 |
c_forum_thread
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 |
c_forum_thread_qualify
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" |
c_forum_thread_qualify_log
A log table of all the changes in the forum threads scores
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_glossary
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" |
c_group_category
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 |
c_group_info
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" |
c_group_rel_tutor
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" |
c_group_rel_user
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 |
c_item_property
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 |
c_link
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" |
c_link_category
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" |
c_lp
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 |
c_lp_category
Categories for courses learning paths
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| name | |
| position |
c_lp_category_user
Access specifications for specific users to specific learning paths categories
| Field | Definition |
|---|---|
| iid | Unique identifier |
| category_id | |
| user_id | See "Common fields" |
c_lp_item
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 |
c_lp_item_view
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 |
c_lp_iv_interaction
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 |
c_lp_iv_objective
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 |
c_lp_view
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" |
c_notebook
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 |
c_online_connected
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| last_connection | |
| user_id | See "Common fields" |
c_online_link
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| name | |
| url |
c_permission_group
Blog-related user permissions ...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_permission_task
Blog-releated user permissions in tasks ...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_permission_user
Blog-related user permissions ...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_quiz
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) |
c_quiz_answer
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 |
c_quiz_question
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 |
c_quiz_question_category
Categories of questions in quizzes
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| title | |
| description |
c_quiz_question_option
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| question_id | |
| name | |
| position |
c_quiz_question_rel_category
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 |
c_quiz_rel_category
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 |
c_quiz_rel_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 |
c_resource
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| source_type | |
| source_id | |
| resource_type | |
| resource_id |
c_role
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_role_group
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_role_permissions
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_role_user
Blog-related roles
| Field | Definition |
|---|---|
| iid | Unique identifier |
| ... |
c_student_publication
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 |
c_student_publication_assignment
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 |
c_student_publication_comment
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 |
c_student_publication_rel_document
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 |
c_student_publication_rel_user
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" |
c_survey
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 |
c_survey_answer
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 |
c_survey_group
...
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| name | |
| description | |
| survey_id |
c_survey_invitation
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 |
c_survey_question
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) |
c_survey_question_option
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 |
c_thematic
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" |
c_thematic_advance
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 |
c_thematic_plan
Second level of thematic progress
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| thematic_id | |
| title | |
| description | |
| description_type |
c_tool
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 |
c_tool_intro
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" |
c_userinfo_content
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 |
c_userinfo_def
Deprecated: User fields in courses
| Field | Definition |
|---|---|
| iid | Unique identifier |
| c_id | See "Common fields" |
| id | deprecated |
| title | |
| comment | |
| line_count | |
| rank |
c_wiki
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" |
c_wiki_conf
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 |
c_wiki_discuss
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 |
c_wiki_mailcue
...
| 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" |
career
Careers (admin panel)
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| status | |
| created_at | |
| updated_at |
chat
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 |
chat_video
Videochat sessions (temporarily deprecated)
| Field | Definition |
|---|---|
| id | Unique identifier |
| from_user | |
| to_user | |
| room_name | |
| datetime |
class_item
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| code | |
| name |
class_user
Link between classes and users
| Field | Definition |
|---|---|
| class_id | |
| user_id | See "Common fields" |
course
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 |
course_category
Courses categories
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| code | |
| parent_id | |
| tree_pos | |
| children_count | |
| auth_course_child | |
| auth_cat_child |
course_module
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| link | |
| image | |
| row | |
| column | |
| position |
course_rel_class
Relationship between course and usergroup
| Field | Definition |
|---|---|
| course_code | FK to course.code! (to be changed to id in the future) |
| class_id |
course_rel_user
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 |
course_rel_user_catalogue
Special accesses to catalogue by specific users
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_id | See "Common fields" |
| c_id | See "Common fields" |
| visible |
course_request
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 |
course_type
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| translation_var | |
| description | |
| props |
event_email_template
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| message | |
| subject | |
| event_type_name | |
| activated | |
| language_id |
event_sent
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_from | References user.id. Represents the user who generated the event. |
| user_to | |
| event_type_name |
extra_field
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 |
extra_field_option_rel_field_option
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_option_id | |
| related_field_option_id | |
| role_id | |
| field_id |
extra_field_options
Possible values for extra_field_values
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_id | |
| option_value | |
| display_text | |
| priority | |
| priority_message | |
| option_order |
extra_field_rel_tag
Tags on extra fields
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_id | |
| tag_id | |
| item_id |
extra_field_saved_search
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| field_id | |
| user_id | See "Common fields" |
| value | |
| created_at | |
| updated_at |
extra_field_values
Values stored for specific resources in specific extra fields
| Field | Definition |
|---|---|
| field_id | |
| value | |
| item_id | |
| created_at | |
| updated_at | |
| comment |
grade_components
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| percentage | |
| title | |
| acronym | |
| grade_model_id |
grade_model
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| description | |
| default_lowest_eval_exclude | |
| default_external_eval | |
| default_external_eval_prefix |
gradebook_category
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) |
gradebook_certificate
Generated certificates
| Field | Definition |
|---|---|
| id | Unique identifier |
| cat_id | |
| user_id | See "Common fields" |
| score_certificate | |
| created_at | |
| path_certificate |
gradebook_evaluation
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 |
gradebook_link
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 |
gradebook_linkeval_log
...
| Field | Definition |
|---|---|
| id | Unique identified |
| id_linkeval_log | |
| name | |
| description | |
| created_at | |
| weight | |
| visible | |
| type | |
| user_id_log | See "Common fields" |
gradebook_result
Results of users on gradebook_link or gradebook_evaluation
| Field | Definition |
|---|---|
| id | Unique identifier |
| user_id | See "Common fields" |
| evaluation_id | |
| created_at | |
| score |
gradebook_result_log
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| id_result | |
| user_id | See "Common fields" |
| evaluation_id | |
| created_at | |
| score |
gradebook_score_display
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| score | |
| display | |
| category_id | |
| score_color_percent |
gradebook_score_log
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| category_id | |
| user_id | See "Common fields" |
| score | |
| registered_at |
hook_call
Internal hooks definition
| Field | Definition |
|---|---|
| id | Unique identifier |
| hook_event_id | |
| hook_observer_id | |
| type | |
| hook_order | |
| enabled |
hook_event
Internal hooks definition
| Field | Definition |
|---|---|
| id | Unique identifier |
| class_name | |
| description |
hook_observer
Internal hooks definition
| Field | Definition |
|---|---|
| id | Unique identifier |
| class_name | |
| path | |
| plugin_name |
language
The language supported for the user interface, and sublanguages
| Field | Definition |
|---|---|
| id | Unique identifier |
| parent_id | |
| original_name | |
| english_name | |
| isocode | |
| dokeos_folder | |
| available |
legal
Terms and conditions
| Field | Definition |
|---|---|
| id | Unique identifier |
| date | |
| content | |
| type | |
| changes | |
| version | |
| language_id | References language.id |
mail_template
...
| Field | Definition |
|---|---|
| id | Unique identifier |
| name | |
| template | |
| type | |
| created_at | |
| updated_at | |
| author_id | |
| url_id | See "Common fields" |
| default_template | |
| system |
message
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 |
message_attachment
File attachments for message. Note that at this point the
| Field | Definition |
|---|---|
| id | |
| path | |
| comment | |
| size | |
| message_id | |
| filename |
message_likes
Likes given to messages on the social network
| Field | Definition |
|---|---|
| id | |
| message_id | |
| user_id | See "Common fields" |
| liked | |
| disliked | |
| updated_at |
notification
...
| Field | Definition |
|---|---|
| id | |
| dest_user_id | See "Common fields" |
| dest_mail | |
| title | |
| content | |
| send_freq | |
| created_at | |
| sent_at |
openid_association
...
| Field | Definition |
|---|---|
| id | |
| idp_endpoint_uri | |
| session_type | |
| assoc_handle | |
| assoc_type | |
| expires_in | |
| mac_key | |
| created |
personal_agenda
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 |
personal_agenda_repeat
Repetition on personal_agenda
| Field | Definition |
|---|---|
| cal_id | |
| cal_type | |
| cal_end | |
| cal_frequency | |
| cal_days |
personal_agenda_repeat_not
Repetition exclusions on personal_agenda_repeat
| Field | Definition |
|---|---|
| cal_id | |
| cal_date |
plugin_*
All plugin tables (see plugin documentation)
promotion
Promotions are contained in careers and contain sessions.
| Field | Definition |
|---|---|
| id | |
| career_id | |
| name | |
| description | |
| status | |
| created_at | |
| updated_at |
room
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 |
scheduled_announcements
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" |
search_engine_ref
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 |
sequence
Sequences for course/session access
| Field | Definition |
|---|---|
| id | |
| name | |
| graph | |
| created_at | |
| updated_at |
sequence_condition
Conditions for sequence
| Field | Definition |
|---|---|
| id | |
| description | |
| mat_op | |
| param | |
| act_true | |
| act_false |
sequence_formula
Formula for sequence
| Field | Definition |
|---|---|
| id | |
| sequence_method_id | |
| sequence_variable_id |
sequence_method
Method for sequence
| Field | Definition |
|---|---|
| id | |
| description | |
| formula | |
| assign | |
| met_type | |
| act_false |
sequence_resource
Method for resource
| Field | Definition |
|---|---|
| id | |
| sequence_id | |
| type | |
| resource_id |
sequence_row_entity
...
| Field | Definition |
|---|---|
| id | |
| sequence_type_entity_id | |
| c_id | See "Common fields" |
| session_id | See "Common fields" |
| row_id | |
| name |
sequence_rule
Rules for sequence
| Field | Definition |
|---|---|
| id | |
| description |
sequence_rule_condition
...
| Field | Definition |
|---|---|
| id | |
| sequence_rule_id | |
| sequence_condition_id |
sequence_rule_method
...
| Field | Definition |
|---|---|
| id | |
| sequence_rule_id | |
| sequence_method_id | |
| method_order |
sequence_type_entity
...
| Field | Definition |
|---|---|
| id | |
| name | |
| description | |
| ent_table |
sequence_valid
...
| Field | Definition |
|---|---|
| id | |
| sequence_variable_id | |
| sequence_condition_id |
sequence_value
...
| 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 |
sequence_variable
...
| Field | Definition |
|---|---|
| id | |
| name | |
| description | |
| default_val |
session
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 |
session_category
Categories of sessions
| Field | Definition |
|---|---|
| id | |
| access_url_id | See "Common fields" |
| name | |
| date_start | |
| date_end |
session_rel_course
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 |
session_rel_course_rel_user
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 |
session_rel_user
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 |
settings_current
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 |
settings_options
Platform settings' possible option
| Field | Definition |
|---|---|
| id | |
| variable | |
| value | |
| display_text |
shared_survey
...
| Field | Definition |
|---|---|
| survey_id | Unique identifier |
| code | Survey code (literal) |
| title | |
| subtitle | |
| author | |
| lang | |
| template | |
| intro | |
| surveythanks | |
| creation_date | |
| course_code |
shared_survey_question
...
| Field | Definition |
|---|---|
| question_id | |
| survey_id | |
| survey_question | |
| survey_question_comment | |
| type | |
| display | |
| sort | |
| code | |
| max_value |
shared_survey_question_option
...
| Field | Definition |
|---|---|
| question_option_id | |
| question_id | |
| survey_id | |
| option_text | |
| sort |
skill
Skills
| Field | Definition |
|---|---|
| id | |
| profile_id | |
| name | |
| short_code | |
| description | |
| access_url_id | See "Common fields" |
| icon | |
| criteria | |
| status | |
| updated_at |
skill_level
Possible levels for a skill
| Field | Definition |
|---|---|
| id | |
| profile_id | |
| name | |
| position | |
| short_name |
skill_profile
Groups of skills levels (level profiles)
| Field | Definition |
|---|---|
| id | |
| name | |
| description |
skill_rel_gradebook
Linking betweek gradebooks (gradebook_category)
| Field | Definition |
|---|---|
| id | |
| gradebook_id | |
| skill_id | Reference to skill.id |
| type |
skill_rel_profile
Linking of skill with skill_profile
| Field | Definition |
|---|---|
| id | |
| skill_id | Reference to skill.id |
| profile_id |
skill_rel_skill
Hierarchy of skills between themselves
| Field | Definition |
|---|---|
| id | |
| skill_id | Reference to skill.id |
| parent_id | |
| relation_type | |
| level |
skill_rel_user
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 |
skill_rel_user_comment
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 |
specific_field
...
| Field | Definition |
|---|---|
| id | |
| code | |
| name |
specific_field_values
...
| Field | Definition |
|---|---|
| id | |
| course_code | References course.code. |
| tool_id | |
| ref_id | |
| field_id | |
| value |
sys_announcement
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" |
sys_calendar
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. |
system_template
Global document templates
| Field | Definition |
|---|---|
| id | |
| title | |
| comment | |
| image | |
| content |
tag
Tags can be assigned to extra fields.
| Field | Definition |
|---|---|
| id | |
| tag | |
| field_id | References extra_field.id. |
| count |
templates
...
| 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 |
ticket_assigned_log
...
| Field | Definition |
|---|---|
| id | |
| ticket_id | |
| user_id | See "Common fields" |
| sys_insert_user_id | See "Common fields" |
| assigned_date |
ticket_category
...
| 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 |
ticket_category_rel_user
...
| Field | Definition |
|---|---|
| id | |
| category_id | References ticket_category.id |
| user_id | See "Common fields" |
ticket_message
...
| 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 |
ticket_message_attachments
...
| 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 |
ticket_priority
...
| 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 |
ticket_project
...
| 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 |
ticket_status
...
| Field | Definition |
|---|---|
| id | |
| code | |
| name | |
| description |
ticket_ticket
...
| 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 |
track_course_ranking
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 |
track_e_access
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 |
track_e_attempt
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 |
track_e_attempt_coeff
...
| Field | Definition |
|---|---|
| id | |
| attempt_id | References track_e_attempt.id |
| marks_coeff |
track_e_attempt_recording
(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" |
track_e_course_access
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 |
track_e_default
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" |
track_e_downloads
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" |
track_e_exercises
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 |
track_e_hotpotatoes
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 |
track_e_hotspot
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 |
track_e_item_property
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" |
track_e_lastaccess
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" |
track_e_links
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" |
track_e_login
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 |
track_e_online
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" |
track_e_open
deprecated
| Field | Definition |
|---|---|
| open_id | |
| open_remote_host | |
| open_agent | |
| open_referer | |
| open_date |
track_e_uploads
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" |
track_stored_values
Deprecated
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| sco_id | |
| course_id | See "Common fields" |
| sv_key | |
| sv_value |
track_stored_values_stack
deprecated
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| sco_id | |
| stack_order | |
| course_id | See "Common fields" |
| sv_key | |
| sv_value |
user
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 |
user_api_key
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 |
user_course_category
Courses categories defined by users in "sort my courses" feature
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| title | |
| sort |
user_friend_relation_type
Dictionary table for the types of social relation types
| Field | Definition |
|---|---|
| id | |
| title |
user_rel_course_vote
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) |
user_rel_event_type
...
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| event_type_name |
user_rel_tag
link between user and tag
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| tag_id |
user_rel_user
links between users
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| friend_user_id | See "Common fields" |
| relation_type | |
| last_edit |
usergroup
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 |
usergroup_rel_course
Link between usergroup and course
| Field | Definition |
|---|---|
| id | |
| usergroup_id | References usergroup.id |
| course_id | See "Common fields" |
usergroup_rel_question
Link between usergroup and c_quiz_question
| Field | Definition |
|---|---|
| id | |
| c_id | See "Common fields" |
| question_id | |
| usergroup_id | References usergroup.id |
| coefficient |
usergroup_rel_session
Link between usergroup and session
| Field | Definition |
|---|---|
| id | |
| usergroup_id | References usergroup.id |
| session_id | See "Common fields" |
usergroup_rel_user
Link between usergroup and user
| Field | Definition |
|---|---|
| id | |
| user_id | See "Common fields" |
| usergroup_id | References usergroup.id |
| relation_type |
usergroup_rel_usergroup
Link between usergroup and itself
| Field | Definition |
|---|---|
| id | |
| group_id | References usergroup.id |
| subgroup_id | References usergroup.id |
| relation_type |
Examples of mixed queries
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)
Extensions
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.