Upload 2017 MAC sorghum season 4 field measurements to BETYdb

[NewcombMaria]

@dlebauer and @gsrohde
I'm ready to start uploading some of the field measurements from the 2017 MAC sorghum season (April - September). The cultivar-site table needs a couple revisions to the cultivar identities. As the plants approached their height potential we realized that 2 accessions (cultivars) were incorrectly identified, based on heights from Clemson and in GRIN. We don't know the correct identities. The appropriate cultivar name probably is 'Unknown' or something similar. The following 4 sites need to be re-assigned to 'Unknown' (or 'Unknown accession') instead of PI329440 and PI330807.

PI329440 MAC Field Scanner Season 4 Range 9 Column 14
PI329440 MAC Field Scanner Season 4 Range 32 Column 10
PI330807 MAC Field Scanner Season 4 Range 3 Column 5
PI330807 MAC Field Scanner Season 4 Range 44 Column 2

This issues overlaps with #126 . It's much easier to start with correct cultivar-site descriptions instead of having 2 sets of spreadsheets with and without revisions. Let me know if you need more info to make the changes.

[NewcombMaria]

@dlebauer I need to revise my previous comment. Please change BAP cultivar PI329440 to "Unknown accession' in sites MAC Field Scanner Season 4 Range 9 Column 14 and Field Scanner Season 4 Range 32 Column 10 as requested above, but PI330807 might not be incorrect. It might only be a mixture of different gentoypes with some plants that are 'off-types'. For now it should remain as it is in the cultivar-sites table.

[dlebauer]

@gsrohde can you please update these records in the database? The correct cultivar name is 'Unknown off type'

[NewcombMaria]

Yes, thanks, @gsrohde the only accession that needs to be changed is PI329440, both replicates:
PI329440 MAC Field Scanner Season 4 Range 9 Column 14
PI329440 MAC Field Scanner Season 4 Range 32 Column 10
Updated to 'Unknown off type'.

[NewcombMaria]

@dlebauer and @gsrohde the 2017 season 4 experimental design has height blocks (tall, medium, short). It's a row-column design with further constraint that accessions (cultivars) are blocked by height classes. How should the height classes be handled in BETYdb? Is it part of the site/cultivar information? The height blocks extend across ranges (4 ranges are short, 10 are medium, and the rest are tall).

[dlebauer]

These can be stored as distinct experiments. If you can give a list of ranges within each block, I can add these

[NewcombMaria]

Thanks @dlebauer . Height block info is in the tab FINAL_DESIGN column C in this spreadsheet from issue #126
https://docs.google.com/spreadsheets/d/1iUTkyU5P3IfohQg-sLq_ETWvaWBHF5Nvchx3iI8C8Qw/edit#gid=1908904980
Or by range number:
Short: ranges 11, 20, 46, 50
Medium: ranges 10, 12, 18, 24, 27, 29, 31, 33, 38, 51
All other ranges are either tall or border plots.

[NewcombMaria]

@gsrohde and @dlebauer I have several 2017 field measurement data sets ready to upload. As soon as the site/cultivar/height-block records are revised I'll start uploading. Has there been any changes to the ability to link Methods to data-sets? Do I still keep track of which Methods go with which data sets, or can it now be linked during the BETYdb upload?

[gsrohde]

@NewcombMaria, @dlebauer

OK, I ran this on bety6's bety database:

UPDATE sites_cultivars
   SET cultivar_id = (SELECT id FROM cultivars 
                                    WHERE name = 'Unknown off type')
   WHERE site_id IN (SELECT id FROM sites
                        WHERE sitename ~ 
                        '^MAC Field Scanner Season 4 Range (9 Column 14|32 Column 10)( (E|W))?');

Checking:

SELECT
	s.sitename AS "site name",
	c.name AS "cultivar name"
FROM
	sites s
            JOIN sites_cultivars sc ON sc.site_id = s.id
            JOIN cultivars c ON c.id = sc.cultivar_id
WHERE
	c.name = 'Unknown off type';

results in

                    site name                    |  cultivar name   
-------------------------------------------------+------------------
 MAC Field Scanner Season 4 Range 9 Column 14    | Unknown off type
 MAC Field Scanner Season 4 Range 32 Column 10   | Unknown off type
 MAC Field Scanner Season 4 Range 9 Column 14 E  | Unknown off type
 MAC Field Scanner Season 4 Range 9 Column 14 W  | Unknown off type
 MAC Field Scanner Season 4 Range 32 Column 10 E | Unknown off type
 MAC Field Scanner Season 4 Range 32 Column 10 W | Unknown off type
(6 rows)

[dlebauer]

👍 💯 👏 '^MAC Field Scanner Season 4 Range (9 Column 14|32 Column 10)( (E|W))?')