1.11 data dictionary

This page is an (incomplete) documentation of the data structure in Chamilo 1.11.

• All fields called c_id always point to the course.id field.
• All fields called user_id always point to the user.id field.
• All fields called session_id always point to the session.id field.

Note 1: All tables starting with a c_ are course-specific tables. They always include a reference to the course as the c_id field, and generally still hold a double identifier: iid is the new, platform-unique identifier, while the combination of id+c_id is the "old" unique identifier. Queries should use the iid field, although some have been left using id+c_id by lack of time during the transition.

Note 2: Sessions are really special, and most course resources tables include a session ID which can be either 0 or NULL (depending on the age of the table), or has a non-zero value. In case it has a (numeric) value, it means the resource is available inside the session-course and not inside the base course. If it is 0 or NULL, it means it is available inside the base course and for some resources, this means it is also visible in the session-course.

Table	Table's purpose	Field	Field's purpose
access_url	List of all URLs if using multiple URLs	id	Unique identifier
		url	URL (including protocol)
		description
		active	1 if the URL can already be used
		created_by	(FK) user.id
		tms	timestamp
		url_type	(not used yet)
access_url_rel_course	Link between the access_url table and the course table	id	Unique identifier
		c_id
		access_url_id
access_url_rel_course_category	Link between access_url and course_category	id	Unique identifier
		access_url_id
		course_category_id
access_url_rel_session	Link between access_url and session for sessions registered in a specific URL	id	Unique identifier
		access_url_id
		session_id
access_url_rel_user	Link between access_url and user for users registered in a specific URL	access_url_id	access_url id (FK)
		user_id
access_url_rel_usergroup	Link between access_url and usergroup for groups registered in a specific URL	id	Unique identifier
		access_url_id
		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)	id	Unique identifier
		user_id
announcement_rel_group	Link between sys_announcement and usergroup	group_id
		announcement_id	id from sys_announcement
block	(not yet used)	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)	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
branch_transaction_status	(not Dictionary of status terms for branch_transaction
c_announcement	Contains the announcements in the corresponding course tool	iid	Unique identifier
		id	deprecated
		c_id
		title
		content
		end_date
		display_order
		email_sent
		session_id
c_announcement_attachment	Contains the attachments (files) of c_announcement	iid	Unique identifier
		id	deprecated
		c_id
		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)	iid	Unique identifier
		c_id
		id	deprecated
		name
		description
		active
		attendance_qualify_title
		attendance_qualify_max
		attendance_weight
		session_id
		locked
c_attendance_calendar	Each attendance datetime is registered here separately.	iid	Unique identifier
		id	deprecated
		c_id
		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	iid	Unique identifier
		id	deprecated
		c_id
		group_id
		calendar_id
c_attendance_result	The global attendance "rate" of a user in an attendance resource	iid	Unique identifier
		id	deprecated
		c_id
		user_id
		attendance_id
		score
c_attendance_sheet	Registers the attendance or lack thereof of a user to a specific calendar datetime	iid	Unique identifier
		c_id
		presence
		user_id
		attendance_calendar_id
c_attendance_sheet_log	Log table for changes in the c_attendance_sheet	iid	Unique identifier
		id	deprecated
		c_id
		attendance_id
		lastedit_date
		lastedit_type
		lastedit_user_id
		calendar_date_value
c_blog	Entries for each blog created in a course	iid	Unique identifier
		c_id
		blog_id
		blog_name
		blog_subtitle
		date_creation
		visibility
		session_id
c_blog_attachment	File attachments to blog posts	iid	Unique identifier
		id	deprecated
		c_id
		path
		comment
		size
		post_id
		filename
		blog_id
		comment_id
c_blog_comment	Comments on blog posts	iid	Unique identifier
		comment_id	deprecated
		c_id
		title
		comment
		author_id
		date_creation
		blog_id
		post_id
		task_id
		parent_comment_id
c_blog_post	Messages posted in blogs	iid	Unique identifier
		blog_id
		c_id
		title
		full_text
		date_creation
		author_id
		post_id
c_blog_rating	Ratings given to blog posts	iid	Unique identifier
		rating_id
		c_id
		blog_id
		rating_type
		item_id
		user_id
		rating
