[DBTNG + XDB] revamp key and index naming

Patch attached demonstrate the above idea as documented in Moodle. It is just a draft and not yet alter most existing schema API implementation.

With following dummy code we can check its result:

// $db_prefix = 'drupal_';
$table = 'menu_links';
$fields = array('menu_name', 'plid', 'expanded', 'has_children');
$name = 'menu_plid_expand_child';

// Clone from PostgreSQL _createIndexSql() handling.
$result = Database::getActiveConnection()->prefixTables('{' . $table . '}_' . $name . '_idx');
var_dump($result);

// Handle with makeConstraintName().
$result = Database::getActiveConnection()->makeConstraintName($table, $fields, 'ix');
var_dump($result);

The result:

string(44) "drupal_menu_links_menu_plid_expand_child_idx"
string(30) "drupal_menulink_menpliexpha_ix" 

Add minor abstraction for more handy and standardize implementation. Dummy demo code snippet (same result as above):

// Handle with makeIndexName().
$result = Database::getActiveConnection()->makeIndexName($table, $fields);
var_dump($result);

Yes agree that educate user is also a possible solution, but I come with some other concerns:

1. Not much people are cross database expert. According to the great different between MySQL and PostgreSQL/Oracle/etc user base, soon we will forget those "guideline" and write code as not cross database compatible. Similar case as PostgreSQL support status before SchemaAPI.
2. It is a good idea to automate some repeat work into predefined handy functions.
3. Standardize handling can also reduce maintenance difficulty between different database backend.

IMHO, documentation is a must, but this issues should also able to simplify our life :-)

P.S. A minor benefit: if the index/unique/pk name will be generated automatically, we will never headache for their naming, too (I am an insane person and always spend too long time for finding a good name...)

I think the change of our API will be a bit similar as Moodle's DDL API. We can remove our $name argument, and on only focus on what we really needed for, e.g. $table, $field, $index. The following function API should change from:

  public function addIndex(&$ret, $table, $name, $fields) {
  public function dropIndex(&$ret, $table, $name) {

To:

  public function addIndex($table, $fields) {
  public function dropIndex($table, $fields) {

BTW, we are NOT Moodle: their $table, $field, $index parameters are always their XMLDB objects (what's this? seems to be a structured object/array) I didn't give too indeed study about how they are functioning, so can't comment much for this. I am now reading their code and implementation...

We just need to provide the $fields once again (maybe it is just a simple array, maybe a structural object as that of Moodle XMLDB), and the magic should done behind for us :-)

  public function addIndex($table, $fields) {
  public function dropIndex($table, $fields) {

A new patch with Partly MySQL revamp. During install MySQL will utilize the new abstract functions so primary/index/unique key will coming with new naming format, e.g. For table menu_links:

• menu_links_path_menu_ix => menulink_linmen_ix
• menu_links_menu_plid_expand_child_ix (> 30) => menulink_exphasmenpli_ix (< 30)
• menu_links_menu_parents_ix => menulink_menp1p2p3p4p5p6p7p_ix (= 30)
• menu_links_router_path_ix => menulink_rou_ix

...any auto-generated name has the potential for duplicates...

Most likely this shouldn't happened with this new patch, because column names will be sorted before combine as new label. Unless we: 1. define keys with identical fields, and 2. define keys with identical type, the name should be unique among the same table.

With the help of this patch, developer will not need to check document and understand the limitation of each database, they don't need to think for a good name, and all add/drop functions are now coming with identical function prototype so simplify the usage. If something we can automate for non-database expert and simplify their life, I would like to do so :D

Update: forget to patch abstract functions prototype... fix it.