iRefIndex Data Preparation for iRefWeb

A special iRefWeb database needs to be created and populated so that iRefWeb may publish iRefIndex data.

Creating a Database

Enter MySQL using a command like the following:

mysql -h <host> -u <admin> -p -A

The <admin> is the name of the user with administrative privileges. For example:

mysql -h myhost -u admin -p -A

Then create a database using commands of the following form:

create database <database>;
grant all privileges on <database>.* to '<username>'@'%';

For example, with <database> given as iRefWeb, <username> given as irefindex, and a substitution for <password>:

create database iRefWeb;
grant all privileges on iRefWeb.* to 'irefindex'@'%';

If difficulties occur granting privileges in this way, try the following statements:

grant select, insert, update, delete, create, drop, references, index, alter, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine on <database>.* to '<username>'@'%';
grant process, file on *.* to '<username>'@'%';

You should choose the same user as the one used to build the iRefIndex database since that database will need to be accessed during this activity.

Obtaining the SQL Scripts

Get the scripts from this location:

• https://hfaistos.uio.no/cgi-bin/viewvc.cgi/bioscape/bioscape/modules/interaction/Sabry/SQL_commands/

Using CVS with the appropriate CVSROOT setting, run the following command:

cvs co bioscape/bioscape/modules/interaction/Sabry/SQL_commands

The CVSROOT environment variable should be set to the following for this to work:

export CVSROOT=:ext:<username>@hfaistos.uio.no:/mn/hfaistos/storage/cvsroot

(The <username> should be replaced with your actual username.)

Preparing the SQL Scripts

The make_iRefWeb.sql script needs to be parameterised to refer to specific databases. The following command should prepare a suitable version of the script given appropriate values for <actual_irefindex_db> and <actual_irefweb_db>:

sed -e 's/<irefindex_db>/<actual_irefindex_db>/g;s/<old_irefweb_db>/<actual_irefweb_db>/g' make_iRefWeb.sql > make_iRefWeb_specific.sql

Running the SQL Scripts

In the SQL_commands directory, two scripts - preprocess_for_iRefWeb.sql and make_iRefWeb.sql - together provide a large number of SQL statements for the creation of iRefWeb data. The first of these scripts should be run as follows and any error conditions noted:

mysql -h <hostname> -u <username> -p -A -D <database> < preprocess_for_iRefWeb.sql

The prepared version of the second script can then be run if no errors were experienced:

mysql -h <hostname> -u <username> -p -A -D <database> < make_iRefWeb_specific.sql