c_blog_rel_user	Link between c_blog and user	iid	Unique identifier
		c_id
		blog_id
		user_id
c_blog_task	Tasks that can be configured inside a blog	iid	Unique identifier
		task_id
		c_id
		blog_id
		title
		description
		color
		system_task
c_blog_task_rel_user	Assignation of tasks (c_blog_task) to users (user)	iid	Unique identifier
		c_id
		blog_id
		target_date
		user_id
		task_id
c_calendar_event	Events of the calendar	iid	Unique identifier
		room_id	(not used yet)
		id
		c_id
		title
		content
		start_date
		end_date
		parent_event_id
		session_id
		all_day
		comment
		color
c_calendar_event_attachment	Attachment (files) to the c_calendar_event records	iid	Unique identifier
		id
		c_id
		path
		comment
		size
		agenda_id
		filename
c_calendar_event_repeat	Definition of repetitions for existing events	iid	Unique identifier
		cal_id
		c_id
		cal_type
		cal_end
		cal_frequency
		cal_days
c_calendar_event_repeat_not	Definition of exclusions/exceptions in repetitions for existing events	iid	Unique identifier
		c_id
		cal_id
		cal_date
c_chat_connected	Logs when a user is active in the course chat	iid	Unique identifier
		id
		c_id
		session_id
		to_group_id
		user_id
		last_connection
c_course_description	The sections available in the course description tool	iid	Unique identifier
		id
		c_id
		title
		content
		session_id
		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	iid	Unique identifier
		id
		c_id
		variable
		subkey
		type
		category
		value
		title
		comment
		subkeytext
c_document	The list of documents and folders of the documents tool	iid	Unique identifier
		id
		c_id
		path
		comment
		title
		filetype
		size
		readonly
		session_id
c_dropbox_category	Folders inside the dropbox	iid	Unique identifier
		c_id
		cat_id
		cat_name
		received
		sent
		user_id
		session_id
c_dropbox_feedback	Feedback sent by users on files in the dropbox	iid	Unique identifier
		c_id
		feedback_id
		file_id
		author_user_id
		feedback
		feedback_date
c_dropbox_file	A file dropped in the dropbox	iid	Unique identifier
		id
		c_id
		uploader_id
		filename
		filesize
		title
		description
		author
		upload_date
		last_upload_date
		cat_id
		session_id
c_dropbox_person	The relationships between files (c_dropbox_file) and whom they are sent to (user)	iid	Unique identifier
		c_id
		file_id
		user_id
c_dropbox_post	A message posted on a file (c_dropbox_file)	iid	Unique identifier
		feedback_date
		feedback
		cat_id
		session_id
		c_id
		file_id
		dest_user_id
c_exercise_category	Optional table created by manual action documented in configuration.php	iid	Unique identifier
		c_id
		name
		description
		created_at
		updated_at
		position
c_forum_attachment	File attachments to c_forum_post records	iid	Unique identifier
		id
		c_id
		path
		comment
		size
		post_id
		filename
c_forum_category	Forum categories (which include forums)	iid	Unique identifier
		...
c_forum_forum	Forums (which include threads)	iid	Unique identifier
		...
c_forum_mailcue	...	iid	Unique identifier
		...
c_forum_notification	...	iid	Unique identifier
		...
c_forum_post	Forum posts (in response to a thread)	iid	Unique identifier
		...
c_forum_thread	Forum threads (which give birth to posts and have more properties than posts)	iid	Unique identifier
		...
c_forum_thread_qualify	If a thread can be scored, the scores given by teachers or other users are stored here	iid	Unique identifier
		...
c_forum_thread_qualify_log	A log table of all the changes in the forum threads scores	iid	Unique identifier
		...
c_glossary	Terms from the course glossary	iid	Unique identifier
		...
c_group_category	Categories of course groups (c_group_info)	iid	Unique identifier
		...
c_group_info	Course groups and their description	iid	Unique identifier
		...
c_group_rel_tutor	Tutors assigned to course groups (c_group_info)	iid	Unique identifier
		...
c_group_rel_user	Users registered in course groups (c_group_info)	iid	Unique identifier
		...
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	iid	Unique identifier
		...
c_link	Course links	iid	Unique identifier
		...
