XMLDBのキーおよびインデックスの命名 (Dev docs)

XMLDB ドキュメンテーション > XMLDBのキーおよびインデックスの命名

---

DB 内のオブジェクトにはそれぞれ名前がついています。テーブル名やフィールド名はわかりやすいですが、キー、インデックス、シーケンスなど、わかりにくい名前で作成されているものもあります。このページでは、これらのオブジェクトが XMLDB ジェネレータによって圧縮された方法で命名される方法を少し説明しようと思います。

まず知っておいていただきたいのは、XMLDB スキーマファイル のもとでは、主にいくつかの厳密な規則 に従って、すべてが簡単に命名されていることです。基本的に、すべてのキーとインデックスには、オブジェクト内のすべてのフィールド名を "-" で区切って連結した1つの文字列の名前が付けられます。つまり、フィールド "fieldA" と "fieldB" に対して1つのインデックスがある場合、XMLDB ファイル内のインデックスは "fieldA-fieldB" という名前になるのです。簡単ですね！

しかし、これらの結果の名前は、XMLDB ジェネレータが DB 構造を作成するために使用される最終的な名前にはなりません。そこで、多くの DB アプリケーションで広く使われている、ある命名法に従います:

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 4 character of each word. For example, the table "glossary_entries_categories" will converted to the "glosentrcate" 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
  • ck: For Check Constraints
  • ix: For Indexes
  • uix: For Unique Indexes
  • seq: For Sequences
  • trg: 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.