This document describes how to set up a database for the CLRA web application.
PREREQUISITE: Initial setup for the CLRA web application.
This document assumes that an environment variable $CLRA_HOME has been set up which corresponds to the clra/clra-java directory, so that source code resides in $CLRA_HOME/src; web pages in $CLRA_HOME/web; shell scripts in $CLRA_HOME/bin; and so on.
The CLRA web application uses either the Oracle or MySQL database. The Oracle database is useful during development, because it allows one to constrain data more tightly than MySQL. Tight data constraints allow application flaws to be detected more quickly. The MySQL database is used for production because it carries no licensing fees for non-profit organizations.
As configured in CVS, the CLRA web application is parameterized for MySQL operation. This document focuses on MySQL configuration. Going forward, a TODO for the project is to document the Oracle configuration. If you'd like to configure the CLRA web application for Oracle, please send a request for support to the project web site on SourceForge.
The basic steps for MySQL configuration are 1) installation of the database software; 2) generation of the MySQL database scripts; 3) configuration of the MySQL database, users, and tables; and 4) optional importation of test data.
Back to TopDownload MySQL from www.mysql.com.
The CLRA web application has been tested under version 3.23.46a with the standard ISAM tables (there's no need to download the 'max' version with transactional support).
Install per directions on the MySQL website. For convenience, create a system environment variable called MYSQL_HOME to point to the installation directory. The $MYSQL_HOME/bin directory does not need to be part of one's PATH variable.
Back to TopAs mentioned above, the CLRA web application is developed against an Oracle database. Whereever possible, configuration scripts are written in Oracle-compatible SQL. Oracle configuration scripts are physically located in the etc/sql-oracle directory.
Most MySQL configuration scripts are generated from corresponding Oracle scripts by applying an AWK translation program. The shell script $CLRA_HOME/bin/oracle-to-mysql.sh applies the AWK program to scripts in the $CLRA_HOME/etc/sql-oracle directory and stores the translated scripts in $CLRA_HOME/etc/sql-mysql directory.
(Because of differences between Oracle and MySQL syntax, five MySQL configuration scripts must maintained separately. Two of these five, test_rowingsession.sql and test_participantsignup.sql, are actually generated from Java programs, src/test/clra/rowing/CreateRowingSessions.java and src/test/clra/rowing/CreateParticipants.java, respectively, and then archived in CVS. The CreateRowingSessions.java and CreateParticipants.java programs must be run yearly, since test data for rowing sessions and participants is time dependent.)
To run the Oracle-to-MySQL translation program, change to the script directory and invoke the oracle-to-mysql.sh shell script:
$ cd $CLRA_HOME/bin $ ./oracle-to-mysql.sh Wed Feb 20 10:32:46 EST 2002 Converting Oracle scripts to mySQL... ...Conversion complete Wed Feb 20 10:32:46 EST 2002Back to Top
Run the command-line tool $MYSQL_HOME/bin as root and set the password of the root administrator:
$ $MYSQL_HOME/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.46-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET PASSWORD FOR root@"%" = PASSWORD('your_choice');
Query OK, 0 rows affected (0.11 sec)
mysql> SET PASSWORD FOR root@"localhost" = PASSWORD('your_choice');
Query OK, 0 rows affected (0.11 sec)
Create the "clra" database and exit mySQL:
mysql> create database clra; Query OK, 1 row affected (0.15 sec) mysql> exitUse the script
$CLRA_HOME/etc/sql-mysql/clra_ddl.sql to create the clra tables.
$ $MYSQL_HOME/bin/mysql -u root -p clra < $CLRA_HOME/etc/sql-mysql/clra_ddl.sql Enter password: ****
Create a user "muze" with the password "muz3" and the SELECT, INSERT, UPDATE and DELETE privileges on the clra database. (NOTE: make sure any production database defines different users and passwords.):
$ $MYSQL_HOME/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.46-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE
-> ON clra.*
-> TO muze@'%'
-> IDENTIFIED BY 'muz3';
Query OK, 0 rows affected (0.15 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE
-> ON clra.*
-> TO muze@'localhost'
-> IDENTIFIED BY 'muz3';
Query OK, 0 rows affected (0.15 sec)
mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------+--------+------------------+
| host | user | password |
+-----------+--------+------------------+
| localhost | root | 123456789abcdef1 |
| % | muze | 402d60942272b768 |
| localhost | muze | 402d60942272b768 |
+-----------+--------+------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
$ $MYSQL_HOME/bin/mysql -u muze -pmuz3 clra
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 3.23.46-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
+----------------+
| Tables_in_clra |
+----------------+
| Boat |
| Boating |
| ClraSequence |
| Member |
| MemberRole |
| Oarset |
| Participant |
| RowingSession |
| Seat |
| SessionCoach |
+----------------+
10 rows in set (0.00 sec)
mysql> exit
Bye
Back to Top
This step should be skipped for a production database. For development and testing purposes, import some data into the clra tables using the $CLRA_HOME/bin/sql_setup-mysql.sh script. This script runs under the MySQL root account.
$ cd $CLRA_HOME/bin $ ./sql_setup-mysql.sh Wed Feb 20 11:26:40 EST 2002 Generating mySQL scripts from Oracle scripts ... Wed Feb 20 11:26:40 EST 2002 Removing derived mySQL scripts ... ...Cleanup complete Wed Feb 20 11:26:40 EST 2002 Wed Feb 20 11:26:40 EST 2002 Converting Oracle scripts to mySQL... awk: oracle-to-mysql.awk:28: fatal: cannot open file `../etc/sql-oracle/test_security.sql' for reading (No such file or directory) ...Conversion complete Wed Feb 20 11:26:40 EST 2002 Executing setup.sql ... Enter password: **** Member 309 MemberRole 318 SessionCoach 0 RowingSession 465 Boat 11 Oarset 5 Boating 0 Seat 0 Participant 11806 setup.sql complete Wed Feb 20 11:26:51 EST 2002The script completely reinitializes the CLRA database and the MySQL scripts, then recreates CLRA tables and imports test data. The script finishes by printing out the number of rows in each CLRA table. The values shown are representative for the current version of the source code (as of Feb. 20, 2002). In particular, the Participant, RowingSession, Member, and MemberRole tables should have non-zero row counts if test data has been successfully imported. Back to Top
Now that the CLRA software has been unpacked and the database has been configured, the next step is to configure the JBoss Java application server to run the CLRA web application. JBoss configuration is covered in a separate document, JBoss Setup.