Note:

If you want to create a new page for developers, you should create it on the Moodle Developer Resource site.

Data definition API: Difference between revisions

From MoodleDocs
m (Text replacement - "<code php>" to "<syntaxhighlight lang="php">")
(19 intermediate revisions by 6 users not shown)
Line 9: Line 9:
==Main info==
==Main info==


'''Important note:''' All the functions showed in this page are for use in '''Moodle 2.0 upwards''', where we changed the [[DB layer 2.0|DB layer]] to support some new features. If you need information for previous Moodle version, take a look to the [[DDL functions - pre 2.0|DDL functions - pre 2.0]] page.
'''Important note:''' All the functions showed in this page are for use in '''Moodle 2.0 upwards''', where we changed the [[DB layer 2.0|DB layer]] to support some new features. If you need information for previous Moodle version, take a look to the [[DDL functions - pre 2.0|DDL functions - pre 2.0]] page. For a detailed reference of changes, see the [[DB layer 2.0 migration docs|migration docs]].


* All the function calls in this page are public methods of the '''database manager''', accessible from the $DB global object. So you'll need to "import" it within your '''upgrade.php''' main function with something like:
* All the function calls in this page are public methods of the '''database manager''', accessible from the $DB global object. So you'll need to "import" it within your '''upgrade.php''' main function with something like:


  function xmldb_xxxx_upgrade {
<syntaxhighlight lang="php">
function xmldb_xxxx_upgrade {
    
    
     global $DB;
     global $DB;
Line 20: Line 21:
    
    
     /// Your upgrade code goes here
     /// Your upgrade code goes here
  }
}
 
</syntaxhighlight>


* Once more, the use of these functions is '''restricted''' to the upgrade processes and it shouldn't be used ever out from there.
* Once more, the use of these functions is '''restricted''' to the upgrade processes and it shouldn't be used ever out from there.
* All the $table, $field, $index parameters are, always, XMLDB objects. Don't forget to read carefully the complete documentation about [[XMLDB creating new DDL functions|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.
* All the $table, $field, $index parameters are, always, XMLDB objects. Don't forget to read carefully the complete documentation about [[XMLDB creating new DDL functions|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 responsible for Moodle upgrading since the 1.7 release.  
* If you want real examples of the usage of these functions it's 100% interesting to examine the '''upgrade.php''' scripts that are responsible for Moodle upgrading since the 1.7 release.  
* Also, it's a '''very good idea''' (highly recommended!) to use the [[XMLDB editor|XMLDB Editor]] itself to generate automatically the desired PHP code. Just play with it!
* Also, it's a '''very good idea''' (highly recommended!) to use the [[XMLDB editor|XMLDB Editor]] itself to generate automatically the desired PHP code. Just play with it!


Line 30: Line 33:


===Handling tables===
===Handling tables===
    * To detect if one table exists:
<syntaxhighlight lang="php">
        table_exists($table)
/// To detect if one table exists:
    * To create one table:
    $dbman->table_exists($table)
        create_table($table, $continue=true, $feedback=true)
 
    * To drop one table:
/// To create one table:
        drop_table($table, $continue=true, $feedback=true)
    $dbman->create_table($table, $continue=true, $feedback=true)
    * To rename one table:
 
        rename_table($table, $newname, $continue=true, $feedback=true)
/// To drop one table:
    $dbman->drop_table($table, $continue=true, $feedback=true)
 
/// To rename one table:
    $dbman->rename_table($table, $newname, $continue=true, $feedback=true)
</syntaxhighlight>


===Handling fields===
===Handling fields===
    * To detect if one field exists:
<syntaxhighlight lang="php">
        field_exists($table, $field)
/// To detect if one field exists:
    * To create one field:
    $dbman->field_exists($table, $field)
        add_field($table, $field, $continue=true, $feedback=true)
 
    * To drop one field:
/// To create one field:
        drop_field($table, $field, $continue=true, $feedback=true)
    $dbman->add_field($table, $field, $continue=true, $feedback=true)
    * To change the type of one field:
 
        change_field_type($table, $field, $continue=true, $feedback=true)
/// To drop one field:
    * To change the precision of one field:
    $dbman->drop_field($table, $field, $continue=true, $feedback=true)
        change_field_precision($table, $field, $continue=true, $feedback=true)
 
    * To change the signed/unsigned status of one field:
/// To change the type of one field:
        change_field_unsigned($table, $field, $continue=true, $feedback=true)
    $dbman->change_field_type($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 precision of one field:
    * To change the enum (check constraint) of one field:
    $dbman->change_field_precision($table, $field, $continue=true, $feedback=true)
        change_field_enum($table, $field, $continue=true, $feedback=true)
 
    * To change the default value of one field:
/// To change the signed/unsigned status of one field:
        change_field_default($table, $field, $continue=true, $feedback=true)
    $dbman->change_field_unsigned($table, $field, $continue=true, $feedback=true)
    * To rename one field:
 
        rename_field($table, $field, $newname, $continue=true, $feedback=true)
/// To make one field nullable or not:
    $dbman->change_field_notnull($table, $field, $continue=true, $feedback=true)
 
/// To drop one enum (check constraint) from one field:
/// (note this function will be only available in Moodle 2.0 as it's needed
/// to drop any enum existing in previous Moodle releases). Will be out in Moodle 2.1
    $dbman->drop_enum_from_field($table, $field, $continue=true, $feedback=true)
 
/// To change the default value of one field:
    $dbman->change_field_default($table, $field, $continue=true, $feedback=true)
 
/// To rename one field:
    $dbman->rename_field($table, $field, $newname, $continue=true, $feedback=true)
</syntaxhighlight>


===Handling indexes===
===Handling indexes===
    * To detect if one index exists:
<syntaxhighlight lang="php">
        index_exists($table, $index)
/// To detect if one index exists:
    * To return the name of one index in DB:
    $dbman->index_exists($table, $index)
        find_index_name($table, $index)
 
    * To add one index:
/// To return the name of one index in DB:
        add_index($table, $index, $continue=true, $feedback=true)
    $dbman->find_index_name($table, $index)
    * To drop one index:
 
        drop_index($table, $index, $continue=true, $feedback=true)
/// To add one index:
    $dbman->add_index($table, $index, $continue=true, $feedback=true)
 
/// To drop one index:
    $dbman->drop_index($table, $index, $continue=true, $feedback=true)
</syntaxhighlight>


==Some considerations==
==Some considerations==
Line 75: Line 101:
# All the $newtablename, $newfieldname parameters are, always, simple strings.
# All the $newtablename, $newfieldname parameters are, always, simple strings.
# All the ***_exists() functions return boolean true/false.
# All the ***_exists() functions return boolean true/false.
# Any problem in the execution of the functions will throw one Exception and the upgrade process will die.
# It's recommendable to use the [[XMLDB editor|XMLDB Editor]] to generate the PHP code automatically (did I say this before? :-P )
# It's recommendable to use the [[XMLDB editor|XMLDB Editor]] to generate the PHP code automatically (did I say this before? :-P )


==See also==
==See also==


* [[Core APIs]]
* [[XMLDB Documentation|XMLDB Documentation]]: Main page of the whole XMLDB documentation, where all the process is defined and all the related information resides.
* [[XMLDB Documentation|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.
* [[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.
Line 85: Line 113:
* [[DDL functions - pre 2.0|DDL functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DDL functions - pre 2.0|DDL functions - pre 2.0]]: '''(deprecated!)''' For information valid before Moodle 2.0.
* [[DB layer 2.0 migration docs|DB layer 2.0 migration docs]]: Information about how to modify your code to work with the new Moodle 2.0 DB layer.
* [[DB layer 2.0 migration docs|DB layer 2.0 migration docs]]: Information about how to modify your code to work with the new Moodle 2.0 DB layer.
* [[DTL functions|DTL functions]]: Exporting, importing and moving of data stored in sql databases


[[Category:DB]]
[[Category:DB]]
[[Category:XMLDB]]
[[Category:XMLDB]]
[[Category:API]]
[[ja:データ定義API]]

Revision as of 13:30, 14 July 2021

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

Important note: All the functions showed in this page are for use in Moodle 2.0 upwards, where we changed the DB layer to support some new features. If you need information for previous Moodle version, take a look to the DDL functions - pre 2.0 page. For a detailed reference of changes, see the migration docs.

  • All the function calls in this page are public methods of the database manager, accessible from the $DB global object. So you'll need to "import" it within your upgrade.php main function with something like:
function xmldb_xxxx_upgrade {
  
    global $DB;
  
    $dbman = $DB->get_manager(); // loads ddl manager and xmldb classes
  
    /// Your upgrade code goes here
}
  • Once more, the use of these functions is restricted to the upgrade processes and it shouldn't be used ever out from there.
  • All the $table, $field, $index parameters are, always, XMLDB objects. 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 100% interesting to examine the upgrade.php scripts that are responsible for Moodle upgrading since the 1.7 release.
  • Also, it's a very good idea (highly recommended!) 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:
    $dbman->table_exists($table)

/// To create one table:
    $dbman->create_table($table, $continue=true, $feedback=true)

/// To drop one table:
    $dbman->drop_table($table, $continue=true, $feedback=true)

/// To rename one table:
    $dbman->rename_table($table, $newname, $continue=true, $feedback=true)

Handling fields

/// To detect if one field exists:
    $dbman->field_exists($table, $field)

/// To create one field:
    $dbman->add_field($table, $field, $continue=true, $feedback=true)

/// To drop one field:
    $dbman->drop_field($table, $field, $continue=true, $feedback=true)

/// To change the type of one field:
    $dbman->change_field_type($table, $field, $continue=true, $feedback=true)

/// To change the precision of one field:
    $dbman->change_field_precision($table, $field, $continue=true, $feedback=true)

/// To change the signed/unsigned status of one field:
    $dbman->change_field_unsigned($table, $field, $continue=true, $feedback=true)

/// To make one field nullable or not:
    $dbman->change_field_notnull($table, $field, $continue=true, $feedback=true)

/// To drop one enum (check constraint) from one field:
/// (note this function will be only available in Moodle 2.0 as it's needed
/// to drop any enum existing in previous Moodle releases). Will be out in Moodle 2.1
    $dbman->drop_enum_from_field($table, $field, $continue=true, $feedback=true)

/// To change the default value of one field:
    $dbman->change_field_default($table, $field, $continue=true, $feedback=true)

/// To rename one field:
    $dbman->rename_field($table, $field, $newname, $continue=true, $feedback=true)

Handling indexes

/// To detect if one index exists:
    $dbman->index_exists($table, $index)

/// To return the name of one index in DB:
    $dbman->find_index_name($table, $index)

/// To add one index:
    $dbman->add_index($table, $index, $continue=true, $feedback=true)

/// To drop one index:
    $dbman->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. Any problem in the execution of the functions will throw one Exception and the upgrade process will die.
  5. It's recommendable to use the XMLDB Editor to generate the PHP code automatically (did I say this before? :-P )

See also