I am in the process of moving student information into QMplus. Some of this information is appropriately represented as a table of records, where it is useful to be able to display an overview of the whole table and to focus a specific record. Previously our past exam papers were hosted by the library as a table of links to files and our undergraduate module details consisted of an overview table containing links to a collection of web pages all generated from a single XML file. In both cases, I wanted a facility that our professional services staff should be able to take over and maintain without, for example, needing to know anything about XML.

In both cases, an HTML table on a QMplus page would be possible. But then we would need to host the files somewhere else and there is no easy way to focus on a single table row. Moreover, editing large tables can get tricky. So I think that a Moodle database offers a better solution.

The steps required to create a Moodle database are these:

1. Decide where to put it. (This decision is important because databases are difficult to move between course areas!)
2. Decide on the key settings, bearing in mind that Moodle databases are considered to be student learning activities. To provide student information, under Availability I set the database to be read only.
3. Decide on the fields (table columns) in each record (table row). The field type primarily determines how the field is edited, e.g. the text input type allows a single short line of unformatted text whereas the text area type allows a possibly large amount of formatted text, and the file type allows a single file to be uploaded.
4. Enter the content, either by typing it or by uploading files. Content other than files can also be imported using a CSV-type file.
5. Edit the HTML display code generated automatically from the field definitions when you created the database. This is optional, but is the only way to get a single-table overview.
6. Optionally, add some CSS and/or JavaScript to customize the appearance and/or add interactivity.

I recommend experimenting before entering a lot of content to ensure that you can construct a usable database. Then enter the main content required. Then edit the content further and make it look prettier if you want. You can edit all the templates retrospectively as much as you want. This is one of the few situations in QMplus where you can use CSS and JavaScript in a fairly conventional way in the appropriate templates, although you need to watch out for interactions with the code already in QMplus and beware that QMplus updates will probably break your customization!

In the case of the past exam papers, I uploaded the PDF files by hand. I obtained some of them by doing a bulk download from the library web page and then giving the files sane filenames. I obtained the rest by asking colleagues to email them to me.

In the case of the module details, I modified the XSLT that I used previously to generate HTML so that it generated a CSV-type file using tabs as field separators (because the text contained commas) and then imported the entries into the database. (I cheated slightly and did some editing by hand, given that the conversion was a one-off process!)

I think the results are satisfactory although people tell me it is generally harder to find information in QMplus than on the Library and Maths web sites.

However, a minor problem with putting files in a Moodle database is that Android users cannot open them directly from the web browser, although they can still open the downloaded files from within Adobe Reader. This is the same problem that affects files elsewhere in QMplus if they use the current default display option of "force download", whereas this problem does not arise if the display option is set to "open". Unfortunately, this control is not available for files in a database.

Technically, what appears to happen is that files for which the display option is set (implicitly for a database) to "force download" are served with their HTTP content disposition header field set to "attachment", which means the browser should offer user-controlled display, whereas files that are set to "open" are served with their content disposition set to "inline", which means the browser should display them automatically. The client cannot control the response sent by the server, so there is no way for the browser to change this behaviour. It is a moot point whether this Moodle behaviour is incorrect and arguably it is Android that is at fault. But the problem would be solved if Moodle offered display control for files in a database and/or automatically set the default display to "open" for appropriate file types such as PDF.

I would like my databases to display all their (50–60) records by default but there doesn't appear to be any official way to set that and for a guest user it always defaults to 10 records per page. One of my colleagues has complained about this. So I have added some JavaScript what works around this limitation by automatically selecting display of 100 records on behalf of the user if necessary and hiding the controls. However, I think that Moodle should allow this control when a database is set up.

The module details database is slower than I would like it to be, although the exam paper database seems fast enough.

There is immense scope for customizing QMplus.

A Moodle database is a single-table database and not a relational database!

The two display modes supported by the database activity, list view and single view, are mediated by HTML that is output when a page is generated. It contains place holders for the database fields. By default, each record is output as a single table, which is appropriate for single view but probably not for list view. To make list view display an overview of the database as a single table, each record needs to be output as a single table row, not as a complete table, and this change needs to be made by hand. The editor offers three components called Header, Repeated entry, and Footer. The Header component is output once, then the Repeated entry component is output once for each record, then the Footer component is output. The Header and Footer components cannot contain well-formed HTML and so must be edited as HTML code and not graphically. Beware that saving in graphical mode will mangle the code and graphical mode is unfortunately the default! It's sufficiently easy to forget this that it's probably worth keeping a recent backup of just this code segment.

Copying a Moodle database complete with its contents is difficult! The best way, and in general the only way, I have found is to do a backup and restore (using either the "Database activity administration" menu or the "Course administration" menu) of the database activity including user data, because Moodle considers the content to be user data. This requires the administrator or course administrator role. Duplicating a database does not duplicate its contents. Nor does a course area import and nor does a course area backup and restore if you exclude user data (which is what import appears to do implicitly). Exporting the contents to a CSV-type file does not include file content and whilst in principle exporting to a CSV-type file and then importing should copy non-file content, I found that this process is sensitive to linebreaks in the text content, so I had to edit them out. (HTML normally contains linebreaks even though they are redundant.)

The fact that database content is regarded as user content may mean that if you roll over a course area containing a database the content will be erased. So I recommend that you either use a course area that does not need to be rolled over or backup the database before you roll the course area over.

There appears to be no straightforward way to change the order of fields. This doesn't really matter because all user interaction is controlled by HTML that can use the field content anywhere you want, but it looks odd and the database activity generates default HTML that lists the fields in the order they are specified. So it's worth trying to get the field definitions right before you construct your database. However, it seems that the field order can be changed without losing information by simply renaming the fields carefully.

The facilities for sorting records seem to be very crude so I use the jQuery tablesorter plug-in to allow flexible sorting of our module details database.

I have saved my past exam paper database as a preset called "Past exam papers (Francis Wright)". Creating a new database activity and then using this preset will create an empty copy of my database, which you can then optionally edit and populate with your own content. (It also provides me with a backup of my database template.) But I suggest that anyone using my preset checks that they really want to run my JavaScript!

The only database guidance currently available is the fairly terse official documentation for the (Moodle 2.6) Database activity module.

Here are the books that I refer to all the time for tweaking QMplus (and other web sites):

1. CSS Pocket Reference by Eric A. Meyer (O'Reilly)
2. jQuery Pocket Reference by David Flanagan (O'Reilly)
3. JavaScript: The Definitive Guide by David Flanagan (O'Reilly) (which includes the jQuery Pocket Reference above as a chapter)