Sakai 1.5.0

Using a Database

Sakai Installation

Sakai Download

Sakai Documentation

Developing with Sakai

Database Drivers

Pre-Requisites: Successfull QuickStart Installation

Before reconfiguring your QuickStart for production use, it should have installed and ran successfully as a QuickStart installation. This process assumes a fully operational QuickStart.

Note: This process is intended for a small to medium sized installation using one application server and a MySQL database. If the intent is to run a large installation with multiple application servers and/or a large Oracle database, then it is best to work with the source, and deploy from the source rather than using QuickStart for production.

Changing the Tomcat Configuration
You may change the Tomcat configuration stored in jakarta-tomcat-5.0.28/conf. You can move Tomcat to another port, configure the JK Connection to use with Apache or other stock Tomcat configurations.

Once you are using this in production, you should not use the Sakai provided start and stop scripts and instead use the Tomcat scripts in jakarta-tomcat-5.0.28/bin.

Reconfiguring QuickStart For MySQL
This section describes the necessary setup and configuration changes for connecting Sakai QuickStart to a MySQL database. Make sure QuickStart is working before attempting to connect it to a MySQL database. If the intention is to run Sakai in a production environment, don't use Sakai QuickStart; it is best to download and deploy Sakai from source (as described in the developer documentation) rather than using Sakai QuickStart.
1. Install MySQL 4.1+
Download and install MySQL 4.1+. Sakai does not support older MySQL versions. The default installation settings are fine.
2. Install MySQL JDBC driver
Copy the MySQL JDBC driver jar file into the /common/lib directory of the Tomcat instance which is (quickstart)/sakai/jakarta-tomcat-5.0.28/common/lib.
3. Setup database and tables

Setup the database and tables in MySQL. This involves creating a MySQL database, user, and password for Sakai. The MySQL tables can be created and populated using the scripts in (quickstart)/sakai/confdb/db/mysql. The mysql commands are the same for UNIX or Windows.

For example, here is the transcript of creating the database and tables on Windows:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\> cd C:\sakai\localfiles\confdb\db\mysql
C:\sakai\localfiles\confdb\db\mysql\> mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51 to server version: 4.1.5-gamma-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database sakai default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on sakai.* to sakaiuser@'localhost' identified by 'sakaipassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on sakai.* to sakaiuser@'127.0.0.1' identified by 'sakaipassword';
Query OK, 0 rows affected (0.00 sec)

mysql> quit

The file all.sql contains the Sakai table definitions:

C:\sakai\localfiles\confdb\db\mysql\> mysql --user=sakaiuser --password=sakaipassword sakai < all.sql
Field Type Null Key Default Extra
CHANNEL_ID varchar(99) PRI
NEXT_ID int(11) YES NULL
.
.
.
.
CREATEDON datetime 0000-00-00 00:00:00
MODIFIEDON timestamp YES CURRENT_TIMESTAMP

C:\sakai\localfiles\confdb\db\mysql\>

4. Configure Sakai
Configure Sakai to communicate with MySQL. Enable the MySQL parameters in (quickstart)/sakai/localfiles/sakai.properties:

# MySQL database settings
sql.vendor=mysql
sql.driver=com.mysql.jdbc.Driver
sql.connect=jdbc:mysql://localhost:3306/sakai?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8
sql.user=sakaiuser
sql.pw=sakaipassword

5. Additional optional MySQL options
You may want to increase the MySQL maximum packet size. This is necessary to enable users to upload large multi-megabyte files into Sakai. You can edit the MySQL configuration file my.cnf:

[mysqld]

max_allowed_packet=31M

You may want to set the default character set of the MySQL instance to be UTF-8. You may also want to set the MySQL timezone to GMT, although this is not strictly necessary.
6. Start Sakai
Start Sakai by running "start.bat mysql" (Windows) or "start.sh mysql" (Unix/Linux/OSX).
Sample Output