XMLDB editorea

Oharra: Itzuli gabekoak. Anima zaitezte eta ekin!.     (itzuli gabeko beste orri batzuk)

Moodle 1.7

Location: Administration > Miscellaneous > XMLDB editor

• The XMLDB editor is a tool for making the .xml files that specify how moodle should set up its database tables. Previously, developers had to make separate .sql install files for mysql and postgres, but now only database-neutral file is needed, which supports many more databases.

• It makes the editing of tables/fields/keys/indexes almost a trivial task, allowing developers to spend the time coding and improving things instead of fighting with XML files and the errors caused by manual editing (of course, developers are free to use such extra time as desired - beers, dance, books, music...) ;-)

• All new install.xml files present under each db directory in Moodle can be edited (and we recommend it) with just some clicks and keystrokes. Those install.xml will contain all the info needed to generate the specific objects needed for each RDBMS supported.

• Note: To be able to handle files properly, the web server needs write access to all db directories where the install.xml files reside (and to the files themselves, of course). If you cannot click either the load or create link, that means that you either have not created the /db directory, or that it is not writeable by the webserver.

Use

• The XMLDB editor is pretty easy to use so there's no need for a complete guide here. Playing with it for a while is highly recommended, viewing how it works and how it modifies the install.xml files.

• It's organised in a top-bottom structure, where you start loading' (or creating) a new XMLDB file. Then, you can edit the file and its general structure will be shown. This structure has two type of elements, tables and statements and the XMLDB editor allows you to add, edit, delete, and move them easily. Also, for initial creation of tables, one small but effective reverse-enginery tool has been developed (only under MySQL) allowing you to retrofit any table from the DB to the XMLDB Editor.

• Whilst editing tables you will see their fields, keys and indexes and you'll be able to handle all them easily. Note that some fields can be non-editable. This is because they are being used in some way (part of one key or index) and the idea is to warn you about that.

• Fields can be edited and you can specify their name, type, length, decimals, null-ability, defaults and so on. Exactly the same for both keys and indexes.

• Whilst editing statements, you must think about them like "collections of sentences". Once you select the type (only inserts are allowed for now) and table you are interested in, you'll be able to introduce the exact values easily, being able to duplicate them easily to gain some speed if you have a lot of sentences in your development. Sentences can be edited and deleted easily too.

• One interesting feature is that all the XMLDB editor pages allow you to enter a comment about the item being modified (table, index, key, field, statement). Use it as you wish, sure that it helps other developers to understand a bit more about the DB model.

Once you have created the install.xml file, you still need to manually create an upgrade.php file in your module's db folder in order to add the tables to your database. The upgrade.php file should start off looking something like this:

<?php

function xmldb_realtimequiz_upgrade($oldversion) {
    global $CFG;

    $result = true;

// Insert PHP code from XMLDB Editor here

    return $result;
}
?>

To get the code for the '// Insert PHP code here' bit, open the XMLDB Editor and load the relevant install.xml file. Choose the 'View PHP' option and then copy and paste the generated code.

Conventions

Apart from the Database Structures guidelines, some more conventions should be followed:

1. About names:
   1. All lowercase names (tables, indexes, keys and fields).
   2. Table names and field names must use only a-z, 0-9 and _ chars.
   3. Key and index names under the XMLDB Files must be formed by concatenating the name of the fields present in the key/index with the '"-" (minus) character.
   4. Primary key always must be named "primary" (this is one exception to the previous convention).
   5. It's highly recommended to avoid reserved words completely. We know we have some of them now but they should be completely out for next releases.
2. About NULLS
   1. Avoid to create all the fields as NOT NULL with the silly default value '' (empty string). The underlying code used to create tables will handle it properly but the XMLDB structure must be REAL. Read more in the [[XMLDB Problems#NOT NULL fields using a DEFAULT '' clause|Problems Page]].
3. About FOREIGN KEYS
   1. Under the tables of every XMLDB file, you must define the existing Foreign Keys (FK) properly. This will allow everybody to know a bit better the structure, allow to evolve to a better constrained system in the future and will provide the underlying code with the needed info to create the proper indexes.
   2. Note that, if you define any field combination as FK you won't have to create any index on that fields, the code will do it automatically!
   3. This convention is only applicable for relations INSIDE one file. Don't generate FK constraints against other files (courseid, userid), use indexes there.
   4. Respect Convention 1.3
4. About UNIQUE KEYS
   1. Declare any fields as UNIQUE KEY (UK) only if they are going to be used as target for one FK. Create unique indexes instead.
   2. Respect Convention 1.3

See also

• Development:XMLDB defining an XML structure
• Using Moodle Using XMLDB editor forum discussion