Project

General

Profile

Feature #7257

Updated by Yannick Warnier about 5 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 (item, (c_item, maybe?)
* one with only historical information about the changes on this item (item_changelog?) (c_item_changelog?)

h2. item 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 |
| branch_id | int(11) | NO | | NULL | |
| 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) | | | | |
| branch_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 | |
| action_access_url_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.

h2. Reasons for branch_id and access_url_id

If we want to make these tables thoroughly resistant to future changes, they should also have a branch_id (ID of the branch of the organization on which it is installed), which will come from the branch table on multiple-branch organizations' Chamilo installations.

The access_url_id field is, however, already defined through the session_id, by restraint, although it would to have it in the item_changelog table (although not sql-normal) to reduce the weight of querying that table on one specific portal.

Placing these fields in these tables will actually remove a lot of weight from other tables, as otherwise the fields might need to be added to many other item tables.

h2. Ideas for a migration script

The migration script should look like this (I guess):
<pre>
CREATE TABLE item_changelog (
...
);
ALTER TABLE c_item_property RENAME item (
...
);
</pre>
Then for each item in the item table, it should create a first record in item_changelog.

h2. Notes

There is currently a track_e_item_property table intended for the same purpose of item_changelog, so track_e_item_property should be changed to look like item_changelog, probably. This hasn't been looked into just yet.

Back