-- generated on: Thu Jan 12 11:37:04 2023
-- input file: ER_dbmodel_Core-2.7.0.xml


-- Copyright (C) 2023 by Diversity Arrays Technology Pty Ltd
--
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.


-- model name: KDDart core ER diagram
-- model version: 2.7.0

-- table activitylog
CREATE TABLE `activitylog` (
  `ActivityLogId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'activity log id',
  `UserId` INTEGER NOT NULL COMMENT 'user id',
  `ActivityDateTime` DATETIME NOT NULL COMMENT 'date time of the activity',
  `ActivityLevel` INTEGER(10) NOT NULL COMMENT 'Logout=2,Incorrect Password=3, Edit=101,Delete=102',
  `ActivityText` VARCHAR(254) NOT NULL COMMENT 'description of activity',
  PRIMARY KEY(`ActivityLogId`)
) ENGINE=InnoDB COMMENT='A log of system user activities';

CREATE  INDEX `xal_UserId` ON `activitylog` (`UserId`);

ALTER TABLE `activitylog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table authorisedsystemgroup
CREATE TABLE `authorisedsystemgroup` (
  `AuthorisedSystemGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'authorised system group id',
  `UserId` INTEGER NOT NULL COMMENT 'user id',
  `SystemGroupId` INTEGER NOT NULL COMMENT 'system group id',
  `IsGroupOwner` TINYINT NOT NULL COMMENT 'flag [0|1] if the user group owner.',
  PRIMARY KEY(`AuthorisedSystemGroupId`)
) ENGINE=InnoDB COMMENT='List of users in system group.';

CREATE  INDEX `xasg_UserId` ON `authorisedsystemgroup` (`UserId`);

CREATE  INDEX `xasg_SystemGroupId` ON `authorisedsystemgroup` (`SystemGroupId`);

CREATE  INDEX `xasg_IsGroupOwner` ON `authorisedsystemgroup` (`IsGroupOwner`);

ALTER TABLE `authorisedsystemgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table contact
CREATE TABLE `contact` (
  `ContactId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'contact id',
  `ContactLastName` VARCHAR(64) NOT NULL COMMENT 'last name',
  `ContactFirstName` VARCHAR(32) NOT NULL COMMENT 'first name',
  `ContactAcronym` VARCHAR(32) NULL COMMENT 'acronym',
  `ContactAddress` VARCHAR(128) NULL COMMENT 'address',
  `ContactTelephone` VARCHAR(14) NULL COMMENT 'phone number',
  `ContactMobile` VARCHAR(14) NULL COMMENT 'mobile number',
  `ContactEMail` VARCHAR(255) NULL COMMENT 'e-mail',
  `OrganisationId` INTEGER NOT NULL COMMENT 'organisation id',
  PRIMARY KEY(`ContactId`)
) ENGINE=InnoDB COMMENT='List of general contacts (not only system users, but also collaborators, material providers, site managers, etc). Links with organisation.';

CREATE  INDEX `xc_LastFirstName` ON `contact` (`ContactLastName`, `ContactFirstName`);

CREATE  INDEX `xc_OrganisationId` ON `contact` (`OrganisationId`);

CREATE  INDEX `xc_ContactEMail` ON `contact` (`ContactEMail`);

ALTER TABLE `contact` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table contactfactor
CREATE TABLE `contactfactor` (
  `ContactId` INTEGER NOT NULL COMMENT 'contact id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'value',
  PRIMARY KEY(`ContactId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for contacts.';

CREATE  INDEX `FactorId` ON `contactfactor` (`FactorId`);

ALTER TABLE `contactfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table designtype
CREATE TABLE `designtype` (
  `DesignTypeId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'design type id',
  `DesignTypeName` VARCHAR(32) NOT NULL COMMENT 'design type name',
  `DesignSoftware` VARCHAR(255) NULL COMMENT 'The executable file of the Software (such as DiGGer) that is used to design the trial of this design type.',
  `DesignTemplateFile` TEXT NULL COMMENT 'The template file defines how the parameter are required to be inserted in the input file for the design software.',
  `DesignGenotypeFormat` VARCHAR(32) NULL COMMENT 'Format in which the Specimen Name and Specimen Id will be exported into the trial design input file.',
  `DesignFactorAliasPrefix` VARCHAR(16) NULL COMMENT 'Prefix that will be used to find the factor for the Trial Design Parameter while importing trial design from the output file generated by the trial design software (such as DiGGer).',
  PRIMARY KEY(`DesignTypeId`)
) ENGINE=InnoDB COMMENT='Each trial (experiment) has a design (layout/method) describing how the genotypes have been planted, the number of replicates, etc. Design type identifies the type of a trial design. For example: - Randomised Block using DiGGer - The trial design of this design type is generated by the DiGGer. - Unknown Design Type. ';

CREATE UNIQUE INDEX `xdt_DesignTypeName` ON `designtype` (`DesignTypeName`);

ALTER TABLE `designtype` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table factor
CREATE TABLE `factor` (
  `FactorId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'factor id',
  `FactorName` VARCHAR(32) NOT NULL COMMENT 'column name',
  `FactorCaption` VARCHAR(64) NULL COMMENT 'caption (shorter version of name)',
  `FactorDescription` VARCHAR(255) NULL COMMENT 'what is stored in the column',
  `TableNameOfFactor` VARCHAR(32) NOT NULL COMMENT 'which main table this factor refers to',
  `FactorDataType` VARCHAR(8) NOT NULL COMMENT 'data type (e.g. VARCHAR)',
  `CanFactorHaveNull` TINYINT(1) NOT NULL COMMENT 'can value be null (0|1)',
  `FactorValueMaxLength` INTEGER(10) NOT NULL COMMENT 'maximum size of value (e.g. 256) refers to the maximum length of FactorValue is VARCHAR in the factor data table like contactfactor',
  `FactorUnit` VARCHAR(16) NULL COMMENT 'value unit (e.g. kg, meters, etc)',
  `OwnGroupId` INTEGER NOT NULL COMMENT 'the group that owns this virtual column definition',
  `Public` TINYINT NOT NULL DEFAULT '0' COMMENT 'if public=1, it means other group administrators can edit and delete this definition',
  `FactorValidRule` VARCHAR(100) NULL COMMENT 'factor value validation rule (optional)',
  `FactorValidRuleErrMsg` VARCHAR(254) NULL COMMENT 'error message if value does not conform to validation rule',
  PRIMARY KEY(`FactorId`)
) ENGINE=InnoDB COMMENT='Main database table containing the definitions of all virtual columns.  A uniform mechanism across the database where all tables with the suffix factor relate to this table.';

CREATE  INDEX `xf_FactorName` ON `factor` (`FactorName`);

CREATE  INDEX `xf_FactorCaption` ON `factor` (`FactorCaption`);

CREATE  INDEX `xf_TableNameOfFactor` ON `factor` (`TableNameOfFactor`);

ALTER TABLE `factor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table factoralias
CREATE TABLE `factoralias` (
  `FactorAliasId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'factoralias id',
  `FactorAliasName` VARCHAR(64) NOT NULL COMMENT 'alternative name of the factor',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  PRIMARY KEY(`FactorAliasId`)
) ENGINE=InnoDB COMMENT='Aliases for factors';

CREATE  INDEX `xfa_FactorAliasName` ON `factoralias` (`FactorAliasName`);

CREATE  INDEX `xfa_FactorId` ON `factoralias` (`FactorId`);

ALTER TABLE `factoralias` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genotype
CREATE TABLE `genotype` (
  `GenotypeId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'genotype id',
  `TaxonomyId` INTEGER NULL COMMENT 'taxonomy id',
  `GenotypeName` VARCHAR(255) NOT NULL COMMENT 'genotype name',
  `GenusId` INTEGER NOT NULL COMMENT 'genus / organism',
  `SpeciesName` VARCHAR(255) NULL COMMENT 'name in Latin - common naming conventions should be established - For use when using different species and a trial from another genus',
  `GenotypeAcronym` VARCHAR(32) NULL COMMENT 'short name of genotype',
  `OriginId` INTEGER(10) NOT NULL COMMENT 'Scource Identifier - possible Part of Plant Variety Rights Information - could refer to organisation or contact',
  `CanPublishGenotype` TINYINT(1) NOT NULL COMMENT 'flag if publicly available',
  `GenotypeColor` VARCHAR(32) NULL COMMENT 'Possibly to utilise as Part of Plant Variety Rights Information',
  `GenotypeNote` VARCHAR(6000) NULL COMMENT 'description',
  `OwnGroupId` INTEGER NOT NULL COMMENT 'group id which owns the record',
  `AccessGroupId` INTEGER NOT NULL DEFAULT '0' COMMENT 'group id with access to the record (different than own group)',
  `OwnGroupPerm` TINYINT NOT NULL COMMENT 'permission for the own group members',
  `AccessGroupPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'permission for the other group members',
  `OtherPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'permission for all the other system users',
  PRIMARY KEY(`GenotypeId`)
) ENGINE=InnoDB COMMENT='List of genotypes available for trial units.  Direct relation to the trial unit maybe sometimes problematic, especially in horticulture when one plant can be a single trial unit and can be a hybrid of a few genotypes.  This is why specimen is established as a subunit of the genotype. In this case one genotype can have more than one specimen (plants, plant groups), which may grow in various locations.  Synonym for genotype can be variety or gemplams and should be used as a generic category. Specimen permission fields are only inherited from this table, using trigger and DAL mechanism.';

CREATE UNIQUE INDEX `xg_GenotypeNameGenusId` ON `genotype` (`GenotypeName`, `GenusId`);

CREATE  INDEX `xg_GenusId` ON `genotype` (`GenusId`);

CREATE  INDEX `xg_OriginId` ON `genotype` (`OriginId`);

CREATE  INDEX `xg_SpeciesName` ON `genotype` (`SpeciesName`);

CREATE  INDEX `xg_OwnGroupId` ON `genotype` (`OwnGroupId`);

CREATE  INDEX `xg_AccessGroupId` ON `genotype` (`AccessGroupId`);

CREATE  INDEX `xg_OwnGroupPerm` ON `genotype` (`OwnGroupPerm`);

CREATE  INDEX `xg_AccessGroupPerm` ON `genotype` (`AccessGroupPerm`);

CREATE  INDEX `xg_OtherPerm` ON `genotype` (`OtherPerm`);

ALTER TABLE `genotype` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genotypealias
CREATE TABLE `genotypealias` (
  `GenotypeAliasId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'genotype alias id',
  `GenotypeAliasName` VARCHAR(255) NOT NULL COMMENT 'genotype alias name',
  `GenotypeId` INTEGER NOT NULL COMMENT 'genotype id',
  `GenotypeAliasType` INTEGER NULL COMMENT 'genotype alias type from generaltype table class genotypealias',
  `GenotypeAliasStatus` INTEGER NULL COMMENT 'status of the alias (e.g. used, preferred, old, etc)',
  `GenotypeAliasLang` VARCHAR(15) NULL COMMENT 'language of the genotype alias name',
  `IsGenotypeName` TINYINT NOT NULL DEFAULT '0' COMMENT 'flag if this name is a current genotype name - can point to only single record in all genotype records',
  `GenusId` INTEGER NOT NULL COMMENT 'genus id - same as for main genotype record - to assure uniqness inside the genus - should be managed by trigger',
  PRIMARY KEY(`GenotypeAliasId`)
) ENGINE=InnoDB COMMENT='One genotype may have many historical names (aliases) under which it has been known.';

CREATE  INDEX `xga_GenotypeAliasName` ON `genotypealias` (`GenotypeAliasName`);

CREATE  INDEX `xga_GenotypeId` ON `genotypealias` (`GenotypeId`);

CREATE  INDEX `xga_GenotypeAliasType` ON `genotypealias` (`GenotypeAliasType`);

CREATE  INDEX `xga_GenotypeAliasStatus` ON `genotypealias` (`GenotypeAliasStatus`);

CREATE  INDEX `xga_GenotypeAliasLang` ON `genotypealias` (`GenotypeAliasLang`);

CREATE UNIQUE INDEX `xga_GenotypeAliasUniq` ON `genotypealias` (`GenusId`, `GenotypeAliasName`);

ALTER TABLE `genotypealias` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genotypefactor
CREATE TABLE `genotypefactor` (
  `GenotypeId` INTEGER NOT NULL COMMENT 'genotype id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'value',
  PRIMARY KEY(`GenotypeId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for genotype descriptions. ';

CREATE  INDEX `FactorId` ON `genotypefactor` (`FactorId`);

ALTER TABLE `genotypefactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genotypetrait
CREATE TABLE `genotypetrait` (
  `GenotypeTraitId` INTEGER(10) NOT NULL AUTO_INCREMENT COMMENT 'genotype trait id',
  `GenotypeId` INTEGER NOT NULL COMMENT 'genotype id',
  `TraitId` INTEGER NOT NULL COMMENT 'trait id',
  `TraitValue` VARCHAR(255) NOT NULL COMMENT 'known trait value, whatever the user specifies it to be, very generic',
  PRIMARY KEY(`GenotypeTraitId`)
) ENGINE=InnoDB COMMENT='Table to store known characteristics for the genotype as a whole e.g. Average Flowing Dates, known disease resistance, observational traits like colour, etc. Useful if there have not been many (or any) trials on a particular genotype and at the same time there is some publicly known data.';

CREATE  INDEX `xgt_GenotypeId` ON `genotypetrait` (`GenotypeId`);

CREATE  INDEX `xgt_TraitId` ON `genotypetrait` (`TraitId`);

ALTER TABLE `genotypetrait` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genus
CREATE TABLE `genus` (
  `GenusId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'genus id',
  `GenusName` VARCHAR(32) NOT NULL COMMENT 'genus name',
  PRIMARY KEY(`GenusId`)
) ENGINE=InnoDB COMMENT='Logical group of genotypes, but not necessary strictly botanically related. Also referred to as crop or organism. ';

CREATE UNIQUE INDEX `xg_GenusName` ON `genus` (`GenusName`);

ALTER TABLE `genus` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table organisation
CREATE TABLE `organisation` (
  `OrganisationId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'organisation id',
  `OrganisationName` VARCHAR(64) NOT NULL COMMENT 'organisation name',
  PRIMARY KEY(`OrganisationId`)
) ENGINE=InnoDB COMMENT='List of collaborating and internal and/or external organisations. ';

CREATE UNIQUE INDEX `xo_OrganisationName` ON `organisation` (`OrganisationName`);

ALTER TABLE `organisation` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table samplemeasurement
CREATE TABLE `samplemeasurement` (
  `TrialUnitId` INTEGER NOT NULL COMMENT 'trial unit id',
  `TraitId` INTEGER NOT NULL COMMENT 'id of the trait being measured',
  `OperatorId` INTEGER NOT NULL COMMENT 'user performing the measurement',
  `InstanceNumber` TINYINT NOT NULL DEFAULT '1' COMMENT 'next consecutive number of the measurement instance if all other values of primary key are the same',
  `SampleTypeId` INTEGER NOT NULL COMMENT 'sample type id',
  `SMGroupId` INTEGER NOT NULL DEFAULT '0' COMMENT 'sample measurement group the measurement if part of - if any',
  `TrialUnitSpecimenId` INTEGER NOT NULL COMMENT 'optional value of trial unit specimen link if measurement is at the level of sub-trialunit',
  `SurveyId` INTEGER NULL COMMENT 'optional value of survey id - if data point comes from a particular survey',
  `MeasureDateTime` DATETIME NOT NULL COMMENT 'date / time of the measurement',
  `TraitValue` VARCHAR(255) NOT NULL COMMENT 'measurement value',
  `StateReason` VARCHAR(30) NULL COMMENT 'optional value state e.g. reason for rejection',
  PRIMARY KEY(`TrialUnitId`, `TraitId`, `OperatorId`, `InstanceNumber`, `SampleTypeId`, `SMGroupId`, `TrialUnitSpecimenId`)
) ENGINE=InnoDB COMMENT='Measurement of the sample from trial unit for a particular trait/variate, by the operator on certain date/time. Measurement can be done for sample type if it does not refer to the entire trial unit.';

CREATE  INDEX `xsm_OperatorId` ON `samplemeasurement` (`OperatorId`);

CREATE  INDEX `xsm_TraitId` ON `samplemeasurement` (`TraitId`);

CREATE  INDEX `xsm_MeasureDateTime` ON `samplemeasurement` (`MeasureDateTime`);

CREATE  INDEX `xsm_SampleTypeId` ON `samplemeasurement` (`SampleTypeId`);

CREATE  INDEX `xsm_SMGroupId` ON `samplemeasurement` (`SMGroupId`);

CREATE  INDEX `xsm_TrialUnitSpecimenId` ON `samplemeasurement` (`TrialUnitSpecimenId`);

CREATE  INDEX `xsm_SurveyId` ON `samplemeasurement` (`SurveyId`);

CREATE  INDEX `xsm_TrialUnitId` ON `samplemeasurement` (`TrialUnitId`);

ALTER TABLE `samplemeasurement` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table site
CREATE TABLE `site` (
  `SiteId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'site id',
  `SiteTypeId` INTEGER NOT NULL COMMENT 'site type id',
  `SiteName` VARCHAR(64) NOT NULL COMMENT 'name',
  `SiteAcronym` VARCHAR(5) NOT NULL COMMENT 'short name of the site, can be used as e.g. part of the trial naming convention',
  `CurrentSiteManagerId` INTEGER NOT NULL COMMENT 'person currently managing the site, not necessarily a user of this system, so linked to the contactId',
  `SiteStartDate` DATETIME NULL COMMENT 'Date when site started to exist',
  `SiteEndDate` DATETIME NULL COMMENT 'Date when site stopped to exist',
  PRIMARY KEY(`SiteId`)
) ENGINE=InnoDB COMMENT='Table storing sites, which may be whole farms, breeding stations or other general environmentally homogeneous areas, where planting occurs. Exact site geographic location is stored in siteloc table.';

CREATE  INDEX `xs_SiteName` ON `site` (`SiteName`);

CREATE  INDEX `xs_CurrentSiteManagerId` ON `site` (`CurrentSiteManagerId`);

CREATE  INDEX `xs_SiteAcronym` ON `site` (`SiteAcronym`);

CREATE  INDEX `xs_SiteStartDate` ON `site` (`SiteStartDate`);

CREATE  INDEX `xs_SiteEndDate` ON `site` (`SiteEndDate`);

ALTER TABLE `site` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table sitefactor
CREATE TABLE `sitefactor` (
  `SiteId` INTEGER NOT NULL COMMENT 'site id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'value',
  PRIMARY KEY(`SiteId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for site table.';

CREATE  INDEX `FactorId` ON `sitefactor` (`FactorId`);

ALTER TABLE `sitefactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table systemgroup
CREATE TABLE `systemgroup` (
  `SystemGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'system group id',
  `SystemGroupName` VARCHAR(64) NOT NULL COMMENT 'system group name',
  `SystemGroupDescription` VARCHAR(255) NOT NULL COMMENT 'system group description',
  PRIMARY KEY(`SystemGroupId`)
) ENGINE=InnoDB COMMENT='Definitions of system groups. Most important in setting record level privileges for many entities.';

CREATE UNIQUE INDEX `xsg_SystemGroupName` ON `systemgroup` (`SystemGroupName`);

ALTER TABLE `systemgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table systemuser
CREATE TABLE `systemuser` (
  `UserId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'user id',
  `UserName` VARCHAR(32) NOT NULL COMMENT 'user name',
  `UserPassword` VARCHAR(128) NOT NULL COMMENT 'user password',
  `PasswordSalt` VARCHAR(64) NOT NULL COMMENT 'password salt (used to hash/encrypt password?)',
  `ContactId` INTEGER NOT NULL COMMENT 'contact id',
  `LastLoginDateTime` DATETIME NULL COMMENT 'date and time of last logon',
  `UserPreference` TEXT NULL COMMENT 'what preferences are stored here and in what format?',
  `UserType` VARCHAR(20) NOT NULL COMMENT 'distinguish between humans and mechanical devices for data input or processing',
  `UserVerification` VARCHAR(255) NULL COMMENT 'token for password reset',
  `UserVerificationDT` DATETIME NULL COMMENT 'password reset date and time',
  PRIMARY KEY(`UserId`)
) ENGINE=InnoDB COMMENT='List of the system users who are authorised to access the system.';

CREATE UNIQUE INDEX `xsu_UserName` ON `systemuser` (`UserName`);

CREATE  INDEX `xsu_ContactId` ON `systemuser` (`ContactId`);

ALTER TABLE `systemuser` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trait
CREATE TABLE `trait` (
  `TraitId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'trait id',
  `UnitId` INTEGER NOT NULL COMMENT 'id of the unit trait will be measured in',
  `TraitGroupTypeId` INTEGER NULL COMMENT 'optional - class traitgroup - (e.g. all plant height related traits can be grouped in plantheight type',
  `TraitName` VARCHAR(32) NOT NULL COMMENT 'trait name',
  `TraitCaption` VARCHAR(64) NOT NULL COMMENT 'trait name (e.g. to display) shorter version or e.g. name without spaces',
  `AltIdentifier` VARCHAR(254) NULL COMMENT 'alternative identifier e.g. code used in another system like trait ontology or other',
  `TraitDescription` VARCHAR(255) NOT NULL COMMENT 'description about trait',
  `TraitDataType` INTEGER NOT NULL COMMENT 'data type as per general type Class traitdatatype (e.g.  DATE, TEXT, CATEGORICAL, ELAPSED_DAYS, INTEGER, DECIMAL) possibly others',
  `TraitValueMaxLength` INTEGER(10) NOT NULL COMMENT 'max length of the value (e.g. 12)',
  `TraitLevel` SET('trialunit','subtrialunit','notetrialunit') NOT NULL DEFAULT 'trialunit' COMMENT 'level at which trait is being used (scored), additional global distinction',
  `IsTraitUsedForAnalysis` TINYINT(1) NOT NULL COMMENT 'flag - can be used to streamline export, e.g export all that need analysis',
  `TraitValRule` VARCHAR(255) NOT NULL COMMENT 'validation rule for the value of the trait',
  `TraitValRuleErrMsg` VARCHAR(255) NOT NULL COMMENT 'error message to display, when validation rule criteria are not met',
  `OwnGroupId` INTEGER NOT NULL COMMENT 'group id owning the record',
  `AccessGroupId` INTEGER NOT NULL DEFAULT '0' COMMENT 'group id with some access to the record',
  `OwnGroupPerm` TINYINT NOT NULL COMMENT 'owning group permissions',
  `AccessGroupPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'other group permissions',
  `OtherPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'all system users permissions',
  PRIMARY KEY(`TraitId`)
) ENGINE=InnoDB COMMENT='Defines the measurement values for the genotype and trial. The specification of a validation rule is optional, however the format must adhere to either a regular or boolean expression. For example a validation rule could be: - Regular Expression TraitValRule=([A-Z]*) - Boolean Expression TraitValRule=(x\g1 and x\k50) ';

CREATE UNIQUE INDEX `xt_TraitName` ON `trait` (`TraitName`);

CREATE  INDEX `xt_TraitCaption` ON `trait` (`TraitCaption`);

CREATE  INDEX `xt_TraitGroupTypeId` ON `trait` (`TraitGroupTypeId`);

CREATE  INDEX `xt_OwnGroupId` ON `trait` (`OwnGroupId`);

CREATE  INDEX `xt_AccessGroupId` ON `trait` (`AccessGroupId`);

CREATE  INDEX `xt_OwnGroupPerm` ON `trait` (`OwnGroupPerm`);

CREATE  INDEX `xt_AccessGroupPerm` ON `trait` (`AccessGroupPerm`);

CREATE  INDEX `xt_OtherPerm` ON `trait` (`OtherPerm`);

CREATE  INDEX `xt_TraitDataType` ON `trait` (`TraitDataType`);

CREATE  INDEX `xt_TraitLevel` ON `trait` (`TraitLevel`);

CREATE UNIQUE INDEX `xt_AltIdentifier` ON `trait` (`AltIdentifier`);

ALTER TABLE `trait` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table traitalias
CREATE TABLE `traitalias` (
  `TraitAliasId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'trait alias id',
  `TraitId` INTEGER NOT NULL COMMENT 'trait id',
  `TraitAliasName` VARCHAR(64) NOT NULL COMMENT 'name of trait alias',
  `TraitAliasCaption` VARCHAR(64) NULL COMMENT 'caption of the trait alias',
  `TraitAliasDescription` VARCHAR(254) NULL COMMENT 'description of the trait alias',
  `TraitAliasValueRuleErrMsg` VARCHAR(254) NULL COMMENT 'value rule error message of the trait alias',
  `TraitLang` VARCHAR(6) NULL COMMENT 'language code (e.g. en for English, sp for Spanish etc) in case trait alias is just a trait translation',
  PRIMARY KEY(`TraitAliasId`)
) ENGINE=InnoDB COMMENT='List of alias names for traits. Sometime for e.g. multinational organisations the same trait can be translated into several languages.  The TraitLang column provides standard language code for translation interfaces. ';

CREATE  INDEX `xta_TraitAliasName` ON `traitalias` (`TraitAliasName`);

CREATE  INDEX `xta_TraitId` ON `traitalias` (`TraitId`);

CREATE  INDEX `xta_TraitLang` ON `traitalias` (`TraitLang`);

ALTER TABLE `traitalias` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table treatment
CREATE TABLE `treatment` (
  `TreatmentId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'treatment id',
  `TreatmentText` VARCHAR(255) NOT NULL COMMENT 'treatment description',
  PRIMARY KEY(`TreatmentId`)
) ENGINE=InnoDB COMMENT='Description (or value) for a treatment for trial unit (plot). Useful if trying to measure response to certain level of irrigations, fertilisation, etc.';

CREATE UNIQUE INDEX `xt_TreatmentText` ON `treatment` (`TreatmentText`);

ALTER TABLE `treatment` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table treatmentfactor
CREATE TABLE `treatmentfactor` (
  `TreatmentId` INTEGER NOT NULL COMMENT 'treatment id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(32) NOT NULL COMMENT 'value',
  PRIMARY KEY(`TreatmentId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for treatment - where more detailed definitions are necessary. ';

CREATE  INDEX `FactorId` ON `treatmentfactor` (`FactorId`);

ALTER TABLE `treatmentfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trial
CREATE TABLE `trial` (
  `TrialId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'trial id',
  `SeasonId` INTEGER NOT NULL COMMENT 'id of the season type from general type - to identify global concept of seasons',
  `ProjectId` INTEGER NULL COMMENT 'id of the project that trial belongs to, it is optional',
  `CurrentWorkflowId` INTEGER NULL COMMENT 'current workflow id to identify which workflow is currently assigned to trial. Optional as trial may not have a workflow assigned at all.',
  `TrialTypeId` INTEGER NOT NULL COMMENT 'trial type id (general type, different from design type, which is trial specific definition)',
  `SiteId` INTEGER NOT NULL COMMENT 'site id, to which trial belongs to',
  `TrialName` VARCHAR(100) NOT NULL COMMENT 'Trial name (can be created as concatenation of site, type, date, number)',
  `TrialNumber` INTEGER NOT NULL COMMENT 'trial running number',
  `TrialAcronym` VARCHAR(30) NOT NULL COMMENT 'alternative short name for a trial',
  `DesignTypeId` INTEGER NOT NULL COMMENT 'design type - relation to design type table',
  `TrialManagerId` INTEGER NOT NULL COMMENT 'person managing trial',
  `TrialStartDate` DATETIME NOT NULL COMMENT 'when started',
  `TrialEndDate` DATETIME NULL COMMENT 'when finished',
  `TrialNote` VARCHAR(6000) NULL COMMENT 'description text',
  `TrialLayout` TEXT NULL COMMENT 'object describing trial units layout - e.g. walking path, relative location of raw1, col1, etc',
  `TULastUpdateTimeStamp` DATETIME NOT NULL COMMENT 'date and time of trial unit last update - used for bulk update of trial units',
  `OwnGroupId` INTEGER NOT NULL COMMENT 'id of the group which owns the record',
  `AccessGroupId` INTEGER NOT NULL DEFAULT '0' COMMENT 'id of the group which have permissions to the record (different to the own group)',
  `OwnGroupPerm` TINYINT NOT NULL COMMENT 'permissions of the own group to the record',
  `AccessGroupPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'permissions of the other group to the record',
  `OtherPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'permissions for all registered users to the record',
  PRIMARY KEY(`TrialId`)
) ENGINE=InnoDB COMMENT='This table contains list of trials (equivalent to field experiments) performed within sites. ';

CREATE  INDEX `xtr_TrialName` ON `trial` (`TrialName`);

CREATE  INDEX `xtr_TrialAcronym` ON `trial` (`TrialAcronym`);

CREATE  INDEX `xtr_ContactId` ON `trial` (`TrialManagerId`);

CREATE  INDEX `xtr_DesignTypeId` ON `trial` (`DesignTypeId`);

CREATE  INDEX `xtr_SiteId` ON `trial` (`SiteId`);

CREATE  INDEX `xtr_TrialTypeId` ON `trial` (`TrialTypeId`);

CREATE  INDEX `xtr_TrialStartDate` ON `trial` (`TrialStartDate`);

CREATE  INDEX `xtr_TrialEndDate` ON `trial` (`TrialEndDate`);

CREATE  INDEX `xtr_CurrentWorkflowId` ON `trial` (`CurrentWorkflowId`);

CREATE  INDEX `xtr_ProjectId` ON `trial` (`ProjectId`);

CREATE  INDEX `xtr_OwnGroupId` ON `trial` (`OwnGroupId`);

CREATE  INDEX `xtr_AccessGroupId` ON `trial` (`AccessGroupId`);

CREATE  INDEX `xtr_OwnGroupPerm` ON `trial` (`OwnGroupPerm`);

CREATE  INDEX `xtr_AccessGroupPerm` ON `trial` (`AccessGroupPerm`);

CREATE  INDEX `xtr_OtherPerm` ON `trial` (`OtherPerm`);

CREATE  INDEX `xtr_SeasonId` ON `trial` (`SeasonId`);

ALTER TABLE `trial` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialfactor
CREATE TABLE `trialfactor` (
  `TrialId` INTEGER NOT NULL COMMENT 'trial id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'value',
  PRIMARY KEY(`TrialId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for trial. ';

CREATE  INDEX `FactorId` ON `trialfactor` (`FactorId`);

ALTER TABLE `trialfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialevent
CREATE TABLE `trialevent` (
  `TrialEventId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'trial event id',
  `UnitId` INTEGER NOT NULL COMMENT '',
  `EventTypeId` INTEGER NOT NULL COMMENT 'trial event type id',
  `TrialId` INTEGER NOT NULL COMMENT 'trial id',
  `OperatorId` INTEGER NOT NULL COMMENT 'person who performed operation',
  `TrialEventValue` VARCHAR(32) NOT NULL COMMENT 'event value (number in the units defined)',
  `TrialEventDate` DATETIME NOT NULL COMMENT 'operation date',
  `TrialEventNote` VARCHAR(254) NULL COMMENT 'additional description of the event',
  PRIMARY KEY(`TrialEventId`)
) ENGINE=InnoDB COMMENT='Trial events are additional descriptors for operations (events) affecting an entire trial.  Example events:  - spray nitrogen 10 tons,  - rain shed over trial during flowering season,  - catastrophic rainfall or thunder, etc.';

CREATE  INDEX `xte_OperatorId` ON `trialevent` (`OperatorId`);

CREATE  INDEX `xte_TrialId` ON `trialevent` (`TrialId`);

CREATE  INDEX `xte_EventTypeId` ON `trialevent` (`EventTypeId`);

CREATE  INDEX `xte_TrialEventDate` ON `trialevent` (`TrialEventDate`);

ALTER TABLE `trialevent` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialeventfactor
CREATE TABLE `trialeventfactor` (
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `TrialEventId` INTEGER NOT NULL COMMENT 'trial event id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'value',
  PRIMARY KEY(`FactorId`, `TrialEventId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns descriptors for trial events. Some events (treatments) done to the trial. Can be a record of history, what was happening on the trial.';

CREATE  INDEX `FactorId` ON `trialeventfactor` (`FactorId`);

ALTER TABLE `trialeventfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialunit
CREATE TABLE `trialunit` (
  `TrialUnitId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Trial Unit Id',
  `TrialId` INTEGER NOT NULL COMMENT 'trial id',
  `SourceTrialUnitId` INTEGER NULL DEFAULT '0' COMMENT 'Source Trial Unit that identifies the source of the sample used in the Trial Unit of the Trial. For example, the source trial unit for a wheat grain sample used in a milling trial can be a trial unit of a Wheat Variety Evaluation Trial. While importing data, the source file must provide information of one of the Alternate Key of Trial and the Unit Position of the source trial to identify the source trial unit.',
  `ReplicateNumber` INTEGER(10) NOT NULL COMMENT 'replicate number - next instance of the same specimen',
  `TrialUnitBarcode` VARCHAR(254) NULL COMMENT 'barcode of the trial unit (plot)',
  `TrialUnitPosition` VARCHAR(254) NULL COMMENT 'string describing the unit position e.g. can be concatenated list of dimensions',
  `TrialUnitEntryId` INTEGER NULL COMMENT 'the same as unit position, but just a numeric value - useful if wanting to order units in numeric fashion',
  `TrialUnitX` INTEGER NULL COMMENT 'X dimension of the trial unit (can be e.g. row)',
  `TrialUnitY` INTEGER NULL COMMENT 'Y dimension of the trial unit (can be e.g. column)',
  `TrialUnitZ` INTEGER NULL COMMENT 'Z dimension of the trial unit (can be e.g. block)',
  `TrialUnitNote` VARCHAR(254) NULL COMMENT 'additional description for the trial unit',
  `SampleSupplierId` INTEGER(10) NULL COMMENT 'sample supplier id (contact or organisation, no defined relation here). Optional field to define who supplied the the seed. Choosing if this is contact or organisation is a matter for organisations convention.',
  `TrialUnitTypeId` INTEGER NULL COMMENT 'type of trial unit',
  PRIMARY KEY(`TrialUnitId`)
) ENGINE=InnoDB COMMENT='This table stores information about each unit of the trial. It assumes that the unit is homogeneous.  One trial unit can contain more than one specimen (e.g. for hybrid plants or when one plant died and another was replanted in place).  Trial unit is a measured unit, which can be further divided by sample types distinguished within.';

CREATE  INDEX `xtu_TrialUnitPosition` ON `trialunit` (`TrialUnitPosition`);

CREATE  INDEX `xtu_SampleSupplierId` ON `trialunit` (`SampleSupplierId`);

CREATE UNIQUE INDEX `xtu_TrialUnitBarcode` ON `trialunit` (`TrialUnitBarcode`);

CREATE  INDEX `xtu_TrialId` ON `trialunit` (`TrialId`);

CREATE  INDEX `xtu_TrialUnitEntryId` ON `trialunit` (`TrialUnitEntryId`);

CREATE  INDEX `xtu_TrialUnitX` ON `trialunit` (`TrialUnitX`);

CREATE  INDEX `xtu_TrialUnitY` ON `trialunit` (`TrialUnitY`);

CREATE  INDEX `xtu_TrialUnitZ` ON `trialunit` (`TrialUnitZ`);

CREATE  INDEX `xtu_SourceTrialUnitId` ON `trialunit` (`SourceTrialUnitId`);

ALTER TABLE `trialunit` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genotypespecimen
CREATE TABLE `genotypespecimen` (
  `GenotypeSpecimenId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'id of the group of genotype and plant combination',
  `SpecimenId` INTEGER NOT NULL COMMENT 'id of the specimen',
  `GenotypeId` INTEGER NOT NULL COMMENT 'id of the genotype',
  `GenotypeSpecimenType` INTEGER NULL COMMENT 'relation to type - useful when a few genotypes compose specimen and one is of type scion and the other is rootstock',
  `InheritanceFlag` TINYINT NULL DEFAULT '1' COMMENT 'flag to point to the genotype from which specimen would inherit permissions in case there is many genotypes for one specimen. Always on for genotypes having one or more specimens.',
  PRIMARY KEY(`GenotypeSpecimenId`)
) ENGINE=InnoDB COMMENT='Maintains the many to many relationship between genotype and specimen. In situations such as with horticulture it may be necessary to define the specimen (individual plant) using many genotypes (hybrid or chimera plants, e.g. trees which have a different root stock and scion).  Enables the definition of more common cases where multiple specimens from a genotype (e.g. individuals grown in different environments) need to be distinguished.  Type can be added, but is not mandatory.';

CREATE  INDEX `xgp_genotype` ON `genotypespecimen` (`GenotypeId`);

CREATE  INDEX `xgp_specimen` ON `genotypespecimen` (`SpecimenId`);

CREATE  INDEX `xgp_gst` ON `genotypespecimen` (`GenotypeSpecimenType`);

CREATE  INDEX `xgp_InheritanceFlag` ON `genotypespecimen` (`InheritanceFlag`);

ALTER TABLE `genotypespecimen` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table specimen
CREATE TABLE `specimen` (
  `SpecimenId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Individual specimen id',
  `BreedingMethodId` INTEGER NOT NULL COMMENT 'id of the breeding method',
  `SourceCrossingId` INTEGER NULL COMMENT 'id of the crossing - optional this specimen is a product of this particular cross',
  `SpecimenName` VARCHAR(254) NOT NULL COMMENT 'Specimen name',
  `SpecimenBarcode` VARCHAR(64) NULL COMMENT 'Optional specimen barcode - if assigned could be printed on label',
  `IsActive` TINYINT(2) NULL DEFAULT '1' COMMENT 'Set to 0 if we want to indicate that it is no longer in production, program or some other binary switch',
  `Pedigree` TEXT NULL COMMENT 'Could be generated Purdy string from male and female parent ID (or some other than Purdy standard)',
  `SelectionHistory` VARCHAR(254) NULL COMMENT 'Can be siblings clones etc, where genotype name is the same. pulses use this a lot',
  `FilialGeneration` INTEGER NULL COMMENT 'Level of specimens being selfed, required when full selection history is not available',
  `SpecimenNote` TEXT NULL COMMENT 'Comments about specimen if applicable',
  `OwnGroupId` INTEGER NOT NULL COMMENT 'group id which owns the record',
  `AccessGroupId` INTEGER NOT NULL DEFAULT '0' COMMENT 'group id which can access the recrod (different than own group)',
  `OwnGroupPerm` TINYINT NOT NULL COMMENT 'permission for group owning the record',
  `AccessGroupPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'permission for access group',
  `OtherPerm` TINYINT NOT NULL DEFAULT '0' COMMENT 'permission for all other system users',
  PRIMARY KEY(`SpecimenId`)
) ENGINE=InnoDB COMMENT='Record for a specimen, i.e. individual plants or their representative groups. May appear to be artificial (i.e. if it is a representation of the whole genotype) however, provides consistency and allows the sampling of more plants (specimens) from the trial unit, as well as composing plant (specimen) from more than one genotype (e.g. in horticulture). Permissions to records are governed by (inherited from) genotype permissions. Relevant db trigger and DAL code takes care for this feature.';

CREATE UNIQUE INDEX `xs_SpecimenName` ON `specimen` (`SpecimenName`);

CREATE UNIQUE INDEX `xs_SpecimenBarcode` ON `specimen` (`SpecimenBarcode`);

CREATE  INDEX `xs_BreedingMethodId` ON `specimen` (`BreedingMethodId`);

CREATE  INDEX `xs_IsActive` ON `specimen` (`IsActive`);

CREATE  INDEX `xs_OwnGroupId` ON `specimen` (`OwnGroupId`);

CREATE  INDEX `xs_AccessGroupId` ON `specimen` (`AccessGroupId`);

CREATE  INDEX `xs_OwnGroupPerm` ON `specimen` (`OwnGroupPerm`);

CREATE  INDEX `xs_AccessGroupPerm` ON `specimen` (`AccessGroupPerm`);

CREATE  INDEX `xs_OtherPerm` ON `specimen` (`OtherPerm`);

CREATE  INDEX `xs_SourceCrossingId` ON `specimen` (`SourceCrossingId`);

ALTER TABLE `specimen` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialunitspecimen
CREATE TABLE `trialunitspecimen` (
  `TrialUnitSpecimenId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal trail unit specimen id',
  `TrialUnitId` INTEGER NOT NULL COMMENT 'trial unit id',
  `SpecimenId` INTEGER NOT NULL COMMENT 'id of the planted specimen',
  `ItemId` INTEGER NULL COMMENT 'source item (e.g. seed bag) for this particular trial unit - having it here allows to use different seed bags for each planted specimen if this is a case, link is optional as the seed source may come from other places',
  `PlantDate` DATE NULL COMMENT 'date when specimen has been planted in the trial unit',
  `HarvestDate` DATE NULL COMMENT 'date when specimen has been harvested from the trial unit',
  `HasDied` TINYINT NULL DEFAULT '0' COMMENT 'flag if specimen died',
  `Notes` VARCHAR(254) NULL COMMENT 'additional notes',
  `SpecimenNumber` INTEGER NOT NULL DEFAULT '0' COMMENT 'distinguish between instances of the same specimen in the same trial unit - just number them',
  `TUSLabel` VARCHAR(254) NULL COMMENT 'label for trial unit specimen - anything to make label on individual',
  `TUSBarcode` VARCHAR(254) NULL COMMENT 'barcode for trial unit specimen - if exists must be unique - labelling individual specimen in trial unit',
  PRIMARY KEY(`TrialUnitSpecimenId`)
) ENGINE=InnoDB COMMENT='Maintains the many to many relationship between trialunit and specimens. If specimen is the representation of the whole genotype (or composition of genotypes), it will have a single record here. Allows the sampling of multiple specimens from a unit or even same specimen multiple times in the unit distinguished by specimen number.  For instances of long lived trials (e.g. tree living 30 years) this enables the reassignment of the same plant into different trial units.';

CREATE  INDEX `xtus_SpecimenId` ON `trialunitspecimen` (`SpecimenId`);

CREATE  INDEX `xtus_PlantDate` ON `trialunitspecimen` (`PlantDate`);

CREATE  INDEX `xtus_HarvestDate` ON `trialunitspecimen` (`HarvestDate`);

CREATE  INDEX `xtus_ItemId` ON `trialunitspecimen` (`ItemId`);

CREATE UNIQUE INDEX `xtus_USN` ON `trialunitspecimen` (`SpecimenId`, `SpecimenNumber`, `TrialUnitId`);

CREATE UNIQUE INDEX `xtus_TUSBarcode` ON `trialunitspecimen` (`TUSBarcode`);

CREATE  INDEX `xtus_TrialUnitId` ON `trialunitspecimen` (`TrialUnitId`);

ALTER TABLE `trialunitspecimen` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table specimenfactor
CREATE TABLE `specimenfactor` (
  `SpecimenId` INTEGER NOT NULL COMMENT 'specimen id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'value',
  PRIMARY KEY(`SpecimenId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for specimens.';

CREATE  INDEX `FactorId` ON `specimenfactor` (`FactorId`);

ALTER TABLE `specimenfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table specimengroup
CREATE TABLE `specimengroup` (
  `SpecimenGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'id of the group of specimens',
  `SpecimenGroupStatus` INTEGER NULL COMMENT 'current status of specimen group',
  `SpecimenGroupTypeId` INTEGER NOT NULL COMMENT 'id of the specimen group type',
  `SpecimenGroupName` VARCHAR(64) NOT NULL COMMENT 'group name',
  `SpecimenGroupNote` VARCHAR(254) NULL COMMENT 'description',
  `SpecimenGroupCreated` DATETIME NOT NULL COMMENT 'date time when group was created',
  `SpecimenGroupLastUpdate` DATETIME NULL COMMENT 'date time when group was last updated',
  `OwnGroupId` INTEGER NULL COMMENT 'group id which owns the record',
  `AccessGroupId` INTEGER NULL COMMENT 'Group id allowed to access specimen group',
  `OwnGroupPerm` TINYINT NULL COMMENT 'permission for the own group members',
  `AccessGroupPerm` TINYINT NULL COMMENT 'permission for the access group members',
  `OtherPerm` TINYINT NULL COMMENT 'permission for all the other system users',
  PRIMARY KEY(`SpecimenGroupId`)
) ENGINE=InnoDB COMMENT='Defines a name for a group of specimens. Possible applications can be: many plants which went through selfing process; groups for next years testing; groups for genotyping.';

CREATE  INDEX `xsg_SpecimenGroupTypeId` ON `specimengroup` (`SpecimenGroupTypeId`);

CREATE UNIQUE INDEX `xsg_SpecimenGroupName` ON `specimengroup` (`SpecimenGroupName`);

CREATE  INDEX `xsg_SpecimenGroupStatus` ON `specimengroup` (`SpecimenGroupStatus`);

CREATE  INDEX `xsg_SpecimenGroupCreated` ON `specimengroup` (`SpecimenGroupCreated`);

CREATE  INDEX `xsg_SpecimenGroupLastUpdate` ON `specimengroup` (`SpecimenGroupLastUpdate`);

CREATE  INDEX `xsg_AccessGroupId` ON `specimengroup` (`AccessGroupId`);

CREATE  INDEX `xsg_AccessGroupPerm` ON `specimengroup` (`AccessGroupPerm`);

CREATE  INDEX `xsg_OwnGroupId` ON `specimengroup` (`OwnGroupId`);

CREATE  INDEX `xsg_OwnGroupPerm` ON `specimengroup` (`OwnGroupPerm`);

CREATE  INDEX `xsg_OtherPerm` ON `specimengroup` (`OtherPerm`);

ALTER TABLE `specimengroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table specimengroupentry
CREATE TABLE `specimengroupentry` (
  `SpecimenGroupEntryId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'entry id',
  `SpecimenId` INTEGER NOT NULL COMMENT 'specimen id',
  `SpecimenGroupId` INTEGER NOT NULL COMMENT 'specimen group id',
  `SpecimenNote` VARCHAR(254) NULL COMMENT 'special note for this specimen in the group',
  PRIMARY KEY(`SpecimenGroupEntryId`, `SpecimenId`, `SpecimenGroupId`)
) ENGINE=InnoDB COMMENT='Maintains the many to many relationship between specimens and specimens groups. ';

CREATE  INDEX `xsge_SpecimenId` ON `specimengroupentry` (`SpecimenId`);

CREATE  INDEX `xsge_SpecimenGroupId` ON `specimengroupentry` (`SpecimenGroupId`);

ALTER TABLE `specimengroupentry` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table deviceregister
CREATE TABLE `deviceregister` (
  `DeviceRegisterId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Internal id',
  `DeviceTypeId` INTEGER NOT NULL COMMENT 'device type id',
  `DeviceId` VARCHAR(100) NOT NULL COMMENT 'Unique device name / id under which it is registered in database',
  `DeviceNote` VARCHAR(255) NULL COMMENT 'Description of the device',
  `Latitude` DECIMAL(16,14) NULL COMMENT 'Latitude of the device in decimal degrees (-90, 90)',
  `Longitude` DECIMAL(16,13) NULL COMMENT 'Longitude of the device in decimal degrees (-180, 180)',
  `DeviceConf` TEXT NULL COMMENT 'Device configuration store - can be object e.g. json or yaml string etc',
  PRIMARY KEY(`DeviceRegisterId`)
) ENGINE=InnoDB COMMENT='Register of devices, which have access to the database to supply data. Devices to include those that supply environmental data or seed inventory data (e.g. scales) and extend to those that supply for phenotypic measurements.  One device can record more than one data type / parameter to more than one environment layer.';

CREATE UNIQUE INDEX `xdr_DeviceId` ON `deviceregister` (`DeviceId`);

CREATE  INDEX `xdr_DeviceTypeId` ON `deviceregister` (`DeviceTypeId`);

ALTER TABLE `deviceregister` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table pedigree
CREATE TABLE `pedigree` (
  `PedigreeId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal record id',
  `SpecimenId` INTEGER NOT NULL COMMENT 'id of the specimen',
  `ParentSpecimenId` INTEGER NOT NULL COMMENT 'id of another specimen, which is its parent',
  `ParentType` INTEGER NOT NULL COMMENT 'Parent type (male female self) or others as in generaltype table in class parent',
  `ParentTrialUnitSpecimenId` INTEGER NULL COMMENT 'id of the trial unit specimen',
  `SelectionReason` VARCHAR(100) NULL COMMENT 'Short description (optional) why the selection was made',
  `NumberOfSpecimens` INTEGER NULL COMMENT 'Number of Specimens: The number of a specific parent specimen used in a breeding process to make progeny specimen. e.g. FemaleSpecimenName: F; MaleSpecimenName: M; 1xF is crossed with 20xM; Pedigree holds: NumberOfSpecimens Female F is 1; NumberOfSpecimens Male M is 20',
  PRIMARY KEY(`PedigreeId`)
) ENGINE=InnoDB COMMENT='Table, which defines specimen (genotype) pedigrees (genealogy). This design assumes that one specimen may have more than 2 parents, which is never a case in reality, but in some scenarios (e.g. bulk pollination)  this kind of information may need to be preserved.';

CREATE  INDEX `xpe_SpecimenId` ON `pedigree` (`SpecimenId`);

CREATE  INDEX `xpe_ParentSpecimenId` ON `pedigree` (`ParentSpecimenId`);

CREATE  INDEX `xpe_ParentType` ON `pedigree` (`ParentType`);

CREATE UNIQUE INDEX `xpe_SpPaType` ON `pedigree` (`SpecimenId`, `ParentSpecimenId`, `ParentType`);

CREATE  INDEX `xpe_ParentTrialUnitSpecimenId` ON `pedigree` (`ParentTrialUnitSpecimenId`);

ALTER TABLE `pedigree` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table breedingmethod
CREATE TABLE `breedingmethod` (
  `BreedingMethodId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal id',
  `BreedingMethodTypeId` INTEGER NOT NULL COMMENT 'type of breeding method related to class breedingmethod in generaltype table',
  `BreedingMethodName` VARCHAR(100) NOT NULL COMMENT 'breeding method name',
  `BreedingMethodAcronym` VARCHAR(30) NULL COMMENT 'short version of the breeding method name',
  `BreedingMethodNote` TEXT NULL COMMENT 'breeding method short description',
  `BreedingMethodSymbol` VARCHAR(10) NULL COMMENT 'symbol of breeding method (e.g. delimiter in pedigree string)',
  PRIMARY KEY(`BreedingMethodId`)
) ENGINE=InnoDB COMMENT='Controlled vocabulary of breeding methodology';

CREATE  INDEX `xbm_TypeId` ON `breedingmethod` (`BreedingMethodTypeId`);

CREATE UNIQUE INDEX `xbm_Name` ON `breedingmethod` (`BreedingMethodName`);

CREATE UNIQUE INDEX `xbm_Acronym` ON `breedingmethod` (`BreedingMethodAcronym`);

ALTER TABLE `breedingmethod` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table breedingmethodfactor
CREATE TABLE `breedingmethodfactor` (
  `BreedingMethodId` INTEGER NOT NULL COMMENT 'Breeding method id',
  `FactorId` INTEGER NOT NULL COMMENT 'Factor id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'Value of the virtual column for Breeding Method',
  PRIMARY KEY(`BreedingMethodId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual column values for Breeding Methods. ';

CREATE  INDEX `FactorId` ON `breedingmethodfactor` (`FactorId`);

ALTER TABLE `breedingmethodfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table item
CREATE TABLE `item` (
  `ItemId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Id of the stored item',
  `TrialUnitSpecimenId` INTEGER NULL COMMENT 'trial unit specimen id',
  `UnitId` INTEGER NULL COMMENT 'Id of the unit if known for the item',
  `ItemSourceId` INTEGER NULL COMMENT 'Id of the contact (who is the external source)',
  `ContainerTypeId` INTEGER NULL COMMENT 'id of the container type',
  `SpecimenId` INTEGER NOT NULL COMMENT 'id of the specimen (bit redundant with trial unit specimen, but since this it may not be present then at least specimen info is there)',
  `ScaleId` INTEGER NULL COMMENT 'id of the device used to take measurement',
  `StorageId` INTEGER NULL COMMENT 'id of the storage location',
  `ItemTypeId` INTEGER NOT NULL COMMENT 'type of the item',
  `ItemStateId` INTEGER NULL COMMENT 'id of the state description (e.g. damaged, thrown away, etc)',
  `ItemBarcode` VARCHAR(32) NULL COMMENT 'barcode on the item container',
  `Amount` DECIMAL(16,3) NULL COMMENT 'amount of the item in container',
  `DateAdded` DATETIME NOT NULL COMMENT 'date time when added',
  `AddedByUserId` INTEGER NOT NULL COMMENT 'who added',
  `LastMeasuredDate` DATETIME NULL COMMENT 'date time when last updated',
  `LastMeasuredUserId` INTEGER NULL COMMENT 'who last updated',
  `ItemOperation` SET('subsample','group') NULL COMMENT 'in case item is derived from other items by taking sample or grouping (mixing) this can be defined here. Item parentage is defined in itemparent table',
  `ItemNote` VARCHAR(254) NULL COMMENT 'some comments',
  `LastUpdateTimeStamp` DATETIME NOT NULL COMMENT 'last update time',
  PRIMARY KEY(`ItemId`)
) ENGINE=InnoDB COMMENT='Items, such as seed bags, stored in the inventory. A generic term and may be used for as inventory for a variety of material. ';

CREATE  INDEX `xi_ScaleId` ON `item` (`ScaleId`);

CREATE  INDEX `xi_StorageId` ON `item` (`StorageId`);

CREATE  INDEX `xi_ItemTypeId` ON `item` (`ItemTypeId`);

CREATE UNIQUE INDEX `xi_ItemBarcode` ON `item` (`ItemBarcode`);

CREATE  INDEX `xi_AddedByUserId` ON `item` (`AddedByUserId`);

CREATE  INDEX `xi_LastMeasuredUserId` ON `item` (`LastMeasuredUserId`);

CREATE  INDEX `xi_DateAdded` ON `item` (`DateAdded`);

CREATE  INDEX `xi_LastMeasuredDate` ON `item` (`LastMeasuredDate`);

CREATE  INDEX `xi_ItemStateId` ON `item` (`ItemStateId`);

CREATE  INDEX `xi_ItemSourceId` ON `item` (`ItemSourceId`);

CREATE  INDEX `xi_ContainerTypeId` ON `item` (`ContainerTypeId`);

CREATE  INDEX `xi_SpecimenId` ON `item` (`SpecimenId`);

CREATE  INDEX `xi_UnitId` ON `item` (`UnitId`);

CREATE  INDEX `xi_TrialUnitSpecimenId` ON `item` (`TrialUnitSpecimenId`);

ALTER TABLE `item` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table storage
CREATE TABLE `storage` (
  `StorageId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'id of the storage position',
  `StorageBarcode` VARCHAR(64) NULL COMMENT 'barcode of the storage position',
  `StorageLocation` VARCHAR(32) NOT NULL COMMENT 'location of the storage (e.g. building, room, freezer, shelf, etc)',
  `StorageParentId` INTEGER NULL COMMENT 'id of the parent storage (e.g. for room parent storage could be building where the room is located)',
  `StorageDetails` VARCHAR(254) NULL COMMENT 'more info about a storage',
  `StorageNote` VARCHAR(254) NULL COMMENT 'detailed storage description',
  PRIMARY KEY(`StorageId`)
) ENGINE=InnoDB COMMENT='General storage locations in a tree like hierarchy. Allows construction of locations such as: - Building 1 - Room 7 - Rack 5  - Shelf 299.';

CREATE UNIQUE INDEX `xs_StorageBarcode` ON `storage` (`StorageBarcode`);

CREATE  INDEX `xs_StorageLocation` ON `storage` (`StorageLocation`);

CREATE  INDEX `xs_StorageParentId` ON `storage` (`StorageParentId`);

ALTER TABLE `storage` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table itemfactor
CREATE TABLE `itemfactor` (
  `ItemId` INTEGER NOT NULL COMMENT 'item id',
  `FactorId` INTEGER NOT NULL COMMENT 'virtual column id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'value in column for an item id',
  PRIMARY KEY(`ItemId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for items. ';

CREATE  INDEX `FactorId` ON `itemfactor` (`FactorId`);

ALTER TABLE `itemfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table generalunit
CREATE TABLE `generalunit` (
  `UnitId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'unit id',
  `UnitTypeId` INTEGER NULL COMMENT 'optional FK to type (e.g. weight, temperature, length etc) - class unittype',
  `UnitName` VARCHAR(12) NOT NULL COMMENT 'unit name (e.g. kg, dkg, etc)',
  `UnitNote` VARCHAR(254) NULL COMMENT 'some description',
  `UnitSource` VARCHAR(254) NULL COMMENT 'source infromation, convention, etc',
  `UseByItem` TINYINT NOT NULL DEFAULT '0' COMMENT 'flag if want to use for item records',
  `UseByTrait` TINYINT NOT NULL DEFAULT '0' COMMENT 'flag if want to use for trait records',
  `UseByTrialEvent` TINYINT NOT NULL DEFAULT '0' COMMENT 'flag if want to use for trial event records',
  `UseBylayerattrib` TINYINT NOT NULL DEFAULT '0' COMMENT 'flag if want to use in layer attributes in enviro module',
  PRIMARY KEY(`UnitId`)
) ENGINE=InnoDB COMMENT='Defines the units used in various places in the system';

CREATE UNIQUE INDEX `xu_UnitName` ON `generalunit` (`UnitName`);

CREATE  INDEX `xu_UnitTypeId` ON `generalunit` (`UnitTypeId`);

CREATE  INDEX `xu_UseByItem` ON `generalunit` (`UseByItem`);

CREATE  INDEX `xu_UseByTrait` ON `generalunit` (`UseByTrait`);

CREATE  INDEX `xu_UseByTrialEvent` ON `generalunit` (`UseByTrialEvent`);

CREATE  INDEX `xu_UseBylayerattrib` ON `generalunit` (`UseBylayerattrib`);

ALTER TABLE `generalunit` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table itemgroup
CREATE TABLE `itemgroup` (
  `ItemGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Item group id',
  `ItemGroupName` VARCHAR(64) NOT NULL COMMENT 'item group name',
  `ItemGroupNote` VARCHAR(254) NULL COMMENT 'comments about item group',
  `AddedByUser` INTEGER NULL COMMENT 'system user id, who created item group',
  `DateAdded` DATETIME NOT NULL COMMENT 'date time when item group added',
  `Active` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'flag if group active',
  PRIMARY KEY(`ItemGroupId`)
) ENGINE=InnoDB COMMENT='Arbitrary Grouping of inventory items which may assist future retrieval, e.g. to group bags to be later sown in a trial. ';

CREATE UNIQUE INDEX `xig_ItemGroupName` ON `itemgroup` (`ItemGroupName`);

CREATE  INDEX `xig_AddedByUser` ON `itemgroup` (`AddedByUser`);

ALTER TABLE `itemgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table itemgroupentry
CREATE TABLE `itemgroupentry` (
  `ItemId` INTEGER NOT NULL COMMENT 'item id',
  `ItemGroupId` INTEGER NOT NULL COMMENT 'item group id'
) ENGINE=InnoDB COMMENT='Maintains the many to many relationship between the item and itemgroup tables. ';

CREATE  INDEX `xige_ItemId` ON `itemgroupentry` (`ItemId`);

CREATE  INDEX `xige_ItemGroupId` ON `itemgroupentry` (`ItemGroupId`);

ALTER TABLE `itemgroupentry` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table deviceregisterfactor
CREATE TABLE `deviceregisterfactor` (
  `DeviceRegisterId` INTEGER NOT NULL COMMENT 'device register id',
  `FactorId` INTEGER NOT NULL COMMENT 'virtual column id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'value for column and device id',
  PRIMARY KEY(`DeviceRegisterId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns values for deivceregister. ';

CREATE  INDEX `FactorId` ON `deviceregisterfactor` (`FactorId`);

ALTER TABLE `deviceregisterfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialtrait
CREATE TABLE `trialtrait` (
  `TrialTraitId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'record id',
  `UnitId` INTEGER NULL COMMENT 'Alternative unit used in trait measurement, optional, if not provided use unit attached to trait directly',
  `TrialId` INTEGER NOT NULL COMMENT 'trial id',
  `TraitId` INTEGER NOT NULL COMMENT 'trait id',
  `Compulsory` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0|1 flag indicating that this combination is compulsory to measure in the trial',
  PRIMARY KEY(`TrialTraitId`)
) ENGINE=InnoDB COMMENT='Part of the trial definition. List of traits expected to be measured for a trial. This table should be rather treated as a control checkpoint, while inserting real measurements into samplemeasurement table.';

CREATE  INDEX `xtt_TraitId` ON `trialtrait` (`TraitId`);

CREATE  INDEX `xtt_TrialId` ON `trialtrait` (`TrialId`);

ALTER TABLE `trialtrait` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table generaltype
CREATE TABLE `generaltype` (
  `TypeId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'general type id',
  `Class` SET('site','item','container','deviceregister','trial','trialevent','sample','specimengroup','specimengroupstatus','state','parent','itemparent','genotypespecimen','markerdataset','workflow','project','itemlog','plate','genmap','multimedia','tissue','genotypealias','genparent','genotypealiasstatus','traitgroup','unittype','trialgroup','breedingmethod','traitdatatype','season','trialunit','survey','extractdatatype') NOT NULL COMMENT 'class of type - possible values (site, item, container, deviceregister, trial, operation, sample, specimengroup, specimengroupstatus, state, parent, itemparent, genotypespecimen, markerdataset, workflow, project, itemlog, plate, genmap, multimedia, tissue, genotypealias, genparent, genotypealiasstatus, traitgroup, unittype, trialgroup, breedingmethod, traitdatatype, season, trialunit, survey, extractdatatype)',
  `TypeName` VARCHAR(100) NOT NULL COMMENT 'name of the type within notation',
  `TypeNote` VARCHAR(254) NULL COMMENT 'type description',
  `IsTypeActive` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '0|1 flag to indicate if type is active (can be used)',
  `IsFixed` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0|1 flag to indicate if all values in the record should stay fixed',
  `TypeMetaData` TEXT NULL COMMENT 'metadata of type to better describe usage of type',
  PRIMARY KEY(`TypeId`)
) ENGINE=InnoDB COMMENT='Contains global vocabularies of types to support classification. The class column lists the possible classes of types.  It also spans to types in Marker Module. With one exception of designtype table for trial all columns named *type refer to particular class of the types in this table. There are also some *status classifications contained here. Classes are: site, item, container, deviceregister, trial, operation, sample, specimengroup, specimengroupstatus, state, parent, itemparent, genotypespecimen, markerdataset, workflow, project, itemlog, plate, genmap, multimedia, tissue, genotypealias, genparent, genotypealiasstatus, traitgroup, unittype, trialgroup, breedingmethod, traitdatatype, season, trialunit, survey, extractdatatype';

CREATE UNIQUE INDEX `xgt_ClassTypeName` ON `generaltype` (`Class`, `TypeName`);

CREATE  INDEX `xgt_TypeName` ON `generaltype` (`TypeName`);

CREATE  INDEX `xgt_IsTypeActive` ON `generaltype` (`IsTypeActive`);

ALTER TABLE `generaltype` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table generaltypefactor
CREATE TABLE `generaltypefactor` (
  `TypeId` INTEGER NOT NULL COMMENT 'type id',
  `FactorId` INTEGER NOT NULL COMMENT 'virtual column id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'value of the virtual column',
  PRIMARY KEY(`TypeId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for genotypes descriptions. ';

CREATE  INDEX `FactorId` ON `generaltypefactor` (`FactorId`);

ALTER TABLE `generaltypefactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table itemparent
CREATE TABLE `itemparent` (
  `ItemParentId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal id',
  `ItemParentType` INTEGER NOT NULL COMMENT 'item parent type (different than parent type in generaltype table) class - itemparent',
  `ItemId` INTEGER NOT NULL COMMENT 'newly created item id from other items',
  `ParentId` INTEGER NOT NULL COMMENT 'item id of the parent item',
  PRIMARY KEY(`ItemParentId`)
) ENGINE=InnoDB COMMENT='This is a bit complex, but allows full flexibility of splitting one item into many as well as grouping many items into one. Full trace of how the inventory items has been rearranged.';

CREATE  INDEX `xip_ItemId` ON `itemparent` (`ItemId`);

CREATE  INDEX `xip_ParentId` ON `itemparent` (`ParentId`);

CREATE  INDEX `xip_ItemParentType` ON `itemparent` (`ItemParentType`);

CREATE UNIQUE INDEX `xip_ItemIDParentID` ON `itemparent` (`ItemId`, `ParentId`);

ALTER TABLE `itemparent` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table barcodeconf
CREATE TABLE `barcodeconf` (
  `BarcodeConfId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'configuration id',
  `SystemTable` VARCHAR(32) NOT NULL COMMENT 'configuration is for this table in the system',
  `SystemField` VARCHAR(32) NOT NULL COMMENT 'configuration is for this field (in the SystemTable)',
  `BarcodeCode` VARCHAR(12) NOT NULL COMMENT 'Name of barcode system (e.g. EAN13, Code39, QR, etc)',
  `BarcodeDef` TEXT NOT NULL COMMENT 'String with barcode definition',
  PRIMARY KEY(`BarcodeConfId`)
) ENGINE=InnoDB COMMENT='Table to store the configuration for barcodes used in the system. DAL should accept any barcode string in any table as long as it is unique.  This table is an optional storage of information on how to generate barcode string automatically.  Conventions for definitions are a subject for separate documentation.';


ALTER TABLE `barcodeconf` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table workflow
CREATE TABLE `workflow` (
  `WorkflowId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'workflow internal id',
  `WorkflowName` VARCHAR(100) NOT NULL COMMENT 'workflow name',
  `WorkflowType` INTEGER NOT NULL COMMENT 'workflow type',
  `WorkflowNote` VARCHAR(254) NULL COMMENT 'some description about workflow',
  `IsActive` INTEGER NOT NULL DEFAULT '1' COMMENT 'flag if it is active',
  PRIMARY KEY(`WorkflowId`)
) ENGINE=InnoDB COMMENT='Contains workflows used in the system for trial workflows and can also be used to link to other processes.  Caters for the definition of multiple workflows for different situations.';

CREATE UNIQUE INDEX `wf_name` ON `workflow` (`WorkflowName`);

CREATE  INDEX `wf_type` ON `workflow` (`WorkflowType`);

CREATE  INDEX `wf_active` ON `workflow` (`IsActive`);

ALTER TABLE `workflow` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table workflowdef
CREATE TABLE `workflowdef` (
  `WorkflowdefId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'workflow step id',
  `WorkflowId` INTEGER NOT NULL COMMENT 'workflow id - this step is part of',
  `StepName` VARCHAR(100) NOT NULL COMMENT 'step name',
  `StepOrder` TINYINT NOT NULL DEFAULT '0' COMMENT 'step order',
  `StepNote` VARCHAR(254) NULL COMMENT 'step description',
  PRIMARY KEY(`WorkflowdefId`)
) ENGINE=InnoDB COMMENT='List of steps for a workflow. StepOrder allows steps to be sorted as required. ';

CREATE  INDEX `wfd_workflow` ON `workflowdef` (`WorkflowId`);

CREATE  INDEX `wfd_name` ON `workflowdef` (`StepName`);

CREATE UNIQUE INDEX `wfd_workflowname` ON `workflowdef` (`WorkflowId`, `StepName`);

CREATE UNIQUE INDEX `wfd_workfloworder` ON `workflowdef` (`WorkflowId`, `StepOrder`);

ALTER TABLE `workflowdef` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialworkflow
CREATE TABLE `trialworkflow` (
  `TrialWorkflowId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal id of trial workflow step',
  `WorkflowdefId` INTEGER NOT NULL COMMENT 'id of workflow step',
  `TrialId` INTEGER NOT NULL COMMENT 'id of the trial the workflow is attached to',
  `CompleteBy` DATETIME NULL COMMENT 'optional deadline to complete step',
  `Completed` TINYINT NOT NULL DEFAULT '0' COMMENT 'flag if completed - default 0',
  `ReminderAt` DATETIME NULL COMMENT 'optional date and time of reminder',
  `ReminderTo` VARCHAR(254) NULL COMMENT 'optional e-mail list where to send reminders to',
  `Note` TEXT NULL COMMENT 'notes about this step',
  PRIMARY KEY(`TrialWorkflowId`)
) ENGINE=InnoDB COMMENT='Some trials follow specific workflows and even if not everything is reflected in the database, certain steps are required to be done and recorded. This table could be such a record of each step or current status of the trial.';

CREATE  INDEX `twf_workflowdef` ON `trialworkflow` (`WorkflowdefId`);

CREATE  INDEX `twf_trial` ON `trialworkflow` (`TrialId`);

ALTER TABLE `trialworkflow` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table project
CREATE TABLE `project` (
  `ProjectId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'project id',
  `ProjectManagerId` INTEGER NOT NULL COMMENT 'manager of the project, link to contact table',
  `TypeId` INTEGER NOT NULL COMMENT 'project type, link to general type table class project',
  `ProjectName` VARCHAR(254) NOT NULL COMMENT 'project name',
  `ProjectStatus` VARCHAR(254) NULL COMMENT 'project status (e.g. stage 2, confirmed, discontinued, etc)',
  `ProjectStartDate` DATETIME NULL COMMENT 'start project date',
  `ProjectEndDate` DATETIME NULL COMMENT 'end project date',
  `ProjectNote` TEXT NULL COMMENT 'project general description',
  PRIMARY KEY(`ProjectId`)
) ENGINE=InnoDB COMMENT='A very generic table to enable the grouping of trials according to some organisational distinction.  Projects may have very different requirements therefore it maybe more appropriate to use the factor column to standardise data within the organisation.';

CREATE  INDEX `xp_ProjectManagerId` ON `project` (`ProjectManagerId`);

CREATE  INDEX `xp_TypeId` ON `project` (`TypeId`);

CREATE UNIQUE INDEX `xp_ProjectName` ON `project` (`ProjectName`);

CREATE  INDEX `xp_ProjectStartDate` ON `project` (`ProjectStartDate`);

CREATE  INDEX `xp_ProjectEndDate` ON `project` (`ProjectEndDate`);

CREATE  INDEX `xp_ProjectStatus` ON `project` (`ProjectStatus`);

ALTER TABLE `project` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table projectfactor
CREATE TABLE `projectfactor` (
  `ProjectId` INTEGER NOT NULL COMMENT 'project id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'value for project factor',
  PRIMARY KEY(`ProjectId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for projects. ';

CREATE  INDEX `FactorId` ON `projectfactor` (`FactorId`);

ALTER TABLE `projectfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genotypealiasfactor
CREATE TABLE `genotypealiasfactor` (
  `GenotypeAliasId` INTEGER NOT NULL COMMENT 'genotype alias id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'factor value',
  PRIMARY KEY(`GenotypeAliasId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional info about genotype aliases';

CREATE  INDEX `FactorId` ON `genotypealiasfactor` (`FactorId`);

ALTER TABLE `genotypealiasfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table multimedia
CREATE TABLE `multimedia` (
  `MultimediaId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal id',
  `SystemTable` SET('genotype', 'specimen', 'project', 'site', 'trial', 'trialunit', 'item', 'extract', 'survey') NOT NULL COMMENT 'name of the supported system table, for which file is attached',
  `RecordId` INTEGER NOT NULL COMMENT 'record id in the table',
  `OperatorId` INTEGER NOT NULL COMMENT 'system user, who uploaded (updated) the file',
  `FileType` INTEGER NOT NULL COMMENT 'file type (e.g. csv table, fasta sequence, image, video, etc)',
  `OrigFileName` VARCHAR(254) NOT NULL COMMENT 'name of the original file',
  `HashFileName` VARCHAR(64) NOT NULL COMMENT 'hash of the orignial file name',
  `UploadTime` DATETIME NOT NULL COMMENT 'time of upload, update of the file',
  `FileExtension` VARCHAR(10) NULL COMMENT 'file extension',
  `MultimediaNote` TEXT NULL COMMENT 'extended description of the multimedia',
  PRIMARY KEY(`MultimediaId`)
) ENGINE=InnoDB COMMENT='This table stores references for multimedia files stored in KDDart system. Files will be attached to particular records in other tables, to e.g. illustrate processes, events or document.';

CREATE  INDEX `xmme_SystemTable` ON `multimedia` (`SystemTable`);

CREATE  INDEX `xmme_RecordId` ON `multimedia` (`RecordId`);

CREATE  INDEX `xmme_OperatorId` ON `multimedia` (`OperatorId`);

CREATE  INDEX `xmme_FileType` ON `multimedia` (`FileType`);

ALTER TABLE `multimedia` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table itemlog
CREATE TABLE `itemlog` (
  `ItemLogId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'item log id',
  `LogTypeId` INTEGER NOT NULL COMMENT 'type, action to log',
  `SystemUserId` INTEGER NOT NULL COMMENT 'who did/log that action',
  `ItemId` INTEGER NOT NULL COMMENT 'item it was logged for',
  `LogDateTime` DATETIME NOT NULL COMMENT 'date time of action',
  `LogMessage` VARCHAR(254) NOT NULL COMMENT 'message or info logged',
  PRIMARY KEY(`ItemLogId`)
) ENGINE=InnoDB COMMENT='Logging actions, changes done to each inventory item';

CREATE  INDEX `xil_LogTypeId` ON `itemlog` (`LogTypeId`);

CREATE  INDEX `xil_SystemUserId` ON `itemlog` (`SystemUserId`);

CREATE  INDEX `xil_ItemId` ON `itemlog` (`ItemId`);

CREATE  INDEX `xil_LogDateTime` ON `itemlog` (`LogDateTime`);

ALTER TABLE `itemlog` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table organisationfactor
CREATE TABLE `organisationfactor` (
  `OrganisationId` INTEGER NOT NULL COMMENT 'organisation id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'factor value',
  PRIMARY KEY(`OrganisationId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional columns for organisations if needed';

CREATE  INDEX `FactorId` ON `organisationfactor` (`FactorId`);

ALTER TABLE `organisationfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table genpedigree
CREATE TABLE `genpedigree` (
  `GenPedigreeId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'GenPedigree id',
  `GenotypeId` INTEGER NOT NULL COMMENT 'id of the genotype',
  `ParentGenotypeId` INTEGER NOT NULL COMMENT 'id of the parent genotype',
  `GenParentType` INTEGER NOT NULL COMMENT 'what is the type of parent (e.g. male, female, self, etc)',
  `NumberOfGenotypes` INTEGER NULL COMMENT 'optional number of parent genotypes perhaps useful to store',
  PRIMARY KEY(`GenPedigreeId`)
) ENGINE=InnoDB COMMENT='This is the same as pedigree table for specimen, but allows to keep pedigree at genotype level if needed - e.g. some generalization is required, while specimen pedigree can be utilized for e.g. selection history only. Gives flexibility to users to select one or use both constructs to track parent/child relationships.';

CREATE  INDEX `xgp_GenotypeId` ON `genpedigree` (`GenotypeId`);

CREATE  INDEX `xgp_ParentGenotypeId` ON `genpedigree` (`ParentGenotypeId`);

CREATE  INDEX `xgp_GenParentType` ON `genpedigree` (`GenParentType`);

CREATE UNIQUE INDEX `xgp_GePaType` ON `genpedigree` (`GenotypeId`, `ParentGenotypeId`, `GenParentType`);

ALTER TABLE `genpedigree` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialgroup
CREATE TABLE `trialgroup` (
  `TrialGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Internal trial group id',
  `TrialGroupOwner` INTEGER NOT NULL COMMENT 'system user id who ownes the trial group',
  `TrialGroupType` INTEGER NOT NULL COMMENT 'trial group type from general type class trialgroup',
  `TrialGroupName` VARCHAR(254) NOT NULL COMMENT 'trial group unique name',
  `TrialGroupStart` DATE NULL COMMENT 'trial group or experiment start date',
  `TrialGroupEnd` DATE NULL COMMENT 'trial group or experiment end date',
  `TrialGroupNote` TEXT NULL COMMENT 'Long description of trial group',
  `TrialGroupLayout` TEXT NULL COMMENT 'layout of the trials in the group, e.g. relative positions to each other',
  PRIMARY KEY(`TrialGroupId`)
) ENGINE=InnoDB COMMENT='Multi location trial grouping. Arbitrary grouping of trials, can be used e.g. for multienvironmental trials or group of trials in multi year experiment, which may or may not share the design.';

CREATE UNIQUE INDEX `xtg_Name` ON `trialgroup` (`TrialGroupName`);

CREATE  INDEX `xtg_start` ON `trialgroup` (`TrialGroupStart`);

CREATE  INDEX `xtg_end` ON `trialgroup` (`TrialGroupEnd`);

CREATE  INDEX `xtg_owner` ON `trialgroup` (`TrialGroupOwner`);

CREATE  INDEX `xtg_type` ON `trialgroup` (`TrialGroupType`);

ALTER TABLE `trialgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialgroupentry
CREATE TABLE `trialgroupentry` (
  `TrialGroupEntryId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal trial group entry id',
  `TrialGroupId` INTEGER NOT NULL COMMENT 'trial group id',
  `TrialId` INTEGER NOT NULL COMMENT 'trial id - member of the trial group',
  PRIMARY KEY(`TrialGroupEntryId`)
) ENGINE=InnoDB COMMENT='Many to many link between trials and trial group';

CREATE  INDEX `xtge_trialid` ON `trialgroupentry` (`TrialId`);

CREATE  INDEX `xtge_trialgroupid` ON `trialgroupentry` (`TrialGroupId`);

ALTER TABLE `trialgroupentry` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialgroupfactor
CREATE TABLE `trialgroupfactor` (
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `TrialGroupId` INTEGER NOT NULL COMMENT 'trial group id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'value',
  PRIMARY KEY(`FactorId`, `TrialGroupId`)
) ENGINE=InnoDB COMMENT='Factor columns for trial groups';

CREATE  INDEX `FactorId` ON `trialgroupfactor` (`FactorId`);

ALTER TABLE `trialgroupfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table conversionrule
CREATE TABLE `conversionrule` (
  `ConversionRuleId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'rule id',
  `FromUnitId` INTEGER NOT NULL COMMENT 'id of the from unit',
  `ToUnitId` INTEGER NOT NULL COMMENT 'id of the to unit',
  `ConversionFormula` VARCHAR(7000) NOT NULL DEFAULT 'x * 1' COMMENT 'formula, where x is the value of the from unit and result of the evaluation in a value in to unit. Formula should eval to JavaScript code.',
  PRIMARY KEY(`ConversionRuleId`)
) ENGINE=InnoDB COMMENT='This table defines conversion rules between units stored in the generalunit table. There can be only one rule per unique pair of from and to unit. Conversion formula should be a string, which evals to JavaScript code. JavaScript has engines and parsers on all platforms and in most modern languages, so it is the safest option to use this notation as close to be generic.';

CREATE  INDEX `xcr_FromUnitId` ON `conversionrule` (`FromUnitId`);

CREATE  INDEX `xcr_ToUnitId` ON `conversionrule` (`ToUnitId`);

CREATE UNIQUE INDEX `xcr_UniqueUnits` ON `conversionrule` (`FromUnitId`, `ToUnitId`);

ALTER TABLE `conversionrule` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialdimension
CREATE TABLE `trialdimension` (
  `TrialDimensionId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Dimension id',
  `TrialId` INTEGER NOT NULL COMMENT 'trial id to which dimension refers to',
  `Dimension` SET('X','Y','Z','EntryId','Position') NOT NULL COMMENT 'which trial dimension it is',
  `DimensionName` VARCHAR(100) NOT NULL COMMENT 'name of the dimension e.g Y can be column or range or something else, EntryId can be PlotId or PlotNumber or else',
  PRIMARY KEY(`TrialDimensionId`)
) ENGINE=InnoDB COMMENT='Optional definition of the trial dimensionality. If user wants to define that X dimension is called e.g. row and Y dimension is called column - this is a place to do it. In the future this can be extended to contain trial level information about e.g. plot width, plot height, plot spaces, etc. Trial and dimention combination has to be unique - forced by unique index.';

CREATE  INDEX `xtd_TrialId` ON `trialdimension` (`TrialId`);

CREATE UNIQUE INDEX `xtd_Dimension` ON `trialdimension` (`TrialId`, `Dimension`);

CREATE UNIQUE INDEX `xtd_DimensionName` ON `trialdimension` (`TrialId`, `DimensionName`);

ALTER TABLE `trialdimension` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table keywordgroup
CREATE TABLE `keywordgroup` (
  `KeywordGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'id of the keyword group',
  `KeywordGroupName` VARCHAR(100) NOT NULL COMMENT 'unique name of the keyword group',
  `OperatorId` INTEGER NULL COMMENT 'optional operator id - if defined than group may belong to this particular user',
  PRIMARY KEY(`KeywordGroupId`)
) ENGINE=InnoDB COMMENT='Arbitrary groups of keywords for easy search or other purpose.';

CREATE UNIQUE INDEX `kwg_KeywordGroupName` ON `keywordgroup` (`KeywordGroupName`, `OperatorId`);

CREATE  INDEX `kwg_OperatorId` ON `keywordgroup` (`OperatorId`);

ALTER TABLE `keywordgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table keyword
CREATE TABLE `keyword` (
  `KeywordId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'keyword id',
  `KeywordName` VARCHAR(30) NOT NULL COMMENT 'unique keyword',
  `KeywordNote` VARCHAR(254) NULL COMMENT 'keyword description',
  `OperatorId` INTEGER NULL COMMENT 'optional owner of the keyword',
  PRIMARY KEY(`KeywordId`)
) ENGINE=InnoDB COMMENT='System wide collection of keywords';

CREATE UNIQUE INDEX `kw_KeywordNameOperator` ON `keyword` (`KeywordName`, `OperatorId`);

CREATE  INDEX `kw_OperatorId` ON `keyword` (`OperatorId`);

ALTER TABLE `keyword` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table keywordgroupentry
CREATE TABLE `keywordgroupentry` (
  `KeywordGroupEntryId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'keyword group entry id',
  `KeywordId` INTEGER NOT NULL COMMENT 'keyword id in the group',
  `KeywordGroupId` INTEGER NOT NULL COMMENT 'keyword group id',
  PRIMARY KEY(`KeywordGroupEntryId`)
) ENGINE=InnoDB COMMENT='list of keywords in the keyword group(s)';

CREATE  INDEX `kwge_KeywordId` ON `keywordgroupentry` (`KeywordId`);

CREATE  INDEX `kwge_KeywordGroupId` ON `keywordgroupentry` (`KeywordGroupId`);

ALTER TABLE `keywordgroupentry` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialunitkeyword
CREATE TABLE `trialunitkeyword` (
  `TrialUnitKeywordId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'trial unit keyword id',
  `TrialUnitId` INTEGER NOT NULL COMMENT 'trial unit id',
  `KeywordId` INTEGER NOT NULL COMMENT 'keyword id',
  PRIMARY KEY(`TrialUnitKeywordId`)
) ENGINE=InnoDB COMMENT='keywords for a trial unit';

CREATE  INDEX `tuk_KeywordId` ON `trialunitkeyword` (`KeywordId`);

CREATE  INDEX `tuk_TrialUnitId` ON `trialunitkeyword` (`TrialUnitId`);

ALTER TABLE `trialunitkeyword` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table specimenkeyword
CREATE TABLE `specimenkeyword` (
  `SpecimenKeywordId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'specimen keyword id',
  `KeywordId` INTEGER NOT NULL COMMENT 'keyword id for a specimen',
  `SpecimenId` INTEGER NOT NULL COMMENT 'specimen id',
  PRIMARY KEY(`SpecimenKeywordId`)
) ENGINE=InnoDB COMMENT='keywords for a specimen';

CREATE  INDEX `sky_KeywordId` ON `specimenkeyword` (`KeywordId`);

CREATE  INDEX `sky_SpecimenId` ON `specimenkeyword` (`SpecimenId`);

ALTER TABLE `specimenkeyword` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table crossing
CREATE TABLE `crossing` (
  `CrossingId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Id for crossing record',
  `TrialId` INTEGER NOT NULL COMMENT 'trial (nursery) for which the crossing is designed',
  `BreedingMethodId` INTEGER NOT NULL COMMENT 'type of the cross defined by breeding method - has to be compatible with pedigree table',
  `MaleParentId` INTEGER NOT NULL COMMENT 'id of the male parent - specimen in the trial unit',
  `FemaleParentId` INTEGER NOT NULL COMMENT 'id of the female parent - specimen in the trial unit',
  `CrossingDateTime` DATETIME NULL COMMENT 'date time when crossing performed',
  `UserId` INTEGER NOT NULL COMMENT 'who has done the cross',
  `CrossingNote` VARCHAR(254) NULL COMMENT 'some notes about the cross',
  PRIMARY KEY(`CrossingId`)
) ENGINE=InnoDB COMMENT='A table which stores crossing information for nursery management';

CREATE  INDEX `xcr_trialid` ON `crossing` (`TrialId`);

CREATE  INDEX `xcr_breedingmethodid` ON `crossing` (`BreedingMethodId`);

CREATE  INDEX `xcr_userid` ON `crossing` (`UserId`);

CREATE  INDEX `xcr_maleparentid` ON `crossing` (`MaleParentId`);

CREATE  INDEX `xcr_femaleparentid` ON `crossing` (`FemaleParentId`);

ALTER TABLE `crossing` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table traitgroup
CREATE TABLE `traitgroup` (
  `TraitGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'trait group id',
  `TraitGroupName` VARCHAR(254) NOT NULL COMMENT 'name of the group',
  `AltIdentifier` VARCHAR(254) NULL COMMENT 'alternative identified e.g. code used in another system like trait ontology',
  `OperatorId` INTEGER NULL COMMENT 'optional operator id - if defined than group may belong to this particular user',
  PRIMARY KEY(`TraitGroupId`)
) ENGINE=InnoDB COMMENT='Group of traits. Can be used for various arbitrary purposes.';

CREATE UNIQUE INDEX `trg_TraitGroupName` ON `traitgroup` (`TraitGroupName`, `OperatorId`);

CREATE  INDEX `trg_OperatorId` ON `traitgroup` (`OperatorId`);

CREATE UNIQUE INDEX `trg_AltIdentifier` ON `traitgroup` (`AltIdentifier`);

ALTER TABLE `traitgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table traitgroupentry
CREATE TABLE `traitgroupentry` (
  `TraitGroupEntryId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'trait group entry internal id',
  `TraitId` INTEGER NOT NULL COMMENT 'trait id belonging to the group',
  `TraitGroupId` INTEGER NOT NULL COMMENT 'trait group id',
  PRIMARY KEY(`TraitGroupEntryId`)
) ENGINE=InnoDB COMMENT='List of trait group members';

CREATE  INDEX `tge_TraitId` ON `traitgroupentry` (`TraitId`);

CREATE  INDEX `tge_TraitGroupId` ON `traitgroupentry` (`TraitGroupId`);

ALTER TABLE `traitgroupentry` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table smgroup
CREATE TABLE `smgroup` (
  `SMGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal group id',
  `SMGroupName` VARCHAR(254) NOT NULL COMMENT 'group name - has to be unique',
  `TrialId` INTEGER NOT NULL COMMENT 'trial id measurements belong to - this is a constrain that grouping measurements between trials is not possible',
  `OperatorId` INTEGER NOT NULL COMMENT 'user - owner of the group',
  `SMGroupStatus` VARCHAR(20) NULL COMMENT 'status of the group',
  `SMGroupDateTime` DATETIME NOT NULL COMMENT 'date and time of the group - possibly creation time or last update',
  `SMGroupNote` TEXT NULL COMMENT 'general comments for the group',
  PRIMARY KEY(`SMGroupId`)
) ENGINE=InnoDB COMMENT='Sample measurements for some portions of the trials may be grouped to distinct them from the same measurements, which were done before, but need to be retained or to have two sets which can be compared. It can also be used as means to keep several versions of the same dataset';

CREATE UNIQUE INDEX `smg_SMGroupName` ON `smgroup` (`SMGroupName`);

CREATE  INDEX `smg_TrialId` ON `smgroup` (`TrialId`);

CREATE  INDEX `smg_OperatorId` ON `smgroup` (`OperatorId`);

CREATE  INDEX `smg_SMGroupDateTime` ON `smgroup` (`SMGroupDateTime`);

CREATE  INDEX `smg_SMGroupStatus` ON `smgroup` (`SMGroupStatus`);

ALTER TABLE `smgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table uniquenumber
CREATE TABLE `uniquenumber` (
  `UniqueNumberId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'sytem wide unique number',
  PRIMARY KEY(`UniqueNumberId`)
) ENGINE=InnoDB COMMENT='Just to assure that every number provided in REST interface is always truly system wide unique.';


ALTER TABLE `uniquenumber` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table itemmeasurement
CREATE TABLE `itemmeasurement` (
  `ItemId` INTEGER NOT NULL COMMENT 'item id',
  `TraitId` INTEGER NOT NULL COMMENT 'id of the trait being measured',
  `OperatorId` INTEGER NOT NULL COMMENT 'user performing the measurement',
  `InstanceNumber` INTEGER NOT NULL DEFAULT '1' COMMENT 'next consecutive number of the measurement instance if all other values of primary key are the same',
  `SampleTypeId` INTEGER NOT NULL COMMENT 'sample type id',
  `IMGroupId` INTEGER NOT NULL DEFAULT '0' COMMENT 'sample measurement group the measurement if part of - if any',
  `MeasureDateTime` DATETIME NULL COMMENT 'date / time of the measurement',
  `TraitValue` VARCHAR(255) NULL COMMENT 'measurement value',
  `StateReason` VARCHAR(255) NULL COMMENT 'optional value state e.g. reason for rejection',
  PRIMARY KEY(`ItemId`, `TraitId`, `OperatorId`, `InstanceNumber`, `SampleTypeId`, `IMGroupId`)
) ENGINE=InnoDB COMMENT='Measurement of the sample from item inventory for a particular trait/variate, by the operator on certain date/time. Measurement can be done for sample type if it does not refer to the entire inventory item.';

CREATE  INDEX `xim_TraitId` ON `itemmeasurement` (`TraitId`);

CREATE  INDEX `xim_OperatorId` ON `itemmeasurement` (`OperatorId`);

CREATE  INDEX `xim_SampleTypeId` ON `itemmeasurement` (`SampleTypeId`);

CREATE  INDEX `xim_IMGroupId` ON `itemmeasurement` (`IMGroupId`);

CREATE  INDEX `xim_MeasureDateTime` ON `itemmeasurement` (`MeasureDateTime`);

ALTER TABLE `itemmeasurement` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table imgroup
CREATE TABLE `imgroup` (
  `IMGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal group id',
  `IMGroupName` VARCHAR(255) NOT NULL COMMENT 'group name - has to be unique',
  `OperatorId` INTEGER NOT NULL COMMENT 'user - owner of the group',
  `IMGroupStatus` VARCHAR(20) NULL COMMENT 'status of the group',
  `IMGroupDateTime` DATETIME NOT NULL COMMENT 'date and time of the group - possibly creation time or last update',
  `IMGroupNote` TEXT NULL COMMENT 'general comments for the group',
  PRIMARY KEY(`IMGroupId`)
) ENGINE=InnoDB COMMENT='Item measurements for some portions of the item data may be grouped to distinct them from the same measurements, which were done before, but need to be retained or to have two sets which can be compared. It can also be used as means to keep several versions of the same dataset.';

CREATE UNIQUE INDEX `ximg_IMGroupName` ON `imgroup` (`IMGroupName`);

CREATE  INDEX `ximg_OperatorId` ON `imgroup` (`OperatorId`);

CREATE  INDEX `ximg_IMGroupStatus` ON `imgroup` (`IMGroupStatus`);

CREATE  INDEX `ximg_IMGroupDateTime` ON `imgroup` (`IMGroupDateTime`);

ALTER TABLE `imgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table crossingmeasurement
CREATE TABLE `crossingmeasurement` (
  `CrossingId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'crossing id',
  `TraitId` INTEGER NOT NULL COMMENT 'id of the trait being measured',
  `OperatorId` INTEGER NOT NULL COMMENT 'user performing the measurement',
  `InstanceNumber` TINYINT NOT NULL COMMENT 'next consecutive number of the measurement instance if all other values of primary key are the same',
  `SampleTypeId` INTEGER NOT NULL COMMENT 'sample type id',
  `CMGroupId` INTEGER NOT NULL COMMENT 'crossing measurement group the measurement if part of - if any',
  `MeasureDateTime` DATETIME NULL COMMENT 'date / time of the measurement',
  `TraitValue` VARCHAR(255) NULL COMMENT 'measurement value',
  `StateReason` VARCHAR(30) NULL COMMENT 'optional value state e.g. reason for rejection',
  PRIMARY KEY(`CrossingId`, `TraitId`, `OperatorId`, `InstanceNumber`, `SampleTypeId`, `CMGroupId`)
) ENGINE=InnoDB COMMENT='Measurement of the sample from crossing for a particular trait/variate, by the operator on certain date/time. Measurement can be done for sample type if it does not refer to the entire crossing.';

CREATE  INDEX `xcm_TraitId` ON `crossingmeasurement` (`TraitId`);

CREATE  INDEX `xcm_OperatorId` ON `crossingmeasurement` (`OperatorId`);

CREATE  INDEX `xcm_SampleTypeId` ON `crossingmeasurement` (`SampleTypeId`);

CREATE  INDEX `xcm_MeasureDateTime` ON `crossingmeasurement` (`MeasureDateTime`);

CREATE  INDEX `xcm_CMGroupId` ON `crossingmeasurement` (`CMGroupId`);

ALTER TABLE `crossingmeasurement` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table cmgroup
CREATE TABLE `cmgroup` (
  `CMGroupId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal group id',
  `CMGroupName` VARCHAR(255) NOT NULL COMMENT 'group name - has to be unique',
  `TrialId` INTEGER NOT NULL COMMENT 'trial id measurements belong to - this is a constrain that grouping measurements between trials is not possible',
  `OperatorId` INTEGER NOT NULL COMMENT 'user - owner of the group',
  `CMGroupStatus` VARCHAR(20) NULL COMMENT 'status of the group',
  `CMGroupDateTime` DATETIME NOT NULL COMMENT 'date and time of the group - possibly creation time or last update',
  `CMGroupNote` TEXT NULL COMMENT 'general comments for the group',
  PRIMARY KEY(`CMGroupId`)
) ENGINE=InnoDB COMMENT='Crossing measurements for some portions of the trials may be grouped to distinct them from the same measurements, which were done before, but need to be retained or to have two sets which can be compared. It can also be used as means to keep several versions of the same dataset.';

CREATE UNIQUE INDEX `xcmg_CMGroupName` ON `cmgroup` (`CMGroupName`);

CREATE  INDEX `xcmg_TrialId` ON `cmgroup` (`TrialId`);

CREATE  INDEX `xcmg_OperatorId` ON `cmgroup` (`OperatorId`);

CREATE  INDEX `xcmg_CMGroupStatus` ON `cmgroup` (`CMGroupStatus`);

CREATE  INDEX `xcmg_CMGroupDateTime` ON `cmgroup` (`CMGroupDateTime`);

ALTER TABLE `cmgroup` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialunittreatment
CREATE TABLE `trialunittreatment` (
  `TrialUnitTreatmentId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal id',
  `TreatmentId` INTEGER NOT NULL COMMENT 'treatment id - which treatment was used inside the unit',
  `TrialUnitId` INTEGER NOT NULL COMMENT 'trial unit id - which trial unit is affected by a treatment',
  `TreatmentDateTime` DATETIME NULL COMMENT 'date time of the treatment',
  `TrialUnitTreatmentNote` TEXT NULL COMMENT 'note for treatment inside this trial unit',
  PRIMARY KEY(`TrialUnitTreatmentId`)
) ENGINE=InnoDB COMMENT='List of treatments for a trial unit.';

CREATE  INDEX `xttr_TreatmentId` ON `trialunittreatment` (`TreatmentId`);

CREATE  INDEX `xttr_TrialUnitId` ON `trialunittreatment` (`TrialUnitId`);

ALTER TABLE `trialunittreatment` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table survey
CREATE TABLE `survey` (
  `SurveyId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Internal survey id',
  `SurveyManagerId` INTEGER NOT NULL COMMENT 'Contact that is assigned as a survey manager',
  `SurveyName` VARCHAR(255) NOT NULL COMMENT 'Survey name',
  `SurveyStartTime` DATETIME NOT NULL COMMENT 'Survey start time',
  `SurveyEndTime` DATETIME NULL COMMENT 'Survey end time',
  `SurveyNote` TEXT NULL COMMENT 'Survey notes and comments',
  `SurveyTypeId` INTEGER NULL COMMENT 'Type of Survey',
  PRIMARY KEY(`SurveyId`)
) ENGINE=InnoDB COMMENT='Survey entity represents field data collection expedition. System users may arbitrary select points of interest (trial units) to visit in arbitrary order. This entity is for planning physical data collection events as well as for calculating an effort in such events.';

CREATE UNIQUE INDEX `xs_SurveryName` ON `survey` (`SurveyName`);

CREATE  INDEX `xs_SurveryStartTime` ON `survey` (`SurveyStartTime`);

CREATE  INDEX `xs_SurveryEndTime` ON `survey` (`SurveyEndTime`);

CREATE  INDEX `xs_SurveyManagerId` ON `survey` (`SurveyManagerId`);

ALTER TABLE `survey` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table surveytrait
CREATE TABLE `surveytrait` (
  `SurveyTraitId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'Internal survey trait id',
  `SurveyId` INTEGER NOT NULL COMMENT 'Survey Id',
  `TraitId` INTEGER NOT NULL COMMENT 'Trait Id',
  `Compulsory` TINYINT NOT NULL DEFAULT '0' COMMENT 'Flag if scoring the trait is compulsory - by default it is not',
  PRIMARY KEY(`SurveyTraitId`)
) ENGINE=InnoDB COMMENT='Linking survey and traits - which traits will be scored during the survey. Can be a subset of traits for trials (experiments) involved, but can also be an arbitrary selection.';

CREATE  INDEX `xst_SurveryId` ON `surveytrait` (`SurveyId`);

CREATE  INDEX `xst_TraitId` ON `surveytrait` (`TraitId`);

ALTER TABLE `surveytrait` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table surveytrialunit
CREATE TABLE `surveytrialunit` (
  `SurveyTrialUnitId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'internal survey trial unit id',
  `SurveyId` INTEGER NOT NULL COMMENT 'survey id',
  `TrialUnitId` INTEGER NOT NULL COMMENT 'trial unit',
  `VisitTime` DATETIME NOT NULL COMMENT 'visit time',
  `VisitOrder` INTEGER NULL COMMENT 'consecutive order number during the visit',
  `CollectorId` INTEGER NULL COMMENT 'id of the person who collected data',
  PRIMARY KEY(`SurveyTrialUnitId`)
) ENGINE=InnoDB COMMENT='Linking survey with trial unit visited during the survey.';

CREATE  INDEX `xstu_SurveyId` ON `surveytrialunit` (`SurveyId`);

CREATE  INDEX `xstu_TrialUnitId` ON `surveytrialunit` (`TrialUnitId`);

CREATE  INDEX `xstu_VisitTime` ON `surveytrialunit` (`VisitTime`);

CREATE  INDEX `xstu_VisitOrder` ON `surveytrialunit` (`VisitOrder`);

CREATE  INDEX `xstu_CollectorId` ON `surveytrialunit` (`CollectorId`);

ALTER TABLE `surveytrialunit` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table taxonomy
CREATE TABLE `taxonomy` (
  `TaxonomyId` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'taxonomy id - internal id of this database',
  `ParentTaxonomyId` INTEGER NULL COMMENT 'parent taxonomy id - what is directly above in hierarchy',
  `TaxonomyName` INTEGER NOT NULL COMMENT 'main name used',
  `TaxonomyClass` VARCHAR(255) NOT NULL COMMENT 'class - species, genera, kingdom',
  `TaxonomySource` VARCHAR(255) NULL COMMENT 'source of taxonomy data - e.g. NCBI, AviBase, World Flora Onlie',
  `TaxonomyExtId` VARCHAR(254) NULL COMMENT 'id in the external source - e.g. tax_id from NCBI',
  `TaxonomyURL` VARCHAR(254) NULL COMMENT 'api endpoint or permalink to fetch information',
  `TaxonomyNote` TEXT NULL COMMENT 'aliased name and extra information',
  PRIMARY KEY(`TaxonomyId`)
) ENGINE=InnoDB COMMENT='Taxonomy hierarchy. Store simple internal taxonomy information from species up and/or link to external taxonomy resources.';

CREATE  INDEX `xtax_ParentTaxonomyId` ON `taxonomy` (`ParentTaxonomyId`);

CREATE  INDEX `xtax_TaxonomyName` ON `taxonomy` (`TaxonomyName`);

CREATE  INDEX `xtax_TaxonomyClass` ON `taxonomy` (`TaxonomyClass`);

ALTER TABLE `taxonomy` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table surveyfactor
CREATE TABLE `surveyfactor` (
  `SurveyId` INTEGER NOT NULL COMMENT 'Survey Id',
  `FactorId` INTEGER NOT NULL COMMENT 'Factor Id',
  `FactorValue` VARCHAR(254) NOT NULL COMMENT 'value',
  PRIMARY KEY(`SurveyId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for genotype descriptions. ';

CREATE  INDEX `FactorId` ON `surveyfactor` (`FactorId`);

ALTER TABLE `surveyfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- table trialunitfactor
CREATE TABLE `trialunitfactor` (
  `TrialUnitId` INTEGER NOT NULL COMMENT 'trial unit id',
  `FactorId` INTEGER NOT NULL COMMENT 'factor id',
  `FactorValue` VARCHAR(255) NOT NULL COMMENT 'value',
  PRIMARY KEY(`TrialUnitId`, `FactorId`)
) ENGINE=InnoDB COMMENT='Additional virtual columns for trial units.';

CREATE  INDEX `FactorId` ON `trialunitfactor` (`FactorId`);

ALTER TABLE `trialunitfactor` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;


ALTER TABLE `contactfactor` ADD FOREIGN KEY (`ContactId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `systemuser` ADD FOREIGN KEY (`ContactId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trial` ADD FOREIGN KEY (`DesignTypeId`)
  REFERENCES `designtype` (`DesignTypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `contactfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `factoralias` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypefactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `sitefactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `treatmentfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialeventfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypealias` ADD FOREIGN KEY (`GenotypeId`)
  REFERENCES `genotype` (`GenotypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypefactor` ADD FOREIGN KEY (`GenotypeId`)
  REFERENCES `genotype` (`GenotypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypetrait` ADD FOREIGN KEY (`GenotypeId`)
  REFERENCES `genotype` (`GenotypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotype` ADD FOREIGN KEY (`GenusId`)
  REFERENCES `genus` (`GenusId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `contact` ADD FOREIGN KEY (`OrganisationId`)
  REFERENCES `organisation` (`OrganisationId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `sitefactor` ADD FOREIGN KEY (`SiteId`)
  REFERENCES `site` (`SiteId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `authorisedsystemgroup` ADD FOREIGN KEY (`SystemGroupId`)
  REFERENCES `systemgroup` (`SystemGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `activitylog` ADD FOREIGN KEY (`UserId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `authorisedsystemgroup` ADD FOREIGN KEY (`UserId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypetrait` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `traitalias` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `treatmentfactor` ADD FOREIGN KEY (`TreatmentId`)
  REFERENCES `treatment` (`TreatmentId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialfactor` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialevent` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunit` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialeventfactor` ADD FOREIGN KEY (`TrialEventId`)
  REFERENCES `trialevent` (`TrialEventId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `samplemeasurement` ADD FOREIGN KEY (`TrialUnitId`)
  REFERENCES `trialunit` (`TrialUnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `samplemeasurement` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trial` ADD FOREIGN KEY (`SiteId`)
  REFERENCES `site` (`SiteId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypespecimen` ADD FOREIGN KEY (`SpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypespecimen` ADD FOREIGN KEY (`GenotypeId`)
  REFERENCES `genotype` (`GenotypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunitspecimen` ADD FOREIGN KEY (`TrialUnitId`)
  REFERENCES `trialunit` (`TrialUnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunitspecimen` ADD FOREIGN KEY (`SpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `samplemeasurement` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimenfactor` ADD FOREIGN KEY (`SpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimenfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trial` ADD FOREIGN KEY (`TrialManagerId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `site` ADD FOREIGN KEY (`CurrentSiteManagerId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimengroupentry` ADD FOREIGN KEY (`SpecimenGroupId`)
  REFERENCES `specimengroup` (`SpecimenGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimengroupentry` ADD FOREIGN KEY (`SpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `pedigree` ADD FOREIGN KEY (`ParentSpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `pedigree` ADD FOREIGN KEY (`SpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimen` ADD FOREIGN KEY (`BreedingMethodId`)
  REFERENCES `breedingmethod` (`BreedingMethodId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `breedingmethodfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `breedingmethodfactor` ADD FOREIGN KEY (`BreedingMethodId`)
  REFERENCES `breedingmethod` (`BreedingMethodId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemfactor` ADD FOREIGN KEY (`ItemId`)
  REFERENCES `item` (`ItemId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemgroupentry` ADD FOREIGN KEY (`ItemId`)
  REFERENCES `item` (`ItemId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemgroupentry` ADD FOREIGN KEY (`ItemGroupId`)
  REFERENCES `itemgroup` (`ItemGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`SpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`UnitId`)
  REFERENCES `generalunit` (`UnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`StorageId`)
  REFERENCES `storage` (`storageId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `deviceregisterfactor` ADD FOREIGN KEY (`DeviceRegisterId`)
  REFERENCES `deviceregister` (`DeviceRegisterId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `deviceregisterfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`ScaleId`)
  REFERENCES `deviceregister` (`DeviceRegisterId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`AddedByUserId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `storage` ADD FOREIGN KEY (`StorageParentId`)
  REFERENCES `storage` (`StorageId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialtrait` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialtrait` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`ContainerTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `deviceregister` ADD FOREIGN KEY (`DeviceTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`ItemTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `site` ADD FOREIGN KEY (`SiteTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trial` ADD FOREIGN KEY (`TrialTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `samplemeasurement` ADD FOREIGN KEY (`SampleTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialevent` ADD FOREIGN KEY (`EventTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `generaltypefactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `generaltypefactor` ADD FOREIGN KEY (`TypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialevent` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimengroup` ADD FOREIGN KEY (`SpecimenGroupTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`ItemSourceId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`ItemStateId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemparent` ADD FOREIGN KEY (`ItemId`)
  REFERENCES `item` (`ItemId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemparent` ADD FOREIGN KEY (`ParentId`)
  REFERENCES `item` (`ItemId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `pedigree` ADD FOREIGN KEY (`ParentType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemparent` ADD FOREIGN KEY (`ItemParentType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunit` ADD FOREIGN KEY (`SourceTrialUnitId`)
  REFERENCES `trialunit` (`TrialUnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypespecimen` ADD FOREIGN KEY (`GenotypeSpecimenType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `item` ADD FOREIGN KEY (`TrialUnitSpecimenId`)
  REFERENCES `trialunitspecimen` (`TrialUnitSpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `workflowdef` ADD FOREIGN KEY (`WorkflowId`)
  REFERENCES `workflow` (`WorkflowId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `workflow` ADD FOREIGN KEY (`WorkflowType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialworkflow` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialworkflow` ADD FOREIGN KEY (`WorkflowdefId`)
  REFERENCES `workflowdef` (`WorkflowdefId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trial` ADD FOREIGN KEY (`CurrentWorkflowId`)
  REFERENCES `workflow` (`WorkflowId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trial` ADD FOREIGN KEY (`ProjectId`)
  REFERENCES `project` (`ProjectId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `project` ADD FOREIGN KEY (`TypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `projectfactor` ADD FOREIGN KEY (`ProjectId`)
  REFERENCES `project` (`ProjectId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `projectfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `project` ADD FOREIGN KEY (`ProjectManagerId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypealiasfactor` ADD FOREIGN KEY (`GenotypeAliasId`)
  REFERENCES `genotypealias` (`GenotypeAliasId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypealiasfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `multimedia` ADD FOREIGN KEY (`FileType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `multimedia` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemlog` ADD FOREIGN KEY (`ItemId`)
  REFERENCES `item` (`ItemId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemlog` ADD FOREIGN KEY (`SystemUserId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemlog` ADD FOREIGN KEY (`LogTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunitspecimen` ADD FOREIGN KEY (`ItemId`)
  REFERENCES `item` (`ItemId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `organisationfactor` ADD FOREIGN KEY (`OrganisationId`)
  REFERENCES `organisation` (`OrganisationId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `organisationfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypealias` ADD FOREIGN KEY (`GenotypeAliasType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genpedigree` ADD FOREIGN KEY (`ParentGenotypeId`)
  REFERENCES `genotype` (`GenotypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genpedigree` ADD FOREIGN KEY (`GenotypeId`)
  REFERENCES `genotype` (`GenotypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genpedigree` ADD FOREIGN KEY (`GenParentType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypealias` ADD FOREIGN KEY (`GenotypeAliasStatus`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trait` ADD FOREIGN KEY (`UnitId`)
  REFERENCES `generalunit` (`UnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialevent` ADD FOREIGN KEY (`UnitId`)
  REFERENCES `generalunit` (`UnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trait` ADD FOREIGN KEY (`TraitGroupTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `generalunit` ADD FOREIGN KEY (`UnitTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialgroupfactor` ADD FOREIGN KEY (`TrialGroupId`)
  REFERENCES `trialgroup` (`TrialGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialgroupfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialgroupentry` ADD FOREIGN KEY (`TrialGroupId`)
  REFERENCES `trialgroup` (`TrialGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialgroupentry` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialgroup` ADD FOREIGN KEY (`TrialGroupType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialgroup` ADD FOREIGN KEY (`TrialGroupOwner`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimengroup` ADD FOREIGN KEY (`SpecimenGroupStatus`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `breedingmethod` ADD FOREIGN KEY (`BreedingMethodTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialtrait` ADD FOREIGN KEY (`UnitId`)
  REFERENCES `generalunit` (`UnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `conversionrule` ADD FOREIGN KEY (`FromUnitId`)
  REFERENCES `generalunit` (`UnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `conversionrule` ADD FOREIGN KEY (`ToUnitId`)
  REFERENCES `generalunit` (`UnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialdimension` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `keywordgroupentry` ADD FOREIGN KEY (`KeywordGroupId`)
  REFERENCES `keywordgroup` (`KeywordGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `keywordgroupentry` ADD FOREIGN KEY (`KeywordId`)
  REFERENCES `keyword` (`KeywordId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunitkeyword` ADD FOREIGN KEY (`TrialUnitId`)
  REFERENCES `trialunit` (`TrialUnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunitkeyword` ADD FOREIGN KEY (`KeywordId`)
  REFERENCES `keyword` (`KeywordId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimenkeyword` ADD FOREIGN KEY (`SpecimenId`)
  REFERENCES `specimen` (`SpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimenkeyword` ADD FOREIGN KEY (`KeywordId`)
  REFERENCES `keyword` (`KeywordId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossing` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossing` ADD FOREIGN KEY (`BreedingMethodId`)
  REFERENCES `breedingmethod` (`BreedingMethodId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossing` ADD FOREIGN KEY (`MaleParentId`)
  REFERENCES `trialunitspecimen` (`TrialUnitSpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossing` ADD FOREIGN KEY (`FemaleParentId`)
  REFERENCES `trialunitspecimen` (`TrialUnitSpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossing` ADD FOREIGN KEY (`UserId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `keywordgroup` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `traitgroupentry` ADD FOREIGN KEY (`TraitGroupId`)
  REFERENCES `traitgroup` (`TraitGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `traitgroupentry` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `traitgroup` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trait` ADD FOREIGN KEY (`TraitDataType`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `keyword` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `smgroup` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `smgroup` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trial` ADD FOREIGN KEY (`SeasonId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotypealias` ADD FOREIGN KEY (`GenusId`)
  REFERENCES `genus` (`GenusId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunittreatment` ADD FOREIGN KEY (`TreatmentId`)
  REFERENCES `treatment` (`TreatmentId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossingmeasurement` ADD FOREIGN KEY (`CMGroupId`)
  REFERENCES `cmgroup` (`CMGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `cmgroup` ADD FOREIGN KEY (`TrialId`)
  REFERENCES `trial` (`TrialId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossingmeasurement` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `cmgroup` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossingmeasurement` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossingmeasurement` ADD FOREIGN KEY (`SampleTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `crossingmeasurement` ADD FOREIGN KEY (`CrossingId`)
  REFERENCES `crossing` (`CrossingId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `surveytrialunit` ADD FOREIGN KEY (`SurveyId`)
  REFERENCES `survey` (`SurveyId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `surveytrait` ADD FOREIGN KEY (`SurveyId`)
  REFERENCES `survey` (`SurveyId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `surveytrait` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `pedigree` ADD FOREIGN KEY (`ParentTrialUnitSpecimenId`)
  REFERENCES `trialunitspecimen` (`TrialUnitSpecimenId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `surveytrialunit` ADD FOREIGN KEY (`TrialUnitId`)
  REFERENCES `trialunit` (`TrialUnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `specimen` ADD FOREIGN KEY (`SourceCrossingId`)
  REFERENCES `crossing` (`CrossingId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemmeasurement` ADD FOREIGN KEY (`ItemId`)
  REFERENCES `item` (`ItemId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemmeasurement` ADD FOREIGN KEY (`TraitId`)
  REFERENCES `trait` (`TraitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemmeasurement` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemmeasurement` ADD FOREIGN KEY (`SampleTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `itemmeasurement` ADD FOREIGN KEY (`IMGroupId`)
  REFERENCES `imgroup` (`IMGroupId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunittreatment` ADD FOREIGN KEY (`TrialUnitId`)
  REFERENCES `trialunit` (`TrialUnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `survey` ADD FOREIGN KEY (`SurveyManagerId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `surveyfactor` ADD FOREIGN KEY (`SurveyId`)
  REFERENCES `survey` (`SurveyId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `surveyfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `imgroup` ADD FOREIGN KEY (`OperatorId`)
  REFERENCES `systemuser` (`UserId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `samplemeasurement` ADD FOREIGN KEY (`SurveyId`)
  REFERENCES `survey` (`SurveyId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `genotype` ADD FOREIGN KEY (`TaxonomyId`)
  REFERENCES `taxonomy` (`TaxonomyId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `taxonomy` ADD FOREIGN KEY (`ParentTaxonomyId`)
  REFERENCES `taxonomy` (`TaxonomyId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunitfactor` ADD FOREIGN KEY (`TrialUnitId`)
  REFERENCES `trialunit` (`TrialUnitId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunitfactor` ADD FOREIGN KEY (`FactorId`)
  REFERENCES `factor` (`FactorId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `trialunit` ADD FOREIGN KEY (`TrialUnitTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `survey` ADD FOREIGN KEY (`SurveyTypeId`)
  REFERENCES `generaltype` (`TypeId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `surveytrialunit` ADD FOREIGN KEY (`CollectorId`)
  REFERENCES `contact` (`ContactId`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

-- additional sql statements


INSERT INTO `organisation` VALUES (1,'Diversity Arrays Technology Pty Ltd');
INSERT INTO `contact` VALUES (1,'Arrays','Diversity','','University of Canberra','02 6122 7300','','admin@example.com',1);
INSERT INTO `systemgroup` VALUES (0,'admin','Admin group');
-- admin initial password is: kddartadmin
INSERT INTO `systemuser` VALUES (0,'admin','dda375a9a8b5e9a809f1939a11a088e06862a253','',1,'2013-12-11 10:01:35','','human');
INSERT INTO `authorisedsystemgroup` VALUES (1,0,0,1);


-- triggers for specimen permissions update, unique genotype and alias naming

DELIMITER //

-- This is to do a combined uniqueness checking between GenotypeName in genotype table
-- and GenotypeAliasName in genotypealias table. MySQL uniqueness constraint cannot be
-- done across multiple tables. In the genotype table, there is a uniquness constraint
-- for GenotypeName and GenusId. However, because the error interaction between table
-- uniqueness constaint with the trigger, this trigger checks GenotypeName, GenusId
-- uniqueness constaint again in addition to the GenotypeAliasName. If the uniqueness
-- constraints in genotype and genotypealias tables are not met, there will be an error
-- when there is an attempt to add a new genotype record.

DROP TRIGGER IF EXISTS `genotype_before_insert`;
CREATE TRIGGER genotype_before_insert 
BEFORE INSERT ON genotype 
FOR EACH ROW
BEGIN
  DECLARE count_geno INT DEFAULT 0;
  DECLARE count_alias INT DEFAULT 0;
  DECLARE msg VARCHAR(255);

	SET count_geno = (SELECT COUNT(*) FROM genotype WHERE GenotypeName=NEW.GenotypeName AND GenusId=NEW.GenusId);

	IF count_geno > 0 THEN
		SET msg = concat('Trigger genotype_before_insert error: genotype name (', NEW.GenotypeName, ') GenusId (', CAST(NEW.GenusId AS CHAR), ') already exists.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
	END IF;

  SET count_alias = (SELECT COUNT(*) FROM genotypealias WHERE GenotypeAliasName=NEW.GenotypeName AND GenusId=NEW.GenusId);

  IF count_alias > 0 THEN
    SET msg = concat('Trigger genotype_before_insert error: genotype name (', NEW.GenotypeName, ') GenusId (', CAST(NEW.GenusId AS CHAR), ') already exists in genotypealias table.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

END//

-- This trigger is similar to genotype_before_insert trigger that it checks the constraints in 
-- genotype and genotypealias tables for the combined uniqueness among GenotypeName and
-- GenotypeAliasName within the same genus. When these constraints are not met, there will be
-- an error when there is an attempt to update an existing genotype record. 

DROP TRIGGER IF EXISTS `genotype_before_update`;
CREATE TRIGGER genotype_before_update 
BEFORE UPDATE ON genotype 
FOR EACH ROW
BEGIN
  DECLARE count_geno INT DEFAULT 0;
  DECLARE count_alias INT DEFAULT 0;
  DECLARE geno_alias_id INT DEFAULT 0;
  DECLARE msg VARCHAR(255);

  SET geno_alias_id = (SELECT GenotypeAliasId FROM genotypealias WHERE GenotypeId=NEW.GenotypeId AND GenotypeAliasName=NEW.GenotypeName);

  IF geno_alias_id IS NULL THEN
     SET geno_alias_id = 0;
  END IF;

	SET count_geno = (SELECT COUNT(*) FROM genotype WHERE GenotypeName=NEW.GenotypeName AND GenusId=NEW.GenusId AND GenotypeId<>NEW.GenotypeId);

	IF count_geno > 0 THEN
		SET msg = concat('Trigger genotype_before_update error: genotype name (', NEW.GenotypeName, ') GenusId (', CAST(NEW.GenusId AS CHAR), ') already exists.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
	END IF;

  SET count_alias = (SELECT COUNT(*) FROM genotypealias WHERE GenotypeAliasName=NEW.GenotypeName AND GenusId=NEW.GenusId AND GenotypeAliasId<>geno_alias_id);

  IF count_alias > 0 THEN
    SET msg = concat('Trigger genotype_before_update error: genotype name (', NEW.GenotypeName, ') GenusId (', CAST(NEW.GenusId AS CHAR), ') already exists in genotypealias table.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

END//

-- This trigger is for replicating genotype permission information to specimen table in order to
-- reduce the need to join genotype with specimen via genotypespecimen table and for replicating
-- GenotypeName and GenusId from genotype to genotypealias table.

DROP TRIGGER IF EXISTS `genotype_after_update`;
CREATE TRIGGER genotype_after_update 
AFTER UPDATE ON genotype 
FOR EACH ROW
BEGIN
  DECLARE old_geno_alias_id INT DEFAULT 0;
  DECLARE new_geno_alias_id INT DEFAULT 0;

  SET old_geno_alias_id = (SELECT GenotypeAliasId FROM genotypealias WHERE GenotypeId=OLD.GenotypeId AND GenotypeAliasName=OLD.GenotypeName);

  IF old_geno_alias_id IS NOT NULL THEN
     UPDATE genotypealias SET
        IsGenotypeName=0
     WHERE
        GenotypeAliasId=old_geno_alias_id;
  END IF;

  SET new_geno_alias_id = (SELECT GenotypeAliasId FROM genotypealias WHERE GenotypeId=NEW.GenotypeId AND GenotypeAliasName=NEW.GenotypeName);

  IF new_geno_alias_id IS NOT NULL THEN
     UPDATE genotypealias SET
       IsGenotypeName=1
     WHERE
       GenotypeAliasId=new_geno_alias_id;
  ELSE
     INSERT INTO genotypealias SET
       GenotypeId=NEW.GenotypeId,
       GenotypeAliasName=NEW.GenotypeName,
       IsGenotypeName=1,
       GenusId=NEW.GenusId;
  END IF;

  IF ( NEW.OwnGroupId <> OLD.OwnGroupId OR
       NEW.OwnGroupPerm <> OLD.OwnGroupPerm OR
       NEW.AccessGroupId <> OLD.AccessGroupId OR
       NEW.AccessGroupPerm <> OLD.AccessGroupPerm OR
       NEW.OtherPerm <> OLD.OtherPerm ) THEN

    UPDATE
      specimen LEFT JOIN genotypespecimen ON specimen.SpecimenId = genotypespecimen.SpecimenId
    SET
      specimen.OwnGroupId      = NEW.OwnGroupId,
      specimen.AccessGroupId   = NEW.AccessGroupId,
      specimen.OwnGroupPerm    = NEW.OwnGroupPerm,
      specimen.AccessGroupPerm = NEW.AccessGroupPerm,
      specimen.OtherPerm       = NEW.OtherPerm
    WHERE
      genotypespecimen.GenotypeId=NEW.GenotypeId AND genotypespecimen.InheritanceFlag=1;

  END IF;

END//

-- This trigger is for creating GenotypeAlias automatically when a new genotype
-- record is added to the database.

DROP TRIGGER IF EXISTS `genotype_after_insert`;
CREATE TRIGGER genotype_after_insert
AFTER INSERT ON genotype
FOR EACH ROW
BEGIN
  INSERT INTO genotypealias SET
    GenotypeId=NEW.GenotypeId,
    GenotypeAliasName=NEW.GenotypeName,
    IsGenotypeName=1,
    GenusId=NEW.GenusId;
END//

-- This trigger is for cross checking the GenotypeAliasName against GenotypeName
-- in genotype table. It also checks it against GenotypeAliasName in genotypealias
-- table. It is redundant to the unique constraint on GenotypeAliasName, GenusId
-- uniqueness. However, this extra checking is here to make this trigger consistent
-- with other triggers.

DROP TRIGGER IF EXISTS `genotypealias_before_insert`;
CREATE TRIGGER genotypealias_before_insert
BEFORE INSERT ON genotypealias
FOR EACH ROW
BEGIN
  DECLARE count_geno INT DEFAULT 0;
  DECLARE count_alias INT DEFAULT 0;
  DECLARE genus_id INT DEFAULT 0;
  DECLARE msg VARCHAR(255);

  SET genus_id = (SELECT GenusId FROM genotype WHERE GenotypeId=NEW.GenotypeId);

  IF NEW.GenusId <> genus_id THEN
    SET msg = concat('Trigger genotypealias_before_insert error: GenusId (', CAST(NEW.GenusId AS CHAR), ') provided is not consistent with GenusId (', CAST(genus_id AS CHAR), ') in the genotype.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

  SET count_geno = (SELECT COUNT(*) FROM genotype WHERE GenotypeName=NEW.GenotypeAliasName AND GenusId=genus_id AND GenotypeId<>NEW.GenotypeId);

  IF count_geno > 0 THEN
    SET msg = concat('Trigger genotypealias_before_insert error: GenotypeAliasName (', NEW.GenotypeAliasName, ') is already used as GenotypeName in another genotype.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

  SET count_alias = (SELECT COUNT(*) FROM genotypealias WHERE GenotypeAliasName=NEW.GenotypeAliasName AND GenusId=genus_id);

  IF count_alias > 0 THEN
    SET msg = concat('Trigger genotypealias_before_insert error: GenotypeAliasName (', NEW.GenotypeAliasName, ') already exists.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

END//

--

DROP TRIGGER IF EXISTS `genotypealias_before_update`;
CREATE TRIGGER genotypealias_before_update
BEFORE UPDATE ON genotypealias
FOR EACH ROW
BEGIN
  DECLARE count_geno INT DEFAULT 0;
  DECLARE count_alias INT DEFAULT 0;
  DECLARE genus_id INT DEFAULT 0;
  DECLARE msg VARCHAR(255);

  SET genus_id = (SELECT GenusId FROM genotype WHERE GenotypeId=NEW.GenotypeId);

  IF NEW.GenusId <> genus_id THEN
    SET msg = concat('Trigger genotypealias_before_update error: GenusId (', CAST(NEW.GenusId AS CHAR), ') provided is not consistent with GenusId (', CAST(genus_id AS CHAR), ') in the genotype.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

  SET count_geno = (SELECT COUNT(*) FROM genotype WHERE GenotypeName=NEW.GenotypeAliasName AND GenusId=genus_id AND GenotypeId<>NEW.GenotypeId);

  IF count_geno > 0 THEN
    SET msg = concat('Trigger genotypealias_before_update error: GenotypeAliasName (', NEW.GenotypeAliasName, ') is already used as GenotypeName in another genotype.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

  SET count_alias = (SELECT COUNT(*) FROM genotypealias WHERE GenotypeAliasName=NEW.GenotypeAliasName AND GenusId=genus_id AND GenotypeAliasId<>NEW.GenotypeAliasId);

  IF count_alias > 0 THEN
    SET msg = concat('Trigger genotypealias_before_update error: GenotypeAliasName (', NEW.GenotypeAliasName, ') already exists.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
  END IF;

  --
END//

DELIMITER ;

