XMLDB key and index naming

XML database schema > XMLDB key and index naming

---

Every object in the DB have its own name. It's easy to know about table names and field names, but other objects, like keys, indexes, sequences... are created with some names difficult to know/understand. This page will try to explain a bit how all those objects will be named by the XMLDB generators in a compressive way.

First of all, you should know that, under the XMLDB Schema files everything is named easily, mainly because there are some exact conventions to follow. Basically, every key and index is named with one string that is the result of concatenating all the field names in the object, separated by "-". So, if we have one index over the fields "fieldA" and "fieldB" the index, in the XMLDB Files will be named "fieldA-fieldB". Simple!

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.

The proposed format to be implemented in the XMLDB constructors (those functions able to convert the XMLDB format to real RDBMS sentences) follows this format:

[$CFG->prefix]tablename_abbreviated_columnames_abbreviated_object_type

Where:

• [$CFG->prefix] will be the prefix to add to all the objects, but to Oracle, see "Naming conventions II for more info (being conservative with our 30cc limit, this prefix shouldn't be longer than 4cc).
• tablename_abbreviated will be an abbreviated representation of the table name, automatically generated by spliting the table name is its words and getting the first 3 character of each word. For example, the table "glossary_entries_categories" will converted to the "gloentcat" abbreviation.
• columnames_abbreviated will be an abbreviated representation of the columns used by the object (index, key, sequence) generated by the concatenation of the first 3 chars of each field. For example, one index over the "name, level, context" fields be converted to the "namlevcon" abbreviation.
• 'object_type will declare de type of object we are creating. It will be one of these:
  • pk: For Primary Keys
  • uk: For Unique Keys
  • fk: For Foreign Keys
  • ix: For Indexes
  • uix: For Unique Indexes
  • seq: For Sequences
  • tri: For Triggers

The method responsible to calculate this names will be shared by all the different XMLDB generators, so all objects will be uniformly named. If any of the calculated names excess the infamous 30cc. the name will be reduced by removing characters from the "columnames_abbreviated" part until if fits completely.

Names generated automatically by RDBMS won't be specified at all. They are:

• sequences: for PostgreSQL, the SERIAL keyword generates one sequence with a different name schema than the specified above.
• primary: for MySQL, the PRIMARY KEY must be named (or is named by default) "primary".

Also, don't forget that you never will have to create/modify these names by hand. The XMLDB layer will generate them automatically and they are pretty different from the conventions used to name elements in the XMLDB Editor.