#!perl
################################################################################
# WeBWorK Online Homework Delivery System
# Copyright © 2000-2003 The WeBWorK Project, http://openwebwork.sf.net/
# $CVSHeader: webwork-modperl/conf/database.conf.dist,v 1.1 2004/01/03 20:07:02 sh002i Exp $
# 
# This program is free software; you can redistribute it and/or modify it under
# the terms of either: (a) the GNU General Public License as published by the
# Free Software Foundation; either version 2, or (at your option) any later
# version, or (b) the "Artistic License" which comes with this package.
# 
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE.  See either the GNU General Public License or the
# Artistic License for more details.
################################################################################

=head1 NAME

database.conf - define stantad database layouts

=head1 SYNOPSIS

In global.conf:

 include "conf/database.conf";
 *dbLayout = $dbLayouts{layoutName};

=head1 DESCRIPTION

This file contains definitions for the commonly-used database layouts. Database
layouts consist of all the information necessary to describe how to access data
used by WeBWorK. For more information on the format of a database layout,
consult the documentation for the WeBWorK::DB module.

A database layout is selected from the list of possible layouts by adding a
line like the one below to the F<global.conf> or F<course.conf> file.

 *dbLayout = $dbLayouts{layoutName};

=cut

%dbLayouts = (); # layouts are added to this hash below

=head1 THE SQL DATABASE LAYOUT ($dbLayouts{sql})

The SQL database layout uses an SQL database server to store the tables of the
WeBWorK database. The SQL driver uses two SQL accounts, a read only and a
read-write account. The read-only account must have C<SELECT> access, and the
read-write account needs C<SELECT>, C<INSERT>, C<UPDATE>, and C<DELETE> access.
The names and passwords of these accounts are given as parameters to each table
in the layout.

 usernameRO     the name of the read-only account
 usernameRW     the name of the read-write account
 passwordRO     the password for the read-only account
 passwordRW     the password for the read-write account

Be default, the name of the read-only account is "webworkRead", and the name of
the read-write account is "webworkWrite". There are no passwords by default. 
It is not recommended that you use only passwords to secure database access.
Most RDBMSs allow IP-based authorization as well. As the system administrator,
IT IS YOUR RESPONSIBILITY TO SECURE DATABASE ACCESS.

Don't confuse the accounts above with the accounts of the users of a course.
These are system-wide accounts which allow WeBWorK to talk to the database
server.

Other parameters that can be given are as follows:

 tableOverride  an alternate name to use when referrring to the table (used
                when a table name is a resereved word)
 fieldOverride  a hash mapping WeBWorK field names to alternate names to use
                when referring to those fields (used when one or more field
				names are reserved words)
 debug          if true, SQL statments are printed before being executed

=cut

my %sqlParams = (
	usernameRO => "webworkRead",
	passwordRO => "",
	usernameRW => "webworkWrite",
	passwordRW => "",
	debug      => 0,
);

$dbLayouts{sql} = {
	password => {
		record => "WeBWorK::DB::Record::Password",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => { %sqlParams },
	},
	permission => {
		record => "WeBWorK::DB::Record::PermissionLevel",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => { %sqlParams },
	},
	key => {
		record => "WeBWorK::DB::Record::Key",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => {
			%sqlParams,
			tableOverride => "key_not_a_keyword",
			fieldOverride => { key => "key_not_a_keyword" },
		},
	},
	user => {
		record => "WeBWorK::DB::Record::User",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => { %sqlParams },
	},
	set => {
		record => "WeBWorK::DB::Record::Set",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => {
			%sqlParams,
			tableOverride => "set_not_a_keyword"
		},
	},
	set_user => {
		record => "WeBWorK::DB::Record::UserSet",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => { %sqlParams },
	},
	problem => {
		record => "WeBWorK::DB::Record::Problem",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => { %sqlParams },
	},
	problem_user => {
		record => "WeBWorK::DB::Record::UserProblem",
		schema => "WeBWorK::DB::Schema::SQL",
		driver => "WeBWorK::DB::Driver::SQL",
		source => "dbi:mysql:webwork_$courseName",
		params => { %sqlParams },
	},
};

=head1 THE GDBM DATABASE LAYOUT ($dbLayouts{gdbm})

This layout uses the traditional WeBWorK 1.x database format, using the GDBM
file format. Use this layout if you wish to share courses between WeBWorK 1.x
and WeBWorK 2.

The C<globalUserID> parameter given for the C<set> and C<problem> tables
denotes the ID of the user that the GlobalTableEmulator will use to store data
for the C<set> and C<problem> tables. This is set to "professor" by default,
but should be overridden on a course-by-course basis to the ID of the professor
who is most likely to be involved in creating new problem sets. Sets which have
not been assigned will only be visible to this user when logging into WeBWorK
1.x.

Use the following code to override these values in a course's F<course.conf>
file:

 $dbLayout{set}->{params}->{globalUserID} = "some_user";
 $dbLayout{problem}->{params}->{globalUserID} = "some_user";

=cut

my %gdbmGlobalTableParams = (
	globalUserID => "professor";
);

