/*written by Michael Matschiner, 05/27/2009*/
/*send feedback to michael.matschiner-at-unibas.ch*/

/*This code is intended for import of data into the 'amplicon' table of the MySQL
database 'gene_sample' running on evo-lutra.zoo.unibas.ch. Data is imported from
the file addToAmplicon.csv, which needs to be correctly formatted for successful
import.
A template Excel file with individual sheets for every database table can be found at
www.evolution.unibas.ch/salzburger/protocols.htm. You'll need to save the respective
sheet in csv format and change all three filenames specified below accordingly.*/

LOAD DATA LOCAL INFILE "/Users/michaelmatschiner/Documents/addToAmplicon.csv"
INTO TABLE amplicon FIELDS TERMINATED BY "," ENCLOSED BY "\""
LINES TERMINATED BY "\r"
IGNORE 5 LINES
(nucleic_acid, gene, sequence, accession_number, @sample.name, @dummy1, @dummy2)
/*as names are not given to extractions, and it would be too complicated to link to
the extraction table using the extraction ids, amplicons are generally linked to entries
in the extraction table where the extraction type is 'stock', and where the sample names
match.*/
SET extraction_id = (SELECT extraction.id FROM extraction INNER JOIN sample ON extraction.sample_id = sample.id
                     WHERE sample.name = @sample.name AND extraction.type = "stock");

LOAD DATA LOCAL INFILE "/Users/michaelmatschiner/Documents/addToAmplicon.csv"
INTO TABLE primer2amplicon FIELDS TERMINATED BY "," ENCLOSED BY "\""
LINES TERMINATED BY "\r"
IGNORE 5 LINES
(@dummy3, @dummy4, @sequence, @dummy5, @sample.name, @primer.name1, @dummy6)
SET primer_id = (SELECT id FROM primer WHERE name = @primer.name1),
    amplicon_id = (SELECT amplicon.id FROM amplicon INNER JOIN extraction ON amplicon.extraction_id = extraction.id
                   WHERE extraction_id = (SELECT extraction.id FROM extraction INNER JOIN sample ON extraction.sample_id = sample.id
                                          WHERE sample.name = @sample.name AND extraction.type = "stock")
                   AND amplicon.sequence = @sequence);

LOAD DATA LOCAL INFILE "/Users/michaelmatschiner/Documents/addToAmplicon.csv"
INTO TABLE primer2amplicon FIELDS TERMINATED BY "," ENCLOSED BY "\""
LINES TERMINATED BY "\r"
IGNORE 5 LINES
(@dummy7, @dummy8, @sequence, @dummy9, @sample.name, @dummy10, @primer.name2)
SET primer_id = (SELECT id FROM primer WHERE name = @primer.name2),
    amplicon_id = (SELECT amplicon.id FROM amplicon INNER JOIN extraction ON amplicon.extraction_id = extraction.id
                   WHERE extraction_id = (SELECT extraction.id FROM extraction INNER JOIN sample ON extraction.sample_id = sample.id
                                          WHERE sample.name = @sample.name AND extraction.type = "stock")
                   AND amplicon.sequence = @sequence)