From ICISWiki

The IRRI SMTA Web Portal

The information in the IRRI SMTA Web Portal are generated involving several components.

Creation of the SMTA document and Loading its information to IRIS local database
Update of the SMTA warehouse tables
The IRRI SMTA Web Portal application

Creation of the SMTA document and Loading its information into IRIS local database

This procedure is tightly related with the processing of outgoing seeds. However, the information used in the SMTA document is curated in the International Rice Information System (IRIS) which is part of the processing of incoming seeds. The flow of information from import to export and its relation to SMTA is described in this document.

Updating the SMTA database (MySQL) on smta.irri.org

SMTA Warehouse Tables

For faster execution time, the SMTA web application accesses four warehouse tables, instead of querying the original ICIS database tables (e.g. STUDY, FACTOR, etc.). These four tables are:

1) OUTGOING_SMTAS

For storing shipment information. (1 shipment = 1 ICIS DMS study)

(Effective Jan 1, 2008: SMTAID & SMTA_FILE_URL length is 20 (increased from 8 and 15, respectively))
(Effective May 16, 2008: new columns SENDER and SHU_REQUEST_NUMBER but both are not visible on the website; added for reporting

purposes of SHU)

(Effective Jan 7, 2009: New column ANON (for "tagging" anonymous shipments). Length of column SMTA_FILE_URL increased to 30)

CREATE TABLE `outgoing_smtas` (
`smtaid`         varchar(20), 
`studyid`        int(10),
`smta_date`      int(10),
`smta_file_url`  varchar(30),
`recipient`      varchar(255),
`country`        varchar(255),
`num_samples`    int(10),
`shu_request_number`         varchar(50),
`sender`         varchar(50), 
`remarks`        varchar(255),
`anon` boolean,
`recipient_type` varchar(255)
);

2) SMTA_ENTRIES

For storing information about seeds/NSBMs included in a shipment.

(Effective Nov 28, 2008: new column MATERIALTYPE, but is not visible on the website; added for reporting purposes of SHU)
(Effective Nov 28, 2008: length of column MTA_STATUS reduced to 20 (from 255))
Effective March 3, 2009: new column SMTAID

CREATE TABLE `smta_entries` (
`studyid`      int(10) NOT NULL,
`ounitid`      int(10),
`gid`          int(10),
`uniquesampleid`  varchar(255),
`designation`  varchar(255),
`origin`       varchar(255),
`mta_status`   varchar(20),
`materialtype`  varchar(255),
`smtaid`       int(10) NOT NULL
);

3) ANCESTRAL_MLS

For storing information about MLS ancestors of seeds/NSBMs with status = "SMTA - UD" (underdeveloped) in a shipment.

Effective March 3, 2009: new column SMTAID

CREATE TABLE `ancestral_mls` (
`gid`                  int(10) NOT NULL,
`ancestral_sample_id`  varchar(255),
`ancestral_origin`     varchar(255),
`studyid`              int(10),
`smtaid`               int(10) NOT NULL
);

4) PASSPORT

For storing MCPD (multi-crop passport descriptor) data

CREATE TABLE `passport` (                     
       `studyid` int(10) default NULL,             
       `AcceNumb` varchar(255) default NULL,       
       `CollNo` varchar(128) default NULL,         
       `COLLCODE` varchar(255) default NULL,       
       `Species` varchar(255) default NULL,        
       `CropName` varchar(255) default NULL,       
       `AcceName` varchar(255) default NULL,       
       `AcqDate` int(10) default NULL,             
       `OrigCty` varchar(255) default NULL,        
       `COLLSITE` varchar(255) default NULL,       
       `LATITUDE` varchar(255) default NULL,       
       `LONGITUD` varchar(255) default NULL,       
       `ELEVATIO` varchar(255) default NULL,       
       `CollDate` varchar(255) default NULL,       
       `BredCode` varchar(255) default NULL,       
       `SampStat` varchar(255) default NULL,       
       `CollSrc` varchar(255) default NULL,        
       `DONORCODE` varchar(255) default NULL,      
       `DonorNum` varchar(128) default NULL,       
       `DuplSite` varchar(255) default NULL,       
       `Storage` varchar(255) default NULL,        
       `REMARKS` varchar(255) default NULL,        
       `CultivarGroup` varchar(255) default NULL,  
       KEY `BredCode` (`BredCode`),                
       KEY `COLLCODE` (`COLLCODE`),                
       KEY `DONORCODE` (`DONORCODE`),              
       KEY `DonorNum` (`DonorNum`),                
       KEY `studyid` (`studyid`)                   
     );