c_link_category	Categories of course links (c_link)	iid	Unique identifier
		...
c_lp	Course learning paths	iid	Unique identifier
		...
c_lp_category	Categories for courses learning paths	iid	Unique identifier
		...
c_lp_category_user	Access specifications for specific users to specific learning paths categories	iid	Unique identifier
		...
c_lp_item	Documents inside a learning path	iid	Unique identifier
		...
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	iid	Unique identifier
		...
c_lp_iv_interaction	SCORM interactions for one specific c_lp_item_view	iid	Unique identifier
		...
c_lp_iv_objective	SCORM objectives for one specific c_lp_item_view	iid	Unique identifier
		...
c_lp_view	View of a learning path (c_lp) by a user. Contains many c_lp_view_item	iid	Unique identifier
		...
c_notebook	Posts in the notebook tool in the course (personal notes)	iid	Unique identifier
		...
c_online_connected	...	iid	Unique identifier
		...
c_online_link	...	iid	Unique identifier
		...
c_permission_group	Blog-related user permissions ...	iid	Unique identifier
		...
c_permission_task	Blog-releated user permissions in tasks ...	iid	Unique identifier
		...
c_permission_user	Blog-related user permissions ...	iid	Unique identifier
		...
c_quiz	List of exercises in courses	iid	Unique identifier
		...
c_quiz_answer	List of possible answers to a question (c_quiz_question) in a c_quiz	iid	Unique identifier
		...
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	iid	Unique identifier
		...
c_quiz_question_category	Categories of questions in quizzes	iid	Unique identifier
		...
c_quiz_question_option	...	iid	Unique identifier
		...
c_quiz_question_rel_category	Relationship between a c_quiz_question and a c_quiz_question_category	iid	Unique identifier
		...
c_quiz_rel_category	Relationship between a c_quiz and a quiz category (see c_exercise_category)	iid	Unique identifier
		...
c_quiz_rel_question	Relationship between a question (c_quiz_question) and a specific quiz (c_quiz)	iid	Unique identifier
		...
c_resource	...	iid	Unique identifier
		...
c_role	Blog-related roles	iid	Unique identifier
		...
c_role_group	Blog-related roles	iid	Unique identifier
		...
c_role_permissions	Blog-related roles	iid	Unique identifier
		...
c_role_user	Blog-related roles	iid	Unique identifier
		...
c_student_publication	An assignment handed over by a student	iid	Unique identifier
		...
c_student_publication_assignment	Assigments definition (tasks/works)	iid	Unique identifier
		...
c_student_publication_comment	Comments on c_student_publication	iid	Unique identifier
		...
c_student_publication_rel_document	Link between a document (c_document) that serves as a template to the assignments	iid	Unique identifier
		...
c_student_publication_rel_user	Link between users and assignments (to assign specific assignments to a reduced number of users)	iid	Unique identifier
		...
c_survey	Surveys	iid	Unique identifier
		...
c_survey_answer	Possible answers to survey questions (c_survey_question)	iid	Unique identifier
		...
c_survey_group	...	iid	Unique identifier
		...
c_survey_invitation	Invitations of users to surveys	iid	Unique identifier
		...
c_survey_question	Survey questions	iid	Unique identifier
		...
c_survey_question_option	Options on survey questions	iid	Unique identifier
		...
c_thematic	Highest level of thematic progress categorization	iid	Unique identifier
		...
c_thematic_advance	Dates related to the progress in a thematic plan	iid	Unique identifier
		...
c_thematic_plan	Second level of thematic progress	iid	Unique identifier
		...
c_tool	Entries of available tools on the course homepage	iid	Unique identifier
		...
c_tool_intro	Introduction sections. On the course homepage but also (if enabled) in each tool.	iid	Unique identifier
		...
c_userinfo_content	Deprecated: Values for user fields in courses	iid	Unique identifier
		...
c_userinfo_def	Deprecated: User fields in courses	iid	Unique identifier
		...
c_wiki	Wiki pages in course	iid	Unique identifier
		...
c_wiki_conf	Wiki configuration in a course	iid	Unique identifier
		...
c_wiki_discuss	Conversations on each wiki page	iid	Unique identifier
		...
