[DBTNG + XDB] revamp key and index naming
| Project: | Drupal |
| Version: | 7.x-dev |
| Component: | database system |
| Category: | feature request |
| Priority: | normal |
| Assigned: | hswong3i |
| Status: | won't fix |
According to existing PostgreSQL implementation, the following index key naming will result as:
- table: node_comment_statistics
+ name: node_comment_timestamp
=> node_comment_statistics_node_comment_timestamp_idx (50 characters) - table: menu_links
+ name: menu_plid_expand_child
=> menu_links_menu_plid_expand_child_idx (37 characters)
In case if we clone these handling directly for Oracle, it will violate the maximum 30 characters restriction and so not work. Moodle have document this problem discovered during XMLDB development and even detail their solution:
But these resulting names aren't going to be the final names used to create the DB structure by the XMLDB generators. There we'll follow one nomenclature widely used by a lot of DB applications, that provides us with:
1. One standard naming convention.
2. The ability to know what's wrong when some key/index is throwing any error.
3. The capability to have multiple Moodle servers running under the same DB.Also, note that all these rules will be applied automatically by the XMLDB generators, so the object will be created with their correct names and you won't need to use such names directly, but under some special circumstances.
Also, every RDBMS has its own limit about the maximum length of such names. They are:
* MySQL: 64 cc.
* PostgreSQL: 64 cc.
* Oracle: 30 cc.
* MSSQL: 128 cc.So, as the smallest number decides, we must be able to name everything in 30 cc. or less.
Should we reference to that of Moodle's XMLDB and so modify our DBTNG as more handy (auto naming based on fields provided), where also able to work around different databases with different requirement?
#1
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:
<?php
// $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"
#2
Add minor abstraction for more handy and standardize implementation. Dummy demo code snippet (same result as above):
<?php// Handle with makeIndexName().
$result = Database::getActiveConnection()->makeIndexName($table, $fields);
var_dump($result);
?>
#3
Well currently we define indexes manually in the schema definition anyway, don't we. If there's a length limit, wouldn't it make sense to just document it and tell people to keep their index lengths to under 30 characters?
#4
Yes agree that educate user is also a possible solution, but I come with some other concerns:
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...)
#5
If we automate index names, what would the affect be for schema hooks and alter operations? We need to think through all the implications of this before we put such functionality in core.
#6
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:
<?phppublic function addIndex(&$ret, $table, $name, $fields) {
public function dropIndex(&$ret, $table, $name) {
?>
To:
<?phppublic 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...
#7
Killing $ret is already on my todo list. You didn't answer the question, however, about what the knock-on effect of magic index naming is. How would a later update hook know how to reference the index to remove or alter it?
#8
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 :-)
<?phppublic function addIndex($table, $fields) {
public function dropIndex($table, $fields) {
?>
#9
Then we have to find the appropriate index, since any auto-generated name has the potential for duplicates and therefore we'd need a counter on the end a la the aliasing logic. So we are then not using a name at all, and instead need to find the index by the fields it indexes on.
I still think we're better off just documenting the length limit and telling module authors to not be dumb. I don't yet understand the problem with that approach.
#10
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:
#11
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.
#12
Since Oracle appears to be the only one to have this limitation, what about solving this issue in the Oracle driver itself? You can easily generate a unique short (<= 30 characters) index name, and store the
drupal identifier/short identifierrelationship somewhere.There is nothing to worry about performance-wise because those index manipulation functions are of seldom use.