MoodleDocs - User contributions [en]

Development:Git Migration

2008-08-08T08:15:25Z

Papillon: /* Other interesting links */ Name typo

= Proposal to move Moodle development from CVS to Git =

There have been discussions to abandon CVS as the main version tracking system, to move to a distributed system instead. Many developers have expressed their interest for [http://git.or.cz Git]. A [http://moodle.org/mod/forum/discuss.php?d=99763 discussion] is taking place in moodle.org forums about a possible move to Git and contains a lot of information.

Some other systems have also been mentioned, such as [http://www.selenic.com/mercurial/wiki/ Mercurial].

= Git and human usage =

* A lot of people need an easy interface to the revision system. Most of them are currently using TortoiseCVS (a lot of translators, it seems). There is no tool (as of late June 2008) for Git that is equivalent in ease of use to TortoiseCVS, though [http://digilander.libero.it/mcostalba/ qgit] (said to be running flawlessly on Windows) and [http://cola.tuxfamily.org/ git-cola] are improving. In the meantime, people could use a CVS gateway, which would need to work both ways (read & write), to give people time to change over once proper tools become available.
* Developers using Eclipse, NetBeans, and most other major IDEs need to wait for plugins. There is a [http://wiki.eclipse.org/EGit/Proposal proposal], which is aiming to provide a pure-Java implementation along with an Eclipse plugin and should lead to support at least in the Java-based IDEs, but this is still not ready for prime time (June 2008).
* [http://vim.sourceforge.net/scripts/script.php?script_id=90 VCSCommand] plugin for Vim now supports Git.
* Some users are using CVS mirrors to deploy (and update) their site. Can't break this. A read-only CVS gateway could be a good working solution to this.

= Git and server-side tool integration =

* Web interface to the repository: Git provides a web interface. Alternatively, Trac now provides a [http://trac-hacks.org/wiki/GitPlugin GitPlugin] that could be used.
* Bug tracking: see [http://forums.atlassian.com/thread.jspa?threadID=24671&tstart=0] and [http://jira.atlassian.com/browse/FE-337] (support still uncomplete, on June 24 2008). Martín Langhoff says: "[http://moodle.org/mod/forum/discuss.php?d=99763#p441057 looks like there is a mercurial plugin we can quickly grab and, ahem, repurpose...]". We could also use a CVS gateway, though there could be problems with this workaround, as "[http://moodle.org/mod/forum/discuss.php?d=99763#p440831 the rev numbers don't line up]" (seems to be fixed, as of late July 2008).
* The module managing [http://moodle.org/cvs moodle.org/cvs] would need to be rewritten to use Git. The user-access control would need to be managed [http://moodle.org/mod/forum/discuss.php?d=99763#p441030 using the pre-received hook].
* Need to see if cvsspam (for the cvs-commits list) would still work
* Moving cvs.moodle.org to git.moodle.org? Or keep both?

= The way to go for developers who want it now =

It's possible to use Git right now, to track
* Follow instructions on the "[[Tracking Moodle CVS with git]]" page
* Use git-cvsexportcommit to commit to Moodle CVS
* Wait until the next refresh (50min after the hour every hour, New Zealand time) to get the patch back into your local Git repository

= Git tutorials and reference =
Here is a list of usefule tutorials and reference links for Git.
* [http://www.kernel.org/pub/software/scm/git/docs/gitcvs-migration.html gitcvs-migration - git for CVS users]
* [http://git.or.cz/course/svn.html Git - SVN Crash Course]
* [http://www.kernel.org/pub/software/scm/git/docs/everyday.html Everyday GIT With 20 Commands Or So]
* [http://www.kernel.org/pub/software/scm/git/docs/gittutorial.html gittutorial - A tutorial introduction to git]
* [http://www.kernel.org/pub/software/scm/git/docs/user-manual.html Git User's Manual]
* [http://www.kernel.org/pub/software/scm/git/docs/howto-index.html GIT Howto Index]

= Other interesting links =

* [http://garrys-brain.blogspot.com/2008/04/git-for-windows-msysgit.html Garry Bodsworth] says some good of the MinGW port of Git (for Windows)
* Installation reports - Git growth amongst DSCMs: [http://tinyurl.com/4uemg2 http://tinyurl.com/4uemg2]
* Usage reports - With a longer timeframe, and counting CVS/SVN: [http://tinyurl.com/4hu2cn http://tinyurl.com/4hu2cn]
* Get a free personal public git repo with some cool tools: [http://github.com/ http://github.com/]
* Free screencasts by Scott Chacon on using Git: [http://gitcasts.com/ http://gitcasts.com/]
* [http://pragprog.com/titles/tsgit/pragmatic-version-control-using-git Pragmatic Version Control using Git] will come out on November 1st, 2008.
* [http://article.gmane.org/gmane.comp.version-control.git/44827 Message from Linus Torvalds, on gmane.comp.version-control.git]

[[Category:Developer]]
[[Category:Developer tools]]

Development:XMLDB Documentation

2007-04-25T10:12:16Z

Papillon: /* Developing */ Typo (it's -> its)

__NOTOC__

XMLDB is Moodle's database abstraction layer. That is, the library of code that lets moodle interact with and access the database.

In this page you will find links to all the XMLDB related documentation. Currently you will find documents both belonging to the [[:Category:XMLDB|XMLDB]] category and to the general [[:Category:Developer|Developer]] and [[:Category:Installation|Installation]] categories plus some interesting external links.

=== Introduction ===

* [[Development:XMLDB introduction|Introduction]]: Where the general idea of the XMLDB whole thing is explained.
* [[Development:XMLDB roadmap|Roadmap]]: An overall view of the process of implementing the XMLDB subsystem, with details for each point of the process.
* [[Development:XMLDB problems|Problems]]: A comprensive list of issues that need to be determined/solved prior to incorporate them to the [[XMLDB Roadmap|roadmap]].

=== Installation ===

* [[Installing MSSQL for PHP]]: One short manual about the steps needed to successfully add MSSQL support to PHP.
* [[Installing Oracle for PHP]]: One short manual about the steps needed to successfully add Oracle support to PHP.
* [[Installing Moodle]]: The guide to install Moodle (once you have the [[Installing AMP|LAMP platform]] working in your server.

=== Developing ===

* [[Development:DDL functions|DDL functions]]: One list of the new XMLDB Data Definition functions and their usage.
* [[Development:DML functions|DML functions]]: One list of the available Data Manipulation functions and their usage.
* [[Development:XMLDB defining an XML structure|XMLDB Usage]]: Explanation about the XML schema basis, the [[Development:XMLDB defining an XML structure#The XMLDB editor|XML editor]] embedded within Moodle, its [[Development:XMLDB defining an XML structure#Conventions|naming conventions]] and one simple but illustrative [[Development:XMLDB defining an XML structure#One example: the assignment module|example]].
* [[Development:Coding|Coding guidelines]]: The main coding guidelines and, more exactly, the [[Development:Coding#Database structures|database structures section]] that you must follow carefully in order to produce good cross-db code.
* [[Development:Database Schema|Database schema]]: Moodle database diagrams in [http://fabforce.net/dbdesigner4/ DBDesigner4] format.

=== Bugs and new features ===

* [http://tracker.moodle.org/secure/IssueNavigator.jspa?reset=true&&type=1&pid=10011&resolution=-1&component=10131&sorter/field=issuekey&sorter/order=DESC XMLDB known bugs]
* [http://tracker.moodle.org/secure/IssueNavigator.jspa?reset=true&&type=4&type=2&type=3&type=5&pid=10011&resolution=-1&component=10131&sorter/field=issuekey&sorter/order=DESC XMLDB pending improvements and features]
* [http://moodle.org/mod/forum/view.php?id=55 Developers forum]: For general development discussions.
* [http://moodle.org/mod/forum/view.php?id=45 Databases forum]: For DBs related development discussions.

=== See also ===
* [[Development:XMLDB column types|DB column types]] - Some links about column types inside every RDBMS and their characteristics
* [[Development:XMLDB key and index naming|DB key and index naming]] - Some info about automatic naming of keys/indexes and other objects.
* [[Development:XMLDB reserved words|DB reserved words]] - A collection of reserver words inside each RDBMS

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

Development:XMLDB defining an XML structure

2007-02-27T11:13:34Z

Papillon: /* The FIELD element */ Note about '''enum'''

[[XML database schema]] > [[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 exact 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 ==

Although the XML is pretty simple to read (and to write), one of the major drawbacks was its easy and error prune 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 edition 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 and, under the Admin Interface, 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.

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.
## 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://moodle.cvs.sourceforge.net/moodle/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) and '''default''' (to assign a default value). Note: If '''enum''' is true, the length field should not be defined.

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://moodle.cvs.sourceforge.net/*checkout*/moodle/moodle/lib/db/install.xml 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://moodle.cvs.sourceforge.net/moodle/moodle/lib/xmldb/xmldb.dtd?view=markup automatically generated DTD] for the XMLDB files. Also one [http://moodle.cvs.sourceforge.net/moodle/moodle/lib/xmldb/xmldb.xsd?view=markup 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:Developer]]
[[Category:XMLDB]]

Development:XMLDB defining an XML structure

2007-02-27T09:14:26Z

Papillon: /* Use */ typo

[[XML database schema]] > [[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 exact 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 ==

Although the XML is pretty simple to read (and to write), one of the major drawbacks was its easy and error prune 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 edition 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 and, under the Admin Interface, 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.

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.
## 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://moodle.cvs.sourceforge.net/moodle/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) 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://moodle.cvs.sourceforge.net/*checkout*/moodle/moodle/lib/db/install.xml 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://moodle.cvs.sourceforge.net/moodle/moodle/lib/xmldb/xmldb.dtd?view=markup automatically generated DTD] for the XMLDB files. Also one [http://moodle.cvs.sourceforge.net/moodle/moodle/lib/xmldb/xmldb.xsd?view=markup 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:Developer]]
[[Category:XMLDB]]