Synchronization of IWIS2-WPMS with IWIS3-CENTRAL-GMS

From ICISWiki

Contents 1 Introduction 2 Pedigree Parsing 2.1 Parsing records from the IWIS2 Crosses Table (SID=0) 2.2 The Algorithm for IWIS records with SID=0 2.3 The Algorithm for Parsing IWIS2 selections (SID<>0) 3 Keeping Track of IWIS2 Changes and synchronizing with IWIS3 changes 4 Uploading the Changes

Introduction

An IWIS3 clone has been added to the IWIS2 SQL-Server database together with bridging tables to link IWIS locations to ICIS locations and log synchronization clashes between IWIS2 and IWIS3.

A program written by Jesper Norgaard in Transact SQL for the SQL Server database is triggered whenever IWIS2 is updated and this program updates the IWIS3 clone.

Pedigree Parsing

All IWIS2 germplasm are recognized in IWIS3 by having non zero values of CID and SID fields in the GERMPLSM table (Non ICIS2 germplasm records must have these fields set to zero). In addition the IWIS3 clone has a field GCHANGE in the GERMPLSM table which is set to zero after each upload of the IWIS2 local to the IWIS3-CENTRAL. If a new germplasm record is added (a new CID, SID is entered into IWIS2) then a new germplasm record is added to the IWIS3 clone and the GCHANGE field is set to -1.

Parsing records from the IWIS2 Crosses Table (SID=0)

IWIS2 germplasm with the following characteristics

• SID=0
• No BCID
• usually a cultivar name (The preferred name of an IWIS2 entry with CID>0 and SID=0 is identified with an IWIS3 Cultivar name (NTYPE=6))
• often an introduction number

are regarded as inbred lines not crosses.

The lines themselves are identified with IWIS3 Imports (correspond to method 62) unless they have an IWIS2 Introduction Number in which case they are IWIS3 collection lines (method 255). If parents are given, a new IWIS3 record is generated representing the cross of those parents according to the cross rules below. (The CID of this new record is set to -1*CID of the original line)

Generally, IWIS2 lines with SID=0 which

• Have a CIMMYT BCID and
• Do not have Introduction Numbers

are non-inbred crosses (F1) in IWIS3

However, there are cases of germplasm in IWIS2 with SID=0 having BCIDs and Cultivar names and/or introduction numbers. Some of these are imported lines and some are CIMMYT crosses to which introduction numbers have been erroneously associated. There also appear to be some CIMMYT crosses without BCIDs

Example (constructed):  GYLDENKAAL
GID=156 has parents GPID1=287 and GPID2=355. GID=156 was already known.
Preferred name GYLDENKAAL
It has no ACCNO
It has no BCID
Name for GID=287 is "287"
Name for GID=355 is "355"

before GID=156 GPID1=287 GPID2=355 method 102(example)
name for GID=156 AMARILLO

now GID=156 GPID1=4275375 GPID2=0 method 62, GNPGS=-1
GID=4275375 GPID1=287 GPID2=355 method 102(example)
name for GID=156 AMARILLO
name for GID=4275375 "287/355" 

Assumptions above: All CIMMYT Crosses were given BCIDs and imports were not (we know that the first assumptions has a number of exceptions, there could be some that was actually given a BCID but are imports, but they will be handled as an F1).

The Algorithm for IWIS records with SID=0

Use IWIS3 Method 255 - COLLECTION LINE SF, Method 62 – IMPORT and Methods 101, 102, 103, 106, 107 and 2 – bi-parental cross types.

1. Does it have ACCNO?
  No -> 2
  Yes, METHOD=255 GNPGS=-1
  1.1 Does it have parents?
     Yes, ADD a cross, give it the A/B cross name, finished
     No, finished

2. Does it have a BCID?
  Yes -> 4
  No -> 3

3. Does it have a preferred name for SID=0?
  No -> 4
  Yes, METHOD=62 GNPGS=-1
     Does it have parents?
     Yes  -> 1.1
     No  -> finished

4. Determine which type 107, 106, 103, 102, 101, 2 (in that order):

Backcross (method 107) in IWIS2 determine by cross type M or F
(definition: When male parent of female is male of cross, or female 
parent of male is female of cross)

If not backcross, apply either of the following 5 methods:

(1) Single cross (method 101) 
    Both parents are inbred lines (see above)
(2) Three-way cross (method 102)
    Has one inbred parent and one F1 parent (not backcross)
(3) Double cross (method 103)
    Both parents are F1s (not backrosses)
(4) Complex cross (method 106)
    One or both parents are backrosses
(5) Unknown generative method (method 2)
    Use when a cross does not comply with 101, 102, 103, 106 or 107
    GNPGS = 2

The Algorithm for Parsing IWIS2 selections (SID<>0)

IWIS2 records with SID<>0 are selections which correspond to IWIS3 derivative methods. Algorithm to determine selection type:

(1) Single Plant Selection (method 205)
    Selection with SNU > 0
(2) Selected bulk (method 206)
    Selection with SNU = 0
(3) Random bulk (method 207)
    Selection with SNU < 0

Non-standard Selection Histories in CIMMYT are used for two purposes:

• To save selection histories with a non-standard format
• To save selections with no selection history but one or several names attached, when nothing is known about the origin of the material, but it is not necessarily a cross

The unknown derivative method (method 31) is used for these.

Keeping Track of IWIS2 Changes and synchronizing with IWIS3 changes

If a germplasm record is changed in IWIS2, the synchronization program checks the IWIS3, CHANGES table to see whether the existing IWIS3 record has been updated by a non IWIS2 user. If so it logs a report of the requested change to a report table, SYNCREP. If not, it makes the equivalent change to the IWIS3 record and increases or decreases the GCHANGE field values by one depending on whether its current value is <0 or >=0. If the required change is a deletion the GCHANGE field value is set to -999. CHANGE records are added to the ICIS CHANGES table so that the IWIS3-CENTRAL database can be updated with the same changes.

Names extracted from IWIS2 are uniquely identified by GID NTYPE and NVAL. The NAMES table in the IWIS3 clone has an NCHANGE field which is set to zero after each update. If new names are added to IWIS2, for new or old germplasm, then equivalent records are added to the IWIS3 clone with the NCHANGE Field set to – 1. If a name is changed in IWIS2, then a check is made in the changes table of IWIS3 to see if external changes have been made to that name. If so, a report of the required IWIS2 change is logged to the SYNCREP table. If not the corresponding change is made in the IWIS3 clone database and the NCHANGE filed is increased or decreased by 1 depending on whether its current value is >= 0 or < 0. If a name is deleted from IWIS2, the corresponding NAMES NCHANGE field is set to -999. CHANGE records are added to the ICIS CHANGES table so that the IWIS3-CENTRAL database can be updated with the same changes.

An exactly similar situation pertains to the ATRIBUTS table in the IWIS3 clone database with the extra field ACHANGE.

Uploading the Changes

At each IWIS2 to IWIS3 upload, all records with non zero GCHANGE, NCHANGE and ACHANGE fields will be exported to an ACCESS database. A series of queries will be run on the records that are downloaded to construct a formal ICIS local database by adding new records and generating CHANGE records for existing IWIS3 records. The SYNCREP reports will be manually interpreted to merge IWIS2 and external changes creating change records in the local database as required. Finally the local database is uploaded to IWIS3-CENTRAL in the usual way and the IWIS3 clone in the IWIS2 SQL Server database is refreshed with the GCHANGE, NCHANGE and ACHANGE fields set to zero.