MoodleDocs - User contributions [en]

Development:Installing and upgrading plugin database tables

2010-06-24T14:07:59Z

Mil091:

==Introduction==

If you have done the right thing, Moodle will automatically create the database tables for your plugin when you visit the Admin notifications page (.../admin/index.php). This process is controlled by three files within your plugin:
;version.php : This records the version of the plugin code
;db/install.xml : This is used when someone installs your plugin for the first time.
;db/upgrade.php : This is used when someone who had an older version of your plugin installed upgrades to the latest version.

In addition, Moodle also stores in the database the currently installed version of each plugin.

In Moodle 1.9 and before, this is stored in the mdl_config table, in a row with the name ''plugintype''_''pluginname''_version. For example qtype_myqtype_version. The exception to this rule are modules and blocks. Installed module version numbers are stored in the mdl_modules table. Block version numbers are in mdl_block.

In Moodle 2.0 an beyond, plugin version numbers are stored in the mdl_config_plugins table, with ''plugintype''_''pluginname'' in the plugin column, and 'version' in the name column - with the same exception for modules and blocks.

==A specific example==

For the rest of this document, I will use a particular example, because it should make the explanation easier. You should be able to see how to generalise it.

We will suppose you that you are making a new question type myqtype. This is plugin type qtype, and the code will be in the question/type/myqtype folder. The currently installed version number will be stored in the qtype_myqtype_version row of the mdl_config table.

In addition, we will just consider the first two releases of the plugin. The first release will have version number 2008080100, and will just use one database table mdl_question_myqtype, with two columns col1 and col2. Then we will suppose that the second release is 2008080200, and that requires an extra column, newcol, to be added to the mdl_question_myqtype table.

==The files you need for the first release==

In what follows, the bits of code you need to replace are '''in bold'''.

;version.php
$plugin->version = 2008080100;
$plugin->requires = '''XXXXXXXXXX; // Copy the current value from the top-level version.php file.'''
;db/install.xml
:This file, which you should [[XMLDB editor|create with the XMLDB editor]], should contain the definition for your mdl_question_myqtype table, with the two columns col1 and col2.

At this stage, you do not need a db/upgrade.php file.

==The files you need for the second release==

;version.php
$plugin->version = 2008080200;
$plugin->requires = '''XXXXXXXXXX; // Copy the current value from the top-level version.php file.'''
;db/install.xml : This file should now contain the updated definition for your mdl_question_myqtype table, with three columns col1, col2 and newcol. You modify this file using the XMLDB editor.
;db/upgrade.php
:This file should contain the code that people need to run to upgrade from version 2008080100 of your plugin. That is, the code to add a column newcol to the mdl_question_myqtype table. You don't have to write this code yourself as the XMLDB editor will generate it for you. The upgrade.php file should contain a single function xmldb_qtype_myqtype_upgrade that looks a bit like:
function xmldb_qtype_myqtype_upgrade($oldversion = 0) {
$result = true;

/// Add a new column newcol to the mdl_question_myqtype
if ($result && $oldversion < 2008080200) {
'''// Code to add the column, generated by the 'View PHP Code' option of the XMLDB editor.'''
}

return $result;
}

''Hint: If you are modifying or adding a field/table, get the XMLDB editor to generate the PHP update code for you '''after''' making the changes in the editor. If you are deleting one, you need to generate the PHP code '''before''' making the change - or you won't be able to select the field/table to write the code for, because it no longer exists.''

==What happens when a user installs or upgrades your plugin==

The process is triggered when an administrator goes to the Admin notifications page (.../admin/index.php). The code that does the work is the upgrade_plugins function in lib/adminlib.php and reading this code is the best way to find out exactly what happens. In pseudo-code, what it does is:

For each plugin of this type (e.g. all qtype plugins) {
// For the body of this loop, suppose the current plugin being processed is myqtype.

Check that question/type/myqtype/version.php, .../db/upgrade.php and .../db/install.xml exist.

if ($CFG->qtype_myqtype_version exists, and is less than the number in version.php) {
Call the upgrade function xmldb_qtype_myqtype_upgrade from
upgrade.php, passing the old version number ($CFG->qtype_myqtype_version)
which says what is currently installed
Update $CFG->qtype_myqtype_version to the latest number from version.php
to record what is currently installed
}

else if ($CFG->qtype_myqtype_version does not exist) {
Create the tables from the definitions in install.xml
Update $CFG->qtype_myqtype_version to the latest number from version.php
to record what is currently installed
}
}

Of course, it is a bit more complex that than. However, the code in the upgrade_plugins is quite clear, and I encourage you to go and have a look at it so you can see all the details of how it works.

Let us now look at some worked examples:

===User installs version 2008080100 of myqtype===

To start with, the mdl_question_myqtype does not exist, and there will not be a qtype_myqtype_version row in the mdl_config table.

# The user will unzip myqtype.zip into the question/type folder.
# The user will visit the Admin notifications page.
# This will trigger Moodle to search for plugings to upgrade. It will find that the code for the qtype_myqtype plugin is now present, but there is no trace of it in the database, so it will install the plugin from the install.xml file.

At the end of this process, the mdl_question_myqtype table will exist with the two columns col1 and col2; and the qtype_myqtype_version row in the mdl_config table will contain 2008080100.

