Notes from my installation

[Miskell, Craig]

Hi,
	Following are the cleaned up notes I took while installing
Gramene release 13.  These are things I had to do in addition to that
which was in the instructions, and that I'm pretty sure would be
required for anyone else doing the installation.  There are no doubt
some fixes I performed which are sub-optimal, or just plain wrong - if
anyone can point those out, along with the "correct" solution, I'd be
most appreciative.  As for now, Gramene appears to be up and running
properly, at least as far as I, a simple computer geek, can tell.  I'll
have to wait for a while for the bioinformatics guys to check it out for
correct operation, so there may still be things I've missed.   Also, the
below assumes an install in /usr/local/gramene.  Modify for your
particular location

My thanks to Ken Youens-Clark and Steven Schmidt for helping me match
data dumps with schemas.  Notes follow:

****************************************

Regarding the website instructions :Line numbers/descriptions didn't
always match the current version.  I  had to guess sometimes, although
it was usually pretty obvious what to change by the context.  

Suggested user creation sql:
create user riceseq identified by riceseq default tablespace
<APPROPRIATE LOCAL VALUE>;
grant connect to riceseq;
grant create table to riceseq;
grant create sequence to riceseq;
grant create trigger to riceseq;
grant create procedure to riceseq;
grant unlimited tablespace to riceseq;
grant query rewrite to riceseq;

Not sure if the rest of the users need all these privs, but I was tired
by then so I gave them everything that riceseq had.  I'm pretty sure
they don't need query rewrite, but hey, it doesn't hurt.
create user phenotype identified by phenotype default tablespace
<APPROPRIATE LOCAL VALUE>;
grant connect to phenotype;
grant create table to phenotype;
grant create sequence to phenotype;
grant create trigger to phenotype;
grant create procedure to phenotype;
grant unlimited tablespace to phenotype;
grant query rewrite to phenotype;

create user ontology identified by ontology default tablespace
<APPROPRIATE LOCAL VALUE>;
grant connect to ontology;
grant create table to ontology;
grant create sequence to ontology;
grant create trigger to ontology;
grant create procedure to ontology;
grant unlimited tablespace to ontology;
grant query rewrite to ontology;


create user cmap identified by cmap default tablespace <APPROPRIATE
LOCAL VALUE>;
grant connect to cmap;
grant create table to cmap;
grant create sequence to cmap;
grant create trigger to cmap;
grant create procedure to cmap;
grant unlimited tablespace to cmap;
grant query rewrite to cmap;

create user ricelite identified by ricelite default tablespace
<APPROPRIATE LOCAL VALUE>;;
grant connect to ricelite;
grant create table to ricelite;
grant create sequence to ricelite;
grant create trigger to ricelite;
grant create procedure to ricelite;
grant unlimited tablespace to ricelite;

Do these grants, in addition to the grants specified in the
documentation.

grant select on cmap.cmap_feature to phenotype;
grant select on cmap.cmap_feature_alias to phenotype;
grant select on cmap.cmap_map to phenotype;
grant select on cmap.cmap_map_set to phenotype;
after creating cmap, but before creating the phenotype schema


Import modifications:

Added following to ricelite.sql:
at start:
***************
CREATE OR REPLACE PACKAGE mysql AS
last_insert_id NUMBER(11);
END;
/
***************
At end:
***************
CREATE TABLE ensembl_gene_helper(gene_id number(11),
description varchar2(2000));

create index ensembl_gh_gene on ensembl_gene_helper(gene_id);

create index ensembl_gh_desc on ensembl_gene_helper(description);
***************
NB: the ensembl_gene_helper stuff was ripped from the perl script
lib/ensembl-lite/scripts/make_gene_helper.pl.  I didn't run this script
because I had data to import, whereas the script appears to generate
data for the table.  That may have been the wrong decision
Also modified table "REPEAT" definition - ripped from an old version of
ora_anopheles_gambiae_lite_11_2.sql (needed varchars instead of  numbers
for some columns)
Also modified table gene_xref definition - removed the not null
constraints (there is null data in the import), as per the way
INITIALIZE_LITE.pl would have create the table.
Maybe I should have run that script afterall?

Import the schemas.  Note that in addition to the other sql files, you
need to 
	1) import ricelite (.sql and .tgz available from same location
as the rest).
	2) run marker-table.sql as CMAP user, to create marker tables
	3) ran qtl-schema-oracle.sql as the ontology user, after
modifying to change the pk constraint name for qtl_species.   NB: Had to
obtain correct QTL table schema from gramene.org  NB: Riceseq.sql did
*not* contain the helpview stuff from mkhelpview.sql as indicated by the
documentation.  I had to  add it myself.  Nor did it contain the
seqtable_helper schema, which I obtained from the gramene_ensembl
tarball, in load-scripts/seqtable/create_seqtable_helper.sql.  
	4) Modify mutant.sql, create table mutant to have 20 chars for
