view release on metacpan or  search on metacpan

doc/createGenPerlSchema.sql  view on Meta::CPAN

  snpIndex              MEDIUMINT UNSIGNED NULL,
  isConfirmed           TINYINT(1) NOT NULL,
  confirmMethod		VARCHAR(255) NULL,
  PRIMARY KEY( snpID )
);

CREATE TABLE Allele 
(
  alleleID              MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
  poID                  BIGINT UNSIGNED NOT NULL,
  name                  VARCHAR(4) NOT NULL,
  type                  ENUM("Code", "Size", "RepeatNumber", "Nucleotide", "Undefined") NOT NULL,
  PRIMARY KEY( alleleID ),
  UNIQUE INDEX poAllele ( poID, name, type)
);

CREATE TABLE Genotype
(
  gtID                  BIGINT UNSIGNED NOT NULL,
  subjectID             BIGINT UNSIGNED NOT NULL,
  poID                  BIGINT UNSIGNED NOT NULL,
  isActive              TINYINT(1) NOT NULL,
  icResult              ENUM("Pass", "Fail", "Ambiguous", "Unknown") NULL,
  dateCollected         DATE NULL,
  PRIMARY KEY( gtID ),
  INDEX( subjectID ),
  INDEX( poID )
);

CREATE TABLE AlleleCall
(
  alleleCallID          BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  gtID                  BIGINT UNSIGNED NOT NULL,
  alleleID              MEDIUMINT UNSIGNED NOT NULL,
  sortOrder             TINYINT(2) NOT NULL,
  phase                 ENUM("Unknown", "Maternal", "Paternal") NOT NULL,
  PRIMARY KEY( alleleCallID ),
  INDEX gtIDX( gtID )
);

CREATE TABLE StudyVariable 
(
  studyVariableID       BIGINT UNSIGNED NOT NULL,
  category              ENUM("Trait", "StaticAffectionStatus", "StaticLiabilityClass", "DynamicAffectionStatus", "Environment", "Treatment") NOT NULL,
  format                ENUM("Number", "Code", "Date", "DerivedNumber", "DerivedCode") NOT NULL,
  isXLinked             TINYINT(1) NOT NULL,
  description           VARCHAR(255) NULL,
  numberLowerBound      DECIMAL(12,5) NULL,
  numberUpperBound      DECIMAL(12,5) NULL,
  dateLowerBound        DATE NULL,
  dateUpperBound        DATE NULL,
  PRIMARY KEY( studyVariableID )
);

CREATE TABLE CodeDerivation
(
  codeDerivationID      MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
  studyVariableID       BIGINT UNSIGNED NOT NULL,
  code                  TINYINT(2) NOT NULL,
  description           VARCHAR(255) NULL,
  formula               TEXT NULL,
  PRIMARY KEY( codeDerivationID ),
  INDEX( studyVariableID )
);

CREATE TABLE AffectionStatusDefinition 
(
  asDefID               MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
  studyVariableID       BIGINT UNSIGNED NOT NULL,
  name                  VARCHAR(120) NOT NULL,
  diseaseAlleleFreq     DECIMAL(7,6) NOT NULL,
  pen11                 DECIMAL(7,6) NOT NULL,
  pen12                 DECIMAL(7,6) NOT NULL,
  pen22                 DECIMAL(7,6) NOT NULL,
  malePen1              DECIMAL(7,6) NULL,
  malePen2              DECIMAL(7,6) NULL,
  PRIMARY KEY( asDefID ), 
  INDEX( studyVariableID )
);

CREATE TABLE AffectionStatusElement 
(
  asElementID           MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
  asDefID               MEDIUMINT UNSIGNED NOT NULL,
  code                  TINYINT(1) NOT NULL,
  type                  ENUM("Unknown", "Unaffected", "Affected") NOT NULL,
  formula               TEXT NULL,
  PRIMARY KEY( asElementID ),
  INDEX( asDefID )
);

CREATE TABLE StaticLCPenetrance 
(
  cdID                  MEDIUMINT UNSIGNED NOT NULL,
  pen11                 DECIMAL(7,6) NOT NULL,
  pen12                 DECIMAL(7,6) NOT NULL,
  pen22                 DECIMAL(7,6) NOT NULL,
  malePen1              DECIMAL(7,6) NULL,
  malePen2              DECIMAL(7,6) NULL,
  PRIMARY KEY( cdID ) 
);

CREATE TABLE LiabilityClassDefinition 
(
  lcDefID               MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
  studyVariableID       BIGINT UNSIGNED NOT NULL,
  name                  VARCHAR(120) NOT NULL,
  PRIMARY KEY( lcDefID ),
  INDEX( studyVariableID )
);

CREATE TABLE LiabilityClass
(
  lcID                  MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
  lcDefID               MEDIUMINT UNSIGNED NOT NULL,
  code                  TINYINT(2) NOT NULL,
  description           VARCHAR(255) NULL,
  pen11                 DECIMAL(7,6) NOT NULL,
  pen12                 DECIMAL(7,6) NOT NULL,
  pen22                 DECIMAL(7,6) NOT NULL,
  malePen1              DECIMAL(7,6) NULL,
  malePen2              DECIMAL(7,6) NULL,
  formula               TEXT NULL,
  PRIMARY KEY( lcID ),
  INDEX( lcDefID )
);

CREATE TABLE Phenotype
(
  ptID                  BIGINT UNSIGNED NOT NULL,
  subjectID             BIGINT UNSIGNED NOT NULL,
  svID                  BIGINT UNSIGNED NOT NULL,
  numberValue           DECIMAL(12,5) NULL,
  codeValue             TINYINT(2) NULL,
  dateValue             DATE NULL,
  isActive              TINYINT(1) NOT NULL,
  dateCollected         DATE NULL,
  PRIMARY KEY( ptID ),
  INDEX( subjectID ),
  INDEX( svID )
);

CREATE TABLE FreqSourceObsFrequency
(
  freqSourceID          BIGINT UNSIGNED NOT NULL,
  obsFreqID             MEDIUMINT UNSIGNED NOT NULL,
  INDEX( freqSourceID )
);

CREATE TABLE ObsFrequency
(
  obsFreqID             MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
  type                  ENUM("Allele", "Ht") NOT NULL,
  alleleID              MEDIUMINT UNSIGNED NULL,
  htID                  MEDIUMINT UNSIGNED NULL,
  frequency             FLOAT NOT NULL,
  PRIMARY KEY( obsFreqID )
);

CREATE TABLE HtMarkerCollection 
(
  hmcID                 BIGINT UNSIGNED NOT NULL,
  distanceUnits         ENUM("cM", "bp", "Kb", "Mb", "cR", "cR3000", "cR10000", "Theta") NOT NULL,
  PRIMARY KEY( hmcID )
);

CREATE TABLE HMCPolyObj 
(
  hmcID                 BIGINT UNSIGNED NOT NULL,
  poID                  BIGINT UNSIGNED NOT NULL,
  sortOrder             TINYINT(2) NOT NULL,
  distance              DECIMAL(10,5) NULL,
  INDEX( hmcID )
);

CREATE TABLE Haplotype 
(
  haplotypeID           BIGINT UNSIGNED NOT NULL,
  hmcID                 BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY( haplotypeID ),
  INDEX( hmcID )
);