===User upgrades from version 2008080100 to version 2008080200 of myqtype===

To start with, the mdl_question_myqtype table will exist with the two columns col1 and col2; and the qtype_myqtype_version row in the mdl_config table will contain 2008080100.

# The user will delete the old question/type/myqtype folder.
# The user will unzip the new myqtype.zip into the question/type folder.
# The user will visit the Admin notifications page.
# This will trigger Moodle to search for plugings to upgrade. It will find that the code for version 2008080200 the qtype_myqtype plugin is now present, but the installed version of the the database tables (qtype_myqtype_version) is 2008080100. Therefore, it will call xmldb_qtype_myqtype_upgrade from upgrade.php, passing 2008080100 as the $oldversion argument.

At the end of this process, the mdl_question_myqtype table will now have three columns col1, col2 and newcol; and the qtype_myqtype_version row in the mdl_config table will contain 2008080200.

===User installs version 2008080200 of myqtype into a clean Moodle install===

To start with, the mdl_question_myqtype does not exist, and there will not be a qtype_myqtype_version row in the mdl_config table.

# The user will unzip the 2008080200 version of myqtype.zip into the question/type folder.
# The user will visit the Admin notifications page.
# This will trigger Moodle to search for plugings to upgrade. It will find that the code for the qtype_myqtype plugin is now present, but there is no trace of it in the database, so it will install the plugin from the install.xml file.

At the end of this process, the mdl_question_myqtype table will exist with three columns col1, col2 and newcol; and the qtype_myqtype_version row in the mdl_config table will contain 2008080200.

==Summary==

The first time a user installs any version of your plugin, the install.xml file will be used to create all the required database tables. Therefore install.xml should always contain the definition of the up-to-date database structure. Moodle recognises this situation because there is a version.php file on disc, but there is no ''plugintype''_''pluginname''_version value in the mdl_config table.

If the user already had a version of your plugin installed, and then upgrades to a newer version, Moodle will detect this because the version.php file will contain a newer version number than the ''plugintype''_''pluginname''_version value in the mdl_config table. In this case, Moodle will run the code in the upgrade.php file, passing in the old version number, so that the correct bits of upgrade can be run, as controlled by the if ($oldversion < XXXXXXXXXX) blocks of code.

The contents of the install.xml and upgrade.php files should be generated using the XMLDB editor.

==Database upgrades and stable branches==

The simple rule is, never make any database changes on a stable branch. You only need to read this section in the rare situations where a database change on the stable branch is unavoidable.

'''Warning!!! advanced material follows.'''

Suppose, in order to fix a bug, you need to make a database change in Moodle 1.9.3+ (which must also be merged into HEAD). The root of the problem is that people may upgrade their Moodle in three different ways, which

* Upgrade from <=1.9.3 to 1.9.4 - this executes the ugprade script on the 1.9 branch.
* Upgrade from <=1.9.3 directly to >=2.0 - this executes the upgrade script on the HEAD branch.
* Upgrade from 1.9.4 to >=2.0 - in this case, you must ensure that the upgrade on HEAD is not executed.

The normal way to do this is ensure that your database upgrade is idempotent. That is, it does not matter if you do it twice. So for example, instead of doing

$dbman->create_table($table);

you should do

if (!$dbman->table_exists($table)) {
$dbman->create_table($table);
}

You should also think about what version numbers to put in your version.php file on each branch. Above all, test carefully.

==See also==

* [[Development:XMLDB_Documentation|XMLDB_Documentation]]
* [[Development:Coding|Coding guidelines]]
* [[Development:DDL functions|DDL functions]]
* [[Development:XMLDB defining an XML structure|install.xml file documentation]]

[[Category:Developer]]
[[Category:XMLDB]]
[[Category:Installation]]

File upload size

2010-04-19T15:27:53Z

Mil091:

Probably the most frequently asked question in the Moodle.org Using Moodle forums is "How do I increase the upload file size limit?" The changes that need be made are the same in all versions of Moodle, just in different OS' they need be made in different places. Upload file sizes are restricted in a number of ways and each one in this list restricts the following ones:

Server level
Moodle site level
Course level
Activity level

This is a contentious issue, mainly because you might think that it should be set inside the Moodle. Unfortunately, this is not so, these are environment issues that need to be set in the server and PHP folders, Moodle cannot work outside itself.

==Physical access to Server==
These instructions assume you have full physical and administrative access to your server. If you are using a hosted server then you will probably need to look into other ways to increase your file upload size.

There are positives and negatives to both methods below. If you modify the pnp.ini file then the changes will effect all php applications on your server. Since PHP5 you can only have one php.ini file on your server. The php.ini method will work with all web servers though. The .htaccess method will only effect the folder and all subfolders that it is placed in, but you must have certain settings enabled in Apache.

===Modifying the php.ini file===
These instructions show you how to change the file upload size by editing your php.ini file.
====Ubuntu Linux Instructions====

These instructions assume that you have installed the standard Moodle package, PHP 5 and Apache 2 via apt-get and left it all as a default install. If you have compiled yourself I presume that you will know where your php.ini files are!

You need to edit the following three settings in your php.ini file located at: /etc/php5/apache2/