mutant_sumbol, to handle data as downloaded from gramene
	5) Add sql for marker_sorghum.
	6) Modify sql for marker_bac_accession_to_object, added
bac_start, bac_stop.  Found sql on web (cvs somewhere)
	7) Modify marker_tos17 sql, needed 100 chars for genome_position
(well, 75 might have done, but 100 seems good)

Moved EVIDENCE_OLD and GO_DBXREF out of ONTOLOGY data dump dir, and
CMAP_DISTINCT_FEATURE_MVIE, CMAP_FNA_MVIEW
CMAP_FEATURE_ALIAS_MVIEW, CMAP_MVIEW_6, CMAP_FEATURE_MVIEW out of the
CMAP data dump dir.  They don't exist in the schema and don't matter
I had to modify importTsvData.pl with the following:
	1) Add ricelite information (triggers and tables) - add RICELITE
to the DBUSER_TO_TRIGGERS and DBUSER_TO_TABLES hash tables.  Check  the
schema for the list of triggers and tables, and use the other bits of
importTsvData for syntax.
	2) include all the relevant riceseq triggers.  It probably
doesn't hurt to simply list *all* the triggers in the riceseq db, but
that is overkill.  For the version 13 release, I had to rename several
of them (the names were simply wrong, but easily identifiable), and add
a couple.  If unsure, add *all*.
	3) Ensure that all constraints in riceseq have distinct names -
they conflicted up in the version 13 release.
	4) Add SEQTABLE_HELPER to the RICESEQ user, and the QTL tables
to teh ONTOLOGY user

Having done the dataimport.

