Student projects/SQLite

Jump to: navigation, search

Note: This page outlines ideas for the XMLDB/SQLite project. It's a specification under construction! If you have any comments or suggestions, please add them to the page comments.

Summary

This project is part of the 2008 edition of Google Summer of Code (GSoC). Mentor: Penny Leach. Student: Andrei Bautu.

The objective of this project is to extend Moodle's database support and features. The main working directions established in the proposal are:

  • implement SQLite in Moodle database abstraction layer;
  • implement a tool which will allow administrators to copy a live Moodle database and switch into 'testing mode' using a SQLite copy of the live database.

SQLite support

Adding support for SQLite in Moodle requires a careful analysis of compatibility between Moodle's database requirements and SQLite's features (e.g. http://docs.moodle.org/en/Development:XMLDB_problems).

SQLite supports almost all of the features of SQL92 (see http://www.sqlite.org/omitted.html). Some areas that need special attention are:

  • proper settings for Unicode support, long field names, numeric/associative fetches, metadata support;
  • incompatible SQL commands (e.g. SQLite does not support MySQL's TRUNCATE because it is not part of standard SQL);
  • unsupported SQL commands (SQLite does not support GRANT/REVOKE);
  • unsupported SQL keywords (SQLite does not support right and full join);
  • security issues (in a misconfigured environment, the SQLite database may be downloaded).

A serious limitation of SQLite is the limited ALTER TABLE command. In this case, the general solution for the missing clauses problem consists in replacing the query with a series of queries that will create a second table to replace the original one. This will be done by the database layer.

Testing mode tool

Using this tool, the live database will be copied to a SQLite testing database. This will allow administrators to experiment in testing mode without performing a backup of the live database or worrying about breaking their live site. The tool will use Moodle's XMLDB to replicate the data from the live database to the test database.

Project timeline

  • April, 14 – May, 25 – develop specification
    • discus required functionality with the mentor;
    • analyze database incompatibilities and possible solutions;
    • collect feedback from the mentor and community.
  • May, 26 – June, 22 – SQLite support
    • implement SQLite support in Moodle database abstraction layer;
    • test and benchmark SQLite support
  • June, 23 – July, 31 – Testing mode tool
    • implement database migration mechanism;
    • provide alternative solutions for non-standard SQL commands in Moodle (if necesary)
  • August, 1 – August, 18 – remove reported bugs
  • September, 3 – submit code and resource files Google

Completion criteria

Moodle will allow users to:

  • work with SQLite databases;
  • use testing mode (i.e. replicate a live database to a SQlite test database)

Moodle installation with SQLite database

Moodle 2.0 offers support for SQLite3 database installations. In this case, no database setup is required. Before starting a Moodle install with a SQLite database be sure to read the Installing Moodle page for general information. Focusing on the Requirements section, you will notice that the pdo and pdo_sqlite extensions are required for the (experimental) SQLite 3 database support.

Using Moodle installer

The SQLite3 database file will be created during the installation process. By default, the database file will be store in Moodle's data directory (see Creating the data directory). During install, the web server must have write access on the directory where the database file will be stored. After installation, the web server must have read-write access to the database file.

The installation process follows the following steps:

  1. Select the language used during the installation process
  2. Check your PHP settings
  3. Confirm the locations of this Moodle installation
    The default SQLite will use the Moodle's data directory for storing of the database file. The SQLite database file also contains a simple protection mechanism that prevents direct downloads of it. However, as emphasised on the Creating the data directory page, you should do your best to protect the Moodle's data directory (and the SQLite database file) against direct web access.
  4. Configure the database
    1. Select the database type (SQLite 3 (PDO))
    2. Fill in the Host Server (optional)
      This setting specifies the directory where the database file will be stored. If it is empty or it is "localhost", then Moodle's data directory will be used. Otherwise, this settings will be used. In order to avoid problems, do not use a path relative to the current working directory (e.g. ../datadir); use instead an absolute path (e.g. /home/abautu/datadir or C:\Inetpub\abautu\datadir).
    3. Fill in a database name, an user and a password (all three are optional)
      The database name, user and password are used to compute the real name of the database file. The database filename is username_database_passwordmd5.sq3.php. If the username is empty, then the username_ prefix is omitted. This prefix allows databases belonging to the same user to be grouped together in a name-ordered directory listing. The passwordmd5 part of the filename offers a simple protection of the password in case the database directory allows web browsing (try to avoid this). The .php filename extension allows the database file to stop direct web downloads in most server setups (don't change it). Notice that the file is still a binary SQLite3 compatible database file. This filenaming scheme allows different users to use the same directory for storing database files, and allows a user to have multiple databases with the same name but different passwords.
    4. Fill in the prefix to use for all table names (optional)
  5. Checking your environment
  6. Download a language pack for your Moodle site
  7. Continue the installation process with the current settings

Manual installation

If you don't want to use Moodle installer, you can manually setup Moodle by following these steps:

  1. Copy your Moodle files into a directory which is web-accesible through your HTTP server.
  2. Create a directory somewhere else for Moodle to store uploaded files in, eg: C:\moodledata. It is recommended that this directory is not web-accesible.
  3. Go into your Moodle folder. Copy the file config-dist.php and rename it it config.php.
  4. Open config.php for editing in a text editor (Notepad will do, just be careful that it doesn't add unwanted spaces at the end).
  5. Change the database info in config.php:
    $CFG->dbtype = 'sqlite3';
    $CFG->dblibrary = 'pdo';
    $CFG->dbhost = 'localhost';// leave dbhost to localhost (or blank) to store the database file in Moodle data directory
    // or fill in an absolute path to store the database file in that directory
    $CFG->dbname = 'moodle'; // leave dbname as it is or fill in another name
    $CFG->dbuser = 'username'; // leave dbuser blank or fill in a username
    $CFG->dbpass = 'password'; // leave dbpass blank or fill in a password
    $CFG->prefix = 'mdl_'; // prefix to use for all table names
  6. Change the file paths in config.php
    $CFG->wwwroot = 'http://localhost/moodle'; // Use an external address if you know it.
    $CFG->dirroot = 'C:\\InetPub\\www\\moodle'; // Use the path to the directory where you copied Moodle on Step 1
    $CFG->dataroot = 'C:\\moodledata'; // Use the path to the directory you created on Step 2
  7. Save config.php - you can ignore the other settings if there are any.

Moodle/SQLite on a stick

You can download the Moodle/SQLite on a stick version from Andrei Bautu's homepage. This package includes Moodle 2.0 dev (Build: 20080814) and Apache/PHP with SQLite. Just unzip and launch run-me.bat. Nothing data is saved in the Windows registry and no files are created outsite the application's folder. So, when your are done testing, just delete the files.

If you have done some work and you would like to keep it, you may do so in one of the following ways:

  • use Moodle's backup/restore features;
  • manually backup the entire moodledata folder;
  • manually backup the moodledata\moodle_d41d8cd98f00b204e9800998ecf8427e.sq3.php database file and the moodledata\filedir and moodledata\user folders;
  • save you database contents in XML format (using Moodle's Miscellaneous/Database transfer tool in administration block) and the moodledata\filedir and moodledata\user folders.

Database migration tool

The database migration tools allows users with administrative privileges (i.e. site:config capability) to export the database content of one Moodle installation into an XML file and import it into another Moodle installation (possible on a different web server, with different database server, but with the same database schema).

Note that the database and XMLDB schemas must match during data export and import and currently the tool does NOT allow you to transfer database content between different databases with different schemas.

The tool is available in the site menu under Experimental/Miscelaneous/DB Transfer entry.

Exporting the database

User view-point

The database export form will only ask the user to provide an optional description of the dump. This information is not used by Moodle, but it can be used later by the user to know what the database dump contains. The export tool will check if the database schema and XMLDB schema are the same. If they do, then the export process starts and the user will receive an XML file with the database content.

Developer view-point

After receiving the database description from the user, the exportdb.php file calls the dbtransfer_export_xml_database function, which retrieves the XMLDB schema with database_manager::get_install_xml_schema static method and compares it against the database schema with the database_manager::check_database_schema method. If there are no differences to report, then the database contente is exported using the file_xml_database_exporter::export_database method and send to the user as a application/xhtml+xml stream.

Importing the database

User view-point

The database import form will ask the user to upload the datafile containing a valid XML dump of the database. The import tool will check if the database schema and XMLDB schema are the same. If they do, then the import process starts and the user will be redirected to the homepage of the Moodle instalation. Note that the homepage might look different after the database import (since Moodle settings will be imported from the database) and the user accounts/passwords might be different (since the user accounts information will be imported from the database).

Developer view-point

After receiving the database XML file from the user, the importdb.php file calls the dbtransfer_import_xml_database function, which retrieves the XMLDB schema with database_manager::get_install_xml_schema static method and compares it against the database schema with the database_manager::check_database_schema method. If there are no differences to report, then the database contente is imported using the file_xml_database_importer::import_database method. During the import of each table, the database/XMLDB schemaHash value of that table is checked against the schemaHash value recorded in the XML data file.

See also