Project

General

Profile

Feature #7257

Updated by Yannick Warnier over 4 years ago

The item_property table is a mess, in particular because it actually serves (in a bad way) two purposes: it stores visibility information about resources and it track the changes on every item.
This makes distinguishing both and generating reports a mess (comment extracted from #5157)

The current table looks like this:
mysql> describe c_item_property;
|------------------|------------------|------|-----|---------------------|----------------|
| Field | Type | Null | Key | Default | Extra |
|------------------|------------------|------|-----|---------------------|----------------|
| c_id | int(11) | NO | PRI | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| tool | varchar(100) | NO | MUL | | a literal identifying the tool (and as such the table)|
| insert_user_id | int(10) unsigned | NO | | 0 | |
| insert_date | datetime | NO | | 0000-00-00 00:00:00 | |
| lastedit_date | datetime | NO | | 0000-00-00 00:00:00 | |
| ref | int(11) | NO | | 0 | the id of the item inside the referred tool's table |
| lastedit_type | varchar(100) | NO | | | |
| lastedit_user_id | int(10) unsigned | NO | | 0 | |
| to_group_id | int(10) unsigned | YES | | NULL | |
| to_user_id | int(10) unsigned | YES | | NULL | |
| visibility | tinyint(4) | NO | | 1 | |
| start_visible | datetime | NO | | 0000-00-00 00:00:00 | |
| end_visible | datetime | NO | | 0000-00-00 00:00:00 | |
| id_session | int(11) | NO | | 0 | |
|------------------|------------------|------|-----|---------------------|----------------|

The idea (for v10) is to have two tables:
* one with only information aobut the item and its attributes (c_item, maybe?)
* one with only historical information about the changes on this item (c_item_changelog?)

h2. c_item

A proposed definition (this must be tested) should be like follows:

mysql> describe item;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| tool_id | int(11) | NO | | NULL | an ID identifying the tool (and as such the table)|
| ref_id | int(11) | NO | | 0 | the iid of the item inside the referred tool's table |
| c_id | int(11) | NO | | NULL | |
| session_id | int(11) | NO | | 0 | |
| insert_user_id | int(10) unsigned | NO | | 0 | |
| insert_date | datetime | NO | | 0000-00-00 00:00:00 | |
| lastedit_date | datetime | NO | | 0000-00-00 00:00:00 | |
| lastedit_user_id | int(10) unsigned | NO | | 0 | |
| lastedit_type | varchar(100) | NO | | | |
| to_group_id | int(10) unsigned | YES | | NULL | |
| to_user_id | int(10) unsigned | YES | | NULL | |
| visibility | tinyint(4) | NO | | 1 | |
| start_visible | datetime | NO | | 0000-00-00 00:00:00 | |
| end_visible | datetime | NO | | 0000-00-00 00:00:00 | |
|------------------|------------------|------|-----|---------------------|----------------|

So the fields here are *practically* untouched (apart from the order and names of the fields):
* c_id is not mandatory anymore, which allows us to later on associate the item to other courses (this is a first step for a future enhancement)
* tool_id (an integer) replaces the "tool" name literal (allowing for faster indexing) *but* we will need a general "tool" or "tool_definition" table to give an ID to each tool (including tools created by a plugin)
* ref_id replaces the previous "ref" and points directly to an item into the table
* session_id instead of id_session (we want to get rid of all "id_session" and replace them by session_id all over chamilo
* the rest is straightforward

h2. item_changelog

This table stores the changes made to any item by anyone. It is assumed this table will be very large, but with this change in structure, it should not be read very often.

Given the unique ID in the "item" table, it would not seem necessary to store the course and session IDs. However, there might be cases in the future where we move an item from one course to another. Because of this, we have to consider an origin and destination course and session.
Reflecting on one change we had to do for one specific Chamilo implementation (branch_transaction), it would be a good idea to be able to reuse this table to replicate most changes to items. As such, we need to keep good track of what is done and define types of changes to have kind of a dictionary for that.

As a rule of thumb, a ref_id (and the tool_id) should never change by the effects of a "change" on an item. If the item changes tool, then it must be deleted and re-created. As such, we don't need to keep track of tool_id and ref_id here.

mysql> describe item_changelog;
| Field | Type | Null | Key | Default | Extra |
| id | bigint | NO | PRI | NULL | auto_increment |
| item_id | int(11) | | | | |
| action_id | int(11) | | | | |
| c_id | int(11) | NO | | NULL | |
| session_id | int(11) | NO | | 0 | |
| action_date | datetime | NO | | 0000-00-00 00:00:00 | |
| action_user_id | int(10) unsigned | NO | | 0 | |
| details | text | NO | | 0000-00-00 00:00:00 | |
|------------------|------------------|------|-----|---------------------|----------------|

Obviously this table should be indexed properly and all queries should preferrably follow the index order to speed it up.

(unfinished)

Back