c_wiki_mailcue	...	iid	Unique identifier
		...
career	Careers (admin panel)
		...
chat	Chat sessions
		...
chat_video	Videochat sessions (temporarily deprecated)
		...
class_item	...
		...
class_user	...
		...
course	Courses	id	Unique identified (referenced by c_id everywhere else)
		...
course_category	Courses categories
		...
course_module	...
		...
course_rel_class	Relationship between course and usergroup
		...
course_rel_user	Subscription of users to courses (if not using sessions)
		...
course_rel_user_catalogue	Special accesses to catalogue by specific users
		...
course_request	Requests for courses by users (if the course requests feature is enabled)
		...
course_type	...
		...
event_email_template	...
		...
event_sent	...
		...
extra_field	Extra fields definition. Also defines which type of resource it applies to (users, courses, quizzes, etc)
		...
extra_field_option_rel_field_option	...
		...
extra_field_options	Possible values for extra_field_values
		...
extra_field_rel_tag	Tags on extra fields
		...
extra_field_saved_search	...
		...
extra_field_values	Values stored for specific resources in specific extra fields
		...
grade_components	...
		...
grade_model	...
		...
gradebook_category	Highest level of the gradebook structure (attributing certificates and skills)
		...
gradebook_certificate	Generated certificates
		...
gradebook_evaluation	External evaluation scored in the gradebook
		...
gradebook_link	Internal evaluation (Chamilo resources) scored in the gradebook
		...
gradebook_linkeval_log	...
		...
gradebook_result	Results of users on gradebook_link or gradebook_evaluation
		...
gradebook_result_log	...
		...
gradebook_score_display	...
		...
gradebook_score_log	...
		...
hook_call	Internal hooks definition
		...
hook_event	Internal hooks definition
		...
hook_observer	Internal hooks definition
		...
language	The language supported for the user interface, and sublanguages
		...
legal	Terms and conditions
		...
mail_template	...
		...
message	User messages on the social network + all messages sent by the system by e-mail and saved for the local inbox
		...
message_attachment	File attachments for message
		...
message_likes	Likes given to messages on the social network
		...
notification	...
		...
openid_association	...
		...
personal_agenda	Personal events stored in the agenda
		...
personal_agenda_repeat	Repetition on personal_agenda
		...
personal_agenda_repeat_not	Repetition exclusions on personal_agenda_repeat
		...
plugin_*	All plugin tables (see plugin documentation)
		...
promotion	Promotions are contained in careers and contain sessions.
		...
room	Rooms are an item for a future feature in Chamilo allowing you to define a physical or virtual room for a synchronous lesson.
		...
scheduled_announcements	If the scheduled announcements for sessions are enabled, contains the messages scheduled to be sent at specific dates to session users
		...
search_engine_ref	Internal data on indexer with Xapian
		...
sequence	Sequences for course/session access
		...
sequence_condition	Conditions for sequence
		...
sequence_formula	Formula for sequence
		...
sequence_method	Method for sequence
		...
sequence_resource	Method for resource
		...
sequence_row_entity	...
		...
sequence_rule	Rules for sequence
		...
sequence_rule_condition	...
		...
sequence_rule_method	...
		...
sequence_type_entity	...
		...
sequence_valid	...
		...
sequence_value	...
		...
sequence_variable	...
		...
session	Course sessions definitions	id	Unique identifier
		...
session_category	Categories of sessions
		...
session_rel_course	Relationship between sessions and courses (which courses are contained in which sessions)
		...
session_rel_course_rel_user	Subscription of users to a course in a session
		...
session_rel_user	Subscription of users in a session
		...
settings_current	Platform settings
		...
settings_options	Platform settings' possible option
		...
shared_survey	...
		...
shared_survey_question	...
		...
shared_survey_question_option	...
		...
skill	Skills
		...
skill_level	Possible levels for a skill
		...
skill_profile	Groups of skills levels (level profiles)
		...
skill_rel_gradebook	Linking betweek gradebooks (gradebook_category)
		...
skill_rel_profile	Linking of skill with skill_profile
		...
skill_rel_skill	Hierarchy of skills between themselves
		...
skill_rel_user	When a user gets a skill, this table registers it
		...
