Data dictionary for List Management Schema - IntegratedBreedingPlatform/Documentation GitHub Wiki
Introduction
An essential part of the GMS is maintaining lists of germplasm descriptors for breeding, evaluation, or any other purposes. Examples of germplasm lists are parent lists, cross lists, pedigree nursery lists, and yield trial entry lists. List entries are selected either from existing lists or directly from the GMS. New germplasm generated by list processes are stored in GMS. Two tables are included in an installation to manage lists: LISTNMS and LISTDATA. List entries are uniquely identified by USERID and ListRecordID.
listnms Table
Lists are differentiated from one another by a LISTID and a user-assigned list_name (LISTNAME). Each list can refer to a parent list in the hierarchy. Although there is no specified convention for naming lists, it is suggested that users develop conventions that will allow them to easily recognize a list’s contents. When a newly generated list is saved, the GMS assigns a unique identification number to a list (LISTID).
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| LIST_ID (LISTID) | The unique ID for each list generated | Integer | 4 |
| LIST_NAME (LISTNAME) | Corresponding name of list | Text | 50 |
| LIST_DATE (LISTDATE) | Date list was created | Integer | 4 |
| LIST_TYPE (LISTTYPE) | Type of list; links to LISTYPE and FCODE in the UDFLDS table | Text | 10 |
| LIST_USER_ID (LISTUID) | ID of user owning the list | Integer | 2 |
| LIST_DESCRIPTION (LISTDESC) | Title or description of the list | Text | 255 |
| LIST_HIERARCHY (LHIERARCHY) | ID of the parent list folder | Integer | 4 |
| LIST_STATUS (LSTATUS) | Status of list or folder: 0=Folder, 1=Open List, 1x = hidden, 1xx = locked, 1xxx= final, 9= Deleted | Integer | 4 |
| SDATE | Integer | 4 | |
| EDATE | Integer | 4 | |
| LISTLOCN | Integer | 4 | |
| LISTREF | Integer | 4 | |
| PROJECTID | Integer | 4 | |
| PROGRAM_UUID | Text | 36 | |
| NOTES | Notes! | Text | Variable |
There are six fields in the LISTNMS table for which the user must enter values: list_name, list_date, list_type, list_description, list_hierarchy and list_status. The list_type (LISTTYPE) field describes the purpose of a list. Users can define List_types by setting constants in the UDFLDS table. The list_description (LISTDESC) contains a title or short description of the contents and purpose of a list. The List_status field contains integer codes which can have four digits WXYZ where:
- Z - flag for either a folder(0) or a list(1)
- Y - flag for hide(1) or unhide(0)
- X - flag for Lock(1) or unlock(0)
- W - flag for Final(1) or not Final(0)
Thus, a row in LISTNMS can represent either a list or a folder. A Folder, can contain another folder(subfolder) or a List, but no ListData Entries. A List can only contain ListData entries. Locking/unlocking and finalizing a list are done recursively to its subfolders and lists. For example if folder PARENT has subfolders SUBF1, SUBF2, and list LST1, and hidden list LST2 and if PARENT is locked (liststatus = 100) then SUBF1, SUBF2 will also have liststatus = 100, LST1 will have liststatus = 101 and LST2 will have liststatus = 111
listdata Table
A list consists of germplasm entries, which are stored in the table, (LISTDATA). Each record in LISTDATA pertains to an entry within a list. ENTRYID is a positive integer which must be unique within each LISTID. The SOURCE column may be used to store a code to indicate where the planting materials came from. This is often the plot id from a previous season, or an accession or stock number. The DESIG column is used to store the name the user wants printed in a fieldbook, not necessarily a variety’s preferred name. The GRPName field usually stores a cross history for the entry. The ENTRYCD, SOURCE, DESIG and GRPNAME columns may be used for other purposes and any of these may also be blank, if the user so desires. Lists often contain entries for unspecified germplasm such as local checks and/or textlines. Textline is an entry used for a comment or for any other purpose it may serve the user. These type of entries, checks and textlines, have zero in the GID field.
| Columns - Long Name (Name) | Description | Type | Length (bytes) |
|---|---|---|---|
| LIST_ID (LISTID) | ID of the list to which the entry belongs (links to the LISTNMS Table) | Integer | 4 |
| GERMPLASM_ID (GID) | GID of germplasm entry. | Integer | 4 |
| ENTRY_ID (ENTRYID) | Sort order for entries in a list | Integer | 4 |
| ENTRY_CODE (ENTRYCD_BKP) | Entry no. or plot in a nursery identifying an entry. UNUSED | Text | 47 |
| SEED SOURCE (SOURCE) | Seed source of germplasm entry | Text | 255 |
| GROUP_NAME(GRPNAME) | Germplasm cross history | Text | 5000 |
| LIST_RECORD_ID (LRECID) | Record ID within the list | Integer | 4 |
| LIST_RECORD_STATUS (LRSTATUS) | Status of the entry: 0 = active, 9 = deleted | Integer | 4 |
| LOCAL_LIST_RECORD_ID (LLRECID) | Local List ID. Legacy. Unused. | Integer | 4 |
listdataprops Table
Name/value pair style properties associated with each list data row.
| Columns Name | Description | Type | Length (bytes) |
|---|---|---|---|
| listdataprop_id | Primary key. DB Record id. | Integer | 4 |
| listdata_id | Foreign key to the listdata table. | Integer | 4 |
| column_name | Name of the property. | Text | 50 |
| value | Value of the property. | Text | 255 |
list_data_view Table
Table for storing added columns and entry details variables of a list
| Columns | Description | Type | Length (bytes) |
|---|---|---|---|
| id | Primary key. DB record id | Integer | 4 |
| listid | Foreign key to the listnms table. | Integer | 4 |
| static_id | Static ID of added columns | Integer | 4 |
| name_fldno | ID that references fldno in udflds table | Integer | 4 |
| cvterm_id | Foreign key to the cvterm table | Integer | 4 |
| type_id | Foreign key to the cvterm table | Integer | 4 |
list_data_details Table
Table to store Entry detail with each list data row
| Columns | Description | Type | Length (bytes) |
|---|---|---|---|
| id | Primary key. DB record id | Integer | 4 |
| variable_id | Foreign key to the cvterm table. | Integer | 4 |
| lrecid | Foreign key to the listdata table. | Integer | 4 |
| value | Value of the entry detail variable | Text | 255 |
| cvalue_id | Foreign key to the cvterm table. | Integer | 4 |
| created_by | ID of user who created the entry detail | Integer | 4 |
| modified_by | ID of user who modified the entry detail | Integer | 4 |
| created_date | Date and time created | Timestamp | |
| modified_date | Date and time modified | Timestamp |