Auto detection of compound indexes

[jpahullo]

Hi all!

The final solution for the compound indexes is to auto detect ALL compound indexes on every single Moodle instance, and considering also customizes indexes, even additional to those specified from Moodle.

The first step is to get all compound indexes. I get this SQL to get ALL compound indexes (i.e., indexes with more than a column).

To do so, we have $DB->get_tables(false); and then $DB->get_indexes($table_name); to get all indexes from the current Moodle instance, independent from the database engine :smiley:

Once I have all compound indexes, they will be filtered to consider only those with user-related column names.

The idea is to have a manual update (like an additional option to the administration settings), so that the compound indexes list is updated only on demand.

Any suggestions are welcome!

Jordi

[jpahullo]

This is a preliminary result of just loading compound indexes from current Moodle database.

WIP.

[ndunand]

That looks great, and would indeed solve all index related problems.

[jpahullo]

Yes! :stuck_out_tongue_closed_eyes:

[jpahullo]

Hi,

I'm working on a way that current file settings (in config/*) related to indexes, user-related column names and, finally, related to compound indexes, be specified in the plugin settings to make it easier for admins to choose and update them.

What do you think about it?

The steps I'm thinking about are as follows:

1. An upgrade script (db/upgrade.php) will set up initial settings into database (via set_config() for instance), acccordingly.
2. Going to plugin settings will show initial settings already set up.
3. By adding or removing selected table names, we are adding or removing settings below when saving settings.
4. The description under every table config text will include the list of table column names to make it easier to set up for admins.
5. Once all user-related columns for all chosen tables are set up, you can save the changes to make it work.

After this plugin upgrade, no more compound indexes, user-related column names and table names will be used from config/* files. The plugin will be using the plugin settings instead: easier and clearer to customize.

Feedback is welcome!

Jordi

[ndunand]

That looks very promising! I like this idea. But I don't see how compound indexes would be taken care of. Would they – as you mentioned earlier – be computed from each plugin's XMLDB files?

[ndunand]

Sorry, just reread your first post 😕 . In understand now.

This all sounds very clear, and would benefit every admin using the plugin.

[jpahullo]

Yes... I think so :smile:

[jpahullo]

Hi all,

I need help. I was working on with this enhancement again after so many days.

Remember that the goal of this issue is to make it easier to define new indexes via the plugins settings page.

For my first trial I thought on considering only unique indexes, but I found that in our current settings, we needed to set up these kinds of compound indexes:

1. Unique database indexes (the vast majority).
2. Non unique database indexes (like in role_assignments).
3. Tables with a relation of columns to consider as index (like in groups_members or journal_entries tables).

I detected that there are more non unique indexes that this plugin should consider, in my honest opinion, since semantically, it makes no sense having the same value for the given key after having merged two users (like in role_assignments). I think this is more about the design of the database scheme than our job. However, we have to consider how to go on.

There are several options to address this variety of current type of compound indexes in our settings:

1. Ask to build unique indexes for the above cases 2 and 3. Too radical and impractical IMHO.
2. Ask to build non unique indexes for the above case 3 and consider only indexes in our new settings (either unique or non unique).
3. Let the above options be as is and build the plugin settings to enable all this kind of possibilities.

The 2nd solution simplifies so much the plugin settings by only considering existing indexes. But we will force creating new indexes into every Moodle once installing/updating to this new version. Remember that tables from Moodle core appear in the case of manually added indexes (like for groups_members). I think this is somehow, by now, that we cannot afford.

The 3rd solution will be more complex for all the people, we as developers and all Moodle administrators, since the plugin settings page will grow so much by enabling all these new configuration options. Even though is the more complex, I think it is the most affordable solution.

Anyway, we should consider also all non unique database indexes in the settings page. But, since they are non unique, I think we should let the administrator to decide whether a given non unique index is included in the process of merging two users.

My position about the solution is in this order: (1st) solution 3, (2nd) solution 2, (3rd) solution 1.

What do you think? I will thank you so much to let me know about your opinions and suggestions.

[jpahullo]

An example of the current settings page under development.

I only have this information shown. The second stage is to use these settings when processing users mergings.

[jpahullo]

The help content:

The whole text is as follows:

This section is very important and you, admin, have to be cautious with it. Below you have the possibility to set up all column names related to the user.id column throughout your database scheme. You have two ways of setting up column names related to user.id. The first one is to just set up a list of common, generic column names that, if appear, they will be related to user.id always, regardless the database table the column name is found in. In the second way, you have the ability to set up specific column names for selected tables.

Having this configured out, this plugin will check througout the whole Moodle database scheme and merge two users considering only:

• These selected tables with only their specified column names.
• The rest of the tables with the generic column names.

Therefore, it is very important that these settings reflect and include all existing column name related to the user.id column. Finally, even though you can set up all the column names in the default list, this plugin works this way for efficiency and clarity.

[jpahullo]

When all settings related to compound indexes were moved into the plugin settings page, there are only three settings that will remain in the config.php file:

1. gathering
2. exceptions
3. tablemergers

I think we could move all settings to that page at the end. Let's see:

1. 'exceptions' has already a setting where the administrator can disable those exceptions.
2. 'gathering' can be a single input type=text for typing the gathering class in use.
3. 'tablemergers' is not an administrator setting actually. It contains the list of tablemerger implementations to load when instantiating the merger. We could make it dynamic without an actual setting. Like using some PHP function (e.g., get all classes given a certain namespace?, or list all classes from a given file path?)

We will not have any config.php or config.local.php since all settings will appear in the settings page.

[ndunand]

Hi Jordi,

I haven't forgotten about you, but haven't had time to look into this yet... sorry. I'll make sure to get back to you next week.

[jpahullo]

This is the candidate content for the settings plugin page.

The most flexible way to define all current indexes in the settings page is:

1. Include automatically all unique compound indexes.
2. Allow admins to select/activate non unique compound indexes.
3. Allow admins to define ad-hoc compound indexes (necessary for the group_members current index).

A screenshot follows:

[jpahullo]

Features and developments:

• [x] Define new settings page to gather legacy php configuration (config.php and config.local.php). No more need for programming php to set up this plugin.
• [x] Deduplicate string keys for messages in settings page.
• [x] Translate settings strings for en.
• [x] Translate settings strings for ca.
• [x] Translate settings strings for es.
• [x] Translate settings strings for fr.
• [ ] Make the plugin work as before.
• [ ] Check settings become equivalent and fully funtional both after an upgrade and from a fresh installation.

Detected bugs:

• [ ] Update checking list when modifying ad-hoc indexes.
• [ ] Update checking list when modifying non unique indexes.
• [ ] Deal with multiple non-unique indexes for a table, all of them having user-related columns. This plugin only allows dealing with an index per table, not multiple indexes per table.

[jpahullo]

@ndunand, would you mind I could start a pull request (not to be accepted by now), just for having French translations checked by you? I don't know French, so that I will use translators for that to the long list of new strings. Would you?

Therefore, you'd have time to check French translations (only that), and I'd have time to conclude the rest of stuff :yum:

Thanks in advance anyway :smiley:

[jpahullo]

Otherwise, I could attach the French translation file here just for you, too :stuck_out_tongue_winking_eye:

[ndunand]

Sure, I can check the FR translation, no problem. Thanks!

[jpahullo]

Sorry @ndunand abouth the bunch of commits, I had troubles when pushing my WIP.

Please, do not accept it, it is just work-in-progress. But this is enough for you to check French translation :stuck_out_tongue_winking_eye: Thank you very much in advance.

Hugs!