skill_rel_user_comment	Users having obtained skills can get feedback from other users. They are stored here.
		...
specific_field	...
		...
specific_field_values	...
		...
sys_announcement	Global announcements
		...
sys_calendar	Global agenda events (not repetition possible)
		...
system_template	Global document templates
		...
tag	Tags
		...
templates	...
		...
ticket_assigned_log	...
		...
ticket_category	...
		...
ticket_category_rel_user	...
		...
ticket_message	...
		...
ticket_message_attachments	...
		...
ticket_priority	...
		...
ticket_project	...
		...
ticket_status	...
		...
ticket_ticket	...
		...
track_course_ranking	Ranking based on score voted by users on courses
		...
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
		...
track_e_attempt	Log of all attempts (answers to questions) in quizzes (c_quiz). These are sub-elements of track_e_exercises.
		...
track_e_attempt_coeff	...
		...
track_e_attempt_recording	(experimental) Log of historical changes to answers. This is not working properly yet in 1.11.x
		...
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.	course_access_id	unique ID
		c_id
		user_id
		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
		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
		...
track_e_downloads	Log of all documents downloads (only logs documents that exist in the c_document table)
		...
track_e_exercises	Log of all quiz (c_quiz) attempts by a user. For the detail of each answer, check track_e_attempt
		...
track_e_hotpotatoes	Log of scores at quizzes of type HotPotatoes
		...
track_e_hotspot	Log of additional data regarding questions of HotSpot type (questions on images).	hotspot_id	unique id
		hotspot_user_id	User ID
		hotspot_course_code	Course code (not ID)
		c_id	Course ID
		hotspot_exe_id
		hotspot_question_id
		hotspot_answer_id
		hotspot_correct
		hotspot_coordinate
track_e_item_property	Log of changes in the c_item_property table	id	Unique ID
		course_id
		item_property_id	ID in the c_item_property table (FK)
		title
		content
		progress
		lastedit_date	datetime
		lastedit_user_id	User ID
		session_id
track_e_lastaccess	Log of accesses to tools in courses	access_id	unique ID
		access_user_id	User ID
		access_date	datetime (in UTC)
		c_id	Course ID
		access_tool	name of the tool
		access_session_id	Session ID
track_e_links	Log of accesses to links in the course's links tool (c_link)	links_id	unique id
		links_user_id	User Id
		links_date	datetime
		c_id
		links_link_id	iid from c_link (FK)
		links_session_id
track_e_login	Log all login and logout actions, with no differentiation of what the user did (see track_e_lastaccess for that)	login_id	unique ID
		login_user_id	User ID
		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	login_id	unique ID
		login_user_id	User ID (FK)
		login_date	datetime
		user_ip	IP address (v4 or v6)
		c_id
		session_id	Session ID
		access_url_id	ID from access_url
track_e_open	deprecated
		...
track_e_uploads	Log uploads	upload_id	unique ID
		upload_user_id	User ID (FK)
		upload_date	datetime
		upload_cours_id	deprecated
		c_id
		upload_work_id	iid from c_student_publication
		upload_session_id	Session ID (FK)
track_stored_values	deprecated
		...
track_stored_values_stack	deprecated
		...
user	Users of the system	id	Unique ID
		user_id	deprecaed
		username	The username used to login
		username_canonical	a cleaned version of the username
		email_canonical	a cleaned version of the email
		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)
		...
user_course_category	Courses categories defined by users in "sort my courses" feature
		...
user_friend_relation_type	Dictionary table for the types of social relation types
		...
user_rel_course_vote	Link between users and courses to register user's vote on the course	id	unique ID
		c_id
		user_id
		session_id
		url_id	Id of access_url (FK)
		vote	int value (usually 0-5)
user_rel_event_type	...
		...
user_rel_tag	link between user and tag
		...
user_rel_user	links between users
		...
usergroup	User groups (global groups) (see classes and social groups in the administration)
		...
usergroup_rel_course	Link between usergroup and course
		...
usergroup_rel_question	Link between usergroup and c_quiz_question
		...
usergroup_rel_session	Link between usergroup and session
		...
usergroup_rel_user	Link between usergroup and user
		...
usergroup_rel_usergroup	Link between usergroup and itself
		...