my %gdbmUserSpecificTableParams = (
	psvnLength => 5;
);

$dbLayouts{gdbm} = {
	password => {
		record => "WeBWorK::DB::Record::Password",
		schema => "WeBWorK::DB::Schema::Auth1Hash",
		driver => "WeBWorK::DB::Driver::GDBM",
		source => "$courseDirs{auth_DATA}/$courseName\_password_DB",
	},
	permission => {
		record => "WeBWorK::DB::Record::PermissionLevel",
		schema => "WeBWorK::DB::Schema::Auth1Hash",
		driver => "WeBWorK::DB::Driver::GDBM",
		source => "$courseDirs{auth_DATA}/$courseName\_permissions_DB",
	},
	key => {
		record => "WeBWorK::DB::Record::Key",
		schema => "WeBWorK::DB::Schema::Auth1Hash",
		driver => "WeBWorK::DB::Driver::GDBM",
		source => "$courseDirs{auth_DATA}/keys",
	},
	user => {
		record => "WeBWorK::DB::Record::User",
		schema => "WeBWorK::DB::Schema::Classlist1Hash",
		driver => "WeBWorK::DB::Driver::GDBM",
		source => "$courseDirs{DATA}/$courseName\_classlist_DB",
	},
	set => {
		record => "WeBWorK::DB::Record::Set",
		schema => "WeBWorK::DB::Schema::GlobalTableEmulator",
		driver => "WeBWorK::DB::Driver::Null",
		params => { %gdbmGlobalTableParams },
	},
	set_user => {
		record => "WeBWorK::DB::Record::UserSet",
		schema => "WeBWorK::DB::Schema::WW1Hash",
		driver => "WeBWorK::DB::Driver::GDBM",
		source => "$courseDirs{DATA}/$courseName\_webwork_DB",
		params => { %gdbmUserSpecificTableParams },
	},
	problem => {
		record => "WeBWorK::DB::Record::Problem",
		schema => "WeBWorK::DB::Schema::GlobalTableEmulator",
		driver => "WeBWorK::DB::Driver::Null",
		params => { %gdbmGlobalTableParams },
	},
	problem_user => {
		record => "WeBWorK::DB::Record::UserProblem",
		schema => "WeBWorK::DB::Schema::WW1Hash",
		driver => "WeBWorK::DB::Driver::GDBM",
		source => "$courseDirs{DATA}/$courseName\_webwork_DB",
		params => { %gdbmUserSpecificTableParams },
	},
};

__END__

Here's an idea for a dblayout improvement:

$dbLayouts{layoutName} = {
	recordOpts => { options passed to the record class for all tables },
	schemaOpts => { options passed to the schema class for all tables },
	driverOpts => { options passed to the driver class for all tables },
	tables => {
		table_name => {
			record => "",
			schema => "",
			driver => "",
			recordOpts => { options passed to the record class for this table },
			schemaOpts => { options passed to the schema class for this table },
			driverOpts => { options passed to the driver class for this table },
		}
	}
};

Code to create an SQL course database:

CREATE DATABASE webwork_$courseName;
USE webwork_$courseName;
CREATE TABLE user ( user_id VARCHAR(255) NOT NULL PRIMARY KEY, first_name TEXT,
	last_name TEXT, email_address TEXT, student_id TEXT, status TEXT,
	section TEXT, recitation TEXT, comment TEXT );
CREATE TABLE password ( user_id VARCHAR(255) NOT NULL PRIMARY KEY,
	password TEXT );
CREATE TABLE permission ( user_id VARCHAR(255) NOT NULL PRIMARY KEY,
	permission INT );
CREATE TABLE key_not_a_keyword ( user_id VARCHAR(255) NOT NULL PRIMARY KEY,
	key_not_a_keyword TEXT, timestamp INT );
CREATE TABLE set_not_a_keyword ( set_id VARCHAR(255) NOT NULL PRIMARY KEY,
	set_header TEXT, problem_header TEXT, open_date INT, due_date INT,
	answer_date INT );
CREATE TABLE set_user ( user_id VARCHAR(255) NOT NULL,
	set_id VARCHAR(255) NOT NULL, psvn INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	set_header TEXT, problem_header TEXT, open_date INT, due_date INT,
	answer_date INT );
CREATE TABLE problem ( set_id VARCHAR(255) NOT NULL,
	problem_id VARCHAR(255) NOT NULL, source_file TEXT, value INT,
	max_attempts INT );
CREATE TABLE problem_user ( user_id VARCHAR(255) NOT NULL,
	set_id VARCHAR(255) NOT NULL, problem_id VARCHAR(255) NOT NULL,
	source_file TEXT, value INT, max_attempts INT, problem_seed INT,
	status FLOAT, attempted INT, last_answer TEXT, num_correct INT,
	num_incorrect INT );
GRANT select ON webwork_$courseName.* TO $usernameRO\@localhost
	IDENTIFIED BY '$passwordRO';
GRANT select, insert, update, delete ON webwork_$courseName.*
	TO $usernameRW\@localhost IDENTIFIED BY '$passwordRW';