Update Process

1. Setup empty MySQL tables

(Using SQLyog MySQL GUI - Community Edition (Stable) at http://code.google.com/p/sqlyog/downloads/list)

It is assumed that MySQL has been installed in your local machine/PC.

Create the "destination" database

Click the green arrow (Execute Current Query) button:

Reconnect using current settings to see the newly created database:

Run scripts for defining the structure of the ICIS tables

The scripts are available on Cropforge. These may be acquired by downloading or through the check-out function in SVN (e.g.TortoiseSVN). Please contact Mr. Carlos Ortiz (c.n.ortiz@cgiar.org) for assistance in SVN setup.

i. (Right-click on the database, click "Restore from SQL dump"):

ii. Select GMS_TABLES.sql :

...then click "Open".

iii. Click the "Execute" button on the Execute Query(s) From a File window:

iv. Click the "Close" button when import is successful:

Repeat steps i to iv for DMS_TABLES.sql:

Repeat steps i to iv for IMS_TABLES.sql:

2. Transfer MS Access data to Mysql tables

Using Bullzip MS Access to MySQL tool <<Download Access-to-MySQL tool (it's freeware)>>

Welcome window

Click "Next".

Select source database

Click Browse ("..." button) for the MS Access database file (MDB) that will be used as the "source" database:

This MDB file (SHU_Import_Export.mdb) is obtained from \\netwin\SHUPortal\SHU\SHU Local DB\

Select tables

Select all tables. Click "Next".

Transfer options

Only select the following options:

Transfer records

Include column list in INSERT statements

Unicode (utf-8)

Click "Run Now".

3. Define indices on Mysql tables

4. Run MySQL stored procedures to generate SMTA warehouse

Define the stored procedures

The create_smta_warehouse.sql script is available on Cropforge

This script may be acquired by downloading or through the check-out function in SVN (e.g.TortoiseSVN). Please contact Mr. Carlos Ortiz (c.n.ortiz@cgiar.org) for assistance in SVN setup.

Call/execute the stored procedures

call create_smta_warehouse(x,y);

where x = start studyid , y = end studyid

5. Create mysqldump file

On local machine, go to the DOS Command Prompt.
Type the following:

mysqldump -uroot -p --no-create-info shudatabasename passport ancestral_mls smta_entries outgoing_smtas passport > smtaYYYY-XXXXtoZZZZ.sql

Only four tables are required:

outgoing_smtas
ancestral_mls
smta_entries
passport

6. Copy the mysqldump file to the server

(Using SSH File Transfer Client)

Please contact Mr. Carlos Ortiz (c.n.ortiz@cgiar.org) for assistance with access privileges to the server (smta.irri.org).

7. Append data to SMTA website database

mysql> use smta;
mysql> source /home/mhabito/smta2010-0565to0579.sql

8. Move SMTA PDFs to webapps folder

SHU is responsible for copying the SMTA PDFs (in batches, using SSH File Transfer Client) to the /home/shu/YYYY/ folder on smta.irri.org

(There are sub-folders for different years).

This last step (done by a user with sudo rights) simply copies the PDFs from SHU's home folder to the webapps folder (so that the PDFs will be linked to the SMTA website).

Please contact Mr. Carlos Ortiz (c.n.ortiz@cgiar.org) for granting of sudo privileges to the server (www.iris.irri.org).

Miscellaneous queries (Central IRIS GMS: MS Access)

Get list of germplasm with IP status "FOR DISTRIBUTION" (also display germplasm creation location (GLOCN), GLOCN of source/GPID2, MTA (SHU reference number)

SELECT g.gid, 
m.mname as method, 
g.glocn, 
l.lname as glocn_description, 
g.gpid2 as gid_of_source,
gpid2_loc.lname as  location_of_source,
a.aval as ipstatus, 
a2.aval as MTA
FROM (germplsm as g inner join atributs as a on g.gid = a.gid), 
methods as m, 
location as l,
germplsm as source, 
location as  gpid2_loc,
(germplsm as g2 inner join atributs as a2 on g2.gid = a2.gid)
where g.methn = m.mid
and g.gpid2 = source.gid
and g.glocn = l.locid
and source.glocn = gpid2_loc.locid
and a.atype = 1131
and a2.atype = 1130
and a.aval like 'FOR DISTRIBUTION*';

ATYPE = 1131 is attribute IPSTATUS
ATYPE = 1130 is attribute MTA (Seed Health Unit reference number)

IRRI SMTA Web Portal http://smta.irri.org/

Web Pages

The IRRI SMTA Web portal application is one of the several applications maintained in cropforge. The package can be downloaded from the Files section of SMTA project. The codes are available in the CVS repository of the said project

List of Outgoing SMTAs

The list of outgoing SMTAs uses the OUTGOING_SMTAS warehouse table. The button under the SMTA Full Text column will show a copy of the SMTA document sent to Recipient. The documents of the SMTAa are stored in one of the folders in the smta.irri.org web server. The SHU MTA Controller uses a tool to upload documents to the said folder.

Clicking the Entries link under the List Of Entries column will show the succeeding web page.

List of Entries of SMTA

The List of Entries web page uses the SMTA_ENTRIES warehouse table. But the records are limited to a certain StudyID associated with the SMTA.

The MCPD button will show the following page

The MCPD

The MCPD page uses the PASSPORT warehouse table.

The Pedigree of an Entry

The pedigree button in the List of Entries page will show a separate page by calling the viewInfo function of the IRIS Web page (http://smta.irri.org/). The GID (Germplasm Identification) of the particular entry is the parameter to the function.

The Characterization Study of an Entry

Similarly, the description button under the Descriptive Data column of List of Entries page will also show a separate page by calling viewStudy function of the IRIS Web page with GID as parameter.

Installation of the SMTA Web Portal

System Requirements

Please make sure that these are installed in the server:

 MySQL 5.0 or higher (See http://dev.mysql.com/downloads/index.html)   
JAVA 1.5 or higher (See in http://java.sun.com/javase/downloads/index.jsp)
TOMCAT 5.5 or higher (See in http://tomcat.apache.org/index.html)   
SMTA database as prescribed above

Installation

 1. Download a copy of the smta.war file in Cropforge.
2. Deploy smta.war in Tomcat.  For detailed info on how to deploy to Tomcat via Tomcat Manager,
 visit Tomcat Manager How-To.
 Tomcat will unpack smta.war to a folder named 'smta' (if Host tag in server.xml 
 is configured with unpackWARs=true). 
3. Review configuration files located in $TOMCAT_HOME$\webapps\smta\WEB-INF\classes\
 a) resource-cropinfo.properties 
    This property file contains most importantly database configuration and location of 
    SMTA pdf files.  
    Specify the values on the left side.

       # Configuration for MySQL (url = jdbc:mysql://localhost:3306/mydb)
    cropinfo.db.driverClassName = com.mysql.jdbc.Driver
    cropinfo.db.protocol = jdbc
    cropinfo.db.database = mysql
    cropinfo.db.host = localhost
    cropinfo.db.port = 3306
    cropinfo.db.catalog = smta       -> contains the database name    
    cropinfo.db.username = root      -> root is the username.  Change as neccessary.
    cropinfo.db.password =           -> specify password to the smta database if any

       Specify the directory of .pdf files (displayed when SMTA Full text button is clicked) here:

       prefix.smta.file.path = /usr/local/tomcat/webapps/smta/files

    b) log4j.properties   
    This is the property file for logging.  
    Specify Tomcat directory here:

       tomcat.home=/usr/local/tomcat    -> Change the value to your default Tomcat directory

       Specify location and name of log file here:

       log4j.appender.logger.File=${tomcat.home}/logs/cropinfo.log

 4. Reload SMTA web application via Tomcat Manager.  Visit Tomcat Manager How-To.

Updating the SMTA website