Overview of the Experience

I would like to say that a lot of work went into researching and implementing this database link with Unit-E (on an Oracle database). It was fraught with many problems and issues, ranging from incomplete/inaccurate instructions on the Interne to a lack of understanding (on our part) of the PHP/Oracle communication procedure. It is hoped that this account of our experiences will help other establishments to implement a similar system, connecting to either Unit-E, or another Oracle database.

A Note about this Document

This document was created primarily as an account of our experiences integrating Moodle with Unit-E, documenting what we did, and where we 'tripped up' along the way. I can't profess that this is a a comprehensive HOWTO guide, as it has been tested only within our own establishment. I do hope though that others (attemptimg the same or similar tasks) can adapt this and save a little time.

Outline of our Aims

Our plan (backed by our new Teaching and Learning Strategy) was to put into a place a Virtual Learning Environment (VLE) (aka Course Management System, aka Learning Platform) that was tightly integrated with our Management Information System (MIS):

It was decided amongst a number of College managers that the VLE platform we would use for this would be Moodle.

The Planned Integration

The MIS in use by the College is product produced by Capita named Unit-E. Unit-E (at its backend) uses an Oracle 9 database.

We decided that the best way to implement the integration between Moodle and Unit-E would be to utilise the new Moodle version 1.7 functionality to install onto an Oracle database, using the same database platform that currently holds the Unit-E data.

An important step along the way (something that we had to re-do) was to ensure that the Moodle database instance was set to UTF-8 (Unicode). Our Oracle database had defaulted to the US_ASCII character set, and Moodle complained about this. We decided to destroy/recreate the database in order to keep Moodle happy about this and protect ourselves in future versions.

Our Moodle Server Platform

For our new Moodle server we decided to use a Linux-based server OS; in this case we chose Ubuntu 6.06 LTS (AKA 'Dapper Drake') as our server platform. Thus any technical instructions mentioned here refer to Linux, specifically Ubuntu. Adapting these instructiong to other Linux distributions (especially Debian-based ones) should be relatively easy.

Whilst in principle the same steps are followed to make this work on a Windows server platform, the technical details will be very different!

Software Installation

General Server Software

Once we had installed the core operating system onto the hardware, we needed to install the correct server software. As root we type:

apt-get install apache2 php5 php5-dev php-pear php5-gd php5-ldap openssh-server samba make gcc xinetd

The operating system now automatically downloads and installs up-to-date versions of all of the required server software from the Internet and the distribution DVD. Linux distributions without aptitude (apt-get) must use either their own installation procedure (eg: RPM) or must obtain and compile these packages from source.

The php5-dev and php-pear packages are required for the Oracle client that we shall install later (and additionally the WebDAV Moodle extension that we plan to make use of). We also wished to use LDAP authentication (hence the PHP-LDAP module). I finally installed OpenSSH for remote administration, as well as SAMBA server to access the VLE filesystem. I will not go into detail about how to configure SSH or SAMBA server, although I must recommend this very simple SAMBA Tutorial.

Note that this solution does not require MySQL at all.

Install the Oracle Instant Client

To install the Oracle Instant Client we must first download and install the files from Oracle's website, then install the extension to PHP (OCI8) that communicates with the instant client. One of our first mistakes was not realising that there are two separate steps to the installation at this stage.

In order to have a basic communication between PHP5 and an Oracle Database we should download the following two files from the Oracle Instant Client Page (link correct as of writing):

I am actually unsure whether the SDK is needed, however instructions I follwed/adapted on this Ubuntu Forum Post suggested the SDK pack also. In order to download these files we required an Oracle website login. It's likely we already had one at the College, but they are free to create.

Installing these files is simply a case of extracting them to a new folder and creating two symbolic links. I chose to install to /opt/Oracle/, but any location is reasonable. Once the files are extracted from the ZIPs, create two symbolic links as such:

At this point came our second trip-up point, where I attempted to continue following the instructions from the forum post above. As pointed out in the comments, the code pear install oci8 is now obsolete. The 'alternative' code presented in the instructions is also not required. Instead the following single line installs the instant client extension for PHP 5.

pecl install oci8

