iRefIndex Data Preparation for iRefWeb

From irefindex
Revision as of 10:41, 20 July 2011 by PaulBoddie (talk | contribs) (→‎Running the SQL Scripts: Corrected database usage.)

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.

Accessing the Previous iRefWeb Database


This should probably be changed so that the process of building an iRefWeb database depends only on the iRefIndex database.

In order to populate the iRefWeb database, the previous version of the database needs to be referenced. Thus, a command of the following form may be required:

grant select on <old_irefweb_db>.* to '<username>'@'%';

Obtaining the SQL Scripts

Get the scripts from this location:

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>

(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 <irefindex_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 <irefweb_database> < make_iRefWeb_specific.sql

Here, <irefweb_database> refers to the iRefWeb database created earlier, whereas <irefindex_database> refers to the current iRefIndex database.