EvoNAPS_create_statements.sql

/*
Crerate statements for the EvoNAPS database

Created by: Franziska Reden
Create date: 08.03.2023


This script includes all create statements for the MySQL database EvoNAPS. 
Overall, there are 13 tables. A short description of each table and columns are added as comments. 
Note, that the create statements need to be executed in the precise order in which they appear in 
this script. Otherwise, the constraints introduced in some tables will give you an error.
*/

create database EvoNAPS; 

DROP TABLE IF EXISTS dataorigin;
CREATE TABLE dataorigin (
  DATABASE_KEY int(11) NOT NULL AUTO_INCREMENT,
  DATABASE_ID varchar(100) NOT NULL COMMENT 'This field holds the name of the source database, which in turn serves as the ID of said database. The entries of this column must be unique.',
  DOI varchar(100) DEFAULT NULL COMMENT 'States the DOI of the paper describing the source database, should there exist one.',
  PUBMED_ID varchar(100) DEFAULT NULL COMMENT 'States the PUBMED-ID of the paper describing the source database, should there exist one.',
  LAST_UPDATED date DEFAULT NULL COMMENT 'States the date the source database was last updated, if available.',
  SEQ_TYPE varchar(100) DEFAULT NULL COMMENT 'States whether the source database holds DNA and/or protein alignments.',
  DESCRIPTION text DEFAULT NULL COMMENT 'A text field that gives a short description of the source database.',
  SIZE text DEFAULT NULL COMMENT 'States the number of alignments the source database holds.',
  COMMENT text DEFAULT NULL COMMENT 'An optional text field for any comments regarding the source database.',
  PRIMARY KEY (DATABASE_KEY),
  UNIQUE KEY DATABASE_ID (DATABASE_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The dataorigin table holds information regarding the original databases the alignments in the EvoNAPS database stem from.';

DROP TABLE IF EXISTS aa_models;
CREATE TABLE aa_models (
  MODEL_KEY int(11) NOT NULL AUTO_INCREMENT,
  MODEL_NAME varchar(100) NOT NULL COMMENT 'Name of the protein model (substitution rate matrix). The name must be unique.',
  REGION varchar(50) DEFAULT NULL COMMENT 'States the region of the cell where the proteins from which the substitution rate matrix was derived from are abundant. Optional, default is NULL.',
  EXPLANATION varchar(100) DEFAULT NULL COMMENT 'This field contains a short description of the model.',
  STAT_DIS_TYPE varchar(50) DEFAULT NULL COMMENT 'This field states whether the state frequencies used in the model are empirical (counted freqeunceis from the alignment) or if the state freqeunceis are predefined and set by the model.',
  FREQ_A DECIMAL(10,9) COMMENT 'The freqeuncy of the amino acid Adenine (A) assumed by the model.',
  FREQ_R DECIMAL(10,9),
  FREQ_N DECIMAL(10,9),
  FREQ_D DECIMAL(10,9),
  FREQ_C DECIMAL(10,9),
  FREQ_Q DECIMAL(10,9),
  FREQ_E DECIMAL(10,9),
  FREQ_G DECIMAL(10,9),
  FREQ_H DECIMAL(10,9),
  FREQ_I DECIMAL(10,9),
  FREQ_L DECIMAL(10,9),
  FREQ_K DECIMAL(10,9),
  FREQ_M DECIMAL(10,9),
  FREQ_F DECIMAL(10,9),
  FREQ_P DECIMAL(10,9),
  FREQ_S DECIMAL(10,9),
  FREQ_T DECIMAL(10,9),
  FREQ_W DECIMAL(10,9),
  FREQ_Y DECIMAL(10,9),
  FREQ_V DECIMAL(10,9),
  RATE_AR DECIMAL(15,9) NOT NULL COMMENT 'The substitution rate from aa A to aa R assumed by the model.',
  RATE_AN DECIMAL(15,9) NOT NULL,
  RATE_AD DECIMAL(15,9) NOT NULL,
  RATE_AC DECIMAL(15,9) NOT NULL,
  RATE_AQ DECIMAL(15,9) NOT NULL,
  RATE_AE DECIMAL(15,9) NOT NULL,
  RATE_AG DECIMAL(15,9) NOT NULL,
  RATE_AH DECIMAL(15,9) NOT NULL,
  RATE_AI DECIMAL(15,9) NOT NULL,
  RATE_AL DECIMAL(15,9) NOT NULL,
  RATE_AK DECIMAL(15,9) NOT NULL,
  RATE_AM DECIMAL(15,9) NOT NULL,
  RATE_AF DECIMAL(15,9) NOT NULL,
  RATE_AP DECIMAL(15,9) NOT NULL,
  RATE_AS DECIMAL(15,9) NOT NULL,
  RATE_AT DECIMAL(15,9) NOT NULL,
  RATE_AW DECIMAL(15,9) NOT NULL,
  RATE_AY DECIMAL(15,9) NOT NULL,
  RATE_AV DECIMAL(15,9) NOT NULL,
  RATE_RN DECIMAL(15,9) NOT NULL,
  RATE_RD DECIMAL(15,9) NOT NULL,
  RATE_RC DECIMAL(15,9) NOT NULL,
  RATE_RQ DECIMAL(15,9) NOT NULL,
  RATE_RE DECIMAL(15,9) NOT NULL,
  RATE_RG DECIMAL(15,9) NOT NULL,
  RATE_RH DECIMAL(15,9) NOT NULL,
  RATE_RI DECIMAL(15,9) NOT NULL,
  RATE_RL DECIMAL(15,9) NOT NULL,
  RATE_RK DECIMAL(15,9) NOT NULL,
  RATE_RM DECIMAL(15,9) NOT NULL,
  RATE_RF DECIMAL(15,9) NOT NULL,
  RATE_RP DECIMAL(15,9) NOT NULL,
  RATE_RS DECIMAL(15,9) NOT NULL,
  RATE_RT DECIMAL(15,9) NOT NULL,
  RATE_RW DECIMAL(15,9) NOT NULL,
  RATE_RY DECIMAL(15,9) NOT NULL,
  RATE_RV DECIMAL(15,9) NOT NULL,
  RATE_ND DECIMAL(15,9) NOT NULL,
  RATE_NC DECIMAL(15,9) NOT NULL,
  RATE_NQ DECIMAL(15,9) NOT NULL,
  RATE_NE DECIMAL(15,9) NOT NULL,
  RATE_NG DECIMAL(15,9) NOT NULL,
  RATE_NH DECIMAL(15,9) NOT NULL,
  RATE_NI DECIMAL(15,9) NOT NULL,
  RATE_NL DECIMAL(15,9) NOT NULL,
  RATE_NK DECIMAL(15,9) NOT NULL,
  RATE_NM DECIMAL(15,9) NOT NULL,
  RATE_NF DECIMAL(15,9) NOT NULL,
  RATE_NP DECIMAL(15,9) NOT NULL,
  RATE_NS DECIMAL(15,9) NOT NULL,
  RATE_NT DECIMAL(15,9) NOT NULL,
  RATE_NW DECIMAL(15,9) NOT NULL,
  RATE_NY DECIMAL(15,9) NOT NULL,
  RATE_NV DECIMAL(15,9) NOT NULL,
  RATE_DC DECIMAL(15,9) NOT NULL,
  RATE_DQ DECIMAL(15,9) NOT NULL,
  RATE_DE DECIMAL(15,9) NOT NULL,
  RATE_DG DECIMAL(15,9) NOT NULL,
  RATE_DH DECIMAL(15,9) NOT NULL,
  RATE_DI DECIMAL(15,9) NOT NULL,
  RATE_DL DECIMAL(15,9) NOT NULL,
  RATE_DK DECIMAL(15,9) NOT NULL,
  RATE_DM DECIMAL(15,9) NOT NULL,
  RATE_DF DECIMAL(15,9) NOT NULL,
  RATE_DP DECIMAL(15,9) NOT NULL,
  RATE_DS DECIMAL(15,9) NOT NULL,
  RATE_DT DECIMAL(15,9) NOT NULL,
  RATE_DW DECIMAL(15,9) NOT NULL,
  RATE_DY DECIMAL(15,9) NOT NULL,
  RATE_DV DECIMAL(15,9) NOT NULL,
  RATE_CQ DECIMAL(15,9) NOT NULL,
  RATE_CE DECIMAL(15,9) NOT NULL,
  RATE_CG DECIMAL(15,9) NOT NULL,
  RATE_CH DECIMAL(15,9) NOT NULL,
  RATE_CI DECIMAL(15,9) NOT NULL,
  RATE_CL DECIMAL(15,9) NOT NULL,
  RATE_CK DECIMAL(15,9) NOT NULL,
  RATE_CM DECIMAL(15,9) NOT NULL,
  RATE_CF DECIMAL(15,9) NOT NULL,
  RATE_CP DECIMAL(15,9) NOT NULL,
  RATE_CS DECIMAL(15,9) NOT NULL,
  RATE_CT DECIMAL(15,9) NOT NULL,
  RATE_CW DECIMAL(15,9) NOT NULL,
  RATE_CY DECIMAL(15,9) NOT NULL,
  RATE_CV DECIMAL(15,9) NOT NULL,
  RATE_QE DECIMAL(15,9) NOT NULL,
  RATE_QG DECIMAL(15,9) NOT NULL,
  RATE_QH DECIMAL(15,9) NOT NULL,
  RATE_QI DECIMAL(15,9) NOT NULL,
  RATE_QL DECIMAL(15,9) NOT NULL,
  RATE_QK DECIMAL(15,9) NOT NULL,
  RATE_QM DECIMAL(15,9) NOT NULL,
  RATE_QF DECIMAL(15,9) NOT NULL,
  RATE_QP DECIMAL(15,9) NOT NULL,
  RATE_QS DECIMAL(15,9) NOT NULL,
  RATE_QT DECIMAL(15,9) NOT NULL,
  RATE_QW DECIMAL(15,9) NOT NULL,
  RATE_QY DECIMAL(15,9) NOT NULL,
  RATE_QV DECIMAL(15,9) NOT NULL,
  RATE_EG DECIMAL(15,9) NOT NULL,
  RATE_EH DECIMAL(15,9) NOT NULL,
  RATE_EI DECIMAL(15,9) NOT NULL,
  RATE_EL DECIMAL(15,9) NOT NULL,
  RATE_EK DECIMAL(15,9) NOT NULL,
  RATE_EM DECIMAL(15,9) NOT NULL,
  RATE_EF DECIMAL(15,9) NOT NULL,
  RATE_EP DECIMAL(15,9) NOT NULL,
  RATE_ES DECIMAL(15,9) NOT NULL,
  RATE_ET DECIMAL(15,9) NOT NULL,
  RATE_EW DECIMAL(15,9) NOT NULL,
  RATE_EY DECIMAL(15,9) NOT NULL,
  RATE_EV DECIMAL(15,9) NOT NULL,
  RATE_GH DECIMAL(15,9) NOT NULL,
  RATE_GI DECIMAL(15,9) NOT NULL,
  RATE_GL DECIMAL(15,9) NOT NULL,
  RATE_GK DECIMAL(15,9) NOT NULL,
  RATE_GM DECIMAL(15,9) NOT NULL,
  RATE_GF DECIMAL(15,9) NOT NULL,
  RATE_GP DECIMAL(15,9) NOT NULL,
  RATE_GS DECIMAL(15,9) NOT NULL,
  RATE_GT DECIMAL(15,9) NOT NULL,
  RATE_GW DECIMAL(15,9) NOT NULL,
  RATE_GY DECIMAL(15,9) NOT NULL,
  RATE_GV DECIMAL(15,9) NOT NULL,
  RATE_HI DECIMAL(15,9) NOT NULL,
  RATE_HL DECIMAL(15,9) NOT NULL,
  RATE_HK DECIMAL(15,9) NOT NULL,
  RATE_HM DECIMAL(15,9) NOT NULL,
  RATE_HF DECIMAL(15,9) NOT NULL,
  RATE_HP DECIMAL(15,9) NOT NULL,
  RATE_HS DECIMAL(15,9) NOT NULL,
  RATE_HT DECIMAL(15,9) NOT NULL,
  RATE_HW DECIMAL(15,9) NOT NULL,
  RATE_HY DECIMAL(15,9) NOT NULL,
  RATE_HV DECIMAL(15,9) NOT NULL,
  RATE_IL DECIMAL(15,9) NOT NULL,
  RATE_IK DECIMAL(15,9) NOT NULL,
  RATE_IM DECIMAL(15,9) NOT NULL,
  RATE_IF DECIMAL(15,9) NOT NULL,
  RATE_IP DECIMAL(15,9) NOT NULL,
  RATE_IS DECIMAL(15,9) NOT NULL,
  RATE_IT DECIMAL(15,9) NOT NULL,
  RATE_IW DECIMAL(15,9) NOT NULL,
  RATE_IY DECIMAL(15,9) NOT NULL,
  RATE_IV DECIMAL(15,9) NOT NULL,
  RATE_LK DECIMAL(15,9) NOT NULL,
  RATE_LM DECIMAL(15,9) NOT NULL,
  RATE_LF DECIMAL(15,9) NOT NULL,
  RATE_LP DECIMAL(15,9) NOT NULL,
  RATE_LS DECIMAL(15,9) NOT NULL,
  RATE_LT DECIMAL(15,9) NOT NULL,
  RATE_LW DECIMAL(15,9) NOT NULL,
  RATE_LY DECIMAL(15,9) NOT NULL,
  RATE_LV DECIMAL(15,9) NOT NULL,
  RATE_KM DECIMAL(15,9) NOT NULL,
  RATE_KF DECIMAL(15,9) NOT NULL,
  RATE_KP DECIMAL(15,9) NOT NULL,
  RATE_KS DECIMAL(15,9) NOT NULL,
  RATE_KT DECIMAL(15,9) NOT NULL,
  RATE_KW DECIMAL(15,9) NOT NULL,
  RATE_KY DECIMAL(15,9) NOT NULL,
  RATE_KV DECIMAL(15,9) NOT NULL,
  RATE_MF DECIMAL(15,9) NOT NULL,
  RATE_MP DECIMAL(15,9) NOT NULL,
  RATE_MS DECIMAL(15,9) NOT NULL,
  RATE_MT DECIMAL(15,9) NOT NULL,
  RATE_MW DECIMAL(15,9) NOT NULL,
  RATE_MY DECIMAL(15,9) NOT NULL,
  RATE_MV DECIMAL(15,9) NOT NULL,
  RATE_FP DECIMAL(15,9) NOT NULL,
  RATE_FS DECIMAL(15,9) NOT NULL,
  RATE_FT DECIMAL(15,9) NOT NULL,
  RATE_FW DECIMAL(15,9) NOT NULL,
  RATE_FY DECIMAL(15,9) NOT NULL,
  RATE_FV DECIMAL(15,9) NOT NULL,
  RATE_PS DECIMAL(15,9) NOT NULL,
  RATE_PT DECIMAL(15,9) NOT NULL,
  RATE_PW DECIMAL(15,9) NOT NULL,
  RATE_PY DECIMAL(15,9) NOT NULL,
  RATE_PV DECIMAL(15,9) NOT NULL,
  RATE_ST DECIMAL(15,9) NOT NULL,
  RATE_SW DECIMAL(15,9) NOT NULL,
  RATE_SY DECIMAL(15,9) NOT NULL,
  RATE_SV DECIMAL(15,9) NOT NULL,
  RATE_TW DECIMAL(15,9) NOT NULL,
  RATE_TY DECIMAL(15,9) NOT NULL,
  RATE_TV DECIMAL(15,9) NOT NULL,
  RATE_WY DECIMAL(15,9) NOT NULL,
  RATE_WV DECIMAL(15,9) NOT NULL,
  RATE_YV DECIMAL(15,9) NOT NULL,
  PRIMARY KEY (MODEL_KEY),
  UNIQUE KEY aa_model_constraint (MODEL_NAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The aa_models table depicts the assumed substitution rates and amino acid frequencies for the different protein models.';

DROP TABLE IF EXISTS dna_models;
CREATE TABLE dna_models (
  MODEL_KEY int(11) NOT NULL AUTO_INCREMENT,
  MODEL_NAME varchar(100) NOT NULL COMMENT 'Name of the protein model (substitution rate matrix). The name must be unique.',
  FREE_PARAMETERS int NOT NULL COMMENT 'States the number of free parameters of the model.',
  BASE_FREQUENCIES varchar(30) NOT NULL COMMENT 'States whether the assumed base frequencies of the model are equal (0.25 for each base) or unequal.',
  SUBSTITUTION_RATES varchar(100) NOT NULL COMMENT 'States (possible) restrictions the model has on the substitution rates.',
  EXPLANATION varchar(100) DEFAULT NULL COMMENT 'This filed gives a short description of the model.',
  SUBSTITUTION_CODE varchar(100) NOT NULL COMMENT 'This field shows the substitution code of the rate matrix.',
  PRIMARY KEY (MODEL_KEY),
  UNIQUE KEY dna_model_constraint (MODEL_NAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The dna_models table gives an overview of the different DNA substitution rate matrices and the assumptions they make regarding sequence evolution.';


DROP TABLE IF EXISTS aa_branches;
DROP TABLE IF EXISTS aa_trees;
DROP TABLE IF EXISTS aa_modelparameters;
DROP TABLE IF EXISTS aa_sequences;
DROP TABLE IF EXISTS aa_alignments;

CREATE TABLE aa_alignments (
  ALI_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL COMMENT 'Name of the alignment (alignment ID). Must be unique.',
  FROM_DATABASE varchar(100) NOT NULL COMMENT 'States from which original database the alignemnt stems from (e.g. PANDIT). Serves as foreign key to connect to the DataOrigin table.',
  DESCRIPTION varchar(100) DEFAULT NULL COMMENT 'A field that can hold an optional comment regarding the alignment. This can be left blank and the default value is accordingly NULL.',
  SEQUENCES int(11) NOT NULL COMMENT 'This column states how many seqeunces (taxa) the alignemnt holds.',
  COLUMNS int(11) NOT NULL COMMENT 'This column states how many sites (columns) the alignemnt has / states the length of the alignment.',
  PARSIMONY_INFORMATIVE_SITES int(11) NOT NULL COMMENT 'States the number of parsimony informative sites in alignment.',
  SINGELTON_SITES int(11) NOT NULL COMMENT 'States the number of singelton sites in alignment.',
  CONSTANT_SITES int(11) NOT NULL COMMENT 'States the number of singelton sites in alignment.',
  FRAC_WILDCARDS_GAPS DECIMAL(5,4) NOT NULL COMMENT 'States the fraction of wildcards and gaps in the alignment.',
  DISTINCT_PATTERNS int(11) NOT NULL COMMENT 'States the number of distinct patterns in alignment.',
  FAILED_CHI2 int(11) NOT NULL COMMENT 'States the number of sequences that failed the chi2 (chi-squared) test. The test examines whether the nucleotide composition of the sequences matches the mean nucleotide frequencies across all sequences.',
  IDENTICAL_SEQ int(11) DEFAULT NULL COMMENT 'States the number of identical sequences in the alignment, should there be any. Default is NULL.',
  EXCLUDED_SEQ int(11) DEFAULT NULL COMMENT 'States the number of excluded sequences in the alignment, should there be any. Default is NULL.',
  PRIMARY KEY (ALI_KEY),
  UNIQUE KEY ALI_ID (ALI_ID),
  KEY FROM_DATABASE (FROM_DATABASE),
  CONSTRAINT AA_Alignments_ibfk_1 FOREIGN KEY (FROM_DATABASE) REFERENCES dataorigin (DATABASE_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The aa_alignments table holds general information and characteristics regarding each protein alignment in the database.';

CREATE TABLE aa_sequences (
  SEQ_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL COMMENT 'Name of the alignment (alignment ID). Serves as foreign key to connect to the AA_Alignments table.',
  SEQ_INDEX int(11) NOT NULL COMMENT 'This column holds the unique index (integer starting with 1) for each sequence of an alignment.',
  SEQ_NAME varchar(250) NOT NULL COMMENT 'States the name of the sequence as it appears in the original alignment.',
  FRAC_WILDCARDS_GAPS DECIMAL(10,9) DEFAULT NULL COMMENT 'States the fraction of wildcards and gaps in the sequence.',
  CHI2_P_VALUE DECIMAL(7,2) DEFAULT NULL COMMENT 'States the p value of the Chi2 test for the sequence. The Chi2 test tests whether the nucleotide composition of the sequence fits the mean nucleotide frequencies across all sequences in the alignment.',
  CHI2_PASSED tinyint(1) DEFAULT NULL COMMENT 'States whether the sequence passed (1) or failed (0) the Chi2 test. The Chi2 test tests whether the nucleotide composition of the sequence fits the mean nucleotide frequencies across all sequences in the alignment.',
  EXCLUDED int(11) DEFAULT NULL COMMENT 'States whether the sequence has been excluded from the IQ-Tree 2 calculations (without the flag --keep-ident). IQ-Tree 2 excludes a sequence if there are already at least two sequences in the alignment that are completely identical (gaps and wildcards must also match).',
  IDENTICAL_TO varchar(10000) DEFAULT NULL COMMENT 'States to which sequence(s) the sequence is identical to, if such (a) sequence(s) exist(s).',
  FREQ_A DECIMAL(10,9) NOT NULL COMMENT 'States the frequencies of the amino acid Alanine (A) in the sequence.',
  FREQ_R DECIMAL(10,9) NOT NULL,
  FREQ_N DECIMAL(10,9) NOT NULL,
  FREQ_D DECIMAL(10,9) NOT NULL,
  FREQ_C DECIMAL(10,9) NOT NULL,
  FREQ_Q DECIMAL(10,9) NOT NULL,
  FREQ_E DECIMAL(10,9) NOT NULL,
  FREQ_G DECIMAL(10,9) NOT NULL,
  FREQ_H DECIMAL(10,9) NOT NULL,
  FREQ_I DECIMAL(10,9) NOT NULL,
  FREQ_L DECIMAL(10,9) NOT NULL,
  FREQ_K DECIMAL(10,9) NOT NULL,
  FREQ_M DECIMAL(10,9) NOT NULL,
  FREQ_F DECIMAL(10,9) NOT NULL,
  FREQ_P DECIMAL(10,9) NOT NULL,
  FREQ_S DECIMAL(10,9) NOT NULL,
  FREQ_T DECIMAL(10,9) NOT NULL,
  FREQ_W DECIMAL(10,9) NOT NULL,
  FREQ_Y DECIMAL(10,9) NOT NULL,
  FREQ_V DECIMAL(10,9) NOT NULL,
  SEQ mediumtext NOT NULL COMMENT 'This text field contains the sequence (with wildcards and gaps) as it appears in the alignment.',
  PRIMARY KEY (SEQ_KEY),
  UNIQUE KEY aa_seq_constraint (ALI_ID,SEQ_INDEX),
  CONSTRAINT AA_Sequences_ibfk_1 FOREIGN KEY (ALI_ID) REFERENCES aa_alignments (ALI_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The aa_sequences table holds the sequences of each protein alignment in the EvoNAPS database as well as information regarding each sequence.';

CREATE TABLE aa_modelparameters (
  MODELTEST_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL,
  IQTREE_VERSION varchar(100) NOT NULL,
  RANDOM_SEED int NOT NULL,
  TIME_STAMP datetime NOT NULL,
  MODEL_TYPE varchar(100) NOT NULL,
  KEEP_IDENT tinyint(1) DEFAULT NULL,
  MODEL varchar(100) NOT NULL,
  BASE_MODEL varchar(100) NOT NULL,
  MODEL_RATE_HETEROGENEITY varchar(100) DEFAULT NULL,
  NUM_RATE_CAT int(11) DEFAULT NULL,
  LOGL DECIMAL(21,9) NOT NULL,
  AIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_AIC FLOAT NOT NULL,
  CONFIDENCE_AIC tinyint(1) NOT NULL,
  AICC DECIMAL(21,9) NOT NULL,
  WEIGHTED_AICC FLOAT NOT NULL,
  CONFIDENCE_AICC tinyint(1) NOT NULL,
  BIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_BIC FLOAT NOT NULL,
  CONFIDENCE_BIC tinyint(1) NOT NULL,
  CAIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_CAIC FLOAT NOT NULL,
  CONFIDENCE_CAIC tinyint(1) NOT NULL,
  ABIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_ABIC FLOAT NOT NULL,
  CONFIDENCE_ABIC tinyint(1) NOT NULL,
  NUM_FREE_PARAMETERS int(11) NOT NULL,
  NUM_MODEL_PARAMETERS int(11) NOT NULL,
  NUM_BRANCHES int(11) NOT NULL,
  TREE_LENGTH DECIMAL(15,9) NOT NULL,
  PROP_INVAR DECIMAL(10,9) DEFAULT NULL,
  ALPHA DECIMAL(15,9) DEFAULT NULL,
  STAT_FREQ_TYPE varchar(100) DEFAULT NULL,
  STAT_FREQ_A DECIMAL(10,9) NOT NULL,
  STAT_FREQ_R DECIMAL(10,9) NOT NULL,
  STAT_FREQ_N DECIMAL(10,9) NOT NULL,
  STAT_FREQ_D DECIMAL(10,9) NOT NULL,
  STAT_FREQ_C DECIMAL(10,9) NOT NULL,
  STAT_FREQ_Q DECIMAL(10,9) NOT NULL,
  STAT_FREQ_E DECIMAL(10,9) NOT NULL,
  STAT_FREQ_G DECIMAL(10,9) NOT NULL,
  STAT_FREQ_H DECIMAL(10,9) NOT NULL,
  STAT_FREQ_I DECIMAL(10,9) NOT NULL,
  STAT_FREQ_L DECIMAL(10,9) NOT NULL,
  STAT_FREQ_K DECIMAL(10,9) NOT NULL,
  STAT_FREQ_M DECIMAL(10,9) NOT NULL,
  STAT_FREQ_F DECIMAL(10,9) NOT NULL,
  STAT_FREQ_P DECIMAL(10,9) NOT NULL,
  STAT_FREQ_S DECIMAL(10,9) NOT NULL,
  STAT_FREQ_T DECIMAL(10,9) NOT NULL,
  STAT_FREQ_W DECIMAL(10,9) NOT NULL,
  STAT_FREQ_Y DECIMAL(10,9) NOT NULL,
  STAT_FREQ_V DECIMAL(10,9) NOT NULL,
  PROP_CAT_1 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_1 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_2 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_2 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_3 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_3 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_4 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_4 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_5 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_5 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_6 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_6 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_7 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_7 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_8 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_8 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_9 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_9 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_10 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_10 DECIMAL(15,9) DEFAULT NULL,
  PRIMARY KEY (MODELTEST_KEY),
  UNIQUE KEY aa_modeltest_constraint (ALI_ID,TIME_STAMP,MODEL),
  KEY BASE_MODEL (BASE_MODEL),
  CONSTRAINT AA_ModelParameters_ibfk_1 FOREIGN KEY (ALI_ID) REFERENCES aa_alignments (ALI_ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT AA_ModelParameters_ibfk_2 FOREIGN KEY (BASE_MODEL) REFERENCES aa_models (MODEL_NAME) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The dna_modelparameters table holds the results of model evaluation conducted on an AA alignment. The table inlcudes information regarding how each model performed (LogL, AIC,...) and the estimated parameters of each model.';

CREATE TABLE aa_trees (
  TREE_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL,
  IQTREE_VERSION varchar(100) DEFAULT NULL,
  RANDOM_SEED int NOT NULL,
  TIME_STAMP datetime NOT NULL,
  MODEL_TYPE varchar(100) NOT NULL,
  TREE_TYPE varchar(100) NOT NULL,
  CHOICE_CRITERIUM varchar(100) DEFAULT NULL,
  KEEP_IDENT tinyint(1) DEFAULT NULL,
  MODEL varchar(100) NOT NULL,
  BASE_MODEL varchar(100) NOT NULL,
  MODEL_RATE_HETEROGENEITY varchar(100) DEFAULT NULL,
  NUM_RATE_CAT int(11) DEFAULT NULL,
  LOGL DECIMAL(21,9) NOT NULL,
  UNCONSTRAINED_LOGL DECIMAL(21,9) DEFAULT NULL,
  AIC DECIMAL(21,9) NOT NULL,
  AICC DECIMAL(21,9) NOT NULL,
  BIC DECIMAL(21,9) NOT NULL,
  CAIC DECIMAL(21,9) DEFAULT NULL,
  ABIC DECIMAL(21,9) DEFAULT NULL,
  NUM_FREE_PARAMETERS int(11) NOT NULL,
  NUM_MODEL_PARAMETERS int(11) NOT NULL,
  NUM_BRANCHES int(11) NOT NULL,
  PROP_INVAR DECIMAL(10,9) DEFAULT NULL,
  ALPHA DECIMAL(15,9) DEFAULT NULL,
  STAT_FREQ_TYPE varchar(100) DEFAULT NULL,
  STAT_FREQ_A DECIMAL(10,9) NOT NULL,
  STAT_FREQ_R DECIMAL(10,9) NOT NULL,
  STAT_FREQ_N DECIMAL(10,9) NOT NULL,
  STAT_FREQ_D DECIMAL(10,9) NOT NULL,
  STAT_FREQ_C DECIMAL(10,9) NOT NULL,
  STAT_FREQ_Q DECIMAL(10,9) NOT NULL,
  STAT_FREQ_E DECIMAL(10,9) NOT NULL,
  STAT_FREQ_G DECIMAL(10,9) NOT NULL,
  STAT_FREQ_H DECIMAL(10,9) NOT NULL,
  STAT_FREQ_I DECIMAL(10,9) NOT NULL,
  STAT_FREQ_L DECIMAL(10,9) NOT NULL,
  STAT_FREQ_K DECIMAL(10,9) NOT NULL,
  STAT_FREQ_M DECIMAL(10,9) NOT NULL,
  STAT_FREQ_F DECIMAL(10,9) NOT NULL,
  STAT_FREQ_P DECIMAL(10,9) NOT NULL,
  STAT_FREQ_S DECIMAL(10,9) NOT NULL,
  STAT_FREQ_T DECIMAL(10,9) NOT NULL,
  STAT_FREQ_W DECIMAL(10,9) NOT NULL,
  STAT_FREQ_Y DECIMAL(10,9) NOT NULL,
  STAT_FREQ_V DECIMAL(10,9) NOT NULL,
  PROP_CAT_1 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_1 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_2 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_2 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_3 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_3 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_4 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_4 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_5 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_5 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_6 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_6 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_7 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_7 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_8 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_8 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_9 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_9 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_10 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_10 DECIMAL(15,9) DEFAULT NULL,
  TREE_LENGTH DECIMAL(15,9) NOT NULL,
  SUM_IBL DECIMAL(15,9) NOT NULL,
  TREE_DIAMETER DECIMAL(15,9) NOT NULL,
  DIST_MIN DECIMAL(15,9) DEFAULT NULL,
  DIST_MAX DECIMAL(15,9) DEFAULT NULL,
  DIST_MEAN DECIMAL(15,9) DEFAULT NULL,
  DIST_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  DIST_VAR DECIMAL(15,9) DEFAULT NULL,
  BL_MIN DECIMAL(15,9) DEFAULT NULL,
  BL_MAX DECIMAL(15,9) DEFAULT NULL,
  BL_MEAN DECIMAL(15,9) DEFAULT NULL,
  BL_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  BL_VAR DECIMAL(15,9) DEFAULT NULL,
  IBL_MIN DECIMAL(15,9) DEFAULT NULL,
  IBL_MAX DECIMAL(15,9) DEFAULT NULL,
  IBL_MEAN DECIMAL(15,9) DEFAULT NULL,
  IBL_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  IBL_VAR DECIMAL(15,9) DEFAULT NULL,
  EBL_MIN DECIMAL(15,9) DEFAULT NULL,
  EBL_MAX DECIMAL(15,9) DEFAULT NULL,
  EBL_MEAN DECIMAL(15,9) DEFAULT NULL,
  EBL_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  EBL_VAR DECIMAL(15,9) DEFAULT NULL,
  POT_LBA_7 int(11) DEFAULT NULL,
  POT_LBA_8 int(11) DEFAULT NULL,
  POT_LBA_9 int(11) DEFAULT NULL,
  POT_LBA_10 int(11) DEFAULT NULL,
  NEWICK_STRING mediumtext NOT NULL,
  PRIMARY KEY (TREE_KEY),
  UNIQUE KEY aa_tree_constraint (ALI_ID,TIME_STAMP,TREE_TYPE),
  KEY AA_Trees_foreign_model (BASE_MODEL),
  CONSTRAINT AA_Trees_foreign_model FOREIGN KEY (BASE_MODEL) REFERENCES aa_models (MODEL_NAME) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT AA_Trees_ibfk_1 FOREIGN KEY (ALI_ID) REFERENCES aa_alignments (ALI_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE aa_branches (
  BRANCH_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL,
  TIME_STAMP datetime NOT NULL,
  TREE_TYPE varchar(100) DEFAULT NULL,
  BRANCH_INDEX int(11) NOT NULL,
  BRANCH_TYPE varchar(30) NOT NULL,
  BL DECIMAL(15,9) NOT NULL,
  SPLIT_SIZE int(11) NOT NULL,
  MIN_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MAX_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MEAN_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MEDIAN_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MIN_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  MAX_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  MEAN_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  MEDIAN_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  PRIMARY KEY (BRANCH_KEY),
  UNIQUE KEY aa_branch_constraint (ALI_ID,BRANCH_INDEX,TIME_STAMP,TREE_TYPE),
  KEY ALI_ID (ALI_ID,TIME_STAMP,TREE_TYPE),
  CONSTRAINT AA_Branches_ibfk_1 FOREIGN KEY (ALI_ID, TIME_STAMP, TREE_TYPE) REFERENCES aa_trees (ALI_ID, TIME_STAMP, TREE_TYPE) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS dna_branches;
DROP TABLE IF EXISTS dna_trees;
DROP TABLE IF EXISTS dna_modelparameters;
DROP TABLE IF EXISTS dna_sequences;
DROP TABLE IF EXISTS dna_alignments;


CREATE TABLE dna_alignments (
  ALI_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL COMMENT 'Name of the alignment (alignment ID). Must be unique.',
  FROM_DATABASE varchar(100) NOT NULL COMMENT 'States from which original database the alignment stems from (e.g. PANDIT). Serves as foreign key to connect to the DataOrigin table.',
  DESCRIPTION varchar(100) DEFAULT NULL COMMENT 'A field that can hold an optional comment regarding the alignment. This can be left blank and the default value is accordingly NULL.',
  SEQUENCES int(11) NOT NULL COMMENT 'This column states how many sequences (taxa) the alignment holds.',
  COLUMNS int(11) NOT NULL COMMENT 'This column states how many sites (columns) the alignment has / states the length of the alignment.',
  PARSIMONY_INFORMATIVE_SITES int(11) NOT NULL COMMENT 'States the number of parsimony informative sites in alignment.',
  SINGELTON_SITES int(11) NOT NULL COMMENT 'States the number of singleton sites in alignment.',
  CONSTANT_SITES int(11) NOT NULL COMMENT 'States the number of constant sites in alignment.',
  FRAC_WILDCARDS_GAPS DECIMAL(5,4) NOT NULL COMMENT 'States the fraction of wildcards and gaps in the alignment.',
  DISTINCT_PATTERNS int(11) NOT NULL COMMENT 'States the number of distinct patterns in alignment.',
  FAILED_CHI2 int(11) NOT NULL COMMENT 'States the number of sequences that failed the chi2 (chi-squared) test. The test examines whether the nucleotide composition of the sequences matches the mean nucleotide frequencies across all sequences.',
  IDENTICAL_SEQ int DEFAULT NULL COMMENT 'States the number of identical sequences in the alignment, should there be any. Default is NULL.',
  EXCLUDED_SEQ int DEFAULT NULL COMMENT 'States the number of excluded sequences in the alignment, should there be any. Default is NULL.',
  PRIMARY KEY (ALI_KEY),
  UNIQUE KEY ALI_ID (ALI_ID),
  KEY FROM_DATABASE (FROM_DATABASE),
  CONSTRAINT DNA_Alignments_ibfk_1 FOREIGN KEY (FROM_DATABASE) REFERENCES dataorigin (DATABASE_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The dna_alignments table holds general information and characteristics regarding each DNA alignment in the database.';

CREATE TABLE dna_sequences (
  SEQ_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL COMMENT 'Name of the alignment (alignment ID). Serves as foreign key to connect to the DNA_Alignments table.',
  SEQ_INDEX int(11) NOT NULL COMMENT 'This column holds the unique index (integer starting with 1) for each sequence of an alignment. Together with the alignment ID (ALI_ID) it serves as a constraint (unique key).',
  SEQ_NAME varchar(250) NOT NULL COMMENT 'States the name of the sequence as it appears in the original alignment.',
  FRAC_WILDCARDS_GAPS DECIMAL(10,9) DEFAULT NULL COMMENT 'States the fractions of wildcards and gaps in the sequence.',
  CHI2_P_VALUE DECIMAL(15,9) DEFAULT NULL COMMENT 'States the p value of the Chi2 test for the sequence. The Chi2 test tests whether the nucleotide composition of the sequence fits the mean nucleotide frequencies across all sequences in the alignment.',
  CHI2_PASSED tinyint(1) DEFAULT NULL COMMENT 'States whether the sequence passed (1) or failed (0) the Chi2 test. The Chi2 test tests whether the nucleotide composition of the sequence fits the mean nucleotide frequencies across all sequences in the alignment.',
  EXCLUDED int(11) DEFAULT NULL COMMENT 'States whether the sequence has been excluded from the IQ-Tree 2 calculations (without the flag --keep-ident). IQ-Tree 2 excludes a sequence if there are already at least two sequences in the alignment that are completely identical (gaps and wildcards must also match).',
  IDENTICAL_TO varchar(10000) DEFAULT NULL COMMENT 'States to which sequence(s) the sequence is identical to, if such (a) sequence(s) exist(s).',
  FREQ_A DECIMAL(10,9) NOT NULL COMMENT 'States the frequency of the base Adenine (A) in the sequence.',
  FREQ_C DECIMAL(10,9) NOT NULL COMMENT 'States the frequency of the base Cytosine (C) in the sequence.',
  FREQ_G DECIMAL(10,9) NOT NULL COMMENT 'States the frequency of the base Guanine (G) in the sequence.',
  FREQ_T DECIMAL(10,9) NOT NULL COMMENT 'States the frequency of the base Thymine (T) in the sequence.',
  SEQ mediumtext NOT NULL COMMENT 'This text field contains the sequence (with wildcards and gaps) as it appears in the alignment.',
  PRIMARY KEY (SEQ_KEY),
  UNIQUE KEY dna_seq_constraint (ALI_ID,SEQ_INDEX),
  CONSTRAINT dna_sequences_foreign_key FOREIGN KEY (ALI_ID) REFERENCES dna_alignments (ALI_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The dna_sequences table holds the sequences of each DNA alignment in the EvoNAPS database as well as information regarding each sequence.';

CREATE TABLE dna_modelparameters (
  MODELTEST_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL,
  IQTREE_VERSION varchar(100) NOT NULL,
  RANDOM_SEED int NOT NULL,
  TIME_STAMP datetime NOT NULL,
  MODEL_TYPE varchar(100) NOT NULL,
  KEEP_IDENT tinyint(1) DEFAULT NULL,
  MODEL varchar(100) NOT NULL,
  BASE_MODEL varchar(100) NOT NULL,
  MODEL_RATE_HETEROGENEITY varchar(100) DEFAULT NULL,
  NUM_RATE_CAT int(11) DEFAULT NULL,
  LOGL DECIMAL(21,9) NOT NULL,
  AIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_AIC FLOAT NOT NULL,
  CONFIDENCE_AIC tinyint(1) NOT NULL,
  AICC DECIMAL(21,9) NOT NULL,
  WEIGHTED_AICC FLOAT NOT NULL,
  CONFIDENCE_AICC tinyint(1) NOT NULL,
  BIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_BIC FLOAT NOT NULL,
  CONFIDENCE_BIC tinyint(1) NOT NULL,
  CAIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_CAIC FLOAT NOT NULL,
  CONFIDENCE_CAIC tinyint(1) NOT NULL,
  ABIC DECIMAL(21,9) NOT NULL,
  WEIGHTED_ABIC FLOAT NOT NULL,
  CONFIDENCE_ABIC tinyint(1) NOT NULL,
  NUM_FREE_PARAMETERS int(11) NOT NULL,
  NUM_MODEL_PARAMETERS int(11) NOT NULL,
  NUM_BRANCHES int(11) NOT NULL,
  TREE_LENGTH DECIMAL(15,9) NOT NULL,
  PROP_INVAR DECIMAL(10,9) DEFAULT NULL,
  ALPHA DECIMAL(15,9) DEFAULT NULL,
  STAT_FREQ_TYPE varchar(100) DEFAULT NULL,
  STAT_FREQ_A DECIMAL(10,9) NOT NULL,
  STAT_FREQ_C DECIMAL(10,9) NOT NULL,
  STAT_FREQ_G DECIMAL(10,9) NOT NULL,
  STAT_FREQ_T DECIMAL(10,9) NOT NULL,
  RATE_AC DECIMAL(15,9) NOT NULL,
  RATE_CA DECIMAL(15,9) NOT NULL,
  RATE_AG DECIMAL(15,9) NOT NULL,
  RATE_GA DECIMAL(15,9) NOT NULL,
  RATE_AT DECIMAL(15,9) NOT NULL,
  RATE_TA DECIMAL(15,9) NOT NULL,
  RATE_CG DECIMAL(15,9) NOT NULL,
  RATE_GC DECIMAL(15,9) NOT NULL,
  RATE_CT DECIMAL(15,9) NOT NULL,
  RATE_TC DECIMAL(15,9) NOT NULL,
  RATE_GT DECIMAL(15,9) NOT NULL,
  RATE_TG DECIMAL(15,9) NOT NULL,
  PROP_CAT_1 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_1 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_2 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_2 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_3 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_3 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_4 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_4 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_5 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_5 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_6 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_6 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_7 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_7 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_8 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_8 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_9 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_9 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_10 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_10 DECIMAL(15,9) DEFAULT NULL,
  PRIMARY KEY (MODELTEST_KEY),
  UNIQUE KEY dna_modeltest_constraint (ALI_ID,TIME_STAMP,MODEL),
  KEY BASE_MODEL (BASE_MODEL),
  KEY ON_MODEL_DNA_ModelParameters (MODEL),
  CONSTRAINT DNA_ModelParameters_ibfk_1 FOREIGN KEY (ALI_ID) REFERENCES dna_alignments (ALI_ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT DNA_ModelParameters_ibfk_2 FOREIGN KEY (BASE_MODEL) REFERENCES dna_models (MODEL_NAME) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The dna_modelparameters table holds the results of model evaluation conducted on an DNA alignment. The table inlcudes information regarding how each model performed (LogL, AIC,...) and the estimated parameters of each model.';

CREATE TABLE dna_trees (
  TREE_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL,
  IQTREE_VERSION varchar(100) NOT NULL,
  RANDOM_SEED int NOT NULL,
  TIME_STAMP datetime NOT NULL,
  MODEL_TYPE varchar(100) NOT NULL,
  TREE_TYPE varchar(100) NOT NULL,
  CHOICE_CRITERIUM varchar(100) DEFAULT NULL,
  KEEP_IDENT tinyint(1) DEFAULT NULL,
  MODEL varchar(100) NOT NULL,
  BASE_MODEL varchar(100) NOT NULL,
  MODEL_RATE_HETEROGENEITY varchar(100) DEFAULT NULL,
  NUM_RATE_CAT int(11) DEFAULT NULL,
  LOGL DECIMAL(21,9) NOT NULL,
  UNCONSTRAINED_LOGL DECIMAL(21,9) DEFAULT NULL,
  AIC DECIMAL(21,9) NOT NULL,
  AICC DECIMAL(21,9) NOT NULL,
  BIC DECIMAL(21,9) NOT NULL,
  CAIC DECIMAL(21,9) DEFAULT NULL,
  ABIC DECIMAL(21,9) DEFAULT NULL,
  NUM_FREE_PARAMETERS int(11) NOT NULL,
  NUM_MODEL_PARAMETERS int(11) NOT NULL,
  NUM_BRANCHES int(11) NOT NULL,
  PROP_INVAR DECIMAL(10,9) DEFAULT NULL,
  ALPHA DECIMAL(15,9) DEFAULT NULL,
  STAT_FREQ_TYPE varchar(100) DEFAULT NULL,
  STAT_FREQ_A DECIMAL(10,9) NOT NULL,
  STAT_FREQ_C DECIMAL(10,9) NOT NULL,
  STAT_FREQ_G DECIMAL(10,9) NOT NULL,
  STAT_FREQ_T DECIMAL(10,9) NOT NULL,
  RATE_AC DECIMAL(15,9) NOT NULL,
  RATE_AG DECIMAL(15,9) NOT NULL,
  RATE_AT DECIMAL(15,9) NOT NULL,
  RATE_CA DECIMAL(15,9) NOT NULL,
  RATE_CG DECIMAL(15,9) NOT NULL,
  RATE_CT DECIMAL(15,9) NOT NULL,
  RATE_GA DECIMAL(15,9) NOT NULL,
  RATE_GC DECIMAL(15,9) NOT NULL,
  RATE_GT DECIMAL(15,9) NOT NULL,
  RATE_TA DECIMAL(15,9) NOT NULL,
  RATE_TC DECIMAL(15,9) NOT NULL,
  RATE_TG DECIMAL(15,9) NOT NULL,
  PROP_CAT_1 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_1 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_2 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_2 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_3 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_3 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_4 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_4 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_5 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_5 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_6 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_6 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_7 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_7 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_8 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_8 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_9 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_9 DECIMAL(15,9) DEFAULT NULL,
  PROP_CAT_10 DECIMAL(10,9) DEFAULT NULL,
  REL_RATE_CAT_10 DECIMAL(15,9) DEFAULT NULL,
  TREE_LENGTH DECIMAL(15,9) NOT NULL,
  SUM_IBL DECIMAL(15,9) NOT NULL,
  TREE_DIAMETER DECIMAL(15,9) NOT NULL,
  DIST_MIN DECIMAL(15,9) DEFAULT NULL,
  DIST_MAX DECIMAL(15,9) DEFAULT NULL,
  DIST_MEAN DECIMAL(15,9) DEFAULT NULL,
  DIST_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  DIST_VAR DECIMAL(15,9) DEFAULT NULL,
  BL_MIN DECIMAL(15,9) DEFAULT NULL,
  BL_MAX DECIMAL(15,9) DEFAULT NULL,
  BL_MEAN DECIMAL(15,9) DEFAULT NULL,
  BL_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  BL_VAR DECIMAL(15,9) DEFAULT NULL,
  IBL_MIN DECIMAL(15,9) DEFAULT NULL,
  IBL_MAX DECIMAL(15,9) DEFAULT NULL,
  IBL_MEAN DECIMAL(15,9) DEFAULT NULL,
  IBL_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  IBL_VAR DECIMAL(15,9) DEFAULT NULL,
  EBL_MIN DECIMAL(15,9) DEFAULT NULL,
  EBL_MAX DECIMAL(15,9) DEFAULT NULL,
  EBL_MEAN DECIMAL(15,9) DEFAULT NULL,
  EBL_MEDIAN DECIMAL(15,9) DEFAULT NULL,
  EBL_VAR DECIMAL(15,9) DEFAULT NULL,
  POT_LBA_7 int(11) DEFAULT NULL,
  POT_LBA_8 int(11) DEFAULT NULL,
  POT_LBA_9 int(11) DEFAULT NULL,
  POT_LBA_10 int(11) DEFAULT NULL,
  NEWICK_STRING mediumtext NOT NULL,
  PRIMARY KEY (TREE_KEY),
  UNIQUE KEY dna_tree_constraint (ALI_ID,TIME_STAMP,TREE_TYPE),
  KEY DNA_Trees_foreign_model (BASE_MODEL),
  KEY ON_MODEL_DNA_Trees (MODEL),
  CONSTRAINT DNA_Trees_foreign_model FOREIGN KEY (BASE_MODEL) REFERENCES dna_models (MODEL_NAME) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT DNA_Trees_ibfk_1 FOREIGN KEY (ALI_ID) REFERENCES dna_alignments (ALI_ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE dna_branches (
  BRANCH_KEY int(11) NOT NULL AUTO_INCREMENT,
  ALI_ID varchar(250) NOT NULL,
  TIME_STAMP datetime NOT NULL,
  TREE_TYPE varchar(100) DEFAULT NULL,
  BRANCH_INDEX int(11) NOT NULL,
  BRANCH_TYPE varchar(30) NOT NULL,
  BL DECIMAL(15,9) NOT NULL,
  SPLIT_SIZE int(11) NOT NULL,
  MIN_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MAX_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MEAN_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MEDIAN_PATH_1 DECIMAL(15,9) DEFAULT NULL,
  MIN_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  MAX_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  MEAN_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  MEDIAN_PATH_2 DECIMAL(15,9) DEFAULT NULL,
  PRIMARY KEY (BRANCH_KEY),
  UNIQUE KEY dna_branch_constraint (ALI_ID,BRANCH_INDEX,TIME_STAMP,TREE_TYPE),
  KEY ALI_ID (ALI_ID,TIME_STAMP,TREE_TYPE),
  CONSTRAINT DNA_Branches_ibfk_1 FOREIGN KEY (ALI_ID, TIME_STAMP, TREE_TYPE) REFERENCES dna_trees (ALI_ID, TIME_STAMP, TREE_TYPE) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;