When prompted, enter instantclient,/opt/Oracle/instantclient_10_1 (replacing with the installation location of your instant client installation if required).

Finally, to enable the PHP 5 OCI8 module, add the following line to /etc/php5/apache2/php.ini. In the Dynamic extensions section (just after the commented-out example entries) add:

extension=oci8.so

If you wish to verify that the OCI8 module is installed at this point, restart your Apache2 server and create a new PHP page within your web published folder (for example /var/www/) containing the single line <? phpinfo(); ?>. Visit this page in a browser window and you should be able to find a section entitled OCI8 on that page (it's some way down). If this section appears and lists some information then PHP is correctly talking to the Instant Client.

Configuring the Instant Client

The next step is to configure the Instant Client to connect to our database. Firstly we must set some environment variables for Apache when it starts up. Ubuntu (and probably many other distributions) provides a file /etc/apache2/envvars where we can enter a number of envrionment variables for Apache as it starts up. Edit this file (or create a script that sets these environment variables for Apache as it starts up, (I believe on a Windows server this is achieved via the registry) to read:

#!/bin/sh

LD_LIBRARY_PATH=/opt/oracle/instantclient_10_1:${LD_LIBRARY_PATH}
TNS_ADMIN=/etc/oracle
export LD_LIBRARY_PATH TNS_ADMIN

You may notice that we do not set NLS_LANG here - I am unsure whether this is a problem that is local to our installation, or whether it will affect everybody, however if I set the NLS_LANG environment variable (as Moodle's installation procedure demands) then the Instant Client fails to connect to the database (which caused much headache for myself). Later I commented some lines of Moodle's code out in order to stop it checking for this environment variable. This issue might affect other establishments also (in which case the Moodle code is at fault, or it might be a quirk of our database set up. I know too little about PHP/Oracle to say which of the two is the case.

The TNS_ADMIN environment variable can be set to any path, specifically it is where you plan to store your tnsnames.ora file. Tnsnames.ora is a feature of Oracle databases, and describes how to find and connect to the database server. Your database administrator should be able to provide you with one once the new database instance has been created. Here is a sample (minimal) file:

MOODLE =
(DESCRIPTION =
  (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = ip.or.dns.of.database.server)
    (PORT = 1521)
  )
  (CONNECT_DATA =
    (SID = NAME_OF_THE_DATABASE_IN_ORACLE)
  )
)

Stop the Apache server and start it; a restart will not be sufficient to apply the new environment variables!.

Installing Moodle

At this point, the web server is ready to accept a Moodle installation. Unzip a Moodle 1.7+ installer into your web published folder (for example /var/www/), ensure that the www-data user can write a new config.php file into its folder and create a folder for Moodle's data.

Optional Step

The following step may or may not apply to other establishments. It did to us!

At this point we were unable to get past the database-configuration step of the installation. This was because Moodle demanded that the NLS_LANG environment variable be set. Setting it and stopping/starting Apache prevented this error, but created a much more mysterious situation in which we couldn't connect and recieved very little feedback of why.

In simple tests (creating small PHP test pages independant of Moodle) I found that setting the NLS_LANG environment variable actually created an error within the Instant Client. A forum post I found relating to the error code reccommended that NLS_LANG is not set (possibly Moodle's source refers to an older Oracle Instant Client version?). I commented the following lines out of install.php (at the time of writing they started on line 423):

/// Get client NLS_LANG environment variable
if (strpos(getenv('NLS_LANG'), 'AL32UTF8') === false) { // Oracle client must be correct UTF8
$errormsg = get_string('dbwrongnlslang', 'install', $encoding);
$nextstage = DATABASE;
$INSTALL['dbencodingtestresults'] = false;
}

The code above performs a check on the NLS_LANG setting on the server. If it is not set to 'AL32UTF8' then it will halt the Moodle installation process and display an error asking the user to set NLS_LANG. Because setting NLS_LANG (to anything at all) prevented the connection from working, we had a mutually exclusive situation that prevented us from connecting. We ensured that the database was set to use the UTF8 character set, removed the check above from Moodle's code, and unset NLS_LANG from the environment variables. After doing this, Moodle's installation proceeded normally.

My advice to others would be try first with NLS_LANG set as Moodle's install procedure requests, but if this does not work, then comment the lines out and unset NLS_LANG (remember that setting/unsetting environment variables requires a start/stop of Apache, not merely a restart). If a number of different establishments on various platforms experience this issue, then maybe Moodle's code is at fault, and a bug can be raised and the code altered at source.

Moodle's Database Configuration

Visit the server's address in a web browser and begin Moodle's installation procedure. When the database-selection stage begins enter the name of the database definition in tnsnames.ora (in our case we called it MOODLE), the username and password of the user to connect to the database (provided by your database admin), and finally a prefix for Moodle tables (we chose m_). The host field gets left empty, because the tnsnames.ora file has this information.

Moodle Installs

If everything has worked correctly, Moodle will now install onto the Oracle database. It should continue with creating its tables and fields etc as if it were using a MySQL database.

You should be able (within your Oracle management client) to see all of the new tables, and begin creating links with the Unit-E database. In our case we used triggers such that upon addition/alteration of a student within our Unit-E database, the record was populated to the Moodle database instance. Similarly we manage enrolments this way, membership of groups (thus our groups are automatically created), the courses on offer, and which courses lecturers teach (we have this information via the timetable, stored within Oracle). The creation of these triggers has been the domain of our Oracle database administrator, and so I can't go into great depth about them, but I have verified that they work really well!

As a final benefit, we are investigating Retrieving data from the Moodle database, and storing it in Unit-E, or making it available through other means. Thus we are looking into accessing assessment data from quizzes conducted within Moodle and making this data available in reporting/assessment-monitoring tools that we already use within the College. This will primarily be a task for our database/MIS manager, finding how to collect the data from Moodle's databases, and importing it into Unit-E in a way that suits the College.

A Problem: Authentication

Description of the Problem

Whilst not specifically relevant to Moodle-on-Oracle, we recognised that this Moodle database configuration would cause a problem with authentication (as stated earlier, we are using LDAP, authenticating users against our curriculum IT network).

The problem arises because we are using a 'push' method to create all of the user, course, group (etc) records in Moodle's database in advance, wheras Moodle is written to use a 'pull' method, looking at an external database and creating user/course records as and when users log in. The way this interacts with Moodle's authentication plugins creates a chicken-before-the-egg situation though. This problem is explained in the steps below:

  1. Moodle examines the username typed into the login prompt and tries to find it in the users table of its own database
  2. Moodle attempts the authentication against the chosen authentication system
  3. If the user authenticates, and the user was not previously found in the database (at steps 1 & 2) then a new user account is created within the database with details from the authentication system

The difficulty arises because the Moodle database (created with MIS data) does not know the user's IT network username, thus at step 1 above the user is not found. This would result in a second, duplicate user record being created when the user tries to log in for the first time (because the authentictaion cannot use the existing record).

Solving the Problem

There are two solutions to this that we could see: Add the IT network username data to the Moodle database (by means of an import), or alter Moodle's source a little in order to tie up the LDAP account and the database entry another way.

In the MIS, all people have a unique ID number, placed into the Moodle database in the idnumber field. As it happens, this number is also present within our LDAP directory for all users. Thus a slight change was made to Moodle's code, the following logic replacing step three above:

  1. If the user authenticates, retrieve additionally the ID number field from the LDAP
  2. Search the Moodle database for a user with the ID number field found from LDAP
  3. If a user is found with this ID number, update that user record with their username from LDAP
  4. If no user is found (or any of the above stages generate an error) then proceed and create a new user record

This procedure involved a very small change to moodlelib.php to include a new php file. A new php file was created (including the logic above), and the lib.php within /auth/ldap/ was slightly modified to include retrieving the appropriate idnumber field from LDAP. The final result was that users authenticated in LDAP, logging into Moodle for the first time, will be matched up with their pre-existsing user records in the database.

Web Pages we Found Helpful

Credit, where credit is due

In establishing this link between Unit-E (an Oracle database), and Moodle, we found the following resources very helpful. Many of the instructions on these pages have been adpated into this document: