EvoNAPS_import_statements.sql

/* 
Example import commands to inport files into the EvoNAPS database. 

Created by: Franziska Reden 
Create date: 08.03.2023


This script includes example import commands for the files generated by the EvoNAPS workflow. 
Exchange the phrase '/PATH/TO/FILE_NAME' to the actual path to and name of the file to be imported. 
Note, that thd import statements need to be exectued in the order as they appear in this script. 
Otherwise, there might be errors due to the constraints as introduced to some tables in the EvoNAPS database. 

Also note, that there are different import commands for importing data into the DNA or protein tables! 
*/

/*
The following commands import the parameters stored in the target files of the EvoNAPS Snakemake workflow 
into the DNA tables of the EvoNAPS database.
*/


/*Loading data into the dna_alignemnts table from the corresponding ali_parameters-file.
Set FROM_DATABASE to the source database.*/
--dna_alignments
LOAD DATA LOCAL INFILE 'FILE_NAME'
IGNORE INTO TABLE dna_alignments FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES
(@ALI_ID, @IQTREE_VERSION, @RANDOM_SEED, @TIME_STAMP, @SEQ_TYPE, 
TAXA, SITES,
PARSIMONY_INFORMATIVE_SITES, SINGLETON_SITES, CONSTANT_SITES, 
FRAC_WILDCARDS_GAPS, DISTINCT_PATTERNS, FAILED_CHI2, IDENTICAL_SEQ, EXCLUDED_SEQ, 
DIST_MIN, DIST_MAX, DIST_MEAN, DIST_MEDIAN, DIST_VAR)
SET
ALI_ID='custom_ali_id',
FROM_DATABASE = 'SOURCE'
;

/*Loading data into the dna_sequences table from the corresponding-file.*/
--dna_sequences
LOAD DATA LOCAL INFILE 'FILE_NAME'
IGNORE INTO TABLE dna_sequences FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, SEQ_INDEX, SEQ_NAME, TAX_ID, TAX_CHECK, @ACC_NR, FRAC_WILDCARDS_GAPS, CHI2_P_VALUE, CHI2_PASSED, EXCLUDED, @IDENTICAL_TO, FREQ_A, FREQ_C, FREQ_G, FREQ_T, SEQ)
SET
ALI_ID='custom_ali_id',
ACC_NR = NULLIF(@ACC_NR, ''),
IDENTICAL_TO = NULLIF(@IDENTICAL_TO, '')
;

/*Loading data into the dna_modelparameters table from the corresponding model_parameters-file.*/
--dna_modelparameters
LOAD DATA LOCAL INFILE 'FILE_NAME'
IGNORE INTO TABLE dna_modelparameters FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES
(@ALI_ID, IQTREE_VERSION, RANDOM_SEED, TIME_STAMP, KEEP_IDENT, ORIGINAL_ALI, MODEL, BASE_MODEL, @FREQ_TYPE, RHAS_MODEL, NUM_RATE_CAT,
LOGL, AIC, AIC_WEIGHT, CONFIDENCE_AIC, AICC, AICC_WEIGHT, CONFIDENCE_AICC, BIC, BIC_WEIGHT, CONFIDENCE_BIC,
CAIC, CAIC_WEIGHT, CONFIDENCE_CAIC, ABIC, ABIC_WEIGHT, CONFIDENCE_ABIC,
NUM_FREE_PARAMETERS, NUM_MODEL_PARAMETERS, NUM_BRANCHES, TREE_LENGTH ,@vpropInvar, @valpha,
FREQ_A, FREQ_C, FREQ_G, FREQ_T,
@vrateAC, @vrateAG, @vrateAT, @vrateCG, @vrateCT, @vrateGT,
@vpropCat_1, @vrelRateCat_1, @vpropCat_2, @vrelRateCat_2, @vpropCat_3, @vrelRateCat_3, @vpropCat_4, @vrelRateCat_4,
@vpropCat_5, @vrelRateCat_5, @vpropCat_6, @vrelRateCat_6, @vpropCat_7, @vrelRateCat_7, @vpropCat_8, @vrelRateCat_8,
@vpropCat_9, @vrelRateCat_9, @vpropCat_10, @vrelRateCat_10)
set
ALI_ID='custom_ali_id',
PROP_INVAR = NULLIF(@vpropInvar, ''),
ALPHA = NULLIF(@valpha, ''),
FREQ_TYPE = IF(@FREQ_TYPE = 'equal', 'uniform', @FREQ_TYPE),
MODEL_TYPE='Markov',
RATE_AC = @vrateAC,
RATE_AG = @vrateAG,
RATE_AT = @vrateAT,
RATE_CG = @vrateCG,
RATE_CT = @vrateCT,
RATE_GT = @vrateGT,
RATE_CA = @vrateAC,
RATE_GA = @vrateAG,
RATE_TA = @vrateAT,
RATE_GC = @vrateCG,
RATE_TC = @vrateCT,
RATE_TG = @vrateGT,
REL_RATE_CAT_1 = NULLIF(@vrelRateCat_1, ''),
PROP_CAT_1 = NULLIF(@vpropCat_1, ''),
REL_RATE_CAT_2 = NULLIF(@vrelRateCat_2, ''),
PROP_CAT_2 = NULLIF(@vpropCat_2, ''),
REL_RATE_CAT_3 = NULLIF(@vrelRateCat_3, ''),
PROP_CAT_3 = NULLIF(@vpropCat_3, ''),
REL_RATE_CAT_4 = NULLIF(@vrelRateCat_4, ''),
PROP_CAT_4 = NULLIF(@vpropCat_4, ''),
REL_RATE_CAT_5 = NULLIF(@vrelRateCat_5, ''),
PROP_CAT_5 = NULLIF(@vpropCat_5, ''),
REL_RATE_CAT_6 = NULLIF(@vrelRateCat_6, ''),
PROP_CAT_6 = NULLIF(@vpropCat_6, ''),
REL_RATE_CAT_7 = NULLIF(@vrelRateCat_7, ''),
PROP_CAT_7 = NULLIF(@vpropCat_7, ''),
REL_RATE_CAT_8 = NULLIF(@vrelRateCat_8, ''),
PROP_CAT_8 = NULLIF(@vpropCat_8, ''),
REL_RATE_CAT_9 = NULLIF(@vrelRateCat_9, ''),
PROP_CAT_9 = NULLIF(@vpropCat_9, ''),
REL_RATE_CAT_10 = NULLIF(@vrelRateCat_10, ''),
PROP_CAT_10 = NULLIF(@vpropCat_10, '')
;

/*Loading data into the dna_trees table from the corresponding tree_parameters-file.*/
--dna_trees
LOAD DATA LOCAL INFILE 'FILE_NAME' 
IGNORE INTO TABLE dna_trees FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, IQTREE_VERSION, RANDOM_SEED, TIME_STAMP, TREE_TYPE, @CHOICE_CRITERIUM, KEEP_IDENT, ORIGINAL_ALI,
MODEL, BASE_MODEL, @FREQ_TYPE, RHAS_MODEL, NUM_RATE_CAT, 
LOGL, @UNCONSTRAINED_LOGL, AIC, AICC, BIC, CAIC, ABIC, NUM_FREE_PARAMETERS, NUM_MODEL_PARAMETERS, NUM_BRANCHES, 
@vpropInvar, @valpha, FREQ_A, FREQ_C, FREQ_G, FREQ_T, 
@vrateAC, @vrateAG, @vrateAT, @vrateCG, @vrateCT, @vrateGT,
@vpropCat_1, @vrelRateCat_1, @vpropCat_2, @vrelRateCat_2, @vpropCat_3, @vrelRateCat_3, @vpropCat_4, @vrelRateCat_4, 
@vpropCat_5, @vrelRateCat_5, @vpropCat_6, @vrelRateCat_6, @vpropCat_7, @vrelRateCat_7, @vpropCat_8, @vrelRateCat_8, 
@vpropCat_9, @vrelRateCat_9, @vpropCat_10, @vrelRateCat_10, 
TREE_LENGTH, SUM_IBL, TREE_DIAMETER, @DIST_MIN, @DIST_MAX, @DIST_MEAN, @DIST_MEDIAN, @DIST_VAR, 
BL_MIN, BL_MAX, BL_MEAN, BL_MEDIAN, BL_VAR, 
@IBL_MIN, @IBL_MAX, @IBL_MEAN, @IBL_MEDIAN, @IBL_VAR, 
@EBL_MIN, @EBL_MAX, @EBL_MEAN, @EBL_MEDIAN, @EBL_VAR, 
POT_LBA_7, POT_LBA_8, POT_LBA_9, POT_LBA_10,
NEWICK_STRING)
set 
ALI_ID='custom_ali_id',
PROP_INVAR = NULLIF(@vpropInvar, ''),
CHOICE_CRITERIUM = NULLIF(@CHOICE_CRITERIUM, ''),
UNCONSTRAINED_LOGL = NULLIF(@UNCONSTRAINED_LOGL, ''),
ALPHA = NULLIF(@valpha, ''),
FREQ_TYPE = IF(@FREQ_TYPE = 'equal', 'uniform', @FREQ_TYPE),
MODEL_TYPE='Markov',
RATE_AC = @vrateAC,
RATE_AG = @vrateAG,
RATE_AT = @vrateAT,
RATE_CG = @vrateCG,
RATE_CT = @vrateCT,
RATE_GT = @vrateGT,
RATE_CA = @vrateAC,
RATE_GA = @vrateAG,
RATE_TA = @vrateAT,
RATE_GC = @vrateCG,
RATE_TC = @vrateCT,
RATE_TG = @vrateGT,
REL_RATE_CAT_1 = NULLIF(@vrelRateCat_1, ''),
PROP_CAT_1 = NULLIF(@vpropCat_1, ''),
REL_RATE_CAT_2 = NULLIF(@vrelRateCat_2, ''),
PROP_CAT_2 = NULLIF(@vpropCat_2, ''),
REL_RATE_CAT_3 = NULLIF(@vrelRateCat_3, ''),
PROP_CAT_3 = NULLIF(@vpropCat_3, ''),
REL_RATE_CAT_4 = NULLIF(@vrelRateCat_4, ''),
PROP_CAT_4 = NULLIF(@vpropCat_4, ''),
REL_RATE_CAT_5 = NULLIF(@vrelRateCat_5, ''),
PROP_CAT_5 = NULLIF(@vpropCat_5, ''),
REL_RATE_CAT_6 = NULLIF(@vrelRateCat_6, ''),
PROP_CAT_6 = NULLIF(@vpropCat_6, ''),
REL_RATE_CAT_7 = NULLIF(@vrelRateCat_7, ''),
PROP_CAT_7 = NULLIF(@vpropCat_7, ''),
REL_RATE_CAT_8 = NULLIF(@vrelRateCat_8, ''),
PROP_CAT_8 = NULLIF(@vpropCat_8, ''),
REL_RATE_CAT_9 = NULLIF(@vrelRateCat_9, ''),
PROP_CAT_9 = NULLIF(@vpropCat_9, ''),
REL_RATE_CAT_10 = NULLIF(@vrelRateCat_10, ''),
PROP_CAT_10 = NULLIF(@vpropCat_10, ''), 
DIST_MIN = NULLIF(@DIST_MIN, ''), 
DIST_MAX = NULLIF(@DIST_MAX, ''),
DIST_MEAN = NULLIF(@DIST_MEAN, ''),
DIST_MEDIAN = NULLIF(@DIST_MEDIAN, ''),
DIST_VAR = NULLIF(@DIST_VAR, ''),
IBL_MIN = NULLIF(@IBL_MIN, ''), 
IBL_MAX = NULLIF(@IBL_MAX, ''),
IBL_MEAN = NULLIF(@IBL_MEAN, ''),
IBL_MEDIAN = NULLIF(@IBL_MEDIAN, ''),
IBL_VAR = NULLIF(@IBL_VAR, ''), 
EBL_MIN = NULLIF(@EBL_MIN, ''), 
EBL_MAX = NULLIF(@EBL_MAX, ''),
EBL_MEAN = NULLIF(@EBL_MEAN, ''),
EBL_MEDIAN = NULLIF(@EBL_MEDIAN, ''),
EBL_VAR = NULLIF(@EBL_VAR, '')
;

/*Loading data into the dna_branches table from the corresponding branch_parameters-file.*/
--dna_branches
LOAD DATA LOCAL INFILE 'FILE_NAME' 
IGNORE INTO TABLE dna_branches FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, @IQTREE_VERSION, @RANDOM_SEED, TIME_STAMP,
TREE_TYPE, BRANCH_INDEX, BRANCH_TYPE, BL, SPLIT_SIZE,
MIN_PATH_1, MAX_PATH_1, MEAN_PATH_1, MEDIAN_PATH_1,
MIN_PATH_2, MAX_PATH_2, MEAN_PATH_2, MEDIAN_PATH_2)
SET 
ALI_ID='custom_ali_id'
;

/*
The following comments import the parameters stored in the target files of the EvoNAPS Snakemake workflow 
into the protein tables of the EvoNAPS database
*/

/*Loading data into the aa_alignments table from the corresponding ali_parameters-file.*/
--aa_alignments
LOAD DATA LOCAL INFILE 'FILE_NAME' 
IGNORE INTO TABLE aa_alignments FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, @IQTREE_VERSION, @RANDOM_SEED, @TIME_STAMP, @SEQ_TYPE, 
TAXA, SITES,
PARSIMONY_INFORMATIVE_SITES, SINGLETON_SITES, CONSTANT_SITES, 
FRAC_WILDCARDS_GAPS, DISTINCT_PATTERNS, FAILED_CHI2, IDENTICAL_SEQ, EXCLUDED_SEQ, 
DIST_MIN, DIST_MAX, DIST_MEAN, DIST_MEDIAN, DIST_VAR)
SET
ALI_ID='custom_ali_id',
FROM_DATABASE = 'SOURCE'
;

/*Loading data into the aa_sequences table from the corresponding seq_parameters-file.*/
--aa_sequences
LOAD DATA LOCAL INFILE 'FILE_NAME'  
IGNORE INTO TABLE aa_sequences FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, SEQ_INDEX, SEQ_NAME, TAX_ID, TAX_CHECK, @ACC_NR, FRAC_WILDCARDS_GAPS, CHI2_P_VALUE, CHI2_PASSED, EXCLUDED, @IDENTICAL_TO, 
FREQ_A, FREQ_R,FREQ_N,FREQ_D,FREQ_C,FREQ_Q,FREQ_E,FREQ_G,FREQ_H,FREQ_I,FREQ_L,FREQ_K,FREQ_M,FREQ_F, FREQ_P,FREQ_S,FREQ_T,FREQ_W, FREQ_Y, FREQ_V, 
SEQ)
SET
ALI_ID='custom_ali_id',
ACC_NR = NULLIF(@ACC_NR, ''),
IDENTICAL_TO = NULLIF(@IDENTICAL_TO, '')
;

/*Loading data into the aa_modelparameters table from the corresponding model_parameters-file.*/
--aa_modelparameters
LOAD DATA LOCAL INFILE 'FILE_NAME' 
IGNORE INTO TABLE aa_modelparameters FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, IQTREE_VERSION, RANDOM_SEED, TIME_STAMP, KEEP_IDENT, ORIGINAL_ALI, MODEL, BASE_MODEL, @FREQ_TYPE, RHAS_MODEL, NUM_RATE_CAT, 
LOGL, AIC, AIC_WEIGHT, CONFIDENCE_AIC, AICC, AICC_WEIGHT, CONFIDENCE_AICC, BIC, BIC_WEIGHT, CONFIDENCE_BIC, 
CAIC, CAIC_WEIGHT, CONFIDENCE_CAIC, ABIC, ABIC_WEIGHT, CONFIDENCE_ABIC, 
NUM_FREE_PARAMETERS, NUM_MODEL_PARAMETERS, NUM_BRANCHES, TREE_LENGTH, @vpropInvar, @valpha, 
@FREQ_A, @FREQ_R,@FREQ_N,@FREQ_D,@FREQ_C,@FREQ_Q,@FREQ_E,@FREQ_G,@FREQ_H,@FREQ_I,@FREQ_L,@FREQ_K,@FREQ_M,@FREQ_F, @FREQ_P,@FREQ_S,@FREQ_T,@FREQ_W, @FREQ_Y, @FREQ_V,
@vpropCat_1, @vrelRateCat_1, @vpropCat_2, @vrelRateCat_2, @vpropCat_3, @vrelRateCat_3, @vpropCat_4, @vrelRateCat_4, 
@vpropCat_5, @vrelRateCat_5, @vpropCat_6, @vrelRateCat_6, @vpropCat_7, @vrelRateCat_7, @vpropCat_8, @vrelRateCat_8, 
@vpropCat_9, @vrelRateCat_9, @vpropCat_10, @vrelRateCat_10)
set 
ALI_ID='custom_ali_id',
PROP_INVAR = NULLIF(@vpropInvar, ''),
ALPHA = NULLIF(@valpha, ''),
FREQ_TYPE = IF(@FREQ_TYPE = 'equal', 'uniform', @FREQ_TYPE),
MODEL_TYPE='Markov',
FREQ_A=@FREQ_A,
FREQ_R=@FREQ_R,
FREQ_N=@FREQ_N,
FREQ_D=@FREQ_D,
FREQ_C=@FREQ_C,
FREQ_Q=@FREQ_Q,
FREQ_E=@FREQ_E,
FREQ_G=@FREQ_G,
FREQ_H=@FREQ_H,
FREQ_I=@FREQ_I,
FREQ_L=@FREQ_L,
FREQ_K=@FREQ_K,
FREQ_M=@FREQ_M,
FREQ_F=@FREQ_F,
FREQ_P=@FREQ_P,
FREQ_S=@FREQ_S,
FREQ_T=@FREQ_T,
FREQ_W=@FREQ_W,
FREQ_Y=@FREQ_Y,
FREQ_V=@FREQ_V,
REL_RATE_CAT_1 = NULLIF(@vrelRateCat_1, ''),
PROP_CAT_1 = NULLIF(@vpropCat_1, ''),
REL_RATE_CAT_2 = NULLIF(@vrelRateCat_2, ''),
PROP_CAT_2 = NULLIF(@vpropCat_2, ''),
REL_RATE_CAT_3 = NULLIF(@vrelRateCat_3, ''),
PROP_CAT_3 = NULLIF(@vpropCat_3, ''),
REL_RATE_CAT_4 = NULLIF(@vrelRateCat_4, ''),
PROP_CAT_4 = NULLIF(@vpropCat_4, ''),
REL_RATE_CAT_5 = NULLIF(@vrelRateCat_5, ''),
PROP_CAT_5 = NULLIF(@vpropCat_5, ''),
REL_RATE_CAT_6 = NULLIF(@vrelRateCat_6, ''),
PROP_CAT_6 = NULLIF(@vpropCat_6, ''),
REL_RATE_CAT_7 = NULLIF(@vrelRateCat_7, ''),
PROP_CAT_7 = NULLIF(@vpropCat_7, ''),
REL_RATE_CAT_8 = NULLIF(@vrelRateCat_8, ''),
PROP_CAT_8 = NULLIF(@vpropCat_8, ''),
REL_RATE_CAT_9 = NULLIF(@vrelRateCat_9, ''),
PROP_CAT_9 = NULLIF(@vpropCat_9, ''),
REL_RATE_CAT_10 = NULLIF(@vrelRateCat_10, ''),
PROP_CAT_10 = NULLIF(@vpropCat_10, '')
;
/*Loading data into the aa_trees table from the corresponding tree_parameters-file.*/
--aa_trees
LOAD DATA LOCAL INFILE 'FILE_NAME' 
IGNORE INTO TABLE aa_trees FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, IQTREE_VERSION, RANDOM_SEED, TIME_STAMP, TREE_TYPE, @CHOICE_CRITERIUM, KEEP_IDENT, ORIGINAL_ALI, 
MODEL, BASE_MODEL, @FREQ_TYPE, RHAS_MODEL, NUM_RATE_CAT, 
LOGL, @UNCONSTRAINED_LOGL, AIC, AICC, BIC, CAIC, ABIC, NUM_FREE_PARAMETERS, NUM_MODEL_PARAMETERS, NUM_BRANCHES, 
@vpropInvar, @valpha, 
@FREQ_A, @FREQ_R,@FREQ_N,@FREQ_D,@FREQ_C,@FREQ_Q,@FREQ_E,@FREQ_G,@FREQ_H,@FREQ_I,@FREQ_L,@FREQ_K,@FREQ_M,@FREQ_F, @FREQ_P,@FREQ_S,@FREQ_T,@FREQ_W, @FREQ_Y, @FREQ_V,
@vpropCat_1, @vrelRateCat_1, @vpropCat_2, @vrelRateCat_2, @vpropCat_3, @vrelRateCat_3, @vpropCat_4, @vrelRateCat_4, 
@vpropCat_5, @vrelRateCat_5, @vpropCat_6, @vrelRateCat_6, @vpropCat_7, @vrelRateCat_7, @vpropCat_8, @vrelRateCat_8, 
@vpropCat_9, @vrelRateCat_9, @vpropCat_10, @vrelRateCat_10,  
TREE_LENGTH, SUM_IBL, TREE_DIAMETER, @DIST_MIN, @DIST_MAX, @DIST_MEAN, @DIST_MEDIAN, @DIST_VAR, 
BL_MIN, BL_MAX, BL_MEAN, BL_MEDIAN, BL_VAR, 
@IBL_MIN, @IBL_MAX, @IBL_MEAN, @IBL_MEDIAN, @IBL_VAR, 
@EBL_MIN, @EBL_MAX, @EBL_MEAN, @EBL_MEDIAN, @EBL_VAR, 
POT_LBA_7, POT_LBA_8, POT_LBA_9, POT_LBA_10,
NEWICK_STRING)
set 
ALI_ID='custom_ali_id',
PROP_INVAR = NULLIF(@vpropInvar, ''),
CHOICE_CRITERIUM = NULLIF(@CHOICE_CRITERIUM, ''),
UNCONSTRAINED_LOGL = NULLIF(@UNCONSTRAINED_LOGL, ''),
ALPHA = NULLIF(@valpha, ''),
FREQ_TYPE = IF(@FREQ_TYPE = 'equal', 'uniform', @FREQ_TYPE),
MODEL_TYPE='Markov',
FREQ_A=@FREQ_A,
FREQ_R=@FREQ_R,
FREQ_N=@FREQ_N,
FREQ_D=@FREQ_D,
FREQ_C=@FREQ_C,
FREQ_Q=@FREQ_Q,
FREQ_E=@FREQ_E,
FREQ_G=@FREQ_G,
FREQ_H=@FREQ_H,
FREQ_I=@FREQ_I,
FREQ_L=@FREQ_L,
FREQ_K=@FREQ_K,
FREQ_M=@FREQ_M,
FREQ_F=@FREQ_F,
FREQ_P=@FREQ_P,
FREQ_S=@FREQ_S,
FREQ_T=@FREQ_T,
FREQ_W=@FREQ_W,
FREQ_Y=@FREQ_Y,
FREQ_V=@FREQ_V,
REL_RATE_CAT_1 = NULLIF(@vrelRateCat_1, ''),
PROP_CAT_1 = NULLIF(@vpropCat_1, ''),
REL_RATE_CAT_2 = NULLIF(@vrelRateCat_2, ''),
PROP_CAT_2 = NULLIF(@vpropCat_2, ''),
REL_RATE_CAT_3 = NULLIF(@vrelRateCat_3, ''),
PROP_CAT_3 = NULLIF(@vpropCat_3, ''),
REL_RATE_CAT_4 = NULLIF(@vrelRateCat_4, ''),
PROP_CAT_4 = NULLIF(@vpropCat_4, ''),
REL_RATE_CAT_5 = NULLIF(@vrelRateCat_5, ''),
PROP_CAT_5 = NULLIF(@vpropCat_5, ''),
REL_RATE_CAT_6 = NULLIF(@vrelRateCat_6, ''),
PROP_CAT_6 = NULLIF(@vpropCat_6, ''),
REL_RATE_CAT_7 = NULLIF(@vrelRateCat_7, ''),
PROP_CAT_7 = NULLIF(@vpropCat_7, ''),
REL_RATE_CAT_8 = NULLIF(@vrelRateCat_8, ''),
PROP_CAT_8 = NULLIF(@vpropCat_8, ''),
REL_RATE_CAT_9 = NULLIF(@vrelRateCat_9, ''),
PROP_CAT_9 = NULLIF(@vpropCat_9, ''),
REL_RATE_CAT_10 = NULLIF(@vrelRateCat_10, ''),
PROP_CAT_10 = NULLIF(@vpropCat_10, ''), 
DIST_MIN = NULLIF(@DIST_MIN, ''), 
DIST_MAX = NULLIF(@DIST_MAX, ''),
DIST_MEAN = NULLIF(@DIST_MEAN, ''),
DIST_MEDIAN = NULLIF(@DIST_MEDIAN, ''),
DIST_VAR = NULLIF(@DIST_VAR, ''), 
IBL_MIN = NULLIF(@IBL_MIN, ''), 
IBL_MAX = NULLIF(@IBL_MAX, ''),
IBL_MEAN = NULLIF(@IBL_MEAN, ''),
IBL_MEDIAN = NULLIF(@IBL_MEDIAN, ''),
IBL_VAR = NULLIF(@IBL_VAR, ''), 
EBL_MIN = NULLIF(@EBL_MIN, ''), 
EBL_MAX = NULLIF(@EBL_MAX, ''),
EBL_MEAN = NULLIF(@EBL_MEAN, ''),
EBL_MEDIAN = NULLIF(@EBL_MEDIAN, ''),
EBL_VAR = NULLIF(@EBL_VAR, '') 
;
/*Loading data into the aa_branches table from the corresponding branch_parameters-file.*/
--aa_branches
LOAD DATA LOCAL INFILE 'FILE_NAME' 
IGNORE INTO TABLE aa_branches FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
(@ALI_ID, @IQTREE_VERSION, @RANDOM_SEED_NUM, TIME_STAMP, TREE_TYPE, BRANCH_INDEX, BRANCH_TYPE, BL, SPLIT_SIZE, 
MEAN_PATH_1, MEDIAN_PATH_1, MIN_PATH_1, MAX_PATH_1, MEAN_PATH_2, MEDIAN_PATH_2, MIN_PATH_2, MAX_PATH_2)
SET
ALI_ID='custom_ali_id',
;