Data definition API

Moodle 2.0

In this page you'll access to the available functions under Moodle to be able to handle DB structures (tables, fields, indexes...).

The objective is to have a well-defined group of functions able to handle all the DB structure (DDL statements) using one neutral description, being able to execute the correct SQL statements required by each RDBMS. All these functions are used exclusively by the installation and upgrade processes.

In this page you'll see a complete list of such functions, with some explanations, tricks and examples of their use. If you are interested, it's also highly recommendable to take a look to the DML functions page where everything about how to handle DB data (select, insert, update, delete i.e. DML statements) is defined.

Of course, feel free to clarify, complete and add more info to all this documentation. It will be welcome, absolutely!

Main info

• All the functions in this page are present in the "lib/ddllib.php" file under Moodle root directory.
• This directory is included automatically both by the installation and the upgrade processes and it shouldn't be used ever out from those parts of Moodle.
• Don't forget to read carefully the complete documentation about creating new DDL functions before playing with these functions. Everything is explained there, with one general example and some really useful tricks to improve the use of all the functions detailed below.
• If you want real examples of the usage of these functions it's highly recommended to examine the upgrade.php scripts that are responsibles for Moodle upgrading since the 1.7 release. Also, it's a very good idea to use the XMLDB Editor itself to generate automatically the desired PHP code. Just play with it!

The functions

Handling tables

   * To detect if one table exists:
       table_exists($table)
   * To create one table:
       create_table($table, $continue=true, $feedback=true)
   * To drop one table:
       drop_table($table, $continue=true, $feedback=true)
   * To rename one table:
        rename_table($table, $newname, $continue=true, $feedback=true)

Handling fields

   * To detect if one field exists:
       field_exists($table, $field)
   * To create one field:
       add_field($table, $field, $continue=true, $feedback=true)
   * To drop one field:
       drop_field($table, $field, $continue=true, $feedback=true)
   * To change the type of one field:
       change_field_type($table, $field, $continue=true, $feedback=true)
   * To change the precision of one field:
        change_field_precision($table, $field, $continue=true, $feedback=true)
   * To change the signed/unsigned status of one field:
       change_field_unsigned($table, $field, $continue=true, $feedback=true)
   * To make one field nullable or not:
       change_field_notnull($table, $field, $continue=true, $feedback=true)
   * To change the enum (check constraint) of one field:
       change_field_enum($table, $field, $continue=true, $feedback=true)
   * To change the default value of one field:
       change_field_default($table, $field, $continue=true, $feedback=true)
   * To rename one field:
       rename_field($table, $field, $newname, $continue=true, $feedback=true)

Handling indexes

   * To detect if one index exists:
       index_exists($table, $index)
   * To return the name of one index in DB:
       find_index_name($table, $index)
   * To add one index:
       add_index($table, $index, $continue=true, $feedback=true)
   * To drop one index:
       drop_index($table, $index, $continue=true, $feedback=true)

Some considerations

1. All the $table, $field, $index parameters are, always, XMLDB objects.
2. All the $newtablename, $newfieldname parameters are, always, simple strings.
3. All the ***_exists() functions return boolean true/false.
4. It's recommendable to use the XMLDB Editor to generate the PHP code automatically (did I say this before? :-P )

See also

• XMLDB Documentation: Main page of the whole XMLDB documentation, where all the process is defined and all the related information resides.
• XMLDB Defining one XML structure: Where you will know a bit more about the underlying XML structure used to define the DB objects, that is used continuously by the functions described in this page.
• Installing and upgrading plugin DB tables
• DML functions: Where all the functions used to handle DB data (DML) are defined.
• DDL functions - pre 2.0: (deprecated!) For information valid before Moodle 2.0.