View source for 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:

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

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

<pre>mysql -h myhost -u admin -p -A</pre>

Then create a database using commands of the following form:

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

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

<pre>
create database iRefWeb;
grant all privileges on iRefWeb.* to 'irefindex'@'%';
</pre>

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

<pre>
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>'@'%';
</pre>

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 ==

{{Note|
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:

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

== Obtaining the SQL Scripts ==

Get the scripts from this location:

<ul>
<li>https://hfaistos.uio.no/cgi-bin/viewvc.cgi/bioscape/bioscape/modules/interaction/Sabry/SQL_commands/</li>
</ul>

Using CVS with the appropriate <tt>CVSROOT</tt> setting, run the following command:

<pre>cvs co bioscape/bioscape/modules/interaction/Sabry/SQL_commands</pre>

The <tt>CVSROOT</tt> environment variable should be set to the following for this to work:

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

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

== Updating the SQL Scripts ==

The <tt>make_iRefWeb.sql</tt> script needs to be updated so that each statement defining a source in the <tt>source_db</tt> table refers to the appropriate release version and date.

== Preparing the SQL Scripts ==

The <tt>make_iRefWeb.sql</tt> and <tt>test_iRefWeb.sql</tt> scripts needs to be parameterised to refer to specific databases. The following commands should prepare a suitable version of the scripts given appropriate values for <tt>&lt;actual_irefindex_db&gt;</tt>, <tt>&lt;actual_old_irefweb_db&gt;</tt> and <tt>&lt;actual_irefweb_db&gt;</tt>:

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

For example, given the following substitutions...

{| border="1" cellspacing="0" cellpadding="5"
! Parameter
! Substitution
! Value
|-
| &lt;irefindex_db&gt;
| &lt;actual_irefindex_db&gt;
| iRefIndex_full_beta9
|-
| &lt;old_irefweb_db&gt;
| &lt;actual_old_irefweb_db&gt;
| iRefWeb8
|-
| &lt;irefweb_db&gt;
| &lt;actual_irefweb_db&gt;
| iRefWeb9
|}

...the commands would be as follows:

 sed -e 's/<irefindex_db>/iRefIndex_full_beta9/g;s/<old_irefweb_db>/iRefWeb8/g' make_iRefWeb.sql > make_iRefWeb_specific.sql
 sed -e 's/<irefweb_db>/iRefWeb9/g;s/<old_irefweb_db>/iRefWeb8/g' test_iRefWeb.sql > test_iRefWeb_specific.sql

== Running the SQL Scripts ==

In the <tt>SQL_commands</tt> directory, two scripts - <tt>preprocess_for_iRefWeb.sql</tt> and <tt>make_iRefWeb.sql</tt> - 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, <tt>&lt;irefweb_database&gt;</tt> refers to the current iRefWeb database created above, whereas <tt>&lt;irefindex_database&gt;</tt> refers to the current iRefIndex database.

The built database can be tested by running the following:

 mysql -h <hostname> -u <username> -p -A -D <irefweb_database> < test_iRefWeb_specific.sql

This will output a number of figures corresponding to changes in the number of interactions and interactors. Large deviations from the typical scale of change may indicate a problem in the generation or assignment of identifiers.

== Generating the Database Dump ==

A database dump can be produced by running a command of the following form:

 ./dump_iRefWeb.sh <hostname> <username> <database>

== Mapping RIGIDs ==

For iRefIndex 9.0, a table will be made available mapping the RIGIDs used in that release (and subsequent releases) to "legacy" RIGIDs used in earlier releases of iRefIndex. This can be produced by running the following SQL command in the iRefIndex database:

 select sourceid, experiment_uid, Represent_rigid, Represent_rogid
 from join_2
 order by sourceid, experiment_uid
 into outfile '/tmp/output/iRefIndex_interaction_interactors';

This output file can then be processed using the <tt>make_legacy_mapping.py</tt> program:

 python make_legacy_mapping.py /tmp/output/iRefIndex_interaction_interactors /tmp/output/iRefIndex_interaction_rigids

(The program needs Python 2.5 or later since it uses the <tt>hashlib</tt> module. It could probably be made compatible with earlier Python versions if necessary.)

The resulting output file (<tt>/tmp/iRefIndex_interaction_rigids</tt> in the above example) is a tab-separated file with correct RIGIDs in the first column and "legacy" RIGIDs in the second column. Since the output may include duplicate lines, the following step is advised:

 sort -u /tmp/output/iRefIndex_interaction_rigids > /tmp/iRefIndex_interaction_rigids

A header can also be included in the file:

 cat header_legacy_mapping.txt /tmp/iRefIndex_interaction_rigids > iRefIndex_interaction_rigids

== All iRefIndex Pages ==

Follow this link for a listing of all iRefIndex related pages (archived and current).

[[Category:iRefIndex]]