Database Layouts

This document describes the concept of a database layout, introduced in WeBWorK 2.

A little background

Each WeBWorK course uses a database to keep track of course data. Within each course's database, there are several lists, or data tables, that contain different types of data. They are as follows:

table	used for
user	Classlist data (user names, email addresses, student IDs, etc.)
password	User passwords
permission	User permission levels
key	User session keys (keep track of logged-in users)
set	Problem sets defined in the course (name, open date ,due date, etc.)
problem	Problems in each problem set (file name, weight, number of tries, etc.)
set_user	User-specific set data for each user. (used primarily to override due dates)
problem_user	User-specific problem data for each user. (each user's score, etc.)

When work on WeBWorK 2 began, the designers wanted schools to be able to continue to use existing WeBWorK 1 courses under WeBWorK 2, and still be able to go back and use those same courses under WeBWorK 1.

WeBWorK 1 uses GDBM databases for storing course data. While simple, they are not particularly scalable, and the WeBWorK designers wanted to give schools the capability of using modern relational database engines like MySQL to store course data for new courses.

So WeBWorK 2 needed to be able to represent course data in two very different ways. The database layout infrastructure is an abstraction layer that allows it to do just that.

The database layout

A database layout tells WeBWorK how to find the course data for each data table in the course.

The database layout for a course that must be backwards compatible with WeBWorK 1 would instruct WeBWorK to use GDBM database files, stored in the course directory, for all of the course's data tables.

The database layout for a course that will only be used under WeBWorK 2 would instruct WeBWorK to use tables in an SQL database for all of the course's data tables.

WeBWorK ships with several predefined database layouts that provide for the situations detailed above. The only action you have to take when installing WeBWorK is to select and set a default database layout.

Available database layouts

The following database layouts are predefined in the WeBWorK configuration file database.conf.

gdbm (deprecated in WeBWorK 2.1, removed in WeBWorK 2.2)

The gdbm database layout maps all course data tables to GDBM database files in the course directory. It is suitable for courses created with WeBWorK 1 and courses which must be backwards compatible with WeBWorK 1. GDBM databases are less scalable than SQL databases, so this layout should not be used for courses that don't require backwards compatibility.

The use of GDBM databases is deprecated and the gdbm database layout will be removed in a future version of WeBWorK.

sql (deprecated in WeBWorK 2.1, removed in WeBWorK 2.2)

The sql database layout maps all course data tables to tables in an SQL database. Courses using this layout cannot be used with WeBWorK 1. Each course that uses this layout will have a separate SQL database.

Using this database layout requires that you are running a MySQL server and have an account on that server with CREATE and FILE privileges and WITH GRANT OPTION. The MySQL root account will work, or you can create a new user that only has these privileges. Information on The MySQL Access Privilege System and GRANT and REVOKE Syntax, consult the MySQL Manual.

For more information about managing courses using the sql layout, see the CourseAdministrationManual.

This is the recommended database layout for courses created under WeBWorK 2.0. However, it is deprecated since WeBWorK 2.1 in favor of the sql_single layout.

sql_single (new in WeBWorK 2.1)

The sql_single database is similar to the sql layout in that it maps all course data tables to tables in an SQL database. However, this layout allows multiple courses to share a single database, streamlining course creation and deletion and making database access more efficient.

Using this database layout requires that you are running an SQL server. You must also create a database on that server (usually called webwork) to contain the data tables for courses using this database layout. This is covered in InstallationManualV2pt1.

This is the recommended database layout for courses created under WeBWorK 2.1. Existing sql courses can be converted to use this layout. See Course Management for details.

sql_moodle (new in WeBWorK 2.3)

The sql_moodle layout is a modified version of the sql_single layout that uses a Moodle database for authentication and user info .(Strictly, the user, password, and permission tables.)

Selecting a default database layout

The default database layout is set in the WeBWorK configuration file global.conf. Which layout you choose depends on your situation. No matter what default database layout you choose, you will be able to explicitly set the database layout of each course you create. The default database layout is only used to provide a default selection for new courses.

If you are using WeBWorK 2.0, use sql. If you are using WeBWorK 2.1 or later, select sql_single.

Setting a default database layout

The default database layout is controlled by the $dbLayoutName variable in the "Database options" section of global.conf. Here is the relevant section as it ships:

# Several database are defined in the file conf/database.conf and stored in the
# hash %dbLayouts.
include "conf/database.conf";

# Select the default database layout. This can be overridden in the course.conf
# file of a particular course. The only database layout supported in WW 2.1.4
# and up is "sql_single".
$dbLayoutName = "sql_single";

# This sets the symbol "dbLayout" as an alias for the selected database layout.
*dbLayout     = $dbLayouts{$dbLayoutName};

To change the default database layout, simply replace sql_single with the name of another database layout.