*Type "sudo nano /etc/php5/apache2/php.ini"
*Press Ctrl and W and type "post_max_size"
*Change the value to the number of Mb you want your site to accept as uploads
*Press Ctrl and W and type "upload_max_filesize"
*Change the value to the number of Mb you want your site to accept as uploads
*Press Ctrl and W and type "max_execution_time"
*Change the value to 600
*Press Ctrl and O
*Press Ctrl and X
*Type sudo /etc/init.d/apache2 restart

Your new file size limit should now appear in Administration > Security > Site Policies > Maximum uploaded file size

====Windows XP and Server 2003 Instructions====

These instructions presume that you have downloaded the latest PHP 5.2.x Windows zip package and extracted it to C:\PHP. If you have installed PHP to another location then change all references to "C:\PHP" to the location you installed PHP too.

*Open C:\PHP
*Right Click the '''php.ini''' file in this folder and choose "Open with..."
*Choose "Wordpad" not "Notepad" to open the file with (Notepad does not properly use the UTF-8 Character set and it's carriage returns can cause problems)
**Better still download and install any text editor that can save the file in a UTF-8 format, [http://www.crimsoneditor.com Crimson Editor] is one such, and use that instead of either Wordpad or Notepad!
*Press Ctrl and F and type "post_max_size"
*Change the value to the number of Mb you want your site to accept as uploads
*Press Ctrl and F and type "upload_max_filesize"
*Change the value to the number of Mb you want your site to accept as uploads
*Press Ctrl and F and type "max_execution_time"
*Change the value to 600
*Press Ctrl and S
*Exit Wordpad
*Restart your webserver
**'''For IIS'''
**Open your Start Menu on your server and select "Run"
**Type "iisreset /RESTART"
**'''For Apache 2'''
**The following command will work as long as you have installed Apache 2 as a service on your Windows Server
**Open your Start Menu on your server and select "Run"
**Type "httpd -k restart"

Your new file size limit should now appear in Administration > Security > Site Policies > Maximum uploaded file size

'''NOTE:''' These instructions should also cover the Xampp Windows installer. Just replace C:\PHP with C:\Moodle\server\php and to restart your Moode with a normal stop-start.

===Modifying the apache config file===
====Ubuntu Linux Instructions====
You may also need to edit the config.php file in the moodle directory:
*Type "gksudo nautilus" to get root permissions
*Navigate to /etc/moodle
*Open apache.conf
*Go to the "<IfModule mod_php5.c>" section
*Change "php_value upload_max_filesize = 2M" to a higher value
*Change "php_value post_max_size = 2M" to a higher value
*Go to the "<IfModule mod_php4.c>" section
*Change "php_value upload_max_filesize = 2M" to a higher value
*Change "php_value post_max_size = 2M" to a higher value
*Save file
*Type sudo /etc/init.d/apache2 restart

===Modifying the .htaccess file===
{{stub}}
The following instructions will only work on an Apache web server, and also the Apache server must have Overrides allowed. Additionally php must be running as an apache module, not as a cgi program.

Create a file called .htaccess in Moodle's main directory (where 'index.php' is located, not the 'moodledata' directory) that contains the following information:

php_value upload_max_filesize 20971520
php_value post_max_size 20971520
php_value max_execution_time 600

20971520 is the integer value for 20Mb. You can use the following site to [http://www.onlineconversion.com/computer_base2.htm convert MegaBytes to Bytes].

==Hosted Server==
Things can be a little different with a hosted server for uploaded and downloaded file size. You are probably going to to be told to create or change a .htaccess file, or to modify a php.ini file.

:It might be a good idea to talk to with your service provider before you attempt anything. They probably have instructions on "how to" and may have their own limits for uploaded file size. Some hosts measure the file size in gigabytes and others in megabytes. If you are unhappy with their limits, then check your contract and consider changing your provider to one that has a limit and price that you like.

===.htaccess with hosted server===
The one purpose of an .htaccess file is to override the the current limitations of both the server and the php.ini file. Your hosted server should inform you where that file needs be placed in your Moodle, but generally in the root is sufficient. They may already have a standard file you can use, if so, use it - but perhaps not.

To the .htaccess file add the lines:
php_value upload_max_filesize 128M
php_value post_max_size 128M

This will limit uploads to 128MB, but you can make it any size you agree with your provider. The wording may vary slightly, according to the demands of the server.

===php.ini with hosted server===
Some servers will not allow you to change the moodle root .htaccess file and tell you to use a php.ini file for php directives. Here you can use the instruction located in the section above called [[File_upload_size#Modifying_the_php.ini_file|Modifying the php.ini file]].

Find the php.ini file in your moodle subfolder on your hosted server. You might want to copy the file as a backup just in case. Edit php.ini, find "upload_max_filesize" and post_max_size in the code. After the = change the number. Here the max filesize is 20 megabytes.

upload_max_filesize = 20M
post_max_size = 20M

:Tip: Still not changed? Some hosts using cpanel have a php config program under services/software. Use the "Single php.ini" option and make sure you note the location of the php.ini file to modify. This changes the .htaccess file in the same area and thus the server limit for all programs using php.

==See Also==
*[[Administration_FAQ#How_do_the_limits_on_uploaded_files_work.3F|Administration FAQ Doc page]]
*[[Site_policies#Maximum_uploaded_file_size|Site Policies Doc page]]
*[[Installing_Moodle/Creating_custom_php.ini_files|Creating custom php.ini files Doc Page]]
* Using Moodle [http://moodle.org/mod/forum/discuss.php?d=39625 Detailed instructions to increase the maximum allowed size for uploaded files] forum discussion
* Using Moodle [http://moodle.org/mod/forum/discuss.php?d=97907 Instructions to increase maximum allowed size on hosted servers] forum discussion
* Using Moodle [http://moodle.org/mod/forum/discuss.php?d=124441 Help on changing the maximum upload size when installing Moodle via apt-get] forum discussion

[[Category:Administrator|File]][[Category:FAQ|File]]

Development talk:NEWMODULE Documentation

2009-12-04T14:30:05Z

Mil091: /* version.php comments */

== Old stuff moved out of the way ==

Daniele, did you know about [[Development:Modules]], which is linked to from [[Developer_documentation#Make_a_new_plugin]]? You may be better off editing that, rather than starting a new page. However don't let me discourage you. That documentation is very limited, and badly needs to be expanded, so it is absolutely brilliant that you want to work on it.--[[User:Tim Hunt|Tim Hunt]] 12:03, 28 March 2008 (CDT)

::Good point Tim! Anyway... I think we can use this as temporary root for Daniele's work and then, simply, move things to their final place. Let's see how this evolves. -- [[User:Eloy Lafuente (stronk7)|Eloy Lafuente (stronk7)]] 12:33, 28 March 2008 (CDT)

----

[http://lyceum.open.ac.uk/temp/creating_moodle_modules.ppt My powerpoint about creating moodle modules] is a bit old (1.8) and maybe not that comprehensible but I use it when I'm trying to explain things to people. It includes a list of all the things you need to have in a module, except the ones I forgot (backuplib iirc is missing). [[User:sam marshall|sam marshall]] 12:52, 28 March 2008 (CDT)

::That's really great, Sam. For sure that presentation will help.Thanks! -- [[User:Eloy Lafuente (stronk7)|Eloy Lafuente (stronk7)]] 13:08, 28 March 2008 (CDT)

Dear Tim, Sam and Eloy, I wrote what was my learning path on these issues. I wrote whatever I found in your powerpoint guide (thanks again, Sam) and whatever I got mainly from Tim, Eloy and Petr. A lot of issues are still obscure to me like: grades, groups, groupings, backup and so forth. Please let me know your opinion to make all together a plan to carry on in the best way. Thanks. -- [[User:Daniele Cordella|Daniele Cordella]] 11:55, 10 September 2009 (UTC)

: Just moved the old discussion stuff here. --[[User:Frank Ralf|Frank Ralf]] 07:55, 29 September 2009 (UTC)

== Formatting tips ==

Hi Daniele, Thanks for starting this documentation.

You might consider using more sub-headings instead of bullet points as those will show up in the contents section and make for easier navigation.

And when you use <nowiki><code php></nowiki> you will get syntax highlighting for your code.

There were also suggestions to incorporate Unit 7 of http://dev.moodle.org/course/view.php?id=2 into Moodle Docs (http://dev.moodle.org/mod/forum/discuss.php?d=360). --[[User:Frank Ralf|Frank Ralf]] 08:05, 29 September 2009 (UTC)

== Grades ==

Thanks for the great work Daniele! This is such an improvement :)

I can't find any reference to pushing grades into Moodle's grade book in the NEWMODULE docs. I think this is a core function of the majority of activity modules and would like to see it documented or at least referenced here. Will this be included in the not too distant future?

== version.php comments ==
in the NEWMODULE download, Can I suggest the comment for the line
$module->cron = 0; // Period for cron to check this module (secs)

be replaced with
$module->cron = 0; //Frequency for cron to check this module (secs). Copied to the cron field of modules table

Development talk:NEWMODULE Documentation

2009-12-04T14:29:18Z

Mil091:

== Old stuff moved out of the way ==

Daniele, did you know about [[Development:Modules]], which is linked to from [[Developer_documentation#Make_a_new_plugin]]? You may be better off editing that, rather than starting a new page. However don't let me discourage you. That documentation is very limited, and badly needs to be expanded, so it is absolutely brilliant that you want to work on it.--[[User:Tim Hunt|Tim Hunt]] 12:03, 28 March 2008 (CDT)

::Good point Tim! Anyway... I think we can use this as temporary root for Daniele's work and then, simply, move things to their final place. Let's see how this evolves. -- [[User:Eloy Lafuente (stronk7)|Eloy Lafuente (stronk7)]] 12:33, 28 March 2008 (CDT)

----

[http://lyceum.open.ac.uk/temp/creating_moodle_modules.ppt My powerpoint about creating moodle modules] is a bit old (1.8) and maybe not that comprehensible but I use it when I'm trying to explain things to people. It includes a list of all the things you need to have in a module, except the ones I forgot (backuplib iirc is missing). [[User:sam marshall|sam marshall]] 12:52, 28 March 2008 (CDT)

::That's really great, Sam. For sure that presentation will help.Thanks! -- [[User:Eloy Lafuente (stronk7)|Eloy Lafuente (stronk7)]] 13:08, 28 March 2008 (CDT)

Dear Tim, Sam and Eloy, I wrote what was my learning path on these issues. I wrote whatever I found in your powerpoint guide (thanks again, Sam) and whatever I got mainly from Tim, Eloy and Petr. A lot of issues are still obscure to me like: grades, groups, groupings, backup and so forth. Please let me know your opinion to make all together a plan to carry on in the best way. Thanks. -- [[User:Daniele Cordella|Daniele Cordella]] 11:55, 10 September 2009 (UTC)

: Just moved the old discussion stuff here. --[[User:Frank Ralf|Frank Ralf]] 07:55, 29 September 2009 (UTC)

== Formatting tips ==

Hi Daniele, Thanks for starting this documentation.

You might consider using more sub-headings instead of bullet points as those will show up in the contents section and make for easier navigation.

And when you use <nowiki><code php></nowiki> you will get syntax highlighting for your code.

There were also suggestions to incorporate Unit 7 of http://dev.moodle.org/course/view.php?id=2 into Moodle Docs (http://dev.moodle.org/mod/forum/discuss.php?d=360). --[[User:Frank Ralf|Frank Ralf]] 08:05, 29 September 2009 (UTC)

== Grades ==

Thanks for the great work Daniele! This is such an improvement :)

I can't find any reference to pushing grades into Moodle's grade book in the NEWMODULE docs. I think this is a core function of the majority of activity modules and would like to see it documented or at least referenced here. Will this be included in the not too distant future?

== version.php comments ==
Can I suggest the comment for the line
$module->cron = 0; // Period for cron to check this module (secs)

be replaced with
$module->cron = 0; //Frequency for cron to check this module (secs). Copied to the cron field of modules table

Development:XMLDB defining an XML structure

2009-04-06T14:34:46Z

Mil091:

[[Development:XMLDB Documentation|XMLDB Documentation]] > [[Development:XMLDB roadmap|Roadmap]] > Defining one XML structure
----

== Justification ==

Before Moodle 1.7, all the DB install and upgrade was developed twice (once to handle MySQL installations and another to handle PostgreSQL installations). This approach, although working, has caused some headaches in the past, mainly because it was really difficult to keep both lines of development 100% on sync. Some developers do they work against one RDBMS and it was complex to develop to the other one (two test environments, skills on both databases, slower development cycle...). And all this was happening with ''only'' two supported RDBMS!

One of the main objectives of Moodle 1.7 is to extend the the number of supported RDBMS to other flavours (more exactly, to Oracle and MSSQL). And the old approach (one line of development for each DB) could become an absolute nightmare.

Because of this we have planned to build one structure to define all the DB objects used by Moodle. This structure will provide the necessary level of abstraction to be shared by all the RDBMS systems, so the "multiple lines of development" explained in the previous paragraph will be out forever, giving us one robust and well defined way to handle DB objects independently of the underlying RDBMS being used.

== Implementation ==

Initially all our best wishes were to use the [http://phplens.com/lens/adodb/docs-datadict.htm#xmlschema AdoDB XML Schema]. As Moodle is using ADOdb libraries to communicate with databases it sounded like the natural approach to solve the problem. But, finally, two reasons prevented us to use it:

# Although working, it seems to be one feature in progress, with important changes/evolutions arriving at the time of write this document.
# Its lack of support for "prefixes" (one Moodle key feature, to allow multiple instances to run in the same server), would force us to create some awful tricks to generate the objects.

So, finally, we decided to build our own XML files, with everything we need to define every object present in the DB.

== The XMLDB editor ==
[[XMLDB_editor | Main article]]

Although the XML is pretty simple to read (and to write), one of the major drawbacks was its easy and error-prone adoption by the developers. Also some problems with versioning systems getting crazy with XML files (thanks ML!) pointed us to the requirement to use one high-density format (it means, physically '''long lines''') in our XML files.

After some intense thoughts we decided to build one specialised editor for our XML format. This editor should be easy to use and provide support for all the objects present one Moodle DB. And it's done (and will support future enhancements easily, we hope).

The XMLDB Editor makes the addition of tables/fields/keys/indexes practically a trivial task, allowing the developer to spend the time coding and improving things instead of fighting against XML files and the errors caused by manual editing (of course, the developer is free to use such extra-time as desired, beers, dance, books, music...) ;-)

All the 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. Obviously, such files, are the neutral replacement for all the *.sql files used until now.

=== Launching ===

Just login to your server as an administrator and, under the Miscellaneous tab of the Administration Block, you'll see a new link pointing to the "XMLDB Editor".

One '''important note''' is that, to be able to handle files properly, the web server needs write access to all those "db" directories where the "install.xml" files reside (and to the files themselves, of course). ;-)

That's all!

=== Use===

We really think the XMLDB Editor is pretty easy to use, so here you won't see a complete guide to use it. We highly recommend you to play with it for a while, viewing how it works and how it modifies the '''install.xml''' files.

It's organised in a top-botton structure, where you start '''loading''' (or '''creating''') a new XMLDB file. Then, you can '''edit''' such file and its '''general structure''' will be showed. This structure have 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.

'''Note: If you can't click on the create links....''' you must first create the /db folder (as shown in the list, but it may not really exist) and then make sure it is writeable by the webserver

While 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 no-editable. It uses to be 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 one. Exactly the same for both '''keys''' and '''indexes'''.

While 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 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 one '''comment''' about the item being modified (table, index, key, field, statement...). Use it at your entire needs, sure it helps other developers to understand a bit more the DB model.

Please, don't forget to read and understand the next section, where we talk about '''some important guidelines''' to create and handle XMLDB files.

== Conventions ==

Apart of the [[Coding#Database_structures| Database Structures guidelines]], some more conventions should be followed:

# About names:
## All lowercase names (tables, indexes, keys and fields).
## Table names and field names must use only a-z, 0-9 and _ chars. Table names can be at most 28 characters long; column names at most 30 characters.
## 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.
## Primary key always must be named "primary" (this is one exception to the previous convention).
## It's highly recommended to avoid [[XMLDB_reserved_words|reserved words]] completely. We know we have some of them now but they should be completely out for next releases.
# About NULLS
## Avoid to create all the fields as NOT NULL with the ''silly'' default value <nowiki>''</nowiki> (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 <nowiki>''</nowiki> clause|Problems Page]].
# About FOREIGN KEYS
## 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.
## 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!
## This convention is only applicable for relations INSIDE one file. Don't generate FK constraints against other files (courseid, userid), use indexes there.
## Respect Convention 1.3
# About UNIQUE KEYS
## Declare any fields as UNIQUE KEY (UK) only if they are going to be used as target for one FK. Create unique indexes instead.
## Respect Convention 1.3

== One example: the assignment module ==

Here we are going to examine the [http://cvs.moodle.org/moodle/mod/assignment/db/install.xml?view=markup current implementation of the XMLDB Schema for the assignment module] (a simple one). It has been completely generated with the XMLDB Editor but it's nice to know a bit more about the XML internals.

As you can see the structure is pretty simple:

* XMLDB
** TABLES, one or more, each one with
*** FIELDS
*** KEYS
*** INDEXES
** STATEMENTS, none or more, each one with
*** SENTENCES

First of all you should note that all the elements contain the '''PREVIOUS''' and '''NEXT''' attributes. They allow us to keep everything ordered although it isn't meaningful at all from the RDBMS perspective. Also the '''COMMENT''' field is present everywhere to be used as desired.

=== The TABLE element ===

We can ignore the TABLE element, as it's simply one container for the internals (FIELDS, KEYS and INDEXES). Let's go to examine them a bit more:

==== The FIELD element ====

It maps with one field in the DB (obviously). For each field you can define its '''name''', '''type''' (from a list of [[XMLDB column types|neutral types]]), '''length''', '''decimals''' (for some types), '''notnull''' (true/false), '''unsigned''' (true/false), '''sequence''' (if it's autonumeric or serial, true/false), '''enum''' (true/false), '''enumvalues''' (the list of values if the field has been declared as enum, for example <tt>'frog','toad','newt'</tt>) and '''default''' (to assign a default value).

So, in our example, we have two tables, assignment and assignment_submissions, each one with its own fields, defining all the information related above. Please note that naming conventions are followed.

==== The KEY element ====

Here is where all the PRIMARY KEYS (PK), UNIQUE KEYS (UK) and FOREIGN KEYS (FK) will be defined. For each key we define its '''name''', '''type''', '''fields''' (that belongs to it) and optionally (if the key is one FK) the target '''reftable''' and '''reffields'''.

In our example, the assignment table has one (mandatory!) PK (called, "primary", rules are rules) built with the "id" field.

The other table, the "assignment_submissions" one, also has its PK (called "primary" once more) and one FK, with the field "assignment" pointing to the field "id" of the table "assignment". Note that the FK follows the name conventions and its name is, simply, the name of the fields being part of it ("assignment"). Also, the FK has as target to one PK of the same module.

Finally, note that there isn't any index created for all these keys. Moodle will generate them automatically when the table is created. All the keys will have their corresponding index. Point. ;-)

==== The INDEX element ====

Where all the indexes will be defined. For each index you can define its '''name''', '''unique''' (true/false) and the '''fields''' that conform it. Please note that naming conventions are followed.

Also, some "obvious index", like the one based in the "assignment" field of the "assignment_submissions" table doesn't exist. Yes, you know why: Because such column has been defined as a FK and the index will be automatically created (see previous section).

=== The STATEMENT element ===

This is the other '''big container''' in the XMLDB Schema (at the same level as the '''TABLES''' one) and we can define its '''name''', '''type''' (only insert allowed for now) and '''table''' (against the sentences will be executed).

Every statement is a collection of '''sentences'''

==== The SENTENCE element ====

Each sentence implies one simple action to be performed against the DB and it can be defined as the "missing part of the SQL statement". In our example, we have one statement, of type "insert" on table "log_display". With this Moodle knows the initial part of the sentence, i.e:

INSERT INTO log_display

and then the text will be added to create this:

INSERT INTO log_display
(module, action, mtable, field)
VALUES
('assignment', 'view', 'assignment', 'name')

There is one important trick when handling sentences, although they aren't in the assignment example. Take a look to the [http://cvs.moodle.org/moodle/lib/db/install.xml?view=co Core Tables XML Schema] (it's a huge one!). If you go near the end, to the statements section, you will see some sentences like this:

<SENTENCE TEXT="....VALUES ('user', 'view', 'user', 'CONCAT(firstname," ",lastname)')"/>

Such "CONCAT" function isn't standard at all (only MySQL supports it), but don't worry, we'll transform it to the correct concatenation operators for other RDBMS. Just be sure to use the syntax showed above.

== DTD and XML schema ==

Not sure if this will be usable for somebody but here you can find one [http://cvs.moodle.org/moodle/lib/xmldb/xmldb.dtd?view=co automatically generated DTD] for the XMLDB files. Also one [http://cvs.moodle.org/moodle/lib/xmldb/xmldb.xsd?view=co automatically generated XML Schema] is available. Any improvement/fix to them will be welcome!

== See also ==

* [[XMLB List of files to create|List of files to create]]: The list of files to be created from scratch. Used to follow the progress.
* http://www.hitsw.com/xml_utilites/: One online XML-DTD-Schema converter.

[[Category:XMLDB]]

Development:XMLDB defining an XML structure

2009-04-06T14:33:37Z

Mil091:

[[Development:XMLDB Documentation|XMLDB Documentation]] > [[Development:XMLDB roadmap|Roadmap]] > Defining one XML structure
----

== Justification ==

Before Moodle 1.7, all the DB install and upgrade was developed twice (once to handle MySQL installations and another to handle PostgreSQL installations). This approach, although working, has caused some headaches in the past, mainly because it was really difficult to keep both lines of development 100% on sync. Some developers do they work against one RDBMS and it was complex to develop to the other one (two test environments, skills on both databases, slower development cycle...). And all this was happening with ''only'' two supported RDBMS!

One of the main objectives of Moodle 1.7 is to extend the the number of supported RDBMS to other flavours (more exactly, to Oracle and MSSQL). And the old approach (one line of development for each DB) could become an absolute nightmare.

Because of this we have planned to build one structure to define all the DB objects used by Moodle. This structure will provide the necessary level of abstraction to be shared by all the RDBMS systems, so the "multiple lines of development" explained in the previous paragraph will be out forever, giving us one robust and well defined way to handle DB objects independently of the underlying RDBMS being used.

== Implementation ==

Initially all our best wishes were to use the [http://phplens.com/lens/adodb/docs-datadict.htm#xmlschema AdoDB XML Schema]. As Moodle is using ADOdb libraries to communicate with databases it sounded like the natural approach to solve the problem. But, finally, two reasons prevented us to use it:

# Although working, it seems to be one feature in progress, with important changes/evolutions arriving at the time of write this document.
# Its lack of support for "prefixes" (one Moodle key feature, to allow multiple instances to run in the same server), would force us to create some awful tricks to generate the objects.

So, finally, we decided to build our own XML files, with everything we need to define every object present in the DB.

== The XMLDB editor ==
[[XMLDB_editor | Main article]]

Although the XML is pretty simple to read (and to write), one of the major drawbacks was its easy and error-prone adoption by the developers. Also some problems with versioning systems getting crazy with XML files (thanks ML!) pointed us to the requirement to use one high-density format (it means, physically '''long lines''') in our XML files.

After some intense thoughts we decided to build one specialised editor for our XML format. This editor should be easy to use and provide support for all the objects present one Moodle DB. And it's done (and will support future enhancements easily, we hope).

The XMLDB Editor makes the adddition of tables/fields/keys/indexes practically a trivial task, allowing the developer to spend the time coding and improving things instead of fighting against XML files and the errors caused by manual editing (of course, the developer is free to use such extra-time as desired, beers, dance, books, music...) ;-)

All the 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. Obviously, such files, are the neutral replacement for all the *.sql files used until now.

=== Launching ===

Just login to your server as an administrator and, under the Miscellaneous tab of the Administration Block, you'll see a new link pointing to the "XMLDB Editor".

One '''important note''' is that, to be able to handle files properly, the web server needs write access to all those "db" directories where the "install.xml" files reside (and to the files themselves, of course). ;-)

That's all!

=== Use===

We really think the XMLDB Editor is pretty easy to use, so here you won't see a complete guide to use it. We highly recommend you to play with it for a while, viewing how it works and how it modifies the '''install.xml''' files.

It's organised in a top-botton structure, where you start '''loading''' (or '''creating''') a new XMLDB file. Then, you can '''edit''' such file and its '''general structure''' will be showed. This structure have 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.

'''Note: If you can't click on the create links....''' you must first create the /db folder (as shown in the list, but it may not really exist) and then make sure it is writeable by the webserver

While 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 no-editable. It uses to be 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 one. Exactly the same for both '''keys''' and '''indexes'''.

While 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 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 one '''comment''' about the item being modified (table, index, key, field, statement...). Use it at your entire needs, sure it helps other developers to understand a bit more the DB model.

Please, don't forget to read and understand the next section, where we talk about '''some important guidelines''' to create and handle XMLDB files.

== Conventions ==

Apart of the [[Coding#Database_structures| Database Structures guidelines]], some more conventions should be followed:

# About names:
## All lowercase names (tables, indexes, keys and fields).
## Table names and field names must use only a-z, 0-9 and _ chars. Table names can be at most 28 characters long; column names at most 30 characters.
## 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.
## Primary key always must be named "primary" (this is one exception to the previous convention).
## It's highly recommended to avoid [[XMLDB_reserved_words|reserved words]] completely. We know we have some of them now but they should be completely out for next releases.
# About NULLS
## Avoid to create all the fields as NOT NULL with the ''silly'' default value <nowiki>''</nowiki> (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 <nowiki>''</nowiki> clause|Problems Page]].
# About FOREIGN KEYS
## 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.
## 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!
## This convention is only applicable for relations INSIDE one file. Don't generate FK constraints against other files (courseid, userid), use indexes there.
## Respect Convention 1.3
# About UNIQUE KEYS
## Declare any fields as UNIQUE KEY (UK) only if they are going to be used as target for one FK. Create unique indexes instead.
## Respect Convention 1.3

== One example: the assignment module ==

Here we are going to examine the [http://cvs.moodle.org/moodle/mod/assignment/db/install.xml?view=markup current implementation of the XMLDB Schema for the assignment module] (a simple one). It has been completely generated with the XMLDB Editor but it's nice to know a bit more about the XML internals.

As you can see the structure is pretty simple:

* XMLDB
** TABLES, one or more, each one with
*** FIELDS
*** KEYS
*** INDEXES
** STATEMENTS, none or more, each one with
*** SENTENCES

First of all you should note that all the elements contain the '''PREVIOUS''' and '''NEXT''' attributes. They allow us to keep everything ordered although it isn't meaningful at all from the RDBMS perspective. Also the '''COMMENT''' field is present everywhere to be used as desired.

=== The TABLE element ===

We can ignore the TABLE element, as it's simply one container for the internals (FIELDS, KEYS and INDEXES). Let's go to examine them a bit more:

==== The FIELD element ====

It maps with one field in the DB (obviously). For each field you can define its '''name''', '''type''' (from a list of [[XMLDB column types|neutral types]]), '''length''', '''decimals''' (for some types), '''notnull''' (true/false), '''unsigned''' (true/false), '''sequence''' (if it's autonumeric or serial, true/false), '''enum''' (true/false), '''enumvalues''' (the list of values if the field has been declared as enum, for example <tt>'frog','toad','newt'</tt>) and '''default''' (to assign a default value).

So, in our example, we have two tables, assignment and assignment_submissions, each one with its own fields, defining all the information related above. Please note that naming conventions are followed.

==== The KEY element ====

Here is where all the PRIMARY KEYS (PK), UNIQUE KEYS (UK) and FOREIGN KEYS (FK) will be defined. For each key we define its '''name''', '''type''', '''fields''' (that belongs to it) and optionally (if the key is one FK) the target '''reftable''' and '''reffields'''.

In our example, the assignment table has one (mandatory!) PK (called, "primary", rules are rules) built with the "id" field.

The other table, the "assignment_submissions" one, also has its PK (called "primary" once more) and one FK, with the field "assignment" pointing to the field "id" of the table "assignment". Note that the FK follows the name conventions and its name is, simply, the name of the fields being part of it ("assignment"). Also, the FK has as target to one PK of the same module.

Finally, note that there isn't any index created for all these keys. Moodle will generate them automatically when the table is created. All the keys will have their corresponding index. Point. ;-)

==== The INDEX element ====

Where all the indexes will be defined. For each index you can define its '''name''', '''unique''' (true/false) and the '''fields''' that conform it. Please note that naming conventions are followed.

Also, some "obvious index", like the one based in the "assignment" field of the "assignment_submissions" table doesn't exist. Yes, you know why: Because such column has been defined as a FK and the index will be automatically created (see previous section).

=== The STATEMENT element ===

This is the other '''big container''' in the XMLDB Schema (at the same level as the '''TABLES''' one) and we can define its '''name''', '''type''' (only insert allowed for now) and '''table''' (against the sentences will be executed).

Every statement is a collection of '''sentences'''

==== The SENTENCE element ====

Each sentence implies one simple action to be performed against the DB and it can be defined as the "missing part of the SQL statement". In our example, we have one statement, of type "insert" on table "log_display". With this Moodle knows the initial part of the sentence, i.e:

INSERT INTO log_display

and then the text will be added to create this:

INSERT INTO log_display
(module, action, mtable, field)
VALUES
('assignment', 'view', 'assignment', 'name')

There is one important trick when handling sentences, although they aren't in the assignment example. Take a look to the [http://cvs.moodle.org/moodle/lib/db/install.xml?view=co Core Tables XML Schema] (it's a huge one!). If you go near the end, to the statements section, you will see some sentences like this:

<SENTENCE TEXT="....VALUES ('user', 'view', 'user', 'CONCAT(firstname," ",lastname)')"/>

Such "CONCAT" function isn't standard at all (only MySQL supports it), but don't worry, we'll transform it to the correct concatenation operators for other RDBMS. Just be sure to use the syntax showed above.

== DTD and XML schema ==

Not sure if this will be usable for somebody but here you can find one [http://cvs.moodle.org/moodle/lib/xmldb/xmldb.dtd?view=co automatically generated DTD] for the XMLDB files. Also one [http://cvs.moodle.org/moodle/lib/xmldb/xmldb.xsd?view=co automatically generated XML Schema] is available. Any improvement/fix to them will be welcome!

== See also ==

* [[XMLB List of files to create|List of files to create]]: The list of files to be created from scratch. Used to follow the progress.
* http://www.hitsw.com/xml_utilites/: One online XML-DTD-Schema converter.

[[Category:XMLDB]]