Edit the conf/*.ini files (japonica.ini and DEFAULTS.ini are the
minimum.  I didn't have to modify any others, but who knows ;-)) and
setup  the database connection info.  Then, from the
/usr/local/gramene/conf directory, run createconfig.pl
perl -I ../lib/ensembl/modules/ create_config.pl 
This creates config.packed (which in theory should be done automatically
by the webserver, but for some reason isn't).  This needs to run  with
*no* errors regarding connecting to the database.  If it gives errors,
your ini files are not correct and you'll need to fix them  before
gramene will run.  NB:  This assumes that the database import has been
completed

Edit conf/gramene.conf - change:
EnsemblServerName to the FQDN of the server on which gramene is
installed
All the database connection strings to be the appropriate values (host
to be the db server, dbname to be the instance in which the various
gramene database users exist, and the relevant username/password for
each connection).  This is a highly painful step, but is necessary.

Also, in CSHL/Config.pm, edit the connection strings (as for
gramene.conf), as well as the constants at the top.

In conf/conf.ensembl/SiteDefs.pm, change ENSEMBL_SERVERROOT to the
correct path

In lib/perl/CSHL/ConfigFile.pm, change the CONFIG_FILE path to
/usr/local/gramene/conf/gramene.conf.

Modify /usr/local/gramene/cgi-bin/markers/marker_view - it needs the
full path to the perl interpreter on the first line

I fully installed CMAP rather than just copying into the gramene
directory, so I had to edit
/usr/lib/perl5/site_perl/5.8.3/Bio/GMOD/CMap/Constants.pm to change the
location of the config file (/usr/local/gramene/conf/cmap.conf).  I
believe that if I hadn't installed it "fully" (in site_perl), then step
8 of the main instructions would have been ok (edit Constants.pm in the
gramene local directory)

Also, in the Apache config I had to change the cmap section to:
Alias       /cmap_tmp /tmp/cmap_cache
<Location /cmap>
    SetHandler      perl-script
    PerlHandler     Bio::GMOD::CMap::Apache
</Location>
and edit templates/cmap/map_detail.tmp and
templates/cmap/cmap_viewer.tmp to use cmap_tmp as the image path, rather
than cmap/tmp. According  to the Apache docs, Location tags are checked
before Alias tags, so the CMAP apache handler will always get first
crack at /cmap/tmp and decide to display the index page.
As a corollary, when editing cmap.conf, change the cache_dir to be in
/tmp/cmap_cache (to match the above Alias)

Also for cmap - modify
/usr/lib/perl5/site_perl/5.8.3/Bio/GMOD/CMap/Apache.pm.  In the handler
method, right at the top change the line
    my $path_info = $apr->path_info || '';
into the following two.
    my $request   = Apache->request;
    my $path_info = $request->uri()|| '';
Yeah, it's crude, but it works.  The reason is that the version of
mod_perl and apache we have doesn't pass through the full path_info
(/cmap/viewer gets a null path info, /cmap/viewer/viewer gets /viewer as
the path info).  I couldn't figure out how to fix it properly
(presumably some configuration at the apache end?), so I cheated and
munged the code.  It works for our situation, but may not necessarily
port to other installations.
Again, for cmap, edit templates/cmap/matrix.tmpl, and change all the <a
href="[% url %]... to <a href="/cmap...  It's related to the mapping
issue - again, this is crude, but works. 

Add 
    PerlSetVar      =>  'AceBrowserConf
/usr/local/gramene/conf/acebrowser.conf'
to the /db/searches Location section in conf/httpd.conf


PERL:
Install Compress::Zlib
Put the following into
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi/Ace/Browser/Loc
alSiteDefs.pm.  The perl-Bioperl rpm doesn't  create this file, whereas
using CPAN to install the Ace module does (in response to various
questions).  I don't even think that the paths  matter that much (maybe
they do - we'll see), but the file must exist and must define SITE_DEFS,
CGI_PATH and HTML_PATH, presumably to valid  paths.
***********************************************************************
# Globals for Ace::Browser::SiteDefs
# these get loaded into whatever package requires them (old style)
package Ace::Browser::LocalSiteDefs;
require Exporter;
@ISA = qw(Exporter);
@EXPORT   = qw();
@EXPORT_OK = qw($SITE_DEFS $CGI_PATH $HTML_PATH);
$SITE_DEFS = '/usr/local/apache/conf/ace';
$CGI_PATH  = '/usr/local/apache/cgi-bin/ace';
$HTML_PATH = '/usr/local/apache/htdocs/ace';
1;
__END__
=head1 NAME

Ace::Browser::LocalSiteDefs - Master Configuration file for AceBrowser

=head1 SYNOPSIS

 use Ace::Browser::LocalSiteDefs qw(  );

=head1 DESCRIPTION

This file, which is created at install time, defines three exportable
variables:

    Location of the directory that hold's AceBrowser's database-specific
              configuration files, e.g. /usr/local/apache/conf/ace/

    Location of AceBrowser's HTML files and images, e.g.
~www/htdocs/ace/

     Location of AceBrowser's CGI scripts, e.g. ~www/cgi-bin/ace/

=head1 SEE ALSO

L<Ace>

=head1 AUTHOR

Lincoln Stein <lstein at cshl.org>

Copyright (c) 1997-1998 Cold Spring Harbor Laboratory

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.  See DISCLAIMER.txt for
disclaimers of warranty.

=cut
***********************************************************************

APACHE: Built as per the following example from perl.apache.org:
% tar xzvf apache_1.3.xx.tar.gz
% tar xzvf mod_perl-1.xx.tar.gz
% cd mod_perl-1.xx
% perl Makefile.PL APACHE_SRC=../apache_1.3.xx/src DO_HTTPD=1
USE_APACI=1 EVERYTHING=1
% make && make test && make install
% cd ../apache_1.3.xx
% make install
It's important to do it this way (or an equivalent way), as mod_perl
needs to be statically linked into apache, *NOT* as a shared object
(the apache config files are in Perl)

Needed to add
$Location{"/db"}={
    SetHandler      =>  'perl-script',
    PerlHandler     =>  'Apache::PerlRun',
    AllowOverride   =>  'None',
    Options         =>  '+ExecCGI',
    Order           =>  'allow,deny',
    Allow           =>  'from all',
    PerlSendHeader  =>  'On'
};

to the httpd.conf (near the other Location sections), otherwise huge
chunks of the code use CGI rather than perl, and break horribly

ENSEMBL:
In $GRAMENE_CODE/lib/modules/Apache/EnsEMBL/SendDecPage.pm, ensure that
use Apache::EnsEMBL::NavTrail;
is uncommented. (and restart the webserver after doing this, if you've
already loaded a page)

Create a /usr/local/gramene/perl symlink to /usr/local/gramene/cgi-bin.
The code which does rewrites assumes the perl directory exists (even
though  the apache config specifically ScriptAliases /perl to /cgi-bin).
Without this symlink, the rewrite code doesn't see the scripts in
cgi-bin,  and gives up.
***********************************************************
Craig Miskell,
Technical Support,
AgResearch Invermay
03 489-9279
"The best reason so far is 'you know what happens when you let 
an Austrian  run another country'."  	-Dimitri Maziuk, on why 
Arnold for President would be a bad idea 
=======================================================================
Attention: The information contained in this message and/or attachments
from AgResearch Limited is intended only for the persons or entities
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipients is prohibited by AgResearch
Limited. If you have received this message in error, please notify the
sender immediately.
=======================================================================